Enterprise Software Solutions 

  Jump to: DW University, Employment

Oracle PL/SQL

1. To compile PL/SQL execute this: @ "FilePath\MyCode.sql" where the "MyCode.sql" file contains the script.
2. Start script with CREATE OR REPLACE PACKAGE MyCode AS
3. The first lines should begin with procedure names.
4. Example of PL/SQL script:

--'***********************************************************************
--' Comments : ORACLE stored procedure package for ETL transformations
--' Parameters : Proc pointer
--' Returns : result pointer
--' Created : Enterprise Software Solutions, Inc. [10/11/2000]
--' Modified :
--'***********************************************************************
CREATE OR REPLACE PACKAGE MyCode AS
fmain INTEGER;
result INTEGER;
STATEMENT VARCHAR2(2000);
PROCEDURE RUN_SQL(SQL_STATEMENT IN VARCHAR2);
FUNCTION GET_TOTAL(SQL_STATEMENT IN VARCHAR2) RETURN FLOAT;
PROCEDURE CREATE_DECILE(
INPUT_TABLE IN VARCHAR2,
DECILE_COLUMN IN VARCHAR2,
JOIN_COLUMN VARCHAR2,
LAST_ORDER_COLUMN VARCHAR2,
GROUP_INDICATOR IN VARCHAR2,
OUTPUT_TABLE IN VARCHAR2);
PROCEDURE RANK_BREAK_BY(
INPUT_TABLE IN VARCHAR2,
COL_TO_RANK_ON IN VARCHAR2,
COL_TO_BREAK_BY IN VARCHAR2,
NEW_RANK_COL_NAME IN VARCHAR2);
PROCEDURE RANK_BREAK_BY2(
INPUT_TABLE IN VARCHAR2,
COL_TO_RANK_ON IN VARCHAR2,
COL_TO_BREAK_BY1 IN VARCHAR2,
COL_TO_BREAK_BY2 IN VARCHAR2,
NEW_RANK_COL_NAME IN VARCHAR2);
PROCEDURE RANK(
INPUT_TABLE IN VARCHAR2,
COL_TO_RANK_ON IN VARCHAR2,
WHERE_CLAUSE IN VARCHAR2,
NEW_RANK_COL_NAME IN VARCHAR2);
PROCEDURE TEST;

END;
/
CREATE OR REPLACE PACKAGE BODY MyCode AS
PROCEDURE altersession IS
BEGIN
fmain := dbms_sql.open_cursor;
STATEMENT := 'alter session set optimizer_mode = rule';
dbms_sql.parse(fmain,STATEMENT,dbms_sql.v7);
result := dbms_sql.EXECUTE(fmain);
dbms_sql.close_cursor(fmain);
END;



--'***********************************************************************
--' Comments : Runs a SQL STATEMENT
--' Parameters : NONE
--' Returns : true if success
--' Created : Enterprise Software Solutions, Inc. [10/11/2000]
--' Modified :
--'***********************************************************************
PROCEDURE RUN_SQL(SQL_STATEMENT IN VARCHAR2) IS
c_Cursor_Handle INTEGER;
ReturnCode INTEGER;
BEGIN
c_Cursor_Handle := dbms_sql.open_cursor;
dbms_sql.parse(c_Cursor_Handle,SQL_STATEMENT,dbms_sql.v7);
ReturnCode := dbms_sql.EXECUTE(c_Cursor_Handle);
dbms_sql.close_cursor(c_Cursor_Handle);
END;

--'***********************************************************************
--' Comments : Returns 1 number for a SQL statement passed in
--' Parameters : SQL statement
--' Returns : the one value of the ReturnValue
--' Created : Enterprise Software Solutions, Inc. [10/13/2000]
--' Modified :
--'***********************************************************************
FUNCTION GET_TOTAL(SQL_STATEMENT IN VARCHAR2) RETURN FLOAT IS
c_Cursor_Handle INTEGER;
ReturnCode INTEGER;
v_Current_Value FLOAT;
--v_Join_Value FLOAT;
v_Total FLOAT;
BEGIN
c_Cursor_Handle := dbms_sql.open_cursor;
dbms_sql.parse(c_Cursor_Handle,SQL_STATEMENT,dbms_sql.v7);
dbms_sql.DEFINE_COLUMN(c_Cursor_Handle, 1, v_Current_Value);
-- dbms_sql.DEFINE_COLUMN(c_Cursor_Handle, 2, v_Join_Value);
ReturnCode := dbms_sql.EXECUTE(c_Cursor_Handle);
v_Total := 0;
LOOP
IF dbms_sql.FETCH_ROWS(c_Cursor_Handle) = 0 THEN
EXIT;
END IF;
dbms_sql.COLUMN_VALUE(c_Cursor_Handle, 1, v_Current_Value);
-- dbms_sql.COLUMN_VALUE(c_Cursor_Handle, 2, v_Join_Value);
v_Total := v_Total + v_Current_Value;
END LOOP;
dbms_sql.close_cursor(c_Cursor_Handle);
RETURN v_Total;
END;




