Learning notes for Oracle stored procedures

1. Simple example of stored procedure

CREATE OR REPLACE PROCEDUREstored procedure name (parameter in, parameter out) as--variable declarations, with each declaration ending with a semicolon. Can be initialized at the same time as the declarationNamevarchar2( -); age Number(8)default 0;--Start logical OperationBEGIN--Business LogicEND

2, the cursor implementation method,

--Explicit implementation (multiple values can be achieved)

cursorCursorvar is Selectevent_id, Isagain, rate fromCall_eventwhereSender=V_sender;--DECLARE cursors, select statements can include single quotes, and so on.   begin      OpenCursorvar;--Open CursorLoopFetchCursorvar intov_event_id, V_isagain, v_rate;--Take value         Exit  whenCursorvar%NotFound--exit loops when no records are recordedDbms_output.put_line (v_event_id|| ', ' ||V_isagain|| ', ' ||v_rate); EndLoop; CloseCursorvar;--Close Cursors         --the properties of the cursor are:%found,%notfounrd,%isopen,%rowcount;     --%found: Returns True when records have been retrieved    --%NOTFOUNRD: Returns True when records are not retrieved    --%isopen: Returns True when the cursor is open    --%rowcount: Represents the number of records retrieved, starting from 1End;

--implicit cursor (can implement query multi-value)

 for inch (     Select  t.col1, t.col2     from  tableName t     where  ...  ) Loop      if=0then         return;    -- Abort sp, return     End if ;   End

--Cursors with parameters (can implement query multi-value)

DeclareisOKinteger; v_event_id Number(Ten); V_isagain Number(2); V_rate Number(2); V_senderChar( One) := '13800138000'; cursorCursorvar (P_sendervarchar2) is Selectevent_id, Isagain, rate fromCall_eventwhereSender=P_sender;--declaring Cursors  begin      OpenCursorvar (V_sender);--Open the cursor and pass the arguments in parentheses. LoopFetchCursorvar intov_event_id, V_isagain, v_rate;--Take value         Exit  whenCursorvar%NotFound--exit loops when no records are recordedDbms_output.put_line (v_event_id|| ', ' ||V_isagain|| ', ' ||v_rate); EndLoop; CloseCursorvar;--Close CursorsEnd;

3. Exception Handling

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;

Refer to Oracle stored procedure Basic syntax + Lift + Example Summary

Basic syntax for Oracle stored procedures and basic tutorials

Learning notes for Oracle stored procedures