ORACLE关于动态SQL的使用
DBMS_SQL使用
DBMS_SQL使用DBMS_SQL是Oracle数据库中一个用于动态SQL的包，用于在运行时构造和执行SQL语句。
它提供了一种灵活的方式来处理动态SQL，允许开发人员在程序中动态生成SQL语句，并在运行时执行这些语句。
使用DBMS_SQL的主要步骤如下：1.打开游标：首先，需要调用DBMS_SQL.OPEN_CURSOR方法来打开一个游标。
游标是用来执行SQL语句和返回结果的对象。
2.解析SQL语句：接下来，使用DBMS_SQL.PARSE方法将SQL语句解析成可执行的格式。
这个方法需要接收游标句柄、SQL语句和语句类型作为参数。
3.绑定变量：如果SQL语句中包含绑定变量，可以使用DBMS_SQL.BIND_VARIABLE方法将变量绑定到游标上。
绑定变量可以防止SQL注入攻击，并且提高了SQL语句的重用性和性能。
4.执行SQL语句：使用DBMS_SQL.EXECUTE方法来执行SQL语句。
这个方法只返回一个结果集，不需要接收任何参数。
5.获取结果：如果SQL语句返回结果集，可以使用DBMS_SQL.FETCH_ROWS方法来获取结果集的行。
可以使用DBMS_SQL.COLUMN_VALUE方法来获取行中的列值。
6.关闭游标：最后，使用DBMS_SQL.CLOSE_CURSOR方法关闭游标。
除了上述基本步骤外，DBMS_SQL还提供了其他一些方法和函数来处理动态SQL。
1.DBMS_SQL.VARIABLE_VALUE：用于获取和设置绑定变量的值。
2.DBMS_ST_ERROR_POSITION：用于获取最后一个错误的位置。
3.DBMS_ST_ERROR_TEXT：用于获取最后一个错误的文本。
4.DBMS_SQL.EXECUTE_AND_FETCH：用于一次性执行SQL语句并获取结果。
5.DBMS_SQL.GET_NEXT_RESULT：用于获取多个结果集。
使用DBMS_SQL的一个常见应用是在存储过程或函数中动态构造和执行SQL语句。
Oracle中动态SQL详解（EXECUTEIMMEDIATE）
Oracle中动态SQL详解（EXECUTEIMMEDIATE）Oracle中动态SQL详解(EXECUTE IMMEDIATE)2017年05⽉02⽇ 18:35:48 阅读数：744 标签：更多个⼈分类：Oracle中动态SQL详解1．静态SQLSQL与动态SQL Oracle编译PL/SQL程序块分为两个种：其⼀为前期联编（early binding），即SQL语句在程序编译期间就已经确定，⼤多数的编译情况属于这种类型；另外⼀种是后期联编（late binding），即SQL语句只有在运⾏阶段才能建⽴，例如当查询条件为⽤户输⼊时，那么Oracle的SQL引擎就⽆法在编译期对该程序语句进⾏确定，只能在⽤户输⼊⼀定的查询条件后才能提交给SQL引擎进⾏处理。
通常，静态SQL采⽤前⼀种编译⽅式，⽽动态SQL采⽤后⼀种编译⽅式。
本⽂主要就动态SQL的开发进⾏讨论，并在最后给出⼀些实际开发的技巧。
2．动态SQL程序开发 理解了动态SQL编译的原理，也就掌握了其基本的开发思想。
动态SQL既然是⼀种”不确定”的SQL，那其执⾏就有其相应的特点。
Oracle中提供了Execute immediate语句来执⾏动态SQL，语法如下：Excute immediate 动态SQL语句 using 绑定参数列表 returning into 输出参数列表;对这⼀语句作如下说明： 1)动态SQL是指DDL和不确定的DML（即带参数的DML） 2)绑定参数列表为输⼊参数列表，即其类型为in类型，在运⾏时刻与动态SQL语句中的参数（实际上占位符，可以理解为函数⾥⾯的形式参数）进⾏绑定。
3)输出参数列表为动态SQL语句执⾏后返回的参数列表。
4)由于动态SQL是在运⾏时刻进⾏确定的，所以相对于静态⽽⾔，其更多的会损失⼀些系统性能来换取其灵活性。
为了更好的说明其开发的过程，下⾯列举⼀个实例： 设数据库的emp表，其数据为如下：ID NAME SALARY100Jacky5600101Rose3000102John4500要求： 1．创建该表并输⼊相应的数据。
Oracle过程中执行动态SQL或DDL语句
如果你用的是Oracle8i 及以上的版本，那简单，在过程中用 execute immediate sql_str 就行， sql_str 是一个拼凑的 SQL 语句，但这个动态语句中带参数，或 Select 的结果要 into 到变量中时就要稍加留心一下了。
而在 8i 以前的版本（谁还用这么古老的玩艺，总有些不得已的地方，老系统考虑升级成本遗留下来的，应用软件所伴随着的等），都没法用 execute immediate，就得使用 DBMS_SQL 包来实现了何谓动态 SQL 和 DDL 语句呢？通常在过程中要操作的表名、字段名都必须是明确的，否则编译过程时就要报错，但如果这两者也用变量名来表示就是动态的。
DDL 就是数据库对象定义的操作，如 CREATE TABLE/VIEW/INDEX/SYN/CLUSTER……，及这些对象的删除、修改操作等等。
比如在Oracle中有执行下面过程块的意图时，就要使用到 execute immediate 或是DBMS_SQL 包了。
当然下面的语句块是通不过的。
declarecol_name varchar2(30) := 'name'; --假定表user存在name字段col_val varchar2(30);beginselect col_name into col_val --按照惯常思维，可能就会这么写from user where age between 18 and 25; --编译时会提示列名不存在的drop table t2; --不能直接执行 DDL 语句，后面查询 t2 编译器就无能为力了end;现在我们提出对上面问题的解，针对第一个 Select 语句来说明，并假设查询中还带有参数。
块中的 DDL 也是类似的解法。
例子因力图涵盖更多内容，所以稍显复杂，如果不需要 into （如 update/delete 语句），或者不带参数，会简单多了，应不难简化。
ORACLE动态执行SQL语句
3. 返回结果集
str_column:='sal';--查询的列 str_Total:=0;--总和
str_sql:=' select '|| 'str_column' ||' from emp ' ; execute immediate str_sql into str_Total;
2. 返回单条结果
declare str varchar2(500); c_1 varchar2(10); r_1 test%rowtype; begin c_1:='测试人员'; str:='select * from test where name=:c WHERE ROWNUM=1'; execute immediate str into r_1 using c_1; DBMS_OUTPUT.PUT_LINE(R_||R_1.ADDRESS); end ;
网络错误503请刷新页面重试持续报错请尝试更换浏览器或网络环境
ORACLE动态执行 SQL语句
Oracle 动态SQL有两种写法：用 DBMS_SQL 或 execute immediate，建议使用后者。试验步骤如下：
Oracle 动态SQL有两种写法：用 DBMS_SQL 或 execute immediate，建议使用egin EXECUTE IMMEDIATE 'drop table temp_1'; EXECUTE IMMEDIATE 'create table temp_1(name varchar2(8))'; end;
/*** DML ***/ declare v_1 varchar2(8); v_2 varchar2(10); str varchar2(50); begin v_1:='测试人员'; v_2:='北京'; str := 'INSERT INTO test (name ,address) VALUES (:1, :2)'; EXECUTE IMMEDIATE str USING v_1, v_2; commit; end;
在Oracle中执行动态SQL的几种方法
在Oracle中执⾏动态SQL的⼏种⽅法转载：以下为内容留存：在中执⾏动态SQL的⼏种⽅法在⼀般的sql操作中，sql语句基本上都是固定的，如：SELECT t.empno,t.ename FROM scott.emp t WHERE t.deptno = 20;但有的时候，从应⽤的需要或程序的编写出发，都可能需要⽤到动态SQl，如：当 from 后的表不确定时，或者where 后的条件不确定时，都需要⽤到动态SQL。
⼀、使⽤动态游标实现1、声明动态游标TYPE i_cursor_type IS REF CURSOR;2、声明游标变量my_cursor i_cursor_type;3、使⽤游标n_deptno:=20;dyn_select := 'select empno,ename from emp where deptno='||n_deptno;OPEN my_cursor FOR dyn_select;LOOPFETCH my_cursor INTO n_empno,v_ename;EXIT WHEN my_cursor%NOTFOUND;--⽤n_empno,v_ename做其它处理--....END LOOP;CLOSE dl_cursor;4、⼩结：动态游标可以胜任⼤多数动态SQL的需求了，使⽤简洁⽅便居家旅⾏之必备杀⼈放⽕之法宝。
⼆、使⽤ EXECUTE IMMEDIATE最早⼤家都使⽤DBMS_SQL包，但是太太⿇烦了，最终都放弃了。
但是⾃从有了EXECUTE IMMEDIATE之后，但要注意以下⼏点：EXECUTE IMMEDIATE代替了以前Oracle8i中DBMS_SQL package包.它解析并马上执⾏动态的SQL语句或⾮运⾏时创建的PL/SQL块.动态创建和执⾏SQL语句性能超前，EXECUTE IMMEDIATE的⽬标在于减⼩企业费⽤并获得较⾼的性能，较之以前它相当容易编码.尽管DBMS_SQL仍然可⽤，但是推荐使⽤EXECUTE IMMEDIATE,因为它获的收益在包之上。
ORACLE中使用DBMS_SQL获取动态SQL执行结果中的列名和值
ORACLE中使⽤DBMS_SQL获取动态SQL执⾏结果中的列名和值1.获取动态SQL中的列名及类型DECLAREl_curid INTEGER;l_cnt NUMBER;l_desctab dbms_sql.desc_tab;l_sqltext VARCHAR2(2000);BEGINl_sqltext :='select *from dba_objects where rownum<= 10'; --可以是任意有效的查询sql⽂本l_curid := dbms_sql.open_cursor();dbms_sql.parse(l_curid, l_sqltext, dbms_sql.native);dbms_sql.describe_columns(l_curid, l_cnt, l_desctab);FOR i IN1 .. l_desctab.count LOOPdbms_output.put_line(rpad(l_desctab(i).col_name, 30)||rpad(l_desctab(i).col_type, 3));END LOOP;dbms_sql.close_cursor(l_curid);END;查询结果OWNER 1OBJECT_NAME 1SUBOBJECT_NAME 1OBJECT_ID 2DATA_OBJECT_ID 2OBJECT_TYPE 1CREATED 12LAST_DDL_TIME 12TIMESTAMP 1STATUS 1TEMPORARY 1GENERATED 1SECONDARY 1NAMESPACE 2EDITION_NAME 1SHARING 1EDITIONABLE 1ORACLE_MAINTAINED 1col_type 1:VARCAHR2,2:NUMBER,12:DATE2.使⽤USING⽅式绑定动态SQL,获取列名及对应的值-- Created on 2017/10/11 by ADMINISTRATORDECLARETYPE typecursor IS REF CURSOR;cursrc typecursor;curid NUMBER;desctab dbms_sql.desc_tab;colcnt NUMBER;vname VARCHAR2(50);vnum NUMBER;vdate DATE;rownumber NUMBER :=5;sqlstmt VARCHAR2(2000);BEGINsqlstmt :='SELECT * FROM fnd_user WHERE rownum < :rownumber';-- 打开光标OPEN cursrc FOR sqlstmtUSING rownumber;-- 从本地动态SQL转换为DBMS_SQLcurid := dbms_sql.to_cursor_number(cursrc);--获取游标⾥⾯的数据列项数和每个数据列的属性，⽐如列名，类型，长度等dbms_sql.describe_columns(curid, colcnt, desctab);-- 定义列FOR i IN1 .. colcnt LOOP--此处是定义游标中列的读取类型，可以定义为字符，数字和⽇期类型，IF desctab(i).col_type =2THENdbms_sql.define_column(curid, i, vnum);ELSIF desctab(i).col_type =12THENdbms_sql.define_column(curid, i, vdate);ELSEdbms_sql.define_column(curid, i, vname, 50);END IF;END LOOP;-- DBMS_SQL包获取⾏--从游标中把数据检索到缓存区（BUFFER）中，缓冲区的值只能被函数COULUMN_VALUE()所读取WHILE dbms_sql.fetch_rows(curid) >0 LOOP --函数column_value()把缓冲区的列的值读⼊相应变量中。
oracle execute immediate用法
oracle execute immediate用法在Oracle数据库中，EXECUTE IMMEDIATE语句用于动态执行SQL 或PL/SQL代码。
以下是EXECUTE IMMEDIATE的一些常见用法：执行动态SQL查询：
sql复制代码
EXECUTE IMMEDIATE 'SELECT column1, column2 FROM table WHERE condition';
执行动态INSERT操作：
sql
EXECUTE IMMEDIATE 'INSERT INTO table (column1, column2) VALUES (:value1, :value2)';
执行动态UPDATE操作：
sql
EXECUTE IMMEDIATE 'UPDATE table SET column1 = :value WHERE condition';
执行动态DELETE操作：
sql
EXECUTE IMMEDIATE 'DELETE FROM table WHERE condition';
在上述示例中，你可以使用绑定变量（例如：:value1、:value2等）来绑定实际的参数值，这样可以避免SQL注入攻击，并提高代码的可读性和可维护性。
除了基本的EXECUTE IMMEDIATE语句外，你还可以使用其他功能和选项来扩展其功能，例如使用绑定变量、错误处理、动态创建表等。
你可以查阅Oracle文档以获取更多关于EXECUTE IMMEDIATE的详细信息和示例。
动态SQL的使用方法
动态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 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--字段类型) asstr_sql varchar2(500);beginstr_sql:=’create table ’||table_name||’(’||field1||’ ’||datatype1||’,’||field2||’ ’||datatype2||’)’;execute immediate str_sql; --动态执⾏DDL语句exceptionwhen others thennull;end ; 以上是编译通过的存储过程代码。
下⾯执⾏存储过程动态建表。
SQL>execute proc_test(’dinya_test’,’id’,’number(8) not null’,’name’,’varchar2(100)’);PL/SQL procedure successfully completedSQL>desc dinya_test;Name Type Nullable Default Comments---- ------------- -------- ------- --------ID NUMBER(8)NAME VARCHAR2(100) YSQL> 到这⾥，就实现了我们的需求，使⽤本地动态SQL根据⽤户输⼊的表名及字段名、字段类型等参数来实现动态执⾏DDL语句。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
关于动态SQL的使用-----摘录内容摘要：在PL/SQL开发过程中，使用SQL，PL/SQL可以实现大部份的需求，但是在某些特殊的情况下，在PL/SQL中使用标准的SQL语句或DML语句不能实现自己的需求，比如需要动态建表或某个不确定的操作需要动态执行。
这就需要使用动态SQL来实现。
本文通过几个实例来详细的讲解动态SQL的使用。
本文适宜读者范围：Oracle初级，中级系统环境：OS：windows2000Professional(英文版)Oracle：8.1.7.1.0正文：一般的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,--表名field1in varchar2,--字段名datatype1in varchar2,--字段类型field2in varchar2,--字段名datatype2in varchar2--字段类型)asstr_sql varchar2(500);beginstr_sql:=create table||table_name||(||field1||||datatype1||,||field2||||datatype2||);execute immediate str_sql;--动态执行DDL语句exceptionwhen others thennull;end;以上是编译通过的存储过程代码。
下面执行存储过程动态建表。
SQL>execute proc_test(dinya_test,id,number(8)not null,name,varchar2(100));PL/SQL procedure successfully completedSQL>desc dinya_test;Name Type Nullable Default Comments----------------------------------------ID NUMBER(8)NAME VARCHAR2(100)YSQL>到这里，就实现了我们的需求，使用本地动态SQL根据用户输入的表名及字段名、字段类型等参数来实现动态执行DDL语句。
2、本地动态SQL执行DML语句。
需求：将用户输入的值插入到上例中建好的dinya_test表中。
create or replace procedure proc_insert(id in number,--输入序号name in varchar2--输入姓名)asstr_sql varchar2(500);beginstr_sql:=insert into dinya_test values(:1,:2);execute immediate str_sql using id,name;--动态执行插入操作exceptionwhen others thennull;end;执行存储过程，插入数据到测试表中。
SQL>execute proc_insert(1,dinya);PL/SQL procedure successfully completedSQL>select*from dinya_test;ID NAME1dinya在上例中，本地动态SQL执行DML语句时使用了using子句，按顺序将输入的值绑定到变量，如果需要输出参数，可以在执行动态SQL的时候，使用RETURNING INTO子句，如：declarep_id number:=1;v_count number;beginv_string:=select count(*)from table_name a where a.id=:id;execute immediate v_string into v_count using p_id;end;更多的关于动态SQL中关于返回值及为输出输入绑定变量执行参数模式的问题，请读者自行做测试。
二、使用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_name1in varchar2,--字段名datatype1in varchar2,--字段类型field_name2in varchar2,--字段名datatype2in varchar2--字段类型)asv_cursor number;--定义光标v_string varchar2(200);--定义字符串变量v_row number;--行数beginv_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);--执行语句dbms_sql.close_cursor(v_cursor);--关闭光标exceptionwhen others thendbms_sql.close_cursor(v_cursor);--关闭光标raise;end;以上过程编译通过后，执行过程创建表结构：SQL>execute proc_dbms_sql(dinya_test2,id,number(8)notnull,name,varchar2(100));PL/SQL procedure successfully completedSQL>desc dinya_test2;Name Type Nullable Default Comments----------------------------------------ID NUMBER(8)NAME VARCHAR2(100)YSQL>2、使用DBMS_SQL包执行DML语句。
需求：使用DBMS_SQL包根据用户输入的值更新表中相对应的记录。
查看表中已有记录：SQL>select*from dinya_test2;ID NAME1Oracle2CSDN3ERPSQL>建存储过程，并编译通过：create or replace procedure proc_dbms_sql_update(id number,name varchar2)asv_cursor number;--定义光标v_string varchar2(200);--字符串变量v_row number;--行数beginv_cursor:=dbms_sql.open_cursor;--为处理打开光标v_string:=update dinya_test2a set =: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);--执行动态ＳＱＬdbms_sql.close_cursor(v_cursor);--关闭光标exceptionwhen others thendbms_sql.close_cursor(v_cursor);--关闭光标raise;end;执行过程，根据用户输入的参数更新表中的数据：SQL>execute proc_dbms_sql_update(2,csdn_dinya);PL/SQL procedure successfully completedSQL>select*from dinya_test2;ID NAME1Oracle2csdn_dinya3ERPSQL>执行过程后将第二条的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语句、事务控制语句及系统控制语句。