PLSQL Reference codes

--PLSQL

--DECLARING AND INITIALIZING VARIABLES
SET SERVEROUTPUT ON
DECLARE
Myname VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE('My name is: '||Myname);
Myname := 'John';
DBMS_OUTPUT.PUT_LINE('My name is: '||Myname);
END;

SET SERVEROUTPUT ON
DECLARE
Myname VARCHAR2(20):= 'John';
BEGIN
Myname := 'Steven';
DBMS_OUTPUT.PUT_LINE('My name is: '||Myname);
END;

--DELIMETERS IN STRING LITERALS
SET SERVEROUTPUT ON
DECLARE
event VARCHAR2(15);
BEGIN
event := q'!Father's day!';
DBMS_OUTPUT.PUT_LINE('3rd Sunday in June is :
'||event);
event := q'[Mother's day]';
DBMS_OUTPUT.PUT_LINE('2nd Sunday in May is :
'||event);
END;

--BINARY FLOAT AND BINARY DOUBLE
SET SERVEROUTPUT ON
DECLARE
bf_var BINARY_FLOAT;
bd_var BINARY_DOUBLE;
BEGIN
bf_var := 270/35f;
bd_var := 140d/0.35;
DBMS_OUTPUT.PUT_LINE('bf: '|| bf_var);
DBMS_OUTPUT.PUT_LINE('bd: '|| bd_var);
END;

--DECLARING ANG INITIALIZING VARIABLES
DECLARE
flag BOOLEAN := FALSE;
BEGIN
flag := TRUE;
END;

--BIND VARIABLES
VARIABLE result NUMBER
BEGIN
SELECT (SALARY*12) + NVL(COMMISSION_PCT,0) INTO :result
FROM employees WHERE employee_id = 144;
END;

--PRINTING BIND VARIABLES
VARIABLE emp_salary NUMBER
BEGIN
SELECT salary INTO :emp_salary
FROM employees WHERE employee_id = 178;
END;

--USING SUBSTITUTION VARIABLE
VARIABLE emp_salary NUMBER
SET AUTOPRINT ON
DECLARE
empno NUMBER(6):=&empno;
BEGIN
SELECT salary INTO :emp_salary
FROM employees WHERE employee_id = empno;
END;

--PROMPT FOR SUBSTITUTION VARIABLE
SET VERIFY OFF
VARIABLE emp_salary NUMBER
ACCEPT empno PROMPT 'Please enter a valid employee
number: '
SET AUTOPRINT ON
DECLARE
empno NUMBER(6):= &empno;
BEGIN
SELECT salary INTO :emp_salary FROM employees
WHERE employee_id = empno;
END;

--USING DEFINE FOR USER VARIABLES
SET VERIFY OFF
DEFINE lname= Urman
DECLARE
fname VARCHAR2(25);
BEGIN
SELECT first_name INTO fname FROM employees
WHERE last_name='&lname';
END;

--DATA TYPE CONVERSION
DECLARE
salary NUMBER(6):=6000;
sal_hike VARCHAR2(5):='1000';
total_salary salary%TYPE;
BEGIN
total_salary:=salary+sal_hike;
END;

--NESTED BLOCKS
DECLARE
outer_variable VARCHAR2(20):='GLOBAL VARIABLE';
BEGIN
DECLARE
inner_variable VARCHAR2(20):='LOCAL VARIABLE';
BEGIN
DBMS_OUTPUT.PUT_LINE(inner_variable);
DBMS_OUTPUT.PUT_LINE(outer_variable);
END;
DBMS_OUTPUT.PUT_LINE(outer_variable);
END;

--VARIABLE SCOPE AND VISIBILITY
DECLARE
father_name VARCHAR2(20):='Patrick';
date_of_birth DATE:='20-Apr-1972';
BEGIN
DECLARE
child_name VARCHAR2(20):='Mike';
date_of_birth DATE:='12-Dec-2002';
BEGIN
DBMS_OUTPUT.PUT_LINE('Father''s Name: '||father_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);
DBMS_OUTPUT.PUT_LINE('Child''s Name: '||child_name);
END;
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);
END;

