oracle 选择存储过程 plsql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28279134/
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 stored proc plsql
提问by user3503891
I'm a bit confused by the stored procedure syntax in Oracle.
我对 Oracle 中的存储过程语法有点困惑。
I started with a simple:
我从一个简单的开始：
select * from test_table;
It works, then I put it in a proc:
它有效，然后我把它放在一个过程中：
CREATE OR REPLACE PROCEDURE example
IS
BEGIN
select * from test_table;
END;
Doesn't work. Expected "INTO" is the error message I get. Now, I've seen syntax examples of SQL Server code that just shoves a select statement into a proc and it works instantly, but that doesn't seem to be the case here.
不起作用。预期的“INTO”是我收到的错误消息。现在，我已经看到 SQL Server 代码的语法示例，它只是将一个 select 语句推入一个 proc 并且它立即工作，但这里似乎并非如此。
回答by OldProgrammer
T-SQL and PL/SQL are completely different languages. In particular, for PL/SQL you have to select the result into some variable or cursor. Depending on what you plan to do with the record data - process in the procedure - or return to the caller, will drive what you have to do.
T-SQL 和 PL/SQL 是完全不同的语言。特别是，对于 PL/SQL，您必须将结果选择到某个变量或游标中。取决于你打算用记录数据做什么——过程中的处理——或者返回给调用者，将驱动你必须做什么。
In your example, if you want to return the record set, you would do something like this:
在您的示例中，如果您想返回记录集，您可以执行以下操作：
CREATE OR REPLACE PROCEDURE example (
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
select * from test_table;
END example ;
See this linkfor examples.
有关示例，请参阅此链接。
回答by mmmmmpie
Hello and welcome to SO.
I assume the full error you're seeing would be PLS-00428: an INTO clause is expected in this SELECT statementand it is correct, you must have an INTOstatement in a stored procedure.
I recommend thislink for syntax relating to the SELECT INTOstatement.
For your code I recommend this (I've changed from your test_tableexample to dba_user):
您好，欢迎来到 SO。
我假设您看到的完整错误PLS-00428: an INTO clause is expected in this SELECT statement是正确的，您必须INTO在存储过程中有一条语句。
我推荐此链接以获取与该SELECT INTO语句相关的语法。
对于您的代码，我建议这样做（我已从您的test_table示例更改为 dba_user）：
CREATE OR REPLACE PROCEDURE example
IS
l_username VARCHAR(25);
BEGIN
select username INTO l_username from dba_users where user_id=1;
END;
/
Note:The INTOclause works with 1column from 1 row. You cannot select multiple records or columns into this. You would need to reference the BULK COLLECTfeature to do that. For examples of that feel free to read here.
注：该INTO条款与工程1列1行。您不能在其中选择多个记录或列。您需要引用该BULK COLLECT功能才能做到这一点。有关示例，请在此处随意阅读。
回答by Lalit Kumar B
select * from test_table;
从 test_table 中选择 *；
SQLand PL/SQLare nor the same. To execute a SQL in a procedure, the parser expects an INTOclause to store the value returned by the sql statement. In PL/SQL, there is a reason to execute a SQL statement. You want to use the result set later to process. Not just retrieve and do nothing.
SQL和PL/SQL也不相同。要在过程中执行 SQL，解析器需要一个INTO子句来存储 sql 语句返回的值。在 中PL/SQL，有理由执行 SQL 语句。您想稍后使用结果集进行处理。不只是检索而什么都不做。
Also, it is a bad idea to use select *in any production system. You don't want to dump all the columns data of the table on an application screen. There are many other reasons, however, not in the scope of this question.
此外，select *在任何生产系统中使用都是一个坏主意。您不想在应用程序屏幕上转储表的所有列数据。然而，还有许多其他原因不在本问题的范围内。
You need to modify your SQL like following -
您需要修改您的 SQL，如下所示 -
SELECT column_name INTO variable FROM table_name
SELECT column_name INTO variable FROM table_name
There are several ways to fetch the data via SQL statement in PL/SQL. You need to elaborate your requirement and narrow down to specific steps here.
在 PL/SQL 中有多种通过 SQL 语句获取数据的方法。您需要在此处详细说明您的要求并缩小到特定步骤。
If you are learning about these concepts, I would recommend you to start reading the Oracle documentation first. Try and understand the concepts, and if you find any issues, then prepare a test case, explain your issue in words and then post a question. Too broad questions are difficult to answer, and are mostly considered out of scope.
如果您正在学习这些概念，我建议您先开始阅读 Oracle 文档。试着理解这些概念，如果你发现任何问题，然后准备一个测试用例，用文字解释你的问题，然后发布一个问题。太宽泛的问题很难回答，而且大多被认为超出了范围。
