`
sangei
  • 浏览: 336304 次
  • 性别: Icon_minigender_1
  • 来自: 西安
社区版块
存档分类
最新评论

execute immediate

阅读更多

EXECUTE IMMEDIATE Statement

The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance. For more information, see Chapter 7.

Syntax

Description of execute_immediate_statement.gif follows
Description of the illustration execute_immediate_statement.gif

Keyword and Parameter Description


bind_argument

An expression whose value is passed to the dynamic SQL statement, or a variable that stores a value returned by the dynamic SQL statement.


define_variable_name

A variable that stores a selected column value.


dynamic_string

A string literal, variable, or expression that represents a single SQL statement or a PL/SQL block. It must be of type CHAR or VARCHAR2, not NCHAR or NVARCHAR2.


INTO ...

Used only for single-row queries, this clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause.


record_name

A user-defined or %ROWTYPE record that stores a selected row.


RETURNING INTO ...

Used only for DML statements that have a RETURNING clause (without a BULK COLLECT clause), this clause specifies the bind variables into which column values are returned. For each value returned by the DML statement, there must be a corresponding, type-compatible variable in the RETURNING INTO clause.


USING ...

Specifies a list of input and/or output bind arguments. The parameter mode defaults to IN.

Usage Notes

Except for multi-row queries, the dynamic string can contain any SQL statement (without the final semicolon) or any PL/SQL block (with the final semicolon). The string can also contain placeholders for bind arguments. You cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.

You can place all bind arguments in the USING clause. The default parameter mode is IN. For DML statements that have a RETURNING clause, you can place OUT arguments in the RETURNING INTO clause without specifying the parameter mode, which, by definition, is OUT. If you use both the USING clause and the RETURNING INTO clause, the USING clause can contain only IN arguments.

At run time, bind arguments replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the USING clause and/or RETURNING INTO clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (TRUE, FALSE, and NULL). To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls to Dynamic SQL".

Dynamic SQL supports all the SQL datatypes. For example, define variables and bind arguments can be collections, LOBs, instances of an object type, and refs. Dynamic SQL does not support PL/SQL-specific types. For example, define variables and bind arguments cannot be Booleans or index-by tables. The only exception is that a PL/SQL record can appear in the INTO clause.

You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. You still incur some overhead, because EXECUTE IMMEDIATE re-prepares the dynamic string before every execution.

The string argument to the EXECUTE IMMEDIATE command cannot be one of the national character types, such as NCHAR or NVARCHAR2.

Examples

The following PL/SQL block contains several examples of dynamic SQL:

DECLARE
   sql_stmt    VARCHAR2(200);
   plsql_block VARCHAR2(500);
   emp_id      NUMBER(4) := 7566;
   salary      NUMBER(7,2);
   dept_id     NUMBER(2) := 50;
   dept_name   VARCHAR2(14) := 'PERSONNEL';
   location    VARCHAR2(13) := 'DALLAS';
   emp_rec     emp%ROWTYPE;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
   sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
   sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
   EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
   plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
   EXECUTE IMMEDIATE plsql_block USING 7788, 500;
   sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
      RETURNING sal INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
   EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
      USING dept_id;
   EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;
分享到:
评论

相关推荐

    Oracle中EXECUTE IMMEDIATE用法

    在Oracle数据库中,`EXECUTE IMMEDIATE`是一个非常强大的特性,允许在运行时动态执行SQL语句或PL/SQL块。这一功能自Oracle 8i版本的DBMS_SQL包引入以来,极大地增强了PL/SQL的灵活性和动态性。通过`EXECUTE ...

    execute immediate 用法研究

    execute immediate 用法研究 Execute Immediate 是 Oracle 中的一个功能强大且灵活的语句,它可以解析并马上执行动态的 SQL 语句或非运行时创建的 PL/SQL 块。 Execute Immediate 的目标是减小企业费用并获得较高的...

    动态SQL之EXECUTE IMMEDIATE

    本文将深入探讨“动态SQL之EXECUTE IMMEDIATE”,并结合标签“源码”和“工具”,阐述如何在实际开发中应用这一技术。 动态SQL主要分为两种形式:绑定变量(Bind Variables)和EXECUTE IMMEDIATE。绑定变量主要用于...

    用execute immediate 执行备份恢复SQLSVR数据库

    "execute immediate" 是Oracle数据库中的动态SQL执行语句,但在这里我们讨论的是SQL Server,它使用不同的命令来实现类似的功能。不过,我们可以理解这个描述是在尝试通过编程方式自动化SQL Server的备份恢复过程。...

    EXECUTE IMMEDIATE用法小结

    EXECUTE IMMEDIATE将不会提交一个DML事务执行,应该显式提交 如果通过EXECUTE IMMEDIATE处理DML命令, 那么在完成以前需要显式提交或者作为EXECUTE IMMEDIATE自己的一部分. 如果通过EXECUTE IMMEDIATE处理DDL命令,

    oracle动态sql之EXECUTE IMMEDIATE.docx

    Oracle 动态 SQL 之 EXECUTE IMMEDIATE Oracle 动态 SQL 中的 EXECUTE IMMEDIATE 语句是数据库中执行动态 SQL 语句或非运行时创建的 PL/SQL 块的重要工具。与 DBMS_SQL package 相比,EXECUTE IMMEDIATE 使用较...

    在Oracle中执行动态SQL

    EXECUTE IMMEDIATE的优点是可以减小企业费用并获得较高的性能,但是需要注意其使用技巧和限制。 Oracle中执行动态SQL可以使用动态游标或EXECUTE IMMEDIATE两种方法。动态游标可以实现灵活的SQL语句生成,而EXECUTE ...

    oracle动态函数执行[文].pdf

    动态SQL的核心在于`EXECUTE IMMEDIATE`语句,这使得我们能够在不知道具体SQL结构或者需要根据运行时条件来决定SQL语句的情况下,灵活地执行数据库操作。 首先,我们要理解`EXECUTE IMMEDIATE`的基本语法: ```sql ...

    Oracle过程中创建视图实例

    其中一个核心功能就是`EXECUTE IMMEDIATE`,它允许开发者在运行时构建并执行SQL语句。这在需要动态生成SQL查询或DML(数据操纵语言)操作时特别有用。本文将深入探讨如何在Oracle过程中使用`EXECUTE IMMEDIATE`来...

    ORCL总结.rar

    本篇文章将深入探讨标题“ORCL总结”所涵盖的两个核心概念:存储过程和EXECUTE IMMEDIATE语句,这两者在数据库编程中扮演着关键角色。 首先,我们来了解“存储过程”。存储过程是一组预先编译好的SQL语句,保存在...

    学习oracle动态函数执行.pdf

    在Oracle中,`EXECUTE IMMEDIATE`语句用于动态执行SQL或者PL/SQL代码,这对于处理不确定的数据操作或者根据用户输入构建查询非常有用。 首先,我们来看一个例子,这个例子展示了如何使用`EXECUTE IMMEDIATE`执行一...

    动态PL/SQL用法例子

    根据给定的信息,本文将详细解释动态PL/SQL的相关知识点,包括动态SQL的基本概念、如何使用`EXECUTE IMMEDIATE`语句创建表、插入数据以及查询数据等。 ### 动态SQL简介 在PL/SQL中,动态SQL指的是运行时才能确定其...

    Gbase8s动态sql的介绍

    在 Gbase8s 中,可以使用 execute immediate 语句来执行动态 SQL,execute immediate 需要在存储过程中使用。 例如,创建一个存储过程,可以用来创建表。以下是一个示例代码: create or replace procedure create...

    oracle动态函数执行.pdf

    动态SQL的核心在于`EXECUTE IMMEDIATE`语句,这使得开发者能够在不知道具体SQL结构或者需要根据运行时的条件来决定SQL行为的情况下编写代码。 在动态SQL中,`EXECUTE IMMEDIATE`的使用方式主要有以下几种: 1. **...

    Oracle过程中创建视图

    在Oracle中,我们可以通过存储过程中的`EXECUTE IMMEDIATE`语句动态执行SQL语句,包括创建视图。例如: ```sql CREATE OR REPLACE PROCEDURE P_View1 AS BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW V_View1...

    oracle-dynamic-SQL.rar_oracle_sql中dynamic用法

    动态SQL的实现主要通过两种方法:直接执行(EXECUTE IMMEDIATE)和PREPARE/EXECUTE组合。下面将详细讲解这两种方法及其应用。 1. 直接执行(EXECUTE IMMEDIATE): 这种方式是直接将SQL语句作为字符串传递给数据库...

    Oracle动态SQL之本地动态SQL的使用.pdf

    使用EXECUTE IMMEDIATE语句执行动态SQL非常简单,只需将要执行的动态SQL语句作为字符串赋值给一个变量,然后将该变量作为EXECUTE IMMEDIATE命令的参数即可执行。在处理动态SQL时,如果有需要绑定参数,则可以在...

    6. 动态SQL语句.ppt

    1. **EXECUTE IMMEDIATE语句**:这是执行大多数动态SQL操作的方式,包括DDL、DCL和DML语句,以及单行SELECT。它直接执行包含在字符串变量中的SQL命令。 ```sql DECLARE sql_stmt1 VARCHAR2(128); BEGIN sql_...

    oracle_dynamic_sql.rar_Dynamic SQL ORAC_oracle_oracle dynam_orac

    在Oracle中,动态SQL主要通过`EXECUTE IMMEDIATE`语句和`PREPARE`、`EXECUTE`、`COMMIT`等组合来实现。例如: ```sql DECLARE sql_statement VARCHAR2(200); BEGIN sql_statement := 'SELECT * FROM employees ...

Global site tag (gtag.js) - Google Analytics