DeveloperDeepak PL/SQL/ORACLE Certified: September 2023

Sunday, 17 September 2023

Reference Cursor in Oracle PLSQL

Example : Strong Ref Cursor

declare
type t_emps is ref cursor return employees%rowtype;
rc_emps t_emps;
r_emps employees%rowtype;
begin
open rc_emps for select * from employees;
loop
fetch rc_emps into r_emps;
exit when rc_emps%notfound;
dbms_output.put_line(r_emps.first_name|| ' ' || r_emps.last_name);
end loop;
close rc_emps;
end;

Example: Using two different queries

declare
type t_emps is ref cursor return employees%rowtype;
rc_emps t_emps;
r_emps employees%rowtype;
begin
open rc_emps for select * from retired_employees;
loop
fetch rc_emps into r_emps;
exit when rc_emps%notfound;
dbms_output.put_line(r_emps.first_name|| ' ' || r_emps.last_name);
end loop;
close rc_emps;

dbms_output.put_line('--------------');

open rc_emps for select * from employees where job_id = 'IT_PROG';
loop
fetch rc_emps into r_emps;
exit when rc_emps%notfound;
dbms_output.put_line(r_emps.first_name|| ' ' || r_emps.last_name);
end loop;
close rc_emps;
end;

Example: Using with %type when declaring records first

declare
r_emps employees%rowtype;
type t_emps is ref cursor return r_emps%type;
rc_emps t_emps;
--type t_emps2 is ref cursor return rc_emps%rowtype;
begin
open rc_emps for select * from retired_employees;
loop
fetch rc_emps into r_emps;
exit when rc_emps%notfound;
dbms_output.put_line(r_emps.first_name|| ' ' || r_emps.last_name);
end loop;
close rc_emps;
dbms_output.put_line('--------------');
open rc_emps for select * from employees where job_id = 'IT_PROG';
loop
fetch rc_emps into r_emps;
exit when rc_emps%notfound;
dbms_output.put_line(r_emps.first_name|| ' ' || r_emps.last_name);
end loop;
close rc_emps;
end;

Example: Manually declared record type with cursors example

declare
type ty_emps is record (e_id number,
first_name employees.first_name%type,
last_name employees.last_name%type,
department_name departments.department_name%type);
r_emps ty_emps;
type t_emps is ref cursor return ty_emps;
rc_emps t_emps;
begin
open rc_emps for select employee_id,first_name,last_name,department_name
from employees join departments using (department_id);
loop
fetch rc_emps into r_emps;
exit when rc_emps%notfound;
dbms_output.put_line(r_emps.first_name|| ' ' || r_emps.last_name||
' is at the department of : '|| r_emps.department_name );
end loop;
close rc_emps;
end;

Example: First example of weak ref cursors

declare
type ty_emps is record (e_id number,
first_name employees.first_name%type,
last_name employees.last_name%type,
department_name departments.department_name%type);
r_emps ty_emps;
type t_emps is ref cursor;
rc_emps t_emps;
q varchar2(200);
begin
q := 'select employee_id,first_name,last_name,department_name
from employees join departments using (department_id)';
open rc_emps for q;
loop
fetch rc_emps into r_emps;
exit when rc_emps%notfound;
dbms_output.put_line(r_emps.first_name|| ' ' || r_emps.last_name||
' is at the department of : '|| r_emps.department_name );
end loop;
close rc_emps;
end;

Example: Bind variables with cursors example

declare
type ty_emps is record (e_id number,
first_name employees.first_name%type,
last_name employees.last_name%type,
department_name departments.department_name%type);
r_emps ty_emps;
type t_emps is ref cursor;
rc_emps t_emps;
r_depts departments%rowtype;
--r t_emps%rowtype;
q varchar2(200);
begin
q := 'select employee_id,first_name,last_name,department_name
from employees join departments using (department_id)
where department_id = :t';
open rc_emps for q using '50';
loop
fetch rc_emps into r_emps;
exit when rc_emps%notfound;
dbms_output.put_line(r_emps.first_name|| ' ' || r_emps.last_name||
' is at the department of : '|| r_emps.department_name );
end loop;
close rc_emps;

open rc_emps for select * from departments;
loop
fetch rc_emps into r_depts;
exit when rc_emps%notfound;
dbms_output.put_line(r_depts.department_id|| ' ' || r_depts.department_name);
end loop;
close rc_emps;
end;

Example: sys_refcursor (Instead of working with weak ref cursor)

declare
type ty_emps is record (e_id number,
first_name employees.first_name%type,
last_name employees.last_name%type,
department_name departments.department_name%type);
r_emps ty_emps;
-- type t_emps is ref cursor;
rc_emps sys_refcursor;
r_depts departments%rowtype;
--r t_emps%rowtype;
q varchar2(200);
begin
q := 'select employee_id,first_name,last_name,department_name
from employees join departments using (department_id)
where department_id = :t';
open rc_emps for q using '50';
loop
fetch rc_emps into r_emps;
exit when rc_emps%notfound;
dbms_output.put_line(r_emps.first_name|| ' ' || r_emps.last_name||
' is at the department of : '|| r_emps.department_name );
end loop;
close rc_emps;

