ORACLE PL / SQL存储过程插入和执行错误

debugcn 发表于 Dev

灰烬30

我正在尝试创建一个存储过程，以将值插入到EMPLOYEE TABLE中。当我尝试使用值执行过程时，将引发错误。请你能帮忙吗？先感谢您。

存储过程----

 create or replace PROCEDURE CREATE_EMP

(empid IN EMPLOYEE.EMP_ID%TYPE,
firstname IN EMPLOYEE.FIRST_NAME%TYPE,
surname IN EMPLOYEE.SURNAME%TYPE,
address IN EMPLOYEE.ADDRESS%TYPE,
city IN EMPLOYEE.CITY%TYPE,
county IN EMPLOYEE.COUNTY_STATE%TYPE,
postcode IN EMPLOYEE.POSTCODE_ZIP%TYPE,
country IN EMPLOYEE.COUNTRY%TYPE,
jobtitle IN EMPLOYEE.JOB_TITLE%TYPE,
startdate IN EMPLOYEE.START_DATE%TYPE,
enddate IN EMPLOYEE.END_DATE%TYPE)

 AS 

 BEGIN

INSERT INTO EMPLOYEE
VALUES(empid, firstname, surname, address, city, county, postcode, country, jobtitle, startdate, enddate);

END CREATE_EMP;

执行命令-

EXECUTE CREATE_EMP(58,'John','Testy','Here Lane','Himble','UK','Skipper',2015/02/02,2017/02/02);

错误信息 - -

Error starting at line : 1 in command -
EXECUTE CREATE_EMP(58,'John','Testy','Here Lane','Himble','UK','Skipper',2015/02/02,2017/02/02)
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_EMP'
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:

桌子 - - -

EMP_ID  NUMBER(8,0)
FIRST_NAME  VARCHAR2(30 BYTE)
SURNAME VARCHAR2(30 BYTE)
ADDRESS VARCHAR2(50 BYTE)
CITY    VARCHAR2(30 BYTE)
COUNTY_STATE    VARCHAR2(30 BYTE)
POSTCODE_ZIP    VARCHAR2(10 BYTE)
COUNTRY VARCHAR2(5 BYTE)
JOB_TITLE   VARCHAR2(20 BYTE)
START_DATE  DATE
END_DATE    DATE

特布基奇

错误消息说：

PLS-00306：调用“ CREATE_EMP”时参数的数量或类型错误

我已经将您给定的参数映射到方法所需的参数：

empid = 58
firstname = 'John'
surname = 'Testy'
address = 'Here Lane'
city = 'Himble'
county
postcode
country = 'UK'
jobtitle = 'Skipper'
startdate = 2015/02/02
enddate = 2017/02/02

由于没有参数具有默认值，因此您仅会在调用中错过2个参数，如错误消息所述。

编辑：我找到了解决方案：

CREATE_EMP在匿名块中执行。这对我有用：

begin
  CREATE_EMP(58, 'John', 'Testy', 'Here Lane', 'Himble', 'Abc','1225', 'UK', 'Skipper', TO_DATE('2015/02/02','YYYY/MM/DD'), TO_DATE('2017/02/02','YYYY/MM/DD'));
end;

这是数据库的创建和CREATE_EMP定义：

CREATE TABLE EMPLOYEE ( 
  EMP_ID  NUMBER(8,0),
  FIRST_NAME  VARCHAR2(30),
  SURNAME VARCHAR2(30),
  ADDRESS VARCHAR2(50),
  CITY    VARCHAR2(30),
  COUNTY_STATE    VARCHAR2(30),
  POSTCODE_ZIP    VARCHAR2(10),
  COUNTRY VARCHAR2(5),
  JOB_TITLE   VARCHAR2(20),
  START_DATE  DATE,
  END_DATE    DATE
);

//

create or replace PROCEDURE CREATE_EMP
(
  empid IN EMPLOYEE.EMP_ID%TYPE,
  firstname IN EMPLOYEE.FIRST_NAME%TYPE,
  surname IN EMPLOYEE.SURNAME%TYPE,
  address IN EMPLOYEE.ADDRESS%TYPE,
  city IN EMPLOYEE.CITY%TYPE,
  county IN EMPLOYEE.COUNTY_STATE%TYPE,
  postcode IN EMPLOYEE.POSTCODE_ZIP%TYPE,
  country IN EMPLOYEE.COUNTRY%TYPE,
  jobtitle IN EMPLOYEE.JOB_TITLE%TYPE,
  startdate IN EMPLOYEE.START_DATE%TYPE,
  enddate IN EMPLOYEE.END_DATE%TYPE)
AS 
BEGIN
  INSERT INTO EMPLOYEE
    (EMP_ID, FIRST_NAME, SURNAME, ADDRESS, CITY, COUNTY_STATE, POSTCODE_ZIP, COUNTRY, JOB_TITLE, START_DATE, END_DATE)
  VALUES
    (empid, firstname, surname, address, city, county, postcode, country, jobtitle, startdate, enddate);
END CREATE_EMP;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。