Oracle 动态SQL「建议收藏」

Oracle 动态SQL「建议收藏」Oracle动态SQL一、动态SQL的简介1、定义静态SQL是指直接嵌入到PL/SQL块中的SQL语句。动态SQL是指运行PL/SQL块是动态输入的SQL语句。2、适用范围如果在PL/SQL块中需要执行DDL语句(create,alter,drop等)、DCL语句(grant,revoke等)或更加灵活的SQL语句,需要用到动态SQL。

大家好,又见面了,我是你们的朋友全栈君。Oracle 动态SQL

一、动态SQL的简介

1、定义

静态SQL是指直接嵌入到PL/SQL块中的SQL语句。

动态SQL是指运行PL/SQL块是动态输入的SQL语句。

2、适用范围

如果在PL/SQL块中需要执行DDL语句(create,alter,drop等)、DCL语句(grant,revoke等)或更加灵活的SQL语句,需要用到动态SQL。

3、静态与动态SQL的比较

1)、静态SQL是在编写PL/SQL块是直接嵌入的SQL语句,而动态SQL是在运行PL/SQL块时动态数据的SQL语句。

2)、静态SQL性能优于动态SQL。

4、动态SQL的处理方法

1)、适用execute immediate

execute immediate可以处理多数动态SQL操作,包括DDL语句、DCL语句、DML语句、及单行select语句(不能处理多行查询)。

2)、适用open-for,fetch和colse语句

为了处理动态的多行查询操作,必须要使用open-for打开游标,使用fetch循环提取数据,最终使用close关闭游标。

3)、使用批量动态SQL

9i新增,通过使用批量动态SQL,可以加快SQL语句处理,进而提高PL/SQL程序的性能。

二、处理非查询语句及单行查询语句

1、定义

处理非查询语句(DDL\DCL\DML等)及单行查询语句使用execute immediate

语法:

execute immediate dynamic_string

[into [define_name1 [,define_name2]…]

[using [in|out|in out] bind_name1 [,[in|out|in out] bingd_nane2]…]

[[returning|return] into bind_name1[,bind_name2]…];

其中dynamic_string用于指定存放SQL语句或PL/SQL块的字符串变量;

define用于指定存放单位查询结果的变量;

bind_name(in)用于指定存放被传递给动态SQL值得变量;

bind_name(out)用于指定存放动态SQL返回值得变量;

2、处理DDL操作

create or replace procedure drop_table(table varchar2) is

  v_sql varchar2(100);

begin

  v_sql:=’drop table ‘|| table_name;

  execute immediate v_sql;

end;

3、处理DCL操作

create or replace procudure grant_sys_priv(priv varchar2,username varchar2) is

  v_sql varchar2(100);

begin

  v_sql:=’grant ‘||priv||’ to ‘||username;

  execute immediate v_sql;

end;

4、处理DML操作

1)、处理无占位符合和returning子句的DML语句

declate

  v_sql varchar2(100);

begin

  v_sql:=’update emp set sql=sql*1.1 where deptno=30′;

  execute immediate v_sql;

end;

2)、处理包含占位符的DML语句

declate

  v_sql varchar2(100);

begin

  v_sql:=’update emp set sql=sql*(1+:percent/100) where deptno=:dno’;

  execute immediate v_sql using &1,&2;

end;

3)、处理包含returning子句的DML语句

说明:使用execute immediate处理带有returning子句的DML语句时,只能处理作用在单行上的DML语句;

如果DML语句作用在多行上,则必须使用bulk子句,此种情况下之后讲述。

declare

  salary number(6,2);

  v_sal varchar2(100);

begin

  v_sql:=’update emp set sal=sal*(1+:percent/100) where empno=:eno returning sal into :salary’;

  execute immediate v_sql using &1,&2 returning into salary;

  dbms_output.put_line(‘新工资:’||salary);

end;

5、处理单行查询

说明:execute immediate不能处理多行查询。

