The return type of the oracle function is record-defined instance.

Source: Internet
Author: User

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.

 
 
  1. CREATE OR REPLACE
  2. Package pkg_test
  3. /* Define the ref cursor type
  4. The return type is not added. The return type is weak. dynamic SQL queries are allowed,
  5. Otherwise, it is strongly typed and cannot be queried using dynamic SQL;
  6. */
  7. Type myrctype is ref cursor;

-- Oracle function declaration

 
 
  1.  function   get(intID   number)   return   myrctype;     
  2. end   pkg_test;     
  3. /     
  4.         
  5. CREATE   OR   REPLACE     
  6. package   body   pkg_test   as    

-- Oracle function body

 
 
  1. Function get (intID number) return myrctype is
  2. Rc myrctype; -- defines the ref cursor variable
  3. Sqlstr varchar2 (500 );
  4. Begin
  5. If intID = 0 then
  6. -- The static test directly returns the result using the select statement.
  7. Open rc for select id, name, sex, address, postcode, birthday from student;
  8. Else
  9. -- Assign a value to a dynamic SQL statement. Use w_id to declare that the variable is obtained from the outside.
  10. Sqlstr: = 'select id, name, sex, address, postcode, birthday from student where id =: w_id ';
  11. -- Dynamic Test: return results using sqlstr strings and PASS Parameters using keywords
  12. Open rc for sqlstr using intid;
  13. End if;
  14.  
  15. Return rc;
  16. End get;
  17.  
  18. 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.