a simple procedure to build a data block

بتاريخ: 21 يوليو 200520 سنة
hi
this is a simple procedure that shows how to build a data block on a stored procedure
-------------------------------
-- AUTHOR : TAREK FATHI BAKR
-- DATE   : 2005 05 30
-- THEME  :BUILDING A DATABLOCK BASED ON STORED PROCEDURE
------------------------------------------------------------------------------------------------------
PACKAGE HOSPITAL_ACT_pkg
AS
  TYPE ACTIVITYrec IS RECORD (
     My_choice      VARCHAR2(50),
     No_of_visits   NUMBER,
     Cons           NUMBER,
     Owen           NUMBER,
     Lab            NUMBER,
     Xr             NUMBER,
     Med            NUMBER,
     Oth            NUMBER,
     Total          NUMBER
  );

--- Define the Ref Cursor
  TYPE ACTIVITYcur IS REF CURSOR
     RETURN ACTIVITYrec;

--- Defines the Table of Records EMP
  TYPE ACTIVITYtab IS TABLE OF ACTIVITYrec
     INDEX BY BINARY_INTEGER;

--- Defines the procedures used for querying records
  PROCEDURE ACTquery_refcur (Block_data IN OUT ACTIVITYcur, P_USERchoice IN VARCHAR2);

  PROCEDURE ACTquery (Block_data IN OUT ACTIVITYtab, P_USERchoice IN VARCHAR2);
/************************************************************************************/
/* THE FOLOWING PROCEDURES AND DATA TYPE ARE USED TO BUILD THE BLOCK CASH_CRDT*/
/************************************************************************************/   
  TYPE ACTIVITY_CASH_CRDTrec IS RECORD (
     My_choice      VARCHAR2(50),
     No_of_visits   NUMBER,
     CASH           NUMBER,
     CREDIT         NUMBER,
     TOTAL          NUMBER
  );
  TYPE ACTIVITY_CASH_CRDTcur IS REF CURSOR
   RETURN ACTIVITY_CASH_CRDTrec;
   
  TYPE ACTIVITY_CASH_CRDTtab IS TABLE OF ACTIVITY_CASH_CRDTrec
   INDEX BY BINARY_INTEGER;

 PROCEDURE CASH_CRDTrefcur (MY_BLOCK IN OUT  ACTIVITY_CASH_CRDTcur,p_USERchoice IN VARCHAR2);
 
 PROCEDURE CASH_CRDTquery  (MY_BLOCK IN OUT  ACTIVITY_CASH_CRDTtab ,p_USERchoice IN VARCHAR2);
   
   	

END;
------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
-- CREATED AND COMMENTED BY  : TAREK FATHI 
-- DATE          : 2005 05 30
--LASTUPDATE         : 2005 05 30  	
-- SUBJECT                    : THE CURRENT STORE PROCEDURE TELLS THE DATA BLOCK HOW TO RETIVE A SPECIFIC DATA
-------------------------------------------------------------------------------------------------------------------------------

