Creating a Stored Procedure

From: Gigi Lipori <pflugg_at_bellsouth.net>
Date: Mon, 23 Sep 2002 14:57:07 -0400
Message-ID: <3d8f62eb$1_1_at_corp-news.newsgroups.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 Mon Sep 23 2002 - 20:57:07 CEST