I am extremely new to PL/SQL so would not be surprised if maybe I am
tackling this in the wrong manner, also hope this is the right place
to ask on this. I have a function that I am building and part of the
function has a cursor that returns three columns of data, those being
Path Name, Module Name and percent complete of the module. A path is
made up of modules and I am wanting to figure out the summation of all
percentages per path and just return the highest and distinct
summation. I thought the best way to address this would be to plug
the values into a collection with the row id being the path name, then
ordering the collection and pulling out the first record since it
would be the highest one after order. I think I have the plugging
into the collection correct, but my understanding of how to apply
ordering to it would appeared to be flawed. I could think of very
non-eloquant ways to maybe do this via some if's in my loop but rather
not take that approach. Seems like if I could just query against the
collection as if it were a table then I could pull the data that way
as well, but my understanding of the docs and the table function would
seem to not be advanced enough. Here is the code I am working with,
hopefully word wrapping does not throw it too far out of wack, also
here is a link to the function in a txt file.
http://www.turbowizard.com/incoming/OraFunction.txt
/* *****************************************************************
*/
/* * *
*/
/* * MODULE: CERTPERCENT.SQL *
*/
/* * AUTHOR: Aaron Rouse. *
*/
/* * DATE: 05/14/2004. *
*/
/* * DESC: return percent complete for certification/GIN *
*/
/* *****************************************************************
*/
/* * *
*/
/* * USAGE: CERTPERCENT(input employee GIN, *
*/
/* * input certification name) * */
/* * *
*/
/* * RETURNS: Percentage of certification *
*/
/* * *
*/
/* *****************************************************************
*/
/* * *
*/
/* * SAMPLE #1: SELECT NO_EMP, NM_CERT, MAX(ACQUIRED_ON) *
*/
/* * AS ACQUIRED_ONDATE, CERTPERCENT(NO_EMP, NM_CERT) * */
/* * FROM SLB_LMS_CERTS_IMPORT * */
/* * GROUP BY NO_EMP, NM_CERT * */
/* * ORDER BY NO_EMP, NM_CERT; * */
/* * *
*/
/* *****************************************************************
*/
CREATE OR REPLACE FUNCTION CERTPERCENT(NO_EMP_num IN NUMBER,
NM_CERT_var IN VARCHAR)
RETURN NUMBER
IS
return_value NUMBER;
modules_in_path NUMBER;
TYPE TBL_PATH IS
VARRAY(10) OF NUMBER;
PATH_NM TBL_PATH;
PATH_PRC TBL_PATH;
-- Pull out percent complete by modules in certification
CURSOR MODULE_PERCENTS (NO_EMP_num IN NUMBER, NM_CERT_var IN
VARCHAR) IS
SELECT C.NM_PATH, C.NM_MODULE,
PERCENTCOMPLETE(C.RQD_NMB_LO, COUNT(DISTINCT D.LO_NAME),
C.NO_EMP, C.NM_CERT, C.NM_MODULE, C.NM_PATH) AS MOD_PERCENTCOMPLETE,
MODULECOUNTER(C.NM_PATH, C.NM_CERT) AS MODULE_CNT
FROM SLB_LMS_CERTS_IMPORT C LEFT OUTER JOIN SLB_LMS_LOS_IMPORT D
ON C.LO_CODE = D.LO_CODE
AND D.LO_STATUS = 'Completed - Successfully'
AND C.NO_EMP = D.NO_EMP
WHERE (LPAD(C.LO_CODE, 2) = 'WS'
AND C.NO_EMP = NO_EMP_num
AND C.NM_CERT = NM_CERT_var
AND C.HASPARENT = 0)
AND C.CERT_STATUS IN ('Acquired','In progress')
GROUP BY C.NM_CERT, C.NM_PATH, C.NM_MODULE, C.RQD_NMB_LO,
C.NO_EMP
ORDER BY C.NM_PATH, C.NM_MODULE;
--modules_percents_rec MODULE_PERCENTS%ROWTYPE;
-- Module count in path
-- CURSOR MOD_CNT_NUM (NM_PATH_var IN VARCHAR, NM_CERT_var IN
VARCHAR) IS
-- SELECT COUNT(DISTINCT NM_MODULE) AS MODULE_CNT FROM
SLB_LMS_CERTS_IMPORT WHERE NM_CERT = NM_CERT_var AND NM_PATH =
NM_PATH_var;
BEGIN
--OPEN MODULE_PERCENTS(NO_EMP_num, NM_CERT_var);
FOR i IN MODULE_PERCENTS(NO_EMP_num, NM_CERT_var) LOOP
PATH_NM(i.NM_PATH) := PATH_NM(i.NM_PATH) +
i.MOD_PERCENTCOMPLETE;
-- This does not take into account when there is more than one path
return_value := return_value + i.MOD_PERCENTCOMPLETE;
modules_in_path := i.MODULE_CNT;
END LOOP;
/*
FOR rec IN (SELECT column_value favs FROM TABLE (TBL_PATH) ORDER BY
column_value)
LOOP
DBMS_OUTPUT.put_line (rec.favs);
END LOOP;
*/
return_value := ROUND(return_value / modules_in_path);
--FETCH MODULE_PERCENTS INTO modules_percents_rec;
--return_value := MODULE_PERCENTS%ROWCOUNT;
RETURN return_value;
END CERTPERCENT;
/
Received on Sat May 15 2004 - 11:13:06 CDT
