使用存储过程一次插入到 3 个表中

Question

如何在存储过程中插入到多个表中？例如，我有一个表单，当我提交此表单值时，它应该插入到 Oracle 数据库的 3 个表中。我写了一个存储过程，

CREATE OR REPLACE PROCEDURE insert_through_procedure(
p_cid IN table1.cid%TYPE,
p_uid IN table1.UID%TYPE,
p_chlnno     IN table1.chlnno%TYPE,
p_cdate      IN table1.cdate%TYPE,
p_createdby  IN table1.createdby%TYPE,
p_createdon  IN table1.createdon%TYPE,
p_finyearid  IN table1.finyearid%TYPE,
p_cstatus    IN table1.cstatus%TYPE,
p_aid        IN table2.applid%TYPE,
p_name       IN table2.aname%TYPE,
p_addr       IN table2.aaddress%TYPE,
p_email      IN table2.aemail%TYPE,
p_mobile     IN table2.mobileno%TYPE,
p_contact    IN table2.contactno%TYPE,
p_cdid       IN cm_challan_details.cdid%TYPE,
p_sid        IN table3.sid%TYPE,
p_year       IN table3.year%TYPE,
p_month      IN table3.month%TYPE,
p_val1       IN table3.val1%TYPE,
p_val2       IN table3.val2%TYPE,
p_val3       IN table3.val3%TYPE,
p_totalval   IN table3.total%TYPE) AS
BEGIN
  INSERT INTO table1(cid, UID, chlnno, cdate, createdby, createdon, finyearid, cstatus) VALUES (1, 1, 'chln01', p_challandate, p_createdby, to_date('02 / 02 / 2015','dd/mm/yyyy'), 1, 1);    
  INSERT INTO table2(cid, aid, aname, aaddress, aemail, mobileno, contactno) VALUES (1, 1, 'usha', p_addr, p_email, p_mobile, p_contact);    
  INSERT INTO table3(cdid, cid, sid, year, month, fee, cess, penalty, total) VALUES (101, 1, 1, p_year, p_month, 10.25, 0.25, 0.1, 10.61);    
  COMMIT;
END insert_through_procedure;

我正在通过在插入语句中提供值来测试这个存储过程，我没有错误，但是当我编译和执行时出现错误，

Connecting to the database u_tax.
ORA-01400: cannot insert NULL into ("PTIS_CHECKLIST"."table1"."CREATEDON")
ORA-06512: at "PTIS_CHECKLIST.INSERT_THROUGH_PROCEDURE", line 28
ORA-06512: at line 48
Process exited.
Disconnecting from the database u_tax.

谢谢。现在我有新的错误。.

Procedure PTIS_CHECKLIST.table3@u_tax
Error(28,3): PL/SQL: SQL Statement ignored
Error(28,166): PL/SQL: ORA-00984: column not allowed here
Error(29,3): PL/SQL: SQL Statement ignored
Error(30,16): PL/SQL: ORA-00984: column not allowed here

新错误已更新，

Connecting to the database u_tax.
ORA-00001: unique constraint (PTIS_CHECKLIST.table1_PK) violated
ORA-06512: at "PTIS_CHECKLIST.insert_through_procedure", line 28
ORA-06512: at line 48
Process exited.
Disconnecting from the database u_tax.

我哪里做错了....？谁能帮我解决问题....

Answer 1

p_createdon在表1中，创建在%类型中，

2015/03/10

什么是10 / 03 / 2015？是日期还是字符串？甲骨文不知道，除非你明确告诉它。您不能像这样插入值10 / 03 / 2015。首先，您需要告诉我们列createdon的数据类型。如果要处理 DATE，则数据类型必须是 DATE。

假设数据类型为 DATE。您需要使用TO_DATE和正确的格式掩码插入以将文字转换为日期。用

to_date('10/03/2015','DD/MM/YYYY')

您的插入语句：

INSERT INTO cm_challan_main 
   (cid, UID, chlnno, cdate, createdby, createdon, finyearid, cstatus)
 VALUES 
(1, 1, chln01, p_challandate, p_createdby, to_date('10/03/2015','DD/MM/YYYY'), 1, 1); 

ORA-00984：此处不允许列

另一个问题是插入语句中使用的列名而不是值。必须将字符串文本括在单引号内。

CHLN01

乌沙

您必须将上述字符串插入为：

'chln01'和'usha' .

Answer 2

从我的评论中复制：

如果是日期类型，则必须在 10/03/2015 周围添加双引号。如果您不添加引号，oracle 将除以值。

或者，您可以使用to_date()函数将其显式转换为日期。

TO_DATE( '10/03/2015', 'dd/mm/yyyy')