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:
- Retrieve the patient name for a give Ordering System (OS) and MRN
- Save a given patient name, ordering system and MRN to the database
- Assume all parameters are valid
- 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!