原创文章,转载请注明: 转载自慢慢的回味
本文链接地址: Oracle存储过程及管道函数速成
实例学习
通过一个简单的数据结构和存储过程来学习:
有student和course两个表,如下:
CREATE TABLE student ( id NUMBER(38) PRIMARY KEY, name varchar2(100), cls_level NUMBER(2) ); CREATE TABLE course ( id NUMBER(38) PRIMARY KEY, course_name varchar2(100), hard_level NUMBER(2), student_id NUMBER(38) ); INSERT INTO student(id,name,cls_level) VALUES (1,'Zhang',1); INSERT INTO student(id,name,cls_level) VALUES (2,'Li',2); INSERT INTO student(id,name,cls_level) VALUES (3,'Wang',3); INSERT INTO course(id,course_name,hard_level, student_id) VALUES (1,'English',1,1); INSERT INTO course(id,course_name,hard_level, student_id) VALUES (2,'Math',2,1); INSERT INTO course(id,course_name,hard_level, student_id) VALUES (3,'English',1,2); INSERT INTO course(id,course_name,hard_level, student_id) VALUES (4,'Math',2,2); INSERT INTO course(id,course_name,hard_level, student_id) VALUES (5,'English',1,3); INSERT INTO course(id,course_name,hard_level, student_id) VALUES (6,'Math',2,3); |
现在准备用2个存储过程管道函数来完成如下SQL的功能:
SELECT a.name, b.course_name FROM student a JOIN course b ON b.student_id = a.id WHERE b.hard_level = 1 AND a.cls_level = 2; |
最后用如下SQL验证,可以得到上面同样的结果:
SELECT * FROM TABLE(pkg_query_course.query_student_course( p_cls_level => 2, p_hard_level => 1 )); |
存储过程源代码脚步:
CREATE OR REPLACE PACKAGE pkg_query_course AS TYPE course_typ IS RECORD ( course_name VARCHAR2(100), student_id NUMBER(38)); TYPE course_tab IS TABLE OF course_typ; TYPE student_course_typ IS RECORD ( course_name VARCHAR2(100), student_name VARCHAR2(100)); TYPE student_course_tab IS TABLE OF student_course_typ; FUNCTION query_course ( p_hard_level IN NUMBER ) RETURN course_tab pipelined; FUNCTION query_student_course ( p_cls_level IN NUMBER, p_hard_level IN NUMBER ) RETURN student_course_tab pipelined; END pkg_query_course; / CREATE OR REPLACE PACKAGE BODY pkg_query_course AS FUNCTION query_course ( p_hard_level IN NUMBER ) RETURN course_tab pipelined AS v_course_typ course_typ; v_sql clob; TYPE cur IS REF CURSOR; c_trans cur; c_trans_id NUMBER(38); v_result NUMBER(38); BEGIN v_sql := ' select course_name, student_id from course where hard_level = :hard_level'; c_trans_id := DBMS_SQL.open_cursor; DBMS_SQL.parse( c_trans_id, v_sql, DBMS_SQL.native); DBMS_SQL.bind_variable( c_trans_id, ':hard_level', p_hard_level); v_result := DBMS_SQL.EXECUTE(c_trans_id); c_trans := DBMS_SQL.to_refcursor(c_trans_id); LOOP FETCH c_trans INTO v_course_typ; EXIT WHEN c_trans%notfound; pipe ROW(v_course_typ); END LOOP; RETURN; END query_course; FUNCTION query_student_course ( p_cls_level IN NUMBER, p_hard_level IN NUMBER ) RETURN student_course_tab pipelined AS v_student_course_typ student_course_typ; v_sql clob; TYPE cur IS REF CURSOR; c_trans cur; c_trans_id NUMBER(38); v_result NUMBER(38); BEGIN v_sql := ' select b.course_name, a.name from student a join table(pkg_query_course.query_course( p_hard_level => :hard_level )) b on b.student_id = a.id where a.cls_level = :cls_level'; c_trans_id := DBMS_SQL.open_cursor; DBMS_SQL.parse( c_trans_id, v_sql, DBMS_SQL.native); DBMS_SQL.bind_variable( c_trans_id, ':hard_level', p_hard_level); DBMS_SQL.bind_variable( c_trans_id, ':cls_level', p_cls_level); v_result := DBMS_SQL.EXECUTE(c_trans_id); c_trans := DBMS_SQL.to_refcursor(c_trans_id); LOOP FETCH c_trans INTO v_student_course_typ; EXIT WHEN c_trans%notfound; pipe ROW(v_student_course_typ); END LOOP; RETURN; END query_student_course; END pkg_query_course; / |
本作品采用知识共享署名 4.0 国际许可协议进行许可。