Oracle 动态SQL学习笔记

转自：http://jonescheng.iteye.com/blog/183620

Oracle 动态SQL学习笔记

博客分类：

 
• [随笔分类]Oracle数据库

SQLOracleCC++C# 

function open_cursor:打开一个动态游标,并返回一个整型;

procedure close_cursor(c in out integer);关闭一个动态游标,参数为open_cursor所打开的游标;

procedure parse(c in integer, statement in varchar2, language_flag in integer):对动态游标所提供的sql语句进行解析,参数C表示游标,statement为sql语句,language-flag为解析sql语句所用oracle版本,一般有V6,V7跟native(在不明白所连database版本时,使用native);

procedure define_column(c in integer, position in integer, column any datatype, [column_size in integer]):定义动态游标所能得到的对应值,其中c为动态游标,positon为对应动态sql中的位置(从1开始),column为该值所对应的变量,可以为任何类型,column_size只有在column为定义长度的类型中使用如VARCHAR2,CHAR等(该过程有很多种情况,此处只对一般使用到的类型进行表述);

function execute(c in integer):执行游标,并返回处理一个整型,代表处理结果(对insert,delete,update才有意义,而对select语句而言可以忽略);

function fetch_rows(c in integer):对游标进行循环取数据,并返回一个整数,为0时表示已经取到游标末端;

procedure column_value(c in integer, position in integer, value):将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量;

procedure bind_variable(c in integer, name in varchar2, value):定义动态sql语句(DML)中所对应字段的值,c为游标,name为字段名称,value为字段的值;

以上是在程序中经常使用到的几个函数及过程,其他函数及过程请参照oracle所提供定义语句dbmssql.sql

(二)一般过程
对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤:
open cursor--->parse--->define column--->excute--->fetch rows--->close cursor;
而对于dml操作(insert,update)则需要进行以下几个步骤:
open cursor--->parse--->bind variable--->execute--->close cursor;
对于delete操作只需要进行以下几个步骤:
open cursor--->parse--->execute--->close cursor;


实例分析
create or replace procedure p_oneproduc is
--变量定义
iid int;
icount int;
strtblname varchar2(100);
ssql varchar2(1024);
serrmsg varchar2(1024);
cid1 number;--动态游标光标编号
cid2 number;--动态光标编号
iipno number;--从动态光标中取出值的存放变量
idateno number;--从动态光标中取出值的存放变量
i number;
cursor cur1 is select id,table_name from userisit where status=2;--定义一个光标
--定义结束
begin
 select count(*) into icount from uservisit where status=2;//查询用户访问表将记录汇总统计存入icount中
 if icount>0 then   
    open cur1;//打开cur1光标
      fetch cur1 into iid,strtblname; //取出光标的值并赋值给iid,strtblname，相应的变量和select变量同序
      exit when cur1%notfound; //如果记录为空时退出 cur%notfound是cur的一个变量，当记录为空时为true
      --重头戏，，动态SQL开始(具体可以参考前面的方法说明)
      ssql:='select distinct ipno,dateno from '||strtblname; --定义一个SQL语句，后面用动态SQL进行执行
      cid1:=dbms_sql.open_cursor; --定义一个动态光标用来执行前面定义的SQL语句
      dbms_sql.Parse(cid1,ssql,dbms_sql.v7); --分析SQL语句
      dbms_sql.Define_Column(cid1,1,iipno); --定义要取出的字段值,1表示第一个字段要取出，即Select语句的ipno
      dbms_sql.Define_Column(cid1,2,idateno);--同上说明
      icount:=dbms_sql.execute(cid1); --执行Sql语句，这里icount得到一个执行的结果
      --执行SQL语句可以和普通光标一样进行取值操作
      i:=0;
      loop
        If dbms_sql.fetch_rows(cid1) > 0 then --如果动态光标的记录数大于0，则进行取值操作
          begin
            dbms_sql.column_value(cid1,1,iipno); --取出值
            dbms_sql.column_value(cid1,2,idateno);--同上
            
            select count(*) into icount from pmhtmpunchkcpc@unionbill where ipno=iipno anddateno=idateno;
            if icount>0 then
              ssql:='update FIRSTCDAY_'||idateno||'@unionbill set isreach=1 where ipno='||iipno;
              execute immediate ssql;//动态立即执行一个SQL语句
            end if
           end
        end if
      end loop
        exception when others then
           null;
        end;
   --运用动态光标执行一组插入操作 （纯碎是为了记录动态插入中的赋值的用法，无逻辑可言）
     ssql:= 'insert uservisit(ipno,dateno) values(:iipno,:idateno)'; --定义一个SQL语句，后面用动态SQL进行执行
     cid2:=dbms_sql.open_cursor; --定义一个动态光标用来执行前面定义的SQL语句
     dbms_sql.Parse(cid2,ssql,v7);
     for j in 1..999 loop
      dbms_sql.bind_variable(cid2, 'iipno', j);
      dbms_sql.bind_variable(cid2, 'idateno', 2);
      icount := dbms_sql.execute(cid2);--插入数据
    end loop;


     --关闭光标的不要忘记了
     if(dbms_sql.is_open(cid1)) then --如果动态光标仍然是开的
        dbms_sql.close_cursor(cid1); --关闭
     end if;
     ---记得关掉第二个动态光标
     if cur1%isopen then
        close cur1;
     end if;

动态游标实例练习

declare
  v_curId   integer;
  v_sql     varchar2(1000);
  v_well_id varchar2(100);
  v_ret     number(3);
  v_id      varchar2(10);
begin
  v_id  := 'abcdefgh';
  v_sql := 'SELECT ID FROM TEST3 WHERE ID = LOWER('||chr(39)||v_id||chr(39)||')';
  v_curId := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_curId, v_Sql, DBMS_SQL.V7);
  DBMS_SQL.DEFINE_COLUMN(v_curId, 1, v_well_id, 128);
  v_ret := DBMS_SQL.EXECUTE(v_curId);

  loop
    if DBMS_SQL.FETCH_ROWS(v_curId) = 0 then
      exit;
    end if;
    DBMS_SQL.COLUMN_VALUE(v_curId, 1, v_well_id);
    DBMS_OUTPUT.PUT_LINE(v_well_id);
  end loop;
  DBMS_SQL.CLOSE_CURSOR(v_curid);
end;