Package in PL/SQL and PL/SQL Transactions

Hi Friends, I hope you are doing great. Here we are presenting another tutorial for Oracle PL/SQL which is Package in PL/SQL and Transaction in PL/SQL.

If you missed the previous one, then please visit once trigger in PL/SQL.

What’s inside:
What is the component of a Package in PL/SQL?
What are the uses of Packages in PL/SQL?
Package Specification
Package body
What is transaction in PL/SQL ?
Types of transactions in PL/SQL
FAQS

What is the component of a Package in PL/SQL?

A package has mainly two components – a specification and body part. A specification of the package is declared the type, memory, constants, variable, exception, cursors, and subprograms.

A body of package defines the cursors, function, loops, and procedures.

Why we use Package in PL/SQL?

package in pl/sql

We can use packages in Pl/SQL in many ways some are mentioned below:

  • Packages allow us to combine the logically related types, variables, constants, subprograms, cursors, and exceptions. By doing this, we can make each package more reusable and manageable.
  • Packages allow us to expose the functionality via their specifications and hide the detailed implementation in the package body. It means we can enhance the code in the body of the package without affecting other dependent packages or applications.
  • Oracle loads the package into memory for the first time we invoke a package subprogram. The subsequent calls of other subprograms in the same package do not require disk I/O. This whole mechanism helps us to improve the performance of the program.

Package Specification

The package specification states the public objects that are accessible from outside the package If the public objects of package specification include the cursors and subprograms(PL/SQL query block).

The Package specification contains the following points:

  1. Package Name
  2. The data types names of any arguments.
  3. The declaration of Procedure, functions, variables, constants, cursors, etc., are local to the PL/SQL database and global to the package.

Creating a package specification

For creating a new package specification, we can use the CREATE PACKAGE  statement which is shown below:

CREATE [OR REPLACE] PACKAGE [schema_name.]<pkg_name> IS | AS
    declarations;
END [<pkg_name>];

Explanation of syntax:

  • Here create is used for creating the package while OR Replace is used to replace the package if it exists and recompile it.
  • schema_name is where the package belongs. By default, it is your schema which you give.
  • After IS | AS you can declare your public variables.
  • Between the  IS | AS and END keywords, you have to declare the public items(variable, procedure, functions, cursor, etc. ) of the package specification.

Example:

CREATE PACKAGE COST_NET 
IS
COST_ONE VARCHAR2(10);
COST_TWO NUMBER(10):= 0;
TOTAL_COST NUMBER(10):=0;
NONET_COST EXCEPTION;
END COST_NET;

OUTPUT:

Package created.

Package body

A package body of PL/SQL contains the execution of the cursors or subprograms(SQL queriers) which is declared in the package specification. In the package body,

All SQL queries and subprograms are excecated in this section.

A package body also has an exception-handling block that is used to handle the run time error or exceptions.

Creating a Package body in PL/SQL

For creating a package body in PL/SQL database, we use CREATE PACKAGE BODY statement as shown in the below section:

CREATE [OR REPLACE] PACKAGE BODY [schema_name.]<pkg_name> IS | AS
    declarations
 implementations section;
[BEGIN
EXCEPTION]
END [<pkg_name>];

Syntax explanation

  • Here create is used for creating the package while OR Replace is used for replacing the package if it exists and recompiles it
  • schema_name is where the package belongs. By default, it is your schema which you give.
  • <pkg_name> is the package name that you will give.
  • Between the  IS | AS and END keywords, the declaration of the public items(variable, procedure, functions, cursor, etc. ) and execution will be done in the package body.

Example:

CREATE OR REPLACE PACKAGE BODY cust_selvalue AS  
   
   PROCEDURE find_selvalue(cust_id customer.id%TYPE) IS 
   v_sal customer.salary%TYPE; 
   BEGIN 
      SELECT vsalary INTO v_sal 
      FROM customer
      WHERE id = v_id; 
      dbms_output.put_line('Salary: '|| v_sal); 
   END find_selvalue; 
END cust_selvalue; 
/
Output will be:  Package body created. 

After Package in PL/SQL section we are going to the Transaction in PL/SQL.

Transaction in PL/SQL Database

transaction in pl/sql

What is a transaction in PL/SQL ?

A PL/SQL transaction is nothing but a series of SQL statements which is used for data manipulation of SQL data. A Transaction is an atomic entity with all changes either committed or rollback.

You can control the transactions by using the COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION command.

Types of Transitions in PL/SQL:

1.Commit: commit is used to make the changes permanently save to a PL/SQL database during the transaction(like insert, update). Syntax:

COMMIT;

For Example:

UPDATE emp_info SET emp_role='PL/SQL Developer'
        WHERE name='Harry';
    COMMIT;

2. Rollback: By using rollback command, we can stop the current transaction and undo all the changes which were made in the code before commit. Syntax:

ROLLBACK [TO < savepoint_name>]; 

if you are not defined the savepoint then you can simply use the rollback command like this:

ROLLBACK ;

For example:

INSERT INTO emp_info (emp_ID,name,age,role) 
VALUES (8, 'Harry', 21, 'PL/SQL Developer' ); 
SAVEPOINT save1;
  
UPDATE emp_info
SET name = 'Ron'; 
ROLLBACK TO save1;

3. SAVEPOINT: SAVEPOINT savepoint_names is nothing, but it indicates the current point in the running transaction. Savepoint helps us to rollback a certain part of a transaction instead of the whole.

Syntax:

SAVEPOINT savepoint_name;

4. Autocommit: By name, We can assume that auto-commit. In auto-commit, we don’t need to write the commit every time in each transaction. We can set the AUTOCOMMIT ON to executing the COMMIT Statement automatically. Commonly, It’s automatic execution for each DML statement.

Syntax for ON and OFF both types:

SET AUTOCOMMIT ON;
SET AUTOCOMMIT OFF;

5. Set Transaction: This transaction statement is used to set the transaction as read-only or both read-write. We can also assign the transaction name.

Syntax:

SET TRANSACTION [ READ ONLY | READ / WRITE ]
        [ NAME 'transaction_name' ];

Example:

set transaction read only name 'emp_info';

set transaction read write name 'emp_info';

FAQS:

The ……….. attribute in the ROLLBACK statement provides ANSI compatibility.
Ans: ROLLBACK.
To make changes permanent a …………..statement has to be given at the SQL prompt.
Ans: To make changes permanent a commit statement has to be given at the SQL prompt.
A ROLLBACK statement can not be used to close a transaction. True or False
Ans: False
………… marks and saves this current point in the processing transaction in PL/SQL.
Ans: SAVEPOINT

In the above, we have discussed the Package in PL/SQL and also different types of Transaction in the PL/SQL database. And also we completed our Oracle PL/SQL tutorials. We have completed the all-important sections of PL/SQL. I hope. It is useful for you. Any question or confusion about these topics, please feel free to contact us.

Thank You 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *