PL/SQL ORACLE

Calling the SQL COUNT Function in PL/SQL
----------------------------------------

DECLARE

job_count NUMBER;

emp_count NUMBER;

BEGIN

SELECT Count(DISTINCT job_id)

INTO job_count

FROM employees;



SELECT Count(* )

INTO emp_count

FROM employees;

END;

/


Substituting PL/SQL Variables
------------------------------

CREATE TABLE employees_temp
AS
SELECT first_name,
last_name
FROM employees;



DECLARE

x VARCHAR2(20) := 'my_first_name';

y VARCHAR2(25) := 'my_last_name';

BEGIN

INSERT INTO employees_temp

VALUES (x,

y);



UPDATE employees_temp

SET last_name = x

WHERE first_name = y;



DELETE FROM employees_temp

WHERE first_name = x;



COMMIT;

END;

/


Checking SQL%ROWCOUNT After an UPDATE
--------------------------------------

CREATE TABLE employees_temp
AS
SELECT *
FROM employees;



BEGIN

UPDATE employees_temp

SET salary = salary * 1.05

WHERE salary < 5000;



dbms_output.Put_line('Updated '

||SQL%ROWCOUNT

||' salaries.');

END;

/



Output :-

Updated 49 salaries.

PL/SQL procedure successfully completed.


Data Manipulation With PL/SQL
---------------------------------

CREATE TABLE employees_temp
AS
SELECT employee_id,
first_name,
last_name
FROM employees;



DECLARE

emp_id employees_temp.employee_id%TYPE;

emp_first_name employees_temp.first_name%TYPE;

emp_last_name employees_temp.last_name%TYPE;

BEGIN

INSERT INTO employees_temp

VALUES (299,

'Bob',

'Henry');



UPDATE employees_temp

SET first_name = 'Robert'

WHERE employee_id = 299;



DELETE FROM employees_temp

WHERE employee_id = 299

RETURNING first_name,last_name INTO emp_first_name,emp_last_name;



COMMIT;



dbms_output.Put_line(emp_first_name

||' '

||emp_last_name);

END;

/



Output :-

Robert Henry

PL/SQL procedure successfully completed.



Using BULK COLLECT With a SELECT INTO Statement
-------------------------------------------------
You can use the BULK COLLECT clause with a SELECT INTO or FETCH statement to retrieve a set of rows into a collection of records.


DECLARE

TYPE employeeset IS TABLE OF employees%ROWTYPE;

underpaid EMPLOYEESET; -- Holds set of rows from EMPLOYEES table.

CURSOR c1 IS

SELECT first_name,

last_name

FROM employees;

TYPE nameset IS TABLE OF c1%ROWTYPE;

some_names NAMESET; -- Holds set of partial rows from EMPLOYEES table.



BEGIN

-- With one query, we bring all the relevant data into the collection of records.

SELECT *

BULK COLLECT INTO underpaid

FROM employees

WHERE salary < 5000

ORDER BY salary DESC;



-- Now we can process the data by examining the collection, or passing it to

-- a separate procedure, instead of writing a loop to FETCH each row.

dbms_output.Put_line(underpaid.COUNT

||' people make less than 5000.');



FOR i IN underpaid.FIRST.. underpaid.LAST LOOP

dbms_output.Put_line(Underpaid(i).last_name

||' makes '

||Underpaid(i).salary);

END LOOP;



-- We can also bring in just some of the table columns.

-- Here we get the first and last names of 10 arbitrary employees.

SELECT first_name,

last_name

BULK COLLECT INTO some_names

FROM employees

WHERE ROWNUM < 11;



FOR i IN some_names.FIRST.. some_names.LAST LOOP

dbms_output.Put_line('Employee = '

||Some_names(i).first_name

||' '

||Some_names(i).last_name);

END LOOP;

END;

/