----------- 02/08/2023 -- PLSQL
--PLSQL stands fro Procedural language Extension to SQL
-- PLSQL is SQL + Prgramming/procedural constructs(variables,constants,data types,
conditional statements,
--looping, named subprograms)
-- it is procedural programming language so follows Top to Bottom approach
-- it provides a block structure for executable unit of code.
-- maintenence of code will be easier as it has fixed structure
PLSQL code is executed by PLSQL Engine
as plsql code is combination of SQL statement and PLSQL statement so plsql engine
willsend SQL statemnt
to SQL statement executor and retrive the results from it. and PLSQL statements
will be executed
by PLSQL statement executor in PLSQL engine.
--- variables
-- constants
-- data types
-- conditional statements
-- looping
-- collections
-- cursor
-- exception handling
-- anonymous block
--- named blocks -- procedure,function,package,trigger
PLSQL Advantages:
-- Modularised program development
-- Integrated with Tools like Forms,Reports
-- Portability
-- Exception Handling
--- PLSQL Block Structure
/*
Declare(optional
Declarations of Variables,Constants, User Defined Exceptions
Begin (mandatory)
-- SQL statements
-- PLSQL statements
Exception (optional)
- Actions to perform when any exceptions occur
End;(mandatory) -- it indictes end of the block
-- PLSQL Block Types:
-- Anonymous Block
-- it will not have name and it won't get stored in database so we can't reuse
it.
-- Named Block -- it will have name and it will get stored in database so we can
reuse/recall it.
-- procudure
-- function
*/
--- First PLSQL Program
SET SERVEROUTPUT Off;
Declare
v_emp_name varchar2(10);
Begin
DBMS_OUTPUT.put_line('The Employee Name is: '||v_emp_name);
SELECT first_name
INTO v_emp_name
FROM employees;
--where employee_id=100;
DBMS_OUTPUT.put_line('The Employee Name is: '||v_emp_name);
end;
--variable is used to store any value temporary
set serveroutput on; --to enable the output on screen of program execution
Declare
v_emp_name varchar2(10):='Ashlesha'; -- variable initialization
v_sal number(10); -- variable Declaration
--v_job varchar2(10);
Begin
DBMS_OUTPUT.put_line('The Employee Name is: '||v_emp_name);
DBMS_OUTPUT.put_line('The Employee Salary is: '||v_sal);
SELECT first_name,salary
INTO v_emp_name,v_sal
FROM employees
where employee_id=100; -- where condition tomretrive exactly one row
DBMS_OUTPUT.put_line('The Employee Name is: '||v_emp_name);
v_emp_name:='Krupali'; -- Variable value Assignment
v_job:='IT_PROG'; -- use of undeclared variable gives us an error -- identifier
'V_JOB' must be declared
DBMS_OUTPUT.put_line('The Employee Job is: '||v_job);
DBMS_OUTPUT.put_line('The Employee Name is: '||v_emp_name||' The Employee Salary
is: '||v_sal);
DBMS_OUTPUT.put_line('The Employee Salary is: '||v_sal);
end;
-- Write a PLSQL program to display employee name,job_id,hire_date and dept id
set serveroutput on;
DECLARE
v_emp_name VARCHAR2(30);
v_job_id VARCHAR2(20);
v_hire_date DATE;
v_dept_id VARCHAR2(20);
BEGIN
SELECT
first_name,
job_id,
hire_date,
department_id
INTO
v_emp_name,
v_job_id,
v_hire_date,
v_dept_id
FROM
employees
WHERE
employee_id = 102;
dbms_output.put_line('the employee name is: ' || v_emp_name);
dbms_output.put_line('the employee job is: ' || v_job_id);
dbms_output.put_line('the employee date is: ' || v_hire_date);
dbms_output.put_line('the employee department is: ' || v_dept_id);
END;
--- Declaring Variables
-- Variable is used for:
-- temporary storage of data
-- manipulation of stored value
-- Reusabilty
--Variable naming Rules:
-- Must start with a letter
-- Can include numbers and strings
-- Can include special characters( _ , $ , #)
-- it can be of maximum 30 characters long
-- it should not be reserved or keyword
--- Handling variables
-- variables are declared and initialized in declare section
-- Assign or modify value of variable in Begin/executable section
-- Can be used to pass as parameters to PLSQL subprograms
-- Used to hol the output of PLSQL subprograms
:= is the assignement operator in PLSQL
-- Declaring and initialing variables
--Syntax: identifier [CONSTANT] data type [NOT NULL] [:= | default expr]
-- whenever we decalre aany variable as CONSTANT or NOT NULL then we must
initialise some value to it.
-- NOT NULL Variable
Declare
v_Comm number(8,2) NOT NULL:=0.5;
begin
DBMS_OUTPUT.PUT_LINE('Commission is: '|| v_Comm);
select commission_pct
INTO v_Comm
from employees
where employee_id=148;
DBMS_OUTPUT.PUT_LINE('Commission is: '|| v_Comm);
end;
--- CONSTANT Variable
declare
V_int_rate CONSTANT number(8,2):=8.5; --PLS-00322: declaration of a constant
'V_INT_RATE' must contain an initialization assignment
begin
DBMS_OUTPUT.PUT_LINE('Interest rate is: '|| V_int_rate);
-- V_int_rate:=11.5;--expression 'V_INT_RATE' cannot be used as an assignment
target
select commission_pct
INTO V_int_rate
from employees
where employee_id=148;
end;
-- DEfault value for variable
declare
--v_comm number(8,2) default 2;
v_comm number(8,2) NOT NULL default 2;
begin
DBMS_OUTPUT.PUT_LINE('Commission is: '|| v_Comm);
v_Comm:= v_Comm+3;
DBMS_OUTPUT.PUT_LINE('Commission is: '|| v_Comm);
select commission_pct
INTO v_Comm
from employees
where employee_id=198;
DBMS_OUTPUT.PUT_LINE('Commission is: '|| v_Comm);
end;
declare
v_event varchar2(40);
begin
v_event:='FRiendship day';
dbms_output.put_line(v_event);
v_event:='Mother''s day';
dbms_output.put_line(v_event);
v_event:=q'[Father's Day]';
dbms_output.put_line(v_event);
end;
