Oracle Stored Procedure Instance 2

--create a stored procedureCREATE OR REPLACE PROCEDURExxxxxxxxxxx_p (--The parameter in represents the input parameter, out represents the input parameter, and the type can use the legal type in any Oracle. Is_yminch CHAR) as--Defining Variablesvs_msgVARCHAR2(4000);--error message VariableVs_ym_begCHAR(6);--Start MonthVs_ym_endCHAR(6);--End MonthVs_ym_sn_begCHAR(6);--same period start monthVs_ym_sn_endCHAR(6);--month ending in the same period--define a cursor (simply a result set that can be traversed) CURSORCur_1 is SELECTArea_code,cmcode,SUM(Rmb_amt)/10000RMB_AMT_SN,SUM(Usd_amt)/10000USD_AMT_SN frombgd_area_cm_m_base_tWHEREYm>=Vs_ym_sn_beg andYm<=Vs_ym_sn_endGROUP  byArea_code,cmcode;BEGIN --using the input parameters to assign the initial value to the variable, the Oralce substr to_char add_months to_date are used. Vs_ym_beg:=SUBSTR (Is_ym,1,6); Vs_ym_end:=SUBSTR (Is_ym,7,6); Vs_ym_sn_beg:=To_char (Add_months (To_date (Vs_ym_beg,'yyyymm'),- A),'yyyymm'); Vs_ym_sn_end:=To_char (Add_months (To_date (Vs_ym_end,'yyyymm'),- A),'yyyymm'); --Delete the data for a specific condition in the table first.  DELETE  fromxxxxxxxxxxx_tWHEREYm=Is_ym; --The Put_Line method of the built-in Dbms_output object is then used to print the number of recorded rows affected, which uses a system variable Sql%rowcountDbms_output.put_line ('del last month record ='||Sql%RowCount||'Strip'); INSERT  intoxxxxxxxxxxx_t (Area_code,ym,cmcode,rmb_amt,usd_amt)SELECTArea_code,is_ym,cmcode,SUM(Rmb_amt)/10000,SUM(Usd_amt)/10000  frombgd_area_cm_m_base_tWHEREYm>=Vs_ym_beg andYm<=Vs_ym_endGROUP  byArea_code,cmcode; Dbms_output.put_line ('ins Month record ='||Sql%RowCount||'Strip'); --updates to the table after the traversal cursor processing. There are several ways to traverse a cursor, which is more intuitive with a for statement.   forRecinchcur_1 LOOPUPDATExxxxxxxxxxx_tSETRmb_amt_sn=Rec.rmb_amt_sn,usd_amt_sn=REC.USD_AMT_SNWHEREArea_code=Rec.area_code andCmcode=Rec. Cmcode andYm=Is_ym;ENDLOOP; COMMIT; --error Handling section. Others represents an arbitrary error except for declarations. SQLERRM is a system built-in variable that holds the details of the current error. EXCEPTION whenOTHERS Thenvs_msg:= 'ERROR in Xxxxxxxxxxx_p ('||Is_ym||'):'||SUBSTR (SQLERRM,1, -); ROLLBACK; --Logs the current error into the log table.    INSERT  intolog_info (proc_name,error_info,op_date)VALUES('xxxxxxxxxxx_p', vs_msg,sysdate); COMMIT; RETURN;END;

Oracle Stored Procedure Instance 2