Welcome to Oracle PLSQL Sessions [Please watch the YouTube session to understand the below notes]
Oracle PLSQL – Introduction
What is PLSQL?
In Oracle database management, PL/SQL is a procedural language extension to Structured Query Language (SQL). The purpose of PL/SQL is to combine database language and procedural programming language. The basic unit in PL/SQL is called a block and is made up of three parts: a declarative part, an executable part and an exception-building part.
Two blocks in PLSQL
1. Anonymous Block
2. Named Block  — Stored as an object in DB
 PROCEDURE
 FUNCTION
 PACKAGE
 TRIGGER
1. Anonymous Block
===================
DECLARE
  — Variable declaration        — Optional
Begin
 — Executable statements        — Mandatory
 Exception
 — Exception handling code       — Optional
end;
/
=========================================================
:= assignment operator
— Anonymous Block – very basic block
Begin
DBMS_OUTPUT.PUT_LINE(‘Welcome To Oracle PLSQL’);
END;
— PLSQL Anonymous Block to Add two numbers
declare
N1 number:=5;
N2 number:=10;
RESULT number;
begin
N1:=20;
N2:=15;
RESULT:=N1+N2;
DBMS_OUTPUT.PUT_LINE(RESULT);
END;
— — PLSQL Anonymous Block to Add two numbers with decimal values
declare
number1 number(10,2):=20.5;
number2 number(10,2):=10.5;
result number(10,2);
begin
result := (number1+number2);
dbms_output.put_line(‘The addition of ‘||number1||’ and ‘||number2||’ are: ‘||result);
end;
— how to get user input in anonymous block
declare
number1 number(10,2):=&number1;
number2 number(10,2):=&number2;
result number(10,2);
begin
result := (number1+number2);
dbms_output.put_line(‘The addition of ‘||number1||’ and ‘||number2||’ are: ‘||result);
end;
— How to re-assin a different value for a variable
declare
number1 number(10,2):=50;
number2 number(10,2):=100;
result number(10,2);
begin
number1:=20;
number2:=30;
result := (number1+number2);
dbms_output.put_line(‘The addition of ‘||number1||’ and ‘||number2||’ are: ‘||result);
end;
— Constant variable declaration
declare
number1 constant number(10,2):=10.5;
number2 number(10,2):=20.5;
result number(10,2);
begin
number1:=20;
number2:=30;
result := (number1+number2);
dbms_output.put_line(‘The addition of ‘||number1||’ and ‘||number2||’ are: ‘||result);
end;
— Not null variable declaration
declare
number1 constant number(10,2):=0;
number2 number(10,2) not null:=0;
result number(10,2);
begin
–number1:=20;
number2:=null;
result := (number1+number2);
dbms_output.put_line(‘The addition of ‘||number1||’ and ‘||number2||’ are: ‘||result);
end;
==================================================================================
— select operation in PLSQL
declare
v_ph_no varchar2(100);
v_job_id varchar2(100);
begin
select phone_number,job_id into v_ph_no,v_job_id from employee where employee_id=102;
dbms_output.put_line(‘The phone number of the employee is ‘||v_ph_no|| ‘ and job id is ‘||v_job_id);
end;
declare
v_salary number;
v_phone_number varchar2(30);
begin
select salary,phone_number into v_salary,v_phone_number from employees where employee_id=&emp_id;
dbms_output.put_line(‘The salary of the employee is: ‘||v_salary||’ Phone ‘||v_phone_number);
end;
==================================================================================
Write a PLSQL anonymous block that shows the usage of the WHILE loop to calculate the average of user entered numbers and entry of more numbers are stopped by entering number 0?
DECLARE
n NUMBER;
avg NUMBER :=0 ;
sum NUMBER :=0 ;
count NUMBER :=0 ;
BEGIN
n := &enter_a_number;
WHILE(n<>0)
LOOP
count := count+1;
sum := sum+n;
n := &enter_a_number;
END LOOP;
avg := sum/count;
DBMS_OUTPUT.PUT_LINE(‘the average is’||avg);
END;
