Oracle uses temporary tables to return data tables

Source: Internet
Author: User


Oracle used a temporary table to return data tables and found many articles about the returned data tables in the stored procedure. We found that there is a big difference between oracle and SQL server. SQL server can directly return data tables in the stored procedure, oracle stored procedures cannot directly return data tables. At most, they return data table pointers. If you want to see the returned content in SQL, you also need to write a loop to read the content. Here I wrote a pipeline table function to return what I want. Note: DWXXTreeNode: unit information tree node (equivalent to a row) DWXXTree: unit information tree (equivalent to a table) www.2cto.com dwxx: unit information table (data table) here we use the content of the stored procedure to return data tables and MPs queue table functions. There are many online functions, so we will not introduce them here. It should be noted that oracle is case sensitive, so DWXXTreeNode and DWXXTree must be case sensitive. Write this article to complete the content of the data table returned by the stored procedure, because sometimes I just want to read the content in pl/SQL, not output it to the outside. After writing the package, I think that the package is implemented in the body like a java class with attributes and methods. Call: select * from table (PG_dwxxTree.getTree (0); pl/SQL code: [SQL] -- tree node, this function is used to return the create or replace type DWXXTreeNode is object (dwdm varchar2 (15), -- unit code dmd number (2) -- layers); create or replace type DWXXTree is table of DWXXTreeNode; www.2cto.com -- the PACKAGE defines create or replace package PG_dwxxTree IS type DataTable is ref cursor; type TreeNode is record (dwdm varchar2 (15), dmd number (2 )); procedure createTree (dt OUT able, fz in number); -- condition: dt: data table pointer, fz: group function getTree (fz in number) return DWXXTree pipelined; END PG_dwxxTree; -- package body, used to generate the create or replace package body PG_dwxxTree IS procedure createTree (dt OUT DataTable, fz in number) is begin -- the specific implementation can be written here, you can also use the temporary table OPEN dt for select dwdm, dmd FROM dwxx where rownum <5; END createTree; www.2cto.com -- call the function getTree (fz in number) returned by the Stored Procedure) return dwxxTree pipelined is invalid DWXXTreeNode; tb_cursor cursor; item cursor; begin createTree (tb_cursor, fz); loop fetch tb_cursor into item; exit when tb_cursor % notfound; consume (item. dwdm | ''| item. dmd | ''); obj_dwxxTreeNode: = dwxxTreeNode (item. dwdm, item. dmd); pipe row (obj_dwxxTreeNode); end loop; return; end getTree; end PG_dwxxTree;

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.