DBMS_SQL的使用转载 oracle dbms sql

DBMS_SQL的使用-------
通常运用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等方法。
-------下面根据不同情况进行详细展示:
在做展示之前,先准备一些基础数据
create table demo (a number,b number,c number);
begin
for i in 1 .. 15 loop
insert into 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语句首先先介绍最常用情况:
CREATE OR REPLACE PROCEDURE define_column(no IN NUMBER) IS
cursor_name INTEGER :=dbms_sql.open_cursor; --在初始化参数时,就可以打开cursor;
row_process INTEGER;
v_bNUMBER;
BEGIN
--解析要执行的SQL.
dbms_sql.parse(cursor_name,
'select * from demo where a= :no',
dbms_sql.native);
--如果要执行的SQL中不需要参数,则可以省略掉bind_variable
dbms_sql.bind_variable(cursor_name, 'no', 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.CREATE OR REPLACE PROCEDUREdefine_array IS
c NUMBER;
d NUMBER;
--DBMS_SQL.NUMBER_TABLE类型实际就是type NUMBER_TABLE istable of number index by binary_integer;
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 demo where rownum<13 order by1',
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)返回单条记录
PROCEDURE single_insert(c1 IN NUMBER, c2 IN NUMBER, r OUT NUMBER)IS cursor_name NUMBER:= dbms_sql.open_cursor;
nNUMBER;
BEGIN
dbms_sql.parse(cursor_name,
'insert into demo 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操作。CREATE OR REPLACE PACKAGEDBMS_SQL_DEMO AS
PROCEDURE multi_insert;
END;
/CREATE OR REPLACE PACKAGE BODYDBMS_SQL_DEMO AS PROCEDUREmulti_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;
nNUMBER;
BEGIN
dbms_sql.parse(cursor_name,
'insert into demo values (:a,:b)returning :a*:b into:r',
DBMS_SQL的使用[转载] oracle dbms sql
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_insertIS
c1dbms_sql.Number_Table;
c2dbms_sql.Number_Table;
cursor_name NUMBER:= dbms_sql.open_cursor;
nNUMBER;
rdbms_sql.Number_Table;
indx NUMBER:= 1;
dNUMBER;
BEGIN
dbms_sql.parse(cursor_name, 'select* from 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;END;
/[转载自http://space.itpub.net/13129975/viewspace-624522]

  

爱华网本文地址 » http://www.413yy.cn/a/25101016/319293.html

更多阅读

oracle decode函数使用方法 oracle 分区使用函数

oracle decode函数使用方法——简介decode()函数是ORACLE PL/SQL是功能强大的函数之一,目前还只有ORACLE公司的SQL提供了此函数,其他数据库厂商的SQL实现还没有此功能。oracle decode函数使用方法——工具/原料·软件环境:  1、Wi

转载 深层清洁面膜的使用方法指南 深层清洁面膜排行榜

原文地址:深层清洁面膜的使用方法指南作者:女人面膜资讯什么是深层清洁面膜?顾名思义,是一种能够深入清洁皮肤毛孔内部的污垢和过剩油脂的洁肤型面膜,相对于洁面乳,它的清洁能力更深入更彻底。一、深层清洁的意义我们的皮肤也会呼吸,它会

水准仪、经纬仪、全站仪的使用方法 经纬仪 水准仪

本文转载自泳池,洗浴设备《水准仪、经纬仪、全站仪的使用方法》水准仪及其使用方法高程测量是测绘地形图的基本工作之一,另外大量的工程、建筑施工也必须量测地面高程,利用水准仪进行水准测量是精密测量高程的主要方法。一、水准仪器组

(58)转载 手机微信的使用技巧大全 微信技巧大全

手机微信的使用技巧大全什么是微信?微信是腾讯公司推出的,提供类似于Kik免费即时通讯服务的免费聊天软件。用户可以通过手机、平板、网页快速发送语音、视频、图片和文字。微信提供公众平台、朋友圈、消息推送等功能,用户可以通过摇一

声明:《DBMS_SQL的使用转载 oracle dbms sql》为网友深院锁清秋分享!如侵犯到您的合法权益请联系我们删除