一个简单的oracle存储过程问题（插入操作)

Question

CREATE OR REPLACE PROCEDURE "InsertAccident" 
(
p_A_No varchar2(50),
p_A_Name varchar2(200),
p_A_Time varchar2(50),
p_A_Address varchar2(200),
p_placeid number,
p_A_Cause varchar2(200),
p_AccidentTypeNo varchar2(50),
p_A_Character varchar2(200),
p_A_DirectLoss varchar2(53),
p_A_InLoss varchar2(53),
p_A_Death number,
p_A_Injury number,
p_A_Description varchar(2000),
p_A_Conclusion varchar(2000),
p_A_Rescue varchar(2000),
p_A_Analysis varchar(2000),
p_A_CaseFile varchar(700),
p_A_Note long
)IS
BEGIN INSERT INTO tblAccident(A_No,A_Name,A_Time,A_Address,placeid,A_Cause,AccidentTypeNo,A_Character,A_DirectLoss,A_InLoss,A_Death,A_Injury,A_Description,A_Conclusion,A_Rescue,A_Analysis,A_CaseFile,A_Note)
VALUES(p_A_No,p_A_Name,p_A_Time,p_A_Address,p_placeid,p_A_Cause,p_AccidentTypeNo,p_A_Character,p_A_DirectLoss,p_A_InLoss,p_A_Death,p_A_Injury,p_A_Description,
p_A_Conclusion,p_A_Rescue,p_A_Analysis,p_A_CaseFile,p_A_Note) END InsertAccident;SHOW ERRORS;
没有错误！
请问错误是在哪里？

Answer 1

p_A_Conclusion,p_A_Rescue,p_A_Analysis,p_A_CaseFile,p_A_Note) 
少了个逗号，还有记得COMMIT.

Answer 2

recompile it ，使用开发工具可以看到相应错误的

Answer 3

我用SQL_PLUS执行SHOW ERRORS  显示没有错误

Answer 4

Oracle传入参数不能带长度，和SQL server不一样CREATE OR REPLACE PROCEDURE "InsertAccident" 
( 
  p_A_No varchar2, 
  p_A_Name varchar2, 
  p_A_Time varchar2, 
  p_A_Address varchar2, 
  p_placeid number, 
  p_A_Cause varchar2, 
  p_AccidentTypeNo varchar2, 
  p_A_Character varchar2, 
  p_A_DirectLoss varchar2, 
  p_A_InLoss varchar2, 
  p_A_Death number, 
  p_A_Injury number, 
  p_A_Description varchar, 
  p_A_Conclusion varchar, 
  p_A_Rescue varchar, 
  p_A_Analysis varchar), 
  p_A_CaseFile varchar, 
  p_A_Note long 
) IS 
BEGIN 
  INSERT INTO tblAccident(
     A_No,
     A_Name,
     A_Time,
     A_Address,
     placeid,
     A_Cause,
     AccidentTypeNo,
     A_Character,
     A_DirectLoss,
     A_InLo,
     ss,
     A_Death,
     A_Injury,
     A_Description,
     A_Conclusion,
     A_Rescue,
     A_Analysis,
     A_CaseFile,A_Note
   ) 
   VALUES (
     p_A_No,
     p_A_Name,
     p_A_Time,
     p_A_Address,
     p_placeid,
     p_A_Cause,
     p_AccidentTypeNo,
     p_A_Character,
     p_A_DirectLoss,
     p_A_InLoss,
     p_A_Death,
     p_A_Injury,
     p_A_Description, 
     p_A_Conclusion,
     p_A_Rescue,
     p_A_Analysis,
     p_A_CaseFile,
     p_A_Note
     ); 
   commit;  
END InsertAccident; 

Answer 5

综合1楼和2楼的建议去掉数字，再加逗号，commit，再执行一遍。用plsql的话编译下就知道有没错误了。

Answer 6

CREATE OR REPLACE PROCEDURE "InsertAccident" 
(
p_A_No varchar2,
p_A_Name varchar2,
p_A_Time varchar2,
p_A_Address varchar2,
p_placeid number,
p_A_Cause varchar2,
p_AccidentTypeNo varchar2,
p_A_Character varchar2,
p_A_DirectLoss varchar2,
p_A_InLoss varchar2,
p_A_Death number,
p_A_Injury number,
p_A_Description varchar,
p_A_Conclusion varchar,
p_A_Rescue varchar2,
p_A_Analysis varchar,
p_A_CaseFile varchar,
p_A_Note long
)IS
BEGIN
INSERT INTO tblAccident
(A_No,
A_Name,
A_Time,
A_Address,
placeid,
A_Cause,
AccidentTypeNo,
A_Character,
A_DirectLoss,
A_InLoss,
A_Death,
A_Injury,
A_Description,
A_Conclusion,
A_Rescue,
A_Analysis,
A_CaseFile,
A_Note)
VALUES
(p_A_No,
p_A_Name,
p_A_Time,
p_A_Address,
p_placeid,
p_A_Cause,
p_AccidentTypeNo,
p_A_Character,
p_A_DirectLoss,
p_A_InLoss,
p_A_Death,
p_A_Injury,
p_A_Description,
p_A_Conclusion,
p_A_Rescue,
p_A_Analysis,
p_A_CaseFile,
p_A_Note);
commit; END InsertAccident;
修改后还有错误，请问哪个地方少个逗号？

