oracle存储过程

创建存储过程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建一个存储过程的基本语句如下：

CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)] 
{AS|IS} 
[说明部分] 
BEGIN 
可执行部分 
[EXCEPTION 
错误处理部分] 
END [过程名]; 

其中：

可选关键字ORREPLACE 表示如果存储过程已经存在，则用新的存储过程覆盖，通常用于存储过程的重建。

参数部分用于定义多个参数(如果没有参数，就可以省略)。参数有三种形式：IN、OUT和IN OUT。如果没有指明参数的形式，则默认为IN。
关键字AS也可以写成IS，后跟过程的说明部分，可以在此定义过程的局部变量。

例一：

创建一个显示雇员总人数的存储过程。

CREATE OR REPLACE PROCEDURE EMP_COUNT
AS
V_TOTAL NUMBER(10);
BEGIN
 SELECT COUNT(*) INTO V_TOTAL FROM EMP;
 DBMS_OUTPUT.PUT_LINE(&＃39;1í?±×üè?êy?a￡o&＃39;||V_TOTAL);
END;

测试：

step1：在PlSql中找到Procedures，右击，如下图：

step2：单击Test，进入下图：

step3：单击执行，在DBMS Output中可以看到执行结果，如下图：

说明：

在该例子中，V_TOTAL变量是存储过程定义的局部变量，用于接收查询到的雇员总人数。
注意：

如果在存储过程中引用了其他用户的对象，比如表，则必须有其他用户授予的对象访问权限。一个存储过程一旦编译成功，就可以由其他用户或程序来引用。但存储过程或函数的所有者必须授予其他用户执行该过程的权限。
存储过程没有参数，在调用时，直接写过程名即可。

例二：

编写显示雇员信息的存储过程EMP_LIST，并引用EMP_COUNT存储过程。

 

	CREATE OR REPLACE PROCEDURE EMP_LIST   
	        AS  
	         CURSOR emp_cursor IS    
	        SELECT empno,ename FROM emp;   
	        BEGIN  
	FOR Emp_record IN emp_cursor LOOP      
	    DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);   
	        END LOOP;   
	        EMP_COUNT;   
	        END; 

测试过程与例一一样，结果如下：

说明：

以上的EMP_LIST存储过程中定义并使用了游标，用来循环显示所有雇员的信息。然后调用已经成功编译的存储过程EMP_COUNT，用来附加显示雇员总人数。通过EXECUTE命令来执行EMP_LIST存储过程。

参数传递
参数的作用是向存储过程传递数据，或从存储过程获得返回结果。正确的使用参数可以大大增加存储过程的灵活性和通用性。
参数的类型有三种，如下所示。

IN  定义一个输入参数变量，用于传递参数给存储过程  
OUT 定义一个输出参数变量，用于从存储过程获取数据  
IN OUT  定义一个输入、输出参数变量，兼有以上两者的功能  

参数的定义形式和作用如下：
参数名 IN 数据类型 DEFAULT 值；
定义一个输入参数变量，用于传递参数给存储过程。在调用存储过程时，主程序的实际参数可以是常量、有值变量或表达式等。DEFAULT 关键字为可选项，用来设定参数的默认值。如果在调用存储过程时不指明参数，则参数变量取默认值。在存储过程中，输入变量接收主程序传递的值，但不能对其进行赋值。
参数名 OUT 数据类型；
定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。
在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。在存储过程中，参数变量只能被赋值而不能将其用于赋值，在存储过程中必须给输出变量至少赋值一次。
参数名 IN OUT 数据类型 DEFAULT 值；
定义一个输入、输出参数变量，兼有以上两者的功能。在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。DEFAULT 关键字为可选项，用来设定参数的默认值。在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。
如果省略IN、OUT或IN OUT，则默认模式是IN。

例一：

编写给雇员增加工资的存储过程CHANGE_SALARY，通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。 

CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)
		AS
		 V_ENAME VARCHAR2(10);
V_SAL NUMBER(5);
		BEGIN
 		SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO;
		 UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO;
		 DBMS_OUTPUT.PUT_LINE(&＃39;雇员&＃39;||V_ENAME||&＃39;的工资被改为&＃39;||TO_CHAR(V_SAL+P_RAISE));
COMMIT;
		EXCEPTION
		 WHEN OTHERS THEN
 		DBMS_OUTPUT.PUT_LINE(&＃39;发生错误，修改失败！&＃39;);
 		ROLLBACK;
		END;

测试过程如下： 

说明：从执行结果可以看到，雇员SCOTT的工资已由原来的3000改为3080。

例二：

使用OUT类型的参数返回存储过程的结果。

CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)
		AS
		BEGIN
		SELECT COUNT(*) INTO P_TOTAL FROM EMP;
		END;

测试结果如下： 

例三：

使用IN OUT类型的参数，给电话号码增加区码。

CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2)
		AS
		BEGIN
		 P_HPONE_NUM:=&＃39;0755-&＃39;||P_HPONE_NUM;
		END;

测试结果如下：