通常运用DBMS_SQL包一般分为如下几步：
1. open cursor：打开cursor
2. parse cursor：解析你要执行的SQL语句
3. bind variable：如果要执行的SQL语句中包含变量，在此就需要绑定变量
4. execute：执行SQL语句
5. close cursor：在执行后关闭此cursor.
如果你还需要返回执行SQL的结果集，还需要使用define_column,define_array等方法，具体可以看如下流程图：
-- The flow of procedure calls will typically look like this: -- -- ----------- -- | open_cursor | -- ----------- -- | -- | -- v -- ----- -- ------------>| parse | -- | ----- -- | | -- | |--------- -- | v | -- | -------------- | -- |-------->| bind_variable | | -- | ^ ------------- | -- | | | | -- | -----------| | -- | |<-------- -- | v -- | query?---------- yes --------- -- | | | -- | no | -- | | | -- | v v -- | ------- ------------- -- |----------->| execute | ->| define_column | -- | ------- | ------------- -- | |------------ | | -- | | | ----------| -- | v | v -- | -------------- | ------- -- | ->| variable_value | | ------>| execute | -- | | -------------- | | ------- -- | | | | | | -- | ----------| | | | -- | | | | v -- | | | | ---------- -- | |<----------- |----->| fetch_rows | -- | | | ---------- -- | | | | -- | | | v -- | | | -------------------- -- | | | | column_value | -- | | | | variable_value | -- | | | --------------------- -- | | | | -- | |<-------------------------- -- | | -- -----------------| -- | -- v -- ------------ -- | close_cursor | -- ------------ --
下面根据不同情况用例子详细展示：
在做展示之前，先准备一些基础数据
create table cux_demo (a number,b number,c number);
begin
for i in 1 .. 15 loop
insert into cux_demo
values
(round(dbms_random.value, 2) * 100,
round(dbms_random.value, 2) * 100,
round(dbms_random.value, 2) * 100);
end loop;
commit;
end;
基础数据完成之后，下面开始对一些具体情况进行分析：
1.执行一般的select语句
首先先介绍最常用情况：
DECLARE
p_no NUMBER := 80;
cursor_name INTEGER := dbms_sql.open_cursor; --在初始化参数时,就可以打开cursor;
row_process INTEGER;
v_b NUMBER;
BEGIN
--解析要执行的SQL.
dbms_sql.parse(cursor_name,
'select * from cux_demo where a= :no',
dbms_sql.native);
--如果要执行的SQL中不需要参数，则可以省略掉bind_variable--
dbms_sql.bind_variable(cursor_name,
'no',
p_no);
--如果需要返回查询语句的结果,则必须在exec之前使用define_column函数定义返回字段;
--define_column函数的第一个参数是最初定义的cursor name,
--第二个参数是指需要返回的字段在查询结果中处于第几列,在此例中返回的字段是查询结果中的第二列,即b列;
--第三个参数就是接收返回结果需要的变量*/
dbms_sql.define_column(cursor_name,
2,
v_b);
--必须定义一个参数接收exec的结果
row_process := dbms_sql.execute(cursor_name);
LOOP
IF dbms_sql.fetch_rows(cursor_name) > 0 THEN
--将前面定义的字段返回给变量v_b--
dbms_sql.column_value(cursor_name,
2,
v_b);
dbms_output.put_line('B is ' || v_b);
ELSE
EXIT;
END IF;
END LOOP;
--数据处理完成后记得要将cursor关闭
dbms_sql.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(cursor_name);
END;
2.使用define_array方法得到查询结果
前面已经分析了如何使用define_column方法得到查询结果，但有时我们想要一次得到多行查询结果，此时我们就需要使用define_array方法，此方法常用于DML操作，稍后会有例子对此介绍，现在先来看一下如果使用define_array.
DECLARE
c NUMBER;
d NUMBER;
n_tab dbms_sql.number_table;
n_tab1 dbms_sql.number_table;
indx NUMBER := 1;
BEGIN
c := dbms_sql.open_cursor;
dbms_sql.parse(c,
'select * from cux_demo where rownum<13 order by 1',
dbms_sql.native);
--在此需要特别介绍一下define_array函数的第一个参数是已经打开的cursor名称,
--第二个参数是指需要返回的字段在查询结果中处于第几列,
--第三个参数就是接收返回结果需要的变量,与define_column不同的是此变量是table,而不是普通的字段类型;
--第四个参数表示一次可以返回的行数;
--第五个参数是指n_tab的index从哪个数值开始,此数值是递增的.在此例中index是从1开始的，一次得到9行结果集，则有n_tab(1)到n_tab(9),如果循环再得到新的结果集,则index继续增长n_tab(10)....
dbms_sql.define_array(c,
1,
n_tab,
9,
indx);
dbms_sql.define_array(c,
2,
n_tab1,
9,
indx);
d := dbms_sql.execute(c);
LOOP
d := dbms_sql.fetch_rows(c);
dbms_output.put_line('fetch rows is ' || d);
EXIT WHEN d < 9;
dbms_sql.column_value(c,
1,
n_tab);
dbms_sql.column_value(c,
2,
n_tab1);
FOR i IN 1 .. d LOOP
dbms_output.put_line(n_tab(i) || ',' || n_tab1(i));
END LOOP;
END LOOP;
dbms_sql.close_cursor(c);
EXCEPTION
WHEN OTHERS THEN
IF dbms_sql.is_open(c) THEN
dbms_sql.close_cursor(c);
END IF;
END;
3.使用variable_value显示DML后的返回结果(单条记录)
以上我们介绍了如何使用DBMS_SQL包来处理数据查询，如果我们把查询语句更换成DML语句，则可以完成各种DML操作。在PL/SQL中我们可以使用returning方法返回DML操作结果，在DBMS_SQL包中可不可以实现呢？答案当然是可以，用variable_value方法就可以实现。下面就分别用两个例子来展示如何实现，一个是返回单条记录，另一个是返回多条记录。
（1）返回单条记录
DECLARE
c1 NUMBER := 100;
c2 NUMBER := 100;
r NUMBER;
cursor_name NUMBER := dbms_sql.open_cursor;
n NUMBER;
BEGIN
dbms_sql.parse(cursor_name,
'insert into cux_demo(a,b) values (:a,:b) returning :a*:b into :r',
dbms_sql.native);
dbms_sql.bind_variable(cursor_name,
'a',
c1);
dbms_sql.bind_variable(cursor_name,
'b',
c2);
dbms_sql.bind_variable(cursor_name,
'r',
r);
n := dbms_sql.execute(cursor_name);
--使用variable_value函数得到DML操作returning的结果集
dbms_sql.variable_value(cursor_name,
'r',
r);
dbms_output.put_line(r);
dbms_sql.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(cursor_name);
END;
（2）返回多条记录
结合define_array使用，可以更好的完成DML操作。
PROCEDURE multi_insert_priv(c1 IN dbms_sql.number_table,
c2 IN dbms_sql.number_table,
r OUT dbms_sql.number_table) IS
cursor_name NUMBER := dbms_sql.open_cursor;
n NUMBER;
BEGIN
dbms_sql.parse(cursor_name,
'insert into cux_demo values (:a,:b) returning :a*:b into :r',
dbms_sql.native);
--使用bind_array函数将number_table类型的变量赋值给绑定变量
dbms_sql.bind_array(cursor_name,
'a',
c1);
dbms_sql.bind_array(cursor_name,
'b',
c2);
dbms_sql.bind_array(cursor_name,
'r',
r);
n := dbms_sql.execute(cursor_name);
--使用variable_value函数将returning的结果集赋值给number_table类型的变量
dbms_sql.variable_value(cursor_name,
'r',
r);
dbms_sql.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(cursor_name);
END;
PROCEDURE multi_insert IS
c1 dbms_sql.number_table;
c2 dbms_sql.number_table;
cursor_name NUMBER := dbms_sql.open_cursor;
n NUMBER;
r dbms_sql.number_table;
indx NUMBER := 1;
d NUMBER;
BEGIN
dbms_sql.parse(cursor_name,
'select * from cux_demo',
dbms_sql.native);
dbms_sql.define_array(cursor_name,
1,
c1,
5,
indx);
dbms_sql.define_array(cursor_name,
2,
c2,
5,
indx);
n := dbms_sql.execute(cursor_name);
LOOP
d := dbms_sql.fetch_rows(cursor_name);
EXIT WHEN d = 0;
dbms_sql.column_value(cursor_name,
1,
c1);
dbms_sql.column_value(cursor_name,
2,
c2);
multi_insert_priv(c1,
c2,
r);
FOR i IN 1 .. r.count LOOP
dbms_output.put_line(r(i));
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(cursor_name);
END;
相关推荐
DBMS_XPLAN是Oracle提供的一个用于查看SQL计划，包括执行计划和解释计划的包；在以前查看SQL执行计划的时候，我都是使用set autotrace命令，不过现在看来，DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。 这5...
DBMS_RANDOM是Oracle提供的一个PL/SQL包，用于生成随机数据和字符。它具有以下函数。 其中，initialize，random，terminate函数在Oracle11g中已不推荐使用，主要用于向后兼容。下面对各个函数进行举例说明 1. ...
一、dbms_job涉及到的知识点 1、创建job： variable jobno number; dbms_job.submit(:jobno, —-job号 'your_procedure;',—-执行的存储过程, ';'不能省略 next_date, —-下次执行时间 'interval' —-每次间隔...
dbms_random是oracle提供的一个随机函数包，以下介绍一些dbms_random的常用示例： dbms_random.value用法： 生成一个大于等于0，小于等于1的38位小数 代码如下: — FUNCTION value RETURN NUMBER; select dbms_...
dbms_lob.instr( lob_loc in blob, pattern in raw, offset in integer := 1; nth in integer := 1) return integer; dbms_lob.instr( lob_loc in clob character set any_cs, pattern in varchar2 character
十九、 Oracle索引 详解 118 1. 索引简介 118 2. 索引的建立、修改、删除 125 3. 索引有单列索引、复合索引 127 4. 索引分类 128 5. 新索引类型 137 6. 查看索引类别以及查看索引字段被引用的字段方法 141 二...
本注释说明主要用于PL/SQL程序及其它SQL文件，其它可作参考； 2． SQLPLUS接受的注释有三种： ―― 这儿是注释 ／* 这儿是注释 *／ REM 这儿是注释 3． 开始注释，类似JAVAK中的开始注释，主要...
Oracle基本PLSQL的使用实例详解 PL/SQL 块是在 SQL 语言之上发展起来的一种应用，可以集中的处理各种复杂的 SQL 操 作。 组成： DECLARE： 声明部分 BEGIN 编写主题 EXCEPTION 捕获异常 END; 1.一个简单的PL/...
-- 隐式游标 （使用的表为Oracle默认自带的emp表） -- sql%rowcount：影响记录条数 sql%found：是否有满足条件的记录 set serveroutput on; declare v_ename a_emp.ename%type; begin select ename into v_ename ...
以下为简要使用步骤，如果想参考详细使用方法，也可以参考ORACLE联机帮助。 1.设置表空间为只读（假定表空间名字为APP_Data 和APP_Index） alter tablespace app_data read only; alter tablespace app_index read ...
言归正传，我们可以针对一个表，基于特定的工作负荷，通过使用DBMS_STATS.SEED_COL_USAGE和REPORT_COL_USAGE来确定我们需要哪些列组。当你不清除需要创建哪个扩展统计信息时，这个技术是非常有用的。需要注意的是，...
6.1使用SQL窗口 6.2结果表格处理 6.3实例模式查询 6.4连接查询 6.5置换变量 6.6更新数据库 6.7查看和编辑XMLTYPE 6.8直接查询导出 6.9保存SQL脚本 6.10创建标准查询 7命令窗口 7.1输入SQL语句和命令 7.2...
1.3.2.3 使用在线重定义：DBMS_REDEFINITION .........................50 1.3.2.4 使用导出导入.........54 1.3.2.4.1 迁移分区表的步骤.....................55 1.3.2.4.2 示例1：使用exp/imp ............
在Oracle 几年的学习中，做了很多的实验，也遇到了很多的问题， 在这个过程中，积累了一些学习文档。也更新到了blog上。 因为太多，不便于查阅。 根据自己对 Oracle 的理解，把这些 blog 进行了分类，并进行了一些...