open rc_emps for select * from departments;
loop
fetch rc_emps into r_depts;
exit when rc_emps%notfound;
dbms_output.put_line(r_depts.department_id|| ' ' || r_depts.department_name);
end loop;
close rc_emps;
end;

Associative Array in Oracle PLSQL

/********************* The First Example ********************/

DECLARE
TYPE e_list IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
emps e_list;
BEGIN
FOR x IN 100 .. 110 LOOP
SELECT first_name
INTO emps(x)
FROM employees
WHERE employee_id = x ;
END LOOP;
FOR i IN emps.first()..emps.last() LOOP
dbms_output.put_line(emps(i));
END LOOP;
END;

/********* Error Example for the SELECT INTO Clause *********/

DECLARE
TYPE e_list IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
emps e_list;
BEGIN
FOR x IN 100 .. 110 LOOP
SELECT first_name
INTO emps(x)
FROM employees
WHERE employee_id = x
AND department_id = 60;
END LOOP;
FOR i IN emps.first()..emps.last() LOOP
dbms_output.put_line(i);
END LOOP;
END;

/******* Error Example about Reaching an Empty Index ********/

DECLARE
TYPE e_list IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
emps e_list;
BEGIN
emps(100) := 'Bob';
emps(120) := 'Sue';
FOR i IN emps.first()..emps.last() LOOP
dbms_output.put_line(emps(i));
END LOOP;
END;

/*************************************************************

An Example of Iterating in Associative Arrays with WHILE LOOPs

*************************************************************/

DECLARE
TYPE e_list IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
emps e_list;
idx PLS_INTEGER;
BEGIN
emps(100) := 'Bob';
emps(120) := 'Sue';
idx := emps.first;

WHILE idx IS NOT NULL LOOP
dbms_output.put_line(emps(idx));
idx := emps.next(idx);
END LOOP;
END;

/*************************************************************

An Example of Using String-based Indexes with Associative Arrays

*************************************************************/

DECLARE
TYPE e_list IS TABLE OF employees.first_name%TYPE INDEX BY employees.email%type;
emps e_list;
idx employees.email%TYPE;
v_email employees.email%TYPE;
v_first_name employees.first_name%TYPE;
BEGIN
FOR x IN 100 .. 110 LOOP
SELECT first_name, email
INTO v_first_name, v_email
FROM employees
WHERE employee_id = x;
emps(v_email) := v_first_name;
END LOOP;

idx := emps.first;
WHILE idx IS NOT NULL LOOP
dbms_output.put_line('The email of '|| emps(idx) ||' is : '|| idx);
idx := emps.next(idx);
END LOOP;
END;

/*** An Example of Using Associative Arrays with Records ****/

DECLARE
TYPE e_list IS TABLE OF employees%rowtype INDEX BY employees.email%TYPE;
emps e_list;
idx employees.email%type;
BEGIN
FOR x IN 100 .. 110 LOOP
SELECT *
INTO emps(x)
FROM employees
WHERE employee_id = x;
END LOOP;

idx := emps.first;

WHILE idx IS NOT NULL LOOP
dbms_output.put_line('The email of ' ||
emps(idx).first_name || ' ' ||
emps(idx).last_name || ' is : '|| emps(idx).email);
idx := emps.next(idx);
END LOOP;
END;

/* An Example of Using Associative Arrays with Record Types */

DECLARE
TYPE e_type IS RECORD (first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE,
email employees.email%TYPE);
TYPE e_list IS TABLE OF e_type INDEX BY employees.email%TYPE;
emps e_list;
idx employees.email%type;
BEGIN
FOR x IN 100 .. 110 LOOP
SELECT first_name,last_name,email
INTO emps(x)
FROM employees
WHERE employee_id = x;
END LOOP;

idx := emps.first;

WHILE idx IS NOT NULL LOOP
dbms_output.put_line('The email of ' ||
emps(idx).first_name || ' ' ||
emps(idx).last_name || ' is : ' ||
emps(idx).email);
idx := emps.next(idx);
END LOOP;
END;

/**** An Example of Printing From the Last to the First *****/

DECLARE
TYPE e_type IS RECORD (first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE,
email employees.email%TYPE);
TYPE e_list IS TABLE OF e_type INDEX BY employees.email%TYPE;
emps e_list;
idx employees.email%type;
BEGIN
FOR x IN 100 .. 110 LOOP
SELECT first_name,last_name, email
INTO emps(x)
FROM employees
WHERE employee_id = x;
END LOOP;

--emps.delete(100,104);
idx := emps.last;

WHILE idx IS NOT NULL LOOP
dbms_output.put_line('The email of ' ||
emps(idx).first_name || ' ' ||
emps(idx).last_name ||' is : ' ||
emps(idx).email);
idx := emps.prior(idx);
END LOOP;
END;

/***** An Example of Inserting with Associative Arrays ******/

