search
HomeDatabaseMysql Tutorial如何使用动态SQL语句?
如何使用动态SQL语句?Jun 07, 2016 pm 03:22 PM
sqlintroduceusedynamichowstatement

这里只介绍动态SQL的使用。关于动态SQL语句的语法,参见:http://blog.csdn.net/chiclewu/article/details/16097133 1.什么是时候需要使用动态SQL? SQL文本在编译时是未知的。 例如,SELECT语句包含的标识符(如表名)在编译时是未知的,或者WHERE子句的条件

这里只介绍动态SQL的使用。关于动态SQL语句的语法,参见:http://blog.csdn.net/chiclewu/article/details/16097133

1.什么是时候需要使用动态SQL?

SQL文本在编译时是未知的。

例如,SELECT语句包含的标识符(如表名)在编译时是未知的,或者WHERE子句的条件数量在编译时是未知。

静态SQL不支持

例如,在PL/SQL中用静态SQL只能执行查询以及DML语句。如果想要执行DDL语句,只能使用动态SQL。

当让使用静态SQL,也有它的好处:

编译成功验证了静态SQL语句引用有效的数据库对象和访问这些对象的权限

编译成功创建了模式对象的依赖关系

2.EXECUTE IMMEDIATE语句

EXECUTE IMMEDIATE语句的意思是使用本地动态SQL处理大多数动态SQL语句。

如果动态SQL语句是自包含的(也就是说,它的绑定参数没有占位符,并且结果不可能返回错误),则EXECUTE IMMEDIATE语句不需要子句。

如果动态SQL语句包行占位符绑定参数,每个占位符在EXECUTE IMMEDIATE语句的子句中必须有一个相应的绑定参数,具体如下:

如果动态SQL语句是一个最多只能返回一行的SELECT语句,OUT绑定参数放置在INTO子句,IN绑定参数放置在USING子句。如果动态SQL语句是一个可以返回多行的SELECT语句,OUT绑定参数放置在BULK COLLECT INTO子句,IN绑定参数放置在USING子句。如果动态SQL语句是一个除了SELECT以外的其他DML语句,且没有RETURNING INTO子句,所有的绑定参数放置在USING子句中。如果动态SQL还语句一个匿名PL/SQL块或CALL语句,把所有的绑定参数放置在USING子句中。

