douxuan4556
2017-05-31 18:12
浏览 52
已采纳

如何在存储过程和语句之间传递日期参数?

I did this statement and it's ok

SELECT a, b 
FROM T_COMMENTS 
WHERE DATE_COMMENTS BETWEEN DATE '2014-12-05' AND DATE '2017-05-16'

But I wanted to create a stored procedure and there is a mistake:

CREATE OR REPLACE PROCEDURE FACEBOOK.PR_SEL_ALL_COM_PH
(CONSULTA OUT SYS_REFCURSOR,COD_US INT,INDATE1 IN VARCHAR2, INDATE2 IN VARCHAR2) IS
BEGIN
    OPEN CONSULTA FOR SELECT a,b FROM T_COMMENTS WHERE DATE_COMMENTS BETWEEN DATE INDATE1 AND DATE INDATE2;
END;
/

What would be the problem in my stored procedure, thanks in advance. Update 1: I'm using php. When I push date for indate1 and indate2 appears this exception

Warning: oci_execute() [function.oci-execute]: ORA-01861: literal does not match format string

How can I solve that? Thanks in advance.

图片转代码服务由CSDN问答提供 功能建议

我做了这个陈述,没关系

  SELECT a,  b 
FROM T_COMMENTS 
其他DATE_COMMENTS日期'2014-12-05'和日期'2017-05-16'
   
 
 

但我想创建一个存储过程 并且有一个错误:

  CREATE或REPLACE PROCEDURE FACEBOOK.PR_SEL_ALL_COM_PH 
(CONSULTA OUT SYS_REFCURSOR,COD_US INT,INDATE1 IN VARCHAR2,INDATE2 in VARCHAR2)IS 
BEGIN 
 在日期IND​​ATE1和DATE INDATE2之间的DATE_COMMENTS中选择a,b FROM T_COMMENTS的OPEN CONSULTA; 
END; 
 / 
   
 
 

我的存储过程中会出现什么问题? , 提前致谢。 更新1: 我正在使用php。 当我为indate1推送日期时,indate2出现此异常

 警告:oci_execute()[function.oci-execute]:ORA-01861:literal与格式字符串不匹配
    
 
 

我该如何解决? 提前致谢。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dousilie9522 2017-05-31 18:24
    已采纳

    Pass dates as dates:

    create or replace procedure facebook.pr_sel_all_com_ph
        ( consulta out sys_refcursor
        , cod_us int
        , indate1 in date
        , indate2 in date )
    is
    begin
        open consulta for
            select a,b
            from   t_comments
            where  date_comments between indate1 and indate2;
    end;
    /
    

    If for some reason you absolutely have to pass them as strings, you will need to convert these to actual dates within the procedure using to_date(indate1,'YYYY-MM-DD'), although of course this gives your procedure a whole extra validation task. (How will the caller know what format to use? What if they pass them in the wrong format? How much should the procedure check the format prior to executing the query? and so on. It's a huge pain and not really the procedure's job.)

    The reason date indate1 fails is that date literals only work with literal quoted text, not variables.

    点赞 打赏 评论