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

    SELECT qty_ord, qty_deld, itemcode, orderno

    INTO   qtyord, qtydeld, icode, ocode

    FROM order_detail

    WHERE orderno = ornos;

    IF qtyord < qtydeld THEN

      RETURN ocode;

      RETURN icode;

  END order_fun;

/

--步驟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

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;

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; --自定義異常錯誤

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;

  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;

  select count(*) into irec from student where stuid=newstudent.stuid;

  if irec=0 then

   dbms_output.put_line('編号為：'||newstudent.stuid || '的記錄不存在，修改失敗！' );

  update student set stuname=newstudent.stuname, sex=newstudent.sex

   where stuid=newstudent.stuid;

   dbms_output.put_line('修改操作成功！');

  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

select count(*) into irec from student where stuid=delid;

if irec=0 then

  dbms_output.put_line('編号為:'||delid ||'的記錄不存在，删除不成功！');

 delete from student where stuid=delid;

 commit;

  dbms_output.put_line('删除成功！恭喜');

exception

  dbms_output.put_line('執行删除時發生不可預知的錯誤，未能按要求執行！');

end deletestudent;

procedure returnstudent(inoutstu in out currefstudent) as

  open inoutstu for select * from student;

end returnstudent;

function returnrecordcount return number as

 reccount number(10);

  select count(*) into reccount from student;

  return reccount;

 dbms_output.put_line ('查詢記錄發生不可預知的錯誤！');

end returnrecordcount;

/*步驟3：調用存儲過程*/

--1.調用studentpackage中的insertstudent過程

  newstu student%rowtype;

  newstu.stuid:='1001';

  newstu.stuname:='馬大哈';

  newstu.sex:='男';

  studentpackage.insertstudent(newstu);

--2.調用studentpackage中的updatestudent過程

declare

  newstu.sex:='女';

  studentpackage.updatestudent(newstu);

  when dup_val_on_index then

    dbms_output.put_line('唯一限制被破壞！');

   dbms_output.put_line('更新過程出現不可預知的錯誤！');

--3.調用studentpackage中的deletestudent過程

begin

studentpackage.deletestudent('888');

--4.studentpackage中的函數過程

  dbms_output.put_line(studentpackage.returnrecordcount);

--案例11：函數的限制

/*步驟1：建立測試表*/

create table sz

(a int);

/*步驟2：建立包*/

create or replace package mypack

as

  procedure updatetable(s int);

  pragma restrict_references(updatetable, wnds);