PACKAGE BODY Hospital_act_pkg
AS
-- QUERY USING REF CURSOR
  PROCEDURE Actquery_refcur (
     Block_data     IN OUT   Activitycur,
     P_userchoice   IN       VARCHAR2
  )
  IS
  BEGIN
     OPEN Block_data
      FOR
         SELECT   to_char(DECODE
                     (TO_CHAR(Hospital_pkg.F_return_choise_value (:Parameter.P_deptno)),
                      1, I.Organization_no,
                      2, I.Doctor_id,
                      3, I.Clinical_entity_no,
                      4, Cs.Specialty_no,
                      5, Ce.Organization_no,
                      I.Staff_id
                     )) My_choice,
                  COUNT (DISTINCT I.Patient_file_no || '-' || I.Episode_no
                        ) No_of_visits,
                  SUM (DECODE (Cs.Specialty_no,
                               I.Specialty_no, 0,
                               DECODE (Specialty_type,
                                       'CNS', (Qty-REFUNDED_QTY) * Unit_tariff_price,
                                       0
                                      )
                              )
                      ) Cons,
                  SUM (DECODE (Cs.Specialty_no,
                               I.Specialty_no, (Qty-REFUNDED_QTY) * Unit_tariff_price,
                               0
                              )
                      ) Owen,
                  SUM (DECODE (Cs.Specialty_no,
                               I.Specialty_no, 0,
                               DECODE (Specialty_type,
                                       'LAB', (Qty-REFUNDED_QTY) * Unit_tariff_price,
                                       0
                                      )
                              )
                      ) Lab,
                  SUM (DECODE (Cs.Specialty_no,
                               I.Specialty_no, 0,
                               DECODE (Specialty_type,
                                       'XR', (Qty-REFUNDED_QTY) * Unit_tariff_price,
                                       0
                                      )
                              )
                      ) Xr,
                  SUM (DECODE (Cs.Specialty_no,
                               I.Specialty_no, 0,
                               DECODE (Specialty_type,
                                       'MED', (Qty-REFUNDED_QTY) * Unit_tariff_price,
                                       0
                                      )
                              )
                      ) Med,
                  SUM (DECODE (Cs.Specialty_no,
                               I.Specialty_no, 0,
                               DECODE (Specialty_type,
                                       'CNS', 0,
                                       'LAB', 0,
                                       'XR', 0,
                                       'MED', 0,
                                       (Qty-REFUNDED_QTY) * Unit_tariff_price
                                      )
                              )
                      ) Oth,
                  SUM ((Qty-REFUNDED_QTY) * Unit_tariff_price) Total
             FROM Rc_patient_invoices I,
                  Rc_patient_invoice_details D,
                  Rc_clinical_specialities S,
                  Rc_clinical_staff Cs,
                  Rc_clinical_entities Ce
            WHERE I.Patient_invoice_no = D.Patient_invoice_no
              AND I.Organization_no = D.Organization_no
              AND I.Specialty_no = S.Specialty_no
              AND I.Doctor_id = Cs.Staff_id
              AND I.Clinical_entity_no = Ce.Clinical_entity_no
              AND (  TRUNC( Patient_invoice_date) >= :Parameter.P_from
                   OR :Parameter.P_from IS NULL
                  )
              AND (   TRUNC(Patient_invoice_date) <= :Parameter.P_to
                   OR :Parameter.P_to IS NULL
                  )
              AND Attendance_type = NVL ('', Attendance_type)
              AND Patient_invoice_type = NVL ('', Patient_invoice_type)
         GROUP BY DECODE
                     (TO_CHAR(Hospital_pkg.F_return_choise_value (:Parameter.P_deptno)),
                      1, I.Organization_no,
                      2, I.Doctor_id,
                      3, I.Clinical_entity_no,
                      4, Cs.Specialty_no,
                      5, Ce.Organization_no,
                      I.Staff_id
                     )
         ORDER BY 9 DESC;
  END;

