PL/SQL - Large objects

VARCHAR2 can store max 4000 bytes data then what next? LOB's or Large object comes into to overcome the Varchar2 limit.

LOB's:

• LOB's or large objects help to store large data.
• It can also store unstructured data such as video, audio, photo images, etc within the database.

LOB's are catwgorized into 2 types:

• Intetnal LOB's
• External LOB's

Internal LOB's:

Internal LOBs are stored withon the database as columns in a table and participate the transaction mode of the server.

• ClOB:  A character LOB, used to store single byte character data. Max size: 8TB to 128 TB.
• BLOB: A binary LOB, used to store binary  raw data. Max size: 8 TB to 128 TB.
• NCLOB: a LOB used to store multi byte character data. Max size: 8TB to 128 TB.

External LOB's:

External LOB's are stored outside of the database as operating system files, only a reference to the actual OS file is stored in the db. External LOBs do not participate in transactions.

BFILE:

BFILE refers to Binary file. The BFILE LOB holds reference to large binary data stored as physical files in the OS outside the db.

Max size: 4 GB

Max size of file name: 255 characters.

Max size of a directory name: 30 characters.

LOB's Restrictions:

• LOBs cant be a part of a clustered table.
• LOBs cant be used in the following part of a SQL statement.
• GROUP BY
• ORDER BY
• SELECT DISTINCT
• JOINS
• AGGREGATE functions
• LOBs cant be analyzed using the Analyze cmd 
• LOBs cant be included in a partitioned index organized table
• LOBs cant be used in VARRAYs.

LOB Locator:

• Every LOB is associated with a lob locator.
• A lob locator is a pointet to the actual location of the LOB value.
• The locator associated with internal LOBs is called a LOB locator while the locator associated with external LOBs os called a BFILE locator.
• When storing data in a LOB column, we are also storing a LOB locator with it.
• This LOB locator is what is returned to us when we select the LOB column.
• The actual LOB valur can then be retrieved using this locator.

Character Large Object (CLOB):

Create a table job_resumes

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

resume clob

);

INSERT INTO JOB_RESUMES values(1, 'Sri' 'Das', ' resume details....');

We can use varius SQL functions on CLOBs. We should use normal functions only on relatively small CLOBs, upto about 100K in size. When we are querying larger LOBs, we sould use the DBMS_LOB package functions for better performances:

Select substr(reume, 1, 30) from job_resumes;

Select DBMS_LOB.substr(reume, 1, 30) from job_resumes;

Select length(resume), dbms_lob.getlength(resume) from job_reumes;

Binary Large Object(BLOB):

• CLOBs can store only character data but BLOBs can store any type of data.
• They are used to hold non-traditional data such as videos, voice, images, pdf, documents etc.
• Even we can insert character data into a BLOB , oracle will store it but it will be in raw data. That means we cant use SQL functions like substr to handle BLOB data.

Create a table job_resumes1

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

resume blob

);

Storing images/videos in BLOBs:

Create a table job_resumes2

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

Profile_picture blob

);

CREATE DIRECTORY MYIMAGES AS 'C:\MYIMAGES';

DECLARE

SRC BFILE := BFILENAME('MYIMAGES','SRI.JPG,');

DEST BLOB;

BEGIN

INSERT INTO JOB_RESUMES2 VALUES(1, 'SRI' ,'M', EMPTY_BLOB()) RETURNING PROFILE_PICTURE INTO DEST;

Dbms_lob.open(src,dbms_lob.lob_readonly);

Dbms_lob.loadfromfile(dest,src,dbms_lob.getlength(src));

Dbms_lob.close(src);

Commit;

End;

/

Binary File(BFILE):

BFile refers to Binary file. This BFILE LOB holds references to large binary data stored as physical files in the OS outside db. They do not participate in the transactions.

Create a table job_resumes3

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

Profile_picture BFILE

);

INSERT INTO JOB_RESUMES values(1, 'Sri' 'Das', BFILENAME('MYIMAGES','SRI.JPG'));

