oracle PLSQL - 将选择查询结果存储在变量抛出错误中

PLSQL - Store a select query result in variable throw error

提问by kumarprd

I want to store a select query result in a variable in PLSQL.

我想将选择查询结果存储在 PLSQL 中的变量中。

SQL>var v_storedate VARCHAR2(19);
SQL>exec :v_storedate := 'select cdate from rprt where cdate between  cdate AND TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS') and ryg='R' and cnum='C002'';

As

作为

SQL>select cdate from rprt where cdate between  cdate AND TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS') and ryg='R' and cnum='C002';

Returns : 2013/04/27-10:06:26:794

返回 : 2013/04/27-10:06:26:794

But it throws error:

但它抛出错误：

 ERROR at line 1: ORA-06550: line 1, column 121: PLS-00103: Encountered
 the symbol "YYYY" when expecting one of the following:
 * & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between ||
 multiset member SUBMULTISET_ The symbol "*" was substituted for "YYYY"
 to continue. ORA-06550: line 1, column 148: PLS-00103: Encountered the
 symbol ") and ryg=" when expecting one of the following: . ( * @ % & =
 - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between

回答by Alex Poole

If you want to store the resultof the query then you need to use a select ... into; at the moment you're trying to store the text of the actual query, not its result. If you wanted to do that you would need to escape the single-quote characters as the other answers have pointed out, and increase the variable size.

如果要存储查询结果，则需要使用select ... into; 目前您正在尝试存储实际查询的文本，而不是其结果。如果你想这样做，你需要像其他答案指出的那样转义单引号字符，并增加变量大小。

var v_storedate VARCHAR2(19);
exec select cdate into :v_storedate from rprt where cdate between cdate AND TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS') and ryg='R' and cnum='C002';
print v_storedate

Which would be easier to deal with using a normal anonymous block rather than SQL*Plus' executeshorthand. You should also give an explicit date format mask when converting it to a string:

使用普通的匿名块而不是 SQL*Plus 的execute简写会更容易处理。在将其转换为字符串时，您还应该给出一个明确的日期格式掩码：

begin
    select to_char(cdate, 'YYYY/MM/DD-HH24:MI:SS')
    into :v_storedate
    from rprt
    where cdate between cdate AND TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS')
    and ryg='R' and cnum='C002';
end;
/

If you want the fractional seconds then you need to make your variable bigger, as 19 chars will only take you to the seconds.

如果你想要小数秒，那么你需要让你的变量更大，因为 19 个字符只会带你到秒。

Either way though you're risking getting either multiple results (which will give ORA-02112) or no results (which will give ORA-01403). As your whereclause doesn't make much sense and the table contents aren't known I don't know which is more likely. As be here now pointed out your cdatecomparison is always going to be true, plus you're doing an implicit date conversion in there which will break at some point. There isn't enough information to fix that for you.

无论哪种方式，您都会冒着获得多个结果（将给出ORA-02112）或没有结果（将给出ORA-01403）的风险。由于您的where条款没有多大意义，并且不知道表格内容，我不知道哪个更有可能。正如现在指出的那样，您的cdate比较总是正确的，而且您正在那里进行隐式日期转换，这会在某些时候中断。没有足够的信息来为您解决这个问题。

---

---

You can't get fractional seconds from a date value anyway, only from a timestamp; which cdateseems to be. But even then the format element for that is FF[0-9]. SSSSSS is the number of seconds since midnight. But as the whole to_char()bit looks wrong that's somewhat moot. Also, if you really do need a comparison with the current time, you should probably be comparing with systimestamprather than sysdateto be consistent - and then not doing any conversion of that.

无论如何，您无法从日期值中获取小数秒，只能从时间戳中获取；这cdate似乎是。但即便如此，其格式元素还是 FF[0-9]。SSSSSS 是自午夜以来的秒数。但由于整个to_char()位看起来不对，这有点没有实际意义。此外，如果您确实需要与当前时间进行比较，您可能应该比较systimestamp而不是sysdate保持一致 - 然后不进行任何转换。

---

---

If you only want the date part:

如果您只想要日期部分：

var v_storedate VARCHAR2(10);
begin
    select to_char(cdate, 'YYYY/MM/DD')
    into :v_storedate
    ...

You can still use execif you want to, but it's less readable once the statement gets longer than your terminal line length:

exec如果你愿意，你仍然可以使用，但是一旦语句变得比你的终端行长度更长，它的可读性就会降低：

var v_storedate VARCHAR2(10);
exec select to_char(cdate, 'YYYY/MM/DD') into :v_storedate from ... where ... ;

回答by David Aldridge

In PL/SQL a better approach to literals with single quotes in them is the quotation syntax: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/fundamentals.htm#CBJJDDCG

在 PL/SQL 中，使用单引号更好的方法是引用语法：http: //docs.oracle.com/cd/B28359_01/appdev.111/b28370/fundamentals.htm#CBJJDDCG

begin
  variable := q'#select cdate from rprt where cdate between  cdate AND TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS') and ryg='R' and cnum='C002'#'
  ...
end

... or with matching delimiters ...

... 或使用匹配的分隔符 ...

begin
  variable := q'[select cdate from rprt where cdate between  cdate AND TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS') and ryg='R' and cnum='C002']'
  ...
end

You might try that in SQL*Plus also ... not sure if it works there.

您也可以在 SQL*Plus 中尝试这样做……不确定它是否在那里工作。

回答by gthomas

Escape your inner apostrophes by doubling them!

通过将它们加倍来摆脱你内心的撇号！

回答by Romesh

• Double the inner apostrophes.
• And Change Data Typeof v_storedateto VARCHAR2(140);

• 将内部撇号加倍。
• 和变更Data Type的v_storedate，以VARCHAR2(140);