oracle 如何将日期值传递给 plsql 中的游标？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5773494/
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
How do I pass a date value to a cursor in plsql?
提问by user83598
Basically I would like to pass a date value to a cursor, and print out the entire row/record after for each found. I am having trouble because a) I don't know if my date is being converted properly in the BEGIN section, and b) I am getting "wrong number or types of arguments in call to 'PUT_LINE'" when printing each row.
基本上我想将一个日期值传递给一个游标，并在每次找到后打印出整个行/记录。我遇到了麻烦，因为 a) 我不知道我的日期是否在 BEGIN 部分正确转换，并且 b) 在打印每一行时，我收到“调用 'PUT_LINE' 的参数数量或类型错误”。
This is what I have so far:
这是我到目前为止：
DEFINE B_HIREDATE = 11-OCT-88
DECLARE
cursor DATE_CUR (the_date DATE) is
select * from employees
where hire_date > to_date(the_date, 'dd-mon-yy')
order by hire_date;
r_emp DATE_CUR%ROWTYPE;
BEGIN
for r_emp IN DATE_CUR('&B_HIREDATE') LOOP
dbms_output.put_line(r_emp);
end LOOP;
END;
/
I am getting no output values, even if I change my select statement to a known single field name.
即使我将 select 语句更改为已知的单个字段名称，我也没有得到任何输出值。
回答by Alex Poole
You can't print out the whole row from a single DBMS_OUTPUTcall, unfortunately; you'll need to print each column returned by the cursor individually. PUT_LINEexpects a VARCHAR2argumentor something that can be implicitly converted. You can concatenate several values into one call. Nice formatting isn't easy though.
DBMS_OUTPUT不幸的是，您无法从一次调用中打印出整行；您需要单独打印游标返回的每一列。PUT_LINE期望一个VARCHAR2参数或可以隐式转换的东西。您可以将多个值连接到一个调用中。好的格式并不容易。
The date conversion is almost OK, but you should have the TO_DATEin the cursor call, as the cursor parameter is expecting a DATE; and you should use RRinstead of YYin your date mask, or preferably use 4-digit years and mask YYYY.
日期转换几乎没问题，但是您应该TO_DATE在游标调用中使用 ，因为游标参数需要DATE; 并且您应该在日期掩码中使用RR而不是YY，或者最好使用 4 位年份和掩码YYYY。
SET SERVEROUTPUT ON
DEFINE B_HIREDATE = 11-OCT-1988
DECLARE
cursor DATE_CUR (the_date DATE) is
select * from employees
where hire_date > the_date
order by hire_date;
BEGIN
for r_emp IN DATE_CUR(TO_DATE('&B_HIREDATE','DD-MON-YYYY')) LOOP
dbms_output.put_line(r_emp.hire_date);
end LOOP;
END;
You don't need to explicitly declare the r_empas a variable with this cursor syntax (but you would with the OPEN/FETCH/CLOSEversion).
你并不需要显式声明r_emp与此游标语法一个变量（但你与OPEN/ FETCH/CLOSE版）。
If you're running this in SQL*Plus, you need to add SET SERVEROUTPUT ONat the start to allow the DBMS_OUTPUTcalls to be displayed. You can do that in SQL Developer too, or there's a separate pane for viewing the output, which you need to enable for the worksheet.
如果您在 SQL*Plus 中运行它，您需要SET SERVEROUTPUT ON在开始时添加以允许DBMS_OUTPUT显示调用。您也可以在 SQL Developer 中执行此操作，或者有一个单独的窗格用于查看输出，您需要为工作表启用该窗格。
