Oracle Stored Procedure Basics

1. Basic Structure
Create OR REPLACE PROCEDURE
(
Parameter 1 in number,
Parameter 2 IN NUMBER
) IS
Variable 1 INTEGER: = 0;
Variable 2 DATE;
BEGIN
END stored procedure name
2. select INTO STATEMENT
Save the select query result to a variable. You can store multiple columns in multiple variables at the same time. One record is required; otherwise, an exception is thrown (if no record exists, NO_DATA_FOUND is thrown)
Example:
BEGIN
Select col1, col2 into variable 1, variable 2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Xxxx;
END;
...
3. IF judgment
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
V_TEST: = 123;
6. Use cursor with for in
...
IS
CURSOR cur IS 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 with Parameters
CURSOR C_USER (C_ID NUMBER) IS select name from user where TYPEID = C_ID;
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
Create a Test WINDOW after connecting to the database
Enter the SP call code in the window, F9 start debug, CTRL + N single-step debugging