PL/SQL is structured into blocks and can use conditional statements, loops and branches to control program flow. Variables can be scoped so that they are only visible within the block where they are defined.

PL/SQL is a database-orientated programming language that extends Oracle SQL with procedural capabilities. We will review in this lab the fundamental features of the language and learn how to integrate it with SQL to help solving database problems.

PL/SQL programs are organized in functions, procedures and packages. There is a limited support for object-oriented programming. PL/SQL is based on the ADA programming language.

PL/SQL blocks come in three types,

1. Anonymous Procedure: An anonymous procedure is an unnamed procedure, it can't be called. It is placed where it is to be run, normally attached to a database trigger or application event.
2. Named Procedure: A named procedure may be called, it may accept inbound parameters but won't explicitly return any value.
3. Named Function: A named function may also be called, it may accept inbound parameters and will always return a value.


PL/SQL block has three parts.

1) Declaration: Necessary variables are declared in this section. (Optional)

2) Begin: This section contain executable statements of SQL and PL/SQL

3) Exception: Any error occurred while executing the statements in begin part can be handled in this part.

Variables and Constants: Variables are used to store query results. Forward references are not allowed. Hence you must first declare the variable and then use it.


Variables can have any SQL datatype, such as CHAR, DATE, NUMBER etc or any PL/SQL datatype like BOOLEAN, BINARY_INTEGER etc.

Declaration using attributes - %type, % rowtype
The constants, variables, and columns and tables of database are associated with certain attributes. These attributes are used to simplify variable and constant declaration.


%Type Attribute
This attribute provides the datatype of variable, constant or a column. This is particularly useful when declaring a variable that refers to a column in a database
Variables and Constant using the attribute are treated like declarations that state the datatype.

The advantage of declaring a variable with an attribute are
a. The exact datatype of the variable need not be known
b. If the definition of column/table changes then the datatype of referenced variable changes at run time.

A variable can also be declared based on other variable
A emp.empno%type
D A%type
( note that A was declared as a variable in the first line and the next line a new variable D is declared on the old variable)


%ROWTYPE Attribute
The %ROWTYPE attribute is useful if a record variable having the same structure as a row in a table or view. The row is represented as a record whose fields have the same names and data types as the columns in the table or view.

To declare a record that can store an entire row from the emp table, the above attribute can be used as follows.
E emp%rowtype
Here the column values are stored in individual fields of the record. A specific field can be referenced with the following syntax
E.empno

Consolidated Declaration Examples

A number(5,2) - ----- variable whose values change in the program
P constant number(3,2):=0.15 ---------- constant whose values will not change
E emp.empno%type --------------- variable based on a row of emp table
Z emp%rowtype ---------------- variable based on a row of table



MATH OPERATIONS WITH PL/SQL

In this session, we will test the functioning of PL/SQL programming using simple mathematical operations like addition, multiplication, division etc.


SQL> SET SERVEROUTPUT ON

1. A Simple program to add two numbers.


1 DECLARE
2 A NUMBER(5):=10;
3 B NUMBER(5) :=15;
4 C NUMBER(5);
5 BEGIN
6 C:=A+B;
7 DBMS_OUTPUT.PUT_LINE(C);
8* END;
25
PL/SQL procedure successfully completed.

2. A Simple program to add two numbers by prompting for numbers from user
SQL>ED p1
DECLARE
A NUMBER(5):=&First_Number;
B NUMBER(5):=&Second_Number;
C NUMBER(5);
BEGIN
C:=A+B;
DBMS_OUTPUT.PUT_LINE(C);
END;
/
SQL> @p1
Enter value for first_number: 10
Enter value for second_number: 20
30

PL/SQL procedure successfully completed.

3. A similar program to multiply two numbers (p3)
SQL>p3
DECLARE
A NUMBER(5):=10;
B NUMBER(5):=15;
C NUMBER(5);
BEGIN
C:=A*B;
DBMS_OUTPUT.PUT_LINE(C);
END;
SQL> @p3
150
PL/SQL procedure successfully completed./



4. A similar program to divide two numbers (p4)

Sql>ed p4
DECLARE
A NUMBER(5):=&First_Number;
B NUMBER(5):=&Second_Number;
C NUMBER(5);
BEGIN
C:=A/B;
DBMS_OUTPUT.PUT_LINE(C);
END;
/
SQL> @p4
Enter value for first_number: 50
old 2: A NUMBER(5):=&First_Number;
new 2: A NUMBER(5):=50;
Enter value for second_number: 5
old 3: B NUMBER(5):=&Second_Number;
new 3: B NUMBER(5):=5;
10

