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);

PL/SQL FUNCTIONS WITH VARIOS OPTIONS

------------------------------------------------------------
-Functions - debug
-------------------------------------------------------------

To examine a function in debug mode

DESC user_plsql_object_settings

SELECT name, TYPE, plsql_debug
FROM user_plsql_object_settings;

ALTER FUNCTION test1 COMPILE DEBUG;

------------------------------------------------------------
-Function without Parameter
-------------------------------------------------------------
CREATE OR REPLACE FUNCTION fn_easy RETURN VARCHAR2 IS
BEGIN
RETURN 'This is a simple function';
END fn_easy;
/

------------------------------------------------------------
-Function - IN Parameter
-------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_ssn (string_in IN VARCHAR2)
RETURN BOOLEAN IS
-- validating ###-##-#### format
incorrect EXCEPTION;
delim CHAR(1);
part1 NUMBER(3,0);
part2 NUMBER(2,0);
part3 NUMBER(4,0);
BEGIN
IF LENGTH(string_in) <> 11 THEN
RAISE incorrect;
END IF;

part1 := TO_NUMBER(SUBSTR(string_in,1,3),'999');

delim := SUBSTR(string_in,4,1);
IF delim <> '-' THEN
RAISE incorrect;
END IF;

part2 := TO_NUMBER(SUBSTR(string_in,5,2),'99');

delim := SUBSTR(string_in,7,1);
IF delim <> '-' THEN
RAISE incorrect;
END IF;

part3 := TO_NUMBER(SUBSTR(string_in,8,4),'9999');

RETURN TRUE;
EXCEPTION
WHEN incorrect THEN
RETURN FALSE;
WHEN OTHERS THEN
RETURN FALSE;
END is_socsecno;
/

SET serveroutput ON

BEGIN
IF is_ssn('123-45-6789') THEN
DBMS_OUTPUT.put_line('True');
ELSE
DBMS_OUTPUT.put_line('False');
END IF;
END;
/

BEGIN
IF is_ssn('123-A5-6789') THEN
DBMS_OUTPUT.put_line('True');
ELSE
DBMS_OUTPUT.put_line('False');
END IF;
END;
/

BEGIN
IF is_ssn('123=45-6789') THEN
DBMS_OUTPUT.put_line('True');
ELSE
DBMS_OUTPUT.put_line('False');
END IF;
END;
/

BEGIN
IF is_ssn('123-A5-67890') THEN
DBMS_OUTPUT.put_line('True');
ELSE
DBMS_OUTPUT.put_line('False');
END IF;
END;
/



-----------------------------------------------------------------
--FUNCTIONS: OUT parameters
-----------------------------------------------------------------

CREATE OR REPLACE FUNCTION out_fn (outparm OUT VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
outparm := 'out param';
RETURN 'return param';
END out_fn;
/

SET serveroutput ON

DECLARE
retval VARCHAR2(20);
outval VARCHAR2(20);
BEGIN
retval := out_fn(outval);
DBMS_OUTPUT.put_line(outval);
DBMS_OUTPUT.put_line(retval);
END;
/



-----------------------------------------------------------
-- FUNCTIONS: IN OUT parameter
----------------------------------------------------------
CREATE OR REPLACE FUNCTION inout_fn (outparm IN OUT VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
outparm := 'Coming out';
RETURN 'return param';
END inout_fn;
/

SET serveroutput ON

DECLARE
retval VARCHAR2(20);
ioval VARCHAR2(20) := 'Going in';
BEGIN
DBMS_OUTPUT.put_line('In: ' || ioval);
retval := inout_fn(ioval);
DBMS_OUTPUT.put_line('Out: ' || ioval);
DBMS_OUTPUT.put_line('Return: ' || retval);
END;
/

--------------------------------------------------------------------------------
--FUNCTION OUT PARAMTER
-------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION out_fn (outparm OUT VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
outparm := 'out param';
RETURN 'return param';
END out_fn;
/

SET serveroutput ON

DECLARE
retval VARCHAR2(20);
outval VARCHAR2(20);
BEGIN
retval := out_fn(outval);
DBMS_OUTPUT.put_line(outval);
DBMS_OUTPUT.put_line(retval);
END;
/
----------------------------------------------------------------
--Function to determine if a number is even:
--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_even(num_in NUMBER) RETURN BOOLEAN IS
BEGIN
IF MOD(num_in, 2) = 0 THEN
RETURN TRUE;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END is_even;
/

-----------------------------------------------------------------------
-- Function to determine if a number is odd:
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_odd(num_in NUMBER) RETURN BOOLEAN IS
BEGIN
RETURN MOD(num_in, 2) = 1;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END is_odd;
/


---------------------------------------------------------------------
-- Sample IF Statement Function:
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION fn_price(pProd VARCHAR2)
RETURN PLS_INTEGER IS
tabPrice discounts.prodprice%TYPE;
tabDisc discounts.proddisc%TYPE;
i PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO i
FROM discounts
WHERE prodname = pProd;

IF i <> 0 THEN
SELECT prodprice, proddisc
INTO tabPrice, tabDisc
FROM discounts
WHERE prodname = pProd;

RETURN tabPrice - (tabPrice * tabDisc/100);
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END fn_price;



------------------------------------------------------------------
-- Function - time difference
------------------------------------------------------------------
CREATE OR REPLACE FUNCTION
time_diff(DATE_1 IN DATE, DATE_2 IN DATE)
RETURN NUMBER IS
NDATE_1 NUMBER;
NDATE_2 NUMBER;
NSECOND_1 NUMBER(5, 0);
NSECOND_2 NUMBER(5, 0);
BEGIN
-- Get Julian date number from
-- first date (DATE_1)
NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

-- Get Julian date number from
-- second date (DATE_2)
NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

-- Get seconds since midnight
-- from first date (DATE_1)
NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

-- Get seconds since midnight
-- from second date (DATE_2)
NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

RETURN (((NDATE_2 - NDATE_1)*86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/