PL/SQL Cursor

Oracle creates a memory area, known as context area, for processing an SQL statement, which contains all information needed for processing the statement, for example, number of rows processed, etc.

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

You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors:

Implicit cursors

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it. Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

The following table provides the description of the most used attributes:

Attribute Description

%FOUND

Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.

%NOTFOUND

The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.

%ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.

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

Any SQL cursor attribute will be accessed as SQL%attribute_name as shown below in the example.

The following program would update the table and increase salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected:

DECLARE

total_rows number(2);

BEGIN

UPDATE customers

SET salary = salary + 500;

IF sql%notfound THEN

dbms_output.put_line('no customers selected');

ELSIF sql%found THEN

total_rows := sql%rowcount;

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

END IF;

END;

/

When the above code is executed at SQL prompt, it produces the following result:

6 customers selected

PL/SQL procedure successfully completed.

Explicit cursors

Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is :

CURSOR cursor_name IS select_statement;

Working with an explicit cursor involves four steps:

Declaring the cursor for initializing in the memory

Opening the cursor for allocating memory

Fetching the cursor for retrieving data

Closing the cursor to release allocated memory

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example:

CURSOR c_customers IS

SELECT id, name, address FROM customers;

Opening the Cursor

Opening the cursor allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open above-defined cursor as follows:

OPEN c_customers;

Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example we will fetch rows from the above-opened cursor as follows:

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will close above-opened cursor as follows:

CLOSE c_customers;

Following is a complete example to illustrate the concepts of explicit cursors:

DECLARE

c_id customers.id%type;

c_name customers.name%type;

c_addr customers.address%type;

CURSOR c_customers is

SELECT id, name, address FROM customers;

BEGIN

OPEN c_customers;

LOOP

FETCH c_customers into c_id, c_name, c_addr;

EXIT WHEN c_customers%notfound;

dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);

END LOOP;

CLOSE c_customers;

END;

/

When the above code is executed at SQL prompt, it produces the following result:

1 Ramesh Ahmedabad

2 Khilan Delhi

3 kaushik Kota

4 Chaitali Mumbai

5 Hardik Bhopal

6 Komal MP

PL/SQL procedure successfully completed.

PL/SQL Exceptions

An error condition during a program execution is called an exception in PL/SQL. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. The General Syntax for exception handling is as follows. Here you can list down as many as exceptions you want to handle. The default exception will be handled using WHEN others THEN: AND ALWAYS SHOULD BE AT END OF THE BLOCK DECLARE

<declarations section>

BEGIN

<executable command(s)>

EXCEPTION

<exception handling goes here >

WHEN exception1 THEN

exception1-handling-statements

WHEN exception2 THEN

exception2-handling-statements

WHEN exception3 THEN

exception3-handling-statements

........

WHEN others THEN

exception3-handling-statements

END;

There are two types of exceptions:

System-defined exceptions

There are various types of exceptions predefined by oracle.

1. NO DATA FOUND

2. ZERO DIVIDE

3. TOO MANY ROWS etc.

Let us write some simple code to illustrate the concept. We will be using the CUSTOMERS table

DECLARE

c_id customers.id%type := 8;

c_name customers.name%type;

c_addr customers.address%type;

BEGIN

SELECT name, address INTO c_name, c_addr

FROM customers

WHERE id = c_id;

DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);

DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);

EXCEPTION

WHEN no_data_found THEN

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

WHEN others THEN

dbms_output.put_line('Error!');

END;

/

When the above code is executed at SQL prompt, it produces the following result:

No such customer!

PL/SQL procedure successfully completed.

User-defined exceptions

PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

The syntax for declaring an exception is:

DECLARE

my-exception EXCEPTION;

The following example illustrates the concept. This program asks for a customer ID, when the user enters an invalid ID, the exception invalid_id is raised.

DECLARE

c_id customers.id%type := &cc_id;

c_name customers.name%type;

c_addr customers.address%type;

-- user defined exception

ex_invalid_id EXCEPTION;

BEGIN

IF c_id <= 0 THEN

RAISE ex_invalid_id;

ELSE

SELECT name, address INTO c_name, c_addr

FROM customers

WHERE id = c_id;

DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);

DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);

END IF;

EXCEPTION

WHEN ex_invalid_id THEN

dbms_output.put_line('ID must be greater than zero!');

WHEN no_data_found THEN

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

WHEN others THEN

dbms_output.put_line('Error!');

END;

/

When the above code is executed at SQL prompt, it produces the following result:

