PLSQL 并非所有变量都绑定

Question

我不断收到以下错误，“ORA-01008：并非所有变量都绑定”，我猜测它 全部基于我的 pPostcode 参数，但我不确定。 我是整个 PLSQL 领域的初学者，如果有任何帮助，我将不胜感激，

这是我的过程：

 procedure all_customer_postcode(pPostcode in carer.postcode%type
                                ,pReport out SYS_REFCURSOR) is
  begin
    open pReport for
      select c.id, c.forename, c.surname,c.address_1,c.address_2,
             c.address_3,c.address_4, c.postcode, c.date_of_birth, cf.id    
        from customer c, customer_friend cf,customer_friend_for cff 
       where c.id = cff.customer_id AND cff.id = cff.customer_friend_id
       AND c.postcode = pPostcode;
  end;

谢谢乔恩

Answer 1

我稍微修改了你的过程，因为你发布的 WHERE 子句对我来说没有意义...

SQL> create or replace procedure all_customer_postcode
  2          (pPostcode in customer.postcode%type
  3                                  ,pReport out SYS_REFCURSOR) is
  4  begin
  5      open pReport for
  6        select c.id
  7               , c.forename
  8               , c.surname
  9               ,c.address_1
 10               ,c.address_2
 11               ,c.postcode
 12               , c.date_of_birth
 13               , cf.id  as cf_id
 14          from customer c
 15              , customer_friend cf
 16              ,customer_friend_for cff
 17         where c.id = cff.customer_id
 18         AND cf.id = cff.customer_friend_id
 19         AND c.postcode = pPostcode;
 20  end;
 21  /

Procedure created.

SQL>

在 SQL*Plus 中使用变量调用它是可行的...

SQL> var rc refcursor
SQL> var pc varchar2(8)
SQL> exec :pc := 'ML1 4KJ'

PL/SQL procedure successfully completed.

SQL> exec all_customer_postcode(:pc, :rc)

PL/SQL procedure successfully completed.

SQL> print rc

        ID FORENAME   SURNAME    ADDRESS_1            ADDRESS_2            POSTCODE DATE_OF_B      CF_ID
---------- ---------- ---------- -------------------- -------------------- -------- --------- ----------
         1 Joe        Chip       1234 Telepath Drive  Milton Lumpky        ML1 4KJ  01-FEB-90         11
         4 Ray        Hollis     777 Telepath Drive   Milton Lumpky        ML1 4KJ  01-SEP-81         44
         5 Pat        Conley     1235 Telepath Drive  Milton Lumpky        ML1 4KJ  01-OCT-91         55

SQL>

那么，我们怎样才能让它抛出 ORA-1008 呢？通过将查询转换为字符串并更改参数的声明方式...

SQL> create or replace procedure all_customer_postcode
  2          (pPostcode in customer.postcode%type
  3                                  ,pReport out SYS_REFCURSOR) is
  4  begin
  5      open pReport for
  6        'select c.id
  7               , c.forename
  8               , c.surname
  9               ,c.address_1
 10               ,c.address_2
 11               ,c.postcode
 12               , c.date_of_birth
 13               , cf.id  as cf_id
 14          from customer c
 15              , customer_friend cf
 16              ,customer_friend_for cff
 17         where c.id = cff.customer_id
 18         AND cf.id = cff.customer_friend_id
 19         AND c.postcode = :pPostcode';
 20  end;
 21  /

Procedure created.

SQL> exec all_customer_postcode(:pc, :rc)
BEGIN all_customer_postcode(:pc, :rc); END;

*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at "APC.ALL_CUSTOMER_POSTCODE", line 5
ORA-06512: at line 1


SQL>

所以让我们修复这个问题...

SQL> create or replace procedure all_customer_postcode
  2          (pPostcode in customer.postcode%type
  3                                  ,pReport out SYS_REFCURSOR) is
  4  begin
  5      open pReport for
  6        'select c.id
  7               , c.forename
  8               , c.surname
  9               ,c.address_1
 10               ,c.address_2
 11               ,c.postcode
 12               , c.date_of_birth
 13               , cf.id  as cf_id
 14          from customer c
 15              , customer_friend cf
 16              ,customer_friend_for cff
 17         where c.id = cff.customer_id
 18         AND cf.id = cff.customer_friend_id
 19         AND c.postcode = :pPostcode' using pPostcode;
 20  end;
 21  /

Procedure created.

SQL> exec all_customer_postcode(:pc, :rc)

PL/SQL procedure successfully completed.

SQL> 

所以我成功地重新创建了一个 ORA-1008；我不确定它是否符合您的ORA-1008情况。您的直觉是正确的，这与 pPostcode 中的值如何传递给查询有关。只是您发布的代码实际上正确执行了操作，因此不会失败。