一般的PL/SQL程序设计中,在DML和事务控制的语句中可以直接使用SQL,但是DDL语句及系统控制语句却不能在PL/SQL中直接使用。也就是说要想在PL/SQL的begin和end之间直接使用类似于“select * from fw.math where n1=2”这样的语句是不行的,会抱错“在select语句中缺少into子句”。更加不能用DDL语句例如创建一个类型为表的对象"create table ss(s int)"。那么,要想实现在PL/SQL中使用DDL语句及系统控制语句,可以通过使用动态SQL来实现。
在Oracle数据库开发PL/SQL块中我们使用的SQL分为:静态SQL语句和动态SQL语句。所谓静态SQL指
在PL/SQL块中使用的SQL语句在编译时是明确的,执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的,如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理,只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。
接下来用几个例子来说明本地动态SQL的用法。
首先,我们可以动态地根据用户的输入来创建一张表,假设表里有2个字段。那么,我现在创建一个带输入参数也就是IN类型的过程:
create or replace procedure
create_table(table_name in varchar2,column_name1 in varchar2,data_type1 in varchar2,
column_name2 in varchar2,data_type2 in varchar2)
is
sql_stmt varchar2(255);
begin
sql_stmt := 'create table '||table_name||'('||column_name1||' '||data_type1||','||
column_name2||' '||data_type2||')';
execute immediate sql_stmt;
dbms_output.put_line('名称为'||table_name||'的表已经成功创建');
exception when others
then dbms_output.put_line(sqlerrm);
end;
这里的sql_stmt也就是一句拼装而成的动态SQL语句
之后,我在PL/SQL里调用这个过程来动态地创建表:
declare
table_name varchar2(200);
column_name1 varchar2(200);
data_type1 varchar2(200);
data_type2 varchar2(200);
column_name2 varchar2(200);
begin
table_name := '&请输入表名';
column_name1 := '&请输入第1列的列名';
data_type1 := '&请输入第1列的类型';
column_name2 := '&请输入第2列的列名';
data_type2 := '&请输入第2列的类型';
fw.create_table(table_name,column_name1,data_type1,column_name2,data_type2);
end;
这里注意可能会产生“权限不足的异常”,我上网查了下原因是:如果在一个匿名块或者过程中通过动态SQL来执行DDL语句需要直接把CREATE TABLE之类的权限直接赋给该用户,通过角色赋的权限会失效,即可能出现“权限不足”的异常。
那么,最好执行“grant create table to fw”给予权限。
上面的例子中,动态SQL是接受了过程的输入参数,同样的,它也可以接收绑定到某个被声明了的变量的值。
那么再看一个例子:
我的需求是根据用户的输入动态地向表里插入一条记录
表名是math,模式是fw,表的结构如下:
名称 是否为空? 类型
----------------------------------------- -------- -------------
N1 NUMBER(38)
N2 NUMBER(38)
MAX NUMBER(38)
首先还是创建一个过程:
create or replace procedure
insert_into_math(n1_input fw.math.n1%type,n2_input fw.math.n2%type,max_input fw.math.max%type)
is
sql_stmt varchar2(255);
begin
sql_stmt := 'insert into fw.math values(:n1,:n2,:max)';
execute immediate sql_stmt using n1_input,n2_input,max_input;
commit;
dbms_output.put_line('插入成功');
exception when others then
dbms_output.put_line(sqlerrm);
end;
在动态SQL“'insert into fw.math values(:n1,:n2,:max)”中:n1,:n2,:,max都是形式为:xxx的占位符,和Hibernate的HQL里的:xxx是一样的,XXX的实质也就是一个绑定变量,因此在引用的时候要在之前加:。而using xxx的xxx代表这些动态的参数要接收名为xxx的变量的值。其实这里也就相当于JDBC里的预编译SQL“insert into fw.math values(?,?,?)”。
接下来,我在PL/SQL里调用上面这个过程:
declare
n1_input fw.math.n1%type;
n2_input fw.math.n2%type;
max_input fw.math.max%type;
begin
n1_input := '&请输入n1的值';
n2_input := '&请输入n2的值';
max_input := '&请输入max的值';
fw.insert_into_math(n1_input,n2_input,max_input);
end;
同样的,动态SQL不仅能接受输入参数,也能用“into XXX”将执行后返回的结果交给一个名为XXX的变量作为输出参数保存。
例如:
declare
n1_input fw.math.n1%type;
count_output number;
sql_stmt varchar2(255);
begin
n1_input := '&请输入n1的值';
sql_stmt:='select count(*) from fw.math where n1=:n1';
execute immediate sql_stmt into count_output using n1_input;
dbms_output.put_line('根据n1='||n1_input||'找到的记录有'||count_output||'条');
exception when others then
dbms_output.put_line(sqlerrm);
end;
以上就是ORACLE中本地动态SQL的基本用法,另外还有一种形式的动态SQL是通过PL/SQL的内置包DBMS_SQL的一些过程和函数通过操作游标来进行的,这里就不再多说。
分享到:
相关推荐
Oracle中动态SQL详解,开发人员必备。
Oracle动态执行SQL四种方式的例子
Oracle动态SQL之本地动态SQL的使用.pdf
资深开发DBA对Oracle SQL编写规范的总结
数据库ORACLE入门基础,本地动态SQL执行语句,PL/SQL数据类型,有具体实例。
Oracle中执行动态SQL的几种方法 在一般的sql操作中,sql语句基本上都是固定的,如: SELECT t.empno,t.ename FROM scott.emp t WHERE t.deptno = 20; 但有的时候,从应用的需要或程序的编写出发,都可能需要用到动态...
oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具...
第17章 本地动态sql 第18章 pl/sql过程 第19章 pl/sql函数 第20章 pl/sql包 第21章 触发器 第22章 使用对象类型 第四部分 pl/sql系统包 第23章 使用大对象 第24章 读写os文件 第25章 开发多媒体...
总结Oracle 脚本编程中,动态SQL的几种用法
oracle数据库的性能优化直接关系到系统的运行效率,而影响数据库性能的一个重要因素就是sql性能问题。本书是作者十年磨一剑的成果之一,深入分析与解剖oracle sql优化与调优技术,主要内容包括: 第一篇“执行计划...
第17章 本地动态sql 第18章 pl/sql过程 第19章 pl/sql函数 第20章 pl/sql包 第21章 触发器 第22章 使用对象类型 第四部分 pl/sql系统包 第23章 使用大对象 第24章 读写os文件 第25章 开发多媒体...
本文讲解了基于Oracle高性能动态SQL程序开发。
对ORACLE-SQL进行一些布局优化,更新它的格式
简单描述Oracle v$sqlarea, v$sql, v$sqltext视图说明
第17章本地动态SQL 第18章PL/SQL过程 第19章PL/SQL函数 第20章PL/SQL包 第21章触发器 第22章使用对象类型 第四部分PL/SQL系统包 第23章使用大对象 第24章读写OS文件 第25章开发多媒体应用 第26...
execute immediate str_sql into tabtcn; --动态执行DDL语句
Oracle数据库监听工具
很难得的SQL(oracle)学习总结哟!
ORACLE 数据库 SQL ORACLE 数据库 SQL ORACLE 数据库 SQL ORACLE 数据库 SQL ORACLE 数据库 SQL ORACLE 数据库 SQL
Oracle与SQLServer的SQL语法差异,用简单易懂的语言和实例对Oracle和Sql Server语法之间的差异进行了对比分析,更加适用于入门的人