子程序和程序包-CSDN博客

本文链接：https://blog.csdn.net/solomon_wei/article/details/4525618

--准备环境
--表1
CREATE TABLE itemfile
(
itemcode varchar2(4),
itemdesc varchar2(20),
p_category varchar2(20),
qty_hand number(5),
re_level number(5),
max_level number(5),
itemrate number(7,2)
);

INSERT INTO itemfile VALUES('i201','nuts','spares',100,50,250,20);
INSERT INTO itemfile VALUES('i202','bolts','spares',95,125,300,16.5);
INSERT INTO itemfile VALUES('i204','holders','spares',18,30,75,112);
INSERT INTO itemfile VALUES('i205','covers','accessories',30,15,50,400);
INSERT INTO itemfile VALUES('i203','panels','accessories',75,30,150,4000);
INSERT INTO itemfile VALUES('i206','brackets','spares',150,73,200,132);

COMMIT;

--表2
CREATE TABLE order_master
(
orderno VARCHAR2(5),
odate DATE,
vencode VARCHAR2(5),
ostatus CHAR(1),
del_date DATE
);
alter session set nls_date_language = 'AMERICAN';
INSERT INTO order_master VALUES('o001','12-MAY-05', 'V002','c', '15-MAY-05');
INSERT INTO order_master VALUES('o002','14-MAY-05', 'V001','p', '15-MAY-05');
INSERT INTO order_master VALUES('o003','14-MAY-05', 'V001','p', '15-FEB-05');
INSERT INTO order_master VALUES('o004','14-MAY-05', 'V003','p', '15-FEB-05');
INSERT INTO order_master VALUES('o005','14-MAY-05', 'V001','p', '15-FEB-05');
INSERT INTO order_master VALUES('o006','14-APR-03', 'V004','p', '18-MAY-05');
INSERT INTO order_master VALUES('o007','14-MAY-04', 'V003','p', '10-FEB-05');
INSERT INTO order_master VALUES('o008','11-MAY-05', 'V001','p', '12-JUN-05');
INSERT INTO order_master VALUES('o009','14-JAN-05', 'V002','c', '16-FEB-05');
INSERT INTO order_master VALUES('o011','14-JAN-05', 'V001','p', '10-FEB-05');
INSERT INTO order_master VALUES('o012','14-FEB-05', 'V003','p', '15-MAY-05');
INSERT INTO order_master VALUES('o013','14-MAR-05', 'V001','p', '15-MAY-05');
INSERT INTO order_master VALUES('o014','14-FEB-05', 'V002','c', '12-MAY-05');
INSERT INTO order_master VALUES('o015','14-APR-03', 'V004','p', '17-APR-05');
COMMIT;

alter session set nls_date_language = 'SIMPLIFIED CHINESE';

--表3
CREATE TABLE ORDER_DETAIL
(
ORDERNO     VARCHAR2(5) PRIMARY KEY,
ODATE       DATE,
VENCODE     VARCHAR2(5),
itemcode    VARCHAR2(10),
qty_ord     NUMBER,
qty_deld    NUMBER,
OSTATUS     CHAR(1),
DEL_DATE    DATE,
ORDER_COST NUMBER
);

--表4
create table student
(
stuid int,
stuname varchar2(10),
sex varchar2(2)
);

insert into student values (101, '刘德华', '男');
insert into student values (102, '张学友', '男');
insert into student values (103, '周润发', '男');
insert into student values (104, 'wind', '女');
insert into student values (105, '林青霞', '女');

--表5
create table subject
(
subid varchar2(10) primary key,
subname varchar2(30) not null
);
insert into subject values ('s001', 'oracle');
insert into subject values ('s002', 'java');

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--案例00：打印乘法小九九
create or replace procedure xjj as
i integer;
j integer;
begin
    dbms_output.put_line(' 打印小九九   ');
     for i in 1..9 loop --这里使用两个..，如果是三个则报错
     for j in 1..9 loop
if i>=j then
    dbms_output.put_line(to_char(j) ||'*'||to_char(i) || '='||to_char(i*j)||' ');
    end if;
    end loop;
dbms_output.put_line('    ');
end loop;
end;

--案例01：一个简单的完整案例
create or replace procedure
find_emp(emp_no number)
as
empname emp.ename%type;
begin
select ename into empname from emp where empno=emp_no;
dbms_output.put_line(' 雇员的姓名是： '|| empname);
exception
when no_data_found then
dbms_output.put_line ( ' 雇员编号未找到： ');
end find_emp;

----案例02-1：根据已知的编号显示出对应职工的姓名
create or replace procedure queryempname
(sfindno emp.empno%type )
as
sname emp.ename%type;
sjob emp.job%type;
begin
select ename, job into sname, sjob from emp where empno=sfindno;
dbms_output.put_line(' 编号为 '||sfindno|| ' 的职工姓名为 '||sname || ' 工作为 :' ||sjob);
exception
when no_data_found then
dbms_output.put_line('没有符合条件的记录！');
when too_many_rows then
dbms_output.put_line('返回的行数太多！');
when others then
dbms_output.put_line('发生以外错误！');
end;

--案例02-2:带有输出out参数的存储过程

--新建存储过程
create or replace procedure test001
(v01 in varchar2,
v02 out number)
as
identity number;
begin
select itemrate into identity from itemfile
where itemcode=v01;
if identity <200 then
v02:=100;
else
v02:=50;
end if;
end;

--调用存储过程方法01：
declare
v2 number; --声明变量时需要和输入参数的类型一致
begin
test001 ('i202', v2);
dbms_output.put_line('v02的值为：'||to_char(v2));
end;

