oracle PLSQL 并非所有变量都绑定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2176388/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PLSQL not all variables bound
提问by user250643
i keep getting the following errror, 'ORA-01008: not all variables bound', im guessign its all based on my pPostcode param but im not sure. I am a beginner the the whole PLSQL secne and any help would be greatly apriciated
我不断收到以下错误消息，“ORA-01008：并非所有变量都绑定”，我猜测所有这些都基于我的 pPostcode 参数，但我不确定。我是整个 PLSQL secne 的初学者，任何帮助都会非常有用
here is my procedure:
这是我的程序：
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;
Thanks Jon
谢谢乔恩
采纳答案by APC
I have amended your procedure slight, as the WHERE clause you published didn't make sense to me...
我稍微修改了你的程序，因为你发布的 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>
Calling it in SQL*Plus with variables works ...
在 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>
So, how can we get it to hurl an ORA-1008? By turning the query into a string and changing the way the parameter is declared...
那么，我们怎样才能让它抛出一个 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>
so let's fix that...
所以让我们解决这个问题......
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>
So I have managed to recreate an ORA-1008; I'm not sure whether it matches your ORA-1008 situation. Your intuition is right, it is something to do with how the value in pPostcodeis passed to the query. It is just that the code you posted actually does it correctly and so doesn't fail.
所以我设法重新创建了一个 ORA-1008；我不确定它是否符合您的 ORA-1008 情况。你的直觉是对的，这与pPostcode传入的值如何传递给查询有关。只是您发布的代码实际上是正确的，因此不会失败。
