Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle
Live SQL.
1. Write an anonymous block to retrieve the doctor’s ID and name
which in charge of certain patient. Allow the user to enter the
patient’s ID.
SET SERVEROUTPUT ON;
accept vdocid prompt 'Please enter doctor id:'
declare
vdocname doctor.doc_name%type;
vdocid number := &vdocid;
BEGIN
Select doc_id, doc_name
into vdocid, vdocname
from doctor
where doc_id = vdocid;
dbms_output.put_line ('Doctor name is :'||vdocname);
dbms_output.put_line ('Doctor id is :'||vdocid);
END;
output:
2. Write an anonymous PL/SQL block that will insert a new doctor into
the DOCTOR table. Verify that insert has been done successfully by
issuing a select * from doctor.
DECLARE
V_DOC_ID NUMBER(3):= 666;
V_DOC_NAME VARCHAR2(9) := 'Saifudin';
V_DATEHIRED DATE := Sysdate;
V_SALPERMON NUMBER(12):= 15000;
V_AREA VARCHAR2(20):= 'Neurology';
V_SUPERVISOR_ID NUMBER(3):= 101;
V_CHGPERAPPT NUMBER(3):= 90;
V_ANNUAL_BONUS NUMBER(5):=5000;
BEGIN
INSERT INTO doctor
(doc_id,doc_name,datehired,salpermon,area,supervisor_id,chgperappt,annual_bonus)
VALUES
(V_DOC_ID,V_DOC_NAME,V_DATEHIRED,V_SALPERMON,V_AREA,V_SUPERVISOR_ID,V_
CHGPERAPPT,V_ANNUAL_BONUS);
END;
Select * from doctor;
3. Write an anonymous PL/SQL block that will update the salary of all
doctors in the Pediatrics area by 1000 (Note: Current salary + 1000).
Verify that the salary has been updated by issuing a select * from
doctor where area = ‘Pediatrics’. You may have to run the select
statement twice to check the data before and after the update.
SET SERVEROUTPUT ON
DECLARE
SALPERMON NUMBER(12);
AREA VARCHAR2(20);
BEGIN
UPDATE doctor
SET salpermon = (salpermon + 1000)
WHERE AREA ='Pediatrics';
dbms_output.put_line ('Value updated');
END;
/
SELECT * FROM doctor where area = 'Pediatrics';
Before update:
After update:
4. Write an anonymous PL/SQL block that will delete all patients for
doctors that works in the Family Practice area. Verify that patients
have been deleted accordingly. Then do a select * from patients
where doc_id = (select doc_id from doctor where area = ‘Family
Practice’. You should get no rows.
SET SERVEROUTPUT ON
BEGIN
DELETE FROM PATIENT
WHERE DOC_ID IN
(SELECT DOC_ID
FROM DOCTOR
WHERE AREA='Family Practice');
DBMS_OUTPUT.PUT_LINE('Record is Deleted');
END;
/
Before delete:
After delete:
