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);

/*(二)一般过程

对于一般的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;

*/

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;