PL/SQL程序设计--学习笔记7--包

例4：利用游标变量创建包curvarpack。由于游标变量指是一个指针，其状态是不确定的，因此他不能随同包存储在数据库中，既不能再PL/SQL包中声明游标变量。但子啊包中可以创建游标变量参照类型，并可向包中的子程序传递游标变量参数。
create or replace package curvarpack as
type deptcurtype is ref cursor return dept%rowtype; --强类型定义
type curtype is ref cursor; --弱类型定义
procedure opendeptvar(cv in out deptcurtype,choice integer default 0,dept_no number default 50,dept_name varchar default'%');
end;

create or replace package body curvarpack as
procedure opendeptvar(cv in out deptcurtype,choice integer default 0,dept_no number default 50,dept_name varchar default'%')
is
begin
if choice=1 then
open cv for select * from dept where deptno<=dept_no;
elsif choice=2 then
open cv for select * from dept where dname like dept_name;
else
open cv for select * from dept;
end if;
end opendeptvar;
end curvarpack;

--定义一个过程
create or replace procedure opencurtype(cv in out curvarpack.curtype,tab char)
as
begin
--由于curvarpack.curtype采用弱类型定义，所以可以使用它定义的游标变量打开不同类型的查询语句
if tab='d' then
open cv for select * from dept;
else
open cv for select * from emp;
end if;
end opencurtype;

--定义一个应用
declare
deptrec dept%rowtyp;
emprec emp%rowtype;
cv1 curvarpack.deptcurtype;
cv2 curvarpack.curtype;
begin
dbms_output.put_line('游标变量强类型定义应用');
curvarpack.opendeptvar(cv1,1,30);
fetch cv1 into deptrec;
while cv1%found loop
dbms_output.put_line(deptrec.deptno||'--'||deptrec.dname);
fetch cv1 into deptrec;
end loop;
close cv1;
dbms_output.put_line('游标变量弱定义应用');
curvarpack.opendeptvar(cv2,2,dept_name=>'a%');
fetch cv2 into deptrec;
while cv2%found loop
dbms_output.put_line(deptrec.deptno||'--'||deptrec.dname);
fetch cv2 into deptrec;
end loop;
dbms_output.put_line('游标变量弱定义应用--dept表');
opencurtype(cv2,'d');
fetch cv2 into deptrec;
while cv2%found loop
dbms_output.put_line(deptrec.deptno||'--'||deptrec.dname);
fetch cv2 into deptrec;
end loop;
dbms_output.put_line('游标变量弱定义应用--emp表');
opencurtype(cv2,‘e’);
fetch cv2 into emprec;
while cv2%found loop
dbms_output.put_line(emprec.empno||'--'||emprec.ename);
fetch cv2 into emprec;
end loop;
close cv2;
end;

例5：子程序重载：PL/SQL允许对包内子程序和本地子程序进行重载。所谓重载是指两个或多个子程序有相同的名称，但拥有不同的参数变量、参数顺序或参数数据类型。
create or replace package demo_pack1
is
deptrec dept%rowtype;
v_sqlcode number;
v_sqlerr varchar2(2048);
function query_dept(dept_no in number) return integer;
function query_dept(dept_no in varchar2) return integer;
end demo_pack1;
create or replace package body demo_pack1
is
function check_dept(dept_no number)
return number
is
flag integer;
begin
select count(*) into flag from dept where deptno=dept_no;
if flag>0 then
return 1;
else
return 0;
end if;
end check_dept;
function check_dept(dept_no varchar2)
return number
is
flag integer;
begin
select count(*) into flag from dept where deptno=dept_no;
if flag>0 then
return 1;
else
return 0;
end if;
end check_dept;
function query_dept(dept_no in number)
return integer
is
begin
if check_dept(dept_no)=1 then
select * into deptrec from dept where deptno=dept_no;
return 1;
else
return 0;
end if;
end query_dept;
function query_dept(dept_no in varchar2)
return integer
is
begin
if check_dept(dept_no)=1 then
select * into deptrec from dept where deptno=dept_no;
return 1;
else
return 0;
end if;
end query_dept;
end demo_pack1;

删除包：可以使用drop package命令对不需要的包进行删除。
drop package demo_pack;