Advantages of PL/SQL
PL/SQL is a completely portable, high-performance transaction processing language
that offers the following advantages:
■ Tight Integration with SQL
■ Better Performance
■ Higher Productivity
■ Full Portability
■ Tight Security
■ Access to Pre-defined Packages
■ Support for Object-Oriented Programming
■ Support for Developing Web Applications and Pages
Block Structure:-
[DECLARE
-- declarations]
BEGIN
-- statements
[EXCEPTION
-- handlers]
END;
1) Declaring Variables:-
part_no NUMBER(6);
part_name VARCHAR2(20);
in_stock BOOLEAN;
part_price NUMBER(6,2);
raise_app_err exception;
emp_rec1 employees%ROWTYPE; -- The datatype of all columns of employees table.
eg:-
DECLARE
CURSOR c1 IS
SELECT last_name, salary, hire_date, job_id FROM employees
WHERE employee_id = 120;
employee_rec c1%ROWTYPE;
BEGIN
OPEN c1;
FETCH c1 INTO employee_rec;
DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
CLOSE C1;
END;
/
emp_rec2 employees.ename%TYPE; -- The datatype of ename column of employee table.
name varchar2(30);
anname name%type -- (anname datatype will be as per name datatype)
Collections:- (Similar to arrarys)
TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER; -- Used for storing array values
comm_tab commissions;
TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
staff staff_list ;
begin
staff := staff_list(100,200,300,400,500);
FOR i IN staff.FIRST..staff.LAST LOOP
SELECT last_name, first_name INTO lname, fname FROM employees
WHERE employees.employee_id = staff(i);
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(staff(i)) || ': ' || lname || ', ' || fname );
END LOOP;
END;
/
Record:- (Records are composite data structures whose fields can have different datatypes.)
TYPE meetin_typ IS RECORD(
date_held DATE,
duration timerec, -- nested record
location VARCHAR2(20),
purpose VARCHAR2(50));
met_ty meetin_typ
Different methods to assign arrays:-
DECLARE
TYPE nested_type IS TABLE OF VARCHAR2(30);
TYPE varray_type IS VARRAY(5) OF INTEGER;
TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
v1 nested_type;
v2 varray_type;
v3 assoc_array_num_type;
v4 assoc_array_str_type;
v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
v1 := nested_type('Shipping','Sales','Finance','Payroll');
v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
v3(99) := 10; -- Just start assigning to elements
v3(7) := 100; -- Subscripts can be any integer values
v4(42) := 'Smith'; -- Just start assigning to elements
v4(54) := 'Jones'; -- Subscripts can be any integer values
v5('Canada') := 'North America'; -- Just start assigning to elements
v5('Greece') := 'Europe'; -- Subscripts can be string values
END;
/
2) Assigning values to variables:-
hourly_salary NUMBER := 22.50;
v_ename_first varchar2(30) := 'MANZOOR';
v_ename_last varchar2(30);
in_stock BOOLEAN := TRUE;
done BOOLEAN;
begin
comm_tab(5) := 20000 * 0.15;
select ename_last into v_ename_last from emp where empid = 10;
done := (counter > 100);
end;
blood_type char := 'O'; (OR) blood_type char default 'O';
cnt number not null := 100;
3) Declaring Constants
cons constant number := 10;
4) Processsing Queries with pl/sql
BEGIN
FOR someone IN (SELECT * FROM employees WHERE employee_id < 120 )
LOOP
DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name ||
',Last name = ' || someone.last_name);
END LOOP;
END;
/
5) IF Statement
Syntax:-
if condition then
stmt;
elseif condition then
stmt;
else
stmt;
end if;
6) Case Statement
Syntax:-
Case when condition then
stmt;
when condition then
stmt;
else
stmt;
end case;
7) Loop Statements:-
Syntax:-
Loop
stmt;
exit when condition;
End loop;
For Loop
Syntax:-
for i in 1..n loop
stmt;
end loop;
for i in (select * from emp) loop
stmt;
end loop;
While loop
Syntax:-
while condition loop
Stmt;
end loop;
8) Goto Statement (unconditional control transfer):-
Syntax:-
<
eg:
i:=0;
<
i := 1+1;
if i > 10 then
goto gothere;
else
goto comehere;
end if;
<
end;
9) Using a Block Label for Name Resolution
<
DECLARE
last_name VARCHAR2(10) := 'King';
BEGIN
-- deletes everyone, because both LAST_NAMEs refer to the column
DELETE FROM employees2 WHERE last_name = last_name;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
ROLLBACK;
-- OK, column and variable have different names
DELETE FROM employees2 WHERE last_name = main.last_name;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
ROLLBACK;
END;
/
9) Creating a Stored procedures:-
create or replace procedure get_emp_name (v_emp in number) as
ss varchar2(30);
begin
select ename into ss from emp where empno = v_emp;
dbms_output.put_line ('Name of the Employee is :' || ss);
exception
when no_data_found then
dbms_output.put_line ('There is no employ with emp id :' || v_emp);
end;
/
To Exec (1st method):-
declare
emp_id number;
begin
emp_id := 100;
get_emp_name(emp_id);
end;
/
To Exec (2nd method):-
SQL> var emp_id number;
SQL> exec :emp_id := 100;
SQL> call get_emp_name(:emp_id);
Name of the Employee is :Ahamed
Call completed.
10) Creating Packages:-
I) Create Package Specification:-
CREATE OR REPLACE PACKAGE emp_actions AS
PROCEDURE hire_employee (employee_id NUMBER);
PROCEDURE fire_employee (emp_id NUMBER);
FUNCTION num_above_salary (emp_id NUMBER) RETURN VARCHAR2;
END emp_actions;
/
II) Create Package Body:-
CREATE OR REPLACE PACKAGE body emp_actions AS
PROCEDURE hire_employee (employee_id NUMBER) is
begin
insert into emp (empno) values (employee_id);
commit;
end;
PROCEDURE fire_employee (emp_id NUMBER) is
begin
delete from emp where empno = emp_id;
commit;
end;
FUNCTION num_above_salary (emp_id NUMBER) RETURN varchar2 is
emp_sal varchar2(30);
BEGIN
SELECT ename INTO emp_sal FROM emp WHERE empno = emp_id;
RETURN emp_sal;
END num_above_salary;
END emp_actions;
/
To Exec:-
a) SQL> exec emp_actions.hire_employee(100);
b) SQL> exec emp_actions.fire_employee(100);
C) SQL> select emp_actions.num_above_salary(10) from dual;
EMP_ACTIONS.NUM_ABOVE_SALARY(10)
--------------------------------
Ahamed
D) CALL emp_actions.hire_employee(300);
E)
BEGIN
DBMS_OUTPUT.PUT_LINE( 'NAME of employees whose emp id 120 is: ' ||
TO_CHAR(emp_actions.num_above_salary(120)));
emp_actions.fire_employee(300);
END;
/
Creation of triggers:-
CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
new_sal NUMBER(8,2), old_sal NUMBER(8,2) );
CREATE OR REPLACE TRIGGER audit_sal
AFTER UPDATE OF salary ON employees FOR EACH ROW
BEGIN
-- bind variables are used here for values
INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE,
:new.salary, :old.salary );
END;
/
===== Still to add more===================================