declare

  v_sql varchar2(100);

  wmp_record emp%rowtype;

begin

  v_sql:=’select * from emp where empno=:eno’;

  execute immediate v_sql into wmp_record using &1;

  dbms_output.put_line(‘雇员’||emp_record.ename||’的工资’||emp_record.sal);

end;

三、处理多行查询语句

说明:需要通过游标来完成

declare

  type empcurtyp is ref cursor;

  emp_cv empcurtyp;

  emp_record emp%rowtype;

  v_sal varchar2(100);

begin

  v_sql:=’select * from emp where deptno=:dno’;

  open emp_cv for v_sql using &dno;

  loop

    fetch emp_cv into emp_record;

    exit when emp_cv%notfound;

    dbms_output.put_line(‘雇员名:’||emp_record.ename||’工资:’||emp.record.sal);

  end loop;

  close emp_cv;

wnd;

四、在动态SQL中使用bulk子句

1、概述

在动态SQL中使用BULK子句为9i新增特征,BULK子句实际是动态SQL语句将变量绑定为集合元素,

集合类型可以是PL/SQL所支持的索引表、嵌套表和VARRAY。但集合元素必须使用SQL数据类型

(例如number、char等),而不能使用PL/SQL数据类型(例binary_integer、boolean等)。

9i开始有三种语句支持BULK子句,execute immediate、fetch和forall,下面逐一讲述。

2、在execute immediate语句中使用bulk子句

1)、概述

通过在execute immediate语句中使用bulk子句可以处理作用在多行上的动态DML返回子句,和多行查询语句。

2)、语法

execute immediate dynamic_name

[bulk collect into define_name[,define_name…]]

[using bind_name[,bind_name…]]

[{returning|return} bulk collect into return_name[,return_name…]];

其中dynamic_name用于指定存放动态SQL语句的字符串变量;

define_name用于指定存放查询结果的集合变量;

bind_name指定绑定变量(存放传递给动态SQL的数据);

return_name指定接收returning子句返回结果的集合变量;

3)、处理作用在多行上的动态DML语句返回子句

declare

  type ename_table_type is table of emp.ename%type index by binary_integer;

  type sal_table_type is table of emp.sal%type index by binary_integer;

  ename_table ename_table_type;

  sal_table sal_table_type;

  v_sql varchar2(100);

begin

  v_sql:=’update emp set sal=sal*(1+:percent/100) where deptno=:dno returning ename,sal into :name,:salary’;

  execute immediate v_sql using &percent,&dno returning bulk collect into ename_table,sal_table;

  for i in 1..ename_table.count loop

    dbms_output.put_line(‘雇员’||ename_table(i)||’的新工资为’||sal_table(i));

  end loop;

end;

4)、处理多行查询语句

declare

  type ename_table_type is table of emp.ename%type index by binary_integer;

  ename_table ename_table_type;

  v_sal varchar2(100);

begin

  v_sal:=’select ename from emp where deptno=:dno’;

  execute immediate v_sql bulk collect into ename_table using &dno;

  for i in 1..ename_table.count loop

    dbms_output.put_line(ename_table(i));

  end loop;

end;

3、在fetch语句中使用bulk子句。

1)、概述

在原来的open-for,fetch,close语句处理动态多行查询语句时,默认每次提取单行数据,循环提取。

通过在fetch语句中引入bulk子句,可以一次提取所有数据。

2)、语法

fetch dynamic_cursor

bulk collect into define_name[,define_name…];

3)、处理多行查询语句

declare

  type empcurtyp is ref cursor;

  emp_cv empcurtyp;

  type ename_table_type is table of emp.ename%type index by binary_integer;

  ename_table ename_table_type;

  v_sql varchar2(100);

begin

  v_sql:=’select ename from emp where job=:title’;

  open emp_cv for v_sql using ‘&job’;

  fetch emp_cv bulk collect into ename_table;

  for i in 1..ename_table.count loop

    dbms_output.put_line(ename_table(i));

  end loop;

  close emp_cv;