Enter value for cc_id: -6 (let's enter a value -6)

old 2: c_id customers.id%type := &cc_id;

new 2: c_id customers.id%type := -6;

ID must be greater than zero!

PL/SQL procedure successfully completed.

PL/SQL Triggers

Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:

A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).

A database definition (DDL) statement (CREATE, ALTER, or DROP).

A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

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

Benefits of Triggers

Triggers can be written for the following purposes:

Generating some derived column values automatically Enforcing referential integrity

Event logging and storing information on table access

Auditing

Synchronous replication of tables

Imposing security authorizations

Preventing invalid transactions

Creating Triggers

The syntax for creating a trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

DECLARE

Declaration-statements

BEGIN

Executable-statements

EXCEPTION

Exception-handling-statements

END;

Where,

CREATE [OR REPLACE] TRIGGER trigger_name: Creates or replaces an existing trigger with the trigger_name.

{BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be executed.

The INSTEAD OF clause is used for creating trigger on a view.

{INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.

[OF col_name]: This specifies the column name that would be updated.

[ON table_name]: This specifies the name of the table associated with the trigger.

[REFERENCING OLD AS o NEW AS n]: This allows you to refer new and old values for various DML statements, like INSERT, UPDATE, and DELETE.

[FOR EACH ROW]: This specifies a row level trigger, i.e., the trigger would be executed for each row being affected. Otherwise the trigger will execute just once when the SQL

statement is executed, which is called a table level trigger.

WHEN (condition): This provides a condition for rows for which the trigger would fire. This clause is valid only for row level triggers.

Example:

To start with, we will be using the CUSTOMERS table we had created and used in the

previous chapters:

Select * from customers;

+----+----------+-----+-----------+----------+

| ID | NAME | AGE | ADDRESS | SALARY |

+----+----------+-----+-----------+----------+

| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |

| 2 | Khilan | 25 | Delhi | 1500.00 |

| 3 | kaushik | 23 | Kota | 2000.00 |

| 4 | Chaitali | 25 | Mumbai | 6500.00 |

| 5 | Hardik | 27 | Bhopal | 8500.00 |

| 6 | Komal | 22 | MP | 4500.00 |

+----+----------+-----+-----------+----------+

The following program creates a row level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values: CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON customers

FOR EACH ROW

WHEN (NEW.ID > 0)

DECLARE

sal_diff number;

BEGIN

sal_diff := :NEW.salary - :OLD.salary;

dbms_output.put_line('Old salary: ' || :OLD.salary);

dbms_output.put_line('New salary: ' || :NEW.salary);

dbms_output.put_line('Salary difference: ' || sal_diff);

END;

/

When the above code is executed at SQL prompt, it produces the following result:

Trigger created.

Here following two points are important and should be noted carefully:

OLD and NEW references are not available for table level triggers, rather you can use them for record level triggers.

If you want to query the table in the same trigger, then you should use the AFTER keyword,

because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.

Above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE, which will fire whenever a record will be deleted using DELETE operation on the table.

DML Trigger Example

This example creates a BEFORE statement trigger named EMP_PERMIT_CHANGES in the

schema SCOTT. You would write this type of trigger to place restrictions on DML statements issued on this table (such as when these types of statements could be issued).

CREATE TRIGGER scott.emp_permit_changes

BEFORE

DELETE OR INSERT OR UPDATE

ON scott.emp

pl/sql_block

DML Trigger Example with Restriction

This example creates a BEFORE row trigger named SALARY_CHECK in the schema SCOTT. The PL/SQL block might specify, for example, that the employee's salary must fall within the established salary range for the employee's job:

CREATE TRIGGER scott.salary_check

BEFORE

INSERT OR UPDATE OF sal, job ON scott.emp

FOR EACH ROW

WHEN (new.job <> 'PRESIDENT')

pl/sql_block

Oracle fires this trigger whenever one of the following statements is issued:

-an INSERT statement that adds rows to the EMP table

-an UPDATE statement that changes values of the SAL or JOB columns of the EMP table

SALARY_CHECK is a BEFORE row trigger, so Oracle fires it before changing each row that is updated by the UPDATE statement or before adding each row that is inserted by the INSERT

statement.

SALARY_CHECK has a trigger restriction that prevents it from checking the salary of the company president.

Calling a Procedure in a Trigger Body Example

You could create the SALARY_CHECK trigger described in the preceding example by calling a

procedure instead of providing the trigger body in a PL/SQL block. Assume you have defined

a procedure SCOTT.CHECK_SAL, which verifies that an employee's salary in in an appropriate

range. Then you could create the trigger SALARY_CHECK as follows:

CREATE TRIGGER scott.salary_check

BEFORE INSERT OR UPDATE OF sal, job ON scott.emp

FOR EACH ROW

WHEN (new.job <> 'PRESIDENT')

CALL check_sal(:new.job, :new.sal, :new.ename);

The procedure CHECK_SAL could be implemented in PL/SQL

INSTEAD OF Trigger

These triggers are same as normal triggers but always associated with Views not with table.

CREATE TRIGGER instrig INSTEAD OF INSERT ON all_customers

FOR EACH ROW

BEGIN

IF (:new.cust.location = 'SAN_JOSE') THEN

INSERT INTO customers_sj

VALUES (:new.cust.cust, :new.cust.address,:new.cust.credit);

ELSE

INSERT INTO customers_pa

VALUES (:new.cust.cust, :new.cust.address, :new.cust.credit);

END IF;

END;

MUTATING triggers and example

Many people who are worked in pl/sql have encounteredORA-04091 table <tablename> is mutating Trigger/function might not see it at some time or the other during the development process. Mutating error occurs when we are performing some DML (insert,delete,update) operations and we are trying to select the affected records from the same trigger. So basically

we are trying to select the records in the trigger from the table that owns the trigger. This creates inconsistency issue and Oracle throws a mutating error.

Let us take a simple scenario. We are selecting the records count from student_table after

updating the result coloumn is 'Pass' through TEST_TRIGGER(This trigger is fired on

student_table).

Step 1: create the table student_table

create table student_table(rollnumber number,name varchar2(50),marks

number,section varchar2(3),result varchar2(30))

Step 2: insert 6 records in to the table student_table

begin

insert into student_table values(1000,'chidambaram',99,'A','Pass');

insert into student_table values(1001,'raja',76,'A','Pass');

insert into student_table values(1002,'ram',48,'A','Fail');

insert into student_table values(1003,'jaya',36,'A','Fail');

insert into student_table values(1004,'bala',99,'A','Pass');

insert into student_table values(1005,'sam',76,'A','Pass');

commit;

end;

SQL> select * from student_table;

ROLLNUMBER NAME MARKS SECTION RESULT

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

1000 chidambaram 99 A Pass

1001 raja 76 A Pass

1002 ram 48 A Fail

1003 jaya 36 A Fail

1004 bala 99 A Pass

1005 sam 76 A Pass

Step 3: Create the trigger test_trigger on student_table

CREATE OR REPLACE TRIGGER TEST_TRIGGER

AFTER UPDATE ON student_table

FOR EACH ROW

DECLARE

ln_count NUMBER;

BEGIN

SELECT count(*)

INTO ln_count

FROM student_table

WHERE result = 'Pass';

dbms_output.put_line('Total Number of Students in Passed in A section is

'||ln_count);

END;

/

Now if we try to change the status of result coloumn to 'Pass' for the rollnumber 1003, We

can see mutating error as we are trying to update the record and the trigger is trying to select total number of records which the result is 'Pass' from the same table.

SQL> update student_table set result='Pass' where rollnumber=1003;

ORA-04091: table APPS.STUDENT_TABLE is mutating, trigger/function may not see it ORA-06512: at "APPS.TEST_TRIGGER", line 5 ORA-04088: error during execution of trigger 'APPS.TEST_TRIGGER'

Step 4: Avoid mutating error : Statement level trigger

First one is to create statement level trigger instead of row level trigger. If we omit the 'for

each row' clause from above trigger, it will become statement level trigger. Let us create a

new statement level trigger.

CREATE OR REPLACE TRIGGER TEST_TRIGGER

AFTER UPDATE ON student_table

DECLARE

ln_count NUMBER;

BEGIN

SELECT count(*)

INTO ln_count

FROM student_table

WHERE result = 'Pass';

dbms_output.put_line('Total Number of Students Passed in A section is '||ln_count);

END;

/

Now let us fire the same update statement again.

SQL> update student_table set result='Pass' where rollnumber=1003;

Total Number of Students Passed in A section is 5

1 row updated

When we defined statement level trigger, update went through fine and it displayed the

total number of invalid objects.

Why this is a problem when we are using 'FOR EACH ROW' clause? As per Oracle

documentation, the session, which issues a triggering statement on the table, cannot query

the same table so that trigger cannot see inconsistent data. This restriction applies to all the

row level triggers and hence we run into mutating table error.

Step 5: Avoid mutating error : using pragma autonomous transaction

CREATE OR REPLACE TRIGGER TEST_TRIGGER

AFTER UPDATE ON student_table

FOR EACH ROW

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

ln_count NUMBER;

BEGIN

SELECT count(*)

INTO ln_count

FROM student_table

WHERE result = 'Pass';

dbms_output.put_line('Total Number of Students in Passed in A section is '||ln_count);

END;

/

We can use USER_TRIGGERS / DBA_TRIGGERS views to get the trigger information.

EXECUTE IMMEDIATE option for Dynamic SQL and PL/SQL

EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i

onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL

block created on the fly. Dynamically created and executed SQL statements are

performance overhead, EXECUTE IMMEDIATE aims at reducing the overhead and give

better performance. It is also easier to code as compared to earlier means. The error

messages generated when using this feature are more user friendly. Though DBMS_SQL

is still available, it is advisable to use EXECUTE IMMEDIATE calls because of its benefits over the package.

Usage tips

1. EXECUTE IMMEDIATE will not commit a DML transaction carried out and an explicit commit should be done.

If the DML command is processed via EXECUTE IMMEDIATE, one needs to explicitly

commit any changes that may have been done before or as part of the EXECUTE

IMMEDIATE itself. If the DDL command is processed via EXECUTE IMMEDIATE, it will commit all previously changed data.

2. Multi-row queries are not supported for returning values, the alternative is to use a temporary table to store the records (see example below) or make use of REF cursors.

3. Do not use a semi-colon when executing SQL statements, and use semi-colon at the end when executing a PL/SQL block.

4. This feature is not covered at large in the Oracle Manuals. Below are examples of all possible ways of using Execute immediate. Hope it is handy.

5. For Forms Developers, this feature will not work in Forms 6i front -end as it is on PL/SQL 8.0.6.3.

Example of EXECUTE IMMEDIATE usage

1. To run a DDL statement in PL/SQL.

begin execute immediate 'set role all'; end;

2. To pass values to a dynamic statement (USING clause).

declare l_depnam varchar2(20) := 'testing'; l_loc varchar2(10) := 'Dubai'; begin execute immediate 'insert into dept values (:1, :2, :3)' using 50, l_depnam, l_loc; commit; end;

3. To retrieve values from a dynamic statement (INTO clause).

declare l_cnt varchar2(20); begin execute immediate 'select count(1) from emp' into l_cnt; dbms_output.put_line(l_cnt); end;

4. To call a routine dynamically: The bind variables used for parameters of the routine

have to be specified along with the parameter type. IN type is the default, others have to be specified explicitly.

declare l_routin varchar2(100) := 'gen2161.get_rowcnt'; l_tblnam varchar2(20) := 'emp'; l_cnt number; l_status varchar2(200); begin execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;' using in l_tblnam, out l_cnt, in out l_status; if l_status != 'OK' then dbms_output.put_line('error'); end if; end;

5. To return value into a PL/SQL record type: The same option can be used for

%rowtype variables also.

declare type empdtlrec is record (empno number(4), ename varchar2(20), deptno number(2)); empdtl empdtlrec; begin execute immediate 'select empno, ename, deptno ' || 'from emp where empno = 7934' into empdtl; end;

6. To pass and retrieve values: The INTO clause should precede the USING clause.

declare l_dept pls_integer := 20; l_nam varchar2(20); l_loc varchar2(20); begin

execute immediate 'select dname, loc from dept where deptno = :1' into l_nam, l_loc using l_dept ; end;

7. Multi-row query option. Use the insert statement to populate a temp table for this

option. Use the temporary table to carry out further processing. Alternatively, you may

use REF cursors to by-pass this drawback.

declare l_sal pls_integer := 2000; begin execute immediate 'insert into temp(empno, ename) ' || ' select empno, ename from emp ' || ' where sal > :1' using l_sal; commit; end; What is a Stored Procedure?

A stored procedure or in simple a proc is a named PL/SQL block which performs

one or more specific task. This is similar to a procedure in other programming

languages.

A procedure has a header and a body. The header consists of the name of the

procedure and the parameters or variables passed to the procedure. The body

consists or declaration section, execution section and exception section similar to a

general PL/SQL Block.

A procedure is similar to an anonymous PL/SQL Block but it is named for

repeated usage.

We can pass parameters to procedures in three ways.

1) IN-parameters – always use to get the input parameter

2) OUT-parameters – always used to return the output

3) IN OUT-parameters – always use as both (In and Out parameter)

