如何将日期值传递给plsql中的游标? [英] How do I pass a date value to a cursor in plsql?

问题描述

基本上，我想将日期值传递给游标，然后为找到的每个行打印出整个行/记录.我遇到了麻烦，因为a)我不知道我的日期是否在BEGIN部分中正确转换了，并且b)打印每一行时，我收到调用'PUT_LINE'时参数或参数类型错误"的信息. /p>

这是我到目前为止所拥有的:

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;
/

即使将select语句更改为已知的单个字段名称，我也没有输出值.

解决方案

很遗憾，您无法从单个DBMS_OUTPUT调用中打印出整行.您需要分别打印光标返回的每一列. PUT_LINE 期望VARCHAR2参数或可以隐式转换的内容.您可以将多个值连接到一个调用中.好的格式并不容易.

日期转换几乎可以，但是您应该在游标调用中包含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;

您无需使用此游标语法将r_emp显式声明为变量(但是使用OPEN/FETCH/CLOSE版本则需要).

如果在SQL * Plus中运行它，则需要在开始时添加SET SERVEROUTPUT ON以允许显示DBMS_OUTPUT调用.您也可以在SQL Developer中执行此操作，或者有一个单独的窗格用于查看输出，您需要为工作表启用该窗格.

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.

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.

解决方案

You can't print out the whole row from a single DBMS_OUTPUT call, unfortunately; you'll need to print each column returned by the cursor individually. PUT_LINE expects a VARCHAR2 argument or something that can be implicitly converted. You can concatenate several values into one call. Nice formatting isn't easy though.

The date conversion is almost OK, but you should have the TO_DATE in the cursor call, as the cursor parameter is expecting a DATE; and you should use RR instead of YY in your date mask, or preferably use 4-digit years and mask 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_emp as a variable with this cursor syntax (but you would with the OPEN/FETCH/CLOSE version).

If you're running this in SQL*Plus, you need to add SET SERVEROUTPUT ON at the start to allow the DBMS_OUTPUT calls 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.

这篇关于如何将日期值传递给plsql中的游标?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！