Package and Cursor

PACKAGES

• It is a db object that binds multiple functionally under a single name and can be used as desired.
• It is a way of creating, generic, encapsulated, reusable code.
• It enhances modularity of functionality.
• Under a package, we define different roles and give the procedures or privileges.

How to create a package?

CREATE  PACKAGE  package_name AS

procedure P₁();           //already created

procedure P₂();           //we can defines roles, procedures etc

END package_name;

//To delete package:

DROP PACKAGE package_name;

//To alter package:

ALTER PACKAGE package_name AS

        Add procedures;

        Remove procedures;

        Alter the definition of procedures;

END package_name;

Here, along with the declaration of the packages, we can along create procedures inside it if it is not created before.

CURSOR

It is a db object that fetches or retains data in certain variables and can be used as a record at a time. It is viewed as a pointer to one row in a set of rows.

How to create a cursor?

BEGIN

DECLARE  a, sub1 int;

DECLARE  b, sub2 varchar(20);

DECLARE cur1 CURSOR FOR SELECT * FROM 1^st div;

OPEN cur1;

LOOP FETCH cur1 INTO a, sub1, b;

END LOOP;

CLOSE cur1;

END;

/