--QUALIFY AN IDENTIFIER
BEGIN <<outer>>
DECLARE
father_name VARCHAR2(20):='Patrick';
date_of_birth DATE:='20-Apr-1972';
BEGIN
DECLARE
child_name VARCHAR2(20):='Mike';
date_of_birth DATE:='12-Dec-2002';
BEGIN
DBMS_OUTPUT.PUT_LINE('Father''s Name: '||father_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '
||outer.date_of_birth);
DBMS_OUTPUT.PUT_LINE('Child''s Name: '||child_name);
END;
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);
END;
END outer;

--DETERMINING VARIABLE SCOPE
BEGIN <<outer>>
DECLARE
sal NUMBER(7,2) := 60000;
comm NUMBER(7,2) := sal * 0.20;
message VARCHAR2(255) := ' eligible for commission';
BEGIN
DECLARE
sal NUMBER(7,2) := 50000;
comm NUMBER(7,2) := 0;
total_comp NUMBER(7,2) := sal + comm;
BEGIN
message := 'CLERK not'||message;
outer.comm := sal * 0.30;
END;
message := 'SALESMAN'||message;
END;
END outer;

--SELECT STATEMENTS IN PLSQL
SET SERVEROUTPUT ON
DECLARE
fname VARCHAR2(25);
BEGIN
SELECT first_name INTO fname
FROM employees WHERE employee_id=200;
DBMS_OUTPUT.PUT_LINE(' First Name is : '||fname);
END;

--RETRIEVING DATA IN PLSQL
DECLARE
emp_hiredate employees.hire_date%TYPE;
emp_salary employees.salary%TYPE;
BEGIN
SELECT hire_date, salary
INTO emp_hiredate, emp_salary
FROM employees
WHERE employee_id = 100;
END;


SET SERVEROUTPUT ON
DECLARE
sum_sal NUMBER(10,2);
deptno NUMBER NOT NULL := 60;
BEGIN
SELECT SUM(salary) -- group function
INTO sum_sal FROM employees
WHERE department_id = deptno;
DBMS_OUTPUT.PUT_LINE ('The sum of salary is '
|| sum_sal);
END;

--INSERTING DATA
BEGIN
INSERT INTO employees
(employee_id, first_name, last_name, email,
hire_date, job_id, salary)
VALUES(employees_seq.NEXTVAL, 'Ruth', 'Cores',
'RCORES',sysdate, 'AD_ASST', 4000);
END;

--UPDATING DATA
DECLARE
sal_increase employees.salary%TYPE := 800;
BEGIN
UPDATE employees
SET salary = salary + sal_increase
WHERE job_id = 'ST_CLERK';
END;

--DELETING DATA
DECLARE
deptno employees.department_id%TYPE := 207;
BEGIN
DELETE FROM employees
WHERE department_id = deptno;
END;

--SIMPLE IF STATEMENT
DECLARE
myage number:=31;
BEGIN
IF myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
END IF;
END;

--IF THEN ELSE STATEMENT
SET SERVEROUTPUT ON
DECLARE
myage number:=31;
BEGIN
IF myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
ELSE
DBMS_OUTPUT.PUT_LINE(' I am not a child ');
END IF;
END;

--IF ELSEIF ELSE CLAUSE
DECLARE
myage number:=31;
BEGIN
IF myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
ELSIF myage < 20
THEN
DBMS_OUTPUT.PUT_LINE(' I am young ');
ELSIF myage < 30
THEN
DBMS_OUTPUT.PUT_LINE(' I am in my twenties');
ELSIF myage < 40
THEN
DBMS_OUTPUT.PUT_LINE(' I am in my thirties');
ELSE
DBMS_OUTPUT.PUT_LINE(' I am always young ');
END IF;
END;

--NULL VALUES IN IF STATEMENT
DECLARE
myage number;
BEGIN
IF myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
ELSE
DBMS_OUTPUT.PUT_LINE(' I am not a child ');
END IF;
END;

