PL/SQL PACKAGES WITH VARIOUS OPTIONS

----------------------------------------------------------------
---CREATE PACKAGE
----------------------------------------------------------------

CREATE OR REPLACE PACKAGE test_pkg AS
PROCEDURE add_test (col1_in NUMBER, col2_in CHAR);
PROCEDURE del_test (col1_in NUMBER);
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg AS

PROCEDURE add_test(col1_in IN NUMBER,col2_in IN CHAR) AS
BEGIN
INSERT INTO test VALUES (col1_in,col2_in);
END add_test;

PROCEDURE del_test(col1_in IN NUMBER) AS
BEGIN
DELETE FROM test WHERE col1_in = col1;
END del_test;

END test_pkg;
/

======================
ALTER PACKAGE test_pkg COMPILE;


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

DROP PACKAGE Header AND BODY

DROP PACKAGE test_pkg;


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

DROP PACKAGE BODY Only

DROP PACKAGE BODY test_pkg;

=================================
---PACKAGES: initialization
=================================

CREATE OR REPLACE PACKAGE init IS
g_var VARCHAR2(50);
FUNCTION dummy RETURN VARCHAR2;
END init_sect;
/

CREATE OR REPLACE PACKAGE BODY init IS
FUNCTION f_test1 RETURN VARCHAR2 IS
BEGIN
RETURN g_var;
END f_test1;
BEGIN
SELECT 'Harod Stassen'
INTO g_var
FROM dual;
END init;
/

=======================================
-- Packages can hold functions as well as procedures
=======================================

CREATE OR REPLACE PACKAGE test2 AS
FUNCTION GetUser RETURN VARCHAR2;
END test2;
/

CREATE OR REPLACE PACKAGE BODY test2 AS
FUNCTION getuser RETURN VARCHAR2 IS
vOSUser user_users.username%TYPE;
BEGIN
SELECT osuser
INTO vUser
FROM sys.v_$session
WHERE sid = (
SELECT sid
FROM sys.v_$mystat
WHERE ROWNUM = 1);
RETURN vUser;
EXCEPTION
WHEN OTHERS THEN
RETURN 'Not Known';
END getuser;
END test2;
/


======================================
---This is a bare-bones package example with both the package specification and the package body.
======================================

CREATE OR REPLACE PACKAGE test1 AS
PROCEDURE get_table_names(number_in IN PLS_INTEGER);
END test1;
/

CREATE OR REPLACE PACKAGE BODY test1 AS
PROCEDURE get_table_names(number_in IN PLS_INTEGER) IS
tabname user_tables.table_name%TYPE;
BEGIN
SELECT table_name
INTO tabname
FROM user_tables
WHERE ROWNUM < style="font-weight: bold;">=================================================
---- 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;
/



CREATE OR REPLACE PACKAGE BODY overload_pkg IS
PROCEDURE local_proc(namein VARCHAR2, nameout OUT VARCHAR2) IS
BEGIN
nameout := TRANSLATE(namein,'AEIOUaeiou','EIOUAeioua');
END local_proc;

PROCEDURE test_proc (namein VARCHAR2) IS
y PLS_INTEGER;
BEGIN
SELECT MAX(seqno)+1
INTO y
FROM persons;

INSERT INTO persons
(seqno, name)
VALUES
(y, namein);
COMMIT;
END test_proc;

PROCEDURE test_proc (numbin PLS_INTEGER) IS
y VARCHAR2(30);
BEGIN
SELECT MAX(name)
INTO y
FROM persons;

local(y, y);

INSERT INTO persons
(seqno, name)
VALUES
(numbin, y);
COMMIT;
END test_proc;

END overload_pkg;
/



======================================
--PACKAGE WITH 2 PROCEDURES
=========================================
CREATE OR REPLACE PACKAGE DEPTPKG
AS
PROCEDURE ADDREC(DEPTNO NUMBER,
DNAME VARCHAR2,
LOC VARCHAR2);
PROCEDURE DELREC(DNO NUMBER);
END DEPTPKG;
/
CREATE OR REPLACE PACKAGE BODY DEPTPKG
AS
PROCEDURE ADDREC(DEPTNO NUMBER,
DNAME VARCHAR2,
LOC VARCHAR2) IS
BEGIN
INSERT INTO DEPT VALUES(DEPTNO, DNAME, LOC)
END ADDRES;

PROCEDURE DELREC(DNO NUMBER) IS
BEGIN
DELETE FROM DEPT WHERE DEPTNO=DNO;
END DELREC;
END DEPTPKG;
/

--- TO TEST THE ABOVE PACKAGE.

EXEC DEPTPKG.ADDREC(50,'HR','DELHI');

EXEC DEPTPKG.DELREC(50);