Introduction to PL/SQL

Introduction to PL/SQL

-PL/SQL required for data processing
-It is a procedural language extension to the non-procedural SQL.

Blocks of PL/SQL code are passed to and processed by a PL/SQL engine.

PL/SQL Architecture


Benefits of PL/SQL

- PL/SQL is portable.
- You can declare variable.
- You can program with procedural language control structures.
- PL/SQL can handle errors.
- Improved Performance

Benefits of Subprogram

1. Easy Maintenance
2. Improved data security and integrity.
3. Improved performance.
4. Improved code clarity.

PL/SQL Block Structure

DECLARE (Optional)
Variables, cursor, user-defined exception
Begin (Mandatory)
- SQL statements
- PL/SQL statements
Exception (Optional)
Actions to perform when errors occur
End;

Declaring PL/SQL Variables

:= (assign operator)

DECLARE
v_hiredate date;
v_deptno number(2) := 10 ;

Naming Rules

DECLARE
memp_id number(4):= 1000 ;
begin
select empno into mempno from emp where empno=1000;
end ;

Base Scalar Data Types

- CHAR
- VARACHAR2
- LONG
- LONG RAW
- NUMBER
- BOOLEAN

Scalar Values Declaration

Declare
V_job Varchar(10) ;
V_orderdate date := sysdate + 7 ;
v_valid BOOLEAN NOT NULL := TRUE ;

Using %TYPE

- The database table and column
- The previously declared variable name
- Declaring Variables with the %TYPE Attribute
v_Name emp.ename%type ;
v_job emp.job%type ;

Using %ROWTYPE
The %ROWTYPE attribute provides a record type that represents a row in a table (or view). The record can store an entire row of data selected from the table or fetched by a cursor (declared later). In case, variables for the entire row of a table need to be declared, then instead of declaring them individually, the attribute %type is used

EMP_ROW_VAR EMP%ROWTYPE:
Here, the variable EMP_ROW_VAR will be a composite variable, consisting of the column names of the table as its members. To refer to a specifiec variable, sal SAL, the following syntax will be used:
EMP_ROW_VAR.sal := 2000 ;


Using BIND Variables

VARIABLE g_salary number ;
BEGIN
select sal into :g_salary from emp where empno=7902 ;
end ;

Print g_salary

For Printing the values :- DBMS_OUTPUT.PUT_LINE

set serveroutput on ;

DEFINE p_anual_sal = 60000 ;
Declare
v_sal
begin
v_sal = v_sal /12 ;

dbms_output.put_line('The monthly salary' || v_sal ) ;
end ;

Commenting Code /* */

declare
V_ename emp.empno%type ;
V_sal emp.sal%type;
v_conat varchar2(30;
begin
v_conat := V_ename || V_sal ;
end ;

Nested Block

Declare
x number;
begin
Declare
Y Number;
Begin
.........
End ;
.......
end ;

Some Sample Progamme using PL/SQL Block
declare
x number:=45 ;
y number:=56 ;
z number ;
begin
z:= x+y ;
dbms_output.Put('the value of z is '||x) ;
dbms_output.Put_line('the value of z is '||y) ;
dbms_output.Put_line('the value of z is '||z) ;
end;



Operators in PL/SQL

--Logical
--Arithmetic
--Concatenation

Same as SQL
------------------------

Select Statement in PL/SQL
---------------------------
- The Into clause

declare
mempno emp.empno%type ;
mename emp.ename%type ;
begin
select empno,ename into mempno,mename from emp where empno=7902;
dbms_output.Put_line('The sasasasasa');
end ;

Manipulating data Using PL/SQL

Make Changes to database tables by using tables.

- Insert
- delete
- Update
- Merge

Insert command using

begin
insert into dept1 (deptno,dname,loc) values (50,'EDP','America') ;
end;

Update command using
declare
V_sal_inc emp.sal%type := 200 ;
begin
update emp set sal = sal + v_sal_inc where deptno= 50 ;
end;

Delete command using

declare
V_deptno emp.deptno%type := 50 ;
begin
delete from emp where deptno= v_deptno ;
end;
-------------------------------------------------------------------------
declare
v_deptid dept1.deptno%type := 50 ;
begin
delete from dept1 where deptno=v_deptid ;
dbms_output.put_line (sql%rowcount || 'Row deleted') ;
end ;


Conditional and Iterative Control

The conditional control available with PL/SQL are:
IF-THEN –ELSE statement

The type of Loops available with PL/SQL
1) Basic Loop
2) While Loop
3) For Loop

IF STATEMENTS
Syntax:
IF then

elsif then

else

end if;

Example :

if v_name='KING' then
v_job := 'President' ;
elsif v_name='JONES' then
v_job := 'Manager' ;
else
v_job := 'Clerk' ;
end if ;

--------------------------------------
declare
mempno emp.empno%type:=&empno ;
msal emp.sal%type;
a varchar2(100) ;
begin
select sal into msal from emp where empno=mempno;
if msal > 100 and msal <= 2000 then a:='Salary is between 1000 and 2000'; elsif msal > 2000 and msal <= 3000 then a:='Salary is between 2001 and 3000'; else a:='Salary is greater than 3000' ; end if ; dbms_output.put_line(a) ; end ; Basic Loop The syntax is; LOOP
exit [loop-label] [WHEN Condition]
end loop ;

Example:
declare
i number:=1 ;
begin
loop
dbms_output.put_line(i) ;
i := i+1 ;
exit when i > 10 ;
end loop;
end ;

WHILE Loop

The syntax is;
WHILE [condition]LOOP

end loop ;

Example

declare
i number:= 1 ;
begin
while I <> IN .. LOOP

End loop

Example
declare
i number ;
begin
for i in 1..10 loop
dbms_output.put_line(i) ;
end loop;
end ;

Using CASE EXPRESSIONS

declare
v_grade char(1) := upper( '&V_grade') ;
v_appraisal varchar2(30) ;
begin
v_appraisal :=
CASE v_grade
WHEN 'A' then 'Execllent'
WHEN 'B' then 'Very Good'
WHEN 'C' then 'Good'
else 'No Such Grade'
end;
dbms_output.put_line ('Grade: ' ||v_grade|| ' Appraisal ' ||v_appraisal) ;
end;

-----Same program but we can write some other way

declare
v_grade char(1) := upper( '&V_grade') ;
v_appraisal varchar2(30) ;
begin
v_appraisal :=
CASE
WHEN v_grade ='A' then 'Execllent'
WHEN v_grade ='B' then 'Very Good'
WHEN v_grade = 'C' then 'Good'
else 'No Such Grade'
end;
dbms_output.put_line ('Grade: ' ||v_grade|| ' Appraisal ' ||v_appraisal) ;
end;

Using GOTO Statement

declare
x number;
y number;
z number;
begin
x:=&x;
y:=&y;
z:=x+y;
if z>=50 then
goto pqr;
else
dbms_output.put_line('Wellcome');
end if;
<>
dbms_output.put_line('fjbfbfbb');
end;