oracle dbms函數大全,自己寫的oracle dbms_sql 函數包例子

create or replace procedure pro_insert_dbms

is

cid number;

cid_DDL number;

selectsql varchar2(1000);

ddlsql    varchar2(1000);

row_count number;

nofrows   integer;

lrowid                           rowid;

ddlrows   integer;

errpsn                           integer;

sqlfcd                           integer;

errc                             integer;

errm                             varchar2(2000);

request_id varchar2(100);

deal_name varchar2(100);

member_name varchar2(100);

begin

dbms_output.put_line('測試dbms_sql包.....');

delete_commit('delete from test_insert i where i.inner_id=2');

selectsql:='select i.request_id,i.deal_name,i.member_name  from test_insert i';

begin

cid:=dbms_sql.open_cursor;

--parse(c in integer, statement in varchar2, language_flag in integer):

--對動态遊标所提供的sql語句進行解析,參數C表示遊标,statement為sql語句,

--language-flag為解析sql語句所用oracle版本,一般有V6,V7跟native(在不明白所連database版本時,使用native);

dbms_sql.parse(cid,selectsql,dbms_sql.native);

--定義動态遊标所能得到的對應值,其中c為動态遊标,

--positon為對應動态sql中的位置(從1開始),column為該值所對應的變量,

--可以為任何類型,column_size隻有在column為定義長度的類型中使用

--如VARCHAR2,CHAR等

dbms_sql.define_column(cid,1,request_id,100);

dbms_sql.define_column(cid,2,deal_name,100);

dbms_sql.define_column(cid,3,member_name,100);

row_count:=0;

nofrows:=dbms_sql.execute(cid);

exception

when others then

errpsn:=dbms_sql.last_error_position;

sqlfcd:=dbms_sql.last_sql_function_code;

lrowid:=dbms_sql.last_row_id;

errc:=SQLCODE;

errm:=sqlerrm;

dbms_output.put_line('Error '||to_char(errc)||' Posn '||to_char(errpsn)||' SQL fCode '||to_char(sqlfcd)||' rowid '||rowidtochar(lrowid));

raise_application_error(-20000,errm);

end;

begin

loop

--fetch_rows(c in integer):對遊标進行循環取資料,并傳回一個整數,

--為0時表示已經取到遊标末端

if dbms_sql.fetch_rows(cid)>0 then

begin

--column_value(c in integer, position in integer, value):

--将所取得的遊标資料指派到相應的變量,c為遊标,position為位置,

--value則為對應的變量;

dbms_sql.column_value(cid,1,request_id);

dbms_sql.column_value(cid,2,deal_name);

dbms_sql.column_value(cid,3,member_name);

dbms_output.put_line(request_id||'---'||deal_name||'---'||member_name);

row_count:=row_count+1;

if row_count=2 then

begin

--TempSql := 'insert into ' || totab || ' values(:r_no, :i_interval, :i_time, :i_index)';

cid_DDL:=dbms_sql.open_cursor;                                 ddlsql:='insert into test_insert(inner_id,request_id,deal_name,member_name) values(100,:request_id,:deal_name,:member_name)';                                 dbms_sql.parse(cid_DDL,ddlsql,dbms_sql.native);                                 dbms_sql.bind_variable(cid_DDL,'request_id',request_id);                                 dbms_sql.bind_variable(cid_DDL,'deal_name',deal_name);                                 dbms_sql.bind_variable(cid_DDL,'member_name',member_name);                                 ddlrows:=dbms_sql.execute(cid_DDL);                                 commit;                                 dbms_sql.close_cursor(cid_DDL);                                                             end;                      end if;               end;   else                begin                 commit;                 exit;               end;     end if;    end loop; end;   exception    when others then      errpsn:=dbms_sql.last_error_position;      sqlfcd:=dbms_sql.last_sql_function_code;      lrowid:=dbms_sql.last_row_id;      errc:=SQLCODE;      errm:=sqlerrm;      dbms_output.put_line('Error '||to_char(errc)||' Posn '||to_char(errpsn)||' SQL fCode '||to_char(sqlfcd)||' rowid '||rowidtochar(lrowid));      raise_application_error(-20000,errm);       -----------關閉cursor   begin      dbms_sql.close_cursor(cid);   exception-----關閉時異常處理         when others then          errpsn:=dbms_sql.last_error_position;          sqlfcd:=dbms_sql.last_sql_function_code;          lrowid:=dbms_sql.last_row_id;          errc:=SQLCODE;          errm:=sqlerrm;      dbms_output.put_line('Error '||to_char(errc)||' Posn '||to_char(errpsn)||' SQL fCode '||to_char(sqlfcd)||' rowid '||rowidtochar(lrowid));      raise_application_error(-20000,errm);      end; end pro_insert_dbms;