Oracle stored procedure (1)

1. to display the content in dbms_output.put_line (v_rolename), run the command in SQL * Plus first.

Set serveroutput on;

Ii. cursor classification:

Using ref cursor, you can pass the result set between programs (one program opens the cursor variable and processes data in another program ).
You can also use ref cursor to implement bulk.SQLTo improve SQL Performance.

 

There are two types of ref cursor: Strong ref cursor and weak ref cursor.

 

Strong ref cursor: Specify the retrun type. The cursor variable must be of the same type as the return type.

Weak ref cursor: return type is not specified and can match any type of cursor variable

 

 

Create or replace procedure test_pp
Is
Type v_cursor is ref cursor;
ABC v_cursor;
V_str varchar2 (200);/* nvarchar2 cannot be used */
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; -- defines a Dynamic Cursor
C_dtcursor c_bhfaultlist; -- declares a Dynamic Cursor
V_csqlstr varchar2 (4000); -- Dynamic Cursor 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
Where B. fprotectionrecordid = A. bussiness_id
And to_date (to_char (B. factiontime, ''yyymmdd'), ''yyymmdd')> =
Add_months (to_date (''' | v_ym | ''', ''yyyymmdd ''),-1)
And to_date (to_char (B. factiontime, ''yyymmdd'), ''yyymmdd') <to_date (''' | v_ym | ''', ''yyymmdd '')
And B. fvoltage >=110
And B. sys_dataowner = ''' | I _funit | '''
And B. factiontime is not null and A. node_name = ''municipal adjustment protection specialization''
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, ''yyymmdd'), ''yyymmdd')> =
Add_months (to_date (''' | v_ym | ''', ''yyyymmdd ''),-1)
And to_date (to_char (B. factiontime, ''yyymmdd'), ''yyymmdd') <to_date (''' | v_ym | ''', ''yyymmdd '')
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
-- Scan history duplicate records
Cursor train_plan is
Select fplanid, version, fdeptname, fclasstypeobj, fprojectname,
Ftrainobj, ftraintimes, ffinishtimes, fmembers, fdays,
Fmembersall, ftrainmonth, ftrainyear, frestmsor, fphone,
Fteachernum, fbook.pdf, fteacher.pdf, fclassroom.pdf, fother.pdf,
Ftrainte, fboardlodg.pdf, 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 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;
-- Scan records with the same plan name as newly imported
Cursor update_train_plan is
Select fplanid, version, fdeptname, fclasstypeobj, fprojectname,
Ftrainobj, ftraintimes, ffinishtimes, fmembers, fdays,
Fmembersall, ftrainmonth, ftrainyear, frestmsor, fphone,
Fteachernum, fbook.pdf, fteacher.pdf, fclassroom.pdf, fother.pdf,
Ftrainte, fboardlodg.pdf, 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
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;
*/
-- Scan Repeated Records of History (these records will be overwritten)
Open train_plan;
Loop
Fetch train_plan into cur_1;
If train_plan % notfound then
Exit;
End if;
-- Scan newly imported records with the same plan name for the same year (these records will be deleted)
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,
Fbooktables = cur_2.fbooktables,
Fteacherworkflow = cur_2.fteacherworkflow,
Fclassroomfee = cur_2.fclassroomfee,
Fother.pdf = cur_2.fother.pdf,
Ftrainfee = cur_2.ftrainfee,
Fboardlodg.pdf = cur_2.fboardlodg.pdf,
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;