Go Back

PLSQL 2

PL/SQL -> PLSQLBasics

INTRODUCTION TO PL/SQL:

PLSQL Basics 1:


Till now we used sql to query database for data. SQL is a fouth generation language.
This means that the language describes what should be done, but not how to do it.
FOR EXAMPLE : 	
DELETE FROM EMP WHERE JOB='CLERK';
Here we don't know how the database actually determines which employees are clerks. 
Probably the server will loop through all the records in some orderto determine 
the proper entries to DELETE. But the details are hiden from us.
Third generation languages such as C OR COBOL, are more procedural in nature. 
A program in a third generation language implements step by step algorithm to 
slove the problem.
For example we accomplish above delete operation like this in this 3 gl.
LOOP over each student record
If this record has JOB = CLERK THEN
DELETE THIS RECORD;
END IF;
END LOOP;
OBJECT ORIENTED LANGUAGES SUCH AS C++ OR JAVA are also third generation languages 
do the same as 'C'. FOURTH GENERATION LANGUAGES such as SQL are fairly simple compared 
to the 3rd genaration languages and have fewer commands. How ever in some cases 
procedural constructs such as VARIABLES, CONTROL STRUCTURES such as IF THEN ELSE 
STATEMENTS AND LOOPS AND PROCEDURES AND FUCTIONS.,etc are more useful for desired program.
This is where pl/sql comes in to the picture. pl/sql is combination of power and the 
flexibility of sql with the procedural constructs of 3rd generation languages.

The basic unit in any PL/SQL program is a block.

There are two different kinds of blocks:
1.	ANONYMOUS BLOCK
2.	NAMED BLOCK.

Anonymous blocks are generally constructed once and excecuted only once. 
This type of block is often issued from a client program to call a 
subprogram stored in the database. named blocks are blocks that have name 
associated with them. 
Named blocks further classified in to 3 types
1.	LABELED BLOCKS.
2.	SUBPROGRAMS(STOREDPROCEDURE AND FUNCTION).
3.	TRIGGERS.

Labeled blocks are anonymous blocks are generally constructed once and 
excecuted only once.are used same as anonymous blocks.

Subprograms consists of PROCEDURES AND FUNCTIONS. 
They can be stored in the DATABASE as standalone objects.
They generally don't change once constructed.
They can executed many times.
SUBPROGRAMS EXECUTED explicitly via call to PROCEDURE OR FUNCTION.

TRIGGERS CONSIST OF PL/SQL BLOCK.
They are generally don't change once they constructed and executed many times.
TRIGGERS executed implictly whenever triggering event occurs.
The triggering event may be dml statement or ddl statement(CREATE OR DROP) 
OR DATABASE EVENT such as START UP OR SHUTDOWN.

BASIC PL/SQL BLOCK:

DECLARE
   DECLARATIVE SECTION is here.
   DECLARE ALL VARIABLES ETC.
BEGIN
   EXECUTABLE SECTION is here.
   ALL SQL STATEMENTS AND PROCEDURAL CONSTRUCTS.
EXCEPTION
   EXCEPTION SECTION here. Errors handling statements goes here.
END;

DECLARE AND EXCEPTION IS OPTIONAL.
That is every PL/SQL block should have BEGIN AND END.

DECLARING PL/SQL VARIABLES:

DECLARE
   v_hiredate		DATE;
   v_deptno		   NUMBER(10) NOT NULL := 10;
   v_loc			   VARCHAR2(10) := 'NEW YORK';
   v_comm		   CONSTANT	NUMBER :=1400;
   v_sal			   NUMBER(10) DEFAULT 1000;

ASSIGNING VALUES TO VARIABLES:
   v_deptno		NUMBER(10) NOT NULL := 10;
   v_loc			VARCHAR2(10) := 'NEW YORK';
   BASE SCALAR DATATYPES:
   VARCHAR2(MAX_LEN)
   NUMBER(P,S);
   DATE
   CHAR(MAX_LEN)
   LONG
   LONG RAW
   BOOLEAN
   BINARY_INTEGER
   PLS_INTEGER


DECLARE
   v_job			VARCHAR2(10);
   v_count		NUMBER(2) := 0;
   v_valid		BOOLEAN NOT NULL := TRUE;

DECLARING VARIABLES WITH %TYPE ATTRIBUTES:
   v_ename 	VARCHAR2(10);
   v_job		EMP.JOB%TYPE;

