Re: STored Procedure

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Tue, 24 Sep 2002 09:29:47 +0400
Message-ID: <amotbc$k84$1_at_babylon.agtel.net>

Something like this should do (not verified, thus may contain errors):

create or replace package my_pkg
as

CURSOR enc_cur (patient_id NUMBER) IS
 SELECT
   TO_CHAR(ENCOUNTERS.START_DATE, 'yyyy-mm-dd') AS ENC_DATE,    ENCOUNTERS.PAT_ID AS PAT_ID,
   TO_CHAR(MAX(PATIENT_LABS.DATE_OF_LAB), 'yyyy-mm-dd') AS DATE_OF_LAB,

   MAX(PATIENT_LABS.WBC) AS WBC,
   MAX(PATIENT_LABS.RBC) AS RBC,
   MAX(PATIENT_LABS.HGB) AS HGB,
   MAX(PATIENT_LABS.HCT) AS HCT,
   MAX(PATIENT_LABS.PT) AS PT,
   MAX(PATIENT_LABS.INR) AS INR,
   MAX(PATIENT_LABS.PLATELETS) AS PLATELETS,
   MAX(PATIENT_LABS.NA) AS NA,
   MAX(PATIENT_LABS.K) AS K,
   MAX(PATIENT_LABS.CL) AS CL,
   MAX(PATIENT_LABS.CO2) AS CO2,
   MAX(PATIENT_LABS.BUN) AS BUN,
   MAX(PATIENT_LABS.CREAT) AS CREAT,
   MAX(PATIENT_LABS.GLUCOSE) AS GLUCOSE,
   MAX(PATIENT_LABS.ALB) AS ALB,
   MAX(PATIENT_LABS.BILT) AS BILT,
   MAX(PATIENT_LABS.BILD) AS BILD,
   MAX(PATIENT_LABS.ALK_PHOS) AS ALK_PHOS,
   MAX(PATIENT_LABS.LDH) AS LDH,
   MAX(PATIENT_LABS.AST) AS AST,
   MAX(PATIENT_LABS.ALT) AS ALT,
   MAX(PATIENT_LABS.AFP) AS AFP,
   MAX(PATIENT_LABS.CALCIUM) AS CALCIUM,
   MAX(PATIENT_LABS.PHOSPHORUS) AS PHOSPHORUS,
   MAX(PATIENT_LABS.MG) AS MG,
   MAX(PATIENT_LABS.TOTAL_PROTEIN) AS TOTAL_PROTEIN,
   MAX(PATIENT_LABS.GAMMA_GLOBULIN) AS GAMMA_GLOBULIN,
   MAX(PATIENT_LABS.URIC_ACID) AS URIC_ACID,
   MAX(PATIENT_LABS.PTT) AS PTT,
   MAX(PATIENT_LABS.CHO) AS CHO,
   MAX(PATIENT_LABS.BP_DIASTOLIC) AS BP_DIASTOLIC,
   MAX(PATIENT_LABS.BP_SYSTOLIC) AS BP_SYSTOLIC,
   MAX(PATIENT_LABS.HEART_RATE) AS HEART_RATE,
   MAX(PATIENT_LABS.WEIGHT) AS WEIGHT,
   MAX(PATIENT_LABS.TEMPERATURE ) AS TEMPERATURE

 FROM
   PATIENT_LABS,
   ENCOUNTERS,
   ENCOUNTER_LABS
 WHERE
       PATIENT_LABS.PAT_ID = ENCOUNTERS.PAT_ID    AND ENCOUNTER_LABS.ENCOUNTER_ID (+) = ENCOUNTERS.ENCOUNTER_ID

   AND PATIENT_LABS.PAT_ID = patient_id                             -- < HERE'S WERE WE PARAMETERIZE

   AND (TO_CHAR(PATIENT_LABS.DATE_OF_LAB, 'yyyy-mm-dd') =  TO_CHAR(ENCOUNTERS.START_DATE,'yyyy-mm-dd')

            OR PATIENT_LABS.DATE_OF_LAB = ENCOUNTER_LABS.DATE_OF_LAB)  GROUP BY
   ENCOUNTERS.START_DATE,
   ENCOUNTERS.PAT_ID; function get_encounter( p_patient_id in number ) return enc_cur%rowtype;

end;
/

create or replace package body my_pkg
as

function get_encounter( p_patient_id in number ) return enc_cur%rowtype is
  l_row enc_cur%rowtype;
begin
 OPEN enc_cur ( p_patient_id );
 FETCH enc_cur INTO l_row;
 CLOSE enc_cur;
 return l_row;
end;
/

Then in your application you call my_pkg.get_encounter(:pat_id) and it will return fetched row. You may vary the way you access the cursor as needed. Also note, that cursor actually has only one parameter - patient ID - because I didn't find any place in your query where encounters.start_date may be specified as parameter, query seems to depend solely on patient ID... There are also other ways to do the same, but this one seems like natural answer to your question. :)

