oracle存储过程命名参数,oracle 存储过程 表名 列名 作参数

create or replace procedure proc_cq (tblName in varchar2,colName in varchar2)is

v_xt_dm varchar2(50);

v_bm varchar2(20) ;

v_zdm varchar2(100) ;

v_dm varchar2(100);

v_yh varchar2(20);

tblnm char(50);

strsql char(200);

TYPE refcursor IS REF CURSOR;

v_cursor REFCURSOR;

v_name VARCHAR(50);

begin

tblnm:=tblname;

strsql:='select distinct '||colName|| ' MZ from '||tblnm; --SJBZDMBZB_DM

-- 打开游标.

OPEN v_cursor for strsql;

--变量付值

select xt_dm,bm,zdm into v_xt_dm,v_bm,v_zdm from bz_cq ;

select yh into v_yh from bz_rjxt where xt_dm=v_xt_dm ;

-- 循环处理.

loop

FETCH v_cursor INTO v_name;

--无数据时 退出循环

EXIT WHEN v_cursor%NOTFOUND;

--组合Dm

select xt_dm||'_'||v_yh||'_'||bm||'_'||zdm||'_'||v_name into v_dm from bz_cq ;

insert into BZ_XTSJKMZCQB (XTSJKMZCQB_DM,XT_DM,YH,BM,ZDM,MZ,LR_SJ,XG_SJ)

select v_dm, xt_dm,v_yh,bm,zdm,v_name, sysdate, sysdate from bz_cq ;

end loop ;

-- 关闭游标.

CLOSE v_cursor;

--提交

commit;

end proc_cq ;