尝试创建程序包-错误PLS-00330 [英] Attempting to create a package - error PLS-00330
问题描述
很遗憾,我正在尝试创建一个包含函数和过程的程序包,并且发生了以下错误
I am attempting to create a package with functions and procedures unfortunately I have encountered issue and the following error has occurred
PLS-00330:类型名称或子类型名称的无效使用编译失败,第11行(19:53:17)
PL/SQL:语句被忽略
PLS-00330: invalid use of type name or subtype nameCompilation failed,line 11 (19:53:17)
PL/SQL: Statement ignored
我已阅读并进行了研究以尝试解决此问题,但尚未能够解决.
I have read it and done research to try fix the issue but have have not been able to fix it.
包装规格:
create or replace package e_con_pack
AS
FUNCTION GET_LOCATION_namel (p_con_id in NUMBER, p_con_name out VARCHAR2)RETURN NUMBER;
PROCEDURE ADD_CONSULTANT ( p_con_id LDS_CONSULTANT.CONSULTANT_ID%type,
p_con_name LDS_CONSULTANT.CST_NAME%type,
p_con_start LDS_CONSULTANT.START_DATE%type,
p_con_end LDS_CONSULTANT.LEAVE_DATE%type,
p_con_loc LDS_CONSULTANT.LOCATION%type,
p_con_spec LDS_CONSULTANT.SPECIALIST_AREA%type);
PROCEDURE update_location(p_con_id IN
lds_consultant.consultant_id%type,
p_con_loc IN lds_consultant.location%type);
PROCEDURE delete_con (p_con_id IN
lds_consultant.consultant_id%type);
end e_con_pack;
包装体:
create or replace PACKAGE BODY e_con_pack
AS
FUNCTION
GET_LOCATION_namel (p_con_id in NUMBER, p_con_name out VARCHAR2)
RETURN NUMBER IS
LOCATION VARCHAR2(30);
BEGIN
SELECT LOCATION INTO LOCATION FROM LDS_CONSULTANT WHERE CONSULTANT_ID = p_con_id;
SELECT CST_NAME INTO p_con_name FROM LDS_CONSULTANT WHERE CONSULTANT_ID = p_con_id;
RETURN NUMBER;
END GET_LOCATION_namel;
PROCEDURE ADD_CONSULTANT
( p_con_id LDS_CONSULTANT.CONSULTANT_ID%type,
p_con_name LDS_CONSULTANT.CST_NAME%type,
p_con_start LDS_CONSULTANT.START_DATE%type,
p_con_end LDS_CONSULTANT.LEAVE_DATE%type,
p_con_loc LDS_CONSULTANT.LOCATION%type,
p_con_spec LDS_CONSULTANT.SPECIALIST_AREA%type)
IS
BEGIN
INSERT INTO LDS_CONSULTANT (CONSULTANT_ID, CST_NAME, START_DATE, LEAVE_DATE, LOCATION, SPECIALIST_AREA)
VALUES (p_con_id, p_con_name, p_con_start, p_con_end, p_con_loc, p_con_spec);
END ADD_CONSULTANT;
PROCEDURE update_location (
p_con_id IN lds_consultant.consultant_id%type,
p_con_loc IN lds_consultant.location%type
)
IS
BEGIN
UPDATE lds_consultant
SET
location = p_con_loc
WHERE consultant_id = p_con_id;
END update_location;
PROCEDURE delete_con (
p_con_id IN lds_consultant.consultant_id%type )
As
Begin
Delete from LDS_CONSULTANT Where CONSULTANT_ID=p_con_id;
END delete_con;
END e_con_pack;
推荐答案
该错误是针对程序包主体的第11行报告的,即
The error is reported against line 11 of the package body, which is
RETURN NUMBER;
数字是数据类型,而不是变量名.目前尚不清楚您实际想要返回的是什么;当您查询和捕获它时,可能是LOCATION
;但由于该字符串是字符串,因此该函数的返回类型也需要为字符串(即VARCHAR2
而不是NUMBER
).也许:
Number is a data type, not a variable name. It isn't clear what you actually want to return here; as you are querying and capturing it, perhaps LOCATION
; but as that is a string, the return type of the function would also need to be a string (i.e. VARCHAR2
rather than NUMBER
). Perhaps:
create or replace PACKAGE BODY e_con_pack
AS
FUNCTION
GET_LOCATION_namel (p_con_id in NUMBER, p_con_name out VARCHAR2)
RETURN VARCHAR2 IS
LOCATION VARCHAR2(30);
BEGIN
SELECT LOCATION INTO LOCATION FROM LDS_CONSULTANT WHERE CONSULTANT_ID = p_con_id;
SELECT CST_NAME INTO p_con_name FROM LDS_CONSULTANT WHERE CONSULTANT_ID = p_con_id;
RETURN LOCATION;
END GET_LOCATION_namel;
或更妙的是,使用列数据类型,就像在其他地方一样:
Or even better, use the column data type as you already are elsewhere:
create or replace PACKAGE BODY e_con_pack
AS
FUNCTION
GET_LOCATION_namel (p_con_id in NUMBER, p_con_name out VARCHAR2)
RETURN LDS_CONSULTANT.LOCATION%type IS
LOCATION LDS_CONSULTANT.LOCATION%type;
BEGIN
...
无论哪种方式,您都需要更改包装规格以使其匹配.
Either way, you will need to change the package specification to match, of course.
这篇关于尝试创建程序包-错误PLS-00330的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!