一、如果要把 DBMS_OUTPUT.PUT_LINE(v_rolename)里的内容显示出来，在sql*plus 里面首先要运行命令
SET SERVEROUTPUT ON;
二、游标分类：
利用REF CURSOR，可以在程序间传递结果集（一个程序里打开游标变量，在另外的程序里处理数据）。
也可以利用REF CURSOR实现BULK SQL，提高SQL性能。
REF CURSOR分两种，Strong REF CURSOR 和 Weak REF CURSOR
Strong REF CURSOR:指定retrun type，CURSOR变量的类型必须和return type一致。
Weak REF CURSOR:不指定return type，能和任何类型的CURSOR变量匹配
CREATE OR REPLACE PROCEDURE test_pp
IS
type v_cursor is ref cursor;
abc v_cursor;
v_str varchar2(200);/*不能用NVARCHAR2*/
v_rolename varchar2(200);
v_id varchar2(200);
BEGIN
v_str := 'select ROLE_NAME,ROLE_ID from POW_ROLE ';
OPEN abc FOR v_str ;
LOOP FETCH abc INTO v_rolename,v_id;
EXIT WHEN abc%NOTFOUND;
insert into testinsert(testname,id)values (v_rolename,v_id);
commit;
DBMS_OUTPUT.PUT_LINE(v_rolename);
END LOOP;
DBMS_OUTPUT.PUT_LINE('OK');
CLOSE abc;
END;
*************************************************8
create or replace function F_BHACTIONREC(i_funit varchar2,
i_ym varchar2)
return number is
currentvalue number;
v_value number;
n_total number;
v_ym varchar2(40);
Type c_bhfaultlist Is Ref Cursor; -- 定义动态游标
c_dtcursor c_bhfaultlist; -- 声明动态游标
v_csqlstr Varchar2(4000); -- 动态游标SQL
begin
v_ym :=i_ym||'25';
if i_funit='BD' or i_funit='DL' or i_funit='SD' or i_funit='SYY' then
v_csqlstr:='SELECT (min(a.handler_time) - b.factiontime) AS det
FROM DMIS_BH_ACTIONREC B, j_wf_processhistory a
WHERE B.FPROTECTIONRECORDID = a.bussiness_id
and to_date(to_char(b.factiontime,''yyyymmdd''),''yyyymmdd'')>=
add_months(to_date('''||v_ym||''',''yyyymmdd''),-1)
and to_date(to_char(b.factiontime,''yyyymmdd''),''yyyymmdd'')<to_date('''||v_ym||''',''yyyymmdd'')
and b.FVOLTAGE >=110
AND B.SYS_DATAOWNER = '''||i_funit||'''
and b.factiontime is not null and a.node_name = ''市调保护专工''
group by b.factiontime,b.fprotectionrecordid ';
else
v_csqlstr:='SELECT (min(a.SENDTIME) - b.factiontime) AS det
FROM DMIS_BH_ACTIONREC B, MESSAGE_RECV a WHERE
B.FPROTECTIONRECORDID = a.BUSS_ID and
to_date(to_char(b.factiontime,''yyyymmdd''),''yyyymmdd'')>=
add_months(to_date('''||v_ym||''',''yyyymmdd''),-1)
and to_date(to_char(b.factiontime,''yyyymmdd''),''yyyymmdd'')<to_date('''||v_ym||''',''yyyymmdd'')
AND B.SYS_DATAOWNER = '''||i_funit||'''
and b.factiontime is not null
and b.FVOLTAGE >=110
group by b.factiontime,b.fprotectionrecordid ';
end if;
currentvalue:=0;
open c_dtcursor for v_csqlstr;
loop
fetch c_dtcursor into v_value;
exit when c_dtcursor%notfound;
if v_value<=7 then
n_total:=0;
else
n_total:=floor(v_value-7);
end if;
currentvalue:=currentvalue+n_total;
end loop;
close c_dtcursor;
return(currentvalue);
end F_BHACTIONREC;
****************************************************
create or replace procedure ADJUST_TRAINPLAN_PK(inparams in varchar2,outparam out varchar)
is
--扫描历史的重复记录
cursor train_plan is
select fplanid, version, fdeptname, fclasstypeobj, fprojectname,
ftrainobj, ftraintimes, ffinishtimes, fmembers, fdays,
fmembersall, ftrainmonth, ftrainyear, fresponsor, fphone,
fteachernum, fbookfee, fteacherfee, fclassroomfee, fotherfee,
ftrainfee, fboardlodgfee,fremark, fexcutestatus, fcancelreason,
fcancelperon,fcanceltime, sys_dataownername,fimportantorder,sys_unitcode,
sys_fille, sys_filldept, sys_filltime, sys_isvalid, sys_dataowner,
fclassowner, ftraintype, fclasstype, excolumn1, excolumn2,
excolumn3, excolumn4, excolumn5
from dmis_js_Trainplan a
where sys_filltime < (select max(sys_filltime)
from dmis_js_Trainplan b
where a.fprojectname = b.fprojectname and
a.Ftrainyear = b.Ftrainyear)
order by a.fprojectname,a.ftrainyear,a.ftrainmonth desc;
cur_1 train_plan%rowtype;
--扫描新导入的同年相同计划名称的记录
cursor update_train_plan is
select fplanid, version, fdeptname, fclasstypeobj, fprojectname,
ftrainobj, ftraintimes, ffinishtimes, fmembers, fdays,
fmembersall, ftrainmonth, ftrainyear, fresponsor, fphone,
fteachernum, fbookfee, fteacherfee, fclassroomfee, fotherfee,
ftrainfee, fboardlodgfee,fremark, fexcutestatus, fcancelreason,
fcancelperon,fcanceltime, sys_dataownername,fimportantorder,sys_unitcode,
sys_fille, sys_filldept, sys_filltime, sys_isvalid, sys_dataowner,
fclassowner, ftraintype, fclasstype, excolumn1, excolumn2,
excolumn3, excolumn4, excolumn5
from dmis_js_Trainplan
where fplanid not in (select fplanid
from dmis_js_Trainplan a
where sys_filltime < (select max(sys_filltime)
from dmis_js_Trainplan b
where a.fprojectname = b.fprojectname and
a.Ftrainyear = b.Ftrainyear)
) and
Ftrainyear = cur_1.ftrainyear and
fprojectname = cur_1.fprojectname;
cur_2 update_train_plan%rowtype;
m_id varchar2(4000);
m_sql varchar2(4000);
begin
/*
update dmis_js_Trainplan
set sys_fille = sysdate
where sys_fille is null;
commit;
*/
--扫描历史的重复记录(这些记录将被覆盖)
open train_plan;
loop
fetch train_plan into cur_1;
if train_plan%notfound then
exit;
end if;
--扫描新导入的同年相同计划名称的记录(这些记录将被删除)
open update_train_plan;
loop
fetch update_train_plan into cur_2;
if update_train_plan%notfound then
exit;
end if;
update dmis_js_Trainplan
set fdeptname = cur_2.fdeptname,
fclasstypeobj = cur_2.fclasstypeobj,
fprojectname = cur_2.fprojectname,
ftrainobj = cur_2.ftrainobj,
ftraintimes = cur_2.ftraintimes,
ffinishtimes = cur_2.ffinishtimes,
fmembers = cur_2.fmembers,
fdays = cur_2.fdays,
fmembersall = cur_2.fmembersall,
ftrainmonth = cur_2.ftrainmonth,
ftrainyear = cur_2.ftrainyear,
fresponsor = cur_2.fresponsor,
fphone = cur_2.fphone,
fteachernum = cur_2.fteachernum,
fbookfee = cur_2.fbookfee,
fteacherfee = cur_2.fteacherfee,
fclassroomfee = cur_2.fclassroomfee,
fotherfee = cur_2.fotherfee,
ftrainfee = cur_2.ftrainfee,
fboardlodgfee = cur_2.fboardlodgfee,
fremark = cur_2.fremark,
fexcutestatus = cur_2.fexcutestatus,
fcancelreason = cur_2.fcancelreason,
fcancelperon = cur_2.fcancelperon,
fcanceltime = cur_2.fcanceltime,
sys_dataownername = cur_2.sys_dataownername,
fimportantorder = cur_2.fimportantorder,
sys_unitcode = cur_2.sys_unitcode,
sys_fille = cur_2.sys_fille,
sys_filldept = cur_2.sys_filldept,
sys_filltime = cur_2.sys_filltime,
sys_isvalid = cur_2.sys_isvalid,
sys_dataowner = cur_2.sys_dataowner,
fclassowner = cur_2.fclassowner,
ftraintype = cur_2.ftraintype,
fclasstype = cur_2.fclasstype,
excolumn1 = cur_2.excolumn1,
excolumn2 = cur_2.excolumn2,
excolumn3 = cur_2.excolumn3,
excolumn4 = cur_2.excolumn4,
excolumn5 = cur_2.excolumn5
where fplanid = cur_1.fplanid;
commit;
if (m_id is null ) then
m_id := ''''||cur_2.fplanid||'''';
else
m_id := m_id||','||''''||cur_2.fplanid||'''';
end if;
end loop;
close update_train_plan;
end loop;
close train_plan;
if (m_id is not null) then
m_sql := 'delete from dmis_js_trainplan '||'where fplanid in ('||m_id||')';
else
m_sql := '';
end if;
/* execute immediate m_sql;
commit;
*/
outparam := m_sql;
end ADJUST_TRAINPLAN_PK;