--调用存储过程方法02：
declare
v1 varchar2(30):='i203';
v2 number; --声明变量时需要和输入参数的类型一致
begin
test001 ('i202', v2);
dbms_output.put_line('v02的值为：'||to_char(v2));
end;
------也可以写成
declare
v1 varchar2(30);
v2 number; --声明变量时需要和输入参数的类型一致
begin
v1:='i203';
test001 ('i202', v2);
dbms_output.put_line('v02的值为：'||to_char(v2));
end;

--案例02－3：新建带 IN OUT参数的过程
--新建代码
create or replace procedure swap
(
p1 in out number,
p2 in out number
)
as
v_temp number;
begin
v_temp:=p1;
p1:=p2;
p2:=v_temp;
end;

--调用带IN OUT参数的存储过程方法01：
/*由于参数即是输入又是输出参数，所以需要声明变量并输入值*/
declare
n01 number:=100;
n02 number:=200;
begin
swap(n01, n02);
dbms_output.put_line ('n01=' ||n01);
dbms_output.put_line ('n02=' ||n02);
end;

--调用带IN OUT参数的存储过程方法02：
declare
n01 number;
n02 number;
begin
n01:=100;
n02:=200;
swap(n01, n02);
dbms_output.put_line ('n01=' ||n01);
dbms_output.put_line ('n02=' ||n02);
end;

--案例03：新建函数案例
create or replace function getname(sno varchar2)
return varchar is /*返回类型后面是不能使用精度值的，只是表示返回的数据类型*/
name varchar(12);
begin
select ename into name from emp where empno=sno;
return name;
exception
when too_many_rows then
dbms_output.put_line('返回值的行数太多！');
when others then
dbms_output.put_line('执行getname函数出现以外错误！');
end;

----案例04：调用函数

declare
nm varchar(12);
begin
nm:=getname('7369');
dbms_output.put_line('输出的值是：'||nm);
end;

--案例05：存储过程编译错误解决
create or replace procedure testproc
as
begin
select * from emp;
end;

--解决办法01：
　　show errors
/*数据库会自动提示编译错误的原因！*/
--解决方法02：
select * from user_errors;

--案例06：带有参数的存储过程完整案例
--新建代码过程
create or replace procedure runbyparameters
(isal in emp.sal%type, --输入指定的工资
sname out varchar, /*输出查询的结果：姓名*/
--sjob in out emp.job%type --输入工作，输出‘找到’或‘没有找到’
sjob in out varchar
/*如果作为输出时类型最好使用长一些的字符型号可以保证
能够保存输出结果;所以输出参数通常不需要带精度，输入参数可以带精度！*/
)
as
icount number;/*过程内部变量声明，用于记录查询到符合条件的记录数目*/
begin
select count(*) into icount from emp
where sal>isal and job=sjob;/*根据两个输入参数将结果给输出参数*/
if icount=1 then
select ename into sname from emp where sal>isal and job=sjob;
/*查找到的信息情况*/
   sname:=' 姓名为 :'||sname||'的职工工资:'||to_char(isal);
   sjob:='工作为:'||sjob;
else
   sname:='没有查到工资为:'||to_char(isal);
    sjob:=' 工作为:'||sjob||' 的记录!';
end if;
exception
   when too_many_rows then
      dbms_output.put_line('返回的值多余一行!');
   when others then
     dbms_output.put_line ('执行过程中发生了不可预知的错误!');
end;

--调用该存储过程
declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40); --emp.job%type;
begin
realsal:=1100;
realname:='';
realjob:='CLERK';
/*第一次调用*/
runbyparameters(realsal, realname, realjob);
dbms_output.put_line(realname||' '||realjob);
/*第二次调用*/
realjob:='MANAGER';
runbyparameters(2900, realname, realjob);
dbms_output.put_line(realname||' '||realjob);
/*第三次调用*/
runbyparameters
(isal=>realsal, sname=>realname, sjob=>realjob);
dbms_output.put_line('带有联合符号'||realname||''||realjob);
end;

--如果分步骤执行则如下：
--第一次调用：
declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40); --emp.job%type;
begin
realsal:=1100;
realname:='';
realjob:='CLERK';
/*第一次调用*/
runbyparameters(realsal, realname, realjob);
dbms_output.put_line(realname||' '||realjob);
end;

--第二次调用：
declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40); --emp.job%type;
begin
realsal:=1100; --由于调用时使用了2900则这里的1100实效
realname:='';
realjob:='CLERK';
/*第二次调用*/
realjob:='MANAGER';
runbyparameters(2900, realname, realjob);
dbms_output.put_line(realname||' '||realjob);
end;

--第三次调用：

declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40); --emp.job%type;
begin
realsal:=1100;
realname:='';
realjob:='CLERK';
/*第三次调用*/
runbyparameters
(isal=>realsal, sname=>realname, sjob=>realjob);
dbms_output.put_line('带有联合符号'||realname||''||realjob);
end;

--案例07：需要使用自主事务的情况
/*步骤1：新建过程p1*/
create or replace procedure p1
as
begin
insert into student values (106, '成龙', '男');
rollback;
end;

/*步骤2：新建内部调用过程P1的过程P2*/
create or replace procedure p2
as
begin
update student set sex='女';
p1; --调用过程P1
end;

/*
由于P2过程调用时会调用过程P1，P2过程先更新值，再运行P1的插入值并且回滚，这里的rollback
只是影响插入的操作还是把更新和插入的操作都回滚呢？

/*步骤3：调用过程P2验证结果：*/
execute p2;
select * from student;

--案例08：使用自主事务

/*步骤1：编译新建过程P1*/
create or replace procedure p1
as
pragma autonomous_transaction;
begin
insert into student values (106, '成龙', '男');
rollback;
end;

/*验证结果*/
--运行过程P2
execute p2;
select * from student;

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