Case: Add students and return to all students in the class.
Create or replace procedure add_stu (
P_sid stu. sid % type,
P_sname stu. sname % type,
P_cid stu. cid % type,
P_data out sys_refcursor -- output variable, system reference cursor
)
As
Begin
Insert into stu (sid, sname, cid)
Values (p_sid, p_sname, p_cid );
Commit;
-- Place the address of the query result set in the referenced cursor variable and pass it out.
Open p_data for select * from stu where cid = p_cid;
End;
-- PL/SQL call
Declare
Stu_data sys_refcursor;
Stu_row stu % rowtype;
Begin
Add_stu (52, 'B', 1, stu_data );
Fetch stu_data into stu_row;
While (stu_data % found)
Loop
Dbms_output.put_line (stu_row.sname );
Fetch stu_data into stu_row;
End loop;
Close stu_data;
End;
-- Called in java
CallableStatement cstmt = null;
String spName = "{call add_stu (?,?,?,?)} ";
Cstmt = conn. prepareCall (spName );
Cstmt. setInt (1, employee ID );
............
Cstmt. registerOutParameter (4, Oracle. jdbc. OracleTypes. CURSOR); -- set the value of 4th question marks
Cstmt.exe cuteUpdate ();
ResultSet rs = (ResultSet) cstmt. getObject (4 );