select max(decode(action,0,apply_date,null)),max(decode(action,1,apply_date,null))
into Stop_date,Reserve_date
from serv_stop_reserve
where serv_id = curServ.serv_id;
我现在是取动作分别为0和1的最近一次的时间,但是结果发现程序select into 后没有了时分秒,
有高手知道吗,重分感谢
into Stop_date,Reserve_date
from serv_stop_reserve
where serv_id = curServ.serv_id;
我现在是取动作分别为0和1的最近一次的时间,但是结果发现程序select into 后没有了时分秒,
有高手知道吗,重分感谢
(
STOP_DATE DATE ,
RESERVE_DATE DATE ,
STATE VARCHAR2(3)
);
curServStop Da$serv_stop_reserve;
select max(decode(action,0,apply_date,null)),max(decode(action,1,apply_date,null))
into curServStop.Stop_date,curServStop.Reserve_date
from serv_stop_reserve
where serv_id = curServ.serv_id;
以上是完整的代码,高手们出招吧
而是into有影响
declare
STOP_DATE varchar2(20) ;
RESERVE_DATE DATE;
begin
select to_char(max(decode(no,1,in_date,null)),'yyyy-mm-dd hh:mi:ss'),max(decode(no,2,in_date,null))
into stop_date,RESERVE_DATE
from test_4;
dbms_output.put_line(stop_date||','||RESERVE_DATE);end; 这是输出结果:
-------------------------------------
2005-11-09 05:11:51,08-11月-05
Compilation errors for PROCEDURE BILL.DATA_PREPROCESS_SCRIPT_NEWError: PLS-00307: 有太多的 'TO_CHAR' 说明与此次调用相匹配
Line: 159
Text: SELECT to_char(max(decode(action,0,apply_date,null)),'yyyy-mm-dd hh:mi:ss'),Error: PL/SQL: SQL Statement ignored
Line: 159
Text: SELECT to_char(max(decode(action,0,apply_date,null)),'yyyy-mm-dd hh:mi:ss'),为什么呢
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 NUMBER
C2 DATESQL> alter session set nls_date_format='yyyy-mm-dd';
SQL> select rownum,c1,to_char(c2,'yyyy-mm-dd hh24:mi:ss') from t_test order by 3; ROWNUM C1 TO_CHAR(C2,'YYYY-MM
---------- ---------- -------------------
14 0 2005-11-10 10:58:31
9 1 2005-11-10 12:17:22
8 0 2005-11-10 19:11:36
13 1 2005-11-10 21:10:01
7 1 2005-11-10 22:09:08
6 0 2005-11-11 01:26:24
12 0 2005-11-11 01:46:11
5 1 2005-11-11 06:22:16
4 0 2005-11-11 09:00:05
11 1 2005-11-11 10:38:46
3 1 2005-11-11 11:37:53 ROWNUM C1 TO_CHAR(C2,'YYYY-MM
---------- ---------- -------------------
2 0 2005-11-11 15:14:52
10 0 2005-11-11 18:51:54
1 1 2005-11-11 19:11:3014 rows selected.SQL> declare
2 d1 date;
3 d2 date;
4 begin
5 select max(decode(c1,0,c2,null)),max(decode(c1,1,c2,null)) into d1,d2 from t_test;
6 dbms_output.put_line(to_char(d1,'yyyy-mm-dd hh24:mi:ss'));
7 dbms_output.put_line(to_char(d2,'yyyy-mm-dd hh24:mi:ss'));
8 end;
9 /
2005-11-11 18:51:54
2005-11-11 19:11:30PL/SQL procedure successfully completed.
2 d1 date;
3 d2 date;
4 begin
5 select max(decode(c1,0,c2,null)),max(decode(c1,1,c2,null)) into d1,d2 from t_test1;
6 dbms_output.put_line(to_char(d1,'yyyy-mm-dd hh24:mi:ss'));
7 dbms_output.put_line(to_char(d2,'yyyy-mm-dd hh24:mi:ss'));
8 end;
9 /Procedure created.SQL> select rownum,c1,to_char(c2,'yyyy-mm-dd hh24:mi:ss') from t_test1 order by 3; ROWNUM C1 TO_CHAR(C2,'YYYY-MM
---------- ---------- -------------------
1 1 2005-11-08 15:18:04
2 0 2005-11-09 18:28:42
3 1 2005-11-09 23:27:24
4 0 2005-11-10 03:18:12
5 1 2005-11-10 06:54:13
6 0 2005-11-10 11:18:13
7 1 2005-11-10 12:21:05
14 0 2005-11-10 12:21:16
10 0 2005-11-10 14:26:49
8 0 2005-11-10 14:58:14
11 1 2005-11-10 16:09:41 ROWNUM C1 TO_CHAR(C2,'YYYY-MM
---------- ---------- -------------------
13 1 2005-11-10 16:22:53
9 1 2005-11-10 17:00:27
12 0 2005-11-10 17:35:2414 rows selected.SQL> set serveroutput on
SQL> exec p_test;
2005-11-10 17:35:24
2005-11-10 17:00:27PL/SQL procedure successfully completed.SQL>
declare
STOP_DATE varchar2(20) ;
RESERVE_DATE DATE;
begin
select to_char(max(decode(no,1,in_date,null)),'yyyy-mm-dd hh:mi:ss'),max(decode(no,2,in_date,null))
into stop_date,RESERVE_DATE
from test_4;
dbms_output.put_line(stop_date||','||to_char(RESERVE_DATE,'yyyy-mm-dd hh24:mi:ss'));end; 输出:
2005-11-11 04:27:56,2005-11-10 16:27:59这说明和decode或max还有into都没关系,只是显示格式的问题,格式化一下就ok了