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');