Basic oracle Stored Procedure syntax

1. syntax structure of the stored procedure:

Create or replace procedure process name

Declaration statement segment;

BEGIN

Execution statement segment;

EXCEPTION

Exception Handling statement segment;

END;

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)
For 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;

Create a stored procedure instance

Create or replace procedure stu_proc
-- Declaration statement segment
V_name varchar2 (20 );
BEGIN
-- Execution statement segment
SELECT o. sname INTO v_name FROM student o WHERE o. id = 1;
DBMS_OUTPUT.put_line (v_name );
EXCEPTION
-- Exception Handling statement segment
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('no _ DATA_FOUND ');
END;

-- Call a stored procedure
-- CALL stu_proc ();