1 EXECUTE IMMEDIATE
oracle中DBMS_SQL package包和EXECUTE IMMEDIATE都可以用来解析并执行动态SQL语句或非运行时创建的PL/SQL块,相比较而言,EXECUTE IMMEDIATE使用较简单,能够满足较常用的需要。
1.1 语法
EXECUTE IMMEDIATE v_sql [BULK COLLECT INTO 或INTO 返回值变量] [INTO 入参 1,.., out 出参1,..]。
说明:
1、v_sql为varchar2类型或clob(11g才支持),可以为DDL、DML等动态拼接的sql字符串。用在pl/sql代码中时,如果是varchar2类型,则长度不能大于32767(32K)。
2、v_sql为DML动态语句时,执行后不会提交,需要使用commit显式提交。如果为DDL命令,执行后则会提交所有之前改变的。
3、如果需要从动态sql返回值,则可以定义返回值变量,BULK COLLECT INTO返回多行值,此时定义的变量需是数组变量的列表或记录表类型;INTO返回单行,此时定义的变量可以使多个pl/sql变量的列表或记录类型。
4、如果动态sql中需要绑定变量,则使用USING,通常绑定的变量为输入入参,此时变量的in可以省略;如果需要绑定输出变量(如调用过程时可能需要输出),则在变量前用out显示指明。
1.2 实例说明
1.2.1 动态DDL
DECLARE
v_sql VARCHAR2(1000);
v_table VARCHAR2(30) := 'test_ynamic_sql';
BEGIN
v_sql := ' create table ' || v_table ||
' (id varchar2(10),name varchar2(100))';
EXECUTE IMMEDIATE v_sql;
END;
1.2.2 动态DML insert
1.2.2.1 不绑定输入变量
DECLARE
v_sql VARCHAR2(1000);
v_table VARCHAR2(30) := 'test_ynamic_sql';
BEGIN
--1、不绑定输入变量
v_sql := ' insert into ' || v_table ||
' values (''1'',''no_binding_in_variable'')';
EXECUTE IMMEDIATE v_sql;
COMMIT; --dml需要显示提交
END;
1.2.2.2 绑定输入变量
DECLARE
v_sql VARCHAR2(1000);
v_table VARCHAR2(30) := 'test_ynamic_sql';
BEGIN
--1、绑定输入变量
v_sql := ' insert into ' || v_table || ' values (:1,:2)';
EXECUTE IMMEDIATE v_sql
USING '2', 'binding_in_variable'; --使用using绑定输入变量
END;
1.2.3 动态DML select
1.2.3.1 返回单行值
DECLARE
v_sql VARCHAR2(1000);
v_table VARCHAR2(30) := 'test_ynamic_sql';
--1、使用简单pl/sql变量v_id,v_name获得单行输出
v_id VARCHAR2(10);
v_name VARCHAR2(100);
--2、使用基于test_ynamic_sql表的记录变量获得单行输出
TYPE test_ynamic_sql_record IS RECORD(
v_id test_ynamic_sql.ID%TYPE,
v_name test_ynamic_sql.NAME%TYPE);
test_ynamic_sql_row test_ynamic_sql_record;
BEGIN
--1、使用简单pl/sql变量v_id,v_name获得单行输出
v_sql := ' select id,name from ' || v_table || ' where id=:1 ';
EXECUTE IMMEDIATE v_sql
INTO v_id, v_name
USING '1';
DBMS_OUTPUT.put_line('id=' || v_id || ',name=' || v_name);
--2、使用基于test_ynamic_sql表的记录变量获得单行输出
EXECUTE IMMEDIATE v_sql
INTO test_ynamic_sql_row
USING '1';
DBMS_OUTPUT.put_line('id=' || test_ynamic_sql_row.v_id || ',name=' ||
test_ynamic_sql_row.v_name);
END;
1.2.3.2 返回多行值
1.2.3.2.1 使用记录表获取
DECLARE
v_sql VARCHAR2(1000);
v_table VARCHAR2(30) := 'test_ynamic_sql';
--1、使用基于test_ynamic_sql表的记录变量获得多行输出
TYPE test_ynamic_sql_record IS RECORD(
id test_ynamic_sql.ID%TYPE,
NAME test_ynamic_sql.NAME%TYPE);
TYPE test_ynamic_sql_table_type IS TABLE OF test_ynamic_sql_record INDEX BY BINARY_INTEGER;
/*可以用以下方式定义记录表*/
--TYPE test_ynamic_sql_table_type IS TABLE OF test_ynamic_sql%ROWTYPE INDEX BY BINARY_INTEGER;
test_ynamic_sql_multi_row test_ynamic_sql_table_type;
BEGIN
--1、使用基于test_ynamic_sql表的记录变量获得多行输出
v_sql := ' select id,name from ' || v_table;
EXECUTE IMMEDIATE v_sql BULK COLLECT
INTO test_ynamic_sql_multi_row;
FOR m IN 1 .. test_ynamic_sql_multi_row.COUNT LOOP
DBMS_OUTPUT.put_line('id=' || test_ynamic_sql_multi_row(m)
.id || ',name=' || test_ynamic_sql_multi_row(m).NAME);
END LOOP;
END;
1.2.3.2.2 使用多个嵌套表获取
DECLARE
v_sql VARCHAR2(1000);
v_table VARCHAR2(30) := 'test_ynamic_sql';
--1、使用基于多个嵌套表获取多行输出
TYPE test_ynamic_sql_id_type IS TABLE OF test_ynamic_sql.ID%TYPE INDEX BY BINARY_INTEGER;
TYPE test_ynamic_sql_name_type IS TABLE OF test_ynamic_sql.NAME%TYPE INDEX BY BINARY_INTEGER;
test_ynamic_sql_multi_row_id test_ynamic_sql_id_type;
test_ynamic_sql_multi_row_name test_ynamic_sql_name_type;
BEGIN
--1、使用基于多个嵌套表获取多行输出
v_sql := ' select id,name from ' || v_table;
EXECUTE IMMEDIATE v_sql BULK COLLECT
INTO test_ynamic_sql_multi_row_id, test_ynamic_sql_multi_row_name;
FOR m IN 1 .. test_ynamic_sql_multi_row_id.COUNT LOOP
DBMS_OUTPUT.put_line('id=' || test_ynamic_sql_multi_row_id(m) ||
',name=' || test_ynamic_sql_multi_row_name(m));
END LOOP;
END;
1.2.4 动态调用函数
1.2.4.1 使用select 获取返回值
DECLARE
v_sql VARCHAR2(1000);
v_name VARCHAR2(100);
BEGIN
--1、先创建测试函数
v_sql := ' CREATE OR REPLACE FUNCTION f_test_ynamic_sql(v_id VARCHAR2) RETURN VARCHAR2 IS
v_name VARCHAR2(100);
BEGIN
SELECT NAME INTO v_name FROM test_ynamic_sql WHERE id = v_id;
RETURN v_name;
END ; ';
EXECUTE IMMEDIATE v_sql;
--2、 使用select 获取返回值
v_sql := ' select f_test_ynamic_sql(:1) from dual';
EXECUTE IMMEDIATE v_sql
INTO v_name
USING '1';
DBMS_OUTPUT.put_line(' NAME = ' || v_name);
END;
1.2.4.2 使用begin .. end绑定函数输出变量
DECLARE
v_sql VARCHAR2(1000);
v_name_o VARCHAR2(100);
BEGIN
--1、先创建测试函数
v_sql := ' CREATE OR REPLACE FUNCTION f_test_ynamic_sql(v_id VARCHAR2,v_name_o out varchar2) RETURN VARCHAR2 IS
v_name VARCHAR2(100);
BEGIN
SELECT NAME INTO v_name FROM test_ynamic_sql WHERE id = v_id;
v_name_o:=v_name;
RETURN v_name;
END ; ';
EXECUTE IMMEDIATE v_sql;
--2、使用begin .. end绑定函数输出变量
v_sql := ' declare v_name varchar2(100);
begin v_name:=f_test_ynamic_sql(:1,:2); end;';
EXECUTE IMMEDIATE v_sql
USING '1', OUT v_name_o;
DBMS_OUTPUT.put_line('name_o=' || v_name_o); --using中的输出变量需要显示说明
END;
1.2.5 动态调用过程
DECLARE
v_sql VARCHAR2(1000);
v_name_o VARCHAR2(100);
BEGIN
--1、先创建测试过程
v_sql := ' CREATE OR REPLACE procedure p_test_ynamic_sql(v_id VARCHAR2,v_name_o out varchar2) IS
BEGIN
SELECT NAME INTO v_name_o FROM test_ynamic_sql WHERE id = v_id;
END ; ';
EXECUTE IMMEDIATE v_sql;
--2、使用begin .. end绑定过程输出变量
v_sql := ' begin p_test_ynamic_sql(:1,:2); end;';
EXECUTE IMMEDIATE v_sql
USING '1', OUT v_name_o; --using中的输出变量需要显示说明
DBMS_OUTPUT.put_line('name_o=' || v_name_o);
END;
分享到:
相关推荐
oracle动态sql之EXECUTE IMMEDIATE.docx
动态创建和执行SQL语句性能超前,EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能,较之以前它相当容易编码.尽管DBMS_SQL仍然可用,但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上。 — 使用技巧 ...
用execute immediate 执行备份恢复SQLSVR数据库
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数据库游标使用大全 ...较好的PL/SQL程序设计是在PL/SQL块中使用象DBMS_SQL这样的内建包或执行EXECUTE IMMEDIATE命令建立动态SQL来执行DDL命令,PL/SQL编译器保证对象引用以及用户的权限。。。。。。
如果需要在pl/sql 程序中使用动态sql,建议使用execute immediate 对于非常大的表,考虑使用表和索引的分区 如果需要在创建索引的时候减少所需时间,可以在会话集设置比较大的sort_area_size 考虑更多的使用decode...
execute immediate sql_str into trow using tid; --将查询的结果给trow,查询语句中用到的参数使用tid替换 dbms_output.put_line('编号:'||trow.id||',名称:'||trow.name); end; drop table newTypes --PL/...
如果直接执行SQL语句或者参数绑定则不用担心太多, 如以下ORACLE存储过程 create or replace ... 例如工程师经常用的DBMS_SQL或者EXECUTE IMMEDIATE 看以下存储过程 create or replace procedure kjdate
An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the ...
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 ...
在Oracle中若删除一个不存在的表,如 “DROP TABLE tableName”,则会提示: ORA-00942:表或视图不存在 若在程序中执行该语句则会报异常,这就需要...EXECUTE IMMEDIATE 'DROP TABLE tableName'; END IF; END; 在Oracle中
第一种:不设置输出格式参数,即用默认的 SQL> create or replace procedure sql_explain(v_sql varchar2) ... 7 execute immediate 'explain plan for '||v_sql; 8 open explain_cursor fo
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 /
在ORACLE数据库杀掉会话进程有三种方式: 1:ALTER SYSTEM KILL SESSION ...SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE Specify IMMEDIATE to instruct Oracle Database to roll back on
begin /*这里不能直接执行select语句但可以直接执行update、delete、insert语句*/ end里面不能接执行select语句,声明会话级临时表必须有“execute immediate 'TRUNCATE TABLE 表名';”这一句不然其他的session无法...
插入字符串作为 Oracle EXECUTE IMMEDIATE 语句 用法:编辑 -> 转换为 PHP 字符串(ctrl + alt + shift + P) 安装: 下载ideaHelpfulPlugins.jar 在 phpStorm Settings -> Plugins -> Install plugin from disk...