oracle存储过程学习经典[语法+实例+调用]

1、Oracle存储过程学习目录Oracle存储过程1Oracle存储过程基础知识 1Oracle存储过程的基本语法 2关于Oracle存储过程的若干问题备忘 41. 在 Oracle中，数据表别名不能加 as。 42. 在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。 53. 在利用select.nto.语法时，必须先确保数据库中有该条记录， 否则会报出"no datafound"异常。54. 在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错55. 在存储过程中，关于出现null的问

2、题56. Hibernate 调用 Oracle 存储过程6用 Java调用 Oracle 存储过程总结 6一、无返回值的存储过程6二、有返回值的存储过程(非列表) 8三、返回列表9在存储过程中做简单动态查询 11一、本地动态SQL 12二、使用 DBMS_SQL包13Oracle存储过程调用 Java方法16Oracle高效分页存储过程实例 17Oracle存储过程基础知识商业规则和业务逻辑可以通过程序存储在Oracle中，这个程序就是 存储过程。存储过程是SQL,PL/SQL,Java语句的组合，它使你能将执行商业规则的代码从你的应用 程序中移动到数据库。这样的结果就是，代码存储一次但是能

3、够被多个程序使用。要创建一个过程对象(procedural object)，必须有CREATEPROCEDURE系统权限。如果 这个过程对象需要被其他的用户schema使用，那么你必须有CREATEKNYPROCEDURE权限。执行 procedure的时候，可能需要 excute权限。或者 EXCUTEANYPROCEDURE权限。 如果单独赋予权限，如下例所示：grant execute on MY_PROCEDUREto Jelly调用一个存储过程的例子：execute MY_PROCEDUREQNE PARAMETER');存储过程(PROCEDURE和函数(FUNCTION)

4、的区别。function有返回值，并且可以直接在Query中引用function和或者使用function的返回值。本质上没有区别，都是PL/SQL程序，都可以有返回值。最根本的区别是：存储过程是命令，而函数是表达式的一部分。比如：select max(NAME) FROM但是不能execmax(NAME)如果此时max是函数。PACKAGE是 function， procedure， variables 和 sql 语句的组合。package 允许多 个procedure使用同一个变量和游标。仓U建 procedure的语法:CREATE OR REPLACE PROCEDURE schem

5、cedure(argumentIN | OUT | IN OUT NO COPY datatype,argumentIN | OUT | IN OUT NO COPY datatype.)authid curre nt_user| defi neris |as pl/sql_subprogram_body1language javaname 'String' | c name, name librarylib_nameSql代码：CREATE PROCEDURE sam.credit(acc_noIN NUMBER, amount IN NUMBER) ASBEGIN

6、UPDATE accountsSET balanee= balanee + amountWHERE account_id= acc_no;END;可以使用 create or replace procedure语句，这个语句的用处在于，你之前赋予的excute权限都将被保留。IN, OUT,IN OUT用来修饰参数。IN表示这个变量必须被调用者赋值然后传入到PROCEDURES行处理。OUT表示PRCEDURE通过这个变量将值传回给调用者。IN OUT则是这两种的组合。authid代表两种权限：定义者权限(difiner right默认),执行者权限(invoker right)。定义者权限说

7、明这个 procedure中涉及的表，视图等对象所需要的权限只要定义者拥有 权限的话就可以访问。执行者权限则需要调用这个procedure的用户拥有相关表和对象的权限。Oracle存储过程的基本语法1. 基本结构CREATERREPLACEROCEDURE存 储过程名字(参数 1 IN NUMBER,参数 2 IN NUMBER）AS变量 1 INTEGER=O;变量2 DATE;BEGINEND存储过程名字2. SELECTNTO STATEMENT将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 记录，否则抛出异常（如果没有记录抛出 NO_DATA_FOUN

8、D）例子：BEGINSELEC1bol1,col2 into 变量 1,变量 2 FROM typestruct where xxx;EXCEPTIONWHEN NO_DATA_FOUNDTHENxxxx;END;3. IF判断IFV_TEST=1THENBEGINdo something END;ENDIF;4. while 循环WHILEV_TEST=1LOOPBEGINXXXXEND;ENDLOOP;5 变量赋值V TEST:=123;6. 用 for in 使用 cursorISCURSOFcur ISSELECT FROM xxx;BEGINFORcur_result in cur

9、LOOPBEGINV_SUM :=cur_result.列名 1+cur_result.列名 2 END;ENDLOOP;END;7. 带参数的 cursorCURSOFC_USER(C_IDNUMBER)ISSELECNAME FROMUSERWHERETYPEID=C_ID;OPENC_USER变量值);LOOPFETCHC_USERNTOV_NAME;EXITFETCHC_USER%NOTFOUND;do somethingENDLOOP;CLOSEP_USER;8. 用 pl/sql developer debug连接数据库后建立一个 Test WINDOW在窗口输入调用 SP的代码,

