存储过程:
包
create or replace package emppackage is
-- Author : ADMINISTRATOR
-- Created : 2012-3-22 14:02:43
-- Purpose : 声明一个存储过程
-- Public type declarations
type empcursor is ref cursor;
-- Public function and procedure declarations
--声明一个存储过程
procedure selectEmps(emplist out empcursor);
end emppackage;
包体
create or replace package body emppackage is
-- Function and procedure implementations
procedure selectEmps(emplist out empcursor) is
begin
open emplist for select * from emp;
end;
end emppackage;
在命令窗口中调用:
declare
empc1 emppackage.empcursor;
emp_row emp%rowtype;
begin
emppackage.selectEmps(empc1);
loop
fetch empc1 into emp_row;
exit when empc1%notfound;
dbms_output.put_line(emp_row.empno);
end loop;
close empc1;
end;
结果:
2345
7369
7499
7521
存储函数:
包:
create or replace package mypackage is
-- Author : ADMINISTRATOR
-- Created : 2012-3-22 14:06:47
-- Purpose : 存储函数
-- Public type declarations
type empc1 is ref cursor;
function queryEmps return empc1;
end mypackage;
包体:
create or replace package body mypackage is
function queryEmps return empc1 is
emp_c1 empc1;
begin
open emp_c1 for select * from emp;
return emp_c1;
end;
end mypackage;
在命令窗口中调用:
declare
emp_c1 mypackage.empc1;
emp_row emp%rowtype;
begin
emp_c1:=mypackage.queryEmps;
loop
fetch emp_c1 into emp_row;
exit when emp_c1%notfound;
dbms_output.put_line(emp_row.empno);
end loop;
close emp_c1;
end;
结果:
2345
7369
7499
7521
【存储过程】利用包构建存储过程和存储函数的案例(转)
最新推荐文章于 2022-08-26 16:29:44 发布