注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

和申的个人主页

专注于java开发,1985wanggang

 
 
 

日志

 
 

引用 plsql 动态SQL  

2010-10-25 13:30:15|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
本文转载自zhengweisincere《plsql 动态SQL》

引用 plsql 动态SQL - 和申 - 和申的个人主页

引用

zhengweisincereplsql 动态SQL

 

declare
user_id varchar2(50);
sql_stmt varchar2(255);
showuser_id varchar2(50);
begin
  user_id  := 'a';
  sql_stmt := 'select sex from test where userid = :user_id';
  execute immediate sql_stmt into showuser_id using user_id;
  dbms_output.put_line(showuser_id);
end;

通常的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的基本用法。


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/LovoEducation/archive/2009/07/17/4357266.aspx

  评论这张
 
阅读(783)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016