- 浏览: 329998 次
- 性别:
- 来自: 西安
文章分类
最新评论
-
hufangxian:
估计面试官都被你的回答搞晕了。
抽象类和接口的理解 -
alvin198761:
需要了解多态和设计模式,了解一些框架的原理,你就知道这东东真正 ...
抽象类和接口的理解 -
sangei:
谢谢楼上提醒,希望慢慢能有所进步。
抽象类和接口的理解 -
jackra:
书背的不错可以看看设计模式如何使用抽象类
抽象类和接口的理解 -
ptsd:
lag(column_x,number1,XX)用来返回当前数 ...
【转】oracle的LAG和LEAD分析函数
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 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, LOB
s, 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;
发表评论
-
SQL中on条件与where条件的区别
2016-02-17 10:26 686/* 标题:SQL中 -
mysql update注意
2016-01-18 16:16 566drop table a; CREATE TABLE IF ... -
Oracle中JOB的创建方法以及一个细节的探究(转)
2012-10-15 16:35 1240在Oracle中可以使用JOB来实现一些任务的自动化执行,类似 ... -
oracle里的job和schedule有什么区别(转)
2012-10-15 16:31 1297210g 中新推出的SCHEDULER可能确实会让很多初接触的朋 ... -
procedure的加密
2012-08-06 14:34 749wrap iname=eptapi.pac oname=ept ... -
sql plus中执行pl/sql
2012-03-23 00:18 0declare v_string varc ... -
(转)oracle 带输出参数存储过程
2012-03-23 00:13 0执行带输出参数的存储过程 SQL> var c v ... -
(转)Oracle:Authid Current_User的使用
2012-03-22 23:53 0我们知道,用户拥有的role权限在存储过程是不可用的。遇 ... -
在pl/sql里select dba_jobs为什么不成功
2012-03-22 23:52 0PROCEDURE TEST1 ( PROCEDR ... -
(转)Oracle 中的exception——(Raise)
2012-03-22 23:30 15121.GOTO,主要用于跳转,但会打乱我们的程序逻辑,一般不使用 ... -
(转)批量绑定—BULK COLLECT
2012-03-22 23:26 2873在 PL/SQL 编码中,经常会从表中获取结果集,然后进行一些 ... -
(转)PL/SQL --> 动态SQL的常见错误
2012-03-22 18:31 1405--============================ ... -
Oracle SELECT with an array using an IN
2012-01-18 17:03 1290Oracle SELECT with an ... -
pl/sql 实现类似于split 的分割字符串的功能
2012-01-12 23:55 61171、先建立一个包,定义数组类型:CREATE OR REP ... -
c#向pl/sql传递数组参数
2012-01-12 23:48 38661.OracleParameterpout1=OracleDb ... -
【实验】【PROCEDURE】一个最简单的oracle存储过程"proc_helloworld"
2011-08-08 09:51 1226本文通过一个最简单的oracle存储过程"proc_ ... -
创建oracle job
2011-07-27 17:20 852oracle job简介 主要的使用情景 定时在后台执行 ...
相关推荐
Oracle中EXECUTE IMMEDIATE用法
execute immediate 用法研究 为初学者打造。
用execute immediate 执行备份恢复SQLSVR数据库
EXECUTE IMMEDIATE将不会提交一个DML事务执行,应该显式提交 如果通过EXECUTE IMMEDIATE处理DML命令, 那么在完成以前需要显式提交或者作为EXECUTE IMMEDIATE自己的一部分. 如果通过EXECUTE IMMEDIATE处理DDL命令,
NULL 博文链接:https://wushuangyan26.iteye.com/blog/1306094
oracle动态sql之EXECUTE IMMEDIATE.docx
在Oracle中若删除一个不存在的表,如 “DROP TABLE tableName”,则会提示: ORA-00942:表或视图不存在 若在程序中执行该语句则会报异常,这就需要...EXECUTE IMMEDIATE 'DROP TABLE tableName'; END IF; END; 在Oracle中
OCILIB是一个便携式Oracle驱动程序,可提供非常快速和可靠地访问Oracle数据库。它提供了一个丰富,功能齐全,并易于使用的API 。OCILIB
整理的Oracle数据库游标使用大全 ...较好的PL/SQL程序设计是在PL/SQL块中使用象DBMS_SQL这样的内建包或执行EXECUTE IMMEDIATE命令建立动态SQL来执行DDL命令,PL/SQL编译器保证对象引用以及用户的权限。。。。。。
execute immediate v_sql into v_count; p_OutRecordCount := v_count; ----执行分页查询 v_heiRownum := p_PageNo * p_PageSize; v_lowRownum := v_heiRownum - p_PageSize + 1; v_sql := 'SELECT * FROM ...
如果需要在pl/sql 程序中使用动态sql,建议使用execute immediate 对于非常大的表,考虑使用表和索引的分区 如果需要在创建索引的时候减少所需时间,可以在会话集设置比较大的sort_area_size 考虑更多的使用decode...
存储过程.txt EXECUTE IMMEDIATE.txt
execute immediate 'create table newTypes(id integer primary key,name nvarchar2(200))'; --create table ... PL/SQL中不能直接使用数据定义语言,而动态sql中允许 sql_str:='select id, name from producttype...
ORACLE 培训 动态SQL编写 EXECUTE IMMEDIATE用法 DBMS_SQL用法
execute immediate str_sql into tabtcn; --动态执行DDL语句
动态PL/SQL用法例子 begin execute immediate 'create table test_qiu(id number)'; end;
在ORACLE数据库杀掉会话进程有三种方式: 1:ALTER SYSTEM KILL SESSION 关于KILL SESSION Clause ,官方文档描述alter system kill session实际上...Specify IMMEDIATE to instruct Oracle Database to roll back on
8.6.1 EXECUTE IMMEDIATE 立即执行语句...................... 278 8.6.2 PREPARE 准备语句 ................................... 278 8.6.3 EXCUTE 执行语句 .................................... 279...
linux oracle 12c 自动启动.docx Oracle12c2 开机自动启动 触发器 SQL> create or replace trigger open_pbds ... 4 execute immediate 'ALTER PLUGGABLE DATABASE ALL OPEN'; 5 end open_pdbs; 6 /