从包装的游标中选择数据，然后打印 [英] SELECT Data FROM CURSOR of PACKAGE, print it

问题描述

我想打印Cursor_pkg.c1.row_emp的数据，例如:Cursor_pkg.c1.row_emp.last_name工作后在Cursor_pkg.row_emp中将存在的Cursor_pkg.c1.row_emp.last_name.我该怎么办?

I want to print data of Cursor_pkg.c1.row_emp, for ex:Cursor_pkg.c1.row_emp.last_name that would be exist in Cursor_pkg.row_emp after Cursor_pkg_func.Print_Cur procedure would work. How can I do it?

1. 我用游标和记录创建包装
2. 我使用在rec中获取游标数据的过程创建PACKAGE
3. 我想输出获取的数据.怎么样?

有两个问题:我想从包emp_rec(行)输出数据，我想直接从包Cursor_pkg_func过程输出数据 P.S.主要思想是存储数据以及用于获取和选择数据的过程/功能

There is two questions: I want to output data from package emp_rec (row) and I want to output it directly from PACKAGE Cursor_pkg_func procedure P.S. The main idea is storing data and procedure/function for fetching and selecting data

 CREATE OR REPLACE PACKAGE Cursor_pkg AUTHID DEFINER IS
        CURSOR C1 IS
            SELECT last_name, job_id FROM employees
            WHERE job_id LIKE '%CLERK%' AND manager_id > 120
            ORDER BY last_name;
        row_emp C1%ROWTYPE;     
    END Cursor_pkg;
    /

CREATE OR REPLACE PACKAGE Cursor_pkg_func IS
PROCEDURE Print_Cur;
END Cursor_pkg_func;
/

CREATE OR REPLACE PACKAGE BODY Cursor_pkg_func IS
    PROCEDURE Print_Cur IS
    BEGIN
        OPEN Cursor_pkg.C1;
        LOOP
            FETCH Cursor_pkg.C1 INTO Cursor_pkg.row_emp;
            EXIT when Cursor_pkg.C1%NOTFOUND;
            DBMS_OUTPUT.put_line(Cursor_pkg.row_emp.last_name);
        END LOOP;
        CLOSE Cursor_pkg.C1;
     END;
END;
/

BEGIN 
Cursor_pkg_func.Print_Cur;
END;

但是我想从Cursor_pkg.row_emp PACKAGE中选择并打印而不创建函数. 以及如何不仅打印last_name而是打印所有行? 错误始于:最后三个语句出了什么问题?

But I want to select and print from Cursor_pkg.row_emp PACKAGE without created function. And how to print not only last_name but all row? Errors start with: What's wrong with last three statements?

CREATE OR REPLACE PACKAGE Cursor_pkg_func IS
TYPE outrec_typ IS RECORD (
    var_char2  VARCHAR2(30)
  );
  TYPE outrecset IS TABLE OF outrec_typ;
  FUNCTION f_trans (p in number ) RETURN outrecset PIPELINED;
END Cursor_pkg_func;
/

CREATE OR REPLACE PACKAGE BODY Cursor_pkg_func IS
    FUNCTION f_trans (p in number) RETURN outrecset PIPELINED IS
    out_rec outrec_typ;
    BEGIN
        OPEN Cursor_pkg.C1;
        LOOP
            FETCH Cursor_pkg.C1 INTO Cursor_pkg.row_emp;
            EXIT when Cursor_pkg.C1%NOTFOUND;
        END LOOP;
        LOOP
        out_rec.var_char2 := Cursor_pkg.row_emp.last_name;
        PIPE ROW(out_rec);
        DBMS_OUTPUT.put_line(out_rec.var_char2);
        END LOOP;
        CLOSE Cursor_pkg.C1;
     RETURN;
     END f_trans;
END Cursor_pkg_func;
/

begin
Cursor_pkg_func.f_trans(5);
end;
/

推荐答案

您已经定义了流水线函数，但这不是调用它的方法:

You have defined a pipelined function, and this is not the way to call it:

SQL> begin
  2  Cursor_pkg_func.f_trans(5);
  3  end;
  4  /
Cursor_pkg_func.f_trans(5);
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00221: 'F_TRANS' is not a procedure or is undefined
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored


SQL> 

您需要使用TABLE()函数.虽然如此，您将在代码中发现该错误:

You need to use a TABLE() function. Although then you will discover the bug in your code:

SQL>  select * from table(Cursor_pkg_func.f_trans(5))
  2   /

SMITH
SMITH
SMITH
''''
SMITH
SMITH
SMITH
SMITH
ERROR:
ORA-00028: your session has been killed



273660 rows selected.

SQL> 

请注意，我必须从另一个会话中终止该会话，否则它将仍在运行.因此，让我们简化函数并摆脱那毫无意义的第二个循环....

Note I had to kill that session from another session, otherwise it would still be running. So let's simplify the function and get rid of that pointless second loop ....

CREATE OR REPLACE PACKAGE BODY Cursor_pkg_func IS
    FUNCTION f_trans (p in number) RETURN outrecset PIPELINED IS
    out_rec outrec_typ;
    BEGIN
        OPEN Cursor_pkg.C1;
        LOOP
            FETCH Cursor_pkg.C1 INTO Cursor_pkg.row_emp;
            EXIT when Cursor_pkg.C1%NOTFOUND;
            out_rec.var_char2 := Cursor_pkg.row_emp.last_name;
            PIPE ROW(out_rec);
        END LOOP;
        CLOSE Cursor_pkg.C1;
     RETURN;
     END f_trans;
END Cursor_pkg_func;
/

....然后lo！

SQL> select * from table(Cursor_pkg_func.f_trans(5))
  2  /

VAR_CHAR2
------------------------------
ADAMS
JAMES
MILLER
SMITH

SQL> 

---

当我添加开始和结束时；选择不起作用"

"When I add begin and end; select not work"

您已经创建了流水线函数.你为什么这么做?之所以这样做，是因为您想要一个可以在SELECT语句的FROM子句中使用的PL/SQL函数.这就是流水线功能的用例.因此，将调用放入匿名PL/SQL块中实际上没有任何意义.

You have created a pipelined function. Why did you do that? The reason you ought to have done that was because you wanted a PL/SQL function which could be used in the FROM clause of a SELECT statement. That is the use case for pipelined functions. So putting the call into an anonymous PL/SQL block really doesn't make sense.

但是无论如何.

请阅读文档.它是非常全面的，它是在线免费的. 《 PL/SQL参考》中的相关部分是静态SQL"一章.很明显，PL/SQL中的SELECT语句必须始终始终将记录提取到具有某些描述的变量中.在这方面，匿名PL/SQL块与存储过程相同. 了解更多.

Please read the documentation. It is quite comprehensive, it is online and free. The pertinent section in the PL/SQL Reference is the chapter on Static SQL. It makes clear that SELECT statements in PL/SQL must always fetch records into a variable of some description. Anonymous PL/SQL blocks are just the same as stored procedures in this regard. Find out more.

这篇关于从包装的游标中选择数据，然后打印的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！