PLSQL Class Notes

THE PL/SQL EXECUTION STRUCTURE

DECLARE
VARIABLE_NAME DATATYPE:=10;
BEGIN

END;

==========================

FOR INITLIZATION WE USE [ := ]
WHERE AS = IS A COMPARATOR

==========================


THE IF ELSE CONDITION BLOCK

IF CONDITION THEN
    STATEMENT;
ELSIF CONFITION2 THEN
    STATEMENT2;
ELSE

ENDIF;


========================


LOOPING STRUCTURE [BASIC]

DECLARE
I NUMBER:=1;
BEGIN
    LOOP
    DBMS_OUTPUT.PUT_LINE(I);
    EXIT WHEN I>=10;
    END LOOP;
END;


=========================


FOR LOOP STRUCTURE

DECLARE
I NUMBER;
BEGIN
FOR I IN 1..10
LOOP
    DBMS_OUTPUT.PUT_LINE(I);
END LOOP;
END;

=========================


WHILE LOOP

DECLARE
I NUMBER:=1;
BEGIN
WHILE(I<=10)
LOOP
    DBMS_OUTOPT.PUT_LINE(I);
I:=I+1;
END LOOP;
END;

===========================


TO TAKE INPUT FROM USER

&SOMECHARACER WHERE &(AMPERSAND) IS A REPLACEMENT VARIABLE EXAMPLE &a1.

NOTE - THERE IS AN ERROR WHILE WE USE THIS COMMAND IN COMMAND LINE WE GET
ERROR WE DONT GET OUTPUT IN CONSOLE TO FIX THIS WE USE
SET SEREVROUTPUT ON


=========================


WHEN THE OUTPUT EXPECTS STRING USE SINGLE QUOTES ('') NOT DOUBLE QUOTES ("")


=========================


CREATE A STORED PROCEDURE


CREATE OR REPLACE PROCEDURE ADD1(A NUMBER, B NUMBER) IS C NUMBER;
BEGIN
C:=A+B;
DBMS_OUTPUT.PUT_LINE(C);
END;

BASIC SYNTAX

CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(PARAMERTER1 DATATYPE, PARAMETER2 DATATYPE) IS VARABLE_NAME DATATYPE;
BEGIN
// LOGICAL CODE
END;


===========================


TO RUN STORED PROCEDURE

EXEC PROCEDURE_NAME;

============================


TO CREATE A FUCTION


CREATE OR REPLACE FUNCTION FUNCTION_NAME(PARAMETER1 NUMBER, PARAMETER2 DATATYPE) RETURN DATATYPE IS VARIABLE_NAME DATATYPE;
BEGIN
C:=A+B
RETURN(X);
END;

TO CALL THE ABOVE FUNCTION

DECLARE
D NUMBER;
BEGIN
D:= FUNCTION_NAME(PARAMETERS, PARAMATERS);
DBMS_OUTPUT.PUT_LINE(D);
END;

=============================
TEST
CREATE A PROCEDURE AND FUCNTION TO FIND MAX OF THREE OF NUMBER

CREATE A FUNTION TO FIND FACTORAIAL OF A NUMBER;


==========================

CREATE OR REPLACE FUNCTION MAX_NUMBER(A NUMBER,B NUMBER,C NUMBER) RETURN NUMBER IS D NUMBER;
BEGIN
C:=1;
IF A>B AND A>C
DBMS_OUTPUT.PUT_LINE('A IS MAX');
IF B>A AND B>C
DBMS_OUTPUT.PUT_LINE('B IS MAX');
ELSE
DBMS_OUTPUT.PUT_LINE('C IS MAX');
END IF;
RETURN(C);
END;

=============================