oracle讲解_存储过程的调用.docx

一.存储过程(PROCEDURE)使用过程, 不仅可以简化客户端应用程序的开发和维护,而且可以提高应用程序的运行性能.CREATE OR REPLACE PROCUDURE procedure_name(arg1 model1 datatype1, arg2model2 datatype2)IS ASPL/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_timeISBEGIN 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)ISBEGININSERT INTO emp(empno, ename,sal,job,deptno)VALUES(eno,ename,sal,job,dno);EXCEPTIONWHEN DUP_VAL_ON_INDEX THENRAISE_APPLICATION_ERROR(-20000, 雇员不能重复);END;除了默认值的参数外,其余的都要输入.(3)带OUT的过程在过程中输出结果是使用OUT或者IN OUT来完成的.定义输出参数的时间,必须要使用OUT来定义输出.CREATE OR REPLACE PROCEDURE query_employee(eno NUMBER,name OUT VARCHAR2,sal OUT NUMBER)ISBEGINSELECT ename, sal INTO name, sal FROM emp WHERE empno=eno;EXCEPTIONWHEN NO_DATA_FOUND THENRAISE_APPLICATION_ERROR(-20000, 雇员不存在);END;对于指定为OUT类型的参数,必须定义变量接收输出参数的数据.调用实例:SQL var name VARCHAR2(10)SQL var sal NUMBERSQL 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)ISv1 NUMBER;v2 NUMBER;BEGINv1:=num1/num2;v2:=MOD(num1, num2);num1 := v1;num2 := v2;END;在应用程序调用IN OUT的存储过程时,必须提供两个变量临时存放数值,运行如下:SQL var n1 NUMBERSQL var n2 NUMBERSQL exec :n1:=100SQL exec :n2:=30SQL 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 datatypeIS|ASPL/SQL Block;arg1,arg2指定函数的参数,当不指定参数数据类型时,不能指定其长度RETURN 指定函数返回的数据类型.注意,函数前面必须要有RETURN子句.在函数体内至少含有一条RETURN子句.函数的参数MODEl和过程的一样(1)不带参数的函数CREATE OR REPLACE FUNCTION get_userRETURN VARCHAR2ISv_user VARCHAR2(100);BEGINSELECT username INTO v_user FROM user_users;RETURN v_user;END;调用方法SQL var v1 VARCHAR2(100)SQL exec :v1:=get_userSQL PRINT v1(2)带IN参数CREATE OR REPLACE FUNCTION get_sal(name IN VARCHAR2)RETURN NUMBERISv_sal emp.sal%TYPE;BEGINSELECT sal INTO v_sal FROM empWHERE upper(ename)=upper(name);RETURN v_sal;EXCEPTIONWHEN NO_DATA_FOUND THENraise-application_error(-20000,该雇员不存在);END;调用方法如下:SQL var sal NUMBERSQL exec :sal:=get_sal(scott)SQL print sal(3)带OUT参数一般情况下,函数只需要单个返回数据,如果希望使用函数同事返回多个数据,就需要用到输出参数了.CREATE OR REPLACR FUNCTION get_info(name VARCHAR2, title OUT VARCHAR2)RETURN VARCHAR2ASdeptname dept.dname%TYPE;BEGINSELECT a.job,b.dname INTO title, deptnameFROM emp a, dept bWHERE a.deptno = b.deptnoAND upper(a.ename) = upper(a.ename)RETURN deptname;EXCEPTIONWHEN NO_DATA_FOUND THENraise_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 NUMBERASv_result NUMBER(6);v_remain NUMBER;BEGINv_result:=num1/num2;v_remain:=MOD(num1, num2);num2 := v_remain;RETURN v_result;EXCEPTIONWHEN ZERO_DIVIDE THENraise_application_error(-20000, 不能除0);END;执行如下:SQL var result1 NUMBERSQL var result2 NUMBERSQL exec :result2:=30SQL exec :result1:=result(100, :result2)SQL print result1 result2(5)函数的删除DROP