oracle动态拼接，执⾏sql
⼀、静态SQL和动态SQL的概念。
  1、静态SQL
  静态SQL是我们常⽤的使⽤SQL语句的⽅式，就是编写PL/SQL时，SQL语句已经编写好了。因为静态SQL是在编写程序时就确定了，我们只能使⽤SQL中的DML和事务控制语句，但是DDL语句，以及会话控制语句却不能再PL/SQL中直接使⽤，如动态创建表或者某个不确定的操作时，这就需要动态SQL来实现。
  2、动态SQL
  动态SQL是指在PL/SQL编译时SQL语句是不确定的，如根据⽤户输⼊的参数的不同来执⾏不同的操作。编译程序对动态语句部分不进⾏处理，只是在程序运⾏时动态创建语句，对语句进⾏分析，病执⾏该语句。
  静态SQL的优势是性能较⾼，但不灵活。动态SQL的优势是灵活，缺点是性能稍差。
⼆、动态创建DML、DDL的SQL语句。
  动态创建SQL有⼀下⼏类：
  1、DDL语句、DCL语句、⾮查询的DML语句、单⾏查询的SELECT语句，这类可以使⽤EXECUTE IMMEDIATE语句执⾏。
  2、多⾏查询的SELECT语句可以使⽤游标来实现。
  3、通过DBMS_SQL程序包实现。
  下⾯来介绍以上3种情况：
  1、使⽤EXECUTE IMMEDIATE语句处理相关语句：
  语法：
  EXECUTE IMMEDIATE dynamic_sql_string
  [into define_variable_list]
  [using bind_argument_list];
  例： 
动态创建表t1
--处理DDL、DCL语句，根据⽤户输⼊的表明及字段名动态创建表t1
DECLARE
tablename VARCHAR2(20);        --表名
field1 VARCHAR2(20);          --字段1名称
datatype1 VARCHAR2(20);        --字段1类型
field2 VARCHAR2(20);          --字段2名称
datatype2 VARCHAR2(20);        --字段2类型
str_sql VARCHAR2(500);        --拼接SQL语句的字符串
BEGIN
tablename := 't1';
field1:='id';
datatype1:='number';
field2:='name';
datatype2:='varchar(20)';
str_sql := 'create table '||tablename||'('||field1 ||' '||datatype1||','||field2 ||' '||datatype2||')';
EXECUTE IMMEDIATE str_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('操作失败!');
END;
  动态插⼊数据； 
-
-动态处理费查询的DML语句：向刚才创建的表中插⼊数据
DECLARE
v_id NUMBER;                    --输⼊序号；
v_name VARCHAR(20);            --输⼊姓名；
str_sql VARCHAR2(500);          --保存拼接的SQL语句
BEGIN
v_id := &vid;
v_name := '&name';
str_sql := 'insert into t1 values(:1,:2)';      --使⽤占位符代表变量
EXECUTE IMMEDIATE str_sql
USING v_id,v_name;                              --使⽤变量替换SQL中的占位符,v_id替换:1,v_name替换:2，依此类推。    COMMIT;                                        --执⾏完毕后直接提交
END;
  查询表中的数据有多少⾏
--处理单⾏查询的SELECT举例，查询表中的数据有多少⾏
DECLARE
v_count NUMBER;
str_sql VARCHAR2(500);
BEGIN
str_sql := 'select count(*) from t1';
EXECUTE IMMEDIATE str_sql INTO v_count;  --将查询的结果存放到变量v_count中。
DBMS_OUTPUT.put_line(v_count);
END;
  绑定变量的优缺点：
  1）可以再库缓存中共享游标，节省了CPU等资源，可以避免额外开销。
  2）SQL语句使⽤绑定变量可以避免被注⼊攻击。
  3）绑定变量是⼀种减少应⽤程序在分析查询时使⽤栓锁数⽬的可靠⽅法。
  不适合使⽤变量绑定的情况：
  1）对于隔相当长⼀段时间才执⾏⼀次的SQL语句，利⽤绑定变量的好处hi被不能有效利⽤⽽抵消。
  2）在数据仓库的情况下。
  3）在对建有索引的字段，且字段⾮常⼤时，利⽤绑定变量可能会导致查询计划错误，从⽽导致查询效率⾮常低。
  实现DDL语句中的注意事项：
  PL/SQL块使⽤动态SQL执⾏DDL语句的时候与其它不同，在DDL中不能使⽤绑定变量。
  实现DML语句中的注意事项：
  不能使⽤绑定变量替换实际的数据库对象名（表，视图，列等），只能替换字⾯两，如果对象名在运⾏时⽣成的，我们只能使⽤字符串拼接。
  2、通过游标实现多⾏查询的SELECT语句
  REF游标可以处理返回届国际的动态SQL。实现动态SQL的REF游标声明和普通REF游标相同，知识OPEN时绑定的是动态SQL字符串。
  例：查询emp表中所有的数据。
