动态sql语句oracle

‘壹’ oracle笔记-动态sql

孙告第 章 动态SQL

为何使用动态SQL

实现动态SQL有两种方式 DBMS_SQL和本地动态SQL（EXECUTE IMMEIDATE）

主要从以下方面考虑使用哪种方式

是否知道涉及的列数和类型

DBMS_SQL包括了一个可以 描述 结果集的存储过程（DBMS_SQL DESCRIBE_COLUMNS） 而本地动态SQL没有

是否知道可能涉及的绑定变量数和类型

DBMS_SQL允许过程化的绑定语句的输入 而本地动态SQL需要在编译时确定

是否使用 数组化 操作（Array Processing）

DBMS_SQL允许 而本地动态SQL基本不可以 但可以用其他方式实现（对查询可用FETCH BULK COLLECT INTO 对INSERT等 可用一个BEGIN … END块中加循环实现）

是否在同一个会话中多次执行同一语句

DBMS_SQL可以分析一次执行多次 而本地动态SQL会在每次执行时进行软分析

是否需要用REF CURSOR返回结果集

仅本地动态SQL可用REF CURSOR返回结果集

如何使用动态SQL

DBMS_SQL

调用OPEN_CURSOR获得一个游标句柄

调用PARSE分析语句 一个游标句柄可以用于多条不同的已分析语句 但一个时间点仅一条有效

调用BIND_VARIABLE或BIND_ARRAY来提供语句的任何输入

若是一个查询（SELECT语句） 调用DIFINE_COLUMN或DEFINE_ARRAY来告知卖凯掘Oracle如何返回结果

调用EXECUTE执行语句

若是一个查中核询 调用FETCH_ROWS来读取数据 可以使用COLUMN_VALUE从SELECT列表根据位置获得这些值

否则 若是一个PL/SQL块或带有RETURN子句的DML语句 可以调用VARIABLE_VALUE从块中根据变量名获得OUT值

调用CLOSE_CURSOR

注意这里对任何异常都应该处理 以关闭游标 防止泄露资源

本地动态SQL

EXECUTE IMMEDIATE 语句

[INTO {变量 变量 … 变量N | 记录体}]

[USING [IN | OUT | IN OUT] 绑定变量 … 绑定变量N]

[{RETURNING | RETURN} INTO 输出 [ … 输出N]…]

注意本地动态SQL仅支持弱类型REF CURSOR 即对于REF CURSOR 不支持BULK COLLECT

最后说明

‘贰’ ORACLE里面动态的添加字段，如果存在就不添加，如果不存在就添加。sql语句怎么写

declare

p_table_namevarchar2(30);

p_column_namevarchar2(30);

p_data_typevarchar2(30);

p_cntnumber;

p_sqlvarchar2(4000);

begin

p_table_name:='';

p_column_name:='';

selectcount(1)intop_cntfromuser_tab_colswherea.table_name=p_table_nameanda.column_name=p_column_name;

ifp_cnt=0then

p_sql:='altertable'||p_table_name||'add'||p_column_name||''||p_data_type;

executeimmediatep_sql;

endif;

end;

没测试，不过基本应该可以

‘叁’ oracle数据库动态SQL语句问题

是这样子的：
正常的SQL应该是这样：
SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME='EMP';
然后游态V_SQL:='';最外层也是有引号的
当表名是变量，但是我们查的时候是需要加上单引号的，销磨逗如果最外面的单引号的话，则里面的单引号就需要单引号再加单引号这样来引用的。
所以，如亏卖果你测试你的V_SQL写的正常不正常的话，可以用raise_application_error(-20201,V_SQL);查看，因为这样输出的是正常的sql的哦。

‘肆’ oracle 中动态sql语句，表名为变量，怎么解