Answer 7

但是SHOW ERRORS提示没有错误，但是编译有错误

Answer 8

desc  表名，贴出表结构看看

Answer 9

SQL> SHOW ERRORS;
没有错误。
SQL> desc tblAccident
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------
 A_NO                                      NOT NULL VARCHAR2(50)
 A_NAME                                    NOT NULL VARCHAR2(200)
 A_TIME                                             VARCHAR2(50)
 A_ADDRESS                                          VARCHAR2(200)
 PLACEID                                            NUMBER(10)
 A_CAUSE                                            VARCHAR2(200)
 ACCIDENTTYPENO                                     VARCHAR2(50)
 A_CHARACTER                                        VARCHAR2(200)
 A_DIRECTLOSS                                       VARCHAR2(53)
 A_INLOSS                                           VARCHAR2(53)
 A_DEATH                                            NUMBER(10)
 A_INJURY                                           NUMBER(10)
 A_DESCRIPTION                                      VARCHAR2(2000)
 A_CONCLUSION                                       VARCHAR2(2000)
 A_RESCUE                                           VARCHAR2(2000)
 A_ANALYSIS                                         VARCHAR2(2000)
 A_CASEFILE                                         VARCHAR2(700)
 A_NOTE                                             LONG

Answer 10

SQL> create table tblAccident(
  2    A_NO VARCHAR2(50) not null,
  3    A_NAME                                    VARCHAR2(200) not null,
  4    A_TIME                                            VARCHAR2(50),
  5    A_ADDRESS                                          VARCHAR2(200),
  6    PLACEID                                            NUMBER(10),
  7    A_CAUSE                                            VARCHAR2(200) ,
  8    ACCIDENTTYPENO                                    VARCHAR2(50),
  9    A_CHARACTER                                        VARCHAR2(200),
 10    A_DIRECTLOSS                                      VARCHAR2(53),
 11    A_INLOSS                                          VARCHAR2(53),
 12    A_DEATH                                            NUMBER(10),
 13    A_INJURY                                          NUMBER(10),
 14    A_DESCRIPTION                                      VARCHAR2(2000),
 15    A_CONCLUSION                                      VARCHAR2(2000),
 16    A_RESCUE                                          VARCHAR2(2000),
 17    A_ANALYSIS                                        VARCHAR2(2000),
 18    A_CASEFILE                                        VARCHAR2(700),
 19    A_NOTE                                            LONG
 20  );Table createdSQL> 
SQL> CREATE OR REPLACE PROCEDURE InsertAccident
  2  (
  3    p_A_No varchar2,
  4    p_A_Name varchar2,
  5    p_A_Time varchar2,
  6    p_A_Address varchar2,
  7    p_placeid number,
  8    p_A_Cause varchar2,
  9    p_AccidentTypeNo varchar2,
 10    p_A_Character varchar2,
 11    p_A_DirectLoss varchar2,
 12    p_A_InLoss varchar2,
 13    p_A_Death number,
 14    p_A_Injury number,
 15    p_A_Description varchar,
 16    p_A_Conclusion varchar,
 17    p_A_Rescue varchar2,
 18    p_A_Analysis varchar,
 19    p_A_CaseFile varchar,
 20    p_A_Note long
 21  )
 22  IS
 23  BEGIN
 24    INSERT INTO tblAccident (
 25      A_No,
 26      A_Name,
 27      A_Time,
 28      A_Address,
 29      placeid,
 30      A_Cause,
 31      AccidentTypeNo,
 32      A_Character,
 33      A_DirectLoss,
 34      A_InLoss,
 35      A_Death,
 36      A_Injury,
 37      A_Description,
 38      A_Conclusion,
 39      A_Rescue,
 40      A_Analysis,
 41      A_CaseFile,
 42      A_Note
 43    )
 44    VALUES
 45    (p_A_No,
 46    p_A_Name,
 47    p_A_Time,
 48    p_A_Address,
 49    p_placeid,
 50    p_A_Cause,
 51    p_AccidentTypeNo,
 52    p_A_Character,
 53    p_A_DirectLoss,
 54    p_A_InLoss,
 55    p_A_Death,
 56    p_A_Injury,
 57    p_A_Description,
 58    p_A_Conclusion,
 59    p_A_Rescue,
 60    p_A_Analysis,
 61    p_A_CaseFile,
 62    P_A_Note
 63    );
 64    commit;
 65  END InsertAccident;
 66  /Procedure createdSQL> 

Answer 11

我最后没加/   ，是不是这个错了？
再问一下，oracle中存储过程怎么调用，跟sql里调用存储过程有什么差别

Answer 12

hyrongg，已经添加成功，可能是因为最后那个/的问题？