Oracle 过程调用导致“PLS-00306：调用中的参数数量或类型错误”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25293317/
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
Oracle procedure call results in "PLS-00306: wrong number or types of arguments in call"
提问by DaviRoli
I'm trying to create a procedure in SQL. It compiles correctly with no errors, but when I try to run it I keep getting the same syntax error.
我正在尝试在 SQL 中创建一个过程。它正确编译，没有错误，但是当我尝试运行它时，我不断收到相同的语法错误。
Here is the error:
这是错误：
exec reset_password(2002)
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'RESET_PASSWORD'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
My query:
我的查询：
CREATE OR REPLACE PROCEDURE reset_password
(
p_customer_id NUMBER,
p_ret_code OUT NUMBER,
p_ret_message OUT VARCHAR2
)
AS
old_password VARCHAR2(10);
new_password VARCHAR2(10);
vnumb NUMBER;
ret_code NUMBER;
ret_message VARCHAR2(50);
Begin
Select count(*)
into vnumb
from customer
where customer_id = p_customer_id;
If vnumb = 0 then
ret_code:= 11;
ret_message:= 'help';
Else
ret_code:= 69;
ret_message:= 'train';
return;
end if;
p_ret_code:= ret_code;
p_ret_message:= ret_message;
end reset_password;
CREATE TABLE customer
( CUSTOMER_ID NUMBER PRIMARY KEY,
PASSWORD VARCHAR2(20) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
EMAIL_ADDRESS VARCHAR2(50) NOT NULL,
PHONE_NUMBER VARCHAR2(15) NOT NULL,
REGISTRATION_DATE DATE NOT NULL,
EXPIRATION_DATE DATE NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL);
INSERT INTO customer
VALUES(2001, 'CpsBTKpN','SMITH','[email protected]', '3123622345',
'02-FEB-2010', '01-FEB-2015', '02-FEB-2010');
INSERT INTO customer
VALUES(2002, 'ZWNWnQJT9', 'JONES', '[email protected]', '6302077890',
'03-MAR-2010', '02-MAR-2015', '31-DEC-2010');
INSERT INTO customer
VALUES(2003, 'gc88Wmvpx', 'MILLER', '[email protected]', '6303551234',
'09-APR-2010', '08-APR-2015', '09-APR-2010');
INSERT INTO customer
VALUES(2004, 'KcxweSYg555', 'JOHNSON', '[email protected]', '7732015678',
'22-APR-2010', '21-APR-2015', '22-APR-2010');
INSERT INTO customer
VALUES(2005, 'CDYe44BBXd', 'CLARK', '[email protected]', '8476391001',
'18-MAY-2010', '17-MAY-2015', '18-MAY-2010');
INSERT INTO customer
VALUES(2006, 'xGqmkw345zr', 'LEWIS', '[email protected]', '2246166666',
'20-MAY-2010', '19-MAY-2015', '20-MAY-2010');
INSERT INTO customer
VALUES(2007, 'Y79zAHQwcB', 'KING', '[email protected]', '3018551234',
'30-JUN-2010', '29-JUN-2015', '30-JUN-2010');
INSERT INTO customer
VALUES(2008, 'vhSDHMDg66', 'SCOTT', '[email protected]', '7701239876',
'30-AUG-2010', '30-DEC-2011', '30-DEC-2011');
COMMIT;
回答by Patrick Bacon
To run your procedure, one can create an anonymous plsql block with a declaration associated with the out parameters:
要运行您的过程，可以创建一个匿名 plsql 块，其中包含与 out 参数关联的声明：
p_ret_code OUT NUMBER,
p_ret_message OUT VARCHAR2
Apparently, you are not passing three parameters (two being out) with your procedural invocation. This is why you are receiving the error:
显然，您没有在过程调用中传递三个参数（两个参数）。这就是您收到错误的原因：
PLS-00306: wrong number or types of arguments in call to 'RESET_PASSWORD'
I use l_ret_code and l_ret_message with matching data types.
我使用具有匹配数据类型的 l_ret_code 和 l_ret_message。
DECLARE
l_ret_code NUMBER;
l_ret_message VARCHAR(50);
BEGIN
reset_password ( 2002, l_ret_code, l_ret_message);
END;
If one would like to use the execute command to run the procedure with the out parameters, using bind variables will work:
如果想使用 execute 命令运行带有 out 参数的过程，使用绑定变量将起作用：
var l_ret_code NUMBER;
var l_ret_message VARCHAR2(50);
EXECUTE reset_password ( 2002, :l_ret_code, :l_ret_message);
