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


OracleOracleOracleOracle 存储过程学习 目录 Oracle 存 储过 程 .............................................................................................................................. 1 Oracle 存 储过 程基 础知 识 ............................................................................................................... 1 Oracle 存 储过 程的 基本 语法 ........................................................................................................... 2 关于Oracle 存 储过 程的 若干 问题 备忘 ...........................................................................................4 1. 在Oracle 中 ,数 据表 别名 不能 加 as。.......................................................................... 4 2. 在存储过程中,select 某一字段时,后面必须紧跟into,如果select 整个记录,利 用 游标 的话 就另 当别 论了 。 ...................................................................................................5 3. 在 利用 select...into...语 法时 ,必 须先 确保 数据 库中 有该 条记 录 ,否 则会 报出 "no data found"异 常。 ............................................................................................................................5 4. 在 存储 过程 中 ,别 名不 能和 字段 名称 相同 ,否 则虽 然编 译可 以通 过 ,但 在运 行阶 段 会 报错 .......................................................................................................................................5 5. 在 存储 过程 中, 关于 出现 null 的 问题 ...........................................................................5 6. Hibernate 调用Oracle 存 储过 程 ..................................................................................... 6 用Java 调用Oracle 存 储过 程总 结 ..................................................................................................6 一、无 返回 值的 存储 过程 ...............................................................................................6 二、有 返回 值的 存储 过程 (非 列表 ) ...........................................................................8 三、返 回列 表 ...................................................................................................................9 在 存储 过程 中做 简单 动态 查询 .....................................................................................................11 一、本 地动 态 SQL..........................................................................................................12 二、使用DBMS_SQL 包.................................................................................................13 Oracle 存 储过 程调 用 Java 方法.....................................................................................................16 Oracle 高 效分 页存 储过 程实 例 ..................................................................................................... 17 OOOOracle racle racle racle 存储过程基础知识 商 业规 则和 业务 逻辑 可以 通过 程序 存储 在 Oracle 中 ,这 个程 序就 是 存 储过 程 。 存 储过 程是 SQL,PL/SQL, Java 语 句的 组合 ,它 使你 能将 执行 商业 规则 的代 码从 你的 应用 程 序中 移动 到数 据库 。这 样的 结果 就是 , 代 码存 储一 次但 是能 够被 多个 程序 使用 。 要 创建 一个 过程 对象 (procedural object),必 须有 CREATEPROCEDURE 系 统权 限 。如果 这个过程对象需要被其他的用户schema 使用,那么你必须有CREATEANYPROCEDURE 权 限。执行procedure 的时候,可能需要excute 权限。或者EXCUTEANYPROCEDURE 权限。 如 果单 独赋 予权 限, 如下 例所 示: grant execute on MY_PROCEDURE to Jelly 调 用一 个存 储过 程的 例子 : execute MY_PROCEDURE('ONEPARAMETER'); 存 储过 程 ((((PROCEDUREPROCEDUREPROCEDUREPROCEDURE))))和函数((((FUNCTIONFUNCTIONFUNCTIONFUNCTION))))的 区别 。 function 有返回值,并且可以直接在Query 中引用function 和或者使用function 的返回 值。 本 质上 没有 区别 ,都是PL/SQL 程序,都 可以 有返 回值 。最 根本 的区 别是 :存储过程是命 令,,,, 而 函数 是表 达式 的一 部分 。比如: select max(NAME) FROM 但 是不 能 exec max(NAME) 如 果此 时 max 是 函数 。 PACKAGE PACKAGE PACKAGE PACKAGE 是function,procedure,variables 和sql 语句的组合。package 允许多个 procedure 使 用同 一个 变量 和游 标。 创建procedure procedure procedure procedure 的 语法 : CREATE[ORREPLACE]PROCEDURE[schema.]procedure [(argument[IN|OUT|INOUT][NOCOPY]datatype [,argument[IN|OUT|INOUT][NOCOPY]datatype]... )] [authid{current_user|definer}] {is|as}{pl/sql_subprogram_body| language{javaname'String'|c[name,name]librarylib_name }] Sql 代 码: CREATEPROCEDUREsam.credit(acc_noINNUMBER,amountINNUMBER)AS BEGIN UPDATEaccounts SETbalance=balance+amount WHEREaccount_id=acc_no; END; 可以使用create or replace procedure 语句,这个语句的用处在于,你之前赋予的 excute 权 限都 将被 保留 。 IN,IN,IN,IN,OUTOUTOUTOUT,,,,ININININ OUT OUT OUT OUT 用 来修 饰参 数。 IN 表 示这 个变 量必 须被 调用 者赋 值然 后传 入到 PROCEDURE 进 行处 理。 OUT 表示PRCEDURE 通 过这 个变 量将 值传 回给 调用 者。 INOUT 则 是这 两种 的组 合。 authid authid authid authid 代 表两 种权 限: 定 义者 权限 ( difiner right 默认), 执行 者权 限( invoker right)。 定 义者 权限 说明 这个 procedure 中 涉及 的表 ,视 图等 对象 所需 要的 权限 只要 定义 者拥 有 权 限的 话就 可以 访问 。 执 行者 权限 则需 要调 用这 个 procedure 的 用户 拥有 相关 表和 对象 的权 限。 OOOOracle racle racle racle 存储过程的基本语法 1.1.1.1. 基 本结 构 CREATEORREPLACEPROCEDURE 存 储过 程名 字 ( 参数1 INNUMBER, 参数2 INNUMBER )AS 变量1 INTEGER:=0; 变量2 DATE; BEGIN END 存 储过 程名 字 2.2.2.2. SELECTSELECTSELECTSELECTINTOINTOINTOINTOSTATEMENTSTATEMENTSTATEMENTSTATEMENT 将select 查 询的 结果 存入 到变 量中 ,可 以同 时将 多个 列存 储多 个变 量中 ,必 须有 一条 记 录, 否则 抛出 异常 (如 果没 有记 录抛 出 NO_DATA_FOUND) 例 子: BEGIN SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx; EXCEPTION WHENNO_DATA_FOUNDTHEN xxxx; END; ... 3.3.3.3. IFIFIFIF 判断 IF V_TEST=1 THEN BEGIN do something END; ENDIF; 4.4.4.4. whilewhilewhilewhile 循环 WHILE V_TEST=1 LOOP BEGIN XXXX END; ENDLOOP; 5.5.5.5. 变 量赋 值 V_TEST:= 123; 6.6.6.6. 用forforforfor inininin 使用cursorcursorcursorcursor ... IS CURSOR cur ISSELECT*FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM:=cur_result.列名1+cur_result.列名2 END; ENDLOOP; END; 7.7.7.7. 带 参数 的 cursorcursorcursorcursor CURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERE TYPEID=C_ID; OPENC_USER(变 量值 ); LOOP FETCHC_USERINTOV_NAME; EXITFETCHC_USER%NOTFOUND; do something ENDLOOP; CLOSEC_USER; 8.8.8.8. 用pl/sqlpl/sqlpl/sqlpl/sql developerdeveloperdeveloperdeveloper debugdebugdebugdebug 连 接数 据库 后建 立一 个 Test WINDOW 在 窗口 输入 调用 SP的 代码 ,F9 开始debug,CTRL+N 单 步调 试 9.9.9.9. Pl/Sql Pl/Sql Pl/Sql Pl/Sql 中 执行 存储 过程 在sql*plus 中: declare --必 要的 变量 声明 ,视 你的 过程 而定 begin execute yourprocudure(parameter1,parameter2,...); end / 在SQL/PLUS中调用存储过程,显示结果: SQL>set serveoutput on --打开输出 SQL>var info1 number; --输出 1 SQL>var info2 number; --输出 2 SQL>declare var1 varchar2(20); --输入 1 var2 varchar2(20); --输入 2 var3 varchar2(20); --输入 2 BEGIN pro(var1,var2,var3,:info1,:info2); END; / SQL>print info1; SQL>print info2; 注:在EXECUTEEXECUTEEXECUTEEXECUTEIMMEDIATEIMMEDIATEIMMEDIATEIMMEDIATE STR 语句是SQLPLUS 中动态执行语句,它在执行中会自动 提交,类 似于 DP中FORMS_DDL 语句,在 此语 句中 str 是 不能 换行 的 ,只 能通 过连 接字 符 "||", 或 着在 在换 行时 加上 "-"连 接字 符。 关于Oracle Oracle Oracle Oracle 存储过程的若干问题备忘 1.1.1.1. 在Oracle Oracle Oracle Oracle 中,数据表别名不能加asasasas。 如: select a.appname from appinfo a;-- 正确 select a.appname from appinfo as a;-- 错误 也 许, 是怕 和 Oracle 中 的存 储过 程中 的关 键字 as冲 突的 问题 吧 2.2.2.2. 在存储过程中,select select select select 某一字段时,后面必须紧跟intointointointo, 如果select select select select 整个记录,利用游标的话就另当别论了。 select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid; -- 有into, 正确 编译 select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有into ,编译报错,提示: Compilation Error: PLS-00428: an INTO clause is expected in this SELECT statement 3.3.3.3. 在利用select...into...select...into...select...into...select...into...语法时,必须先确保数据库中有该条 记录,否则会报出"no"no"no"no datadatadatadata found"found"found"found"异常。 可 以在 该语 法之 前 ,先 利用 select count(*) from 查 看数 据库 中是 否存 在该 记录 ,如 果存 在 ,再 利用 select...into... 4.4.4.4. 在存储过程中,别名不能和字段名称相同,否则虽然编译 可以通过,但在运行阶段会报错 select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid; -- 正 确运 行 select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid =foundationid; -- 运 行阶 段报 错, 提示 : ORA-01422:exact fetch returns more than requested number of rows 5.5.5.5. 在存储过程中,关于出现null null null null 的问题 假 设有 一个 表 A, 定义 如下 : create table A( id varchar2(50) primary key not null, vcount number(8) not null, bid varchar2(50) not null -- 外键 ); 如 果在 存储 过程 中, 使用 如下 语句 : select sum(vcount) into fcount from A where bid='xxxxxx'; 如果A 表 中不 存 在bid="xxxxxx"的 记录 ,则fcount=null(即使fcount 定 义时 设置 了默 认值 , 如:fcount number(8):=0 依 然无 效 ,fcount 还 是会 变成 null),这 样以 后使 用 fcount 时 就可 能 有 问题 ,所 以在 这里 最好 先判 断一 下: if fcount is null then fcount:=0; end if; 这 样就 一切 ok了。 6.6.6.6. Hibernate Hibernate Hibernate Hibernate 调用Oracle Oracle Oracle Oracle 存储过程 thisthisthisthis.pnumberManager.getHibernateTemplate().execute( newnewnewnewHibernateCallback()...{ publicpublicpublicpublicObjectdoInHibernate(Sessionsession) throwsthrowsthrowsthrowsHibernateException,SQLException...{ CallableStatementcs=session .connection() .prepareCall("{call modifyapppnumber_remain(?)}"); cs.setString(1,foundationid); cs.execute(); returnreturnreturnreturnnullnullnullnull; } }); 用JJJJava ava ava ava 调用OOOOracle racle racle racle 存储过程总结 一、无返回值的存储过程 测 试表 : --Createtable createtableTESTTB ( IDVARCHAR2(30), NAMEVARCHAR2(30) ) tablespaceBOM pctfree10 initrans1 maxtrans255 storage ( initial64K minextents1 maxextentsunlimited ); 例: 存 储过 程为 (当 然了 ,这 就先 要求 要建 张表 TESTTB,里 面两 个字 段( I_ID,I_NAME)。 ): CREATEORREPLACEPROCEDURETESTA(PARA1INVARCHAR2,PARA2INVARCHAR2) AS BEGIN INSERTINTOBOM.TESTTB(ID,NAME)VALUES(PARA1,PARA2); ENDTESTA; 在Java 里 调用 时就 用下 面的 代码 : packagecom.yiming.procedure.test; importjava.sql.CallableStatement; importjava.sql.Connection; importjava.sql.DriverManager; importjava.sql.ResultSet; importjava.sql.SQLException; importjava.sql.Statement; publicclassTestProcedureDemo1{ publicTestProcedureDemo1(){ } publicstaticvoidmain(String[]args){ Stringdriver="Oracle.jdbc.driver.OracleDriver"; StringstrUrl="jdbc:Oracle:thin:@10.20.30.30:1521:vasms"; Statementstmt=null; ResultSetrs=null; Connectionconn=null; CallableStatementproc=null; try{ Class.forName(driver); conn=DriverManager.getConnection(strUrl,"bom","bom"); proc=conn.prepareCall("{callBOM.TESTA(?,?)}"); proc.setString(1,"100"); proc.setString(2,"TestOne"); proc.execute(); }catch(SQLExceptionex2){ ex2.printStackTrace(); }catch(Exceptionex2){ ex2.printStackTrace(); }finally{ try{ if(rs!=null){ rs.close(); if(stmt!=null){ stmt.close(); } if(conn!=null){ conn.close(); } } }catch(SQLExceptionex1){ } } } } 二、有返回值的存储过程(非列表) 例 :存 储过 程为 : CREATEORREPLACEPROCEDURETESTB(PARA1INVARCHAR2,PARA2OUTVARCHAR2) AS BEGIN SELECTNAMEINTOPARA2FROMTESTTBWHEREID=PARA1; ENDTESTB; 在Java 里 调用 时就 用下 面的 代码 : package com.yiming.procedure.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; public class TestProcedureDemo2 { public static void main(String[] args) { String driver = "Oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:Oracle:thin:@10.20.30.30:1521:vasms"; Statement stmt = null; ResultSet rs = null; Connection conn = null; CallableStatement 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 = proc.getString(2); System.out.println("=testPrint=is=" + testPrint); } catch (SQLException ex2) { ex2.printStackTrace(); } catch (Exception ex2) { ex2.printStackTrace(); } finally { try { if (rs != null) { rs.close(); if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } } catch (SQLException ex1) { } } } } 注意,这 里的 proc.getString(2)中 的数 值 2并 非任 意的 ,而 是和 存储 过程 中的 out 列 对应 的,如果out 是在第一个位置,那就是proc.getString(1),如果是第三个位置,就是 proc.getString(3), 当然 也可 以同 时有 多个 返回 值, 那就 是再 多加 几个 out 参 数了 。 三、返回列表 由于Oracle Oracle Oracle Oracle 存储过程没有返回值,它的所有返回值都是通过out out out out 参数来替代的,列表 同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage 了.所以要分两 部 分, 1. 建 一个 程序 包 。 如下 : CREATEORREPLACEPACKAGETESTPACKAGEAS TYPETEST_CURSORISREFCURSOR; endTESTPACKAGE; 2. 建 立存 储过 程 , 存储 过程 为: CREATEORREPLACEPROCEDURETESTC(P_CURSORout TESTPACKAGE.TEST_CURSOR)IS BEGIN OPENP_CURSORFOR SELECT*FROMBOM.TESTTB; ENDTESTC; 可 以看 到, 它是 把游 标( 可以 理解 为一 个指 针 ), 作为 一个 out 参 数来 返回 值的 。 在Java 里 调用 时就 用下 面的 代码 : 在 这里 要注 意 ,在 执行 前一 定要 先把 Oracle 的 驱动 包放 到 class 路 径里 ,否 则会 报错 的 。 packagecom.yiming.procedure.test; importjava.sql.CallableStatement; importjava.sql.Connection; importjava.sql.DriverManager; importjava.sql.ResultSet; importjava.sql.SQLException; importjava.sql.Statement; publicclassTestProcedureDemo3{ publicstaticvoidmain(String[]args){ Stringdriver="Oracle.jdbc.driver.OracleDriver"; StringstrUrl="jdbc:Oracle:thin:@10.20.30.30:1521:vasms"; Statementstmt=null; ResultSetrs=null; Connectionconn=null; CallableStatementproc=null; try{ Class.forName(driver); conn=DriverManager.getConnection(strUrl,"bom","bom"); proc=conn.prepareCall("{callbom.testc(?)}"); proc.registerOutParameter(1, Oracle.jdbc.OracleTypes.CURSOR); proc.execute(); rs=(ResultSet)proc.getObject(1); while(rs.next()){ System.out.println(""+rs.getString(1)+ "" +rs.getString(2)+""); } }catch(SQLExceptionex2){ ex2.printStackTrace(); }catch(Exceptionex2){ ex2.printStackTrace(); }finally{ try{ if(rs!=null){ rs.close(); if(stmt!=null){ stmt.close(); } if(conn!=null){ conn.close(); } } }catch(SQLExceptionex1){ } } } } 在存储过程中做简单动态查询 在 存储 过程 中做 简单 动态 查询 代码 ,例如: CREATEORREPLACE procedure ZXM_SB_GZ_GET (p_table in varchar2, p_name in varchar2, p_value in varchar2, outpara out lntxdba.zxm_pag_cs_power.c_type ) as begin declare wherevalue varchar2(200); begin wherevalue:=select * from ||p_table|| where ||p_name||=||p_value; open outpara for wherevalue; end; end; 一 般的 PL/SQL 程 序设 计中 ,在 DML 和 事务 控制 的语 句中 可以 直接 使用 SQL, 但是 DDL 语 句及 系统 控制 语句 却不 能在 PL/SQL 中 直接 使用 ,要 想实 现在 PL/SQL 中 使用 DDL语 句及 系 统 控制 语句 ,可 以通 过使 用动 态 SQL 来 实现 。 首先我们应该了解什么是动态SQL,在Oracle 数据库开发PL/SQL 块中我们使用的SQL 分为:静态SQL 语 句和 动态 SQL 语句。所 谓静 态 SQL 指在PL/SQL 块 中使 用的 SQL 语 句在 编 译 时是 明确 的, 执行 的是 确定 对象 。而 动态 SQL 是 指在 PL/SQL 块 编译 时 SQL 语 句是 不确 定 的,如 根据 用户 输入 的参 数的 不同 而执 行不 同的 操作 。编 译程 序对 动态 语句 部分 不进 行处 理 , 只 是在 程序 运行 时动 态地 创建 语句 、对 语句 进行 语法 分析 并执 行该 语句 。 Oracle 中 动态 SQL 可 以通 过本 地动 态 SQL 来 执行 ,也 可以 通过 DBMS_SQL 包 来执 行 。下 面就 这 两种 情况 分别 进行 说明 : 一、本地动态SQLSQLSQLSQL 本 地动 态 SQL 是 使用 EXECUTE IMMEDIATE 语 句来 实现 的。 1、本 地动 态 SQL 执行DDL语 句: 需 求: 根据 用户 输入 的表 名及 字段 名等 参数 动态 建表 。 create or replace procedure proc_test ( table_name in varchar2, --表名 field1 in varchar2, --字 段名 datatype1 in varchar2, --字 段类 型 field2 in varchar2, --字 段名 datatype2 in varchar2 --字 段类 型 ) as str_sql varchar2(500); begin str_sql:=’create table ’||table_name||’(’||field1||’’||datatype1||’,’||field2||’’||datatype2||’)’; execute immediate str_sql; --动 态执 行 DDL语句 exception when others then null; end ; 以 上是 编译 通过 的存 储过 程代 码。 下面 执行 存储 过程 动态 建表 。 SQL> execute proc_test(’dinya_test’,’id’,’number(8) not null’,’name’,’varchar2(100)’); PL/SQL procedure successfully completed SQL> desc dinya_test; Name Type Nullable Default Comments ---------------------------------------- ID NUMBER(8) NAME VARCHAR2(100) Y SQL> 到 这里 ,就 实现 了我 们的 需求 ,使 用本 地动 态 SQL 根 据用 户输 入的 表名 及字 段名 、字段 类 型等 参数 来实 现动 态执 行 DDL语 句。 2、本 地动 态 SQL 执行DML 语 句。 需 求: 将用 户输 入的 值插 入到 上例 中建 好的 dinya_test 表 中。 create or replace procedure proc_insert ( id in number, --输 入序 号 name in varchar2 --输 入姓 名 ) as str_sql varchar2(500); begin str_sql:=’insert into dinya_test values(:1,:2)’; execute immediate str_sql using id,name; --动 态执 行插 入操 作 exception when others then null; end ; 执 行存 储过 程, 插入 数据 到测 试表 中。 SQL> execute proc_insert(1,’dinya’); PL/SQL procedure successfully completed SQL> select * from dinya_test; IDNAME 1 dinya 在上例中,本地动态SQL 执行DML 语句时使用了using 子句,按顺序将输入的值绑定 到 变量 ,如 果需 要输 出参 数 ,可 以在 执行 动态 SQL 的 时候 ,使用RETURNINGINTO 子句,如: declare p_id number:=1; v_count number; begin v_string:=’select count(*) from table_name a where a.id=:id’; execute immediate v_string into v_count using p_id; end ; 二、使用DBMS_SQL DBMS_SQL DBMS_SQL 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 replace procedure proc_dbms_sql ( table_name in varchar2, --表名 field_name1 in varchar2, --字 段名 datatype1 in varchar2, --字 段类 型 field_name2 in varchar2, --字 段名 datatype2 in varchar2 --字 段类 型 )as v_cursor number; --定 义光 标 v_string varchar2(200); --定 义字 符串 变量 v_row number; --行数 begin v_cursor:=dbms_sql.open_cursor; --为 处理 打开 光标 v_string:=’create table ’||table_name||’(’||field_name1||’’||datatype1||’,’||field_name2||’’||datatype2||’)’; dbms_sql.parse(v_cursor,v_string,dbms_sql.native); --分 析语 句 v_row:=dbms_sql.execute(v_cursor); --执 行语 句 dbms_sql.close_cursor(v_cursor); --关 闭光 标 exception when others then dbms_sql.close_cursor(v_cursor); --关 闭光 标 raise; end; 以 上过 程编 译通 过后 ,执 行过 程创 建表 结构 : SQL> execute proc_dbms_sql(’dinya_test2’,’id’,’number(8) not null’,’name’,’varchar2(100)’); PL/SQL procedure successfully completed SQL> desc dinya_test2; Name Type Nullable Default Comments ---------------------------------------- ID NUMBER(8) NAME VARCHAR2(100) Y SQL> 2、使用DBMS_SQL 包 执行 DML 语句 需 求: 使用 DBMS_SQL 包 根据 用户 输入 的值 更新 表中 相对 应的 记录 。 查 看表 中已 有记 录: SQL> select * from dinya_test2; IDNAME 1 Oracle 2 CSDN 3 ERP SQL> 建 存储 过程 ,并 编译 通过 : create or replace procedure proc_dbms_sql_update ( id number, name varchar2 )as v_cursor number; --定 义光 标 v_string varchar2(200); --字 符串 变量 v_row number; --行数 begin v_cursor:=dbms_sql.open_cursor; --为 处理 打开 光标 v_string:=’update dinya_test2 a set a.name=:p_name where a.id=:p_id’; dbms_sql.parse(v_cursor,v_string,dbms_sql.native); --分 析语 句 dbms_sql.bind_variable(v_cursor,’:p_name’,name); --绑 定变 量 dbms_sql.bind_variable(v_cursor,’:p_id’,id); --绑 定变 量 v_row:=dbms_sql.execute(v_cursor); --执 行动 态S QL dbms_sql.close_cursor(v_cursor); --关 闭光 标 exception when others then dbms_sql.close_cursor(v_cursor); --关 闭光 标 raise; end; 执 行过 程, 根据 用户 输入 的参 数更 新表 中的 数据 : SQL> execute proc_dbms_sql_update(2,’csdn_dinya’); PL/SQL procedure successfully completed SQL> select * from dinya_test2; IDNAME 1 Oracle 2 csdn_dinya 3 ERP SQL> 执行过程后将第二条的name 字段的数据更新为新值csdn_dinya。这样就完成了使用 dbms_sql 包 来执 行 DML 语 句的 功能 。 使用DBMS_SQL 中 ,如 果要 执行 的动 态语 句不 是查 询语 句, 使用 DBMS_SQL.Execute 或 DBMS_SQL.Variable_Value 来执行,如果要执行动态语句是查询语句,则要使用 DBMS_SQL.define_column 定义输出变量,然后使用DBMS_SQL.Execute, DBMS_SQL.Fetch_Rows, DBMS_SQL.Column_Value 及DBMS_SQL.Variable_Value 来执行查询并 得 到结 果。 总 结说 明: 在Oracle 开 发过 程中 ,我 们可 以使 用动 态 SQL 来 执行 DDL语 句、 DML 语 句、 事务 控制 语句及系统控制语句。但是需要注意的是,PL/SQL 块中使用动态SQL 执行DDL语句的时候 与 别的 不同 ,在 DDL中 使用 绑定 变量 是非 法的 ( bind_variable(v_cursor,’:p_name’,name)),分 析 后不 需要 执行 DBMS_SQL.Bind_Variable,直 接将 输入 的变 量加 到字 符串 中即 可 。另外,DDL 是 在调 用 DBMS_SQL.PARSE 时 执行 的 ,所以DBMS_SQL.EXECUTE 也 可以 不用 ,即 在上 例中 的 v_row:=dbms_sql.execute(v_cursor)部 分可 以不 要。 Oracle Oracle Oracle Oracle 存储过程调用Java Java Java Java 方法 存 储过 程中 调用 Java Java Java Java 程 序段 软 件环 境: 1、操 作系 统: Windows 2000 Server 2、数据库:Oracle 8i R2 (8.1.7) for NT 企 业版 3、安 装路 径: C:\ORACLE 实 现方 法: 1、创 建一 个文 件为 Test.java publicpublicpublicpublicclassclassclassclassTest{ publicpublicpublicpublicstaticstaticstaticstaticvoidvoidvoidvoidmain(Stringargs[]){ System.out.println("HELLOTHISiSAJavaPROCEDURE"); } } 2、javac Test.java 3、java Test 4、SQL> conn system/manager SQL> grant create any directory to scott; SQL> conn scott/tiger SQL> 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 procedure test_java as language java name 'TEST.main(java.lang.String[])'; / 过 程已 创建 。 SQL> set serveroutput on size 5000 SQL> call dbms_java.set_output(5000); 调 用完 成。 SQL> execute test_java; HELLOTHIS iS A Java PROCEDURE PL/SQL 过 程已 成功 完成 。 SQL> call test_java(); HELLOTHIS iS A Java PROCEDURE 调 用完 成。 Oracle 8I 9I 都测试通过。 OOOOracle racle racle racle 高效分页存储过程实例 create or replace package p_page is -- Author :PHARAOHS -- Created : 2006-4-30 14:14:14 -- Purpose : 分 页过 程 TYPE type_cur ISREFCURSOR;--定 义游 标变 量用 于返 回记 录集 PROCEDURE Pagination( Pindex in number, --分 页索 引 Psql in varchar2, --产生dataset 的sql 语句 Psize in number, --页 面大 小 Pcount out number, --返 回分 页总 数 v_cur out type_cur --返 回当 前页 数据 记录 ); procedure PageRecordsCount( Psqlcount in varchar2, --产生dataset 的sql 语句 Prcount out number --返 回记 录总 数 ); end p_page; / create or replace package body p_page is PROCEDURE Pagination( Pindex in number, Psql in varchar2, Psize in number, Pcount out number, v_cur out type_cur ) AS v_sql VARCHAR2(1000); v_count number; v_Plow number; v_Phei number; Begin ------------------------------------------------------------取 分页 总数 v_sql := 'select count(*) from (' || Psql || ')'; execute immediate v_sql into v_count; Pcount := ceil(v_count/Psize); ------------------------------------------------------------显 示任 意页 内容 v_Phei := Pindex * Psize + Psize; v_Plow := v_Phei - Psize + 1; --Psql := 'select rownum rn,t.* from zzda t' ;--要 求必 须包 含 rownum 字段 v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ; open v_cur for v_sql; End Pagination; --***************************************************************************** ********* procedure PageRecordsCount( Psqlcount in varchar2, Prcount out number ) as v_sql varchar2(1000); v_prcount number; begin v_sql := 'select count(*) from (' || Psqlcount || ')'; execute immediate v_sql into v_prcount; Prcount := v_prcount; --返 回记 录总 数 end PageRecordsCount; --***************************************************************************** ********* end p_page; /
还剩17页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 1 人已下载

下载pdf

pdf贡献者

a10296844

贡献于2012-08-27

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf