Oracle Stored Procedures primer to learn basic syntax

1. Basic structure
create OR REPLACE PROCEDURE stored procedure name
(
Parameter 1 IN NUMBER,
Parameter 2 IN NUMBER
) IS
Variable 1 INTEGER :=0;
The variable 2 DATE;
BEGIN
END stored procedure name
2. select INTO STATEMENT
The result of the select query is stored in a variable. Multiple columns can be stored in multiple variables at the same time. There must be 1 record, or an exception is thrown (if no record is thrown, NO_DATA_FOUND).
Example:
BEGIN
select col1,col2 into variable 1, FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
.
3. IF judgment
IF V_TEST THEN = 1
BEGIN
do something
END;
END IF;
4. while cycle
WHILE V_TEST LOOP = 1
BEGIN
XXXX
END;
END LOOP;
5. Variable assignment
V_TEST: = 123;
6. Use for in
.
IS
CURSOR cur 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;
cursor with parameters
CURSOR C_USER(C_ID NUMBER) IS select FROM 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;
Use pl/sql developer debug
Establish 1 Test WINDOW after connecting to the database
Enter the code to call SP in the window,F9 starts debug,CTRL+N single-step debugging