PL/SQL procedure successfully completed.

Another way of prompting for values.
5. Multiply/Divide two numbers by prompting for values from the user
1 DECLARE
2 A NUMBER;
3 B NUMBER;
4 C NUMBER;
5 BEGIN
6 A:=&A;
7 B:=&SECOND_NUMBER;
8 C:=A*B;
9 DBMS_OUTPUT.PUT_LINE(C);
10* END;
SQL> /
Enter value for a: 20
old 6: A:=&A;
new 6: A:=20;
Enter value for second_number: 2
old 7: B:=&SECOND_NUMBER;
new 7: B:=2;

40
PL/SQL procedure successfully completed.








6. Adding/Subtracting two numbers

1 DECLARE
2 A NUMBER;
3 B NUMBER;
4 C NUMBER;
5 BEGIN
6 A:=&A;
7 B:=&SECOND_NUMBER;
8 C:=A+B;
9 DBMS_OUTPUT.PUT_LINE(C);
10* END;
SQL> /
Enter value for a: 10
old 6: A:=&A;
new 6: A:=10;
Enter value for second_number: 20
old 7: B:=&SECOND_NUMBER;
new 7: B:=20;
30

PL/SQL procedure successfully completed.


Review Questions :
1. Add two numbers and display the result as “The sum of firstnumber and secondnumber is ____
2. Multiply two numbers by initialising with values 5 and 3.(Assinging values to variables at coding time)
3. Writa a program to multiply two numbers by prompting for values from the user. Then the result
should be display as "The multiplication of 5 and 10 is 50" (if 5 and 10 were supplied by the user).




Review Questions on MATH OPERATIONS WITH PL/SQL

1. Add two numbers and display the result as “The sum of firstnumber and secondnumber is ____
2. Multiply two numbers by initialising with values 5 and 3.(Assinging values to variables at coding time)
3. Writa a program to multiply two numbers by prompting for values from the user. Then the result
should be display as "The multiplication of 5 and 10 is 50" (if 5 and 10 were supplied by the user).


Answers for review questions
1. Add two numbers and display the result as “The sum of firstnumber and secondnumber is ____
1 DECLARE
2 A NUMBER(5):=&First_Number;
3 B NUMBER(5):=&Second_Number;
4 C NUMBER(5);
5 BEGIN
6 C:=A+B;
7 DBMS_OUTPUT.PUT_LINE('The sum of firstnumber and secondnumber is 'C);
8* END;
SQL> /
Enter value for first_number: 10
old 2: A NUMBER(5):=&First_Number;
new 2: A NUMBER(5):=10;
Enter value for second_number: 20
old 3: B NUMBER(5):=&Second_Number;
new 3: B NUMBER(5):=20;
The sum of firstnumber and secondnumber is 30
PL/SQL procedure successfully completed.

2. Multiply two numbers by initialising with values 5 and 3.(Assinging values to variables at coding time)
1 DECLARE
2 A NUMBER(5):=5;
3 B NUMBER(5) :=3;
4 C NUMBER(5);
5 BEGIN
6 C:=A*B;
7 DBMS_OUTPUT.PUT_LINE(C);
8* END;
SQL> /
15
PL/SQL procedure successfully completed.

3. Write a program to multiply two numbers by prompting for values from the user. Then the result should be display as "The multiplication of 5 and 10 is 50" (if 5 and 10 were supplied by the user).

1 DECLARE
2 A NUMBER;
3 B NUMBER;
4 C NUMBER;
5 BEGIN
6 A:=&A;
7 B:=&SECOND_NUMBER;
8 C:=A*B;
9 DBMS_OUTPUT.PUT_LINE('The multiplication of 'A' and 'B
10 ' is ' C);
11* END;
SQL> /
Enter value for a: 10
old 6: A:=&A;
new 6: A:=10;
Enter value for second_number: 20
old 7: B:=&SECOND_NUMBER;
new 7: B:=20;
The multiplication of 10 and 20 is 200
PL/SQL procedure successfully completed.


2. Working with Simple PL/SQL blocks on database tables


SQL> SET SERVEROUTPUT ON

1. To display the job and salary of the Employee name SMITH from the EMP table.
SQL> DECLARE
2 VSAL NUMBER;
3 VJOB VARCHAR2(10);
4 BEGIN
5 SELECT JOB, SAL INTO VJOB, VSAL FROM EMP WHERE ENAME ='SMITH';
6 DBMS_OUTPUT.PUT_LINE(VSAL ' 'VJOB);
7 END;
8 /
800 CLERK

