CODEWITHSUNDEEP
sqloracle-databasejoinplsql
luffy
luffy

Reputation: 51

Join table in PL/SQL

Hi i would like to ask i get an error when i run this code , can i know how to fix it, i want to know the doctor for this patient when i enter the patient id, thank you. below is my code

DECLARE
  patientid(3)  := &pt_id;
  dname     doc_name%type;
BEGIN
  SELECT doc_name
    INTO dname
    FROM doctor
    JOIN patient
      ON doctor.doc_id = patient.doc_id
   WHERE pt_id = patientid;

  DBMS_OUTPUT.PUT_LINE('He/She is the patient of Dr.' || dname);
END;

Upvotes: 0

Views: 345

Answers (1)

Littlefoot
Littlefoot

Reputation: 143023

What is patient(3) supposed to be? Datatype is missing!

Though, consider something like this:

SQL> set serveroutput on
SQL> declare
  2    -- No         : patientid(3):=&pt_id;
  3    -- Better     : patientid varchar2(3) := &pt_id;  -- is it VARCHAR2? or NUMBER? Who knows ...
  4    -- Even better:
  5    l_pt_id   patient.pt_id%type := &par_pt_id;
  6    l_dname   doctor.doc_name%type;
  7  begin
  8    select d.doc_name
  9      into l_dname
 10      from doctor d join patient p on d.doc_id = p.doc_id
 11     where p.pt_id = l_pt_id;
 12
 13    dbms_output.put_line ('He/She is the patient of Dr. '|| l_dname);
 14  end;
 15  /
Enter value for par_pt_id: 100
He/She is the patient of Dr. Luffy

PL/SQL procedure successfully completed.

SQL>

Upvotes: 2

Related Questions