表名可用变量，但一般需要用到动态sql，举例如下：
declare
v_date varchar2(8);--定义日期变量
v_sql varchar2(2000);--定义动态sql
v_tablename varchar2(20);--定义动态表名
begin
select to_char(sysdate,'yyyymmdd') into v_date from al;--取日期变量
v_tablename := 'T_'||v_date;--为动态表命名
v_sql := 'create table '||v_tablename||'
(id int,
name varchar2(20))';--为动态sql赋值
dbms_output.put_line(v_sql);--打印sql语句
execute immediate v_sql;--执行动态sql
end;
执行以后，就会生成以日期命名的表。

‘伍’ oracle动态表名查询 如何写sql语句或者存储过程实现： 根据某张表中的某个字段的值决定查询哪一张表

begin
for cur in(select id,flag fromu a)
loop
if cur.flag=0 then
select * from b;
...
else
select * from C;
...
end if;
end loop;
end;

‘陆’ 如何在oracle存储过程中执行动态sql语句

给你一个案例对这些，使用execute immediate就可以了，存储过程和语句块也是一样的，自己改一改，没区别的。

语法格式
EXECUTEIMMEDIATEdynamic_string
[INTO{define_variable[,define_variable]...|record}]
[USING[IN|OUT|INOUT]bind_argument[,[IN|OUT|INOUT]bind_argument]...]
[{RETURNING|RETURN}INTObind_argument[,bind_argument]...];

1，操作DDL语句，这也是动态SQL的常用操作之一
如下所示使用动态SQL创建数据库表：
DECLARE
l_dync_sqlVARCHAR2(100);
BEGIN
l_dync_sql:='CREATETABLEcux_dync_test(idNUMBER,creation_dateDATE)';
EXECUTEIMMEDIATEl_dync_sql;
END;
2，操作DML语句，使用USING子句可以按照顺序将输入的值绑定到变量，如果动态SQL只有单行输出的话可以直接使用INTO来接收输出值，如下所示。
DECLARE
l_dync_sqlVARCHAR2(100);
l_person_nameVARCHAR2(140);
l_ageNUMBER;
BEGIN
l_dync_sql:='SELECTperson_name,ageFROMcux_cursor_testWHEREperson_id=:1';
EXECUTEIMMEDIATEl_dync_sql
INTOl_person_name,l_age--使用into语句接手动态SQL的输出，如果输出多行则出错
USING101;--给绑定变量赋值
dbms_output.put_line('PersonName:'||l_person_name);
dbms_output.put_line('Age:'||l_age);
END;

‘柒’ oracle存储过程中如何执行动态SQL语句

有时需要在oracle
存储过程
中执行动态SQL
语句
，例如表名是动态的，或字段是动态的，或查询命令是动态的，可用下面的方法：
set
serveroutput
on
declare
n
number;
sql_stmt
varchar2(50);
t
varchar2(20);
begin
execute
immediate
'alter
session
set
nls_date_format=''YYYYMMDD''';
t
:=
't_'
||
sysdate;
sql_stmt
:=
'select
count(*)
from
'
||
t;
execute
immediate
sql_stmt
into
n;
dbms_output.put_line('The
number
of
rows
of
'
||
t
||
'
is
'
||
n);
end;
如果动态SQL
语句
很长很复杂，则可用包装.
CREATE
OR
REPLACE
PACKAGE
test_pkg
IS
TYPE
cur_typ
IS
REF
CURSOR;
PROCEDURE
test_proc
(v_table
VARCHAR2,t_cur
OUT
cur_typ);
END;
/
CREATE
OR
REPLACE
PACKAGE
BODY
test_pkg
IS
PROCEDURE
test_proc
(v_table
VARCHAR2,t_cur
OUT
cur_typ)
IS
sqlstr
VARCHAR2(2000);
BEGIN
sqlstr
:=
'SELECT
*
FROM
'||v_table;
OPEN
t_cur
FOR
sqlstr;
END;
END;
/
在oracle
中批量导入，导出和删除表名以某些字符开头的表
spool
c:\a.sql
select
'drop
table
'
||
tname
||
';'
from
tab
where
tname
like
'T%';
spool
off
@c:\a