A procedure may or may not return any value.

General Syntax to create a procedure is:

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters] IS Declaration section BEGIN

Execution section EXCEPTION Exception section END;

IS - marks the beginning of the body of the procedure and is similar to DECLARE

in anonymous PL/SQL Blocks. The code between IS and BEGIN forms the

Declaration section.

The syntax within the brackets [ ] indicate they are optional. By using CREATE

OR REPLACE together the procedure is created if no other procedure with the

same name exists or the existing procedure is replaced with the current code.

The below example creates a procedure ‘employer_details’ which gives the details

of the employee.

1> CREATE OR REPLACE PROCEDURE employer_details 2> IS 3> CURSOR emp_cur IS 4> SELECT first_name, last_name, salary FROM emp_tbl; 5> emp_rec emp_cur%rowtype; 6> BEGIN 7> FOR emp_rec in sales_cur 8> LOOP 9> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name 10> || ' ' ||emp_cur.salary); 11> END LOOP; 12>END; 13> /

How to execute a Stored Procedure?

There are two ways to execute a procedure.

1) From the SQL prompt.

EXECUTE [or EXEC] procedure_name;

2) Within another procedure – simply use the procedure name.

procedure_name;

NOTE: In the examples given above, we are using backward slash ‘/’ at the end of the program. This indicates the oracle engine that the PL/SQL program has ended and it can begin processing the statements.

