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(‘¹ÍÔ±×ÜÈËÊýΪ£º’||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(‘雇员’||V_ENAME||’的工资被改为’||TO_CHAR(V_SAL+P_RAISE));
- COMMIT;
- EXCEPTION
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE(‘发生错误，修改失败！’);
- 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;
测试结果如下：
