Oracle存储过程及调用

一.存储过程(PROCEDURE) 

CREATE [OR REPLACE] PROCEDURE procedure_name
(arg1 [model1] datatype1, arg2[model2] datatype2)
IS [AS]
PL/SQL Block;

arg1,arg2用于指定过程的参数,IS/AS用于开始一个PL/SQL块.当指定参数数据类型时,不能指定其长度.

在建立过程的时,既可以指定输入参数(IN),也可以指定输出参数(OUT)以及输入输出参数(IN OUT).

如果不定义参数模式,则默认为输入参数,

如果要定义输出参数,则需要指定OUT关键字,

如果定义输入输出参数,要指定IN OUT关键字.

set serveroutput on打开oracle的输出.

示例一:不带参数的过程

CREATE OR REPLACE PROCEDURE out_time--存储过程没有参数时，在存储过程名字后面不能有括号
IS--声明不使用DECLARE关键字
BEGIN   
dbms_output.put_line(systimestamp);
END;

调用如下： 

begin
out_time;
end;
或者
call out_time();
或者
SQL>exec out_time;--exec是sqlplus命令，只能在sqlplus命令窗口中使用；call为SQL命令，没有限制

注意：存储过程没有参数时,exec可以直接跟过程名（可以省略()），但call则必须带上(). 
示例二:带IN参数的过程

CREATE OR REPLACE PROCEDURE getSalary(eNo NUMBER)  --参数的数据类型不能指定长度
AS
salary emp.sal%TYPE;--salary数据类型和sal相同
BEGIN
SELECT SAL INTO salary  FROM EMP WHERE EMPNO=eNo;
DBMS_OUTPUT.PUT_LINE(eNo||'号员工的工资为'||salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
END;

调用如下：

BEGIN
getSalary(7788);
END;
或者
SQL> EXEC getSalary(7788); --exec是sqlplus命令，只能在sqlplus命令窗口中使用；还可以使用名称传递,使用=>符号来指定值 
如 SQL> exec getSalary(dno=>50)
CALL getSalary(7788);

示例三:创建含有输入和输出参数的存储过程

CREATE OR REPLACE PROCEDURE getSalary(eNo IN NUMBER,salary OUT NUMBER)
AS
BEGIN
SELECT SAL INTO salary  FROM EMP WHERE EMPNO=eNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
END;

当过程中含有输出参数时，调用时必须通过BEGIN  END块，不能通过EXEC或CALL调用。如：

DECLARE
salary NUMBER(7,2);
BEGIN
getSalary(7788,salary);
DBMS_OUTPUT.PUT_LINE(salary);
END;

或者必须定义变量接收输出参数的数据.调用实例:

SQL> var sal NUMBER;
SQL> exec query_employee(77,:sal);

示例四:创建参数类型既是输入参数也是输出参数的过程

CREATE OR REPLACE PROCEDURE getSalary(noSalary IN OUT NUMBER)
AS
BEGIN
SELECT SAL INTO noSalary FROM EMP WHERE EMPNO=noSalary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
END;

调用如下：

DECLARE
no NUMBER(7,2);
BEGIN
no:=7788;
getSalary(no);
DBMS_OUTPUT.PUT_LINE(no);
END;

二、维护过程
1、删除存储过程

DROP PROCEDURE Proc_Name;

2、查看过程状态

SELECT object_name,status  FROM USER_OBJECTS WHERE object_type='PROCEDURE';

3、重新编译过程

ALTER PROCEDURE Proc_Name COMPILE;

4、查看过程代码

SELECT * FROM USER_SOURCE WHERE TYPE='PROCEDURE';