How to insert a input present in plsql procedure in a select statement

Question

I have 2 tables, parent and child. I am making a procedure which will take id as input and give the output from both the tables. If a row is not found in table 2 it will give an exception.

For this, I used a cursor for select statement and passed it as out parameter in my procedure, but I am confused what to give condition in the cursor's select statement so that it will check the id which is provided as input and display the rows from both the tables.screen shot of my code

CODE:

    create or replace procedure stu_proc
(st_id in student_main.stu_id%type,
st_cur out sys_refcursor)
is
cursor cur_st(st_id number) is select * from student_main sm 
join student_details sd
on sm.id = sd.st_id
where sm.id = st_id;
st_id cur_st%rowtype;
begin
open cur_st;
loop
fetch cur_st into st_cur;
exit when cur_st%notfound;
end loop;
close cursor;
exception
when no_data_found then 
dbms_output.put_line('Student details not found');
end;

Answer 1

yes i can join but my main concern is to join only those rows whose input will be provided by user as student id(used as IN parameter in the procedure)

I can see few issues with your code.

1) Passing parameters to cursor is not done, so the input value you passed is actaully not used while evaluating the cursor.

2) This variable declaration is looking wrong st_id cur_st%rowtype. It should be like st_cur cur_st%rowtype.

Also note that since you are already using s sys_refcursor for retruning the result of the query, there is no need to open another explicit cursor. You code can be modified as below:

Please try below.

CREATE OR REPLACE PROCEDURE stu_proc(
        col_st_id IN student_main.id%type,
        st_cur OUT sys_refcursor)
    IS  
    BEGIN
      OPEN st_cur for 
       SELECT sm.*
        FROM student_main sm
        JOIN student_details sd
        ON sm.id    = sd.st_id
        WHERE sm.id = col_st_id;

      EXCEPTION
      WHEN no_data_found THEN
        dbms_output.put_line('Student details not found');
    END;