PL/SQL continued in week 5 with some leaning towards integrating small PL/SQL programs into our Forms applications.
Procedures, Functions and triggers have now been added to our repertoire. An example of a simple function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE OR REPLACE FUNCTION patient_age (Current_Patient PATIENT_DETAILS.Pname%TYPE) RETURN NUMBER IS PatientDOB DATE; CurrentAge NUMBER; BEGIN SELECT PDOB INTO PatientDOB FROM Patient_Details WHERE PName = Current_Patient; CurrentAge := TRUNC((SYSDATE - PatientDOB)/365); RETURN CurrentAge; END; |
The query for running/viewing/debugging such a store procedure would be:
1 2 3 4 5 6 7 | SELECT PName, patient_age(PName) AS Agefrom Patient_Details; SELECT object_name FROM user_objects WHERE object_type='FUNCTION'; SELECT * FROM user_source WHERE name='PATIENT_AGE'; SELECT * FROM user_errors; |
David Taniar made a goof portion of source code available on moodle for student, this will assist greatly.
The component that we have covered so far are not particularly complicated. However, when implementing the number of processes we need to remember is beginning to grow. Some practical revision of work done in the tutorials will be needed.
This weeks tutorial involved the implementation of a naive library program. For me, implementing the stored procedures that we had just learnt was the easy part. Remembering how to create a button to interact with a LOV object proved more illusive. I will endevour to find some time whilst at uni to do some revision of the LOV and button objects!