/******************************************************************************/
/* QUERY USING TABLE OF RECORDS : THIS IS THE QUERY THAT THE BLOCK IS BASED ON*/
/******************************************************************************/
  PROCEDURE Actquery (Block_data IN OUT Activitytab, P_userchoice IN VARCHAR2)
  IS
     I   NUMBER;

     CURSOR Empselect
     IS
        SELECT   to_char(DECODE
                    (TO_CHAR(Hospital_pkg.F_return_choise_value (:Parameter.P_deptno)),
                     1, I.Organization_no,
                     2, I.Doctor_id,
                     3, I.Clinical_entity_no,
                     4, Cs.Specialty_no,
                     5, Ce.Organization_no,
                     I.Staff_id
                    )) My_choice,
                 COUNT (DISTINCT I.Patient_file_no || '-' || I.Episode_no
                       ) No_of_visits,
                 SUM (DECODE (Cs.Specialty_no,
                              I.Specialty_no, 0,
                              DECODE (Specialty_type,
                                      'CNS', (Qty-REFUNDED_QTY) * Unit_tariff_price,
                                      0
                                     )
                             )
                     ) Cons,
                 SUM (DECODE (Cs.Specialty_no,
                              I.Specialty_no, (Qty-REFUNDED_QTY) * Unit_tariff_price,
                              0
                             )
                     ) Owen,
                 SUM (DECODE (Cs.Specialty_no,
                              I.Specialty_no, 0,
                              DECODE (Specialty_type,
                                      'LAB', (Qty-REFUNDED_QTY) * Unit_tariff_price,
                                      0
                                     )
                             )
                     ) Lab,
                 SUM (DECODE (Cs.Specialty_no,
                              I.Specialty_no, 0,
                              DECODE (Specialty_type,
                                      'XR', (Qty-REFUNDED_QTY) * Unit_tariff_price,
                                      0
                                     )
                             )
                     ) Xr,
                 SUM (DECODE (Cs.Specialty_no,
                              I.Specialty_no, 0,
                              DECODE (Specialty_type,
                                      'MED', (Qty-REFUNDED_QTY) * Unit_tariff_price,
                                      0
                                     )
                             )
                     ) Med,
                 SUM (DECODE (Cs.Specialty_no,
                              I.Specialty_no, 0,
                              DECODE (Specialty_type,
                                      'CNS', 0,
                                      'LAB', 0,
                                      'XR', 0,
                                      'MED', 0,
                                      (Qty-REFUNDED_QTY) * Unit_tariff_price
                                     )
                             )
                     ) Oth,
                 SUM ((Qty-REFUNDED_QTY) * Unit_tariff_price) Total
            FROM Rc_patient_invoices I,
                 Rc_patient_invoice_details D,
                 Rc_clinical_specialities S,
                 Rc_clinical_staff Cs,
                 Rc_clinical_entities Ce
           WHERE I.Patient_invoice_no = D.Patient_invoice_no
             AND I.Organization_no = D.Organization_no
             AND I.Specialty_no = S.Specialty_no
             AND I.Doctor_id = Cs.Staff_id
             AND I.Clinical_entity_no = Ce.Clinical_entity_no
             AND TRUNC( Patient_invoice_date) between nvl(:Parameter.P_from,TRUNC( Patient_invoice_date)) and nvl(:Parameter.P_to,sysdate)
--              AND (   TRUNC(Patient_invoice_date) >= :Parameter.P_from
--                   OR :Parameter.P_from IS NULL
--                  )
--              AND (   TRUNC(Patient_invoice_date) <= :Parameter.P_to
--                   OR :Parameter.P_to IS NULL
--                  )
             AND Attendance_type = NVL ('', Attendance_type)
             AND Patient_invoice_type = NVL ('', Patient_invoice_type)
        GROUP BY DECODE
                     (TO_CHAR(Hospital_pkg.F_return_choise_value (:Parameter.P_deptno)),
                      1, I.Organization_no,
                      2, I.Doctor_id,
                      3, I.Clinical_entity_no,
                      4, Cs.Specialty_no,
                      5, Ce.Organization_no,
                      I.Staff_id
                     )
        ORDER BY 9 DESC;
  BEGIN
     OPEN Empselect;

     I := 1;

     LOOP
        FETCH Empselect
         INTO Block_data (I).My_choice, Block_data (I).No_of_visits,
              Block_data (I).Cons, Block_data (I).Owen, Block_data (I).Lab,
              Block_data (I).Xr, Block_data (I).Med, Block_data (I).Oth,
              Block_data (I).Total;

        EXIT WHEN Empselect%NOTFOUND;
        I := I + 1;
     END LOOP;
  END Actquery;

