Write a cursor which will display gym wise member details.(Use Parametrized Cursor)

RDBMS SOLUTION

ASSIGNMENT 5 

SET B Q.3 SOLUTION

Write a cursor which will display gym wise member details.(Use Parametrized Cursor)

Source code:

    

    /*set b q 3 solution

    here the parameterized cursor, the parameter will be gym name

    Explaination of the code at the end*/

Declare

v_gname varchar2(40):=:gymname;

v_mid member.mid%type;

v_mname member.mname%type;

cursor get_det is

select m.mid , m.mname from member m, gym g,g_m gm 

where gm.gname=v_gname and m.mid=gm.mid;

begin

dbms_output.put_line('Gym name:-  ' ||v_gname);

open get_det;

loop

Exit when get_det%notfound;

fetch get_det into v_mid, v_mname;

dbms_output.put_line(v_mid||'      '||v_mname);

end loop;

close get_det;

end;

/* Explaination:

This is a PL/SQL block of code used to retrieve and display data from the "member" table based on the gym name provided.

The first line declares a variable named "v_gname" with a data type of varchar2(40) and assigns a value to it from a variable or constant named "gymname". 

The next two lines declare two variables, "v_mid" and "v_mname", with their respective data types. These variables will be used to store the values retrieved from the "member" table.

The "get_det" cursor is defined to retrieve the "mid" and "mname" columns from the "member" table based on the gym name provided in the variable "v_gname". The cursor joins the "member", "gym", and "g_m" tables.

The "dbms_output.put_line" statement is used to display the gym name on the console.

The "open get_det" statement opens the cursor and starts the loop to retrieve the data from the "member" table.

The "loop" statement starts a loop that will execute until there are no more rows to fetch.

The "exit when get_det%notfound" statement checks if there are any more rows to fetch. If there are no more rows, the loop is exited.

The "fetch get_det into v_mid, v_mname" statement retrieves the next row from the "get_det" cursor and stores the values in the variables.

The "dbms_output.put_line" statement prints the values of the variables v_mid and v_mname on the console.

The "end loop" statement marks the end of the loop.

The "close get_det" statement closes the cursor and ends the PL/SQL block.

Overall, this code retrieves the data from the "member" table based on the gym name provided and displays it on the console in a tabular format. It joins the "member", "gym", and "g_m" tables to retrieve the data.

*/

   

    

 Download code         next