--'***********************************************************************
--' Comments : Creates Deciles for Vantinm Alergist
--' Asumptions : 1) Both the input and output tables already exists
--' 2) Final decile col will be 2nd to last col in table
--' 3) Last Col in table is an inicator of peer decile groups
--' 4) Last Col in table is named "GROUP_INDICATOR"
--' Parameters :
--' INPUT_TABLE ==> table to make decile based on
--' DECILE_COLUMN ==> column to base deciling on
--' GROUP_INDICATOR ==> flag indicate peer decile groups
--' OUTPUT_TABLE ==> table that will have decile
--' Returns : the one value of the ReturnValue
--' Usage : MyCode.CREATE_DECILE({Above parm seperated by comma});
--' Created : Enterprise Software Solutions, Inc. [10/17/2000]
--' Modified :
--'***********************************************************************
PROCEDURE CREATE_DECILE(
INPUT_TABLE IN VARCHAR2,
DECILE_COLUMN IN VARCHAR2,
JOIN_COLUMN VARCHAR2,
LAST_ORDER_COLUMN VARCHAR2,
GROUP_INDICATOR IN VARCHAR2,
OUTPUT_TABLE IN VARCHAR2) IS
c_Cursor_Handle INTEGER;
ReturnCode INTEGER;
SQL_STATEMENT VARCHAR2(2000);
--**Declare vars as in table
v_Current_Value FLOAT;
v_Join_Value FLOAT;
v_RunningTotal FLOAT;
v_GrandTotal FLOAT;

-- to use the "putline",
-- the "serveroutput" option in SQL plus environment
-- mustbe set to "current" and "on"
-- dbms_output.PUT_LINE('Text goes here');
BEGIN

-- clean up contents of tables from before
SQL_STATEMENT := 'DELETE FROM ' || OUTPUT_TABLE
|| ' WHERE Group_Indicator = ' || '''' || GROUP_INDICATOR || '''';
MyCode.RUN_SQL(SQL_STATEMENT);
COMMIT;

--**declare cursor
SQL_STATEMENT := 'SELECT DISTINCT '
|| DECILE_COLUMN || ' AS Current_Value,
' || JOIN_COLUMN || ' AS Join_Key,
' || LAST_ORDER_COLUMN || ' AS Order_By
FROM ' || INPUT_TABLE
|| ' ORDER BY 1,3,2';
-- get the grand total for calculations
v_GrandTotal := MyCode.GET_TOTAL(SQL_STATEMENT);
-- open cursor for creating running total
c_Cursor_Handle := dbms_sql.open_cursor;
dbms_sql.parse(c_Cursor_Handle,SQL_STATEMENT,dbms_sql.v7);
dbms_sql.DEFINE_COLUMN(c_Cursor_Handle, 1, v_Current_Value);
dbms_sql.DEFINE_COLUMN(c_Cursor_Handle, 2, v_Join_Value);
ReturnCode := dbms_sql.EXECUTE(c_Cursor_Handle);

--loop through all records in distinct Rx counts table and add running total
v_RunningTotal := 0;
LOOP
IF dbms_sql.FETCH_ROWS(c_Cursor_Handle) = 0 THEN
EXIT;
END IF;
dbms_sql.COLUMN_VALUE(c_Cursor_Handle, 1, v_Current_Value);
dbms_sql.COLUMN_VALUE(c_Cursor_Handle, 2, v_Join_Value);

--add current value to running total
v_RunningTotal := v_RunningTotal + v_Current_Value;

