PL/SQL Programs
1) Create a PL/SQL block for implementing declare, begin, and exception blocks.
2) Create a PL/SQL block for implementing the decision and looping statements.
3) Create a user-defined exception and raise raise_application_error.
4) Create a procedure with in and out parameters.
5) Create a function and call the function using stored procedure.
6) Develop programs using features parameters in a CURSOR, FOR UPDATE CURSOR,
WHERE CURRENT of clause and CURSOR variables.
7) Develop Programs using BEFORE and AFTER Triggers, Row and Statement Triggers.
Prepare the PL/SQL block for Dynamic SQL Execution
DECLARE
var_user VARCHAR2(30);
BEGIN
SELECT user INTO var_user FROM dual;
DBMS_OUTPUT.PUT_LINE (''Current User is ''||var_user);
END;
Write the Dynamic SQL program.
SET SERVEROUTPUT ON;
DECLARE
plsql_blk VARCHAR2 (250);
BEGIN
plsql_blk := 'DECLARE
var_user VARCHAR2 (10);
BEGIN
SELECT user INTO var_user FROM dual;
DBMS_OUTPUT.PUT_LINE (''Current User is ''||var_user);
END;';
EXECUTE IMMEDIATE plsql_blk;
END;
/
Prepare the named PL/SQL block for Dynamic SQL Execution.
CREATE OR REPLACE FUNCTION circle_area (radius NUMBER)
RETURN NUMBER IS
pi CONSTANT NUMBER(7,2) := 3.141;
area NUMBER(7,2);
BEGIN
area := pi * ( radius * radius );
RETURN area;
END;
3. Create a user-defined exception and raise raise_application_error.
User-Define Exception Using Raise_Application_Error Procedure
SET SERVEROUTPUT ON;
ACCEPT var_age NUMBER PROMPT 'What is yor age';
DECLARE
age NUMBER := &var_age;
BEGIN
IF age < 18 THEN
RAISE_APPLICATION_ERROR (-20008, 'you should be 18 or above for the DRINK!');
END IF;
DBMS_OUTPUT.PUT_LINE ('Sure, What would you like to have?');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/
Create a procedure with in and out parameters
DECLARE
a number;
b number;
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
BEGIN
a:= 23;
b:= 45;
findMin(a, b, c);
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/
Example 2 :
DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
END;
BEGIN
a:= 23;
squareNum(a);
dbms_output.put_line(' Square of (23): ' || a);
END;
/
Prepare the named PL/SQL block for Dynamic SQL Execution.
CREATE OR REPLACE FUNCTION circle_area (radius NUMBER)
RETURN NUMBER IS
pi CONSTANT NUMBER(7,2) := 3.141;
area NUMBER(7,2);
BEGIN
area := pi * ( radius * radius );
RETURN area;
END;