PL/SQL - Create an Object | create table function

Object Creation:

CREATE OR REPLACE TYPE SALES_ROW AS OBJECT

(

S_DATE DATE,

S_ORDERID NUMBER,

S_PRODUCTID NUMBER,

S_CUSTOMERID NUMBER,

S_TOTALAMOUNT NUMBER

);

CREATE a table of  type  SALES_ROW

CREATE TYPE SALES_TABLE IS TABLE OF SALES_ROW;

Table function:

CREATE OR REPLACE FUNCTION FETCH_SALES_TABLE (S_ORDERID NUMBER)

RETURN SALES_TABLE

IS

L_TAB SALES_TABLE := SALES_TABLE();

BEGIN

FOR C IN 

(

SELECT SALES_DATE,ORDER_ID,PRODUCT_ID, CUSTOMER_ID, TOTAL_AMOUNT FROM SALES WHERE ORDER_ID = S_ORDERED

)

LOOP

L_TAB.EXTEND;

L_TAB(L_TAB.LAST) := SALES_ROW(C.SALES_DATE,C.ORDER_ID,C.PRODUCT_ID,C.CUSTOMER_ID,C.TOTAL_AMOUNT);

END LOOP;

RETURN L_TAB;

END;

NOW we can run this function just like querying from a regular table:

Select * from table (fetch_sales_table(1267));

Pipelined functions:

Pipeline negates the need to build huge collections by piping rows out of the function as they are created  saving memory and allowing subsequent processing to start before all the rows are generated.

CREATE OR REPLACE FUNCTION FETCH_SALES_TABLE (S_ORDERID NUMBER)

RETURN SALES_TABLE

PIPELINED

IS

BEGIN

FOR C IN 

(

SELECT SALES_DATE,ORDER_ID,PRODUCT_ID, CUSTOMER_ID, TOTAL_AMOUNT FROM SALES WHERE ORDER_ID = S_ORDERED

)

LOOP

PIPE ROW( SALES_ROW(C.SALES_DATE,C.ORDER_ID,C.PRODUCT_ID,C.CUSTOMER_ID,C.TOTAL_AMOUNT));

END LOOP;

END;

To execute:

Select * from table(fetch_sales_table(1221));

PL/SQL - Bulk Processing

Context Switch:

Every PL/SQL code includes both PL/SQL and SQL statements. PL/SQL statements are run by PL/SQL statement executor. SQL statements are run by the SQL statement executor.

When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. The SQL engine executes the SQL statement and returns information back to the PL/SQL engine.

The transfer of control is called a Context Switch and each of these switches incurs ovethead that slows down the overall performance of our programs.

Bulk Processing concepts:

Bulk Collect:

SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval. We will use BULK COLLECT functionality to fetch all the ORDER_IDs and store it in collection.

FOR ALL:

INSERTs, UPDATEs and DELETEs that use collections to change multiple rows of data very quickly. We will use FOR ALL functionality to update all the rows stored in the collection. FORALL "bundles up" all the updates and passes them to the SQL engine with a single contet switch.

Example:

CREATE OR REPLACE PROCEDURE UPDATE_TAX(TAX_RATE IN NUMBER)

AS

L_ELIGIBLE BOOLEAN;

-- Associative array

TYPE ORDERID_TYPE IS TABLE OF SALES.ORDERID%TYPE INDEX BY PLS_INTEGER

L_ORDER_IDS ORDERID_TYPE;

BEGIN

-- bulk collect the order ids into a collection

SELECT DISTINCT ORDER_ID BULK COLLECT INTO L_ORDER_IDS FROM SALES;

-- for all to update all the rows as a bundle.

FORALL INDX IN 1 .. L_ORDER_IDS.COUNT

UPDATE SALES S

SET S.TAX_AMOUNT = S.TOTAL_AMOUNT *TAX_RATE WHERE S.ORDER_ID = L_ORDER_IDS(INDX);

END;

