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.
在Sql Server中,经常在我测试存储过程的主体时,我将主体复制到SSMS中,将页面顶部的变量DECLARE,将它们设置为一些样本值,然后按原样执行主体。
For Example, if my proc is
例如,如果我的proc是
CREATE PROC MySampleProc
@Name VARCHAR(20)
AS
SELECT @Name
Then my test sql would be
然后我的测试SQL将是
DECLARE @Name VARCHAR(20)
SET @Name = 'Tom'
SELECT @Name
What is the Oracle PL/SQL equivalent to this?
什么是Oracle PL / SQL相当于此?
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"
这是我提出的最接近的,但是我得到了“PLS-00428:在这个SELECT语句中需要一个INTO子句”
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;
But again, it wants an 'INTO' when really I just want the records printed on the screen, not stored in another table....
但是,当我真的只想要在屏幕上打印的记录而不是存储在另一个表格中时,它想要一个'INTO'。
RESOLVED:
解决:
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....
感谢@Allan,我的工作得很好。 Oracle SQL Developer显然会记住您提供的参数值。但是,PL / SQL Developer不需要这个......
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
如果你“运行为脚本”,它将遵守你的默认值,但它只会返回结果为ASCI文本,而不是网格/电子表格
3 个解决方案
#1
18
Revised Answer
修订后的答案
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:
如果您没有从其他程序调用此代码,则可以选择跳过PL / SQL并使用绑定变量在SQL中严格执行此操作:
var myname varchar2(20);
exec :myname := 'Tom';
SELECT *
FROM Customers
WHERE Name = :myname;
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.
在许多工具(例如Toad和SQL Developer)中,省略var和exec语句将导致程序提示您输入值。
Original Answer
原始答案
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:
T-SQL和PL / SQL之间的一个很大区别是Oracle不允许您隐式返回查询结果。结果总是必须以某种方式显式返回。最简单的方法是使用DBMS_OUTPUT(大致相当于print)来输出变量:
DECLARE
myname varchar2(20);
BEGIN
myname := 'Tom';
dbms_output.print_line(myname);
END;
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:
但是,如果您尝试返回结果集,这并不是非常有用。在这种情况下,您要么返回一个集合,要么返回一个refcursor。但是,使用这些解决方案中的任何一个都需要将代码包装在函数或过程中,并从能够消耗结果的内容中运行函数/过程。以这种方式工作的函数可能如下所示:
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;
#2
4
In Oracle PL/SQL, if you are running a query that may return multiple rows, you need a cursor to iterate over the results. The simplest way is with a for loop, e.g.:
在Oracle PL / SQL中,如果您正在运行可能返回多行的查询,则需要一个游标来迭代结果。最简单的方法是使用for循环,例如:
declare
myname varchar2(20) := 'tom';
begin
for result_cursor in (select * from mytable where first_name = myname) loop
dbms_output.put_line(result_cursor.first_name);
dbms_output.put_line(result_cursor.other_field);
end loop;
end;
If you have a query that returns exactly one row, then you can use the select...into...
syntax, e.g.:
如果您的查询只返回一行,那么您可以使用select ... into ...语法,例如:
declare
myname varchar2(20);
begin
select first_name into myname
from mytable
where person_id = 123;
end;
#3
1
Variables are not defined, but declared.
变量未定义,但已声明。
This is possible duplicate of declare variables in a pl/sql block
这可能是pl / sql块中声明变量的重复
But you can look here :
但你可以看看这里:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#i27306
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#i27306
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/overview.htm
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/overview.htm
UPDATE:
更新:
Refer here : How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?
请参阅此处:如何从执行动态SQL的Oracle PL / SQL匿名块返回结果集/游标?
#1
18
Revised Answer
修订后的答案
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:
如果您没有从其他程序调用此代码,则可以选择跳过PL / SQL并使用绑定变量在SQL中严格执行此操作:
var myname varchar2(20);
exec :myname := 'Tom';
SELECT *
FROM Customers
WHERE Name = :myname;
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.
在许多工具(例如Toad和SQL Developer)中,省略var和exec语句将导致程序提示您输入值。
Original Answer
原始答案
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:
T-SQL和PL / SQL之间的一个很大区别是Oracle不允许您隐式返回查询结果。结果总是必须以某种方式显式返回。最简单的方法是使用DBMS_OUTPUT(大致相当于print)来输出变量:
DECLARE
myname varchar2(20);
BEGIN
myname := 'Tom';
dbms_output.print_line(myname);
END;
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:
但是,如果您尝试返回结果集,这并不是非常有用。在这种情况下,您要么返回一个集合,要么返回一个refcursor。但是,使用这些解决方案中的任何一个都需要将代码包装在函数或过程中,并从能够消耗结果的内容中运行函数/过程。以这种方式工作的函数可能如下所示:
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;
#2
4
In Oracle PL/SQL, if you are running a query that may return multiple rows, you need a cursor to iterate over the results. The simplest way is with a for loop, e.g.:
在Oracle PL / SQL中,如果您正在运行可能返回多行的查询,则需要一个游标来迭代结果。最简单的方法是使用for循环,例如:
declare
myname varchar2(20) := 'tom';
begin
for result_cursor in (select * from mytable where first_name = myname) loop
dbms_output.put_line(result_cursor.first_name);
dbms_output.put_line(result_cursor.other_field);
end loop;
end;
If you have a query that returns exactly one row, then you can use the select...into...
syntax, e.g.:
如果您的查询只返回一行,那么您可以使用select ... into ...语法,例如:
declare
myname varchar2(20);
begin
select first_name into myname
from mytable
where person_id = 123;
end;
#3
1
Variables are not defined, but declared.
变量未定义,但已声明。
This is possible duplicate of declare variables in a pl/sql block
这可能是pl / sql块中声明变量的重复
But you can look here :
但你可以看看这里:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#i27306
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#i27306
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/overview.htm
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/overview.htm
UPDATE:
更新:
Refer here : How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?
请参阅此处:如何从执行动态SQL的Oracle PL / SQL匿名块返回结果集/游标?