Help Center/ GaussDB(DWS)/ Tool Guide/ DSC/ Oracle Syntax Migration/ PL/SQL Packages/ REF CURSOR
Updated on 2024-07-19 GMT+08:00
View PDF
Share

REF CURSOR

REF Cursor is a data type that can store the database cursor values and is used to return query results. DSC supports migration of REF CURSOR. The example below shows how the DSC migrates lref_strong_emptyp (local REF CURSOR) and ref_strong_emptyp (package-level REF CURSOR).

Input - REF CURSOR in PL/SQL Package (Package Specification and Body)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
# Package specification
CREATE OR REPLACE PACKAGE pkg_refcur 
IS 
	TYPE ref_variable IS REF CURSOR;     
	TYPE ref_strong_emptyp IS REF CURSOR RETURN emp_o%ROWTYPE;
	PROCEDURE p_get_employees ( v_id in INTEGER ,po_results OUT ref_strong_emptyp );
	
END pkg_refcur ;
/

# Package body
CREATE OR REPLACE PACKAGE BODY pkg_refcur 
IS 
	TYPE lref_strong_emptyp IS REF CURSOR RETURN emp_o%ROWTYPE ;     
	var_num NUMBER ;
	
	PROCEDURE p_get_employees ( v_id IN INTEGER, po_results OUT ref_strong_emptyp ) 
	is 
		vemp_rc lref_strong_emptyp ;
	Begin
		OPEN po_results for 
		SELECT * FROM emp_o e
		 WHERE e.id = v_id;

	EXCEPTION 
		WHEN OTHERS THEN 
			RAISE;
	END p_get_employees;
END pkg_refcur;
/

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
 
BEGIN
          INSERT INTO MIG_ORA_EXT.MIG_PKG_VARIABLES ( SCHEMA_NAME ,PACKAGE_NAME ,SPEC_OR_BODY ,VARIABLE_NAME ,VARIABLE_TYPE ,CONSTANT_I ,DEFAULT_VALUE ,EXPRESSION_I )
     VALUES ( UPPER( current_schema ( ) ) ,UPPER( 'pkg_refcur' ) ,'B' ,UPPER( 'var_num' ) ,UPPER( 'NUMBER' ) ,false ,NULL ,false ) ;
END ;
/

CREATE
     OR REPLACE PROCEDURE pkg_refcur#p_get_employees ( v_id IN INTEGER ,po_results OUT SYS_REFCURSOR ) is vemp_rc SYS_REFCURSOR ;
     Begin
          OPEN po_results for SELECT
                    *
               FROM
                    emp_o e
               WHERE
                    e.id = v_id ;
                    EXCEPTION WHEN OTHERS
               THEN RAISE ;
     END ;
     /
Parent topic: PL/SQL Packages