What is a Function in PL/SQL?

A function is a named PL/SQL Block which is similar to a procedure. The major

difference between a procedure and a function is, a function must always return a

value, but a procedure may or may not return a value.

General Syntax to create a function is

CREATE [OR REPLACE] FUNCTION function_name [parameters] RETURN return_datatype; IS Declaration_section BEGIN Execution_section Return return_variable; EXCEPTION exception section Return return_variable; END;

NOTE: - Functions can be called in SQL statements whereas procedures can’t be.

1) Return Type: The header section defines the return type of the function. The

return datatype can be any of the oracle datatype like varchar, number etc.

2) The execution and exception section both should return a value which is of the

datatype defined in the header section.

For example, let’s create a frunction called ''employer_details_func' similar to the

one created in stored proc

1> CREATE OR REPLACE FUNCTION employer_details_func

2> RETURN VARCHAR(20);

3> IS

5> emp_name VARCHAR(20);

6> BEGIN

7> SELECT first_name INTO emp_name

8> FROM emp_tbl WHERE empID = '100';

9> RETURN emp_name;

10> END;

11> /

In the example we are retrieving the ‘first_name’ of employee with empID 100 to

variable ‘emp_name’. The return type of the function is VARCHAR which is

declared in line no 2. The function returns the 'emp_name' which is of type

