我可以在存储过程中的游标查询中使用timestamp变量吗? [英] I can use a timestamp variable on query of cursor in a store procedure?

问题描述

我需要一个时间戳变量，但会发生变化，但是当我尝试将此时间戳变量传递给statement时，这是行不通的:

I need a timestamp variable that whill change dinamicaly, but when i try pass this timestamp variable to statement , this doesn't work:

没有时间戳变量，它可以工作:

without a timestamp variable ,it works:

CREATE OR REPLACE
PROCEDURE "PR_TEST"( sl_cursor OUT SYS_REFCURSOR)  
IS
    stm VARCHAR2(3000);
    var_ativo number:= 1 ;
BEGIN
    stm := 'SELECT
               *
        FROM
            SIMET.TB_1 SC
            JOIN SIMET.TB_2 D ON D.HASH = SC.HASH
        WHERE
             SC.IS_ACTIVE = ' || var_ativo ;            
     OPEN sl_cursor FOR  stm ;
END;

但是使用timestamp var，它不起作用:

but with the timestamp var , it doesnt work:

CREATE OR REPLACE
PROCEDURE "PR_TEST"( sl_cursor OUT SYS_REFCURSOR)  
IS
    stm VARCHAR2(3000);
    var_ativo number:= 1 ;
    var_ts timestamp := SYSTIMESTAMP
BEGIN
    stm := 'SELECT
               *
        FROM
            SIMET.TB_1 SC
            JOIN SIMET.TB_2 D ON D.HASH = SC.HASH
        WHERE
             SC.IS_ACTIVE = ' || var_ativo 
         AND 
             sc.timestamp = ' || var_ts

     OPEN sl_cursor FOR  stm ;
END;

有人知道我该怎么做吗?

Someone knows how i could do it?

推荐答案

直接的问题是您没有正确连接字符串.您还需要将AND作为文字的一部分.而且您缺少分号.

The immediate problem is that you aren't concatenating your string properly; you need to have the AND as part of the literal too. And you're missing a semicolon.

stm := 'SELECT
           *
    FROM
        SIMET.TB_1 SC
        JOIN SIMET.TB_2 D ON D.HASH = SC.HASH
    WHERE
         SC.IS_ACTIVE = ' || var_ativo || '
     AND 
         sc.timestamp = ' || var_ts;

但是现在您正在将v_ts变量隐式转换为字符串.作为字符串，需要将其用转义的单引号引起来:

But now you are implicitly converting your v_ts variable to a string. As a string it would need to be enclosed in escaped single quotes:

         sc.timestamp = ''' || var_ts || '''';

但是，这涉及更多的隐式转换以与列值进行比较，因此，实际上，您希望将其显式转换为两种方式.您可能会想到，这是您做错了事的一个危险信号.

But that involves more implicit conversion to compare with the column values so really you'd want to explicitly convert it both ways. Which, as you might imagine, is a bit of a red-flag that you're doing something wrong.

如果使用动态SQL，则应使用绑定变量:

If you are using dynamic SQL then you should use bind variables:

stm := 'SELECT
           *
    FROM
        SIMET.TB_1 SC
        JOIN SIMET.TB_2 D ON D.HASH = SC.HASH
    WHERE
         SC.IS_ACTIVE = :ativo
     AND 
         sc.timestamp = :ts';

 OPEN sl_cursor FOR stm USING var_ativo, var_ts;

但是从显示的内容来看，您根本不应该在这里使用动态SQL:

But from what you've shown you should not be using dynamic SQL here at all:

 OPEN sl_cursor FOR
    SELECT
           *
    FROM
        SIMET.TB_1 SC
        JOIN SIMET.TB_2 D ON D.HASH = SC.HASH
    WHERE
         SC.IS_ACTIVE = var_ativo 
     AND 
         sc.timestamp = var_ts;

引用Tom Kyte的话:"仅在完全没有静态方法静态地使用动态SQL时，才使用动态SQL."

To quote Tom Kyte: "You use dynamic sql only when there is quite simply NO WAY to do it statically."

您的前提是，将您正在做的事情拆分为较小的查询和更新可能是不正确的，并且您可能会使其整体运行速度变慢；但这是一个单独的问题，需要更多的信息来解决.

Your premise that splitting whatever you're doing into smaller queries and updates is probably incorrect, and you're likely to be making it slower overall; but that's a separate issue and something that would need a lot more information to address.

这篇关于我可以在存储过程中的游标查询中使用timestamp变量吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！