oracle存储过程编写的问题1

Question

我的存储过程现在是这样如下：
 (
plateid in Varchar2,
platecolor in Varchar2,
platetype in Varchar2,
crossingtime in Date,
crossingname in Varchar2,
aspectname in Varchar2,
wayname in Varchar2,
speed in Varchar2,
illegaltype in Number,
blackstate in Number,
filepath1 in Varchar2,
filepath2 in Varchar2,
filepath3 in Varchar2,
filepath4 in Varchar2,
result_blackstate out Number,
result_plateid out Varchar
)
as
  v_count number;
begin
  select count(*) into v_count from tb_plate where plateid=plateid ;
  if v_count>0 
  then
  result_plateid:=plateid;
  else
  insert into sysadmin.tb_plate(ID,plateid,platecolor,platetype) values(plate_id.nextval,plateid,platecolor,platetype);    
  end if;
  if illegaltype>1 and blackstate!=3
  then
  insert into sysadmin.tb_illegalcar(
  id,
  plateid,
  crossingtime,
  crossingname,
  aspectname,
  wayname,
  speed,
  illegaltype,
  blackstate,
  filepath1,
  filepath2,
  filepath3,
  filepath4)
  values(
  ILLEGALCAR_ID.nextval,
  plateid,
  to_date(crossingtime,'YYYY-MM-DD HH24:MI:SS'),
  crossingname ,
  aspectname ,
  wayname ,
  speed ,
  illegaltype ,
  blackstate ,
  filepath1 ,
  filepath2 ,
  filepath3 ,
  filepath4 );
  else  
  if illegaltype=1 and blackstate<2
  then  
  insert into sysadmin.tb_normalcar(id,
  plateid,
  crossingtime,
  crossingname,
  aspectname,
  wayname,
  speed,
  illegaltype,
  blackstate,
  filepath1,
  filepath2,
  filepath3,
  filepath4)
  values(
  NORMALCAR_ID.nextval,
  plateid,
  crossingtime ,
  crossingname ,
  aspectname ,
  wayname ,
  speed ,
  illegaltype ,
  blackstate ,
  filepath1 ,
  filepath2 ,
  filepath3 ,
  filepath4 );
  end if;
  end if;
end;
情况是这样的，我想在这个里面做个判断如果当前插入的这条信息的blackstate=3那么这条信息就不添加进任何的表，并且返回一个blackstate值。
请问这个应该如何实现？

Answer 1

CREATE OR REPLACE PROCEDURE INSREC(plateid           IN VARCHAR2,
                                   platecolor        IN VARCHAR2,
                                   platetype         IN VARCHAR2,
                                   crossingtime      IN DATE,
                                   crossingname      IN VARCHAR2,
                                   aspectname        IN VARCHAR2,
                                   wayname           IN VARCHAR2,
                                   speed             IN VARCHAR2,
                                   illegaltype       IN NUMBER,
                                   blackstate        IN NUMBER,
                                   filepath1         IN VARCHAR2,
                                   filepath2         IN VARCHAR2,
                                   filepath3         IN VARCHAR2,
                                   filepath4         IN VARCHAR2,
                                   result_blackstate OUT NUMBER,
                                   result_plateid    OUT VARCHAR) AS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM tb_plate WHERE plateid = plateid;
  IF v_count > 0 THEN
    result_plateid := plateid;
  ELSE
    INSERT INTO sysadmin.tb_plate
      (ID, plateid, platecolor, platetype)
    VALUES
      (plate_id.nextval, plateid, platecolor, platetype);
  END IF;
  IF illegaltype > 1 AND blackstate != 3 THEN
    INSERT INTO sysadmin.tb_illegalcar
      (id,
       plateid,
       crossingtime,
       crossingname,
       aspectname,
       wayname,
       speed,
       illegaltype,
       blackstate,
       filepath1,
       filepath2,
       filepath3,
       filepath4)
    VALUES
      (ILLEGALCAR_ID.nextval,
       plateid,
       to_date(crossingtime, 'YYYY-MM-DD HH24:MI:SS'),
       crossingname,
       aspectname,
       wayname,
       speed,
       illegaltype,
       blackstate,
       filepath1,
       filepath2,
       filepath3,
       filepath4);
  ELSIF illegaltype = 1 AND blackstate < 2 THEN
    INSERT INTO sysadmin.tb_normalcar
      (id,
       plateid,
       crossingtime,
       crossingname,
       aspectname,
       wayname,
       speed,
       illegaltype,
       blackstate,
       filepath1,
       filepath2,
       filepath3,
       filepath4)
    VALUES
      (NORMALCAR_ID.nextval,
       plateid,
       crossingtime,
       crossingname,
       aspectname,
       wayname,
       speed,
       illegaltype,
       blackstate,
       filepath1,
       filepath2,
       filepath3,
       filepath4);
  ELSE
    result_blackstate := blackstate;
  END IF;
  COMMIT;
END;

Answer 2

但是我插入一个测试数据根本插补进去。总是报一个错误：
ERROR 位于第一行：
pls-00306：调用'proc_vulgatecar'时参数个数或类型错误
pl/sql：Statement ignored
我调用存储过程的语句是：
exec sysadmin.proc_test ('京A00282','2','1','2008-04-08 12:35:23','中关村','东向西','车道一','135',1,1,'e:\filepath1','e:\filepath2','e:\filepath3','e:\filepath4')