PL/SQL procedure successfully completed.

2. Accepting values from the user in a PL/SQL block
SQL> ED T21
DECLARE
VDNAME VARCHAR2(10);
VLOC VARCHAR2(10);
BEGIN
SELECT DNAME, LOC INTO VDNAME, VLOC FROM DEPT WHERE DEPTNO=&DNO;
DBMS_OUTPUT.PUT_LINE(VDNAME' 'VLOC);
END;
SQL> @T21
8 /
Enter value for dno: 20

RESEARCH DALLAS

PL/SQL procedure successfully completed.

3. Display the name, salary and job of the employee MARTIN of the emp table in the format MARTIN is working as SALESMAN and earning a salary of 1250
using a PL/SQL block
DECLARE
VNAME VARCHAR2(20);
VSAL NUMBER;
VJOB VARCHAR2(10);
BEGIN
SELECT ENAME,JOB, SAL INTO VNAME,VJOB, VSAL FROM EMP WHERE ENAME ='MARTIN';
DBMS_OUTPUT.PUT_LINE(VNAME ' is working as ' VJOB ' and earning a salary of 'VSAL);
END;
MARTIN is working as SALESMAN and earning a salary of 1250
PL/SQL procedure successfully completed.


4. With the question 3, prompt for the ename and display the job and salary accordingly
DECLARE
VNAME VARCHAR2(20);
VSAL NUMBER;
VJOB VARCHAR2(10);
BEGIN
SELECT ENAME,JOB, SAL INTO VNAME,VJOB, VSAL FROM EMP WHERE ENAME ='&VNAME';
DBMS_OUTPUT.PUT_LINE(VNAME ' is working as ' VJOB ' and earning a salary of 'VSAL);
END;
Enter value for vname: BLAKE
old 6: SELECT ENAME,JOB, SAL INTO VNAME,VJOB, VSAL FROM EMP WHERE ENAME ='&VNAME';
new 6: SELECT ENAME,JOB, SAL INTO VNAME,VJOB, VSAL FROM EMP WHERE ENAME ='BLAKE';
BLAKE is working as MANAGER and earning a salary of 2850

PL/SQL procedure successfully completed.

3.WORKING WITH %TYPE AND %ROWTYPE

Declaration using attributes - %type, % rowtype
The constants, variables, and columns and tables of database are associated with certain attributes. These attributes are used to simplify variable and constant declaration.


%Type Attribute
This attribute provides the datatype of variable, constant or a column. This is particularly useful when declaring a variable that refers to a column (single column only) in a database
%ROWTYPE Attribute
The %ROWTYPE attribute is useful if a record variable having the same structure as a row in a table or view. The row is represented as a record whose fields have the same names and data types as the columns in the table or view.

1. To display the salary and commission of an employee using %type
DECLARE
VSAL EMP.SAL%TYPE;
VCOMM EMP.COMM%TYPE;
BEGIN
SELECT SAL, COMM INTO VSAL,VCOMM FROM EMP WHERE EMPNO=&NO;
DBMS_OUTPUT.PUT_LINE('THE TOTAL SALARY IS ' TO_CHAR(VSAL+NVL(vCOMM,0))); END;
Enter value for no: 7654
old 5: SELECT SAL, COMM INTO VSAL,VCOMM FROM EMP WHERE EMPNO=&NO;
new 5: SELECT SAL, COMM INTO VSAL,VCOMM FROM EMP WHERE EMPNO=7654;
THE TOTAL SALARY IS 2650

PL/SQL procedure successfully completed.


2. To know the length of ename when empno is given as input by the user.
DECLARE
N NUMBER;
NEMPNO EMP.EMPNO%TYPE:=&EMPNO;
BEGIN
SELECT LENGTH(ENAME) INTO N FROM EMP WHERE EMPNO=NEMPNO;
DBMS_OUTPUT.PUT_LINE('The length of ename is ' N);
END;
Enter value for empno: 7369
old 3: NEMPNO EMP.EMPNO%TYPE:=&EMPNO;
new 3: NEMPNO EMP.EMPNO%TYPE:=7369;
The length of ename is 5

PL/SQL procedure successfully completed.



3. To display the deptno and dname as ‘ accounting is deptno 10’ using %rowtype;

