Oracle存储过程实例

--准备环境
--表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');

----------------------------------------------------------------
****************************************************************
第一部分：开发存储过程
****************************************************************
----------------------------------------------------------------
----------------------------------------------------------------
***************************************************************
1.建立过程：不带任何参数
---------------------------------------------------------------
--案例01：建立一个输出当前系统日期和时间的过程
create or replace procedure system_out_time
is
begin
dbms_output.put_line(systimestamp);
end;

调用过程方法01：
SQL> exec system_out_time;

调用过程方法02：
SQL> call system_out_time();
 
--案例01：打印乘法小九九
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;

----------------------------------------------------------------
***************************************************************
2.建立过程：带输入IN参数
---------------------------------------------------------------

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

----案例02：根据已知的编号显示出对应职工的姓名
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;
 
--案例03：新建一个带有输入参数的存储过程
create or replace procedure add_empoyee
(
eno number,
name varchar2,
sal number,
job varchar2 default 'CLERK',
dno number
)
is
e_inte  exception;
pragma exception_init(e_inte, -2291);  --关联例外和错误号,相当于raise
begin
insert into emp (empno, ename, sal, job,deptno) values (eno, name, sal, job, dno);
exception
when dup_val_on_index then    --dup_val_on_index是oracle预定义错误
raise_application_error(-20000,'雇员信息不能重复！');   --raise_application_error显示触发例外
when e_inte then
raise_application_error(-20001, '部门号不存在！');
end;
 
---如该改写为，注意运行结果的异同：
create or replace procedure add_empoyee
(
eno number,
name varchar2,
sal number,
job varchar2 default 'CLERK',
dno number
)
is
e_inte  exception;
begin
insert into emp (empno, ename, sal, job,deptno) values (eno, name, sal, job, dno);
exception
when dup_val_on_index then    --dup_val_on_index是oracle预定义错误
dbms_output.put_line('雇员信息不能重复！');   
when e_inte then
dbms_output.put_line('部门号不存在！');
end;
 
--调用方法
exec add_empoyee(&no,'&name', &sal,'&job',&dno);
exec add_empoyee(1113,'CLERK',2000,'MANAGER',15);
如果使用job的默认值则：
exec add_empoyee(1113,'CLERK',2000,null,10);
 
----------------------------------------------------------------
***************************************************************
3.建立过程：带输入out参数
---------------------------------------------------------------
 
 
--案例01:带有输出out参数的存储过程
--新建存储过程
create or replace procedure test001
(epno in number,
v02 out varchar2)
as
salary number;
begin
 select sal into salary from emp
where empno=epno;
if salary <1000 then
  v02:='这样的工资太低！';
elsif salary  between 1000 and 2000 then
  v02:='这样的工资还可以接受！';
else
v02:='这样的待遇是我们不离开的原因！';
end if;
end;

--调用存储过程方法01 (这样的值是固定的)：
declare 
 v2 varchar2(200);  --声明变量时需要和输入参数的类型一致
 begin
 test001 (&no, v2);
 dbms_output.put_line('v02的值为：'||v2);
end;
 
--调用存储过程方法02（用户交互）：
declare 
 empno number:=&empno;
 v2 varchar(200);  --声明变量时需要和输入参数的类型一致
 begin
 test001 (empno, v2);
 dbms_output.put_line('v02的值为：'||v2);
end;
------也可以写成
declare 
 empno number;
 v2 varchar(200);  --声明变量时需要和输入参数的类型一致
 begin
 empno:=&empno;
 test001 (empno, v2);
 dbms_output.put_line('v02的值为：'||v2);
end;
 
--案例02：新建一个用于输出雇员名以及工资的过程
create or replace procedure query_emp
(
eno number,
name  out varchar2,
salary out number
)
is
begin
select ename, sal into name, salary from emp where empno=eno;
exception
when no_data_found then
raise_application_error(-20000, '这个员工不存在！');
end;
 
--调用过程方法01:(使用固定的变量)
declare
nae varchar2(10);  --这里的变量和过程中输入参数的类型需要对应
sala number;
begin
query_emp(7788,nae,sala);
dbms_output.put_line('the employee name is : '||nae);
dbms_output.put_line('the employee sal is : '||sala);
end;
--调用过程方法02:(使用交互的变量)
declare
nae varchar2(10);  --这里的变量和过程中输入参数的类型需要对应
sala number;
begin
query_emp(&empno,nae,sala);
dbms_output.put_line('the employee name is : '||nae);
dbms_output.put_line('the employee sal is : '||sala);
end;
----------------------------------------------------------------
在存储过程中使用dbms_output.put_line
----------------------------------------------------------------
--案例02的另外一种写法：
create or replace procedure query_emp
(
eno number,
name  out varchar2,
salary out number
)
is
begin
select ename, sal into name, salary from emp where empno=eno;
dbms_output.put_line('the employee name is : '||name);
dbms_output.put_line('the employee sal is : '||salary);
exception
when no_data_found then
raise_application_error(-20000, '这个员工不存在！');
end;
 
--调用过程方法01:(使用固定的变量)
declare
nae varchar2(10);  --这里的变量和过程中输入参数的类型需要对应
sala number;
begin
query_emp(7788,nae,sala);
end;

--调用过程方法02:(使用交互式变量)
declare
nae varchar2(10);  --这里的变量和过程中输入参数的类型需要对应
sala number;
begin
query_emp(&empno,nae,sala);
end;
 
 
 

----------------------------------------------------------------
***************************************************************
4.建立过程：带输入 IN out参数
---------------------------------------------------------------
--案例01：新建带 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;

--案例02:新建一个带有in out参数的过程
create or replace procedure comp
(
nm01 in out number,
nm02 in out number
)
is 
v1 number;
v2 number;
begin
v1:=nm01/nm02;
v2:=mod(nm01,nm02);
nm01:=v1;
nm02:=v2;
end;

--调用
declare
n1 number;
n2 number;
begin
n1:=100;
n2:=30;
comp(n1,n2);
dbms_output.put_line(n1);
dbms_output.put_line(n2);
end;
 
----------------------------------------------------------------
***************************************************************
5.建立过程：为参数传递变量和数据
---------------------------------------------------------------
exec queryempname(sfindno=>7788);

exec queryempname(sfindno=>&no);