--update the current record into the table
SQL_STATEMENT := 'INSERT INTO ' || OUTPUT_TABLE
|| ' (SELECT d.*,
10-(TRUNC(((' || v_RunningTotal/v_GrandTotal || ')
-.000000001)*10)) AS DECILE_COLUMN, '
|| '''' || GROUP_INDICATOR || '''' || ' AS Group_Indicator
FROM ' || INPUT_TABLE || ' d
WHERE ' || JOIN_COLUMN || ' = ' || v_Join_Value || ')';
MyCode.RUN_SQL(SQL_STATEMENT);
END LOOP;
dbms_sql.close_cursor(c_Cursor_Handle);
COMMIT;
END;



--'***********************************************************************
--' Comments : Creates a ranking column with a "break by"
--' The new column will be added to the end of the table
--' Asumptions :
--' Parameters :
--' INPUT_TABLE ==> table to add rank column to
--' COL_TO_RANK_ON ==> column to base ranking on
--' COL_TO_BREAK_BY ==> first column of the "for each"
--' NEW_RANK_COL_NAME ==> column to base deciling on
--' Returns : the one value of the ReturnValue
--' Usage : MyCode.RANK_BREAK_BY({Above parms seperated by comma});
--' Created : Enterprise Software Solutions, Inc. [03/15/2001]
--' Modified :
--'***********************************************************************
PROCEDURE RANK_BREAK_BY(
INPUT_TABLE IN VARCHAR2,
COL_TO_RANK_ON IN VARCHAR2,
COL_TO_BREAK_BY IN VARCHAR2,
NEW_RANK_COL_NAME IN VARCHAR2) IS

ReturnCode INTEGER;
c_Group_Cursor_Handle INTEGER;
SQL_STATEMENT VARCHAR2(2000);
v_Break_By VARCHAR2(50);
WHERE_CLAUSE VARCHAR2(50);


-- to use the "putline",
-- the "serveroutput" option in SQL plus environment
-- mustbe set to "current" and "on"
-- OR ecevute in SQL plus "set serveroutput on"
-- dbms_output.PUT_LINE('Text goes here');
BEGIN
--**add new rank column to end of table
SQL_STATEMENT := 'ALTER TABLE ' || INPUT_TABLE
|| ' ADD (' || NEW_RANK_COL_NAME || ' NUMBER )';
MyCode.RUN_SQL(SQL_STATEMENT);

--**declare cursor
SQL_STATEMENT := 'SELECT DISTINCT '
|| COL_TO_BREAK_BY || ' AS Current_Value FROM '
|| INPUT_TABLE
|| ' ORDER BY 1';
-- open cursor for breakby values
c_Group_Cursor_Handle := dbms_sql.open_cursor;
dbms_sql.parse(c_Group_Cursor_Handle,SQL_STATEMENT,dbms_sql.v7);
dbms_sql.DEFINE_COLUMN(c_Group_Cursor_Handle, 1, v_Break_By, 50);
ReturnCode := dbms_sql.EXECUTE(c_Group_Cursor_Handle);

--loop through all records in distinct "Break By" COL
LOOP
IF dbms_sql.FETCH_ROWS(c_Group_Cursor_Handle) = 0 THEN
EXIT;
END IF;
dbms_sql.COLUMN_VALUE(c_Group_Cursor_Handle, 1, v_Break_By);
--* Do the ranking for this "break-by"
WHERE_CLAUSE:= 'WHERE ' || COL_TO_BREAK_BY || ' = ' || v_Break_By;
MyCode.RANK(INPUT_TABLE, COL_TO_RANK_ON, WHERE_CLAUSE, NEW_RANK_COL_NAME);
END LOOP;
dbms_sql.close_cursor(c_Group_Cursor_Handle);
COMMIT;
END;


--'***********************************************************************
--' Comments : Creates a ranking column with a "break by"
--' The new column will be added to the end of the table
--' Asumptions :
--' Parameters :
--' INPUT_TABLE ==> table to add rank column to
--' COL_TO_RANK_ON ==> column to base ranking on
--' COL_TO_BREAK_BY1 ==> first column of the "for each"
--' COL_TO_BREAK_BY2 ==> second column of the "for each"
--' NEW_RANK_COL_NAME ==> column to base deciling on
--' Returns : the one value of the ReturnValue
--' Usage : MyCode.RANK_BREAK_BY({Above parms seperated by comma});
--' Created : Enterprise Software Solutions, Inc. [03/15/2001]
--' Modified :
--'***********************************************************************
PROCEDURE RANK_BREAK_BY2(
INPUT_TABLE IN VARCHAR2,
COL_TO_RANK_ON IN VARCHAR2,
COL_TO_BREAK_BY1 IN VARCHAR2,
COL_TO_BREAK_BY2 IN VARCHAR2,
NEW_RANK_COL_NAME IN VARCHAR2) IS

