Oracle中执行动态SQL
作者:springsnow 发布时间:2024-01-19 20:55:37
一、概述
在一般的sql操作中,sql语句基本上都是固定的,如: SELECT t.empno,t.ename FROM scott.emp t WHERE t.deptno = 20;
但有的时候,从应用的需要或程序的编写出发,都可能需要用到动态SQl,如:
当 from 后的表 不确定时,或者where 后的条件不确定时,都需要用到动态SQL。
使用execute immediate语句可以处理包括ddl(create、alter和drop)、DCL(grant、revoke)、DML(insert、update、delete)以及单行select语句。
execute immediate语句:
execute immediate dynamic_string
[into {define_variable[,define_variable]…|record}]
[using [in|out|in out] bind_argument[,[in|out|in out]bind_argument]…]
[{returning|return} into bind_argument[, bind_argument]…]
define_variable用于指定存放单行查询结果的变量;
using in bind_argument用于指定存放传递给动态sql值的变量,即在dynamic中存在占位符时使用;
using out bind_argument用于指定存放动态sql返回值的变量。
二、执行ddl、dcl语句
不能使用into和using子句。
begin
execute immediate 'create table ma_org(org_codevarchar2(20),org_name varchar2(254))';
execute immediate 'drop table ma_org';
end;
语句
begin
execute immediate 'grant insert on ma_org to scott'
end;
三、处理dml语句
1、给动态语句传值(USING 子句)
如果dml语句包含占位符,那么在execute immediate语句之后必须要带有using子句;
declare
orgcode varchar2(10);
orgname varchar2(254);
begin
orgcode := 1200;
execute immediate 'select org_name fromma_org
where org_code = :X'
into orgname
using orgcode;
dbms_output.put_line(orgname);
end;
2、从动态语句检索值(INTO子句)
3、动态调用存储过程
declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;
if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end;
4、处理包含returing子句的DML语句
如果dml语句带有returning子句,那么在execute immediate语句之后必须带有returning into子句,并且此时只能处理作用的单行上的dml语句,如果dml语句作用在多行上,则必须使用bulk子句。
declare
orgcode varchar2(10);
orgname varchar2(254);
rname varchar2(254);
begin
orgcode := '1200';
orgname := '天津市分行';
execute immediate 'update ma_org set org_name=:X
where org_code = :Y returning org_name into :rname'
using orgname, orgcode
returning into rname;
dbms_output.put_line(orgname);
end;
5、在retuing into中使用bulk collect into
四、处理多行查询
oracle通过使用bulk collect into子句处理动态sql中的多行查询可以加快处理速度,从而提高应用程序的性能。当使用bulk子句时,集合类型可以是plsql所支持的索引表、嵌套表和varray,但集合元素必须使用sql数据类型。在oracle9i以后,有三种语句支持bulk子句,execute immediate,fetch和forall。
1、使用动态游标(游标变量)处理多行查询类动态sql语句。
DECLARE
TYPE ref_cur IS REF CURSOR;
rc ref_cur;
emprow emp%ROWTYPE;
v_sql VARCHAR2(100):= 'select * from emp where deptno = :x'; --动态执行的SQL语句
BEGIN
OPEN rc FOR v_sql USING 30; --打开游标,绑定执行的SQL语句,并传递参数
LOOP
FETCH rc INTO emprow;
EXIT WHEN rc%NOTFOUND;
dbms_output.put_line('name:'||emprow.ename||' sal:'||emprow.sal);
END LOOP;
CLOSE rc;
END;
2、在execute immediate中使用bulk collect into
示例:
declare
type org_table_type is table of ma_org%rowtype;
org_table org_table_type;
v_orgcode varchar2(20);
begin
v_orgcode := '%00%';
execute immediate 'select * from ma_org where org_code like:v_orgcode' bulk collect into org_table
using v_orgcode;
for i in 1..org_table.count
loop
dbms_output.put_line(org_table(i).org_code||','||org_table(i).org_name);
end loop;
end;
3、在forall语句中使用bulk collect into语句
示例:
declare
type type_org_code is table of ma_org.org_code%type;
type type_org_name is table of ma_org.org_name%type;
v_orgcode type_org_code;
v_orgname type_org_name;
begin
v_orgcode := type_org_code('1100','1200');
forall i in 1..v_orgcode.count
execute immediate 'update ma_org set org_name = org_code||org_namewhere org_code = :p1 returning org_name into :p2'
using v_orgcode(i)
returning bulk collect into v_orgname;
for i in v_orgname.first..v_orgname.last
loop
dbms_output.put_line(v_orgname(i));
end loop;
end;
来源:https://www.cnblogs.com/springsnow/archive/2011/12/09/2282528.html
猜你喜欢
- 本文实例分析了Python星号*与**用法。分享给大家供大家参考,具体如下:1. 加了星号(*)的变量名会存放所有未命名的变量参数,不能存放
- tf2.0的三个优点:1、方便搭建网络架构;2、自动求导3、GPU加速(便于大数据计算)安装过程(概要提示)step1:安装annacond
- 写在最前面:这个我打算分几次写,由于我们通过selenium拿到的图片会很模糊,所以使用Tesseract识别之前要对图片先进行处理。第一步
- 最近帮伙计做了一个从网页抓取股票信息并把相应信息存入MySQL中的程序。使用环境:Python 2.5 for WindowsMySQLdb
- 最近项目中遇见 Jquery Ajax 缓存问题,load出来的页面状态有时正常,有时不对,记录一下,希望对大家有帮助使用jquery里lo
- 下面一段代码是小编给大家介绍的Python ldap实现登录实例代码,一起看看吧ldap_config = { 'lda
- 在Python中可以通过在属性变量名前加上双下划线定义属性为私有属性,如例子:#! encoding=UTF-8 class A:
- import threadingfrom time import sleepdef test_func(id): &n
- 在这篇文章中,我们将分析一个网络爬虫。网络爬虫是一个扫描网络内容并记录其有用信息的工具。它能打开一大堆网页,分析每个页面的内容以便寻找所有感
- use strict;use warnings;# Print all files in a directorysub print_file
- 1.关闭浏览器全部标签页driver.quit()2.关闭当前标签页(从标签页A打开新的标签页B,关闭标签页A)driver.close()
- 英文文档:classmethod(function)Return a class method for function.A class m
- asp采集常用的几个FUCTION如:利用流保存文件,利用fso检测文件是否存在,利用fso检测文件夹是否存在,保存文件,取得远程数据等1.
- 就是一个简单的python查询百度关键词排名的函数,以下是一些简介:1、UA随机2、操作简单方便,直接getRank(关键词,域名)就可以了
- 装饰器(Decorators)是 Python 的一个重要部分。简单地说:他们是修改其他函数的功能的函数。他们有助于让我们的代码更简短,也更
- 阅读上一章:Chapter 13 为文字指定样式Chapter 14 图片替换随着更多设计师与开发者开始使用标准(特别是CSS),每天都会有
- 制作这个播放器的目的是为了将下载下来的mp3文件进行随机或是顺序的播放。选择需要播放的音乐的路径,选择播放方式,经过测试可以完美的播放本地音
- 背景之前是用的是typora来写的文章,最近typora最近开始收费了,所以就不想用了,于是找到了一个替代品MarkText,感觉跟typo
- 在网页中,我们经常需要引用大量的javascript和css文件,在加上许多javascript库都包含debug版和经过压缩的releas
- 在SQL Server数据库中,有min server memory与max server memory两个内存选项。数据库管理员合理设置这