代码之家 › 专栏 › 技术社区 › John Wick

为什么“select into a variable”语句使存储过程不向表中插入任何内容?

plsql oracle sql

John Wick · 技术社区 · 7 年前

所以我现在有一个存储过程,它对一个名为“value”的表进行简单的插入。我测试了这件作品并称之为以下程序:

Create Or Replace Procedure TEST_PROCEDURE(rValue_tx IN VARCHAR, rData_Type IN VARCHAR)
IS
BEGIN
IF rValue_Tx >= 0 THEN
                IF rData_Type in ('TEST', 'REAL')
                THEN
                Insert into Value (VALUE_ID, VALUE_TX, CREATE_DT)
                VALUES (1, rValue_tx, SYSDATE);
END IF;
END IF;
END TEST_PROCEDURE;

但是,当我试图在变量中插入一个数字(添加另一层复杂性)时,什么也不会插入。下面是添加了另一层复杂性的代码:

Create Or Replace Procedure TEST_PROCEDURE_NEW(rValue_tx IN VARCHAR, rData_Type IN VARCHAR)
IS
v_MAX_historic_value value.value_tx%type;
BEGIN
---
SELECT MAX(BUFFER_MAX)
  INTO v_MAX_Historic_Value
  FROM max_look_up;
EXCEPTION
     WHEN no_data_found
     THEN SELECT 0
            INTO v_MAX_Historic_Value
            FROM DUAL;
---
IF rValue_Tx >= 0 
THEN
                IF rData_Type in ('TEST', 'REAL')
                THEN
                Insert into Value (VALUE_ID, VALUE_TX, CREATE_DT)
                VALUES (null, rValue_tx, SYSDATE);
END IF;
END IF;
END TEST_PROCEDURE_NEW;

我确信我遗漏了一些关于pl/sql的基本规则,但似乎无法确定哪条规则。这似乎是导致过程不向值表中插入任何行的异常情况,但我不确定原因(或者是否有更好的方法?)…如有任何帮助,我们将不胜感激:(

当max look up中没有数据,结果返回nothing/null时,我是否有更好的方法来处理?

3 回复 | 直到 7 年前

hmmftg Stands with Palestine 7 年前

CREATE OR REPLACE PROCEDURE TEST_PROCEDURE_NEW (
                                                RVALUE_TX    IN VARCHAR,
                                                RDATA_TYPE   IN VARCHAR
                                               )
IS
   V_MAX_HISTORIC_VALUE   VALUE.VALUE_TX%TYPE;
BEGIN
   ---
   BEGIN
      SELECT MAX (BUFFER_MAX) INTO V_MAX_HISTORIC_VALUE FROM MAX_LOOK_UP;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         SELECT 0 INTO V_MAX_HISTORIC_VALUE FROM DUAL;
   END;

   ---
   IF RVALUE_TX >= 0
   THEN
      IF RDATA_TYPE IN ('TEST', 'REAL')
      THEN
         INSERT INTO VALUE (VALUE_ID, VALUE_TX, CREATE_DT)
         VALUES (NULL, RVALUE_TX, SYSDATE);
      END IF;
   END IF;
END TEST_PROCEDURE_NEW;

Codo 7 年前

SELECT MAX(...) NO_DATA_FOUND INSERT

Create Or Replace Procedure TEST_PROCEDURE_NEW(rValue_tx IN VARCHAR, rData_Type IN VARCHAR)
IS
    v_MAX_historic_value value.value_tx%type;
BEGIN

    SELECT MAX(BUFFER_MAX)
        INTO v_MAX_Historic_Value
        FROM max_look_up;

EXCEPTION
    WHEN no_data_found
    THEN
        SELECT 0
            INTO v_MAX_Historic_Value
            FROM DUAL;

        IF rValue_Tx >= 0 THEN
            IF rData_Type in ('TEST', 'REAL') THEN
                Insert into Value (VALUE_ID, VALUE_TX, CREATE_DT)
                    VALUES (null, rValue_tx, SYSDATE);
            END IF;
        END IF;
END TEST_PROCEDURE_NEW;

BEGIN END SELECT NULL MAX(...)

Create Or Replace Procedure TEST_PROCEDURE_NEW(rValue_tx IN VARCHAR, rData_Type IN VARCHAR)
IS
    v_MAX_historic_value value.value_tx%type;
BEGIN

    SELECT MAX(BUFFER_MAX)
        INTO v_MAX_Historic_Value
        FROM max_look_up;

    IF v_MAX_Historic_Value IS NULL THEN
        v_MAX_Historic_Value := 0;
    END IF;

    ...


    IF rValue_Tx >= 0 THEN
        IF rData_Type in ('TEST', 'REAL') THEN
            Insert into Value (VALUE_ID, VALUE_TX, CREATE_DT)
                VALUES (null, rValue_tx, SYSDATE);
        END IF;
    END IF;
END TEST_PROCEDURE_NEW;

John Wick 7 年前

Create Or Replace Procedure TEST_PROCEDURE_NEW(rValue_tx IN VARCHAR, rData_Type IN VARCHAR)
IS
v_MAX_historic_value value.value_tx%type;
BEGIN

with EXEC as
(select 0 buffer_max
   from dual
  UNION
 select MAX(BUFFER_MAX)
    FROM max_look_up) select max(buffer_max) into v_MAX_Historic_Value from EXEC;
IF rValue_Tx >= 0 THEN
    IF rData_Type in ('TEST', 'REAL') THEN
        Insert into Value (VALUE_ID, VALUE_TX, CREATE_DT)
            VALUES (null, rValue_tx, SYSDATE);
        END IF;
    END IF;
END TEST_PROCEDURE_NEW;