oracle sysdate在where子句中

问题描述：

我试图在oracle数据库的触发器的where子句中使用SYSDATE，但代码引发“ORA-01722：invalid number”异常。oracle sysdate在where子句中

SELECT 'ATT' || '-' || TO_CHAR(SYSDATE, 'YY') || '-' 
     || LPAD((SELECT TO_NUMBER(COUNT(*)) 
       FROM ATT_REQUEST_DATA 
       WHERE REQID like ('ATT-' + TO_CHAR(SYSDATE, 'YY') + '-%')) + 1, '4', '0')

你知道，我应该怎么修复呢？

这里是表的定义

CREATE TABLE ATT_REQUEST_DATA 
(REQID VARCHAR2(50), 
    REQ_SUBID NUMBER, 
    FLAG_ID NUMBER, 
    DATE_FROM DATE, 
    DATE_TO DATE, 
    CREATED_DATE DATE, 

    CONSTRAINT ATT_REQUEST_DATA_PK PRIMARY KEY (REQID, REQ_SUBID) 
); 
/

这里是插入数据

INSERT INTO ATT_REQUEST_DATA (FLAG_ID, DATE_FROM, DATE_TO, CREATED_DATE) 
VALUES (2, SYSDATE, SYSDATE, SYSDATE);

和这里的一个例子是误差输出

ORA-01722：无效号码
ORA-06512：在 “PD.ATT_REQUEST_DATA_TRG_ID”，第4行
ORA-04088：触发的执行期间错误 'PD.ATT_REQUEST_DATA_TRG_ID'

这里是整个触发

CREATE OR REPLACE TRIGGER ATT_REQUEST_DATA_TRG_ID 
BEFORE INSERT ON ATT_REQUEST_DATA FOR EACH ROW 
BEGIN 
    IF (:NEW.REQID IS NULL) THEN 

     SELECT 'ATT' || '-' || TO_CHAR(SYSDATE, 'YY') || '-' || LPAD((SELECT TO_NUMBER(COUNT(*)) FROM ATT_REQUEST_DATA WHERE REQID like ('ATT-' + TO_CHAR(SYSDATE, 'YY') + '-%')) + 1, '4', '0') 
     INTO :NEW.REQID 
     FROM DUAL; 

     SELECT (SELECT COUNT(*) FROM ATT_REQUEST_DATA WHERE REQID = :NEW.REQID) + 1 
     INTO :NEW.REQ_SUBID 
     FROM DUAL; 


    END IF; 

END; 
/

请添加'ATT_REQUEST_DATA'表的结构，一些样本数据和期望的输出。您不需要为'COUNT（*）'使用'TO_NUMBER'。 'COUNT'已经给你一个NUMBER。 –

请加上它。不要添加评论。还要添加一些你想要的样本数据和输出 –

你触发器中的代码与你发布的原始SQL不匹配 – APC

答

这不是使用SYSDATE导致你的问题

你的代码有几个怪事。首先是这个宝石：TO_NUMBER(COUNT(*))。 count()返回一个数字，因此不需要施放它;幸运的是，Oracle很聪明，知道这一点，所以这不是ORA-01722错误的根源。

不，那是因为这些操作：('ATT-' + TO_CHAR(SYSDATE, 'YY') + '-%'))。在Oracle中SQL +是一个算术运算符：SQL引擎认为你想要加'ATT-'到TO_CHAR(SYSDATE, 'YY')，我们不能在字符串上进行加法。为了解决这个问题，与连接符||更换+：

('ATT-' || TO_CHAR(SYSDATE, 'YY') || '-%'))

答

试试这个：

with 
    p as (select 'ATT' || '-' || TO_CHAR(SYSDATE, 'YY') || '-' pattern from dual) 
    , cnt as ( 
    SELECT pattern, COUNT(*) cnt 
    FROM ATT_REQUEST_DATA cross join p 
    WHERE REQID like pattern || '%') 
    , res as (select pattern || LPAD( cnt + 1, '4', '0') from cnt) 
select * from res;

看到http://sqlfiddle.com/#!4/c971a6/5

注意，我从句用来分解出乌尔SQL的子部分纳入更小的块使调试更容易。你可以安全地把它重组成一个Select而不需要WITH子句

oracle sysdate在where子句中

相关推荐