1 DECLARE
2 DREC DEPT%ROWTYPE;
3 BEGIN
4 SELECT * INTO DREC FROM DEPT WHERE DEPTNO=&DNO;
5 DBMS_OUTPUT.PUT_LINE(DREC.DEPTNO' IS FOR DEPARTMENT 'DREC.DNAME);
6* END;
7 /
Enter value for dno: 20
old 4: SELECT * INTO DREC FROM DEPT WHERE DEPTNO=&DNO;
new 4: SELECT * INTO DREC FROM DEPT WHERE DEPTNO=20;
20 IS FOR DEPARTMENT SALES

PL/SQL procedure successfully completed.

4. Write a PL/SQL code using %Type and %RowType to display the string as
'The Employee Martin is Working in Deptno 30 and earning a salary of 1250'
here the ename, his deptno and salary is fetched at run time from the emp table
based on empno given by the user
DECLARE
VEMPNO EMP.EMPNO%TYPE:=&EMPNO;
EMP_REC EMP%ROWTYPE;
BEGIN
SELECT * INTO EMP_REC FROM EMP
WHERE EMPNO=VEMPNO;
DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE 'EMP_REC.ENAME' IS WORKING IN DEPTNO '
EMP_REC.DEPTNO' AND EARNING A SALARY OF 'EMP_REC.SAL);
END;
SQL> /
Enter value for empno: 7654
old 2: VEMPNO EMP.EMPNO%TYPE:=&EMPNO;
new 2: VEMPNO EMP.EMPNO%TYPE:=7654;
THE EMPLOYEE MARTIN IS WORKING IN DEPTNO 30 AND EARNING A SALARY OF 1250

PL/SQL procedure successfully completed.

5. How do I insert values into a table using a PL/SQL block.
DECLARE
EMP_REC EMP%ROWTYPE;
BEGIN
INSERT INTO EMP VALUES(&EMPN0,'&ENAME','&JOB','&MGR','&HIREDATE', &SAL, &COMM,&DEPTNO);
DBMS_OUTPUT.PUT_LINE('ONE RECORD UPDATED');
END;

SQL> @ T22
8 /
Enter value for empn0: 8989
Enter value for ename: MUKHTAR
Enter value for job: MANAGER
Enter value for mgr: 7782
Enter value for hiredate: 12-SEP-86
Enter value for sal: 3500
Enter value for comm: 350
Enter value for deptno: 20
old 4: INSERT INTO EMP VALUES(&EMPN0,'&ENAME','&JOB','&MGR','&HIREDATE',&SAL,'&COMM',&DEPTNO);
new 4: INSERT INTO EMP VALUES(8989,'MUKHTAR','MANAGER','7782','12-SEP-86',3500,'350',20);
ONE RECORD UPDATED
PL/SQL procedure successfully completed.

6. What you see that I record. !!
Write a PL/SQL block that will make a copy of the empno, ename and salary of all the employee records that are being seen by running this PL/SQL block. To verify that, it is also being shown
in the next line.
DECLARE
EMP_REC EMP%ROWTYPE;
EMCHK CHKEMP%ROWTYPE;
BEGIN
SELECT * INTO EMP_REC FROM EMP
WHERE EMPNO=&EMPNO;
INSERT INTO CHKEMP(EMPNO, ENAME, SAL) VALUES(EMP_REC.EMPNO,EMP_REC.ENAME,EMP_REC.SAL);
DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE 'EMP_REC.ENAME' IS WORKING IN DEPTNO '
EMP_REC.DEPTNO' AND EARNING A SALARY OF 'EMP_REC.SAL);
SELECT * INTO EMCHK FROM CHKEMP WHERE EMPNO=EMP_REC.EMPNO;
DBMS_OUTPUT.PUT_LINE(EMCHK.EMPNO ' 'EMCHK.ENAME);
END;
/
SQL> SELECT * FROM CHKEMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
7654 MARTIN 1250



Review Questions.
1. Write the PL/SQL code that will display the Ename based on the empno in the following string format . 'The Ename SMITH has 5 characters in his name'

2.With reference to Qno4,Is it possible to write the code by using only the %RowType.

Review Questions.(Solution Set)
2.With reference to Qno4,Is it possible to write the code by using only the %RowType.
DECLARE
EMP_REC EMP%ROWTYPE;
BEGIN
SELECT * INTO EMP_REC FROM EMP
WHERE EMPNO=&EMPNO;
DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE 'EMP_REC.ENAME' IS WORKING IN DEPTNO '
EMP_REC.DEPTNO' AND EARNING A SALARY OF 'EMP_REC.SAL);
END;
/
THE EMPLOYEE MARTIN IS WORKING IN DEPTNO 30 AND EARNING A SALARY OF 1250
PL/SQL procedure successfully completed.