--
在Spec中定义
TYPE type_refcursor IS REF CURSOR ;
-- 在Body中写Procedure
PROCEDURE check_form_field (p_table_name IN VARCHAR2 , o_curs OUT type_refcursor)
IS
BEGIN
IF p_table_name IS NULL
THEN
OPEN o_curs FOR '
SELECT '' NO_TABLE '' AS field_name, '' NO_TABLE '' AS field_type,
'' 0 '' AS field_length
FROM DUAL ' ;
END IF ;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END check_form_field;
TYPE type_refcursor IS REF CURSOR ;
-- 在Body中写Procedure
PROCEDURE check_form_field (p_table_name IN VARCHAR2 , o_curs OUT type_refcursor)
IS
BEGIN
IF p_table_name IS NULL
THEN
OPEN o_curs FOR '
SELECT '' NO_TABLE '' AS field_name, '' NO_TABLE '' AS field_type,
'' 0 '' AS field_length
FROM DUAL ' ;
END IF ;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END check_form_field;
测试:
DECLARE
TYPE cursor_type_1 IS REF CURSOR ;
v_curs cursor_type_1;
l_rec_num NUMBER ;
field_name VARCHAR2 ( 100 );
field_type VARCHAR2 ( 100 );
field_length NUMBER ;
BEGIN
l_rec_num : = 0 ;
form_define_advance.check_form_field (p_table_name => ' BQEFORM7 ' ,
o_curs => v_curs
);
LOOP
FETCH v_curs
INTO field_name, field_type, field_length;
EXIT WHEN v_curs % NOTFOUND;
l_rec_num : = l_rec_num + 1 ;
DBMS_OUTPUT.put_line (l_rec_num);
DBMS_OUTPUT.put_line (field_name);
DBMS_OUTPUT.put_line (field_type);
DBMS_OUTPUT.put_line (field_length);
END LOOP;
IF v_curs % ISOPEN
THEN
CLOSE v_curs;
END IF ;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SUBSTR ( ' Error '
|| TO_CHAR (SQLCODE)
|| ' : '
|| SQLERRM,
1 ,
255
)
);
RAISE;
END ;
TYPE cursor_type_1 IS REF CURSOR ;
v_curs cursor_type_1;
l_rec_num NUMBER ;
field_name VARCHAR2 ( 100 );
field_type VARCHAR2 ( 100 );
field_length NUMBER ;
BEGIN
l_rec_num : = 0 ;
form_define_advance.check_form_field (p_table_name => ' BQEFORM7 ' ,
o_curs => v_curs
);
LOOP
FETCH v_curs
INTO field_name, field_type, field_length;
EXIT WHEN v_curs % NOTFOUND;
l_rec_num : = l_rec_num + 1 ;
DBMS_OUTPUT.put_line (l_rec_num);
DBMS_OUTPUT.put_line (field_name);
DBMS_OUTPUT.put_line (field_type);
DBMS_OUTPUT.put_line (field_length);
END LOOP;
IF v_curs % ISOPEN
THEN
CLOSE v_curs;
END IF ;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SUBSTR ( ' Error '
|| TO_CHAR (SQLCODE)
|| ' : '
|| SQLERRM,
1 ,
255
)
);
RAISE;
END ;