--CASE EXPRESSION
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
grade CHAR(1) := UPPER('&grade');
appraisal VARCHAR2(20);
BEGIN
appraisal :=
CASE grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
ELSE 'No such grade'
END;
DBMS_OUTPUT.PUT_LINE ('Grade: '|| grade || '
Appraisal ' || appraisal);
END;

--BASIC LOOPS
DECLARE
countryid locations.country_id%TYPE := 'CA';
loc_id locations.location_id%TYPE;
counter NUMBER(2) := 1;
new_city locations.city%TYPE := 'Montreal';
BEGIN
SELECT MAX(location_id) INTO loc_id FROM locations
WHERE country_id = countryid;
LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((loc_id + counter), new_city, countryid);
counter := counter + 1;
EXIT WHEN counter > 3;
END LOOP;
END;

--WHILE LOOPS
DECLARE
countryid locations.country_id%TYPE := 'CA';
loc_id locations.location_id%TYPE;
new_city locations.city%TYPE := 'Montreal';
counter NUMBER := 1;
BEGIN
SELECT MAX(location_id) INTO loc_id FROM locations
WHERE country_id = countryid;
WHILE counter <= 3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((loc_id + counter), new_city, countryid);
counter := counter + 1;
END LOOP;
END;

--FOR LOOP
DECLARE
countryid locations.country_id%TYPE := 'CA';
loc_id locations.location_id%TYPE;
new_city locations.city%TYPE := 'Montreal';
BEGIN
SELECT MAX(location_id) INTO loc_id
FROM locations
WHERE country_id = countryid;
FOR i IN 1..3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((loc_id + i), new_city, countryid );
END LOOP;
END;

--INDEX BY TABLE OF RECORDS
SET SERVEROUTPUT ON
DECLARE
TYPE emp_table_type IS TABLE OF
employees%ROWTYPE INDEX BY PLS_INTEGER;
my_emp_table emp_table_type;
max_count NUMBER(3):= 104;
BEGIN
FOR i IN 100..max_count
LOOP
SELECT * INTO my_emp_table(i) FROM employees
WHERE employee_id = i;
END LOOP;
FOR i IN my_emp_table.FIRST..my_emp_table.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
END LOOP;
END;


--FETCHING DATA FROM THE CURSOR
SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id =30;
empno employees.employee_id%TYPE;
lname employees.last_name%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO empno, lname;
DBMS_OUTPUT.PUT_LINE( empno ||' '||lname);
END;

--CURSOR FOR LOOPS
SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id =30;
BEGIN
FOR emp_record IN emp_cursor
LOOP
DBMS_OUTPUT.PUT_LINE( emp_record.employee_id
||' ' ||emp_record.last_name);
END LOOP;
END;

--%ROWCOUNT AND %NOTFOUND
SET SERVEROUTPUT ON
DECLARE
empno employees.employee_id%TYPE;
ename employees.last_name%TYPE;
CURSOR emp_cursor IS SELECT employee_id,
last_name FROM employees;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO empno, ename;
EXIT WHEN emp_cursor%ROWCOUNT > 10 OR
emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(empno)
||' '|| ename);
END LOOP;
CLOSE emp_cursor;
END ;

