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