`

动态sql

 
阅读更多

        一般的PL/SQL程序设计中,在DML和事务控制的语句中可以直接使用SQL,但是DDL语句及系统控制语句却不能在PL/SQL中直接使用,要想实现在PL/SQL中使用DDL语句及系统控制语句,可以通过使用动态SQL来实现。

  首先我们应该了解什么是动态SQL,在Oracle数据库开发PL/SQL块中我们使用的SQL分为:静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的,执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的,如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理,只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。

  Oracle中动态SQL可以通过本地动态SQL来执行,也可以通过DBMS_SQL包来执行。下面就这两种情况分别进行说明:

  一、本地动态SQL

  本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的。

  1、本地动态SQL执行DDL语句:

  需求:根据用户输入的表名及字段名等参数动态建表。

create or replace procedure proc_test
(
    table_name 
in varchar2,      --表名
    field1 in varchar2,          --字段名
    datatype1 in varchar2,       --字段类型
    field2 in varchar2,          --字段名
    datatype2 in varchar2        --字段类型
as 
    str_sql 
varchar2(500);
begin 
    str_sql:
= 'create table'||' '||table_name||'('||field1||' '||datatype1||','||field2||' '||datatype2||')';
    
execute immediate str_sql;   --动态执行DDL语句
    exception when others then 
            
null;
end

   以上是编译通过的存储过程代码。下面执行存储过程动态建表。

 SQL> execute proc_test('dinya_test','id','number(8) not null','name','varchar2(100)');

       PL
/SQL procedure successfully completed

  SQL
> desc dinya_test;
       Name Type          Nullable 
Default Comments 
       
---- ------------- -------- ------- --------   
       ID   NUMBER(8)

       NAME 
VARCHAR2(100) Y

  SQL
> 

    到这里,就实现了我们的需求,使用本地动态SQL根据用户输入的表名及字段名、字段类型等参数来实现动态执行DDL语句。

   2、本地动态SQL执行DML语句。

  需求:将用户输入的值插入到上例中建好的dinya_test表中。

create or replace procedure proc_insert
(
    id 
in number,                                 --输入序号
    name in varchar2                              --输入姓名
as 
    str_sql 
varchar2(500);
begin 
    str_sql:
='insert into dinya_test values(:1,:2)';
    
execute immediate str_sql using id,name;      --动态执行插入操作
    exception when others then 
            
null;
end

  执行存储过程,插入数据到测试表中。

  SQL> execute proc_insert(1,'dinya');
       PL
/SQL procedure successfully completed
  SQL
> select * from dinya_test;
        ID      NAME
        
1       dinya 

  在上例中,本地动态SQL执行DML语句时使用了using子句,按顺序将输入的值绑定到变量,如果需要输出参数,可以在执行动态SQL的时候,使用RETURNING INTO 子句,如:

declare
    p_id 
number:=1;
    v_count 
number;
begin 
    v_string:
='select count(*) from table_name a where a.id=:id';
    
execute immediate v_string returning into v_count using p_id;  
end;

     二、使用DBMS_SQL包

  使用DBMS_SQL包实现动态SQL的步骤如下:
    A、先将要执行的SQL语句或一个语句块放到一个字符串变量中。
    B、使用DBMS_SQL包的parse过程来分析该字符串。
    C、使用DBMS_SQL包的bind_variable过程来绑定变量。
    D、使用DBMS_SQL包的execute函数来执行语句。

  1、使用DBMS_SQL包执行DDL语句

  需求:使用DBMS_SQL包根据用户输入的表名、字段名及字段类型建表。

create or replace procedure proc_dbms_sql
(
    table_name 
in varchar2,       --表名
    field_name1 in varchar2,      --字段名
    datatype1 in varchar2,        --字段类型
    field_name2 in varchar2,      --字段名
    datatype2 in varchar2         --字段类型
)as
    v_cursor 
number;              --定义光标
    v_string varchar2(200);       --定义字符串变量
    v_row number;                 --行数
begin
    v_cursor:
=dbms_sql.open_cursor;      --为处理打开光标
    v_string:='create table'||' '||table_name||'('||field_name1||' '||datatype1||','||field_name2||' '||datatype2||')';
    dbms_sql.parse(v_cursor,v_string,dbms_sql.native);    
--分析语句
    v_row:=dbms_sql.execute(v_cursor);   --执行语句,动态SQL执行DDL时可以不写
    dbms_sql.close_cursor(v_cursor);     --关闭光标
    exception when others then
            dbms_sql.close_cursor(v_cursor);  
--关闭光标
            raise;
end

   以上过程编译通过后,执行过程创建表结构:

SQL> execute proc_dbms_sql('dinya_test2','id','number(8) not null','name','varchar2(100)');

           PL
/SQL procedure successfully completed

SQL
> desc dinya_test2;
           Name Type          Nullable 
Default Comments 
           
---- ------------- -------- ------- -------- 
           ID   NUMBER(8)                               
          NAME 
VARCHAR2(100) Y                         

SQL
> 

    2、使用DBMS_SQL包执行DML语句

  需求:使用DBMS_SQL包根据用户输入的值更新表中相对应的记录。

  查看表中已有记录:

SQL> select * from dinya_test2;
        ID NAME
        
1 Oracle
        
2 CSDN
        
3 ERP
SQL
> 

   建存储过程,并编译通过:

create or replace procedure proc_dbms_sql_update
(
    id 
number,
    name 
varchar2
)
as
    v_cursor 
number;            --定义光标
    v_string varchar2(200);   --字符串变量
    v_row number;               --行数
begin
    v_cursor:
=dbms_sql.open_cursor;    --为处理打开光标
    v_string:='update dinya_test2 a set a.name=:p_name where a.id=:p_id';
    dbms_sql.parse(v_cursor,v_string,dbms_sql.native);     
--分析语句
    dbms_sql.bind_variable(v_cursor,':p_name',name);       --绑定变量
    dbms_sql.bind_variable(v_cursor,':p_id',id);                 --绑定变量
    v_row:=dbms_sql.execute(v_cursor);            --执行动态SQL
    dbms_sql.close_cursor(v_cursor);                                --关闭光标
    exception when others then
            dbms_sql.close_cursor(v_cursor);                        
--关闭光标
            raise;
end

   执行过程,根据用户输入的参数更新表中的数据:

SQL> execute proc_dbms_sql_update(2,'csdn_dinya');

        PL
/SQL procedure successfully completed

SQL
> select * from dinya_test2;
        ID NAME
        
1 Oracle
        
2 csdn_dinya
        
3 ERP
SQL
> 

    执行过程后将第二条的name字段的数据更新为新值csdn_dinya。这样就完成了使用dbms_sql包来执行DML语句的功能。

   使用DBMS_SQL中,如果要执行的动态语句不是查询语句,使用DBMS_SQL.Execute或DBMS_SQL.Variable_Value来执行,如果要执行动态语句是查询语句,则要使用DBMS_SQL.define_column定义输出变量,然后使用DBMS_SQL.Execute, DBMS_SQL.Fetch_Rows, DBMS_SQL.Column_Value及DBMS_SQL.Variable_Value来执行查询并得到结果。 
  
        总结说明:

  在Oracle开发过程中,我们可以使用动态SQL来执行DDL语句、DML语句、事务控制语句及系统控制语句。但是需要注意的是,PL/SQL块中使用动态SQL执行DDL语句的时候与别的不同,在DDL中使用绑定变量是非法的(bind_variable(v_cursor,':p_name',name)),分析后不需要执行DBMS_SQL.Bind_Variable,直接将输入的变量加到字符串中即可。另外,DDL是在调用DBMS_SQL.PARSE时执行的,所以DBMS_SQL.EXECUTE也可以不用,即在上例中的v_row:=dbms_sql.execute(v_cursor)部分可以不要。


        转:http://dev.yesky.com/187/2029687.shtml

分享到:
评论

相关推荐

    hibernate实现动态SQL查询

    本篇文章主要探讨如何利用Hibernate实现动态SQL查询,结合XML配置和FREEMARKER模板引擎来生成执行的SQL语句。 一、Hibernate简介 Hibernate作为一款强大的持久层框架,它简化了Java应用程序与数据库之间的交互。...

    kettel循环执行动态sql

    标题中的“Kettle循环执行动态SQL”指的是使用Pentaho Data Integration(Kettle)工具来设计数据集成工作流,其中包含动态生成并执行SQL语句的步骤。在数据处理和ETL(Extract, Transform, Load)过程中,动态SQL是...

    动态SQL 并且把返回的值赋给变量

    ### 动态SQL 并且把返回的值赋给变量 在SQL Server中,动态SQL是一种强大而灵活的工具,允许开发人员根据运行时条件构建并执行SQL查询。本文将详细介绍如何利用`sp_executesql`来执行动态SQL,并重点讨论如何将执行...

    静态、动态sql及各种游标

    静态、动态SQL及各种游标 静态SQL和动态SQL是两种不同的SQL语句执行方式,分别应用于不同的场景中。静态SQL是指在PL/SQL中直接运行的SQL语句,没有什么特别之处。动态SQL则是指利用EXECUTE IMMEDIATE语句执行的SQL...

    Gbase8s动态sql的介绍

    Gbase8s 动态 SQL 介绍 Gbase8s 动态 SQL 介绍 在 Gbase8s 中,动态 SQL 是一种特殊的 SQL 语句形式,它可以在执行时动态改变 SQL 语句的内容,不需要预先知道每个 SQL 语句的确切内容。与静态 SQL 不同,静态 SQL...

    mybatis 动态sql及参数传递

    在实际开发过程中,我们往往需要编写复杂的SQL语句,拼接稍有不注意就会导致错误,Mybatis给开发者提供了动态SQL,大大降低了拼接SQL导致的错误。 动态标签 if标签 if标签通常用那个胡where语句,update语句,insert...

    本地动态SQL的开发

    ### 本地动态SQL的开发 #### 一、动态SQL的概念 动态SQL是在程序运行时构建并执行的SQL语句,其内容无法在编译时完全确定。与静态SQL(如常见的`UPDATE`语句)不同,动态SQL允许开发者在运行时根据实际情况构建...

    SQLServer动态SQL语句的用法

    SQL Server 动态 SQL 语句的用法 SQL Server 中的动态 SQL 语句是一种灵活的查询方式,它可以根据不同的情况生成不同的 SQL 语句。动态 SQL 语句可以用来实现复杂的业务逻辑,提高查询效率和灵活性。 普通 SQL ...

    mybatis之动态SQL

    动态 SQL 是 MyBatis 的一大特色,它允许我们在运行时根据条件构建 SQL 语句,极大地提高了代码的可维护性和灵活性。 动态 SQL 在 MyBatis 中主要通过 XML 映射文件或者注解来实现。在 XML 映射文件中,我们可以...

    DB2游标及动态SQL

    ### DB2游标及动态SQL详解 #### 一、DB2游标概念与使用 **1.1 游标简介** 在DB2中,游标是一种用于遍历查询结果集的强大工具。它允许用户一行一行地处理数据,这对于需要逐行处理数据的应用场景非常有用。游标通常...

    ibatis动态SQL标签用法

    iBatis动态SQL标签用法 iBatis是Java持久层框架,提供了动态SQL标签来实现动态查询。动态SQL标签可以根据不同的条件生成不同的SQL语句,从而提高查询效率和灵活性。 动态SQL片段 iBatis提供了动态SQL片段的功能,...

    动态SQL与绑定变量

    动态SQL与绑定变量是数据库编程中的重要概念,尤其在Oracle数据库中被广泛应用。动态SQL允许在运行时构建和执行SQL语句,而绑定变量则是在动态SQL中用于替代具体值的占位符,使得SQL语句更为灵活和高效。 ### 1. ...

    《kettle中实现动态SQL查询》博客文章示例代码

    本文将深入探讨如何在Kettle中实现动态SQL查询,这对于处理复杂的数据库操作和数据迁移至关重要。我们将讨论问号占位符和命名参数这两种方法,并分析它们的区别。 首先,让我们理解什么是动态SQL查询。在传统的SQL...

    一款IDEA插件,可以实现生成Mysql的动态SQL,适合新手使用mybatis来写项目,加快项目落地周期.zip

    mybatis动态sql 1.什么是动态SQL? Mabits是一个Java持久化框架,它提供了动态SQL的功能。动态SQL是一种根据不同条件动态生成SQL语句的技术。在Mabits中,动态SQL通常是通过使用一组特殊的标签和代码块来实现的,...

    sql语句、动态SQL语句基本语法

    SQL(Structured Query Language)是用于管理和操作...综上所述,掌握SQL语句和动态SQL的基本语法对于任何IT从业者来说都至关重要,无论是数据库管理员还是开发者,都需要熟练运用这些技能来高效地操作和管理数据库。

    学习SQL应知道的动态SQL语句基本语法 mssql

    在SQL世界中,动态SQL(Dynamic SQL)是一种强大的技术,特别是在复杂的数据库操作和程序设计中。在Microsoft SQL Server(MSSQL)环境下,掌握动态SQL的基本语法是提升SQL编程能力的关键。下面,我们将深入探讨动态...

    SpringBoot+JPA+Freemarker 生成动态SQL

    本篇将详细讲解如何利用这些技术来生成动态SQL。 首先,SpringBoot是Spring框架的一个轻量级版本,它简化了配置,提供了一种快速开发全新Spring应用的方式。SpringBoot集成了大量的常用库,如数据访问、安全、缓存...

    Oracle中动态SQL详解

    ### Oracle中动态SQL详解 #### 一、动态SQL概述与特点 在Oracle数据库中,动态SQL是一种非常重要的技术,尤其对于开发人员来说更是必不可少的一部分。动态SQL是指在运行时构建并执行的SQL语句,与静态SQL(在编译...

    hibernate增删改查和动态sql

    “hibernate增删改查和动态sql”这个标题涵盖了两个主要的Hibernate使用场景。首先,"增删改查"(CRUD操作)是任何数据库操作的基础,包括创建(Create)、读取(Read)、更新(Update)和删除(Delete)。在...

Global site tag (gtag.js) - Google Analytics