Note: suppose we have 10 orders, if we fetch the order one by one then it will have 10 context switch but if we use above code to select bulk data and update forall then it will have 1 context switch thus reducing the overhead and peformances.

Bulk Collect with Limit:

PL/SQL collections are arrays in memory, so massive collections can have a detrimental effect on system performance due to the amount of memory they require. In some situations, it may be necessary to split the data being processed into chunks to make the code mote memory friendly. The chunking can be achieved using the LIMIT clause of the BULK collect.

CREATE OR REPLACE PROCEDURE UPDATE_TAX(TAX_RATE IN NUMBER)

AS

L_ELIGIBLE BOOLEAN;

-- Associative array

TYPE ORDERID_TYPE IS TABLE OF SALES.ORDERID%TYPE INDEX BY PLS_INTEGER

L_ORDER_IDS ORDERID_TYPE;

L_ELIGIBLE_ORDERS  ORDERID_TYPE;

CURSOR SALES_CURSOR

IS

SELECT DISTINCT ORDER_ID FROM SALES;

BEGIN

OPEN SALES_CURSOR;

LOOP

FETCH SALES_CURSOR

BULK COLLECT INTO L_ORDER_IDS

LIMIT 100;

FOR INDX IN 1 .. L_ORDER_IDS.COUNT

LOOP

CHECK_ELIGIBLE(L_ORDER_IDS(INDX), L_ELIGIBLE);

IF L_ELIGIBLE

THEN

L_ELIGIBLE_ORDERS(L_ELIGIBLE_ORDERS.COUNT+1):= L_ORDER_IDS(INDX);

END IF;

END LOOP;

EXIT WHEN L_ORDER_IDS.COUNT =0;

END LOOP;

-- for all to update all the rows as a bundle.

FORALL INDX IN 1 .. L_ELIGIBLE_ORDER.COUNT

UPDATE SALES S

SET S.TAX_AMOUNT = S.TOTAL_AMOUNT *TAX_RATE WHERE S.ORDER_ID = L_ELIGIBLE_ORDERS(INDX);

COMMIT;

CLOSE SALES_CURSOR;

END;

BULK collect with Rowtype:

CREATE OR REPLACE PROCEDURE FETCH_SALES_CUR(S_DATE DATE)

AS

CURSOR SALE_CURSOR

IS

SELECT SALES_DAYE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,SALESPERSON_ID,QUANTITY,UNIT_PRICE,SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT

FROM SALES

WHERE SALES_DATE =S_DATE;

TYPE L_SALESTABLE IS TABLE OF SALES%ROWTYPE;

L_SALES L_SALESTABLE;

BEGIN

OPEN SALE_CUTSOR;

LOOP

OPEN FETCH SALE_CURSOR BULK COLLECT INTO L_SALES LIMIT 100;

FOR INDX IN 1 .. L_SALES.COUNT

Loop

Dbms_output.put_line(L_SALES(INDX).SALES_DATE);

Dbms_output.put_line(L_SALES(INDX).ORDER_ID);

Dbms_output.put_line(L_SALES(INDX).PRODUCT_ID);

Dbms_output.put_line(L_SALES(INDX).customer_id);

...

END LOOP;

EXIT WHEN SALE_CURSOR%NOTFOUND;

END LOOP;

CLOSE SALE_CURSOR;

END;

/

PL/SQL - Triggers

Triggers are stored program , which are automatically executed or fired when some events occur.

Triggers are infact executed in response of the following events:

• DML operation - delete, insert and update
• DDL operation - create, alter or drop
• Other db operations - servererror, logon, logoff, shutdown

Triggers could be defined on table, view, schema or db with which the event is associated.

Benefits of Triggers:

• Generating some derived column values automatically
• Enforcing referencial integrity
• Event logging and storing info on table access
• Auditing
• Imposing security authorization
• Preventing invalid transactions

Trigger types:

• Row level Triggers
• Statement level triggers
• Before triggers
• After triggers

Row level Triggers:

