ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【笔记】游标变量

【笔记】游标变量

原创 Linux操作系统 作者:yellowlee 时间:2009-04-03 13:50:20 0 删除 编辑

/*To centralize data retrieval, you can group type-compatible queries in a stored
procedure. In the example below, the packaged procedure declares a selector as one
of its formal parameters. When called, the procedure opens the cursor variable
emp_cv for the chosen query.*/

CREATE PACKAGE emp_data AS
  TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  PROCEDURE open_emp_cv(emp_cv IN OUT EmpCurTyp, choice INT);
END emp_data;

CREATE PACKAGE BODY emp_data AS
  PROCEDURE open_emp_cv(emp_cv IN OUT EmpCurTyp, choice INT) IS
  BEGIN
    IF choice = 1 THEN
      OPEN emp_cv FOR
        SELECT * FROM emp WHERE comm IS NOT NULL;
    ELSIF choice = 2 THEN
      OPEN emp_cv FOR
        SELECT * FROM emp WHERE sal > 2500;
    ELSIF choice = 3 THEN
      OPEN emp_cv FOR
        SELECT * FROM emp WHERE deptno = 20;
    END IF;
  END;
END emp_data;


/*For more flexibility, you can pass a cursor variable and a selector to a stored
procedure that executes queries with different return types. Here is an example:*/


CREATE PACKAGE admin_data AS
  TYPE GenCurTyp IS REF CURSOR;
  PROCEDURE open_cv(generic_cv IN OUT GenCurTyp, choice INT);
END admin_data;

CREATE PACKAGE BODY admin_data AS
  PROCEDURE open_cv(generic_cv IN OUT GenCurTyp, choice INT) IS
  BEGIN
    IF choice = 1 THEN
      OPEN generic_cv FOR
        SELECT * FROM emp;
    ELSIF choice = 2 THEN
      OPEN generic_cv FOR
        SELECT * FROM dept;
    ELSIF choice = 3 THEN
      OPEN generic_cv FOR
        SELECT * FROM salgrade;
    END IF;
  END;
END admin_data;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16179598/viewspace-584507/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-12-27

  • 博文量
    316
  • 访问量
    658631