Oracle SQL 存储过程重复错误和多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27228986/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL Stored Procedures Duplicate Error and Multiple Rows
提问by Mocking
I am trying to create a stored procedure for ORACLE SQL but it keeps throwing the error:
我正在尝试为 ORACLE SQL 创建一个存储过程，但它不断抛出错误：
Error(1,1): PLS-00410: duplicate fields in RECORD,TABLE or argument list are not permitted
I do not see any duplicate fields so I was wondering why this was happening (procedure is below). Also stored procedures only seem to allow queries to return one row, is there any way to make it return more than one row?
我没有看到任何重复的字段，所以我想知道为什么会发生这种情况（程序如下）。另外存储过程似乎只允许查询返回一行，有什么办法让它返回多于一行吗？
I saw a lot of questions pertaining to returning multiple rows but none of them were too clear. I also need multiple stored procedures so I was wondering if there could be any clashing of variables and whatnot.
我看到很多关于返回多行的问题，但没有一个太清楚。我还需要多个存储过程，所以我想知道是否有任何变量冲突之类的。
CREATE OR REPLACE PROCEDURE ARTIST_CHECK(
p5_checkartist IN VARCHAR2,
p5_artist OUT TESTTABLE.artist%TYPE,
p5_thisweekpos OUT TESTTABLE.thisweekpos%TYPE,
p5_lastweekpos OUT TESTTABLE.lastweekpos%TYPE,
p5_title OUT TESTTABLE.title%TYPE,
p5_artist OUT TESTTABLE.artist%TYPE,
p5_entrydate OUT TESTTABLE.entrydate%TYPE,
p5_entrypos OUT TESTTABLE.entrypos%TYPE,
p5_peakpos OUT TESTTABLE.peakpos%TYPE,
p5_totalweek OUT TESTTABLE.totalweek%TYPE,
p5_thisweekdate OUT TESTTABLE.thisweekdate%TYPE)
IS
BEGIN
select t.THISWEEKPOS ,t.LASTWEEKPOS ,t.TITLE ,t.ARTIST ,t.ENTRYDATE ,t.ENTRYPOS ,t.PEAKPOS ,t.TOTALWEEK ,t.THISWEEKDATE
into p5_thisweekpos, p5_lastweekpos, p5_title, p5_artist, p5_entrydate, p5_entrypos, p5_peakpos, p5_totalweek, p5_thisweekdate
from(select artist as match, max(thisweekdate) as recent from testtable where upper(artist) like '%p5_checkartist%' group by artist), testtable t
where t.ARTIST = match and t.THISWEEKDATE = recent;
END;
回答by Shantanu
below is there twice .. try omitting one per your code
下面有两次 .. 尝试每个代码省略一个
p5_artist OUT TESTTABLE.artist%TYPE,
回答by Matt
Remove the extra p5_artist OUT TESTTABLE.artist%TYPE,
去除多余的 p5_artist OUT TESTTABLE.artist%TYPE,
CREATE OR REPLACE PROCEDURE ARTIST_CHECK(
p5_checkartist IN VARCHAR2,
p5_artist OUT TESTTABLE.artist%TYPE,
p5_thisweekpos OUT TESTTABLE.thisweekpos%TYPE,
p5_lastweekpos OUT TESTTABLE.lastweekpos%TYPE,
p5_title OUT TESTTABLE.title%TYPE,
p5_entrydate OUT TESTTABLE.entrydate%TYPE,
p5_entrypos OUT TESTTABLE.entrypos%TYPE,
p5_peakpos OUT TESTTABLE.peakpos%TYPE,
p5_totalweek OUT TESTTABLE.totalweek%TYPE,
p5_thisweekdate OUT TESTTABLE.thisweekdate%TYPE)
IS
BEGIN
select t.THISWEEKPOS ,t.LASTWEEKPOS ,t.TITLE ,t.ARTIST ,t.ENTRYDATE ,t.ENTRYPOS ,t.PEAKPOS ,t.TOTALWEEK ,t.THISWEEKDATE
into p5_thisweekpos, p5_lastweekpos, p5_title, p5_artist, p5_entrydate, p5_entrypos, p5_peakpos, p5_totalweek, p5_thisweekdate
from(select artist as match, max(thisweekdate) as recent from testtable where upper(artist) like '%p5_checkartist%' group by artist), testtable t
where t.ARTIST = match and t.THISWEEKDATE = recent;
END;