The advantage of declaring this type is for us no need worry about the lengh of the variables. 
v_job will be declared as same size of emp table job column.

Only the values TRUE, FALSE, NULL can be assigned to the BOOLEAN variable.

DBMS_OUTPUT.PUT_LINE is used to print output. 

IN SQL*PLUS we have SET SERVEROUTPUT ON.

THE BELOW PL/SQL BLOCK IS ANONYMOUS BLOCK:
SQL> cl scr 
SQL> SET VERIFY OFF 
SQL> cl scr 
SQL>  DECLARE
         V_DEPTNO  NUMBER(10);
         V_LOC     VARCHAR2(10);
      BEGIN
         SELECT DEPTNO,LOC
           INTO V_DEPTNO,V_LOC
           FROM DEPT
          WHERE DNAME='SALES';
      END;
      /
PL/SQL procedure successfully completed.

SQL>  DECLARE
         V_DEPTNO  NUMBER(10);
         V_LOC     VARCHAR2(10);
      BEGIN
         SELECT DEPTNO,LOC
           INTO V_DEPTNO,V_LOC
           FROM DEPT
          WHERE DNAME='SALES';
         DBMS_OUTPUT.PUT_LINE(V_DEPTNO || '       ' ||  V_LOC);
      END;
SQL> /
PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> /
30       CHICAGO                                                                                    
PL/SQL procedure successfully completed.

THE BELOW PL/SQL BLOCK IS LABELLED BLOCK:

<>
SQL>  DECLARE
         V_DEPTNO  DEPT.DEPTNO%TYPE;
         V_LOC     DEPT.LOC%TYPE;
      BEGIN
         SELECT DEPTNO,LOC
           INTO V_DEPTNO,V_LOC
           FROM DEPT
          WHERE DNAME='SALES';
         DBMS_OUTPUT.PUT_LINE(V_DEPTNO || '       ' ||  V_LOC);
      END;<>

SQL> /
30       CHICAGO                                                                                    
PL/SQL procedure successfully completed.


SQL>  DECLARE 
         v_deptno  emp.deptno%TYPE := 10; 
         v_sum_sal emp.sal%TYPE; 
      BEGIN 
         SELECT   SUM(sal) 
           INTO   v_sum_sal 
           FROM   emp 
          WHERE   deptno = v_deptno; 

          dbms_output.Put_line(v_sum_sal); 
      END; 
SQL> /

8750                                                                                                

PL/SQL procedure successfully completed.


INSERTING DATA:

BEGIN 
    INSERT INTO dept 
    VALUES     (50, 
                'COMPUTERS', 
                'MIAMI'); 
END;

UPDATING DATA:

DECLARE 
    v_sal_increase emp.sal%TYPE := 2000; 
BEGIN 
    UPDATE emp 
    SET    sal = sal + v_sal_increase 
    WHERE  job = 'ANALYST'; 
END; 

DELETING DATA:

DECLARE 
   v_deptno emp.deptno%TYPE := 10; 
BEGIN 
   DELETE FROM emp 
   WHERE  deptno = v_deptno; 
END; 
CONTROLLING PL/SQL FLOW EXECUTION: CONDITIONAL
IF STATEMENTS:
  IF BOOLEAN_EXPRESSION1 THEN
    SEQUENCE_STATEMENTS;
  ELSIF BOOLEAN_EXPRESSION1 THEN
    SEQUENCE_STATEMENTS;
  ELSE
    SEQUENCE_STATEMENTS;
  END IF;
  WHERE BOOLEAN_EXPRESSION
IS
  ANY EXPRESSION THAT EVALUATES TO A BOOLEAN VALUE. THE
ELSIF AND
ELSE
  CLAUSES ARE OPTIONAL AND THERE CAN BE
AS
  MANY
