1、存储过程与函数的概述

• 存储过程和存储函数：是存储在数据库中的被命名的PLSQL块，供所有用户程序调用，完成特定功能的子程序。

a、存储过程和存储函数的区别

• 是否使用return返回值。（即存储过程不返回值，存储函数返回值）

b、第一个存储过程与函数的程序

• 用存储过程或者函数实现输出‘Hello Everyone!’。

--创建存储过程
		CREATE OR REPLACE PROCEDURE first_proc
		IS
		BEGIN
			DBMS_OUTPUT.put_line('我的过程');
			DBMS_OUTPUT.put_line('Hello Everyone!');
		END;
		--创建函数
		CREATE OR REPLACE FUNCTION first_func
		RETURN VARCHAR
		IS 
		BEGIN
			DBMS_OUTPUT.put_line('我的函数');
			RETURN 'hello everyone';
		END;
	```
	-  可以看到，当执行完代码之后，左侧的all objects中已经的Functions和Procedures中已经分别保存了存储过程FIRST_FUNC和存储函数FREST_PROC。
![](http://i2.51cto.com/images/blog/201712/18/36c673eba3839a9157a70c02bb403fbb.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)
-  调用存储过程

BEGIN 
	first_proc;
END;

-  调用存储函数

BEGIN
	DBMS_OUTPUT.put_line(first_func);
END;

# 2、存储过程的创建
## a、创建存储过程的语法

CREATE [OR REPLACE] PROCEDURE procedure_name [(argument1 [{ IN | OUT | IN OUT}] type,argument2 [ { IN | OUT | IN OUT}] type,...)] （IN 为输入参数、OUT输出参数、IN | OUT为输入输出参数） { IS | AS }（即使没有声明部分，IS | AS 也不能够省略，IS或者AS选择哪一个都可以） 声明部分，类型.变量的说明 BEGIN 执行部分 EXCEPTION 可选的异常错误处理部分 END;

## b、代码示例：创建输入参数的存储过程

CREATE OR REPLACE PROCEDURE proc1 
	(v_empno IN emp01.empno%TYPE)
	IS 
	BEGIN
		--根据员工号删除指定的员工信息
		DECLARE FROM emp01 WHERE empno = v_empno;
		--判断是否删除成功
		IF SQL%NOTFOUND THEN
			-- -20000 ~ -20999之间
			RAISE_APPLICATION_ERROR(-20008,'指定删除的员工不存在');
		ELSE
			DBMS_OUTPUT。put_line('删除成功');
	END;
```

• 在存储过程或者是存储函数中，一般不需要在其中添加COMMIT或ROLLBACK，谁调用由谁添加COMMIT或ROLLBACK

c、创建带有输出参数的存储过程

CREATE OR REPLACE PROCEDURE proc2
			(v_deptno IN NUMBER,v_avgsal OUT NUMBER,v_cnt out NUMBER)
			 IS
	BEGIN
			 SELECT AVG(sal),count(*)
			 INTO v_avgsal,v_cnt
			 FROM emp WHERE deptno = v_deptno;
	 EXCEPTION
		 WHEN NO_DATA_FOUND THEN
			 dbms_output.put_line('没有此部门');
		 WHEN OTHERS THEN
			 dbms_output.put_line(SQLERRM);
	 END;

d、创建带有输入输出参数的存储过程

CREATE OR REPLACE PROCEDURE proc3
	(v_num1 IN OUT NUMBER,v_num2 IN OUT NUMBER)
	AS
			 v_temp NUMBER := 0;
	BEGIN
			 v_temp := v_num1;
			 v_num1 := v_num2;
			 v_num2 := v_temp;
	END;

3、存储函数的创建

a、创建存储函数的语法

CREATE [OR REPLACE] FUNCTION function_name
	[ argument1[ { IN | OUT | IN OUT }] type,argument2[{IN | OUT | IN OUT }] type,...)]
	RETURN return_type
	{ IS | AS}
		声明部分，类型.变量的说明
	BEGIN
		执行部分，函数体
	EXCEPTION
		可选的异常错误处理部分
	END;

b、创建带有输入参数的存储函数

• 根据部门编号返回该部门的总工资：

CREATE OR REPLACE FUNCTION func1
	(v_deptno IN NUMBER)
	RETURN NUMBER
	IS
				 v_sumsal NUMBER;
	BEGIN
				 SELECT SUM(SAL) INTO v_sumsal FROM emp WHERE deptno = v_deptno;
				 RETURN v_sumsal;
	EXCEPTION
				 WHEN NO_DATA_FOUND THEN
					 DBMS_OUTPUT.put_line('没有此部门');
				 WHEN OTHERS THEN 
					 DBMS_OUTPUT.put_line(SQLERRM);
	END;

c、创建带有输出参数的存储函数

• 根据员工号输出员工的姓名和员工的工资，并且返回员工的年收入：

CREATE OR REPLACE FUNCTION func2
	(v_empno IN emp.empno%TYPE,v_name OUT emp.ename%TYPE,v_sal OUT emp.sal%TYPE)
	RETURN NUMBER
	IS 
			v_salsum NUMBER;
	BEGIN
			SELECT ename,sal,(sal + nvl(comm,0)) * 12 INTO v_name,v_sal,v_salsum
			FROM emp WHERE empno = v_empno;
			RETURN v_salsum;
	EXCEPTION
			WHEN NO_DATA_FOUND THEN
				DBMS_OUTPUT.put_line('没有此员工');
			WHEN OTHERS THEN 
				DBMS_OUTPUT.put_line(SQLERRM);
	END;