10、F9开始debug,CTRL+N单步调试9. Pl/Sql中执行存储过程在 sql*plus 中：declare-必要的变量声明，视你的过程而定beginexecute yourprocudure(parameter1,parameter2,.);end/在SQL/PLUS中调用存储过程，显示结果：SQL>set serveoutput on-打开输出SQL>var info'n umber;-输出1SQL>var info2 number;-输出2SQL>declarevarlvarchar2(20);-输入1var2varchar2(20);-输入2var3

11、varchar2(20);-输入2BEGINpro(var1,var2,var3,:i nfo1,:i nfo2);END;/SQL>pri nt in fo1;SQL>pri nt in fo2;注：在EXECUTEMMEDIATE STR语句是 SQLPLUS动态执行语句，它在执行中会自动提交，类似于DP中FORMS_DDL语句，在此语句中str是不能换行的，只能通过连接字符"|"， 或着在在换行时加上"-"连接字符。关于Oracle存储过程的若干问题备忘1. 在Oracle中，数据表别名不能加as。如:select a.appname

12、from appinfo a;-正确select a.appname from appinfo as a;-错误也许，是怕和 Oracle中的存储过程中的关键字as冲突的问题吧2. 在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。select af.keynode into knfrom APPFOUNDATIONbfwhere af.appid=aid and af.foundationid=fid;-有 into，正确编译select af.keynodefrom APPFOUNDATIONhfwhere af.appid=a

13、id and af.foundationid=fid;- 没 有 into ， 编译 报错， 提示： Compilation Error: PLS-00428:an INTO clause is expected in this SELECstatement3. 在利用o.语法时，必须先确保数据库中有该条 记录，否则会报出"no data found"异常。可以在该语法之前，先利用select count(*) from查看数据库中是否存在该记录，如果存在，再利用 o.4. 在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，

14、但在运行阶段会报错select key node into kn from APPFOUNDATIONvhere appid=aid and fou ndatio ni d=fid;-正确运行select af.keynode into kn from APPFOUNDATIONaf where af.appid=appid and af.foundationid=foun dati onid;-运行阶段报错，提示：ORA-01422:exact fetch returns more than requested number of rows5. 在存储过程中，关于出现null的问题假设有一个

15、表A,定义如下：create table A(id varchar2(50) primary key not null,vcount number(8) not nuII,bid varchar2(50) not null -夕卜键);如果在存储过程中，使用如下语句：select sum(vco unt) in to fcou nt from A where bid='xxxxxx'如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值， 如：fcount number(8):=0依然无效，fcount还

16、是会变成null)，这样以后使用fcount时就可能 有问题，所以在这里最好先判断一下：if fcount is null then fcoun t:=0;end if;这样就一切ok 了。6. Hibernate 调用Oracle存储过程this .pn umberMa nager.getHiber nateTemplate().execute(new HibernateCallback().public ObjectdoInH iber nate(Sessi onsessi on)throwsHiber nateExcepti on,SQLExcepti on.CallableStatem

17、e ntcs = sessi on.conn ecti on().prepareCall("callmodifyappp number_rema in( ?)");cs.setStri ng(1,foun dati oni d);cs.execute();return n ull ;);用Java调用Oracle存储过程总结无返回值的存储过程测试表：-Create tablecreate table TESTTB (ID VARCHAR2(30),NAME VARCHAR2(30)tablespaceBOMpctfree10in itra ns1maxtra ns255st

18、orage(in itial 64Kmi nexte nts1maxexte ntsun limited)；例：存储过程为(当然了，这就先要求要建张表TESTTE里面两个字段(l_ID, l_NAME)。)：CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2, PARA2 IN VARCHAR2)ASBEGININSERT INTO BOM.TESTTB(ID,END TESTA;NAME) VALUES (PARA1, PARA2);在Java里调用时就用下面的代码：packagecom. yimin cedure.test;im

