The methods defined by the oracle function return type as record may not be available to everyone. The following describes an oracle function return type as record-defined instance for your reference.
- CREATE OR REPLACE
- Package pkg_test
- /* Define the ref cursor type
- The return type is not added. The return type is weak. dynamic SQL queries are allowed,
- Otherwise, it is strongly typed and cannot be queried using dynamic SQL;
- */
- Type myrctype is ref cursor;
-- Oracle function declaration
- function get(intID number) return myrctype;
- end pkg_test;
- /
-
- CREATE OR REPLACE
- package body pkg_test as
-- Oracle function body
- Function get (intID number) return myrctype is
- Rc myrctype; -- defines the ref cursor variable
- Sqlstr varchar2 (500 );
- Begin
- If intID = 0 then
- -- The static test directly returns the result using the select statement.
- Open rc for select id, name, sex, address, postcode, birthday from student;
- Else
- -- Assign a value to a dynamic SQL statement. Use w_id to declare that the variable is obtained from the outside.
- Sqlstr: = 'select id, name, sex, address, postcode, birthday from student where id =: w_id ';
- -- Dynamic Test: return results using sqlstr strings and PASS Parameters using keywords
- Open rc for sqlstr using intid;
- End if;
-
- Return rc;
- End get;
-
- Pkg_test;
Use of Oracle to_char Functions
Two cases of Java calling Oracle Functions
Learn about the Oracle FBI Index
How to uninstall an Oracle database in Windows
Multiple table Connection Methods in Oracle