PLS-00428:此SELECT语句中应有一个INTO子句 [英] PLS-00428: an INTO clause is expected in this SELECT statement
问题描述
我想将Rownum存储为变量,而不是使用昂贵的Join. 我需要从Select语句中获取此信息,因为Rownum在各种环境下都会有所不同,因此它不能是代码中的文字字符串.
I am wanting to store a Rownum as a variable rather than use a costly Join. I need to get this from a Select statement as the Rownum will be different on various environments so it cannot be a literal string in the code.
对于上下文,此查询在Oracle Siebel CRM模式上执行,并且检索某些具有特定类型和属性的产品.
For context, this query is executed on Oracle Siebel CRM schema and it retrieves some products of specific type and attributes.
我尝试在Toad和Oracle SQL Developer中使用以下SQL代码,但是出现以下错误:
I tried using the following SQL code in Toad and Oracle SQL Developer, however I am getting the following error:
PLS-00428:此SELECT语句中应包含一个INTO子句
PLS-00428: an INTO clause is expected in this SELECT statement
这是代码
DECLARE
PROD_ROW_ID varchar(10) := NULL;
BEGIN
SELECT ROW_ID INTO VIS_ROW_ID FROM SIEBEL.S_PROD_INT WHERE PART_NUM = 'S0146404';
BEGIN
SELECT rtrim(VIS.SERIAL_NUM) || ',' || rtrim(PLANID.DESC_TEXT) || ',' ||
CASE
WHEN PLANID.HIGH = 'TEST123'
THEN
CASE
WHEN to_date(PROD.START_DATE) + 30 > sysdate
THEN 'Y'
ELSE 'N'
END
ELSE 'N'
END
|| ',' || 'GB' || ',' ||
rtrim(to_char(PROD.START_DATE, 'YYYY-MM-DD'))
FROM SIEBEL.S_LST_OF_VAL PLANID
INNER JOIN SIEBEL.S_PROD_INT PROD
ON PROD.PART_NUM = PLANID.VAL
INNER JOIN SIEBEL.S_ASSET NETFLIX
ON PROD.PROD_ID = PROD.ROW_ID
INNER JOIN SIEBEL.S_ASSET VIS
ON VIS.PROM_INTEG_ID = PROD.PROM_INTEG_ID
INNER JOIN SIEBEL.S_PROD_INT VISPROD
ON VIS.PROD_ID = VISPROD.ROW_ID
WHERE PLANID.TYPE = 'Test Plan'
AND PLANID.ACTIVE_FLG = 'Y'
AND VISPROD.PART_NUM = VIS_ROW_ID
AND PROD.STATUS_CD = 'Active'
AND VIS.SERIAL_NUM IS NOT NULL;
END;
END;
/
推荐答案
在PLSQL块中,必须将选择语句的列分配给变量,而在SQL语句中不是这种情况.
In PLSQL block, columns of select statements must be assigned to variables, which is not the case in SQL statements.
第二个BEGIN的SQL语句没有INTO子句,这会导致错误.
The second BEGIN's SQL statement doesn't have INTO clause and that caused the error.
DECLARE
PROD_ROW_ID VARCHAR (10) := NULL;
VIS_ROW_ID NUMBER;
DSC VARCHAR (512);
BEGIN
SELECT ROW_ID
INTO VIS_ROW_ID
FROM SIEBEL.S_PROD_INT
WHERE PART_NUM = 'S0146404';
BEGIN
SELECT RTRIM (VIS.SERIAL_NUM)
|| ','
|| RTRIM (PLANID.DESC_TEXT)
|| ','
|| CASE
WHEN PLANID.HIGH = 'TEST123'
THEN
CASE
WHEN TO_DATE (PROD.START_DATE) + 30 > SYSDATE
THEN
'Y'
ELSE
'N'
END
ELSE
'N'
END
|| ','
|| 'GB'
|| ','
|| RTRIM (TO_CHAR (PROD.START_DATE, 'YYYY-MM-DD'))
INTO DSC
FROM SIEBEL.S_LST_OF_VAL PLANID
INNER JOIN SIEBEL.S_PROD_INT PROD
ON PROD.PART_NUM = PLANID.VAL
INNER JOIN SIEBEL.S_ASSET NETFLIX
ON PROD.PROD_ID = PROD.ROW_ID
INNER JOIN SIEBEL.S_ASSET VIS
ON VIS.PROM_INTEG_ID = PROD.PROM_INTEG_ID
INNER JOIN SIEBEL.S_PROD_INT VISPROD
ON VIS.PROD_ID = VISPROD.ROW_ID
WHERE PLANID.TYPE = 'Test Plan'
AND PLANID.ACTIVE_FLG = 'Y'
AND VISPROD.PART_NUM = VIS_ROW_ID
AND PROD.STATUS_CD = 'Active'
AND VIS.SERIAL_NUM IS NOT NULL;
END;
END;
/
参考
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS00601 http://docs.oracle.com/cd/B19306_01 /appdev.102/b14261/selectinto_statement.htm#CJAJAAIG http://pls-00428.ora-code.com/
这篇关于PLS-00428:此SELECT语句中应有一个INTO子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!