19、portjava.sql.CallableStateme nt;importjava.sql.C onn ecti on;import java.sql.DriverMa nager;import java.sql.ResultSet;importjava.sql.SQLExcepti on;importjava.sql.Stateme nt;public class TestProcedureDemo1 public TestProcedureDemo1()static voidpublicStr ingdriverStri ngstrUrlma in (Str in gargs) =&qu

20、ot;Oracle.jdbc.driver.OracleDriver"="jdbc:Oracle:thi n: 0:1521:vasms"Stateme ntstmt= n ull;ResultSetrs = n ull;Conn ecti onconn = n ull;CallableStateme ntproc = n ull;try Class.forName(driver);conn = DriverMa nager.getC onn ectio n(strUrl,"bom", "bom");pr

21、oc = conn .prepareCall("call BOM.TESTA(?,?) ");proc.setStr in g(1,"100");proc.setStri ng(2,"Test On e");proc.execute(); catch(SQLExceptionex2) ex2.pri ntStackTrace(); catch (Exception ex2) ex2.pri ntStackTrace(); fin ally try if (rs != n ull) rs.close();if (stmt !=n ull

22、)stmt.close();if (conn 匸n ull)conn .close(); catch (SQLException ex1) 有返回值的存储过程（非列表）例：存储过程为:在Java里调用时就用下面的代码:package cedure.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java

23、.sql.Statement;import java.sql.Types;public classTestProcedureDemo2 public static void main(String args) String driver = "Oracle.jdbc.driver.OracleDriver"String strUrl = "jdbc:Oracle:thin:0:1521:vasms"Statement stmt = null;ResultSetrs = null;Connection conn = null;Calla

24、bleStatement proc = null;try Class.forName(driver);conn = DriverManager.getConnection(strUrl, "bom", "bom"); proc =conn.prepareCall(" call BOM.TESTB(?,?"); proc.setString(1, "100");proc.registerOutParameter(2, Types.VARCHAR); proc.execute();String testPrint =

25、proc.getString(2);System.out.pri ntln ("=testPri nt=is=" + testPri nt); catch (SQLException ex2) ex2.pri ntStackTrace(); catch (Exception ex2) ex2.pri ntStackTrace();finally try if (rs != null) rs.close();if (stmt != null) stmt.close();if (conn != null) conn .close(); catch (SQLExceptionex

26、1) 注意，这里的proc.getString(2)中的数值2并非任意的，而是和存储过程中的 out列对应 的，如果out是在第一个位置，那就是proc.getStri ng(1)，如果是第三个位置，就是 proc.getString(3),当然也可以同时有多个返回值，那就是再多加几个out参数了。三、返回列表out参数来替代的，列表pagkage 了 .所以要分两由于Oracle存储过程没有返回值，它的所有返回值都是通过 同样也不例外，但由于是集合，所以不能用一般的参数，必须要用 部分，1.建一个程序包。如下：CREATE OR REPLACE PACKAGE TESTPACKAGE AS

27、TYPE TEST_CURSOR IS REF CURSOR;end TESTPACKAGE;2.建立存储过程，存储过程为：CREATE OR REPLACE PROCEDURE TESTC(P_CURSOR outTESTPACKAGE.TEST_CURSOR) ISBEGINOPEN P CURSOR FORSELECT * FROM BOM.TESTTB;END TESTC;可以看到，它是把游标(可以理解为一个指针),作为一个out参数来返回值的。在Java里调用时就用下面的代码：在这里要注意，在执行前一定要先把 Oracle的驱动包放到class路径里，否则会报错的。packageco

28、m. yimin cedure.test;import java.sql.CallableStateme nt;importjava.sql.C onn ecti on;importjava.sql.DriverMa nager;import java.sql.ResultSet;import java.sql.SQLExcepti on;importjava.sql.Stateme nt;public class TestProcedureDemo3 public static voidmain( Str in gargs) Stri ngdriver= "Oracle.

29、jdbc.driver.OracleDriver"Str ingstrUrl= "jdbc:Oracle:thi n: 0:1521:vasms"Stateme ntstmt= n ull;ResultSetrs = n ull;Conn ecti onconn = n ull;CallableStateme ntproc = n ull;try Class.forName(driver);conn = DriverMa nager.getC onn ectio n(strUrl,"bom", "bom"