d、创建带有输入输出参数的存储函数

• 求两个数的平方和，并输出两个数的平方

CREATE OR REPLACE FUNCTION func3
	(n1 IN OUT NUMBER,n2 IN OUT NUMBER)
	RETURN NUMBER
	AS
	BEGIN
		n1 := n1*n1;
		n2 := n2*n2;
		RETURN n1+n2;
	END;

4、存储过程的调用和删除

a、调用存储过程

• 方法一：ORACLE使用EXECUTE语句来实现对存储过程的调用：（属于SQLPlus命令）

EXEC[UTE] Procedure_name(parameter1,parameter2...)

• 方法二：在PL/SQL代码中直接调用，如：

BEGIN
		procedure_name(parameter1,parameter2...)
END;

b、调用存储过程的不同情况

注意：本专题第2节存储过程的创建后，Oracle数据库中保存了4个存储过程。 - 调用无参数的存储过程：直接引用过程名即可。 - 调用带有输入参数的存储过程。 - 调用带有输出参数的参数过程。 - 调用带有输入输出参数的存储过程。

• SQLPlus环境：

-- 调用无参的存储过程
	SQL> set serveroutput on
	SQL> exec first_proc
	我的过程
	Hello Everyone!
	PL/SQL procedure successfully completed
-- 调用带有输入参数的存储过程
	SQL> exec proc1(1234);
	begin proc1(1234); end;
	ORA-20008: 指定删除的员工不存在
	ORA-06512: 在 "SCOTT.PROC1",line 9
	ORA-06512: 在 line 1

• PL/SQL环境：

-- 调用无参的存储过程
BEGIN
   first_proc;
END;
-- 调用带有输入参数的存储过程
BEGIN
  proc1(1234);
END;
-- 调用带有输出参数的存储过程
DECLARE
   v_avgsal NUMBER;
   v_count NUMBER;
BEGIN
   PROC2(10,v_avgsal,v_count);
   DBMS_OUTPUT.put_line('平均工资:' || v_avgsal);
   DBMS_OUTPUT.put_line('总人数：' || v_count);
END;
--调用带有输入输出参数的存储过程
DECLARE
  v_n1 NUMBER := 5;
  v_n2 NUMBER := 10;
BEGIN
  PROC3(v_n1,v_n2);
  DBMS_OUTPUT.put_line('N1:' || v_n1);
  DBMS_OUTPUT.put_line('N2:' || v_n2);
END;

c、删除存储过程的语法格式

• 使用DROP PROCEDURE命令，语法如下：

DROP PROCEDURE [user.] Procedure_name

5、存储函数的调用和删除

• 注意：本专题第3节存储函数的创建后，Oracle数据库中保存了4个存储函数。

a、调用存储函数

• 存储过程和存储函数的调用都是一样，可分为： 1、调用无参数的存储函数； 2、调用带有输入参数的存储函数； 3、调用带有输出参数的存储函数； 4、调用带有输入输出参数的存储函数。

• PL.SQL环境：

--调用无参数的函数
BEGIN
  dbms_output.put_line(first_func);
END;
-- 调用带有输入参数的函数
BEGIN
  dbms_output.put_line('部门的工资总额：' || func1(&no));
END;
-- 调用带有输出参数的函数
DECLARE
   v_name emp.ename%TYPE;
   v_sal emp.sal%TYPE;
   v_salsum NUMBER;
BEGIN
  v_salsum := func2(&no,v_name,v_sal);
  DBMS_OUTPUT.put_line('姓名：' || v_name);
  DBMS_OUTPUT.put_line('工资：' || v_sal);
  DBMS_OUTPUT.put_line('年收入：' || v_salsum);
END;
-- 调用带有输入输出参数的函数
DECLARE
   v_n1 NUMBER := 5;
   v_n2 NUMBER := 6;
   v_sum number;
BEGIN
  v_sum := func3(v_n1,v_n2);
  DBMS_OUTPUT.put_line('n1的平方：' || v_n1);
  DBMS_OUTPUT.put_line('n2的平方：' || v_n2);
  DBMS_OUTPUT.put_line('n1和n2的平方和：' || v_sum);
END;

b、删除函数过程

• 可以使用DROP FUNCTION命令，语法如下：

DROP FUNCTION [user.] Function_name

6、附加说明

• 参数默认值的问题：
• 过程和函数都可以在声明过程或者函数参数时，使用DEFAULT关键字为输入参数指定默认值。
• 示例代码：求部门的年收入。（存储函数）

CREATE OR REPLACE FUNCTION func2
	(v_empno IN emp.empno%TYPE,v_salsum
			FROM emp WHERE empno = v_empno;
			RETURN v_salsum;
	EXCEPTION
			WHEN NO_DATA_FOUND THEN
				DBMS_OUTPUT.put_line('没有此员工');
			WHEN OTHERS THEN 
				DBMS_OUTPUT.put_line(SQLERRM);
	END;

• 调用此存储函数：

--调用
	DECLARE
		v_totalsal NUMBER;
	BEGIN
		v_totalsal := func5;
		DBMS_OUTPUT.put_line(v_totalsal);
	END;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!