从日期属性提取日期(Extract Date from a date attribute)

所以我有这张桌子 -

create table orders ( ono number(5) not null primary key, cno number(5) references customers, eno number(4) references employees, received date, shipped date);

填入下面的脚本 -

insert into orders values (1020,1111,1000,'10-DEC-11','12-DEC-11'); insert into orders values (1021,1111,1000,'12-JAN-12','15-JAN-12'); insert into orders values (1022,2222,1001,'13-FEB-12','20-FEB-12'); insert into orders values (1023,3333,1000,'12-MAR-12',null);

现在我正在尝试创建一个PL/SQL Procedure ，它将输入作为一个月并显示该特定月份的订单。看来我不能得到EXTRACT()权利。这是我的代码块

CREATE OR REPLACE PROCEDURE TEST_REPORT (MONTH_NUMBER IN NUMBER ) AS V_MONTH VARCHAR2(10); BEGIN SELECT RECEIVED INTO V_MONTH FROM ORDERS WHERE EXTRACT(MONTH FROM TO_DATE( ORDERS.RECEIVED, 'DD-MON-RR'))=MONTH_NUMBER; END TEST_REPORT;

我在这里做错了什么？为了记录它编译和执行，但不显示任何东西。

非常感谢

尝试2

CREATE OR REPLACE PROCEDURE TEST_REPORT (MONTH_NUMBER IN NUMBER ) DECLARE CURSOR C1 IS SELECT RECEIVED INTO V_MONTH FROM ORDERS WHERE EXTRACT(MONTH FROM ORDERS.RECEIVED) = MONTH_NUMBER; V_DATE ORDERS.RECEIVED%TYPE; BEGIN OPEN C1; LOOP FETCH C1 INTO V_DATE; IF C1%FOUND THEN -- fetch succeeded DBMS_OUTPUT.PUT_LINE('DATE ' || V_DATE); ELSE -- fetch failed, so exit loop EXIT; END IF; END LOOP; END TEST_REPORT;

So I have this table--

create table orders ( ono number(5) not null primary key, cno number(5) references customers, eno number(4) references employees, received date, shipped date);

Populated with the script below--

Now I'm trying to create a PL/SQL Procedure which takes a input as a month and displays orders for that particular month. It seems I just cant get the EXTRACT() right. Here is my code block

What am I doing wrong here? For the record it compiles and executes but doesnt show anything.

Many Thanks

Attempt 2

最满意答案

由于'received'的类型为DATE，因此不应使用TO_DATE。 TO_DATE用于将字符串（VARCHAR2）解析为日期格式。确切地说，它不应该给你带来麻烦，但我已经面临着奇怪的行为。

替换以下查询：

SELECT RECEIVED INTO V_MONTH FROM ORDERS WHERE EXTRACT(MONTH FROM TO_DATE(ORDERS.RECEIVED, 'DD-MON-RR'))=MONTH_NUMBER;

有了这个：

SELECT RECEIVED INTO V_MONTH FROM ORDERS WHERE EXTRACT(MONTH FROM ORDERS.RECEIVED) = MONTH_NUMBER; DBMS_OUTPUT.PUT_LINE(V_MONTH); --Print your result.

有关EXTRACT的更多信息和示例，请参阅文档。

另外，您应该知道，如果存在多个适合您的查询的行，则会自v_month类型为VARCHAR2以来引发异常。您应该考虑使用集合或迭代游标。可以在此处找到完全符合您需求的隐式光标的简单示例。

编辑：在我们的谈话之后，这里是您的案例的解决方案：

CREATE OR REPLACE PROCEDURE TEST_REPORT (MONTH_NUMBER IN NUMBER ) AS CURSOR C1 IS SELECT RECEIVED FROM ORDERS WHERE EXTRACT(MONTH FROM ORDERS.RECEIVED) = MONTH_NUMBER; V_DATE ORDERS.RECEIVED%TYPE; BEGIN OPEN C1; LOOP FETCH C1 INTO V_DATE; IF C1%FOUND THEN -- fetch succeeded DBMS_OUTPUT.PUT_LINE('DATE ' || V_DATE); ELSE -- fetch failed, so exit loop EXIT; END IF; END LOOP; END TEST_REPORT;

Since 'received' is of type DATE, you shouldn't use TO_DATE on it. TO_DATE is used for parsing a string (VARCHAR2) into date format. To be precise, it should not cause you troubles but I've already face odd behaviors with this.

Replace the following query:

SELECT RECEIVED INTO V_MONTH FROM ORDERS WHERE EXTRACT(MONTH FROM TO_DATE(ORDERS.RECEIVED, 'DD-MON-RR'))=MONTH_NUMBER;

With this one:

SELECT RECEIVED INTO V_MONTH FROM ORDERS WHERE EXTRACT(MONTH FROM ORDERS.RECEIVED) = MONTH_NUMBER; DBMS_OUTPUT.PUT_LINE(V_MONTH); --Print your result.

Further information and examples of EXTRACT can be found in the documentation.

Also, you should be aware that in case of more than one row that fits your query, an exception will be thrown since v_month is of type VARCHAR2. You should consider using a collection or iterating a cursor. A simple example of an implicit cursor, which perfectly fits your needs, can be found here.

EDIT: Following our conversation, here's a solution for your case:

更多推荐