2021 – RDBMS Data API

This post introduces the PLSQL API for the RDBMS version of the simple patient tracking system. First up, here is a reminder of the data.

INSERT INTO patient ( patient_id, patient_name ) VALUES(1,'Patient 1');
INSERT INTO patient_mrn ( patient_id, ordering_system, patient_mrn ) VALUES(1,1,'MRN123');
INSERT INTO patient_mrn ( patient_id, ordering_system, patient_mrn ) VALUES(2,1,'MRN456');

INSERT INTO patient ( patient_id, patient_name )VALUES(2,'Patient 2');
INSERT INTO patient_mrn ( patient_id, ordering_system, patient_mrn ) VALUES(1,2,'MRN99');

I’ll create a sequence generator to ensure patient id values will be unique.

CREATE SEQUENCE patient_seq START WITH 10;

The simple API requirements at this point are:

  1. Retrieve the patient name for a give Ordering System (OS) and MRN
  2. Save a given patient name, ordering system and MRN to the database
  3. Assume all parameters are valid
  4. Don’t report any errors

And here is the PLSQL package header for the API.

CREATE OR REPLACE PACKAGE patient_pkg AS

-- return the patient name associated with the
-- given OS and MRN FUNCTION get_pname ( p_os NUMBER, p_mrn VARCHAR2 ) RETURN VARCHAR2;
-- save the patient name, ordering system and mrn to
-- the database PROCEDURE add_patient ( p_name VARCHAR2, p_os NUMBER, p_mrn VARCHAR2 ); END;

And the package body.

CREATE OR REPLACE PACKAGE BODY patient_pkg AS

  /*-----------------------------------------------------------*/
  FUNCTION get_pname ( p_os NUMBER,
                       p_mrn VARCHAR2 )
     RETURN VARCHAR2 IS
  /*-----------------------------------------------------------*/
    v_ret_val VARCHAR2(30);
  BEGIN
-- use the OS and MRN to retrieve the patient ID
-- and then use a subquery to get the patient name SELECT ( SELECT patient_name FROM patient WHERE patient_id = pmrn.patient_id ) INTO v_ret_val FROM patient_mrn pmrn WHERE ordering_system = p_os AND patient_mrn = p_mrn; RETURN(v_ret_val); EXCEPTION WHEN OTHERS THEN RETURN(NULL); END; /*-----------------------------------------------------------*/ PROCEDURE add_patient ( p_name VARCHAR2, p_os NUMBER, p_mrn VARCHAR2 ) AS /*-----------------------------------------------------------*/ BEGIN
-- insert the patient record using the sequence
-- generator for the patient id INSERT INTO patient(patient_id, patient_name) VALUES(patient_seq.nextval, p_name); -- insert the os and mrn using the patient id
-- from the sequence generator
INSERT INTO patient_mrn(patient_id, ordering_system, patient_mrn) VALUES(patient_seq.currval, p_os, p_mrn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END; END;

And now for some testing.

First query the existing data.
SELECT *
FROM patient p,
patient_mrn pmrn
WHERE p.patient_id = pmrn.patient_id;

PATIENT_ID PATIENT_NAME ORDERING_SYSTEM PATIENT_ID PATIENT_MRN
1 Patient 1 1 1 MRN123
1 Patient 1 2 1 MRN456
2 Patient 2 1 2 MRN99
And test retrieval via the API. First for an OS and MRN that exist.
BEGIN
DBMS_OUTPUT.PUT_LINE('Patient = ' || patient_pkg.get_pname(1,'MRN123'));
END;
Patient = Patient 1
And one retrieval of an OS and MRN that does not exist.
BEGIN
DBMS_OUTPUT.PUT_LINE(patient_pkg.get_pname(33,'MRNBOB'));
END;
Does not return anything. No error. Nothing. That is what the requirement was after all.
Now to add a patient
BEGIN
patient_pkg.add_patient('Patient 44',2,'MRN444');
END;
And query it.
SELECT *
FROM patient p,
patient_mrn pmrn
WHERE p.patient_id = pmrn.patient_id
AND patient_name LIKE '%44%'
PATIENT_ID PATIENT_NAME ORDERING_SYSTEM PATIENT_ID PATIENT_MRN
10 Patient 44 2 10 MRN444

And add it again…

And query it.
PATIENT_ID PATIENT_NAME ORDERING_SYSTEM PATIENT_ID PATIENT_MRN
10 Patient 44 2 10 MRN444
10 Patient 44 2 10 MRN444

Remember the requirement specified no validation! Lets ensure we meet that
requirement by trying to add a patient name that is too long.

BEGIN
patient_pkg.add_patient('Patient Long Name, way too long, way way too long',2,'MRN345');
END;

And try to retrieve it.
SELECT *
FROM patient p,
patient_mrn pmrn
WHERE p.patient_id = pmrn.patient_id
AND patient_name LIKE '%Long%'
No Data Found

The requirements have been met!

Leave a Comment