oracle 存储过程

创建和删除存储过程

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

 

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

 

 

其中:

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

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

例一:

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

 

  1. CREATE OR REPLACE PROCEDURE EMP_COUNT
  2. AS
  3. V_TOTAL NUMBER(10);
  4. BEGIN
  5.  SELECT COUNT(*) INTO V_TOTAL FROM EMP;
  6.  DBMS_OUTPUT.PUT_LINE(‘¹ÍÔ±×ÜÈËÊýΪ£º’||V_TOTAL);
  7. END;

测试:

 

step1:在PlSql中找到Procedures,右击,如下图:


step2:单击Test,进入下图:

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

说明:

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

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

例二:

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

 

  1. CREATE OR REPLACE PROCEDURE EMP_LIST
  2.         AS
  3.          CURSOR emp_cursor IS
  4.         SELECT empno,ename FROM emp;
  5.         BEGIN
  6. FOR Emp_record IN emp_cursor LOOP
  7.     DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);
  8.         END LOOP;
  9.         EMP_COUNT;
  10.         END;

测试过程与例一一样,结果如下:

 


说明:

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

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

 

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

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

 

例一:

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

 

  1. CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)
  2.         AS
  3.          V_ENAME VARCHAR2(10);
  4. V_SAL NUMBER(5);
  5.         BEGIN
  6.         SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO;
  7.          UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO;
  8.          DBMS_OUTPUT.PUT_LINE(‘雇员’||V_ENAME||’的工资被改为’||TO_CHAR(V_SAL+P_RAISE));
  9. COMMIT;
  10.         EXCEPTION
  11.          WHEN OTHERS THEN
  12.         DBMS_OUTPUT.PUT_LINE(‘发生错误,修改失败!’);
  13.         ROLLBACK;
  14.         END;

测试过程如下:

 


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

例二:

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

 

  1. CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)
  2.         AS
  3.         BEGIN
  4.         SELECT COUNT(*) INTO P_TOTAL FROM EMP;
  5.         END;

测试结果如下:

 

 

 

标签