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;

测试结果如下：

例三：

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

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

测试结果如下：