30、);proc = conn .prepareCall("call bom.testc(?) ");proc.registerOutParameter(1,Oracle.jdbc.OracleTypes.CURSOR);proc.execute();rs = (ResultSet) proc.getObject(1);while(rs. next()System.out.pr intln(" <tr><td>"+ rs.getStri ng(1)+"</td><td>"+ rs.getSt

31、ri ng(2)+ "</td>v/tr>"); catch (SQLException ex2) ex2.pri ntStackTrace(); catch (Exception ex2) ex2.pri ntStackTrace(); fin allytry if (rs !=n ull)rs.close();if (stmt !=n ull)stmt.close();if (conn 匸n ull)conn .close(); catch(SQLExceptionex1) 在存储过程中做简单动态查询在存储过程中做简单动态查询代码，例如:CREATE&

32、gt;RREPLACE)rocedure ZXM_SB_GZ_GET(p_table in varchar2,p_name in varchar2,p_value in varchar2, outpara out Intxdba.zxm_pag_cs_power.c_type)asbegindeclarewherevalue varchar2(200);beginwherevalue:=select * from |p_table| where |p_name|=|p_value; open outpara for wherevalue;en d;en d;般的PL/SQL程序设计中，在 DM

33、L和事务控制的语句中可以直接使用SQL,但是DDL语句及系统控制语句却不能在PL/SQL中直接使用，要想实现在PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现。首先我们应该了解什么是动态SQL，在Oracle数据库开发PL/SQL块中我们使用的 SQL分为：静态SQL语句和动态SQL语句。所谓静态 SQL指在PL/SQL块中使用的SQL语句在编 译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该