ReturnCode INTEGER;
c_Group_Cursor_Handle1 INTEGER;
c_Group_Cursor_Handle2 INTEGER;
SQL_STATEMENT VARCHAR2(2000);
v_Break_By1 VARCHAR2(50);
v_Break_By2 VARCHAR2(50);
WHERE_CLAUSE VARCHAR2(50);

-- to use the "putline",
-- the "serveroutput" option in SQL plus environment
-- mustbe set to "current" and "on"
-- OR ecevute in SQL plus "set serveroutput on"
-- dbms_output.PUT_LINE('Text goes here');
BEGIN
--**add new rank column to end of table
SQL_STATEMENT := 'ALTER TABLE ' || INPUT_TABLE
|| ' ADD (' || NEW_RANK_COL_NAME || ' NUMBER )';
MyCode.RUN_SQL(SQL_STATEMENT);

--**declare cursor
SQL_STATEMENT := 'SELECT DISTINCT '
|| COL_TO_BREAK_BY1 || ' AS Current_Value FROM '
|| INPUT_TABLE
|| ' ORDER BY 1';
-- open cursor for breakby values
c_Group_Cursor_Handle1 := dbms_sql.open_cursor;
dbms_sql.parse(c_Group_Cursor_Handle1,SQL_STATEMENT,dbms_sql.v7);
dbms_sql.DEFINE_COLUMN(c_Group_Cursor_Handle1, 1, v_Break_By1, 50);
ReturnCode := dbms_sql.EXECUTE(c_Group_Cursor_Handle1);

--loop through all records in distinct "Break By" COL
LOOP
IF dbms_sql.FETCH_ROWS(c_Group_Cursor_Handle1) = 0 THEN
EXIT;
END IF;
dbms_sql.COLUMN_VALUE(c_Group_Cursor_Handle1, 1, v_Break_By1);


--******* INNER LOOP *********
--******* INNER LOOP *********
--******* INNER LOOP *********

--**declare cursor
SQL_STATEMENT := 'SELECT DISTINCT '
|| COL_TO_BREAK_BY2 || ' AS Current_Value FROM '
|| INPUT_TABLE
|| ' WHERE ' || COL_TO_BREAK_BY1
|| ' = ' || v_Break_By1
|| ' ORDER BY 1';
-- open cursor for breakby values
c_Group_Cursor_Handle2 := dbms_sql.open_cursor;
dbms_sql.parse(c_Group_Cursor_Handle2,SQL_STATEMENT,dbms_sql.v7);
dbms_sql.DEFINE_COLUMN(c_Group_Cursor_Handle2, 1, v_Break_By2, 50);
ReturnCode := dbms_sql.EXECUTE(c_Group_Cursor_Handle2);

--loop through all records in distinct "Break By" COL
LOOP
IF dbms_sql.FETCH_ROWS(c_Group_Cursor_Handle2) = 0 THEN
EXIT;
END IF;
dbms_sql.COLUMN_VALUE(c_Group_Cursor_Handle2, 1, v_Break_By2);
--* Do the ranking for this "break-by"
WHERE_CLAUSE:= 'WHERE ' || COL_TO_BREAK_BY1 || ' = ' || v_Break_By1
|| ' AND ' || COL_TO_BREAK_BY2 || ' = ' || 
v_Break_By2;
MyCode.RANK(INPUT_TABLE, COL_TO_RANK_ON, WHERE_CLAUSE, 
NEW_RANK_COL_NAME);
END LOOP;
dbms_sql.close_cursor(c_Group_Cursor_Handle2);
END LOOP;
dbms_sql.close_cursor(c_Group_Cursor_Handle1);
COMMIT;
END;



