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)


相关推荐

  • pycharm使用小技巧_pycharm基本使用方法

    pycharm使用小技巧_pycharm基本使用方法Pycharm作为Python开发最常用的IDE之一,不仅兼容性好,而且功能也相当丰富,比如调试、语法高亮、智能提示等等功能,它还支持web开发框架比如Django等,当你熟悉了它之后,开发效率是相当之高的。但对于新手来说,Pycharm功能丰富的同时也是一把双刃剑,有的小伙伴刚上手之后看到一堆的英文界面难免会懵逼,哈哈哈,没有关系,今天博主就来教大家一些Pycharm最常用的技巧,以及一些pycharm常用的快捷键,让你快速上手Python开发中最常用的IDEPycharm,跟上老司机的车速!一

  • u12a u12s_utf8mb3

    u12a u12s_utf8mb31.u8就是unsignedchar,是8位无符号char类型的值/*!<Signedintegertypes*/typedefsignedcharint8_t;typedefsignedshortint16_t;typedefsignedlongint32_t;/*!<Unsignedinteger…

    2022年10月15日
  • 背单词的方法

    背单词的方法

  • Android之ViewPager的简单使用[通俗易懂]

    Android之ViewPager的简单使用[通俗易懂]AndroidViewPager的简单使用Android3.0后引入的一个UI控件——ViewPager(视图滑动切换工具)功能:通过手势滑动可以完成View的切换,一般是用来做APP的引导页或者实现图片轮播,因为是3.0后引入的,如果想在低版本下使用,就需要引入v4兼容包ViewPager就是一个简单的页面切换组件,我们可以往里面填充多个View,然后我们可以左右滑动,从而切换不同的View,我们可以通过setPageTransformer()方法为我们的ViewPager设置切换时

  • TCP、UDP数据包大小的限制[通俗易懂]

    TCP、UDP数据包大小的限制[通俗易懂]1、概述首先要看TCP/IP协议,涉及到四层:链路层,网络层,传输层,应用层。  其中以太网(Ethernet)的数据帧在链路层  IP包在网络层  TCP或UDP包在传输层  TCP或UDP中的数据(Data)在应用层  它们的关系是数据帧{IP包{TCP或UDP包{Data}}}  不同的协议层对数据包有不同的称谓,在传输层叫做段(se

  • 推荐安卓开发神器(里面有各种UI特效和实例)

    推荐安卓开发神器(里面有各种UI特效和实例)

发表回复

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

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