PL/SQL Reference

Below are some PL/SQL Examples for your reference which will help you in writing the stored procedures in your application.
Note: The Quotes “‘” may have a different Unicode value and you may need to replace them with the good ones.

Create Procedure

CREATE OR REPLACE PROCEDURE “PROCEDURE_NAME”
(firstVar IN NUMBER,
secondVar IN VARCHAR2,
thirdVar IN NUMBER )
IS
BEGIN
— Your execution code
END;

Calling Procedure

BEGIN
proedure_name(1, ‘IThelp’ ,5);
dbms_output.put_line(‘Process Successfully Executed’);
END;

Delete Procedure

DROP PROCEDURE procedure_name;

Declare and Initialize Variables

DECLARE
n1 NUMBER := 42;
c1 CONSTANT NUMBER := 98;
BEGIN
–Rest of the code–
END

Using IF ELSE ELSIF

DECLARE
— Declarations
BEGIN
— check the boolean condition using if statement
IF( <condition> ) THEN
— Code Statements
ELSIF (<condition_2>) THEN
— Code Statements
ELSE
— Else Statements
END IF;
END;

Using CASE

DECLARE
— Declarations
BEGIN
CASE variable_name
WHEN ‘1’ THEN dbms_output.put_line(‘Choice is 1’);
WHEN ‘2’ THEN dbms_output.put_line(‘Choice is 2’);
WHEN ‘3’ THEN dbms_output.put_line(‘Choice is 3’);
ELSE dbms_output.put_line(‘Choice is other’);
END CASE;
END;

Using LOOP with EXIT WHEN

DECLARE
i NUMBER := 1;
BEGIN
LOOP
EXIT WHEN i>20;
— Code statements
i := i+1;
END LOOP;
END;

Using FOR LOOP

BEGIN
FOR n IN 1..10 LOOP
— Code Statements
END LOOP;
END;

Using WHILE LOOP

DECLARE
i INTEGER := 1;
BEGIN
WHILE i <= 20 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;

Creating Functions

CREATE OR REPLACE FUNCTION function_name(num1 IN NUMBER , num2 IN NUMBER)
RETURN NUMBER
IS
num3 NUMBER(10);
BEGIN
— Operations
RETURN num3;
END;

Using Cursors: OPEN , FETCH , CLOSE

DECLARE
— Declarations
CURSOR cursor_name IS
— SELECT STATEMENT
BEGIN
OPEN cursor_name;
LOOP
FETCH cursor_name INTO — Further code
EXIT WHEN cursor_name%notfound;
END LOOP;
CLOSE cursor_name;
END;

Using Exceptions

DECLARE
— Variable Declarations
BEGIN
— Some Code

EXCEPTION
WHEN exception_1 THEN
— Exception Handling code
WHEN exception_2 THEN
— Exception Handling code
WHEN exception_3 THEN
— Exception Handling code
WHEN OTHERS THEN
— Exception Handling code

END;

Also view : Basic SQL Queries