VARCHAR as the return value in line no 9.

Packages in PLSQL In Oracle PL/SQL, a PACKAGE is a group of programmatic constructs combined

("packaged") into a single unit. The PL/SQL elements that may be included in a package

are cursors, variables, exceptions, PL/SQL records and collection types, procedures, and

functions.

A package exists in two parts:

Package Specification: This contains the public constructs and acts as the interface to

the package. Note that it contains only constructs prototyping without any logical code.

Package Body: This contains the definition of the constructs prototyped in the spec. It

may also contain the private or locally defined program units, which can be used within

the scope of the package body only.

Packages demonstrate Encapsulation, Data hiding, Subprogram overloading and

Modularization. In terms of performance, packages potentially have both positive and

negative effect. Upon the first call to the package, the entire Package is loaded into the

System Global Area (SGA), which may degrade overall performance. After the package is

loaded, however, if any subprogram in the package needs to be invoked, it requires no

further disk I/O. Packaged subprograms also stop cascading dependencies, which avoids

unnecessary compilation.

CREATE OR REPLACE PACKAGE PKG_UTIL IS

PROCEDURE P_ENAME(P_VAR VARCHAR2);

END;

/

Package created.

CREATE OR REPLACE PACKAGE BODY PKG_UTIL IS

PROCEDURE P_ENAME(P_VAR VARCHAR2) IS

BEGIN

DBMS_OUTPUT.PUT_LINE(P_VAR);

END;

END PKG_UTIL;

/

Package body created.

SQL> EXEC PKG_UTIL.P_ENAME('PSOUG');

PSOUG

PL/SQL procedure successfully completed.

SELECT object_name

FROM user_objects

WHERE object_type = 'PACKAGE';

SELECT object_name, argument_name, position, data_type, data_length

FROM user_arguments

