代码改变世界

子程序和程序包-4

2012-03-20 09:19  java ee spring  阅读(224)  评论(0编辑  收藏  举报
----程序包 

--案例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;