oracle 从 PACKAGE 的 CURSOR 中选择数据，打印出来
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18035778/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT Data FROM CURSOR of PACKAGE, print it
提问by ifooi
I want to print data of Cursor_pkg.c1.row_emp, for ex:Cursor_pkg.c1.row_emp.last_namethat would be exist in Cursor_pkg.row_empafter Cursor_pkg_func.Print_Curprocedure would work. How can I do it?
我想打印 的数据Cursor_pkg.c1.row_emp，例如：在程序工作后将Cursor_pkg.c1.row_emp.last_name存在的数据。我该怎么做？Cursor_pkg.row_empCursor_pkg_func.Print_Cur
- I create PACKAGE with cursor and rec
- I create PACKAGE with procedure that fetch cursor data in rec
- I want to output fetched data. How?
- 我用光标和 rec 创建 PACKAGE
- 我使用在 rec 中获取游标数据的过程创建 PACKAGE
- 我想输出获取的数据。如何？
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_funcprocedure
P.S. The main idea is storing data and procedure/function for fetching and selecting data
有两个问题： 我想从包emp_rec（行）中输出数据，我想直接从PACKAGECursor_pkg_func过程中输出PS 主要思想是存储数据和用于获取和选择数据的过程/函数
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;
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?
但是我想从 Cursor_pkg.row_emp PACKAGE 选择和打印而没有创建函数。以及如何不仅打印姓氏而且打印所有行？错误开始于：最后三个语句有什么问题？
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;
/
回答by APC
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>
You need to use a TABLE() function. Although then you will discover the bug in your code:
您需要使用 TABLE() 函数。虽然这样你会发现你的代码中的错误：
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;
/
.... then 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"
“当我添加开始和结束时；选择不起作用”
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.
您已经创建了一个流水线函数。你为什么这么做？您应该这样做的原因是因为您想要一个可以在 SELECT 语句的 FROM 子句中使用的 PL/SQL 函数。这就是流水线函数的用例。因此，将调用放入匿名 PL/SQL 块中确实没有意义。
But anyway.
但无论如何。
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 alwaysfetch records into a variable of some description. Anonymous PL/SQL blocks are just the same as stored procedures in this regard. Find out more.
请阅读文档。它非常全面，在线且免费。PL/SQL 参考中的相关部分是关于静态 SQL 的章节。它清楚地表明 PL/SQL 中的 SELECT 语句必须始终将记录提取到某个描述的变量中。在这方面，匿名 PL/SQL 块与存储过程相同。 了解更多。
