如何像在T-SQL中一样在PL/SQL中声明和使用变量? [英] How do I declare and use variables in PL/SQL like I do in T-SQL?
问题描述
在Sql Server中,通常当我测试存储过程的主体时,我将主体复制到SSMS中,对页面顶部的变量进行DECLARE,将它们设置为一些示例值,然后以如下方式执行主体: -是.
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.
例如,如果我的进程是
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中声明和使用变量?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!