英文:
PL/SQL Stored Procedure - return records from a table
问题
我对SQL工作非常陌生。我想知道如何在PL/SQL存储过程中返回一个选择语句。
到目前为止，我的理解很有限，我认为应该将数据的返回放入一个表中，并将表中的数据分配给引用游标。有了这个加载后，然后通过REF游标循环，并返回数据？
实际将其转换为存储过程的代码对我来说完全迷失了，因为没有足够的示例来满足我的用例。感谢提前的帮助 
英文:
I am very new to doing SQL work. I was wondering how I return a select statement in a stored procedure in PL/SQL.
My understanding so far (that is little) is that I should put the return of the data in a table and assign the data within the table to a reference cursor. With that loaded then LOOP through the REF Cursor and present the data back?
Actually converting that into code for a stored procedure has lost me completely with little examples to see with my use case. Any help is appreciated.
Many thanks in advance
答案1
得分: 1
这里有一个例子：过程只有一个参数 - OUT - 参数，它是一个 refcursor：
SQL> create or replace procedure p_test (par_rc out sys_refcursor)
2 is
3 begin
4 open par_rc for select deptno, dname, loc from dept;
5 end;
6 /
过程已创建。
要调用这样的过程，您需要将结果存储在某个地方。为了做到这一点，我将在 SQL*Plus 中声明一个变量，并使用 `begin-end` 块调用该过程，将变量名称作为其参数：
SQL> var l_rc refcursor;
SQL>
SQL> begin
2 p_test (:l_rc);
3 end;
4 /
PL/SQL 过程成功完成。
打印结果：
SQL> print l_rc
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>;
可能还有其他选项，这取决于您实际要做的事情。
英文:
Here's one example: procedure has only one - OUT - parameter, which is a refcursor:
SQL> create or replace procedure p_test (par_rc out sys_refcursor)
2 is
3 begin
4 open par_rc for select deptno, dname, loc from dept;
5 end;
6 /
Procedure created.
In order to call such a procedure, you need to store the result into something. In order to do that, I'll declare a variable (in SQL*Plus, which is a tool I use for this example) and call the procedure using begin-end block, providing the variable name as its parameter:
SQL> var l_rc refcursor;
SQL>
SQL> begin
2 p_test (:l_rc);
3 end;
4 /
PL/SQL procedure successfully completed.
Print the result:
SQL> print l_rc
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
There might be other options, which depend on what you really are doing.
答案2
得分: 1
通常，存储过程用于在数据库中执行数据操作，而函数用于返回值或数据。如果您只想在存储过程中使用SELECT语句，那么您需要使用游标，它就像在存储过程开始时声明的任何其他变量一样，然后在存储过程代码中隐式或显式打开。
隐式游标示例：
declare
cursor sample_cur is --这可以是您的SELECT语句
select sysdate as today from dual;
begin
for rec in sample_cur loop
-- 逐步处理游标返回的每个记录
dbms_output.put_line(rec.today);
end loop;
end;
英文:
Usually a stored procedure is used to perform manipulations of data in the database, and functions are used to return values or data. If you're just trying to use a select statement within a stored procedure, then you would want to use a cursor, which is something you declare like any other variable at the beginning of the procedure, and then open either implicitly or explicitly within the procedure code.
Example of an implicit cursor:
declare
cursor sample_cur is --this can be your select statement
select sysdate as today from dual;
begin
for rec in sample_cur loop
-- step by step for each record you return in your cursor
dbms_output.put_line(rec.today);
end loop;
end;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论