Stored Procedure (SP) can return data through one variable. We need to get dataset some times. Only one way to get the target is return cursor which can store dataset in SP.
A cursor is a mechanism by which you can assign a name to a “Select statement” and manipulate the information with that SQL statement.
l
CREATE OR REPLACEPROCEDURE SP_NAME(
AS
v_created VARCHAR2(100);
BEGIN
OPEN TEST1 FOR SELECT created
LOOP
FETCH TEST1 INTO v_created;
EXIT WHEN TEST1%NOTFOUND;
dbms_output.put_line(v_created);
END LOOP;
CLOSE TEST1;
END SP_TEMPLATE;
/
l
DECLARE
BEGIN
END;
l
SQL1:
OPENTEST1 FOR SELECT created
SQL2:
OPENTEST1 FOR initialStr;
Note: If you want to call the SP with cursor with JAVA, remove the code after LOOP clause.