And if you need recordset (that is, cursor) instead of row, you can access enc_cur cursor directly (open, fetch) - it IS the recordset you're after.

Corrections and additions welcome.

--
Vladimir Zakharychev (bob_at_dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Gigi Lipori" <pflugg_at_bellsouth.net> wrote in message
news:EiOj9.5019$J91.418384_at_e3500-atl1.usenetserver.com...


> I have the SQL listed below. I want to turn it into a stored procedure where


> I pass it ENCOUNTERS.PAT_ID and ENCOUNTERS.START_DATE and then get back the

> appropriate recordset (should only be one record). How do I do that? I am

> admittedly an oracle newbie..

>

> SELECT

>   TO_CHAR(ENCOUNTERS.START_DATE, 'yyyy-mm-dd') AS ENC_DATE,

>   ENCOUNTERS.PAT_ID AS PAT_ID,

>   TO_CHAR(MAX(PATIENT_LABS.DATE_OF_LAB), 'yyyy-mm-dd') AS DATE_OF_LAB,

>   MAX(PATIENT_LABS.WBC) AS WBC,

>   MAX(PATIENT_LABS.RBC) AS RBC,

>   MAX(PATIENT_LABS.HGB) AS HGB,

>   MAX(PATIENT_LABS.HCT) AS HCT,

>   MAX(PATIENT_LABS.PT) AS PT,

>   MAX(PATIENT_LABS.INR) AS INR,

>   MAX(PATIENT_LABS.PLATELETS) AS PLATELETS,

>   MAX(PATIENT_LABS.NA) AS NA,

>   MAX(PATIENT_LABS.K) AS K,

>   MAX(PATIENT_LABS.CL) AS CL,

>   MAX(PATIENT_LABS.CO2) AS CO2,

>   MAX(PATIENT_LABS.BUN) AS BUN,

>   MAX(PATIENT_LABS.CREAT) AS CREAT,

>   MAX(PATIENT_LABS.GLUCOSE) AS GLUCOSE,

>   MAX(PATIENT_LABS.ALB) AS ALB,

>   MAX(PATIENT_LABS.BILT) AS BILT,

>   MAX(PATIENT_LABS.BILD) AS BILD,

>   MAX (PATIENT_LABS.ALK_PHOS) AS ALK_PHOS,

>   MAX(PATIENT_LABS.LDH) AS LDH,

>   MAX(PATIENT_LABS.AST) AS AST,

>   MAX(PATIENT_LABS.ALT) AS ALT,

>   MAX(PATIENT_LABS.AFP) AS AFP,

>   MAX(PATIENT_LABS.CALCIUM) AS CALCIUM,

>   MAX(PATIENT_LABS.PHOSPHORUS) AS PHOSPHORUS,

>   MAX(PATIENT_LABS.MG) AS MG,

>   MAX(PATIENT_LABS.TOTAL_PROTEIN) AS TOTAL_PROTEIN,

>   MAX(PATIENT_LABS.GAMMA_GLOBULIN) AS GAMMA_GLOBULIN,

>   MAX(PATIENT_LABS.URIC_ACID) AS URIC_ACID,

>   MAX(PATIENT_LABS.PTT) AS PTT,

>   MAX(PATIENT_LABS.CHO) AS CHO,

>   MAX(PATIENT_LABS.BP_DIASTOLIC) AS BP_DIASTOLIC,

>   MAX(PATIENT_LABS.BP_SYSTOLIC) AS BP_SYSTOLIC,

>   MAX(PATIENT_LABS.HEART_RATE) AS HEART_RATE,

>   MAX(PATIENT_LABS.WEIGHT) AS WEIGHT,

>   MAX(PATIENT_LABS.TEMPERATURE ) AS TEMPERATURE

> FROM

>   PATIENT_LABS,

>   ENCOUNTERS,

>   ENCOUNTER_LABS

> WHERE

>       PATIENT_LABS.PAT_ID = ENCOUNTERS.PAT_ID

>   AND ENCOUNTER_LABS.ENCOUNTER_ID (+) = ENCOUNTERS.ENCOUNTER_ID

>   AND PATIENT_LABS.PAT_ID = 1568

>   AND (TO_CHAR(PATIENT_LABS.DATE_OF_LAB, 'yyyy-mm-dd') =

> TO_CHAR(ENCOUNTERS.START_DATE,'yyyy-mm-dd')

>            OR PATIENT_LABS.DATE_OF_LAB = ENCOUNTER_LABS.DATE_OF_LAB)

> GROUP BY

>   ENCOUNTERS.START_DATE,

>   ENCOUNTERS.PAT_ID.

>

>

>

>

>

>

>

Received on Tue Sep 24 2002 - 07:29:47 CEST