34、语句。Oracle中动态SQL可以通过本地动态 SQL来执行，也可以通过DBMS_SQL包来执行。下面就 这两种情况分别进行说明：本地动态SQL本地动态 SQL是使用EXECUTEMMEDIATE语句来实现的。1、本地动态 SQL执行DDL语句:需求：根据用户输入的表名及字段名等参数动态建表。create or replace procedure proc_test(table_name in varchar2,-表名field1 in varchar2,-字段名datatype1 in varchar2,-字段类型field2 in varchar2,-字段名datatype2 in var

35、char2-字段类型)asstr_sql varchar2(500);begintable ' |table_name|' ( ' |fiWld1|datatyp'1|execute immediate str_sql;-动态执行 DDL 语句 excepti onwhen others thenn ull;end ;str_sql:=' c'|feld|2atatype'2|')reate以上是编译通过的存储过程代码。下面执行存储过程动态建表。SQL>execute proc_test( ' dinya_test

36、' , ' idnot nulInumjber(8ame' , ' varchar2(100)');PL/SQLprocedure successfully completedSQL>desc diny a_test;Name Type Nullable Default CommentsID NUMBER(8)NAME VARCHAR2(100)YSQL>到这里，就实现了我们的需求，使用本地动态SQL根据用户输入的表名及字段名、字段类型等参数来实现动态执行DDL语句。2、本地动态SQL执行DML语句。需求：将用户输入的值插入到上例中建好的di

37、nya test表中。create or replace procedure proc_insert(id in number,-输入序号name in varchar2-输入姓名)asstr_sql varchar2(500);beginstr_sql:= ' iriistortdinya_test values(:1,:2)'；execute immediate str_sql using id,name;-动态执行插入操作 excepti onwhen others thenn ull;end ;执行存储过程，插入数据到测试表中。SQL>execute proc_i

38、nsert(1,' dinya ');PL/SQLprocedure successfully completedSQL>select * from dinya_test;ID NAME1 din ya在上例中，本地动态SQL执行DML语句时使用了 using子句，按顺序将输入的值绑定到变量，如果需要输出参数，可以在执行动态 SQL的时候，使用RETURNINGINTO子句，如:declarep_id number:=1;v_count number;beginv_string:= ' scdectt(*) from table_name a where a.id

39、=:id '；execute immediate v_string into v_count using p_id;end ;使用DBMS_SQL包使用DBMS_SQL包实现动态SQL的步骤如下：A、先将要执行的 SQL语句或一个语句块放到一个字符串变量中。B 使用 DBMS_SQL包的parse过程来分析该字符串。C 使用 DBMS_SQL包的bind_variable过程来绑定变量。D、使用DBMS_SQL包的execute函数来执行语句。1、使用 DBMS_SQL包执行DDL语句需求：使用DBMS SQL包根据用户输入的表名、字段名及字段类型建表。create or replac

40、e procedure proc_dbms_sql(table_name in varchar2,field_name1 in varchar2, datatype1 in varchar2,field_name2 in varchar2, datatype2 in varchar2)asv_cursor number;v_string varchar2(200);v_row number;beginv_cursor:=dbms_sql.ope n_cursor;-为处理打开光标-表名-字段名 -字段类型-字段名 -字段类型-定义光标-定义字符串变量-行数v_stri ng:= ' t

41、able ' |table_name| ' ( ' |field_name1|datatype1|' , ' |field_name2dBtatype2|dbms_sql.parse(v_cursor,v_stri ng,dbms_sql. native);-分析语句v_row:=dbms_sql.execute(v_cursor);-执行语句dbms_sql.close_cursor(v_cursor);-关闭光标excepti onwhen others thendbms_sql.close_cursor(v_cursor);-关闭光标raise;e

42、n d;以上过程编译通过后，执行过程创建表结构：create')'SQL>execute proc_dbms_sql( ' dinya_test2PL/SQLprocedure successfully completedSQL>desc din ya_test2;Name Type Nullable Default CommentsID NUMBER(8)NAME VARCHAR2(100)YSQL>idnbtnuilnumber(8ame' , ' varchar2(100)');2、使用DBMS_SQL包执行DML语句需求

43、：使用DBMS_SQL包根据用户输入的值更新表中相对应的记录。 查看表中已有记录：1 Oracle2CSDN3ERPSQL>建存储过程，并编译通过:create or replace procedure proc_dbms_sql_update (id number, name varchar2)asv_cursor number;-定义光标v_string varchar2(200);-字符串变量v_row number;-行数beginv_cursor:=dbms_sql.ope n_cursor;-为处理打开光标v_string:=' updiaya_test2 a set

44、 =:p_name where a.id=:p_iddbms_sql.parse(v_cursor,v_stri ng,dbms_sql. native);-分析语句dbms_sql.b in d_variable(v_cursor, dbms_sql.b in d_variable(v_cursor, v_row:=dbms_sql.execute(v_cursor); dbms_sql.close_cursor(v_cursor);excepti onwhen others then dbms_sql.close_cursor(v_cursor); raise;en d;:p_

45、name '_-绑am变量:p_id-绑定变量-执行动态SQL-关闭光标-关闭光标执行过程，根据用户输入的参数更新表中的数据:SQL>execute proc_dbms_sql_update(2, ' csdn_dinya ');PL/SQLprocedure successfully completedSQL>select * from dinya_test2;ID NAME1 Oracle2 csdn_dinya3 ERPSQL>执行过程后将第二条的name字段的数据更新为新值csdn_dinya。这样就完成了使用 dbms_sql包来执行DML语

46、句的功能。使用DBMS_SQL中，如果要执行的动态语句不是查询语句，使用DBMS_SQL.Execute或DBMS_SQL.Variable_Value来执行，如果要执行动态语句 是查询语句，则 要使用 DBMS_SQL.defi ne_colu mn 定义输出变量，然后使用 DBMS_SQL.Execute, DBMS_SQL.Fetch_RowsDBMS_SQL.Column_Value及 DBMS_SQL.Variable_Value来执行查询并 得到结果。总结说明：在Oracle开发过程中，我们可以使用动态SQL来执行DDL语句、DML语句、事务控制语句及系统控制语句。但是需要注意的是

47、，PL/SQL块中使用动态 SQL执行DDL语句的时候与别的不同，在 DDL中使用绑定变量是非法的( bind_variable(v_cursor, ' :p_name' )ai分e) 析后不需要执行 DBMS_SQL.Bind_Variable,直接将输入的变量加到字符串中即可。另外，DDL是在调用DBMS_SQL.PARS时执行的，所以 DBMS_SQL.EXECUT也可以不用，即在上例中的 v_row:=dbms_sql.execute(v_cursor)咅B分可以不要。Oracle存储过程调用Java方法存储过程中调用Java程序段 软件环境：1、操作系统： Windo

48、ws 2000 Server2、数 据库：Oracle 8i R2 (8.1.7) for NT 企业版3、安装路径：C:ORACLE实现方法：1、创建一个文件为 Test.javapublic class Test public static void main( Stri ngargs) System. out .println("HELLOTHIS iS A Java PROCEDURE");2、javac Test.java3、java Test4、SQL>conn system/managerSQL>grant create any directory to scott;SQL>conn scott/tigerSQL>create or replace directory test_dir as'd:'目录已创建。SQL>create or replace java class using bfile(test_dir,'TEST.CLASS')2/Java已创建。SQL>select object_name,object_type,STATUS from user_objects;SQL>create or replace p