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) > 2

SELECT s_id FROM student_course GROUP BY s_id HAVING MIN(grade)> 80 AND MAX(grade) < 100
SELECT s_id FROM student_course GROUP BY s_id HAVING MAX(grade)> 60 

SELECT s_id, c_id, grade FROM student_course WHERE grade > 81
UNION 
SELECT s_id, c_id, grade FROM student_course WHERE grade < 60;
------------------------------------------------------------------------------------------