如果动态SQL语句调用一个子程序,请确保:

    每个对应子程序参数占位符的绑定参数与子程序参数具有相同的参数模式和兼容的数据类型。

    绑定参数不要有SQL不支持的数据类型(例如,布尔类型,关联数组,以及用户自定的记录类型)

    USING子句不能包含NULL字面量。如果想要在USING子句中使用NULL值,可以使用位初始化的变量或者函数显示将NULL转换成一个有类型的值。

    2.1动态SQL语句是一个最多只能返回一行的SELECT语句

    使用动态SQL语句返回单列,查询SCOTT的薪水:

    declare
    v_sql_text varchar2(1000);
    v_sal number;
    v_ename emp.ename%type := 'SCOTT';
    begin
    v_sql_text := 'select e.sal from emp e where e.ename = :ename';

    execute immediate v_sql_text
    into v_sal
    using v_ename;

    dbms_output.put_line(v_ename || ':' || v_sal);

    end;

    使用动态SQL返回一条记录,查询SCOTT的基本信息:

    declare
    v_sql_text varchar2(1000);
    v_ename emp.ename%type := 'SCOTT';
    vrt_emp emp%rowtype;
    begin
    v_sql_text := 'select * from emp e where e.ename = :ename';
    execute immediate v_sql_text
    into vrt_emp
    using v_ename;
    dbms_output.put_line(v_ename || '的基本信息:');
    dbms_output.put_line('工号:' || vrt_emp.empno);
    dbms_output.put_line('工资:' || vrt_emp.sal);
    dbms_output.put_line('入职日期:' || vrt_emp.hiredate);

    end;

    2.2动态SQL语句是一个可以返回多行的SELECT语句

    2.2.1只有一个占位符

    使用动态SQL语句返回多行记录,查询30部门的员工基本信息:

    declare
    v_sql_text varchar2(1000);
    v_deptno emp.deptno%type := 30;

    type nt_emp is table of emp%rowtype;
    vnt_emp nt_emp;
    begin
    v_sql_text := 'select * from emp e where e.deptno = :deptno';
    execute immediate v_sql_text bulk collect
    into vnt_emp
    using v_deptno;

    for i in 1 .. vnt_emp.count loop
    dbms_output.put_line(vnt_emp(i).ename || '的基本信息:');
    dbms_output.put_line('工号:' || vnt_emp(i).empno);
    dbms_output.put_line('工资:' || vnt_emp(i).sal);
    dbms_output.put_line('入职日期:' || vnt_emp(i).hiredate);
    dbms_output.put_line('');
    end loop;

    end

    2.2.2多个占位符

    查询20部门工资大于2000的员工基本信息:

    declare
    v_sql_text varchar2(1000);
    v_deptno emp.deptno%type := 20;
    v_sal number := 2000;

    type nt_emp is table of emp%rowtype;
    vnt_emp nt_emp;
    begin
    v_sql_text := 'select * from emp e where e.sal>:sal and e.deptno = :deptno';
    execute immediate v_sql_text bulk collect
    into vnt_emp
    using v_sal, v_deptno; --注意绑定多个变量时,绑定变量只与占位符位置有关,与占位符名称无关,

    for i in 1 .. vnt_emp.count loop
    dbms_output.put_line(vnt_emp(i).ename || '的基本信息:');
    dbms_output.put_line('工号:' || vnt_emp(i).empno);
    dbms_output.put_line('工资:' || vnt_emp(i).sal);
    dbms_output.put_line('入职日期:' || vnt_emp(i).hiredate);
    dbms_output.put_line('');
    end loop;

    注意:对于SQL文本,占位符名称是没有意义的,绑定变量与占位符名称无关,只与占位符的配置有关。即使有多个相同名称占位符,也需要每个占位符对应一个绑定变量。对于PL/SQL块,占位符名称是有意义的,相同名称的占位符,只需要第一个占位符绑定变量。

    2.3动态SQL语句是一个带有RETURNING子句的DML语句

    KING的工资增长20%,返回增长后的工资:

    eclare
    v_sql_text varchar2(1000);
    v_sal number;
    v_ename emp.ename%type := 'KING';
    begin

    v_sql_text := 'update emp e set e.sal= e.sal*1.2 where e.ename = :ename returning e.sal into :sal';

    execute immediate v_sql_text
    using v_ename
    returning into v_sal;

    dbms_output.put_line(v_ename || ':' || v_sal);

    end;

    注意:只有当v_sql_text语句有returning into子句时,动态SQL语句才能使用returning into子句。

    2.4给占位符传递NULL值

    2.4.1通过未初始化变量传递NULL值

    declare
    v_sql_text varchar2(1000);
    v_deptno emp.ename%type := 'ALLEN';
    v_comm emp.comm%type;

    begin
    v_sql_text := 'update emp e set e.comm = :comm where e.ename =:ename';
    execute immediate v_sql_text
    using v_comm, v_deptno;
    end;

    2.4.2通过函数将NULL值显式的转换成一个有类型的值

    declare
    v_sql_text varchar2(1000);
    v_deptno emp.ename%type := 'ALLEN';
    begin
    v_sql_text := 'update emp e set e.comm = :comm where e.ename =:ename';
    execute immediate v_sql_text
    using to_number(null), v_deptno;
    end;

    3.OPEN FOR语句

    PL/SQL引入OPEN FOR语句实际上并不是为了支持本地动态SQL,而是为了支持游标变量。现在它以一种极其优雅的方式实现了多行的动态查询。

    使用OPEN FOR语句来关联动态SQL语句的游标变量,在OPEN FOR语句的USING子句中,指定动态SQL语句每个占位符的绑定参数。

    使用FETCH语句获取运行时结果集。使用CLOSE语句关闭游标变量

    使用OPEN FOR语句查询出10部门的员工的基本信息:

    declare
    type rc_emp is ref cursor;
    vrc_emp rc_emp;

    v_sql_text varchar2(1000);
    v_deptno emp.deptno%type := 10;
    vrt_emp emp%rowtype;

    begin
    v_sql_text := 'select * from emp e where e.deptno=:deptno';

    open vrc_emp for v_sql_text
    using v_deptno;
    loop
    exit when vrc_emp%notfound;
    fetch vrc_emp
    into vrt_emp;

    dbms_output.put_line(vrt_emp.ename || '的基本信息:');
    dbms_output.put_line('工号:' || vrt_emp.empno);
    dbms_output.put_line('工资:' || vrt_emp.sal);
    dbms_output.put_line('入职日期:' || vrt_emp.hiredate);
    dbms_output.put_line('');

    end loop;
    close vrc_emp;

    end;

    4.重复的占位符名称

    如果在动态SQL语句重复占位符名称,要知道占位符关联绑定参数的方式依赖于动态语句的类型。

    如果执行的是一个动态SQL字符串,则必须为每一个占位符提供一个绑定参数,即使这些占位符是重复的。如果执行的是一个动态PL/SQL块,则必须为每一个唯一占位符提供一个绑定参数,即重复的占位符只需要提供一个绑定参数。

    4.1重复占位符的动态SQL字符串

    declare
    v_sql_text varchar2(1000);
    v_sal emp.sal%type := 4000;
    v_comm emp.comm%type;
    v_ename emp.ename%type := 'SCOTT';
    begin

    v_sql_text := 'update emp e set e.sal=:sal , e.comm = :sal*0.1 where e.ename =:ename returning e.comm into :comm ';

    execute immediate v_sql_text
    using v_sal, v_sal, in v_ename
    returning into v_comm;
    dbms_output.put_line(v_ename || '分红:' || v_comm);

    end;

    4.2重复占位符的动态PL/SQL块

    declare
    v_sql_text varchar2(1000);
    v_sal number;
    v_ename emp.ename%type := 'KING';
    begin

    v_sql_text := ' begin select e.sal,e.ename into :sal,:ename from emp e where e.ename =:ename; end;';

    execute immediate v_sql_text
    using out v_sal, in out v_ename;
    dbms_output.put_line(v_ename || ':' || v_sal);

    end;

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
如何在Go中使用命名管道?如何在Go中使用命名管道?May 11, 2023 pm 04:22 PM

