PLSQL并非所有变量都已绑定 [英] PLSQL not all variables bound
问题描述
我不断收到以下错误消息:"ORA-01008:并非所有变量都已绑定"，我猜想其 全部基于我的pPostcode参数，但我不确定. 我是一个初学者，整个PLSQL都是安全的，任何帮助都会大大增加
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
这是我的程序:
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;
感谢乔恩
推荐答案
由于您发布的WHERE子句对我没有意义，因此我对您的过程进行了小幅修改...
I have amended your procedure slight, as the WHERE clause you published didn't make sense to me...
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中使用变量调用它可以工作...
Calling it in SQL*Plus with variables works ...
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?通过将查询转换为字符串并更改参数的声明方式...
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...
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>
因此，我设法重新创建了ORA-1008；我不确定它是否符合您的ORA-1008的情况.您的直觉是正确的，这与如何将pPostcode中的值传递给查询有关.只是您发布的代码可以正确正确地执行操作，所以不会失败.
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 pPostcode is passed to the query. It is just that the code you posted actually does it correctly and so doesn't fail.
这篇关于PLSQL并非所有变量都已绑定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