end;

4、在forall语句中使用bulk子句。

1)、概述

使用forall语句,可允许在动态SQL语句中为输入变量同时提供多个数据,

但forall语句只适用于动态的insert\update\delete语句,

不适用于动态select语句,并且forall语句是和execute immediate结合使用的。

2)、语法

forall index in lower bound…upper bound

execute immediate dynamic_name

using bind_name | bind_name(index) [,bind_name | bind_name(index)]…

[{returning | return} bulk collect into bind_name[,bind_name…]];

3)、处理作用在多行上的动态DML语句返回子句

declare

  type ename_table_type is table of emp.ename%type;

  type sal_table_type is table of emp.sal%type;

  ename_table ename_table_type;

  sal_table sal_table_type;

  v_sal varchar2(100);

begin

  ename_table:=ename_table_type(‘scott’,’smith’,’clark’);

  v_sql:=’update emp set sal=sal*1.1 where ename=:1 returning sal into :2′;

  forall i in 1..ename_table.count

    execute immediate v_sql using ename_table(i) returning bulk collect into v_sal;

    for j in 1..ename_table.count loop

      dbms_output.put_line(‘雇员’||ename_table(i)||’新工资为’||sal_table(j));

    end loop;

end;

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/152530.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • Centos 6.9_centos iptables

    Centos 6.9_centos iptables规则集类型hash:net#创建网络规则集(可以是是网段或者IP)hash:net,port#创建网络规则集,带端口,基础用法ipsetcreatefwhash:net#创建规则集ipsetaddfw10.0.0.0/24#给新加的规则添加IP段ipsetdelfw10.68.22.1/24#删除IPi…

  • .net 调用java WebService简单教程

    .net 调用java WebService简单教程java滴WebService配置比较复杂tomcat+jdk+cxf+spring+(strtus)看你心情吧==首先·创建一个··WebProject把cxf里面的lib再再里面的库复制到你的工程下···我java菜吖··不知道哪些必须滴···懒人全都放进去额接着开始写代码啦··packagecom.ws;importjavax.jws.WebService;@WebServicepublicinterfaceIHello{ publicStringHelloWord(

  • poj1195(二维树状数组)

    poj1195(二维树状数组)

  • python设置时间过期改变状态_Python Redis设置过期时间「建议收藏」

    python设置时间过期改变状态_Python Redis设置过期时间「建议收藏」默认情况下,某个键的存在是没有时效性的,即在明确删除该键之前,该键会一直有效。当然在创建字符串数据时是可以指定时效的,也可以在键创建后修改时效属性,如让该键的有效期为5秒,到了时间该键便会自动被删除。下面的代码演示了一个键在设定时效后自动被删除的情况:importtime,redisr=redis.Redis(host=’10.20.17.13′,port=6379,db=0)…

  • Eclipse SVN冲突详细解决方案「建议收藏」

    Eclipse SVN冲突详细解决方案「建议收藏」大家一起开发,难免有时会同时修改同一个文件,这样就要学会解决冲突。当大家更新代码,发现以下情况的时候,就说明你的修改的文件和服务器的文件产生了冲突(一般是别人也改了同一个文件)。1)和服务器有冲突的文件:2)点击Update以后,如果出现以下情况(出现四个文件),就说明需要解决冲突。如何解决冲突:出现文件冲突的时候:你有四个选择:1以我修改的为准,不管服务

    2022年10月14日
  • Linux使用SCP进行文件传输

    Linux使用SCP进行文件传输本章内容基于CentOS6.5(32位)scp就是securecopy,是用来进行远程文件拷贝的。数据传输使用ssh,并且和ssh使用相同的认证方式,提供相同的安全保证。scp不需要安装额外的软件,使用起来简单方便,安全可靠且支持限速参数但是它不支持排除目录为了学习SCP命令我创建了3个Linux虚拟机,其ip地址分别位192.168.132.130、192.168.132.1…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号