Oracle存储过程实例代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】 Oracle存储过程实例
作者:Dezai.CN / 发布于2013/5/12/ 1038

	--准备环境
	--表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);
试试其它关键字
　存储过程　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)