oracle script

--oracle script--CREATE OR REPLACE FUNCTION is_table_exists(tableName VARCHAR2)  RETURN BOOLEAN IS      row_count  NUMBER;      is_exists  BOOLEAN;  BEGIN  --    SELECT count(1) INTO row_count FROM dual      WHERE EXISTS (SELECT * FROM user_tables WHERE table_name = UPPER(tableName));        --    IF row_count = 0 THEN        is_exists := FALSE;      ELSE      v_sql VARCHAR2;      v_sql = 'DROP TABEL' || tableName;      --EXECUTE IMMEDIATE v_sql USING 'ZENGWENFENG','001';      EXECUTE IMMEDIATE v_sql;      is_exists := TRUE;    END IF;        --    RETURN is_exists;  END;is_table_exists('student');CREATE TABLE student(id VARCHAR2(10),name VARCHAR2(20),age NUMBER(2));COMMENT ON TABLE student IS '学生';COMMENT ON COLUMN student.id is '主键';COMMENT ON COLUMN student.name is '学生姓名';COMMENT ON COLUMN student.age is '学生年龄';ALTER TABLE student ADD CONSTRAINT pk_student PRIMARY KEY(id);INSERT INTO student (id, name, age) VALUES ('005129', 'zhangsan', 22);INSERT INTO student (id, name, age) VALUES ('005128', 'lisi', 24);INSERT INTO student (id, name, age) VALUES ('005127', 'wangwu', 27);INSERT INTO student (id, name, age) VALUES ('005126', 'xiaoliu', 25);------------------------------------------------------------------------------------------is_table_exists('course');CREATE TABLE course(id VARCHAR2(10),name VARCHAR2(20),hours NUMBER,teacher VARCHAR2(20));COMMENT ON TABLE course IS '课程表';COMMENT ON COLUMN course.id is '课程主键';COMMENT ON COLUMN course.name is '课程姓名';COMMENT ON COLUMN course.hours is '课程学时';COMMENT ON COLUMN course.teacher is '课程老师';ALTER TABLE course ADD CONSTRAINT pk_course PRIMARY KEY(id);INSERT INTO course (id, name, hours, teacher) VALUES ('001', 'math', 2, 'li');INSERT INTO course (id, name, hours, teacher) VALUES ('002', 'english', 3, 'wang');INSERT INTO course (id, name, hours, teacher) VALUES ('003', 'chinese', 4, 'zhang');------------------------------------------------------------------------------------------is_table_exists('student_course');CREATE TABLE student_course(s_id VARCHAR2(10),c_id VARCHAR2(10),grade NUMBER(4,2));COMMENT ON TABLE student_course IS '课程表';COMMENT ON COLUMN student_course.s_id IS '学生主键 student.id';COMMENT ON COLUMN student_course.c_id IS '课程主键 course.id';COMMENT ON COLUMN student_course.grade IS '分数';ALTER TABLE student_course ADD CONSTRAINT uk_student_course UNIQUE (s_id, c_id);INSERT INTO student_course (s_id, c_id, grade) VALUES ('005129', '001', 50);INSERT INTO student_course (s_id, c_id, grade) VALUES ('005129', '003', 97);INSERT INTO student_course (s_id, c_id, grade) VALUES ('005128', '001', 86);INSERT INTO student_course (s_id, c_id, grade) VALUES ('005128', '002', 76);INSERT INTO student_course (s_id, c_id, grade) VALUES ('005127', '002', 85);INSERT INTO student_course (s_id, c_id, grade) VALUES ('005127', '003', 91);INSERT INTO student_course (s_id, c_id, grade) VALUES ('005126', '001', 67);INSERT INTO student_course (s_id, c_id, grade) VALUES ('005126', '002', 61);INSERT INTO student_course (s_id, c_id, grade) VALUES ('005126', '003', 80);------------------------------------------------------------------------------------------SELECT * FROM student_course ORDER BY s_id, c_id;SELECT s_id, COUNT(1) AS cnt FROM student_course GROUP BY s_id;SELECT s_id, COUNT(1) AS cnt FROM student_course GROUP BY s_id HAVING COUNT(1) > 2SELECT s_id FROM student_course GROUP BY s_id HAVING MIN(grade)> 80 AND MAX(grade) < 100SELECT s_id FROM student_course GROUP BY s_id HAVING MAX(grade)> 60 SELECT s_id, c_id, grade FROM student_course WHERE grade > 81UNION SELECT s_id, c_id, grade FROM student_course WHERE grade < 60;------------------------------------------------------------------------------------------