WHERE package_name = 'test_pkg'

ORDER BY object_name, position;

Retrieve PACKAGE Source Code SELECT text

FROM user_source

WHERE name = 'test_pkg';

================================

Overloaded Packages

SELECT overload, COUNT(*)

FROM all_arguments

WHERE overload IS NOT NULL

GROUP BY overload

HAVING COUNT(*) > 1

ORDER BY TO_NUMBER(overload);

Package Overloading Packages can contain multiple iterations of procedures

with the same name using different parameters. This is called 'overloading'.

CREATE OR REPLACE PACKAGE overload_pkgIS

PROCEDURE insby(namein VARCHAR2);

PROCEDURE insby(numbin PLS_INTEGER);

END overload_pkg;

PL/SQL Records

What are records?

Records are another type of datatypes which oracle allows to be defined as a placeholder.

Records are composite datatypes, which means it is a combination of different scalar datatypes

like char, varchar, number etc. Each scalar data types in the record holds a value. A record

can be visualized as a row of data. It can contain all the contents of a row.

Declaring a record:

To declare a record, you must first define a composite datatype; then declare a record for that

type.

The General Syntax to define a composite datatype is:

TYPE record_type_name IS RECORD

(first_col_name column_datatype,

second_col_name column_datatype, ...);

record_type_name – it is the name of the composite type you want to define.

first_col_name, second_col_name, etc.,- it is the names the fields/columns within

the record.

column_datatype defines the scalar datatype of the fields.

-- Record Type declaration in Oracle PLSQL

DECLARE

CURSOR c_empdata IS SELECT ename,sal FROM emp;

TYPE emp_data IS RECORD (ename_rec emp.ename%TYPE,sal_rec emp.sal%TYPE);

emp_d emp_data;

BEGIN

OPEN c_empdata;

LOOP

FETCH c_empdata INTO emp_d;

EXIT WHEN c_empdata%NOTFOUND;

dbms_output.put_line(emp_d.ename_rec||','||emp_d.sal_rec);

END LOOP;

END;

This is similar as we have emp%rowtype but it is useful when we don’t know the

coming data type of the cursor. Let say joins of multiple tables where we have data type

of different types.

DECLARE

TYPE tempno IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER;

TYPE tename IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;

TYPE tjob IS TABLE OF emp.job%TYPE INDEX BY BINARY_INTEGER;

TYPE tmgr IS TABLE OF emp.mgr%TYPE INDEX BY BINARY_INTEGER;

TYPE thiredate IS TABLE OF emp.hiredate%TYPE INDEX BY BINARY_INTEGER;

TYPE tsal IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;

TYPE tcomm IS TABLE OF emp.comm%TYPE INDEX BY BINARY_INTEGER;

TYPE tdeptno IS TABLE OF emp.deptno%TYPE INDEX BY BINARY_INTEGER;

CURSOR c_empdata IS SELECT * FROM emp;

lv_empno tempno;

lv_ename tename;

lv_job tjob;

lv_mgr tmgr;

lv_hiredate thiredate;

lv_sal tsal;

lv_comm tcomm;

lv_deptno tdeptno;

lv_no NUMBER := 0;

PROCEDURE ins_emp_bkp_sp

(

in_empno tempno,

in_ename tename,

in_job tjob,

in_mgr tmgr,

in_hiredate thiredate,

in_sal tsal,

in_comm tcomm,

in_deptno tdeptno

) IS

BEGIN

DELETE emp_bkp;

FORALL J IN in_empno.first..in_empno.last

INSERT INTO emp_bkp VALUES(in_empno( j ),in_ename( j ),in_job( j ),in_mgr( j

),in_hiredate( j ),in_sal( j ),in_comm( j ),in_deptno( j ));

COMMIT;

dbms_output.put_line('Total records are '||in_empno.count||' inserted in table');

END;

BEGIN

FOR i IN c_empdata

LOOP

lv_no := lv_no + 1;

lv_empno(lv_no) := i.empno;

lv_ename(lv_no) := i.ename;

lv_job(lv_no) := i.job;

lv_mgr(lv_no) := i.mgr;

lv_hiredate(lv_no) := i.hiredate;

lv_sal(lv_no) := i.sal;

lv_comm(lv_no) := i.comm;

lv_deptno(lv_no) := i.deptno;

END LOOP;

ins_emp_bkp_sp(lv_empno,lv_ename,lv_job,lv_mgr,lv_hiredate,lv_sal,lv_comm,lv_deptn

o);

END;