/***********************************************************************/
/* QURING CASH_CREDIT DATABLOCK USING THE FOLLOWING TABLE OF RECORDS*/
/***********************************************************************/
  PROCEDURE Cash_crdtrefcur (
     My_block       IN OUT   Activity_cash_crdtcur,
     P_userchoice   IN       VARCHAR2
  )
  IS
  BEGIN
     OPEN My_block
      FOR
         SELECT   to_char(DECODE
                     (Hospital_pkg.F_return_choise_value (:Parameter.P_deptno),
                      1, I.Organization_no,
                      2, I.Doctor_id,
                      3, I.Clinical_entity_no,
                      4, Cs.Specialty_no,
                      5, Ce.Organization_no,
                      I.Staff_id
                     )) My_choice,
                  COUNT (DISTINCT I.Patient_file_no || '-' || I.Episode_no
                        ) No_of_visits,
                  SUM (DECODE (Payment_type, '0', 0, Amount)) Cash,
                  SUM (DECODE (Payment_type, '0', Amount, 0)) Credit,
                  SUM (Amount) Total
             FROM Rc_patient_invoices I,
                  Rc_clinical_specialities S,
                  Rc_clinical_staff Cs,
                  Rc_clinical_entities Ce,
                  Rc_patient_invoice_payments P
            WHERE I.Specialty_no = S.Specialty_no
              AND I.Doctor_id = Cs.Staff_id
              AND I.Clinical_entity_no = Ce.Clinical_entity_no
              AND I.Patient_invoice_no = P.Patient_invoice_no
              AND I.Organization_no = P.Organization_no
              AND (  TRUNC( Patient_invoice_date) >= :Parameter.P_from
                   OR :Parameter.P_from IS NULL
                  )
              AND (   TRUNC(Patient_invoice_date) <= :Parameter.P_to
                   OR :Parameter.P_to IS NULL
                  )
              AND Attendance_type = NVL ('', Attendance_type)
         GROUP BY DECODE
                     (Hospital_pkg.F_return_choise_value (:Parameter.P_deptno),
                      1, I.Organization_no,
                      2, I.Doctor_id,
                      3, I.Clinical_entity_no,
                      4, Cs.Specialty_no,
                      5, Ce.Organization_no,
                      I.Staff_id
                     )
         ORDER BY 5 DESC;
  END Cash_crdtrefcur;

------------------------------------------------------------------------------------------------
  PROCEDURE Cash_crdtquery (
     My_block       IN OUT   Activity_cash_crdttab,
     P_userchoice   IN       VARCHAR2
  )
  IS
     I   NUMBER;

     CURSOR Cash_crdt_cur
     IS
        SELECT   to_char(DECODE
                    (Hospital_pkg.F_return_choise_value (:Parameter.P_deptno),
                     1, I.Organization_no,
                     2, I.Doctor_id,
                     3, I.Clinical_entity_no,
                     4, Cs.Specialty_no,
                     5, Ce.Organization_no,
                     I.Staff_id
                    )) My_choice,
                 COUNT (DISTINCT I.Patient_file_no || '-' || I.Episode_no
                       ) No_of_visits,
                 SUM (DECODE (Payment_type, '0', 0, Amount)) Cash,
                 SUM (DECODE (Payment_type, '0', Amount, 0)) Credit,
                 SUM (Amount) Total
            FROM Rc_patient_invoices I,
                 Rc_clinical_specialities S,
                 Rc_clinical_staff Cs,
                 Rc_clinical_entities Ce,
                 Rc_patient_invoice_payments P
           WHERE I.Specialty_no = S.Specialty_no
             AND I.Doctor_id = Cs.Staff_id
             AND I.Clinical_entity_no = Ce.Clinical_entity_no
             AND I.Patient_invoice_no = P.Patient_invoice_no
             AND I.Organization_no = P.Organization_no
             AND TRUNC( Patient_invoice_date) between nvl(:Parameter.P_from,TRUNC( Patient_invoice_date)) and nvl(:Parameter.P_to,sysdate)
--              AND (  TRUNC( Patient_invoice_date) >= :Parameter.P_from
--                   OR :Parameter.P_from IS NULL
--                  )
--              AND (  TRUNC( Patient_invoice_date) <= :Parameter.P_to
--                   OR :Parameter.P_to IS NULL
--                  )
             AND Attendance_type = NVL ('', Attendance_type)
        GROUP BY DECODE
                     (Hospital_pkg.F_return_choise_value (:Parameter.P_deptno),
                      1, I.Organization_no,
                      2, I.Doctor_id,
                      3, I.Clinical_entity_no,
                      4, Cs.Specialty_no,
                      5, Ce.Organization_no,
                      I.Staff_id
                     )
        ORDER BY 5 DESC;
  BEGIN
     OPEN Cash_crdt_cur;

     I := 1;

     LOOP
        FETCH Cash_crdt_cur
         INTO My_block (I).My_choice, My_block (I).No_of_visits,
              My_block (I).Cash, My_block (I).Credit, My_block (I).Total;

        EXIT WHEN Cash_crdt_cur%NOTFOUND;
        I := I + 1;
     END LOOP;
  END Cash_crdtquery;
-------------------------------------------------------------------------------------------------------------------------------
END;
-------------------------------------------------------------------------------------------------------------------------------
ref_cursor.sql
اقتباس