--'***********************************************************************
--' Comments : Loads a ranking column
--' Asumptions :
--' Parameters :
--' INPUT_TABLE ==> table to add rank column to
--' COL_TO_RANK_ON ==> column to base ranking on
--' COL_TO_BREAK_BY ==> column to break the ranking on
--' BREAK_BY_FILTER ==> column value to filter for
--' NEW_RANK_COL_NAME ==> column to base deciling on
--' Returns : the one value of the ReturnValue
--' Usage : MyCode.RANK({Above parms seperated by comma});
--' Created : Enterprise Software Solutions, Inc. [03/19/2001]
--' Modified :
--'***********************************************************************
PROCEDURE RANK(
INPUT_TABLE IN VARCHAR2,
COL_TO_RANK_ON IN VARCHAR2,
WHERE_CLAUSE in VARCHAR2,
NEW_RANK_COL_NAME IN VARCHAR2) IS

ReturnCode INTEGER;
c_Detail_Cursor_Handle INTEGER;
SQL_STATEMENT VARCHAR2(2000);
v_Row_ID ROWID;
v_Rank_Based_On FLOAT;
v_RankValue INTEGER;

BEGIN

SQL_STATEMENT := 'SELECT ROWID, ' || COL_TO_RANK_ON
|| ' FROM ' || INPUT_TABLE || ' '
|| WHERE_CLAUSE || ' ORDER BY 2 DESC';
-- open cursor for updating the rank column
c_Detail_Cursor_Handle := dbms_sql.open_cursor;
dbms_sql.parse(c_Detail_Cursor_Handle,SQL_STATEMENT,dbms_sql.v7);
dbms_sql.DEFINE_COLUMN_ROWID(c_Detail_Cursor_Handle, 1, v_Row_ID);
dbms_sql.DEFINE_COLUMN(c_Detail_Cursor_Handle, 2, v_Rank_Based_On);
ReturnCode := dbms_sql.EXECUTE(c_Detail_Cursor_Handle);
--loop through all records in distinct Rx counts table and add running total
v_RankValue := 0;
LOOP
IF dbms_sql.FETCH_ROWS(c_Detail_Cursor_Handle) = 0 THEN
EXIT;
END IF;
dbms_sql.COLUMN_VALUE_ROWID(c_Detail_Cursor_Handle, 1, v_Row_ID);
dbms_sql.COLUMN_VALUE(c_Detail_Cursor_Handle, 2, v_Rank_Based_On);

--add current value to running total
v_RankValue := v_RankValue + 1;

--update the current record into the table

SQL_STATEMENT := 'UPDATE '|| INPUT_TABLE
|| ' SET ' || NEW_RANK_COL_NAME
|| ' = ' || v_RankValue
|| ' WHERE ROWID = ''' || v_Row_ID || '''';
MyCode.RUN_SQL(SQL_STATEMENT);
END LOOP;
dbms_sql.close_cursor(c_Detail_Cursor_Handle);
END;



--'***********************************************************************
--' Comments : test procedure to run a function
--'***********************************************************************
PROCEDURE TEST IS
SQL_STATEMENT VARCHAR2(2000);
BEGIN
--** remove new rank column from end of table
SQL_STATEMENT := 'DROP TABLE z9stmich.NET_SALES_TOP_CUST';
-- MyCode.RUN_SQL(SQL_STATEMENT);
SQL_STATEMENT := 'CREATE TABLE z9stmich.NET_SALES_TOP_CUST as
SELECT
PROD_FAMILY_ID, PROD_FAMILY_DESC,
CUST_ID,
PERIOD_MTH_ID, PERIOD_MON_DESC,
COT_ID, COT_DESC,
NET_SALES_YTMLY,
NET_SALES_CMTY,
NET_SALES_QTMTY,
NET_SALES_YTMTY,
NET_SALES_CMLY,
NET_SALES_QTMLY,
NET_SALES_YTMTY_COT,
NET_SALES_YTMLY_COT
FROM insdwdss.NET_SALES_TOP_CUST';
--dbms_output.PUT_LINE(SQL_STATEMENT);
-- MyCode.RUN_SQL(SQL_STATEMENT);
--** run procedure
MyCode.RANK_BREAK_BY2(
'NET_SALES_TOP_CUST',
'NET_SALES_YTMTY',
'COT_ID',
'PROD_FAMILY_ID',
'INDUSTRY_SALES_RANK');
END;

END;
/

[ Home ] [ Up ]

Send mail to Webmaster@ESScorporation.com with questions or comments about this web site.
Copyright © 2004 Enterprise Software Solutions, Inc.
Last modified: July 29, 2004