DECLARE
TYPE ref_cur IS REF CURSOR;
rc ref_cur;
emprow emp%ROWTYPE;
v_sql VARCHAR2(100):= 'select * from emp where deptno = :x';  --动态执⾏的SQL语句
BEGIN
OPEN rc FOR v_sql USING 30;  --打开游标，绑定执⾏的SQL语句，并传递参数
LOOP
FETCH rc INTO emprow;
EXIT WHEN rc%NOTFOUND;
dbms_output.put_line('name:'||ame||'  sal:'||emprow.sal);
END LOOP;
CLOSE rc;
END;
  3、DBMS_SQL程序包
  DBMS_SQL程序包是系统提供给我们的另⼀种使⽤动态SQL的⽅法。程序包中封装了⼀些列存储过程，帮助我们动态执⾏SQL。  使⽤DBMS_SQL包实现动态SQL的步骤如下：
  1）将要执⾏的SQL语句或⼀个语句块放到⼀个字符串变量中。
  2）使⽤DBMS_SQL包的parse过程来分析该字符串。
  3）使⽤DBMS_SQL包的bind_variable过程来绑定变量。
  4）使⽤DBMS_SQL包的execute函数来执⾏语句。
  例：使⽤DBMS_SQL创建表 
DECLARE
tablename VARCHAR2(20) :='t2';                --表名
field1 VARCHAR2(20) :='id';                    --字段1名称
datatype1 VARCHAR2(20) :='number';            --字段1类型
field2 VARCHAR2(20) :='name';                  --字段2名称
datatype2 VARCHAR2(20) :='varchar(20)';        --字段2类型
v_sql VARCHAR2(500) := 'create table '||tablename||'('||field1 ||' '||datatype1||','||field2 ||' '||datatype2||')';          --拼接SQL语句的字符串
v_cursor NUMBER;                                --定义光标
v_row NUMBER;                                  --⾏数
BEGIN
v_cursor:=dbms_sql.open_cursor;                              --为处理打开光标
dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);              --分析语句；
v_row:=ute(v_cursor);                          --执⾏sql语句；
dbms_sql.close_cursor(v_cursor);                            --关闭光标；
DBMS_OUTPUT.put_line(v_row);
END;
  向表中插⼊⼀条数据：
DECLARE
v_id NUMBER := &vid;
v_name VARCHAR2(20) := '&vname';
v_sql VARCHAR2(100) := 'insert into t2 values(:id,:name)';
v_cursor NUMBER;
v_row NUMBER;
BEGIN
v_cursor:=dbms_sql.open_cursor;
dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);
dbms_sql.bind_variable(v_cursor,':id',v_id);
dbms_sql.bind_variable(v_cursor,':name',v_name);
v_row := ute(v_cursor);
dbms_sql.close_cursor(v_cursor);
COMMIT;
DBMS_OUTPUT.put_line(v_row);
END;
  查询EMP中的数据
DECLARE
V_DEPTNO NUMBER := &DEPTNO;
V_SQL    VARCHAR2(100) := 'select empno,ename,sal from emp where deptno = :deptno';
V_CURSOR NUMBER;
V_NO    NUMBER;
V_ENAME  VARCHAR2(20);
V_SAL    NUMBER;
v_start  NUMBER;
sql语句替换表中内容BEGIN
V_CURSOR := DBMS_SQL.OPEN_CURSOR;                                  --打开游标
DBMS_SQL.PARSE(V_CURSOR, V_SQL, DBMS_SQL.NATIVE);                  --解析动态SQL语句
DBMS_SQL.BIND_VARIABLE(V_CURSOR, ':deptno', V_DEPTNO);            --传递参数
DBMS_SQL.DEFINE_COLUMN(V_CURSOR, 1, V_NO);                        --定义输出的列，和查询的列相匹配
DBMS_SQL.DEFINE_COLUMN(V_CURSOR, 2, V_ENAME,20);
DBMS_SQL.DEFINE_COLUMN(V_CURSOR, 3, V_SAL);
v_start := ute(V_CURSOR);                            --执⾏SQL语句，需要有接受返回值
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS(V_CURSOR) <= 0;                    --解析游标，
DBMS_SQL.COLUMN_VALUE(V_CURSOR, 1, V_NO);                        --将当前⾏的数据写⼊上⾯对应的列中。    DBMS_SQL.COLUMN_VALUE(V_CURSOR, 2, V_ENAME);
DBMS_SQL.COLUMN_VALUE(V_CURSOR, 1, V_SAL);
DBMS_OUTPUT.PUT_LINE('no:' || V_NO || '  enmae:' || V_ENAME ||'    sal:' || V_SAL);  --输出内容
END LOOP;
dbms_sql.close_cursor(v_cursor);                                    --关闭游标
END;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。