Oracle Stored Procedure Syntax

1. Basic structure CREATE OR REPLACE PROCEDURE stored procedure name (parameter 1 in number, parameter 2 in number) is variable 1 INTEGER: = 0; Variable 2 DATE; The BEGIN end stored procedure name 2.SELECT into STATEMENT stores the results of a SELECT query into a variable, can store multiple columns simultaneously in multiple variables, must have a record, or else throw an exception (if no record throws No_data_found) Example: BEGIN SELECT col1,col2 into variable 1, variable 2 from typestruct where xxx; EXCEPTION when No_data_found THEN xxxx; End; ... 3.IF to determine IF v_test=1 THEN BEGIN do something end; End IF; 4.while Loop while V_test=1 loop BEGIN XXXX end; End LOOP; 5. Variable assignment value v_test: = 123; 6. Use for cursor ... is CURSOR cur are SELECT * from XXX; Begin for Cur_result in cur LOOP begin v_sum: =cur_result. Column name 1+cur_result. Column Name 2 end; End LOOP; End; 7. CURSOR CURSOR C_user (c_id number) is SELECT NAME from USER WHERE typeid=c_id with parameters; OPEN c_user (variable value); LOOP FETCH c_user into V_name; EXIT FETCH C_user%notfound; Do something end LOOP; Close C_user; 8. Use PL/SQL Developer Debug Connect the database to create a test window in the window to enter the calling SP code, F9 start debug,ctrl+n step debugging This article is from the original PHP Information link: http:// Www.phpq.net/oracle/oracle-stored-procedure-syntax.html