PL SQL Examples

1)Add new row to table my_employees.

BEGIN
INSERT INTO my_employees
(id, last_name,first_name,userid,salary)
VALUES
(5, 'K', 'Philip',5, 4000);
END;

2) Updating salary (Adding 800 t o salary).

DECLARE
v_sal_increase my_employees.salary%TYPE := 800;
BEGIN
UPDATE my_employees
SET salary = salary + v_sal_increase
WHERE id = 5;
END;

3) Deletion

DECLARE
BEGIN
DELETE FROM my_employees
WHERE id=5;
END;


DECLARE
v_id my_employees.id%TYPE :=6;
BEGIN
DELETE FROM my_employees
WHERE id=v_id;
END;


PROCEDURE
----------------------

4) IN Parameters: Example

CREATE OR REPLACE PROCEDURE raise_salary(v_id IN my_employees.id%TYPE)
IS
BEGIN
UPDATE my_employees
SET salary=salary+400
WHERE id = v_id;
END raise_salary;

exec raise_salary(5);

5) OUT Parameters: Example

CREATE OR REPLACE PROCEDURE query_emp
(p_id IN my_employees.id%TYPE,
p_name OUT my_employees.first_name%TYPE,
p_salary OUT my_employees.salary%TYPE)
IS
BEGIN
SELECT first_name, salary
INTO p_name, p_salary
FROM my_employees
WHERE id = p_id;
END query_emp;

Viewing OUT Parameters

VARIABLE g_name VARCHAR2(25)

VARIABLE g_sal NUMBER

EXECUTE query_emp(1, :g_name, :g_sal)

PRINT g_name g_sal



FUNCTION
----------------

6)

CREATE OR REPLACE FUNCTION get_sal(p_id IN my_employees.id%TYPE)
RETURN NUMBER
IS
v_salary my_employees.salary%TYPE :=0;
BEGIN
SELECT salary
INTO v_salary
FROM my_employees
WHERE id = p_id;
RETURN v_salary;
END get_sal;


VARIABLE g_salary NUMBER

execute :g_salary :=get_sal(1)

print g_salary

7)
--------------------------
PROCEDURES
--------------------
1)
CREATE OR REPLACE PROCEDURE add_job (v_id IN jobs.job_id%TYPE,v_title IN
jobs.job_title%TYPE)
IS
BEGIN
INSERT INTO jobs(job_id,job_title) VALUES (v_id,v_title);
END;

show error;

exec add_job('IT_DBA','Database Administrator');

select*from jobs;


2)
CREATE OR REPLACE PROCEDURE upd_job (v_id IN jobs.job_id%TYPE,v_title IN
jobs.job_title%TYPE)
IS
BEGIN
UPDATE jobs
SET job_title = v_title where job_id=v_id;
END;

exec upd_job('IT_DBA','Database Administrator');

select*from jobs;

3)
CREATE OR REPLACE PROCEDURE del_job (v_id IN jobs.job_id%TYPE)
IS
BEGIN
DELETE FROM jobs
WHERE job_id=v_id;
END;

exec del_job('IT_DBA');

select*from jobs;


4)
CREATE OR REPLACE PROCEDURE query_emp (v_id IN employees.employee_id%TYPE,
hv_salary OUT employees.salary%TYPE,
hv_jobid OUT employees.job_id%TYPE)
IS
BEGIN
SELECT job_id,salary
INTO hv_jobid,hv_salary
FROM employees
WHERE employee_id = v_id;
END;

VARIABLE g_jobid VARCHAR2(25)

VARIABLE g_sal NUMBER

EXECUTE query_emp(188, :g_jobid, :g_sal)

PRINT g_jobid g_sal

-----------------------------------
FUNCTIONS
-------------------

1)
CREATE OR REPLACE FUNCTION q_job(v_id IN jobs.job_id%TYPE)
RETURN VARCHAR2
IS
v_jobtitle jobs.job_title%TYPE;
BEGIN
SELECT job_title
INTO v_jobtitle
FROM jobs
WHERE job_id = v_id;
RETURN v_jobtitle;
END;

VARIABLE g_jobtitle VARCHAR2(35)

execute :g_jobtitle :=q_job('IT_DBA')

print g_jobtitle

2)
CREATE OR REPLACE FUNCTION annual_comp(v_salary IN employees.salary%TYPE,v_commission IN employees.commission_pct%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN((v_salary*12)+(v_commission*v_salary*12));
END;

VARIABLE g_annu_sal NUMBER

execute :g_annu_sal :=annual_comp(2000,2)

print g_annu_sal

3)
CREATE OR REPLACE FUNCTION valid_deptno(v_depart_id IN employees.salary%TYPE)
RETURN BOOLEAN
IS
v_check_id employees.department_id%TYPE;
BEGIN
SELECT department_id
INTO v_check_id
FROM employees WHERE department_id=v_depart_id;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
WHEN OTHERS THEN
RETURN TRUE;

END;


-----------------------------
SET SERVEROUTPUT ON


DECLARE
flag BOOLEAN;
BEGIN

flag :=valid_deptno(50);

IF(flag=TRUE)
THEN
DBMS_OUTPUT.PUT_LINE('Department id Exist');
else
DBMS_OUTPUT.PUT_LINE('Department id not Exist');
END IF;
END;

*******************************************************

----------------------------ok
CREATE OR REPLACE FUNCTION valid_deptno(v_depart_id IN employees.salary%TYPE)
RETURN NUMBER
IS
v_check_id number:=0;
BEGIN
SELECT 1
INTO v_check_id
FROM employees WHERE department_id=v_depart_id;
RETURN v_check_id;
END;

--------------------------------------------

DECLARE
retval NUMBER;
BEGIN
retval :=valid_deptno(50);
END;


VARIABLE g_dep NUMBER

execute :g_dep :=valid_deptno(5000)

-------------------------------ok


CREATE OR REPLACE FUNCTION valid_deptno(v_depart_id IN employees.salary%TYPE)
RETURN BOOLEAN
IS
v_check_id employees.department_id%TYPE;
BEGIN
SELECT department_id
INTO v_check_id
FROM employees WHERE department_id=v_depart_id;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;

-------------------------------

DECLARE
flag BOOLEAN;
BEGIN

DBMS_OUTPUT.PUT_LINE('Department id not Exist');

END;

************************************************************