如何像在 T-SQL 中一样在 PL/SQL 中声明和使用变量?

问题描述

在 Sql Server 中，当我测试存储过程的主体时，我经常将主体复制到 SSMS，在页面顶部声明变量，将它们设置为一些示例值，然后将主体作为-是.

In Sql Server, often times when I'm testing the body of a stored procedure, I copy the body into SSMS, DECLARE the variables at the top of the page, set them to some sample values, and execute the body as-is.

例如，如果我的 proc 是

For Example, if my proc is

CREATE PROC MySampleProc
    @Name   VARCHAR(20)
AS
    SELECT @Name

那么我的测试 sql 将是

Then my test sql would be

DECLARE @Name VARCHAR(20)
SET     @Name = 'Tom'

    SELECT @Name

与此等效的 Oracle PL/SQL 是什么?

What is the Oracle PL/SQL equivalent to this?

这是我想出的最接近的，但我得到PLS-00428:在这个 SELECT 语句中需要一个 INTO 子句"

This is the closest that I've come up with, but I'm getting "PLS-00428: an INTO clause is expected in this SELECT statement"

DECLARE
   myname varchar2(20);
BEGIN
     myname := 'Tom';

     select myname from DUAL;
END;

这是我真正想做的一个更好的例子:

This is a better example of what I'm really trying to do:

DECLARE
   myname varchar2(20);
BEGIN
     myname := 'Tom';

     SELECT *
     FROM   Customers
     WHERE  Name = myname;
END;

但同样，当我真的只想将记录打印在屏幕上，而不是存储在另一个表中时，它想要一个INTO"......

But again, it wants an 'INTO' when really I just want the records printed on the screen, not stored in another table....

已解决:

感谢@Allan，我让它运行良好.Oracle SQL Developer 显然会记住您提供给它的参数值.但是，PL/SQL Developer 不想与此有任何关系....

Thanks to @Allan, I've got it working well enough. Oracle SQL Developer apparently remembers the parameter values you supply it with. PL/SQL Developer, however, wants nothing to do with this....

如果您以脚本方式运行"，它将遵循您的默认设置，但只会以 ASCI 文本形式返回结果，而不是以网格/电子表格形式返回结果

If you "Run As Script", it will abide by your defaults, but it will only return results as ASCI text, not in a grid/spreadsheet

推荐答案

修改后的答案

如果您不是从另一个程序调用此代码，则可以选择跳过 PL/SQL 并使用绑定变量在 SQL 中严格执行:

If you're not calling this code from another program, an option is to skip PL/SQL and do it strictly in SQL using bind variables:

var myname varchar2(20);

exec :myname := 'Tom';

SELECT *
FROM   Customers
WHERE  Name = :myname;

在许多工具(例如 Toad 和 SQL Developer)中，省略 var 和 exec 语句会导致程序提示您输入值.

In many tools (such as Toad and SQL Developer), omitting the var and exec statements will cause the program to prompt you for the value.

原答案

T-SQL 和 PL/SQL 之间的一个很大区别是 Oracle 不允许您隐式返回查询结果.结果总是必须以某种方式显式返回.最简单的方法是使用DBMS_OUTPUT(大致相当于print)输出变量:

A big difference between T-SQL and PL/SQL is that Oracle doesn't let you implicitly return the result of a query. The result always has to be explicitly returned in some fashion. The simplest way is to use DBMS_OUTPUT (roughly equivalent to print) to output the variable:

DECLARE
   myname varchar2(20);
BEGIN
     myname := 'Tom';

     dbms_output.print_line(myname);
END;

但是，如果您尝试返回结果集，这并不是很有帮助.在这种情况下，您要么想要返回一个集合，要么想要返回一个 refcursor.但是，使用这些解决方案中的任何一个都需要将您的代码包装在一个函数或过程中，并从能够使用结果的东西中运行该函数/过程.以这种方式工作的函数可能如下所示:

This isn't terribly helpful if you're trying to return a result set, however. In that case, you'll either want to return a collection or a refcursor. However, using either of those solutions would require wrapping your code in a function or procedure and running the function/procedure from something that's capable of consuming the results. A function that worked in this way might look something like this:

CREATE FUNCTION my_function (myname in varchar2)
     my_refcursor out sys_refcursor
BEGIN
     open my_refcursor for
     SELECT *
     FROM   Customers
     WHERE  Name = myname;

     return my_refcursor;
END my_function;

这篇关于如何像在 T-SQL 中一样在 PL/SQL 中声明和使用变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！