Oracle中PLSQL函数传递游标的四种方式（实例）

mingjie

发布于 2022-09-23 10:22:34

4200

发布于 2022-09-23 10:22:34

Oracle游标变量在函数1传递给函数2 的几种方式总结：

1 使用sys_refcursor在函数out参数中传递

drop table employees;
create table employees(employee_id int, salary int, raise int, job_id varchar(16), last_name varchar(16), first_name varchar(16));
insert into employees values (1, 1000, 60, 'ST_CLERK', 'Geller', 'Rose');
insert into employees values (2, 2000, 100, 'ST_CLERK', 'Green', 'Rachel');
insert into employees values (3, 4000, 400, 'ST_AAAAA', 'Bing', 'Chanller');
insert into employees values (4, 6000, 630, 'AD_CLERK', 'Geller', 'Rose');
insert into employees values (5, 7000, 120, 'AD_DSFWR', 'Green', 'Rachel');
insert into employees values (6, 8000, 410, 'AD_AAAAA', 'Bing', 'Chanller');


set serveroutput on;
create or replace procedure p_tmp1(cv out sys_refcursor)
is
  factor        INTEGER := 2;
begin
  OPEN cv FOR
    SELECT salary, salary*factor
    FROM employees
    WHERE job_id LIKE 'AD_%';
end;
/

declare
  cv sys_refcursor;
  sal           employees.salary%TYPE;
  sal_multiple  employees.salary%TYPE;
  factor        INTEGER := 2;
begin
  p_tmp1(cv);
  loop
    FETCH cv INTO sal, sal_multiple;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
    DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
    DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
    factor := factor + 1;
  end loop;
end;
/

执行结果：

factor = 2
sal = 6000
sal_multiple = 12000
factor = 3
sal = 7000
sal_multiple = 14000
factor = 4
sal = 8000
sal_multiple = 16000

2 使用sys_refcursor在函数返回值中传递

drop table employees;
create table employees(employee_id int, salary int, raise int, job_id varchar(16), last_name varchar(16), first_name varchar(16));
insert into employees values (1, 1000, 60, 'ST_CLERK', 'Geller', 'Rose');
insert into employees values (2, 2000, 100, 'ST_CLERK', 'Green', 'Rachel');
insert into employees values (3, 4000, 400, 'ST_AAAAA', 'Bing', 'Chanller');
insert into employees values (4, 6000, 630, 'AD_CLERK', 'Geller', 'Rose');
insert into employees values (5, 7000, 120, 'AD_DSFWR', 'Green', 'Rachel');
insert into employees values (6, 8000, 410, 'AD_AAAAA', 'Bing', 'Chanller');


set serveroutput on;
create or replace function p_fun1(factor int)
  return sys_refcursor
is
  cv sys_refcursor;
begin
  OPEN cv FOR
    SELECT salary, salary*factor
    FROM employees
    WHERE job_id LIKE 'AD_%';
  return cv;
end;
/

declare
  cv sys_refcursor;
  sal           employees.salary%TYPE;
  sal_multiple  employees.salary%TYPE;
  factor        INTEGER := 2;
begin
  cv := p_fun1(2);
  loop
    FETCH cv INTO sal, sal_multiple;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
    DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
    DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
    factor := factor + 1;
  end loop;
end;
/

执行结果：

factor = 2
sal = 6000
sal_multiple = 12000
factor = 3
sal = 7000
sal_multiple = 14000
factor = 4
sal = 8000
sal_multiple = 16000

3 使用包定义的ref cursor类型在函数out参数中传递

drop table employees;
create table employees(employee_id int, salary int, raise int, job_id varchar(16), last_name varchar(16), first_name varchar(16));
insert into employees values (1, 1000, 60, 'ST_CLERK', 'Geller', 'Rose');
insert into employees values (2, 2000, 100, 'ST_CLERK', 'Green', 'Rachel');
insert into employees values (3, 4000, 400, 'ST_AAAAA', 'Bing', 'Chanller');
insert into employees values (4, 6000, 630, 'AD_CLERK', 'Geller', 'Rose');
insert into employees values (5, 7000, 120, 'AD_DSFWR', 'Green', 'Rachel');
insert into employees values (6, 8000, 410, 'AD_AAAAA', 'Bing', 'Chanller');


create or replace package pkg_g1 as
  type type_cv is ref cursor;
end;
/

create or replace procedure p_tmp2(cv out pkg_g1.type_cv)
is
  factor        INTEGER := 2;
begin
  OPEN cv FOR
    SELECT salary, salary*factor
    FROM employees
    WHERE job_id LIKE 'AD_%';
end;
/

declare
  cv pkg_g1.type_cv;
  sal           employees.salary%TYPE;
  sal_multiple  employees.salary%TYPE;
  factor        INTEGER := 2;
begin
  p_tmp2(cv);
  loop
    FETCH cv INTO sal, sal_multiple;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
    DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
    DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
    factor := factor + 1;
  end loop;
end;
/

执行结果：

factor = 2
sal = 6000
sal_multiple = 12000
factor = 3
sal = 7000
sal_multiple = 14000
factor = 4
sal = 8000
sal_multiple = 16000

3 使用包定义的ref cursor类型在函数返回值中传递

drop table employees;
create table employees(employee_id int, salary int, raise int, job_id varchar(16), last_name varchar(16), first_name varchar(16));
insert into employees values (1, 1000, 60, 'ST_CLERK', 'Geller', 'Rose');
insert into employees values (2, 2000, 100, 'ST_CLERK', 'Green', 'Rachel');
insert into employees values (3, 4000, 400, 'ST_AAAAA', 'Bing', 'Chanller');
insert into employees values (4, 6000, 630, 'AD_CLERK', 'Geller', 'Rose');
insert into employees values (5, 7000, 120, 'AD_DSFWR', 'Green', 'Rachel');
insert into employees values (6, 8000, 410, 'AD_AAAAA', 'Bing', 'Chanller');


create or replace package pkg_g1 as
  type type_cv is ref cursor;
end;
/

create or replace function p_fun2(factor int)
  return pkg_g1.type_cv
is
  cv pkg_g1.type_cv;
begin
  OPEN cv FOR
    SELECT salary, salary*factor
    FROM employees
    WHERE job_id LIKE 'AD_%';
  return cv;
end;
/

declare
  cv pkg_g1.type_cv;
  sal           employees.salary%TYPE;
  sal_multiple  employees.salary%TYPE;
  factor        INTEGER := 2;
begin
  cv := p_fun2(2);
  loop
    FETCH cv INTO sal, sal_multiple;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
    DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
    DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
    factor := factor + 1;
  end loop;
end;
/

执行结果：

factor = 2
sal = 6000
sal_multiple = 12000
factor = 3
sal = 7000
sal_multiple = 14000
factor = 4
sal = 8000
sal_multiple = 16000

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-08-22，如有侵权请联系 cloudcommunity@tencent.com 删除

css

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

css

登录后参与评论

0 条评论

热度

Oracle中PLSQL函数传递游标的四种方式（实例）

Oracle中PLSQL函数传递游标的四种方式（实例）

1 使用sys_refcursor在函数out参数中传递

2 使用sys_refcursor在函数返回值中传递

3 使用包定义的ref cursor类型在函数out参数中传递

3 使用包定义的ref cursor类型在函数返回值中传递

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