Usage of midstream targets in Oracle databases

Cursor definition:

• • --- Use declare -- to define a cursor and extract all data from tab_stu. cursor c_tab_stu is select * from tab_stu; r_tab_stu tab_stu % rowtype; -- use rowtype to store cursor data begin -- open cursor open c_tab_stu; -- extract data fetch c_tab_stu into r_tab_stu; dbms_output.put_line ('stu _ id: '| r_tab_stu.stu_id ); dbms_output.put_line ('stu _ name: '| r_tab_stu.stu_name); dbms_output.put_line ('stu _ age:' | r_tab_stu.stu_age); -- close cursor close c_tab_stu; end;

    
    

  • -- Use the loop to extract all records declare -- Define a cursor and extract all data in tab_stu. cursor c_tab_stu is select * from tab_stu; r_tab_stu tab_stu % rowtype; -- use rowtype to store cursor data begin -- open cursor open c_tab_stu; -- extract data loop fetch c_tab_stu into r_tab_stu; -- exit condition, that is, exit when c_tab_stu % notfound; values ('stu _ id: '| r_tab_stu.stu_id); dbms_output.put_line ('stu _ name:' | r_tab_stu.stu_name); values ('stu _ age: '| r_tab_stu.stu_age ); end loop; -- close the cursor close c_tab_stu; end;

    
    

  • -- A cursor containing conditions -- To achieve the goal, query the students' declare in each class -- first define two cursors -- Define tab_class cursor s_tab_class isselect * from tab_class; -- Define tab_stu cursor, however, a parameter is required here, which is the idcursor s_tab_stu (v_class_id number) isselect * from tab_stu where class_id = v_class_id; -- defines two storage variables -- defines tab_classv_tab_class_rowtype tab_class % rowtype; -- Define export tab_stu % rowtype; begin -- open the cursor open s_tab_class; loop -- extract data fetch s_tab_class into v_tab_class_rowtype; exit when s_tab_class % notfound; dbms_output.put_line ('class: '| bytes |' '); -- nested loop that traverses open s_tab_stu (bytes) for students in the current class; loop fetch s_tab_stu into v_tab_stu_rowtype; exit when s_tab_stu % notfound; dbms_output.put_line (v_tab_stu_rowtype.stu_name); end loop; close s_tab_stu; end loop; close s_tab_class; end;