oracle 存储过程和函数的调用方法

一.存储过程(PROCEDURE)
使用过程, 不仅可以简化客户端应用程序的开发和维护,而且可以提高应用程序的运行性能.
CREATE [OR REPLACE] PROCUDURE 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关键字.
一般使用execute(或exec) 过程名 或者 call 过程名来调用过程.
set serveroutput on打开oracle的输出.
(1)不带参数的过程
CREATE OR REPLACE PROCEDURE out_time
IS
BEGIN   
dbms_output.put_line(systimestamp);
END;

call out_time();或者 exec out_time;
(2)带IN参数的过程
如果不指定参数模式,则默认的为IN,也可以显示的指定输入模式IN.
CREATE OR REPLACE PROCEDURE add_employee
(eno NUMBER,name VARCHAR2,sal NUMBER,job VARCHAR2 DEFAULT 'CCC', dno NUMBER)
IS
BEGIN
INSERT INTO emp(empno, ename,sal,job,deptno)
VALUES(eno,ename,sal,job,dno);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20000, '雇员不能重复');
END;
除了默认值的参数外,其余的都要输入.
(3)带OUT的过程
在过程中输出结果是使用OUT或者IN OUT来完成的.
定义输出参数的时间,必须要使用OUT来定义输出.
CREATE OR REPLACE PROCEDURE query_employee
(eno NUMBER,name OUT VARCHAR2,sal OUT NUMBER)
IS
BEGIN
SELECT ename, sal INTO name, sal FROM emp WHERE empno=eno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, '雇员不存在');
END;
对于指定为OUT类型的参数,必须定义变量接收输出参数的数据.调用实例:
SQL> var name VARCHAR2(10)
SQL> var sal NUMBER
SQL> exec query_employee(77,:name,:sal)
SQL> print name sal
(4)带IN和OUT的过程
在调用前要通过变量给参数传递数据,在调用结束后,会通过此变量将值传递给应用程序
CREATE OR PROCEDURE compute
(num IN OUT NUMBER, num2 IN OUT NUMBER)
IS
v1 NUMBER;
v2 NUMBER;
BEGIN
v1:=num1/num2;
v2:=MOD(num1, num2);
num1 := v1;
num2 := v2;
END;
在应用程序调用IN OUT的存储过程时,必须提供两个变量临时存放数值,运行如下:
SQL> var n1 NUMBER
SQL> var n2 NUMBER
SQL> exec :n1:=100
SQL> exec :n2:=30
SQL> exec compute(:n1, :n2)
SQL> PRINT n1 n2
(5)为参数传递变量和数据
[1]位置传递
如 SQL> exec add_dept(50, 'SALES', 'NEW YORK')
[2]名称传递,使用=>符号来指定值
如 SQL> exec add_dept(dno=>50)
[3]组合传递,以上两种传递方法交替
(6)查看过程的源代码
过程名,源代码,执行代码放到了数据字典中,通过查询数据字典USER_SOURCE,可以显示当前用户定义的过程
SELECT text FROM user_source WHERE name='ADD_DEPT';
(7)删除过程
DROP PROCEDURE 过程名

二.函数(FUNCTION)
CREATE [OR REPLACE] FUNCTION function_name
(arg1 [model1] datatype1,arg2 [model2] datatype2)
RETURN datatype
IS|AS
PL/SQL Block;
arg1,arg2指定函数的参数,当不指定参数数据类型时,不能指定其长度
RETURN 指定函数返回的数据类型.注意,函数前面必须要有RETURN子句.在函数体内至少含有一条RETURN子句.函数的参数MODEl和过程的一样
(1)不带参数的函数
CREATE OR REPLACE FUNCTION get_user
RETURN VARCHAR2
IS
v_user VARCHAR2(100);
BEGIN
SELECT username INTO v_user FROM user_users;
RETURN v_user;
END;
调用方法
SQL> var v1 VARCHAR2(100)
SQL> exec :v1:=get_user
SQL> PRINT v1
(2)带IN参数
CREATE OR REPLACE FUNCTION get_sal(name IN VARCHAR2)
RETURN NUMBER
IS
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp
WHERE upper(ename)=upper(name);
RETURN v_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise-application_error(-20000,'该雇员不存在');
END;
调用方法如下:
SQL> var sal NUMBER
SQL> exec :sal:=get_sal('scott')
SQL> print sal
(3)带OUT参数
一般情况下,函数只需要单个返回数据,如果希望使用函数同事返回多个数据,就需要用到输出参数了.
CREATE OR REPLACR FUNCTION get_info
(name VARCHAR2, title OUT VARCHAR2)
RETURN VARCHAR2
AS
deptname dept.dname%TYPE;
BEGIN
SELECT a.job,b.dname INTO title, deptname
FROM emp a, dept b
WHERE a.deptno = b.deptno
AND upper(a.ename) = upper(a.ename)
RETURN deptname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_erro(-20000, '雇员不存在');
END;
由于此函数带有OUT参数,所以要定义变量接收OUT参数和函数的返回值
SQL> var job varchar2(20)
SQL> var dname varchar2920)
SQL> exec :dname:=getinfo('scott',:job)
SQL> print dname job
(4)带IN OUT参数
在调用函数之前需要通过变量给该种参数传递数据
CREATE OR REPLACE FUNCTION result
(num1 NUMBER, num2 IN OUT NUMBER)
RETURN NUMBER
AS
v_result NUMBER(6);
v_remain NUMBER;
BEGIN
v_result:=num1/num2;
v_remain:=MOD(num1, num2);
num2 := v_remain;
RETURN v_result;
EXCEPTION
WHEN ZERO_DIVIDE THEN
raise_application_error(-20000, '不能除0');
END;
执行如下:
SQL> var result1 NUMBER
SQL> var result2 NUMBER
SQL> exec :result2:=30
SQL> exec :result1:=result(100, :result2)
SQL> print result1 result2
(5)函数的删除
DROP FUNCTION 函数名

三.子程序的管理
列出当前用户的子程序
数据字典视图USER_OBJECTS显示当前用户所包含的所有对象.可以列出用户的表,视图,索引,也可以列出用户的过程,函数等.
SELECT object_name, created, status FROM user_object2 WHERE object_type IN ('PROCEDURE', 'FUNCTION');