PL/SQL存储函数，存储过程

一.存储过程和存储函数

存储过程和存储函数跟我们知道的表、视图、索引、序列、同义词等一样是我们数据中的对象。

笔记教程见：https://github.com/caojx-git/learn/blob/master/notes/oracle/PLSQL.sql

1.1什么是存储过程和存储函数：

指存储在数据库中供所有的用户程序调用的 子程序叫存储过程、存储函数。
存储过程和存储函数的相同点：完成特定功能的程序
存储过程和存储函数的区别：是否用return 语句返回值，存储过程不能使用return 返回一个函数的值，存储函数可以，对于其他的
可以认为他们是相同的。

二、创建和使用存储过程

使用create procedure命令建立存储过程和存储函数

 

语法：

 

 

	--创建或替换一个存储过程              参数列表需要指明输入或者输出参数
	create [or replace] procedure 过程名（Name in | out type, Name in | out type, ...）
	as | is --相当于PL/SQL块的declare，这里不可省略
	PLSQL子程序体；

 

 

 

 

 

1.2.1不带参数的存储过程

--第一个存储过程打印helloworld --注意不带参数的存储过程过程名不能有()

create [or replace] procedure sayhelloWorld
	as 
	--相当于PL/SQL中declare说明部分，不过这里即使没有说明部分也需要写
	begin
		dbms_output.put_line('helloWorld');
	end sayhelloWorld;

 

 

如果使用PL/SQL Developer 工具，如果执行成功可以在左边的procedure区域看到我们执行后编译后的存储过程（成功一般显示为绿色，如果不对会有一个小红x）

 

 

1.2.2调用存储过程

方式一：在 Command Windows（命令窗口中执行） exec sayhelloWorld();


方式二：可以在SQL Windows或 Command Windowsz中执行PL/SQL调用

1.2.3带参数的存储过程

--创建一个带参数的存储过程，给指定的员工涨100员工资，并打印涨前和涨后的薪水

--in表示是一个输入参数，如果带参数，需要指明是输入参数还是输出参数

 

	create or repalce procedure raisesalary(eno in number) 
		as
			--定义一个变量保存涨前的薪水
			psal emp.sal%type;
		begin
			--得到员工的涨前的薪水
			select sal into psal emp where empno=eno;
			--给员工涨100
			update emp set sal = sal+100 where empno = eno;
			--这里进行了update,不过我们一般不在存储过程和存储函数中进行提交事务，一般由调用者进行提交

			--打印涨前和涨后的薪水
			dbms_output.put_line('涨前:'||psal||'涨后'||(psal+100));
		end;

 

 

 

 

 

1.2.4 带参数的存储过程的调用

			begin
				raisesalary(7839); --给员工号为7839涨工资
				raisesalary(7566); --给员工号为7566涨工资
			end;

 

1.3存储过程的debug调试

有时候有一些存储过程比较大，需要进行debug调试，看是否符合我们的逻辑需求，使用PL/SQL Developer可以对存储过程进行调试

1.3.1选中需要调试的存储过程--》test进入debug调试模式

 

1.3.2设置断点，可以单步运行

 

三、存储函数

函数（Function） 为一命名的存储程序，可带参数，并返回一计算值
函数和过程结构类似，但必需要有一个return子句，用于返回函数数值。
 

3.1创建存储函数的语法

--带参数的存储函数必需指明参数列表是输入参数还是输出参数

--假如不带参数，不能带（）

 

	create [or replace] function 函数名（Name in | out type, Name in | out type, ...）
	return 函数值类型
	as | is
	PL/SQL子程序体；

 

 

案例：

 

 

--存储函数：查询某个员工的年收入

 

 

		create or replace function queryempincomme(eno in number)
		return number
		as
			--定义变量保存员工的薪水和奖金
			psal emp.sal%type;
			pcomm emp.comm%type;
		begin
			--得到员工的月薪和奖金
			select sal,comm into psal,pcomm from emp where empno=eno;
			--直接返回年收入
			return psal*12+nvl(pcomm,0);
		end;

 

 

 

 

 

3.1.1存储函数的调用

		declare
			ypsal number;
		begin
			--得到员工7891的年收入
			ypsal:=queryempincomme(7891);
			dbms_output.put_line(ypsal);
		end;

 

四、in和out 参数

前边我们介绍了存储过程和存储函数
a.一般来讲，存储过程和存储函数的区别在于存储函数可以有一个返回值；而存储过程没有返回值
b.如果存储过程或存储函数带参数的话我们需要指明是输入参数还是输出参数
c.存储过程和存储函数都可以通过out参数指定一个或多个输出参数，我们可以利用out参数，在过程或函数中实现返回一个或多个值
 （即存储过程本来不能有返回值，但利用out参数，我们就可以实现存储过程返回值）
d.一般如果需要返回多个值，我们优先使用存储过程，如果只要返回一个值我们优先使用存储函数

案例：
--利用out参数查询员工的姓名，月薪和职位

	create or replace procedure queryempinfo(eno in number,pename out varchar2,psal out nubmer,pjob out varchar2)
	as
	begin
		--得到员工的姓名，月薪，职位
		select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
	end;

调用：

 

	declare 
		eno number;
		pename varchar2(30);
		psal number;
		pjob varchar2(200);
	begin
		eno := 7839;
		--调用存储过程，我们可以得到out参数的返回值
		queryempinfo(eno,pename,psal,pjob);
		dbms_output.put_line(pename);
		dbms_output.put_line(psal);
		dbms_output.put_line(pjob);
	end;

问题：上边的案例只利用out参数返回了员工的部分信息
1.假如需要查询员工的所有信息，out参数有很多，难道要写很多个out参数？
2.查询某个部门中所有员工的所有信息---》out中返回一个集合

 

五、程序包

 

程序包在一般情况下使用的还是比较少的，这里粗略的说一下，如果需要使用，请见其他资料如《精通Oracle10g SQL和PL/SQL》

oracle中的程序包分为包头和包体，包头负责声明，包体负责实现（者很像java中的接口与实现类的关系）

包头语法：

	create [or replace] package package_name
	is | as
	--定义公用常量、变量、游标、类型
	--定义公用的过程和函数
	end package_name;

包体语法

 

	create [or replace] package body package_name
	is | as
	--定义私有常量、变量、类型、游标、过程和函数
	--实现公用的过程和函数
	end package_name

案例：查询某个部门中所有员工的所有信息,这里使用如下方案，实现第4条留下的几个问题out参数很多显然不可取，我们是使用cursor光标实现

创建包头

 

	create or replace package mypackage as 
		--定义公用的类型 自定义类型empcursor 为 cursor类型
		type empcursor is ref cursor;
		--定义公用的过程和函数 --之后需要在包体中实现
		procedure queryEmpList(dno in number,empList out empcursor);
		end mypackage;

创建包体

 

		create or replace package body mypackage
		as
			procedure queryEmpList(dno in number,empList out empcursor)
			as
			begin
				--打开光标
				open empcursor from select * from emp where deptno=dno;
			end queryEmpList;
		end mypackage;

 

 

 

调用程序包：
调用公用变量
exec 程序包名.公用变量名 := 赋值；
调用公共过程
exec 程序包名.公用过程名(参数)；

 

 

 

 

 

 

 

 

下边两个图是我截取《精通Oracle10g SQL和PL/SQL》的片段