PL-SQL Essentials
Below all programs were taken from "Oracle PL/SQL Programming" book of o'reilly. Book is worth reading and given number of tips for pl-sql programming.
- Returning Clause and Cursor For Loop
- Accessing cursor from package
- Open_Close_Cursor Bulk Collect Type
- Open_CLose_Cursor Bulk Collect RowType
- Where current Of _ for update of first_name
- Object Oriented
- Returning clause using bulk collect into
- Returning Clause without Bulk Collect
- Cursor For Loop
- Nested Tables
- Varray
- Associative Array
- Collection Methods
- Execute Immediate -- Using Clause
--==================Returning Clause and Cursor For Loop======================
declare
my_salary hr.employees.salary%TYPE;
my_empName hr.employees.first_name%TYPE;
begin
For rec in (select *
from hr.employees)
Loop
update hr.employees
set salary = 101
where employee_id = rec.employee_id
returning salary, first_name
into my_salary, my_empName;
dbms_output.put_line('New Salary For ' || my_empName || '=' || my_salary);
End Loop;
end;
--=======================================================================
Accessing cursor from package
/*Package*/
create or replace package empInfo_pkg
is
cursor emp_cur
is select employee_id, first_name, email
from hr.employees
where MANAGER_ID = 124;
end empInfo_pkg;
/*Anonymouns Proc*/
declare
emp_rec empInfo_pkg.emp_cur%rowtype;
begin
if empInfo_pkg.emp_cur%isopen
then
close empInfo_pkg.emp_cur;
end if;
open empInfo_pkg.emp_cur;
loop
fetch empInfo_pkg.emp_cur into emp_rec;
exit when empInfo_pkg.emp_cur%notfound or empInfo_pkg.emp_cur%rowcount = 6;
dbms_output.put_line(empInfo_pkg.emp_cur%rowcount || emp_rec.email);
end loop;
end;
--=====================================================================
Open_Close_Cursor Bulk Collect Type
declare
cursor emp_info is
select employee_id, first_name, email
from hr.employees;
type empId is table of hr.employees.employee_id%type index by pls_integer;
type empName is table of hr.employees.first_name%type index by pls_integer;
type empEmail is table of hr.employees.email%type index by pls_integer;
employeeID empId;
employeeName empName;
employeeEmail empEmail;
begin
open emp_info;
fetch emp_info bulk collect into employeeID, employeeName, employeeEmail;
close emp_info;
for l_row in employeeID.first .. employeeID.last
loop
dbms_output.put_line(employeeID(l_row) ||employeeName(l_row) || employeeName(l_row) );
end loop;
end;
--=======================================================================
Open_CLose_Cursor Bulk Collect RowType
declare
cursor emp_info is
select *
from hr.employees;
type empRec is table of hr.employees%rowtype index by pls_integer;
employeeRecord empRec;
begin
open emp_info;
fetch emp_info bulk collect into employeeRecord;
close emp_info;
for l_row in employeeRecord.first .. employeeRecord.last
loop
dbms_output.put_line(employeeRecord(l_row).employee_id);
end loop;
end;
--========================================================================
Where current Of _ for update of first_name
/*
1. if u remove "for update of first_name" clause program will not compile
(might be "where current of " requires "for update of"
2. If you remove 2nd "exit", program will give error saying that "ORA-01002:
fetch out of sequence"
*/
declare
cursor emp_curr
is select * from hr.employees
for update of first_name ;
emp_rec emp_curr%rowtype;
begin
open emp_curr;
loop
fetch emp_curr into emp_rec;
if emp_curr%notfound
then
exit;
elsif emp_rec.manager_id = 124
then
dbms_output.put_line(emp_rec.first_name);
update hr.employees
set first_name = 'Dada'
where current of emp_curr;
commit;
exit;
end if;
end loop;
close emp_curr;
end;
--==============================================================================
create or replace type catalog_item_t as object
(
id integer,
title varchar2(4000),
not instantiable member function ck_digit_okay return boolean,
member function print return varchar2
)not instantiable not final;
------------------------------------------------------------------
/*Need to format the code to get it compile- do not know why*/
/* Formatted on 2010/05/16 11:34 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TYPE book_t UNDER catalog_item_t (
isbn VARCHAR2 (13),
pages INTEGER,
CONSTRUCTOR FUNCTION book_t (
ID IN INTEGER DEFAULT NULL,
title IN VARCHAR2 DEFAULT NULL,
isbn IN VARCHAR2 DEFAULT NULL,
pages IN INTEGER DEFAULT NULL
)
RETURN SELF AS RESULT,
OVERRIDING MEMBER FUNCTION ck_digit_okay
RETURN BOOLEAN,
OVERRIDING MEMBER FUNCTION PRINT
RETURN VARCHAR2
)
;
------------------------------------------------------------------
/* Formatted on 2010/05/16 13:13 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TYPE BODY book_t
AS
CONSTRUCTOR FUNCTION book_t (
ID IN INTEGER,
title IN VARCHAR2,
isbn IN VARCHAR2,
pages IN INTEGER
)
RETURN SELF AS RESULT
IS
BEGIN
SELF.ID := ID;
SELF.title := title;
SELF.isbn := isbn;
SELF.pages := pages;
IF isbn IS NULL OR SELF.ck_digit_okay
THEN
RETURN;
ELSE
raise_application_error (-20000,
'ISBN' || isbn || 'has bad check digit'
);
END IF;
END;
OVERRIDING MEMBER FUNCTION ck_digit_okay
RETURN BOOLEAN
IS
subtotal PLS_INTEGER := 0;
isbn_digits VARCHAR2 (10);
BEGIN
/*Remove dashes and Spaces*/
isbn_digits := REPLACE (REPLACE (SELF.isbn, '-'), '');
IF LENGTH (isbn_digits) != 10
THEN
RETURN FALSE;
END IF;
FOR nth_digit IN 1 .. 9
LOOP
subtotal :=
subtotal
+ (11 - nth_digit)
* TO_NUMBER (SUBSTR (isbn_digits, nth_digit), 1);
END LOOP;
/*Check digit can be 'X' which has value of 10 */
IF UPPER (SUBSTR (isbn_digits, 10, 1)) = 'X'
THEN
subtotal := subtotal + 10;
ELSE
subtotal := subtotal + TO_NUMBER (SUBSTR (isbn_digits, 10, 1));
END IF;
RETURN MOD (subtotal, 11) = 0;
EXCEPTION
WHEN OTHERS
THEN
RETURN FALSE;
END;
OVERRIDING MEMBER FUNCTION PRINT
RETURN VARCHAR2
IS
BEGIN
RETURN 'id='
|| ID
|| '; title='
|| title
|| '; isbn='
|| isbn
|| '; pages='
|| pages;
END;
END;
/
---------------------------------------------------------------------------
/* Formatted on 2010/05/16 13:21 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TYPE serial_t UNDER catalog_item_t (
issn VARCHAR2 (10),
open_or_closed VARCHAR2 (1),
CONSTRUCTOR FUNCTION serial_t (
ID IN INTEGER DEFAULT NULL,
title IN VARCHAR2 DEFAULT NULL,
issn IN VARCHAR2 DEFAULT NULL,
open_or_closed IN VARCHAR2 DEFAULT NULL
)
RETURN SELF AS RESULT,
OVERRIDING MEMBER FUNCTION ck_digit_okay
RETURN BOOLEAN,
OVERRIDING MEMBER FUNCTION PRINT
RETURN VARCHAR2
)
NOT FINAL;
----------------------------OR ------------------------------------------
CREATE OR REPLACE TYPE BODY book_t
AS
CONSTRUCTOR FUNCTION book_t (
ID IN INTEGER,
title IN VARCHAR2,
isbn IN VARCHAR2,
pages IN INTEGER
)
RETURN SELF AS RESULT
IS
BEGIN
SELF.ID := ID;
SELF.title := title;
SELF.isbn := isbn;
SELF.pages := pages;
RETURN;
END;
OVERRIDING MEMBER FUNCTION ck_digit_okay
RETURN BOOLEAN
IS
subtotal PLS_INTEGER := 0;
isbn_digits VARCHAR2 (10);
BEGIN
/*Remove dashes and Spaces*/
isbn_digits := REPLACE (REPLACE (SELF.isbn, '-'), '');
IF LENGTH (isbn_digits) != 10
THEN
RETURN FALSE;
END IF;
FOR nth_digit IN 1 .. 9
LOOP
subtotal :=
subtotal
+ (11 - nth_digit)
* TO_NUMBER (SUBSTR (isbn_digits, nth_digit), 1);
END LOOP;
/*Check digit can be 'X' which has value of 10 */
IF UPPER (SUBSTR (isbn_digits, 10, 1)) = 'X'
THEN
subtotal := subtotal + 10;
ELSE
subtotal := subtotal + TO_NUMBER (SUBSTR (isbn_digits, 10, 1));
END IF;
RETURN MOD (subtotal, 11) = 0;
EXCEPTION
WHEN OTHERS
THEN
RETURN FALSE;
END;
OVERRIDING MEMBER FUNCTION PRINT
RETURN VARCHAR2
IS
BEGIN
RETURN 'id='
|| ID
|| '; title='
|| title
|| '; isbn='
|| isbn
|| '; pages='
|| pages;
END;
END;
/
-----------------------OutPut------------------------------
declare
superClass catalog_item_t;
subClass book_t;
begin
--superClass := catelog_item_t();
subClass := new book_t(title => '3 mistakes of my life', isbn=>'0-6848-238-02');
superClass := subClass; --this creates a new object and not just a reference of a object
superClass := new book_t(title => 'My Name Is Khan');
superClass.title := 'Hello World';
dbms_output.put_line(subClass.print());
dbms_output.put_line(superClass.print());
end;
id=; title=3 mistakes of my life; isbn=0-6848-238-02; pages=
id=; title=Hello World; isbn=; pages=
--===============================================================================
Returning clause using bulk collect into
declare
type list_of_names_t is varray(10) of cdw_op_as_resource_dim.resource_code%type;
happyfamily list_of_names_t;
l_row pls_integer;
type list_of_resource is table of cdw_op_as_resource_dim.cdw_op_resource_id%type index by pls_integer;
list_res list_of_resource;
begin
/* You do not need to extend assofiative array
You do not need to initialize associative array*/
happyfamily := list_of_names_t();
happyfamily.extend(2);
happyfamily(1) := 'pkhvrk';
happyfamily(2) := 'anbeach';
forall indx in happyfamily.first .. happyfamily.last
update cdw_op_as_resource_dim
set current_record_flag = 'Y'
where resource_code = (happyfamily(indx))
returning cdw_op_resource_id bulk collect into list_res;
for l_row in list_res.first .. list_res.last
loop
dbms_output.put_line(list_res(l_row));
end loop;
rollback;
end;
--========================================================
Returning Clause without Bulk Collect
declare
type employee_rec is table of cdw_op_as_resource_dim%rowtype;
l_employees employee_rec;
updated_record cdw_op_as_resource_dim.cdw_op_resource_id%type;
begin
select * bulk collect into l_employees
from cdw_op_as_resource_dim
where rownum < 20
and current_record_flag = 'Y';
for l_row in l_employees.first .. l_employees.last
loop
dbms_output.put_line(l_employees(l_row).resource_code);
update cdw_op_as_resource_dim
set current_record_flag = 'Y'
where resource_code = l_employees(l_row).resource_code
returning cdw_op_resource_id into updated_record;
dbms_output.put_line(updated_record);
end loop;
end;
--===============================================================
--Bulk Collect INto
declare
type employee_ids is table of cdw_op_as_resource_dim.resource_code%type;
l_employees employee_ids;
begin
select resource_code bulk collect into l_employees
from cdw_op_as_resource_dim
where rownum < 15;
for l_row in l_employees.first .. l_employees.last
loop
dbms_output.put_line(l_employees(l_row));
end loop;
end;
--===============================================================
begin
for emp_rec in
(
select cdw_op_resource_id, resource_code
from cdw_op_as_resource_dim
where resource_code in ('anbeach','venkk','pkhvrk')
)
loop
if emp_rec.resource_code in ('venkk','pkhvrk')
then
dbms_output.put_line(emp_rec.resource_code || emp_rec.cdw_op_resource_id);
end if;
end loop;
end;
/*
pkhvrk16093
pkhvrk838779
pkhvrk1555737
venkk20419
venkk19817
venkk38099
venkk271747
venkk1555739
*/
--==================================================
Nested Tables - Sir correct if anything wrong*/
create type list_of_names_t is table of varchar2(100);
declare
/*
Varray and Nested Tables you need to always initialized
which is not a case in associative array
*/
happyfamily list_of_names_t := list_of_names_t();
children list_of_names_t := list_of_names_t();
parents list_of_names_t := list_of_names_t ();
begin
/*
Varray and Nested tables you always need to extend before inserting a record
which is not a case in associative array
*/
happyfamily.extend(4);
happyfamily(1) := 'magogate';
happyfamily(2) := 'sgakhar';
happyfamily(3) := 'staksale';
happyfamily(4) := 'Ashish Sir';
children.EXTEND;
children(1) := 'magogate';
children.EXTEND;
children(2) := 'sgakhar';
parents := happyfamily multiset except children;
/*
You can use first and last here as collection is densily filled
which is not a case in associative array.
Nested and Varray are always densily filled
*/
for l_row in parents.FIRST .. parents.LAST
loop
dbms_output.put_line(parents(l_row));
end loop;
end;
--========================================================================
create type first_names_t is varray(2) of varchar2(100);
create type child_names_t is varray(1) of varchar2(100);
create table family(
surname varchar2(1000),
parent_names first_names_t,
children_names child_names_t
);
declare
parents first_names_t := first_names_t();
children child_names_t := child_names_t();
begin
parents.extend(2);
parents(1) := 'staksale';
parents(2) := 'Ashish Sir';
children.extend;
children(1) := 'magogate';
insert into family
(surname, parent_names, children_names)
values('cisco', parents, children);
commit;
end;
--========================================================
declare
type list_of_names_t is table of cdw_op_as_resource_dim.resource_code%type index by pls_integer;
happyfamily list_of_names_t;
l_row pls_integer;
begin
/* You do not need to extend assofiative array
You do not need to initialize associative array*/
happyfamily(200001001) := 'magogate';
happyfamily(-157070) := 'staksale';
happyfamily(-9090) := 'sgakhar';
happyfamily(-88) := 'AshishSir';
l_row := happyfamily.first;
while(l_row is not null)
loop
dbms_output.put_line(happyfamily(l_row));
l_row := happyfamily.next(l_row);
end loop;
end;
/*
Out put started from lowest value first and greatest value at last
staksale
sgakhar
khade
magogate
*
--==================================================================
declare
type list_of_names_t is table of cdw_op_as_resource_dim.resource_code%type index by pls_integer;
happyfamily list_of_names_t;
l_row pls_integer;
begin
/* You do not need to extend assofiative array
You do not need to initialize associative array*/
happyfamily(200001001) := 'magogate';
happyfamily(-157070) := 'staksale';
happyfamily(-9090) := 'sgakhar';
happyfamily(-88) := 'AshishSir';
l_row := happyfamily.first;
while(l_row is not null)
loop
dbms_output.put_line('Display -->'||happyfamily(l_row));
dbms_output.put_line('Prior -->'||happyfamily.prior(l_row));
dbms_output.put_line('Next -->'||happyfamily.next(l_row));
l_row := happyfamily.next(l_row);
end loop;
dbms_output.put_line('Count -->'||happyfamily.count);
dbms_output.put_line('First --> '||happyfamily.first);
dbms_output.put_line('Last --> '||happyfamily.last);
happyfamily.delete(-88);
if not happyfamily.exists(-88)
then
dbms_output.put_line('object at -88 is not present');
end if;
if happyfamily.exists(-88) = false
then
dbms_output.put_line('object at -88 is not present');
end if;
dbms_output.put_line('Count -->'||happyfamily.count);
happyfamily.delete();
dbms_output.put_line('After deletion the Count Is-->'||happyfamily.count);
end;
/*
Display -->staksale
Prior -->
Next -->-9090
Display -->sgakhar
Prior -->-157070
Next -->-88
Display -->AshishSir
Prior -->-9090
Next -->200001001
Display -->magogate
Prior -->-88
Next -->
Count -->4
First --> -157070
Last --> 200001001
object at -88 is not present
object at -88 is not present
Count -->3
After deletion the Count Is-->0
*/
--============================================================
Execute Immediate -- Using Clause
DECLARE
REC_COUNT NUMBER;
v_total number;
cdate date:='20-May-2010';
sql_stmt varchar2(200);
begin
selectcount(*)into v_total from all_tables;
dbms_output.put_line('Totatl number of tables at CDW_BOASA ' || v_total );
for OBJ_rec in
(
SELECT *
FROM ALL_TABLES
where table_name like'%DW%'
)
loop
begin
sql_stmt :='SELECT count(*) FROM '|| OBJ_rec.owner || '.' || OBJ_rec.Table_Name || ' WHERE trunc(create_date) <= :cdate';
EXECUTEIMMEDIATE sql_stmt INTO REC_COUNT USING cdate;
dbms_output.put_line(OBJ_rec.table_NAME || ' = ' || REC_COUNT );
exception
whenothers
then
dbms_output.put_line(sql_stmt);
end;
endloop;
end;
Children Pages
Add Child Page , LastUpdatedDate:6/20/10 4:07 AM, CreatedBy: Mandar Gogate 0 Attachments