时间戳

首页 > 解决方案 > 如何在 PL/SQL 游标中给出参数

oracle - 如何在 PL/SQL 游标中给出参数

问题描述

我有一个 8k 行的表，分成 1k 的子集。

程序将以这种方式调用 EXEC BONIFICA_GECT(0); 即，我只需要对子集=0 运行脚本内的命令（字段称为乐透）

INSERT INTO T_TEST_CI_CO (customerid,customercode) ValueS(v_cid,v_cod);

我尝试创建程序，但我不明白如何在光标上给出输入值

CREATE OR REPLACE PROCEDURE BONIFICA_GECT(LOTTO IN NUMBER)
IS

CURSOR id IS
SELECT numero_pratica FROM bonifica1_GECT_2020 WHERE 1=1 lotto=LOTTO
                                               AND (new_status_gect IS NULL OR new_status_gect='PARTIAL_LOAD');

v_pratica bonifica1_GECT_2020.numero_pratica%TYPE;
v_cid     bonifica1_GECT_2020.customerid%TYPE;
v_cod      bonifica1_GECT_2020.customercode%TYPE;
v_lotto NUMBER;
V_CHECK_LOTTO NUMBER;

BEGIN

    OPEN id;
    LOOP
       fetch id INTO v_pratica;
       EXIT WHEN id%NOTFOUND;

       SELECT customerid,customercode INTO v_cid,v_cod
       FROM  bonifica1_GECT_2020
       WHERE numero_pratica=v_pratica;

       INSERT INTO T_TEST_CI_CO (customerid,customercode) ValueS(v_cid,v_cod);

    END LOOP;
    CLOSE id;

COMMIT;

EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        NULL;
    WHEN OTHERS
    THEN
        /* Consider logging the error and then re-raise */
        RAISE;

END BONIFICA_GECT;

标签： oracleplsql

解决方案

您的光标当前有（忽略流浪1=1）：

CURSOR id IS
SELECT numero_pratica FROM bonifica1_GECT_2020 WHERE lotto=LOTTO
                                               AND (new_status_gect IS NULL OR new_status_gect='PARTIAL_LOAD');

两边都lotto=LOTTO指表中的列，所以始终为真（除非列值为空）；双方都没有看到同名的参数参数。

您需要给参数一个不同的名称，例如：

CREATE OR REPLACE PROCEDURE BONIFICA_GECT(P_LOTTO IN NUMBER)
IS
  CURSOR id IS
    SELECT numero_pratica
    FROM bonifica1_GECT_2020
    WHERE lotto = P_LOTTO
    AND (new_status_gect IS NULL OR new_status_gect='PARTIAL_LOAD');
...

您可以改为使用对象名称作为前缀：

    WHERE bonifica1_GECT_2020.lotto = BONIFICA_GECT.LOTTO

但我更喜欢给参数和变量名加上更简单的前缀。

我不确定你为什么只从光标中获取 ID，然后从同一个表中查找其他值；你可以这样做：

CREATE OR REPLACE PROCEDURE BONIFICA_GECT(P_LOTTO IN NUMBER)
IS
  CURSOR v_cursor IS
    SELECT numero_pratica, customerid, customercode
    FROM bonifica1_GECT_2020
    WHERE lotto = P_LOTTO
    AND (new_status_gect IS NULL OR new_status_gect='PARTIAL_LOAD');

  v_pratica bonifica1_GECT_2020.numero_pratica%TYPE;
  v_cid     bonifica1_GECT_2020.customerid%TYPE;
  v_cod     bonifica1_GECT_2020.customercode%TYPE;
BEGIN

  OPEN v_cursor;
  LOOP
     FETCH v_cursor INTO v_pratica, v_cid, v_cod;
     EXIT WHEN v_cursor%NOTFOUND;

     INSERT INTO T_TEST_CI_CO (customerid,customercode
     VALUES (v_cid, v_cod);

  END LOOP;
  CLOSE v_cursor;
END BONIFICA_GECT;

你不能用这个（或你的原件）得到 no-data-found ；除非您确实需要将其记录在某个地方，否则捕捉其他人不是一个好主意。

您可以将光标本身参数化；再次选择不同的前缀/名称：

CREATE OR REPLACE PROCEDURE BONIFICA_GECT(P_LOTTO IN NUMBER)
IS
  CURSOR v_cursor (C_LOTTO NUMBER) IS
    SELECT numero_pratica, customerid, customercode
    FROM bonifica1_GECT_2020
    WHERE lotto = C_LOTTO
    AND (new_status_gect IS NULL OR new_status_gect='PARTIAL_LOAD');

  v_pratica bonifica1_GECT_2020.numero_pratica%TYPE;
  v_cid     bonifica1_GECT_2020.customerid%TYPE;
  v_cod     bonifica1_GECT_2020.customercode%TYPE;
BEGIN

  OPEN v_cursor (P_LOTTO);
...

或者如果你真的不想有前缀，你可以指定参数来自游标：

CREATE OR REPLACE PROCEDURE BONIFICA_GECT(LOTTO IN NUMBER)
IS
  CURSOR v_cursor (LOTTO NUMBER) IS
    SELECT numero_pratica, customerid, customercode
    FROM bonifica1_GECT_2020
    WHERE lotto = v_cursor.LOTTO
    AND (new_status_gect IS NULL OR new_status_gect='PARTIAL_LOAD');
...
  OPEN v_cursor (LOTTO);
  -- or: OPEN v_cursor (BONIFICA_GECT.LOTTO);
...

无论哪种方式，只有在同一过程中重用具有不同参数的游标时，这才是真正有用的。

您还可以使用更简单的隐式游标；但我认为这是这种游标的练习。

在循环中插入不是很有效，这可以作为单个insert .. select; 甚至不需要 PL/SQL。同样，我假设这是练习的一部分。

推荐阅读