Row level triggers execute once for each row in a transaction. For example, if an update statement updates multiple rows of a table, a row level trigger is fired once for each row affected by the update statement.

Example, an update statement updates 3 rows in a table, then 3 triggers will be fired.

Statement level Triggers:

Statement level triggers are triggered only once for each transaction. For example, when an update commands update 2 rows, the commands contained in the trigger are executed only once.

Before Triggers:

Before trigger execute the trigger action before the triggering event(insert, update or delete). 

• Before triggers are used to drive specific column values before completing a triggering insert, update operation.
• Before triggers are used when the trigger action should determine whether or not the triggering statement should be allowed to complete.

After Triggers:

After trigger executes the trigger action after the triggering event(insert, update or delete).

Example, 

To perform cascade delete operation, it means that user delete the record from one table but the corresponding records in other tables are deleted automatically by a trigger which fired after the execution of delete statement issued by the user.

Example 1 - statment level trigger:

When we will update the customet table, before the update, the following trigger will be fired and put an audit entry into the audit table.

CREATE TRIGGER customer_before_update

BEFORE UPDATE

ON CUSTOMER

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'BEFORE UPDATE OPERATION');

END;

Example 2 - staement level with more events

CREATE TRIGGER customer_after_action

AFTER INSERT OR DELETE OR UPDATE

ON CUSTOMER

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

IF INSERTING THEN

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'INSERT OPERATION');

ELSIF DELETING THEN

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'DELETE OPERATION');

ELSIF UPDATING THEN

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'UPDATE OPERATION');

END IF;

END;

Example row level trigger:

CREATE TRIGGER customer_after_update

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)

VALUES('CUSTOMER',V_USERNAME,SYSDATE,'INSERT OPERATION ROW LEVEL');

END;

OLD and NEW pseudo records with an example:

When a row level trigger fires, the PL/SQL runtime system creates and populates the two pseudo records OLD and NEW.

• For an Insert trigger, OLD : none, NEW: nee value
• For an Update trigger, OLD: old value, NEW: New value
• For a Delete trigger, OLD: old value, NEW: none

CREATE TRIGGER customer_after_update

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)

VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);

END;

Row level Triggers Example with WHEN clause:

CREATE TRIGGER customer_after_update

AFTER UPDATE

ON CUSTOMER

FOR EACH ROW

WHEN (OLD.REGION = 'SOUTH')

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)

VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);

END;

Row level triggers with OF clause

CREATE TRIGGER customer_after_update

AFTER UPDATE

OF CUSTOMER_ID

ON CUSTOMER

FOR EACH ROW

WHEN (OLD.REGION = 'SOUTH')

DECLARE

V_USERNAME VARCHAR2(100);

BEGIN

SELECT USER INTO V_USERNAME FROM DUAL;

INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)

VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);

END;

Disable and enable triggers:

Disable/Enable:

ALTER TRIGGER <TRIGGERNAME> DISABLE;

ALTER TRIGGER <TRIGGERNAME> ENABLE;

Disable / enable all the triggers present in the table:

ALTER TABLE <TABLE NAME> DISABLE ALL TRIGGERS;

ALTER TABLE <TABLE NAME> ENABLE ALL TRIGGERS;

Drop a trigger:

DROP  TRIGGER <TRIGGER NAME>;

Rename a trigger:

ALTER TRIGGER <OLD TRIGGER NAME> RENAME TO <NEW TRIGGER NAME>

PL/SQL - Collections

Collection:

• Collection is a composite datatype like Record.
• An Oracle PL/SQL collection is a single dimensional array
• It consist of one more elements accessible through an index value.
• All the elements have the same data type.

Advantages:

Collections are used in some of the important performance optimization features og PL/SQL,

• BULK collect : select statements that retrieve multiple rows with a single fetch.
• FORALL : inserts, updates and deletes that use collections to change multiple rows of data very quickly
• Table functions : functions that return collections and can be called in the FORM clause of a select statement.

Sparse vs dense collection:

If a collection contains all the elements is called dense. And a collection is sparse if there is atleast one index value between the lowest and highest defined index values that is not defined.

Collection methods:

A collection method is a procedure or function that either provide information about the collection or changes the contents of the collection. Methods are attached to the collection variable with dot notation, for example, my_collection.FIRST.

Types of collections:

• Associative Array or index by tables
• Nested tables
• VARRAY

Associative array:

This type of collection is indexed using BINARY_INTEGER values or VARCHAR2 values, which dont need to be consecutive. This were originally called as PL/SQL tables.

DECLARE

TYPE customer_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;

customer_table customer_type;

v_idx number;

BEGIN

customer_table(1):= 'John';

customer_table(2):= 'Rudy';

customer_table(3):= 'Rub';

customer_table(6):= 'Sri';

--delete 3rd element

customer_table.delete(3);

--traverse sparse collection

v_idx := customer_table.first;

WHILE v_idx IS NOT NULL 

LOOP

Dbms_output.put_line('customer name: ' || customer_table(v_idx));

v_idx := customer_table.next(v_idx);

END LOOP display_loop;

END;

Nested Tables:

• Nested table can be stored in a database.
• It can be sparse but are almost always dense.
• They can be indexed only by integer.
• We can use the MULTISET operator to perfoem set operations and to perform equality comparisons on nested tables.
• The elements entry must be sequential.

DECLARE

TYPE customer_type IS TABLE OF VARCHAR2(100);

-- initialize the collection

customer_table customer_type := customer_type();

v_idx number;

BEGIN

--you have to extend before using the table

customer_table.extend(4);

customer_table(1):= 'John';

customer_table(2):= 'Rudy';

customer_table(3):= 'Rub';

--customer_table(6):= 'Sri'; -- throws an error

customer_table(4):='Sri'; -- it must be sequential.

--delete 3rd element

customer_table.delete(3);

--traverse sparse collection

v_idx := customer_table.first;

WHILE v_idx IS NOT NULL 

LOOP

Dbms_output.put_line('customer name: ' || customer_table(v_idx));

v_idx := customer_table.next(v_idx);

END LOOP display_loop;

END;

/

VARRAY:

• A VARRAY is similar to a nested table except we must specify an uppet bound in the declaration.
• Like nested table, we can store in the tadabase.
• Unlike nested tables, individual elements cant be deleted so they remain same.

DECLARE

TYPE customer_type IS VARRAY(4) OF VARCHAR2(100);

-- initialize the collection

customer_table customer_type := customer_type();

v_idx number;

BEGIN

--you have to extend before using the table

customer_table.extend(4);

customer_table(1):= 'John';

customer_table(2):= 'Rudy';

customer_table(3):= 'Rub';

--customer_table(6):= 'Sri'; -- throws an error

customer_table(4):='Sri'; -- it must be sequential.

--can not delete an item. 

--customer_table.delete(3);

--traverse sparse collection

v_idx := customer_table.first;

WHILE v_idx IS NOT NULL 

LOOP

Dbms_output.put_line('customer name: ' || customer_table(v_idx));

v_idx := customer_table.next(v_idx);

END LOOP display_loop;

END;

/

Collection methods:

Multiset operator:

We can do following operations

• MULTISET UNION
• MULTISET UNION DISTINCT
• MULTISET EXCEPT
• MULTISET INTERSECT

DECLARE

TYPE t_tab IS TABLE OF NUMBER;

l_tab1 t_tab := t_tab(1,2,3,4,5,6);

l_tab2 t_tab := t_tab(5,6,7,8,9,10);

BEGIN

l_tab1 := l_tab1 MULTISET INTETSET l_tab2;

FOR i IN l_tab1.first .. l_tab1.last LOOP

Dbms_output.put_line(l_tab1(i));

END LOOP;

END;

/

PL/SQL - Cursors

Cursors:

Oracle creates a memory area, called as context area for processing an SQL statement, which contains all information needed for processing the statement.

