子程序和程序包-4

----程序包

--案例09：程序包试验1
/*步骤1：新建包*/
CREATE OR REPLACE PACKAGE pack_me IS
PROCEDURE order_proc (orno VARCHAR2);
FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2;
END pack_me;

/*步骤2：新建包主体*/
CREATE OR REPLACE PACKAGE BODY pack_me AS
PROCEDURE order_proc (orno VARCHAR2) IS
stat CHAR(1);
BEGIN
SELECT ostatus INTO stat FROM order_master
WHERE orderno = orno;
IF stat = 'p' THEN
DBMS_OUTPUT.PUT_LINE('暂挂的订单');
ELSE
DBMS_OUTPUT.PUT_LINE('已完成的订单');
END IF;
END order_proc;
FUNCTION order_fun(ornos VARCHAR2)
RETURN VARCHAR2
IS
icode VARCHAR2(5);
ocode VARCHAR2(5);
qtyord NUMBER;
qtydeld NUMBER;
BEGIN
SELECT qty_ord, qty_deld, itemcode, orderno
INTO qtyord, qtydeld, icode, ocode
FROM order_detail
WHERE orderno = ornos;
IF qtyord < qtydeld THEN
RETURN ocode;
ELSE
RETURN icode;
END IF;
END order_fun;
END pack_me;
/

--步骤3：执行包中的过程
execute pack_me.order_proc('o002');

--步骤4：执行包中的函数
declare
msg varchar2(10);
begin
msg:=pack_me.order_fun('o002');
dbms_output.put_line('值是 :' ||msg);
end;

--案例10：程序包试验2
/*步骤1：新建包*/

create or replace package studentpackage
is
type currefstudent is ref cursor return student%rowtype;
procedure selectstudent(findid in student.stuid%type);
procedure insertstudent(newstudent in student%rowtype);
procedure updatestudent(newstudent in student%rowtype);
procedure deletestudent(delid in student.stuid%type);
procedure returnstudent (inoutstu in out currefstudent);
function returnrecordcount return number;
end studentpackage;

/*步骤2：新建包体*/
create or replace package body studentpackage as
procedure selectstudent(findid in student.stuid%type) as
cursor findcur is select * from student where stuid=findid;
begin
for s in findcur loop
dbms_output.put_line(s.stuid||' '||s.stuname|| ' '||s.sex);
end loop;
exception
when no_data_found then
dbms_output.put_line('没有查到ID为：' ||findid|| '的记录!!');
when others then
dbms_output.put_line('查询过程遇到不可预知的错误！');
end selectstudent;

procedure insertstudent(newstudent in student%rowtype)as
irec integer;
not_exists_student exception; --自定义异常错误
begin
select count(*) into irec from student where stuid=newstudent.stuid;
if irec>0 then
raise not_exists_student;
else
insert into student values (newstudent.stuid, newstudent.stuname, newstudent.sex);
commit;
end if;
exception
when not_exists_student then
dbms_output.put_line ('要插入的编号为：'||newstudent.stuid||'的记录已经存在！');
when others then
dbms_output.put_line('插入记录操作的过程中出现不可预知的错误！');
end insertstudent;

procedure updatestudent(newstudent in student%rowtype) as
irec integer;
begin
select count(*) into irec from student where stuid=newstudent.stuid;
if irec=0 then
dbms_output.put_line('编号为：'||newstudent.stuid || '的记录不存在，修改失败！' );
else
update student set stuname=newstudent.stuname, sex=newstudent.sex
where stuid=newstudent.stuid;
commit;
dbms_output.put_line('修改操作成功！');
end if;
exception
when no_data_found then
dbms_output.put_line('编号为：' ||newstudent.stuid||'的记录不存在，修改失败！');
when others then
dbms_output.put_line('执行修改操作时发生不可预知的错误！修改不成功!');
end updatestudent;

procedure deletestudent (delid in student.stuid%type) as
irec integer;
begin
select count(*) into irec from student where stuid=delid;
if irec=0 then
dbms_output.put_line('编号为:'||delid ||'的记录不存在，删除不成功！');
else
delete from student where stuid=delid;
commit;
dbms_output.put_line('删除成功！恭喜');
end if;
exception
when others then
dbms_output.put_line('执行删除时发生不可预知的错误，未能按要求执行！');
end deletestudent;

procedure returnstudent(inoutstu in out currefstudent) as
begin
open inoutstu for select * from student;
end returnstudent;
function returnrecordcount return number as
reccount number(10);
begin
select count(*) into reccount from student;
return reccount;
exception
when others then
dbms_output.put_line ('查询记录发生不可预知的错误！');
end returnrecordcount;
end studentpackage;

/*步骤3：调用存储过程*/
--1.调用studentpackage中的insertstudent过程
declare
newstu student%rowtype;
begin
newstu.stuid:='1001';
newstu.stuname:='马大哈';
newstu.sex:='男';
studentpackage.insertstudent(newstu);
end;

--2.调用studentpackage中的updatestudent过程

declare
newstu student%rowtype;
begin
newstu.stuid:='1001';
newstu.stuname:='马大哈';
newstu.sex:='女';
studentpackage.updatestudent(newstu);
exception
when dup_val_on_index then
dbms_output.put_line('唯一约束被破坏！');
when others then
dbms_output.put_line('更新过程出现不可预知的错误！');
end;

--3.调用studentpackage中的deletestudent过程

begin
studentpackage.deletestudent('888');
end;

--4.studentpackage中的函数过程
begin
dbms_output.put_line(studentpackage.returnrecordcount);
end;

--案例11：函数的限制

/*步骤1：新建测试表*/
create table sz
(a int);

/*步骤2：新建包*/
create or replace package mypack
as
procedure updatetable(s int);
pragma restrict_references(updatetable, wnds);
end;

/*步骤3：新建包体*/
create or replace package body mypack
as
procedure updatetable(s int) is
begin
update sz set a=s;
end;
end;