Package in Database Management System(DBMS).

-

Packages:

Packages are defined as database objects  use to bundle various sub programs together. It is act as Container which may consists of various datatype, various Variable, Cursor, Procedure , Function etc.

It is created in two separate steps given as follows;  

1. Package Specification
2.Package Body

Package Specification:

Package  specification acts as an interface to the specification body. It consists of various types of declaration, such as variable, cursor, procedure , function without any coding details.
Anything specify inside package specification by  default it taken  as a public member which means the number can be used directly from outside of the package.
  
Any coding done in package body but not specification in package specification are known as private member which means such coding can be used only by the member of  the package.

Syntax of package specification: 

CREATE PACKAGE package_name 
IS|AS 

DECLARE
    .......BLOCK...............

BEGIN
    .......BLOCK...............

EXCEPTION
    .......BLOCK...............

END package_name;

Example:

CREATE PACKAGE pack1
IS|AS
PROCEDURE totalamt (e_id.salesrep.emp_id %TYPE);
FUNCTION findemp (cust_id customer.cust_id%TYPE)
    RETURN salesrep.emp_id%TYPE;
END pack1;


Package Body:

Package body is actual implementation of package. Inside package body, we implement all the procedures and functions specified inside package specification.

Its syntax is given as follows:

CREATE PACKAGEBODY package_body_name 
IS|AS

PROCEDURE procedure_name
IS|AS
    DECLARE
        ...........BLOCK.............

    BEGIN
        ...........BLOCK.............

    EXCEPTION 
        ...........BLOCK.............
    END;

FUNCTION function_name
(variable DATATYPE,.....) RETURN 
IS|AS
    DECLARE
        ...........BLOCK..........

    BEGIN
        ...........BLOCK..........

    EXCEPTION
        ...........BLOCK..........
    END;
END package_body;

To execute the member of the package :

package_name.memer_name;

CREATE PACKAGEBODY pack1
IS|AS
    PROCEDURE total_amt
    (e_id salesrep.emp_id%TYPE)
    IS|AS
    amt orders.amount%TYPE;

    BEGIN
        SELECT SUM(amount) 
        INTO amt
        FROM orders,salesrep
        WHERE rep=emp_id;
        DBMS_OUTPUT.PUT_LINE('total amount is',||amt);
    END;

    FUNCTION findemp (cust_id suctomer.custid %TYPE)
    IS|AS
    BEGIN
        SELECT custrep INTO eid
        FROM  customer
        WHERE custid=cust_id;
         RETURN eid;
    END;

    DECLARE 
        c_id customer.sudt_id%TYPE:=&cust_id;
        e_id salesrep.emp_id%TYPE;

    BEGIN
        e_id:=pack1.findemp(c_id);
        pack1.totalamt(c_id);
END;

Comments

Post a Comment

Popular posts from this blog

Introduction to Python.

-
What is python? Python is an object-oriented programming language created by Guido Rossum in 1998. If you just beginning your programming career, python suits you best. With python you can do everything from GUI developement, web application, Machine learning, IOT, Game Developement etc. so it is general purpose high level programming language. It is very easy language,it is good for beginners. It is case sensitive language. Python is Dynamically Typed, Write less code do more. Introduction Easy to Learn and Use Python is easy to use and learn, it is user friendly and high level programming language. Expressive language Pyton language is more expressive means that is more understand and readable. Unique style Python is an interpreted language i.e interpreter execute the code line by line at a time . This makes debugging easy and thus suitable for beginners. Cross Platform Language Python can run equally on different platform such as windows, Linu...
Read more

Decision Making Statement.

-
Image
Conditional Statement Decision making of an anticipation of condition occuring by execution of program and dpecific action is taken according to condtion. Following is a general form of typical decision making structure found in most programming language. Python programming language uses any non-zero or non-null value as true and if it is either zero or null then it is assume false value. if Condition: In python the block of statement is executed if the condition is true. Syntax if (condition): Block(s) Example var1=100 if var1: print('i got an true value') print(var1) output i got an true value 100 Question: Write a program that prompt user to enter two integer values, print the message "equal" if both entered value are equal a=int(input("Enter first number")) b=int(input("Enter second number")) if a==b: print("equal") output Enter fir...
Read more