ELSIF CLAUSES ARE DESIRED. EXAMPLE:
   DECLARE
     V_JOB EMP.JOB%TYPE;
   BEGIN
      SELECT JOB INTO V_JOB FROM EMP 5 WHERE EMPNO = & EMPLOYEE_NUMBER;
      IF V_JOB = 'CLERK' THEN
         DBMS_OUTPUT.PUT_LINE(V_JOB);
         DBMS_OUTPUT.PUT_LINE('HIS SALARY INCREASES 10%');
      ELSIF V_JOB = 'MANAGER' THEN
         DBMS_OUTPUT.PUT_LINE(V_JOB);
         DBMS_OUTPUT.PUT_LINE('HIS SALARY INCREASES 30%');
      ELSIF V_JOB = 'SALESMAN' THEN
         DBMS_OUTPUT.PUT_LINE(V_JOB);
         DBMS_OUTPUT.PUT_LINE ('HIS SALARY INCREASES 20%');
      ELSE
         DBMS_OUTPUT.PUT_LINE (V_JOB);
         DBMS_OUTPUT.PUT_LINE ('HIS SALARY INCREASES 0%');
      END IF;
   END;
   /

Enter value for employee_number: 7369
CLERK                                                                           
HIS SALARY INCREASES 10%                                                        
PL/SQL procedure successfully completed.

CASE STATEMENT: 
   DECLARE
      V_JOB EMP.JOB%TYPE;
   BEGIN
      SELECT JOB 
        INTO V_JOB 
        FROM EMP 
       WHERE EMPNO = & EMPLOYEE_NUMBER;
      CASE V_JOB 
      WHEN 'CLERK' THEN
         DBMS_OUTPUT.PUT_LINE(V_JOB);
         DBMS_OUTPUT.PUT_LINE('HIS SALARY INCREASES 10%');
      WHEN 'MANAGER' THEN
         DBMS_OUTPUT.PUT_LINE(V_JOB);
         DBMS_OUTPUT.PUT_LINE('HIS SALARY INCREASES 30%');
        
      WHEN 'SALESMAN' THEN
         DBMS_OUTPUT.PUT_LINE(V_JOB);
         DBMS_OUTPUT.PUT_LINE('HIS SALARY INCREASES 20%');
        
      ELSE
         DBMS_OUTPUT.PUT_LINE(V_JOB);
         DBMS_OUTPUT.PUT_LINE('HIS SALARY INCREASES 0%');
        
      END CASE;
   END;
   /

Enter value for employee_number: 7369
CLERK                                                                           
HIS SALARY INCREASES 10%                                                        
PL/SQL procedure successfully completed.

CASE STATEMENT WITH NO ELSE STATEMENT:
   DECLARE 
      V_JOB EMP.JOB%TYPE;
   BEGIN
      SELECT JOB 
        INTO V_JOB 
        FROM EMP 
       WHERE EMPNO = & EMPLOYEE_NUMBER;
      CASE V_JOB 
      WHEN 'CLERK' 
      THEN
         DBMS_OUTPUT.PUT_LINE
         (V_JOB);
         DBMS_OUTPUT.PUT_LINE('HIS SALARY INCREASES 10%');
      WHEN 'MANAGER' THEN
         DBMS_OUTPUT.PUT_LINE(V_JOB);
         DBMS_OUTPUT.PUT_LINE('HIS SALARY INCREASES 30%');
        
      WHEN 'SALESMAN' THEN
         DBMS_OUTPUT.PUT_LINE(V_JOB);
         DBMS_OUTPUT.PUT_LINE('HIS SALARY INCREASES 20%');
        --(HERE NO ELSE STATEMENT). 
      END CASE;
   END;
   /
   
Enter value for employee_number: 7788

DECLARE
*
ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement 
ORA-06512: at line 6 

SQL> /
Enter value for employee_number: 7369
CLERK                                                                           
HIS SALARY INCREASES 10%                                                        
PL/SQL procedure successfully completed.

CASE STATEMENT WITH LABEL:
DECLARE
   V_JOB  EMP.JOB%TYPE;
BEGIN
   SELECT JOB 
     INTO V_JOB 
     FROM EMP
    WHERE EMPNO = & EMPLOYEE_NUMBER;
   <>
   CASE V_JOB
   WHEN 'CLERK'  THEN
      DBMS_OUTPUT.PUT_LINE(V_JOB);
      DBMS_OUTPUT.PUT_LINE('HIS SALARY INCREASES 10%');
   WHEN 'MANAGER' THEN
      DBMS_OUTPUT.PUT_LINE(V_JOB);
      DBMS_OUTPUT.PUT_LINE('HIS SALARY INCREASES 30%');
   WHEN 'SALESMAN' THEN
      DBMS_OUTPUT.PUT_LINE(V_JOB);
      DBMS_OUTPUT.PUT_LINE('HIS SALARY INCREASES 20%');
   END CASE MYCASE;
END;
/

Enter value for employee_number: 7369
CLERK                                                                           
HIS SALARY INCREASES 10%                                                        
PL/SQL procedure successfully completed.

 CASESTATEMENT WITH NO TEST EXPRESSION: 

DECLARE 
    v_job emp.job%TYPE; 
BEGIN 
    SELECT job 
    INTO   v_job 
    FROM   emp 
    WHERE  empno = & employee_number; 

    CASE 
      WHEN v_job = 'CLERK' THEN 
        dbms_output.Put_line(v_job); 

        dbms_output.Put_line('HIS SALARY INCREASES 10%'); 
      WHEN v_job = 'MANAGER' THEN 
        dbms_output.Put_line(v_job); 

        dbms_output.Put_line('HIS SALARY INCREASES 30%'); 
      WHEN v_job = 'SALESMAN' THEN 
        dbms_output.Put_line(v_job); 

        dbms_output.Put_line('HIS SALARY INCREASES 20%'); 
      ELSE 
        dbms_output.Put_line(v_job); 

        dbms_output.Put_line('HIS SALARY INCREASES 0%'); 
    END CASE; 
END; 
/
Enter value for employee_number: 7369
CLERK                                                                           
HIS SALARY INCREASES 10%                                                        
PL/SQL procedure successfully completed.


LOGIC TABLES

AND:
AND 	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

OR
OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

NOT:
NOT	
TRUE	FALSE
FALSE	TRUE
NULL	NULL

ITERATIVECONTROL:

LOOP STATEMENTS

LOOPS REPEAT A STATEMENT OR SEQUENCE OF STATEMENTS MULTIPLE TIMES.
THERE ARE THREE LOOPS TYPES:
   1	BASIC LOOP
   2	FOR LOOP
   3	WHILE LOOP

 BASIC LOOP:

DECLARE 
    v_counter NUMBER(10) := 0; 
BEGIN 
    LOOP 
        dbms_output.Put_line(v_counter); 

        v_counter := v_counter + 1; 

        IF v_counter > 10 THEN 
          EXIT; 
        END IF; 
    END LOOP; 
END; 
/

0                                                                               
1                                                                               
2                                                                               
3                                                                               
4                                                                               
5                                                                               
6                                                                               
7                                                                               
8                                                                               
9                                                                               
10                                                                              

PL/SQL procedure successfully completed.

DECLARE 
    v_counter NUMBER(10) := 0; 
BEGIN 
    LOOP 
        dbms_output.Put_line(v_counter); 

        v_counter := v_counter + 1; 

        EXIT WHEN v_counter > 10; 
    END LOOP; 
END; 
/ 
0                                                                               
1                                                                               
2                                                                               
3                                                                               
4                                                                               
5                                                                               
6                                                                               
7                                                                               
8                                                                               
9                                                                               
10                                                                              

PL/SQL procedure successfully completed.

 FOR LOOP:
BEGIN 
    FOR i IN 1 .. 10 LOOP 
        dbms_output.Put_line(i); 
    END LOOP; 
END; 
/
1                                                                               
2                                                                               
3                                                                               
4                                                                               
5                                                                               
6                                                                               
7                                                                               
8                                                                               
9                                                                               
10                                                                              

PL/SQL procedure successfully completed.

BEGIN 
    FOR i IN REVERSE 1 .. 10 LOOP 
        dbms_output.Put_line(i); 
    END LOOP; 
END; 
10                                                                              
9                                                                               
8                                                                               
7                                                                               
6                                                                               
5                                                                               
4                                                                               
3                                                                               
2                                                                               
1                                                                               
PL/SQL procedure successfully completed.

WHILE LOOP

DECLARE 
    v_counter NUMBER(10) := 0; 
    v_num     NUMBER(10) := 10; 
BEGIN 
    WHILE v_counter < v_num LOOP 
        dbms_output.Put_line(v_counter); 

        v_counter := v_counter + 1; 
    END LOOP; 
END; 
/ 
0                                                                               
1                                                                               
2                                                                               
3                                                                               
4                                                                               
5                                                                               
6                                                                               
7                                                                               
8                                                                               
9                                                                               
PL/SQL procedure successfully completed.


Go Back

Go Back