子程序和程序包-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;