命名管道是一种在操作系统中相对比较低级的进程通信方式,它是一种以文件为中介的进程通信方式。在Go语言中,通过os包提供了对命名管道的支持。在本文中,我们将介绍如何在Go中使用命名管道来实现进程间通信。一、命名管道的概念命名管道是一种特殊的文件,可以被多个进程同时访问。在Linux系统中,命名管道是一种特殊的文件类型,它们存在于文件系统的某个位置上,并且可以在

如何在Go中使用第三方库?如何在Go中使用第三方库?May 11, 2023 pm 03:30 PM

在Go语言中,使用第三方库是非常方便的。许多优秀的第三方库和框架可以帮助我们快速地开发应用程序,同时也减少了我们自己编写代码的工作量。但是如何正确地使用第三方库,确保其稳定性和可靠性,是我们必须了解的一个问题。本文将从以下几个方面介绍如何使用第三方库,并结合具体例子进行讲解。一、第三方库的获取Go语言中获取第三方库有以下两种方式:1.使用goget命令首先

如何在PHP中使用协程?如何在PHP中使用协程?May 12, 2023 am 08:10 AM

随着传统的多线程模型在高并发场景下的性能瓶颈,协程成为了PHP编程领域的热门话题。协程是一种轻量级的线程,能够在单线程中实现多任务的并发执行。在PHP的语言生态中,协程得到了广泛的应用,比如Swoole、Workerman等框架就提供了对协程的支持。那么,如何在PHP中使用协程呢?本文将介绍一些基本的使用方法以及常见的注意事项,帮助读者了解协程的运作原理,以

如何在Go中使用WebSocket?如何在Go中使用WebSocket?May 11, 2023 pm 04:17 PM

近年来,WebSocket技术已经成为了Web开发中不可或缺的一部分。WebSocket是一种在单个TCP连接上进行全双工通信的协议,它使得客户端和服务器之间的通信更加流畅和高效。如今,很多现代的Web应用程序都使用了WebSocket技术,例如实时聊天、在线游戏以及实时数据可视化等。Go语言作为一个现代的编程语言,自然也提供了很好的支持WebSock

如何在 Windows 11 中按需使用 OneDrive 的文件如何在 Windows 11 中按需使用 OneDrive 的文件Apr 14, 2023 pm 12:34 PM

<p>Windows 系统上的 OneDrive 应用程序允许您将文件存储在高达 5 GB 的云上。OneDrive 应用程序中还有另一个功能,它允许用户选择一个选项,是将文件保留在系统空间上还是在线提供,而不占用您的系统存储空间。此功能称为按需文件。在这篇文章中,我们进一步探索了此功能,并解释了有关如何在 Windows 11 电脑上的 OneDrive 中按需使用文件的各种选项。</p><h2>如何使用 On

如何在Go中使用音频处理?如何在Go中使用音频处理?May 11, 2023 pm 04:37 PM

随着音频处理在各种应用场景中的普及,越来越多的程序员开始使用Go编写音频处理程序。Go语言作为一种现代化的编程语言,具有优秀的并发性和高效率的特点,使用它进行音频处理十分方便。本文将介绍如何在Go中使用音频处理技术,包括读取、写入、处理和分析音频数据等方面的内容。一、读取音频数据在Go中读取音频数据有多种方式。其中比较常用的是使用第三方库进行读取,比如go-

如何在Go中使用嵌套结构?如何在Go中使用嵌套结构?May 11, 2023 pm 04:39 PM

在Go语言中,嵌套结构是一种非常常见的技术。通过将一个结构体嵌入到另一个结构体中,我们可以将复杂的数据模型分解成更小的部分,使其易于理解和维护。本篇文章将介绍如何在Go中使用嵌套结构以及一些最佳实践。一、定义嵌套结构首先,我们需要定义一个包含嵌套结构的结构体。下面的代码演示了如何定义一个包含Person结构体的Company结构体:typePersons

如何在Go中使用反转依赖?如何在Go中使用反转依赖?May 11, 2023 pm 03:39 PM

Go语言中的反转依赖是一种非常实用的技术,它可以帮助开发者更好地进行软件开发。在本文中,我们将详细介绍什么是反转依赖,并且演示如何在Go语言中使用它来优化软件。一、什么是反转依赖在传统的软件开发中,模块之间存在着依赖关系。一些模块被其他模块所依赖,而另一些模块则依赖于其他模块。这种依赖关系在软件中非常普遍,但同时也会带来很多问题。一旦一个模块的代码发生了变化

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft