Oracle存储过程及管道函数速成

原创文章，转载请注明： 转载自慢慢的回味

本文链接地址: 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 国际许可协议进行许可。

发表回复