oracle包如何写,自己写的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;