--CURSOR FOR LOOPS USING SUBQUERIES
SET SERVEROUTPUT ON
BEGIN
FOR emp_record IN (SELECT employee_id, last_name
FROM employees WHERE department_id =30)
LOOP
DBMS_OUTPUT.PUT_LINE( emp_record.employee_id ||'
'||emp_record.last_name);
END LOOP;
END;

--EXCEPTION
SET SERVEROUTPUT ON
BEGIN
FOR emp_record IN (SELECT employee_id, last_name
FROM employees WHERE department_id =30)
LOOP
DBMS_OUTPUT.PUT_LINE( emp_record.employee_id ||'
'||emp_record.last_name);
END LOOP;
END;

SET SERVEROUTPUT ON
DECLARE
lname VARCHAR2(15);
BEGIN
SELECT last_name INTO lname FROM employees WHERE
first_name='John';
DBMS_OUTPUT.PUT_LINE ('John''s last name is : '
||lname);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE (' Your select statement
retrieved multiple rows. Consider using a
cursor.');
END;

--NON PREDEFINED ERRORS
SET SERVEROUTPUT ON
DECLARE
insert_excep EXCEPTION;
PRAGMA EXCEPTION_INIT
(insert_excep, -01400);
BEGIN
INSERT INTO departments
(department_id, department_name) VALUES (280, NULL);
EXCEPTION
WHEN insert_excep THEN
DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

--TRAPPING USER DEFINED EXCEPTION
ACCEPT deptno PROMPT 'Please enter the department number:'
ACCEPT name PROMPT 'Please enter the department name:'
DECLARE
invalid_department EXCEPTION;
name VARCHAR2(20):='&name';
deptno NUMBER :=&deptno;
BEGIN
UPDATE departments
SET department_name = name
WHERE department_id = deptno;
IF SQL%NOTFOUND THEN
RAISE invalid_department;
END IF;
COMMIT;
EXCEPTION
WHEN invalid_department THEN
DBMS_OUTPUT.PUT_LINE('No such department id.');
END;

--PROCEDURES
CREATE TABLE dept AS SELECT * FROM departments;
CREATE PROCEDURE add_dept1 IS
dept_id dept.department_id%TYPE;
dept_name dept.department_name%TYPE;
BEGIN
dept_id:=280;
dept_name:='ST-Curriculum';
INSERT INTO dept(department_id,department_name)
VALUES(dept_id,dept_name);
DBMS_OUTPUT.PUT_LINE(' Inserted '||
SQL%ROWCOUNT ||' row ');
END;

CREATE PROCEDURE getemp IS -- header
emp_id employees.employee_id%type;
lname employees.last_name%type;
BEGIN
emp_id := 100;
SELECT last_name INTO lname
FROM EMPLOYEES
WHERE employee_id = emp_id;
DBMS_OUTPUT.PUT_LINE('Last name: '||lname);
END;


--FUNCTIONS
CREATE FUNCTION check_sal RETURN Boolean IS
dept_id employees.department_id%TYPE;
empno employees.employee_id%TYPE;
sal employees.salary%TYPE;
avg_sal employees.salary%TYPE;
BEGIN
empno:=205;
SELECT salary,department_id INTO sal,dept_id
FROM employees WHERE employee_id= empno;
SELECT avg(salary) INTO avg_sal FROM employees
WHERE department_id=dept_id;
IF sal > avg_sal THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;

CREATE FUNCTION avg_salary RETURN NUMBER IS
avg_sal employees.salary%type;
BEGIN
SELECT AVG(salary) INTO avg_sal
FROM EMPLOYEES;
RETURN avg_sal;
END;

--INVOKING THE FUNCTION
SET SERVEROUTPUT ON
BEGIN
IF (check_sal IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('The function returned
NULL due to exception');
ELSIF (check_sal) THEN
DBMS_OUTPUT.PUT_LINE('Salary > average');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary < average');
END IF;
END;

--FETCHING
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec employees%ROWTYPE;
sql_stmt VARCHAR2(200);
my_job VARCHAR2(10) := 'ST_CLERK';
BEGIN
sql_stmt := 'SELECT * FROM employees
WHERE job_id = :j';
OPEN emp_cv FOR sql_stmt USING my_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
-- process record
END LOOP;
CLOSE emp_cv;
END;

--PACKAGE

--PACKAGES
SELECT * FROM STUDENT;
INSERT INTO STUDENT(ROLL_NO, STUD_NAME, MARKS) VALUES(11, 'SONALI', 94);

--PACKAGE SPECIFICATION
CREATE OR REPLACE PACKAGE STUDENT_PACKAGE
AS
     PROCEDURE FIND_MARKS(roll_no1 STUDENT.ROLL_NO%TYPE);
END STUDENT_PACKAGE;

--PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY STUDENT_PACKAGE
AS 
      PROCEDURE FIND_MARKS(roll_no1 STUDENT.ROLL_NO%TYPE)
IS
   s_marks STUDENT.MARKS%TYPE;
BEGIN
      SELECT MARKS INTO s_marks
      FROM STUDENT
      WHERE ROLL_NO=roll_no1;
      DBMS_OUTPUT.PUT_LINE('MARKS '|| s_marks);
   END FIND_MARKS;
END STUDENT_PACKAGE;

--CALLING PACKAGE
DECLARE
   NUM STUDENT.ROLL_NO%TYPE := &mk;
BEGIN
   STUDENT_PACKAGE.FIND_MARKS(NUM);
END;

CREATE TRIGGER check_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
c_min constant number(8,2) := 1000.0;
c_max constant number(8,2) := 500000.0;
BEGIN
IF :new.salary > c_max OR
:new.salary < c_min THEN
RAISE_APPLICATION_ERROR(-20000,
'New salary is too small or large');
END IF;
END;

--USING IN PARAMETERS IN PROCEDURES
CREATE OR REPLACE PROCEDURE raise_salary
(id IN employees.employee_id%TYPE,
percent IN NUMBER)
IS
BEGIN
UPDATE employees
SET salary = salary * (1 + percent/100)
WHERE employee_id = id;
END raise_salary;

EXECUTE raise_salary(176,10)

--USING OUT PARAMETERS IN PROCEDURES
CREATE OR REPLACE PROCEDURE query_emp
(id IN employees.employee_id%TYPE,
name OUT employees.last_name%TYPE,
salary OUT employees.salary%TYPE) IS
BEGIN
SELECT last_name, salary INTO name, salary
FROM employees
WHERE employee_id = id;
END query_emp;

DECLARE
emp_name employees.last_name%TYPE;
emp_sal employees.salary%TYPE;
BEGIN
query_emp(171, emp_name, emp_sal);
END;

--PASSING PARAMETERS IN PROCEDURE
CREATE OR REPLACE PROCEDURE add_dept(
name IN departments.department_name%TYPE,
loc IN departments.location_id%TYPE) IS
BEGIN
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (departments_seq.NEXTVAL, name, loc);
END add_dept;

EXECUTE add_dept ('TRAINING', 2500);

EXECUTE add_dept (loc=>2400, name=>'EDUCATION')

--EXCEPTION HANDLING
CREATE PROCEDURE add_department(
name VARCHAR2, mgr NUMBER, loc NUMBER) IS
BEGIN
INSERT INTO DEPARTMENTS (department_id,
department_name, manager_id, location_id)
VALUES (DEPARTMENTS_SEQ.NEXTVAL, name, mgr, loc);
DBMS_OUTPUT.PUT_LINE('Added Dept: '||name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Err: adding dept: '||name);
END;

CREATE PROCEDURE create_departments IS
BEGIN
add_department('Media', 100, 1800);
add_department('Editing', 99, 1800);
add_department('Advertising', 101, 1800);
END;

--REMOVING FUNCTIONS
DROP FUNCTION avg_salary;



/***********************PACKAGES**************************
--PACKAGES
SELECT * FROM STUDENT;

--PACKAGE SPECIFICATION
CREATE OR REPLACE PACKAGE STUDENT_PACKAGE
AS
     PROCEDURE FIND_MARKS(roll_no1 STUDENT.ROLL_NO%TYPE);
END STUDENT_PACKAGE;

--PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY STUDENT_PACKAGE
AS 
      PROCEDURE FIND_MARKS(roll_no1 STUDENT.ROLL_NO%TYPE)
IS
   s_marks STUDENT.MARKS%TYPE;
BEGIN
      SELECT MARKS INTO s_marks
      FROM STUDENT
      WHERE ROLL_NO=roll_no1;
      DBMS_OUTPUT.PUT_LINE('MARKS '|| s_marks);
   END FIND_MARKS;
END STUDENT_PACKAGE;

--CALLING PACKAGE
DECLARE
   NUM STUDENT.ROLL_NO%TYPE := &mk;
BEGIN
   STUDENT_PACKAGE.FIND_MARKS(NUM);
END;