A cursor is a pointer to this context area. PL/SQL controls the context area through cursor. A cursor holds the rows( one or many) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.

We can name a cursor so that we can use it in a program to fetch and process the rows returned by the SQL statements, one at a time,

There are two types of cursors:

• Implicit cursor
• Explicit cursor

Implicit cursor:

• Implicit cursors are automatically created by Oracle whenever an SQL statement(DML statements like INSERT, UPDATE AND DELETE) is executed. 
• For INSERT operations, the cutsor holds the data that needs to be inserted. For UPDATE AND DELETE operations, the cursors identifies the tows that would be affected.

In PL/SQL, we can refer to the most recent implicit cursor as the SQL CURSOR, which always has the following attributes:

%FOUND : Returns true of Select into, insert, update and delete statement affected one or more rows, else it returns FALSE.

%ISOPEN : always returns false, cause Oracle closes the sql cursor automatically after executing its associated sql statement.

%NOTFOUND : The logical opposite of %FOUND.

%ROWCOUNT: Returns the number of rows affected by an INSERT, UPDATE OR DELETE or returned by a SELECT INTO statement.

Example:

IF sql%found THEN

total_rows := sql%rowcount;

dbms_output.put_line( total_rows || 'customers selected ');

END IF;

Explicit cursor:

A cursor which we can create ourselves, not a default oracle one, is called explicit cursor. 

**A cursor is the name for a structure in memory, called a private SQL area, which the server allocates at runtime for each statement.

Steps to create an Explicit cursor:

• Declare
• Open
• Fetch one or more times
• Close

Example1: retrieving 1 row using explicit cursor:

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

--declaration of cursor

cursor c is SELECT first_name, country FROM customer WHERE customer_id = c_id;

BEGIN

open c; -- open cursor

-- fetching of data

fetch c INTO c_name, c_country;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

Close c; --closing cursor.

EXCEPTION

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN too_many_rows THEN

