Oracle弹出varianles,Oracle PL/SQL select into variables

问题

I am trying to run the following query in SQL Developer, but I am receiving an error. I am trying to declare two local variables (var_num1 and payDate) and then set the variables. Does anyone know what I can be doing incorrectly? I know Oracle SQL is a little different than SQL Server.

DECLARE

var_num1 number;

payDate date;

BEGIN

var_num1 := 100;

payDate := '10/1/2013'

BEGIN

SELECT * FROM Paycode WHERE PaycodeID = var_num1 and PaycodeDate = payDate;

End;

END;

Error report:

ORA-06550: line 6, column 2:

PLS-00428: an INTO clause is expected in this SELECT statement

06550. 00000 - "line %s, column %s:\n%s"

*Cause: Usually a PL/SQL compilation error.

*Action:

回答1:

You cannot use SELECT without INTO clause in PL/SQL. The output of the SELECT must be stored somewhere. Eg. table or variable or perhaps a record. See example below how to store result of the SELECT statement into record.

DECLARE

var_num1 number;

payDate date;

v_result Paycode%ROWTYPE;

BEGIN

var_num1 := 100;

payDate := '10/1/2013';

SELECT * INTO v_result

FROM Paycode

WHERE PaycodeID = var_num1 and PaycodeDate = payDate;

END;

回答2:

If you want to get a resultset back in SQL Developer with this code block you will need to open a ref cursor for the query e.g.

DECLARE

refCur REF CURSOR;

refc refCur;

var_num1 number;

payDate date;

v_result Paycode%ROWTYPE;

BEGIN

var_num1 := 100;

payDate := '10/1/2013';

OPEN refc FOR

SELECT *

FROM Paycode

WHERE PaycodeID = var_num1 and PaycodeDate = payDate;

END;

来源：https://stackoverflow.com/questions/20474612/oracle-pl-sql-select-into-variables