CREATE TABLE employees_salary_history
AS SELECT * FROM employees WHERE 1=2;

ALTER TABLE employees_salary_history ADD insert_date DATE;

SELECT * FROM employees_salary_history;
/
DECLARE
TYPE e_list IS TABLE OF employees_salary_history%rowtype INDEX BY PLS_INTEGER;
emps e_list;
idx PLS_INTEGER;
BEGIN
FOR x IN 100 .. 110 LOOP
SELECT e.*,'01-JUN-20'
INTO emps(x)
FROM employees e
WHERE employee_id = x;
END LOOP;

idx := emps.first;

WHILE idx IS NOT NULL LOOP
emps(idx).salary := emps(idx).salary + emps(idx).salary*0.2;
INSERT INTO employees_salary_history VALUES emps(idx);
dbms_output.put_line('The employee ' || emps(idx).first_name ||
' is inserted to the history table');
idx := emps.next(idx);
END LOOP;
END;
/
DROP TABLE employees_salary_history;

Saturday, 16 September 2023

Use of Varray & Nested Table in Oracle SQL

Code For the Storing Varray Example:

Step-01 Create type to define RECORD as Object in SQL for holding 2 fields data.

CREATE OR REPLACE TYPE t_phone_number AS OBJECT(p_type VARCHAR2(10), p_number VARCHAR2(50));

CREATE OR REPLACE TYPE v_phone_numbers AS VARRAY(3) OF t_phone_number;

Step-03 Use Varray Data type in table.

CREATE TABLE emps_with_phones(employee_id NUMBER,

first_name VARCHAR2(50),

last_name VARCHAR2(50),

phone_number v_phone_numbers);

Step-04 Verify Table.

SELECT * FROM emps_with_phones;

Step-05 Add data to Varray

INSERT INTO emps_with_phones VALUES(10,'Alex','Brown',

v_phone_numbers(t_phone_number('HOME','111.111.1111'),

t_phone_number('WORK','222.222.2222'),

t_phone_number('MOBILE','333.333.3333')));

INSERT INTO emps_with_phones VALUES(11,'Bob','Green',

v_phone_numbers(t_phone_number('HOME','000.000.000'),

t_phone_number('WORK','444.444.4444')));

Step-06 Query the data table to verify Varray.

SELECT e.first_name,

last_name,

p.p_type,

p.p_number

FROM emps_with_phones e, table(e.phone_number) p;

Step-02 Create type as Varray to store as Object.

Code For the Storing Nested Table Example

Step-01 Define Type as table.

CREATE OR REPLACE TYPE n_phone_numbers AS TABLE OF t_phone_number;

Step-02 Use Table Data Type in table column.

CREATE TABLE emps_with_phones2(employee_id NUMBER,

first_name VARCHAR2(50),

last_name VARCHAR2(50),

phone_number n_phone_numbers)

NESTED TABLE phone_number STORE AS phone_numbers_table;

Step-03 Verify Table.

SELECT * FROM emps_with_phones2;

Step-04 Add data to table using Nested Table.

INSERT INTO emps_with_phones2

VALUES(10,'Alex','Brown',n_phone_numbers(t_phone_number('HOME','111.111.1111'),

t_phone_number('WORK','222.222.2222'),

t_phone_number('MOBILE','333.333.3333'))

);

INSERT INTO emps_with_phones2

VALUES(11,'Bob','Green',n_phone_numbers(t_phone_number('HOME','000.000.000'),

t_phone_number('WORK','444.444.4444'))

);

Step-05 Verify data from table.

SELECT e.first_name, last_name, p.p_type, p.p_number

FROM emps_with_phones2 e, table(e.phone_number) p;

Step-06 Add and Modify data to table using Nested Table.

INSERT INTO emps_with_phones2

VALUES(11,'Bob','Green',n_phone_numbers(t_phone_number('HOME','000.000.000'),

t_phone_number('WORK','444.444.4444'),

t_phone_number('WORK2','444.444.4444'),

t_phone_number('WORK3','444.444.4444'),

t_phone_number('WORK4','444.444.4444'),

t_phone_number('WORK5','444.444.4444'))

);

SELECT * FROM emps_with_phones2;

UPDATE emps_with_phones2

SET phone_number = n_phone_numbers(t_phone_number('HOME','000.000.000'),

t_phone_number('WORK','444.444.4444'),

t_phone_number('WORK2','444.444.4444'),

t_phone_number('WORK3','444.444.4444'),

t_phone_number('WORK4','444.444.4444'),

t_phone_number('WORK5','444.444.4444'))

WHERE employee_id = 11;

Step-07 Add and Modify data to table using anonymous block:

DECLARE

p_num n_phone_numbers;

BEGIN

SELECT phone_number

INTO p_num

FROM emps_with_phones2

WHERE employee_id = 10;

p_num.extend;

p_num(5) := t_phone_number('FAX','999.99.9999');

UPDATE emps_with_phones2

SET phone_number = p_num

WHERE employee_id = 10;

END;