pl sql tutorial - Package in pl/sql oracle database
pl/sql tutorial, PL/SQL package is a kind of program only however it contains the subprogram which actually achieves the intended objective
Oracle SQL Complete Tutorial/Training/Course for the beginner:
• Tutorial#1 Basics Of Oracle SQL for Beginn...
-----------------------------------------------------------------------------------------------------------
pl/sql Theory
-----------------------------------------------------------------------------------------------------------
PL/SQL package is a logical grouping of a related subprogram (procedure/function) into a single element.
A Package is compiled and stored as a database object that can be used later.
PL/SQL package has two components.
1) Package Specification: its has only a declaration
2) Package Body: it has a complete definition of procedure/function/cursor
Access specification:
1) private : Private element( procedure/function/variable) are element which are not accessed ouside of the package.
and those element (procedure/function/variable) not declared in specification that will be private
element
2) public: Public element(procedure/function) can be accessed outside of the package and we declare those
element in specification then it becomes public
key Note :
1) whenever specification recompiled, the package also has to recompile
2) If the cursor is defined in the package specification or in global
part of the package body, then the cursor once opened will persist till
the end of the session
3) overloading is nothing but if procedure/function with different
number of parameters, a different type of parameters, or different retype
then it will be known as overloading
4) all the element in the package will share a common keyspace
syntax of package specification:
CREATE [OR REPLACE] PACKAGE package_name
IS
sub_program and public element declaration
.
.
END package name
syntax of package body:
CREATE [OR REPLACE] PACKAGE BODY package_name
IS
Private element definition
sub_program and public element definition
.
BEGIN
Package Initialization
END package_name
Package related table :
ALL_OBJECT
USER_OBJECT
ALL_SOURCE
USER_SOURCE
ALL_PROCEDURES
USER_PROCEDURES
Example :
CREATE OR REPLACE PACKAGE employee_pkg AS
errorMess varchar2(100);
PROCEDURE find_sal(emp_id employees.employee_id%type);
CURSOR c1 RETURN employees%ROWTYPE;
END employee_pkg;
CREATE OR REPLACE PACKAGE BODY employee_pkg AS
CURSOR c1 RETURN employees%ROWTYPE IS
SELECT * FROM employees WHERE salary = 2500;
PROCEDURE find_sal(emp_id employees.employee_id%TYPE) IS
emp_sal employees.salary%TYPE;
BEGIN
SELECT salary INTO emp_sal
FROM employees
WHERE employee_id = emp_id;
dbms_output.put_line('Salary: '|| emp_sal);
END find_sal;
END employee_pkg;
/
set SERVEROUTPUT on;
begin
employee_pkg.find_sal(7499);
END;
/
advantages :
1) Organized code management.
2) Easy (top-down) application design.
3) Easy changes to the implementation.
4) Security and maintainability.
5) Session-wide persistence of variables.
6) Better performance.
follow me on:
Facebook Page:
https://www.facebook.com/Equalconnect...
/ equalconnect
/ lrnwthr
Auf dieser Seite können Sie das Online-Video pl sql tutorial - Package in pl/sql oracle database mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer EqualConnect Coach 08 November 2020 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 12,023 Mal angesehen und es wurde von 172 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