dbms_output.put_line('You got more than 1 row!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

/

Example2: retrieving more than 1 row using explicit cursor:

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

--declaration of cursor

cursor c is SELECT first_name, country FROM customer WHERE customer_id = c_id;

BEGIN

open c; -- open cursor

LOOP

-- fetching of data

fetch c INTO c_name, c_country;

EXIT WHEN C%NOTFOUND;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

END LOOP;

Close c; --closing cursor.

END;

Using Records in cursor:

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

--declaration of cursor

cursor c is SELECT first_name, country FROM customer WHERE customer_id = c_id;

c_rec customer%rowtype;

BEGIN

open c; -- open cursor

LOOP

-- fetching of data

fetch c INTO c_rec;

EXIT WHEN C%NOTFOUND;

dbms_output.put_line('Name: '|| c_rec.first_name);

dbms_output.put_line('Country: '|| c_rec.country);

END LOOP;

Close c; --closing cursor.

END;

Cursor For Loop:

With cursor FOR loop, the body of the loop is executed for each row returned by the sql query.

Syntax:

FOR customer_rec IN

(Select * from customer where customer_id = 10)

LOOP

Dbms_output.put_line(customer_rec.last_name);

END LOOP;

Example:

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

BEGIN

FOR c_rec IN (SELECT first_name, country FROM customer WHERE customer_id = c_id)

LOOP

dbms_output.put_line('Name: '|| c_rec.first_name);

dbms_output.put_line('Country: '|| c_rec.country);

END LOOP;

END;

Cursor variable and reference cursor:

A cursor variable is a variable that points to a cursor or a result set. We can pass a cursor variable as a arugment to a procedure or function.

Pass a cursor variable back to the host environment that called the program unit - the result set can be consumed for display or other processing.

Syntax:

C_variable SYS_REFCURSOR;

Example:

CREATE OR REPLACE FUNCTION get_names

(

     cust_id IN NUMBER

)

     RETURN SYS_REFCURSOR

IS

     l_return SYS_REFCURSOR;

BEGIN

     OPEN l_return FOR

          SELECT first_name,last_name FROM        customer WHERE customer_id = cust_id;

     RETURN l_return;

END get_names;

CREATE OR REPLACE PROCEDURE DISPLAY_NAMES

IS

C_REC SYS_REFCURSOR;

FNAME VARCHAR2(50);

LNAME VARCHAR2(50);

BEGIN

C_REC := GET_NAMES(10);

LOOP

FETCH C_REC INTO FNAME,LNAME;

EXIT WHEN C_REC%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(FNAME);

DBMS_OUTPUT.PUT_LINE(LNAME);

END LOOP;

CLOSE C_REC;

END;

EXECUTE DISPLAY_NAMES();

Exceptions for cursors:

CURSOR_ALREADY_OPEN: we get this exception when we try to open a cursor thats already open.

INVALID_CURSOR: If we try to reference a cursor that does not exist. This may have happened cause we have executed a FETCH cursor or CLOSE cursor before opening the cursor.

PL/SQL - Working with Records | ROWTYPE data type

Records:

• A record is a composite datatype, which means that it can hold more than one piece of information, as compared to a scalar datatype such as string or number.
• Tables are made up of rows of data, each consisting of one or more colunns, so it stands to reason that Oracle database would make it as easy as possible to work with those rows of data inside a PL/SQL program, and it does precisely that through its implementation of the record. 
• Dealing one row at a time rather than long list of columns or variables,  is far better performance and easy to maintain.

Example:

CREATE OR REPLACE PROCEDURE process_customer

(

c_id IN customer.customer_id%type

)

IS

c_rec customer%rowtype;

BEGIN

Select * into c_rec FROM customer WHERE customer_id = c_id;

Dbms_output.put_line('First Name: ' || c_rec.first_name);

Dbms_output.put_line('Last Name: ' || c_rec.last_name);

END;

Passing Records as parameters:

CREATE OR REPLACE PROCEDURE process_customer

(

c_id IN customer.customer_id%type

)

IS

c_rec customer%rowtype;

BEGIN

Select * into c_rec FROM customer WHERE customer_id = c_id;

show_customer(c_rec);

END;

CREATE PROCEDURE show_customer

(

customer_in IN customer%ROWTYPE

)

IS

BEGIN

Dbms_output.put_line('First Name: ' || customer_in.first_name);

Dbms_output.put_line('Last Name: ' || customer_in.last_name);

END;

/

Insert data using Records:

CREATE OR REPLACE PROCEDURE show_customer

(

customer_in IN customer%ROWTYPE

)

IS

BEGIN

INSERT INTO CUSTOMER VALUES CUSTOMER_IN;

COMMIT;

END;

/

Update data using Records:

CREATE OR REPLACE PROCEDURE update_customer

(

customer_in IN customer%ROWTYPE

)

IS

BEGIN

UPDATE CUSTOMER SET ROW =  CUSTOMER_IN WHERE CUSTOMER_ID = CUSTOMER_IN.CUSTOMER_ID;

COMMIT;

END;

/

User defined Record types:

So far whatever records we have used , are based on table or cursor. We can declare our own user defined record using TYPE....RECORD statement.

Some times business requirement is such that it does not meet with table columns, so in that case, we can create custom record to overconme such hardle.

Syntax:

TYPE customer_rec IS RECORD

(

customer_name varchar2(100),

total_sales number,

...

)

customer_info_rec1 customer_rec;

customer_info_rec2 customer_rec;

Example:

CREATE OR REPLACE PROCEDURE process_customer

(

c_id IN customer.customer_id%type

)

IS

TYPE customer_rec IS RECORD

(

first_name varchar2(100);

last_name varchar2(100);

)

c_rec customer_rec;

BEGIN

Select first_name,last_name into c_rec FROM customer WHERE customer_id = c_id;

Dbms_output.put_line('First Name: ' || c_rec.first_name);

Dbms_output.put_line('Last Name: ' || c_rec.last_name);

END;