如何創建可以將記錄插入兩個表的儲存過程一世nf或者rm我x一世nF○r米一世XInformix

我需要創建一個可以在兩個表中插入記錄的儲存過程

我有以下兩個表：

create table "sacro".doc_submitted 
(
doc_id serial not null constraint "sacro".nc_doc_sub00,
doc_category_id decimal(9,0),
ent_no varchar(11,1),
trak_no varchar(12),
indexuser varchar(8),
dateindexed datetime year to second,
doc_status decimal(2,0),
doc_mode decimal(2,0),
doc_path varchar(254),
doc_pages integer,
image_exists smallint,
scan_user varchar(8),
scan_date datetime year to second,
job_type varchar(10),
job_id integer,
cdr_label varchar(255),
image_release char(1)
);


create table "sacro".job 
(
job_id serial8 not null ,
ent_no varchar(11,1),
compl_user varchar(8),
trak_no varchar(12),
job_type varchar(10),
scan_indicator varchar(1),
job_accept smallint,
date_received date,
date_accept date,
agent_code varchar(6),
fax_send smallint,
primary key (job_id)  constraint "informix".pk_job00
);

我的目標是有一個儲存過程，它可以根據兩個表中不包括 job_id 和 doc_id 的列在這兩個表中插入一條記錄。

這是我到目前為止所擁有的：

CREATE PROCEDURE sp_IndexDocument (
doc_category_id decimal(9,0),
ent_no varchar(11,1),
trak_no varchar(12),
indexuser varchar(8),
dateindexed datetime year to second,
doc_status decimal(2,0),
doc_mode decimal(2,0),
doc_path varchar(254),
doc_pages integer,
image_exists smallint,
scan_user varchar(8),
scan_date datetime year to second,
job_type varchar(10),
cdr_label varchar(255),
image_release char(1),
compl_user varchar(8),
scan_indicator varchar(1),
job_accept smallint,
date_received date,
date_accept date,
agent_code varchar(6),
fax_send smallint)

INSERT INTO sacro:doc_submitted (doc_category_id,ent_no,trak_no,indexuser,dateindexed,doc_status,doc_mode,doc_path,doc_pages,image_exists,scan_user,scan_date,job_type,job_id,cdr_label,image_release)
VALUES (doc_category_id,ent_no,trak_no,indexuser,dateindexed,doc_status,doc_mode,doc_path,doc_pages,image_exists,scan_user,scan_date,job_type,cdr_label,image_release);
INSERT INTO sacro:job (ent_no,compl_user,trak_no,job_type,scan_indicator,job_accept,date_received,date_accept,agent_code,fax_send)
VALUES (ent_no,compl_user,trak_no,job_type,scan_indicator,job_accept,date_received,date_accept,agent_code,fax_send);
END PROCEDURE;

我執行以下過程將值插入到兩個表中：

EXECUTE PROCEDURE sp_IndexDocument ('100', 'M1997554436', 'M1997554436', 'HEN481', 'EXTEND(MDY(6,28,1999), YEAR to SECOND)+9 UNITS HOUR+6 UNITS MINUTE+22 UNITS SECOND', '1', '0', '\19990107\M\1997\014436\100\1.tif', '2', '1', 'xcel', 'EXTEND(MDY(7,8,1999), YEAR to SECOND)+10 UNITS HOUR+56 UNITS MINUTE+4 UNITS SECOND', 'CM31', '692118', '19990095', 'Y','B1998025909', 'Good', 'B19980259089', 'FILE', 'N', '1', NULL, NULL, NULL, NULL)

我得到以下錯誤程式碼：

674: Routine (sp_indexdocument) can not be resolved.

如果數據類型相同，我不明白問題出在哪裡

這似乎對我有用：

CREATE PROCEDURE sacro.sp_indexdocument (
doc_category_id decimal(9,0),
ent_no varchar(11,1),
trak_no varchar(12),
indexuser varchar(8),
dateindexed datetime year to second,
doc_status decimal(2,0),
doc_mode decimal(2,0),
doc_path varchar(254),
doc_pages integer,
image_exists smallint,
scan_user varchar(8),
scan_date datetime year to second,
job_type varchar(10),
cdr_label varchar(255),
image_release char(1),
compl_user varchar(8),
scan_indicator varchar(1),
job_accept smallint,
date_received date,
date_accept date,
agent_code varchar(6),
fax_send smallint,
job_job_type varchar(10))

DEFINE jobid INTEGER;
SELECT MAX(job_id) + 1 INTO jobid FROM job;

--Add the record to the Job Table
INSERT INTO sacro:job (ent_no, compl_user, trak_no, job_type, scan_indicator, job_accept, date_received, date_accept, agent_code, fax_send) 
              VALUES (ent_no, compl_user, trak_no, job_job_type, scan_indicator, job_accept, date_received, date_accept, agent_code, fax_send);

--Add the record to the Doc Submitted Table                                                                                     
INSERT INTO sacro:doc_submitted (doc_category_id, ent_no, trak_no, indexuser, dateindexed, doc_status, doc_mode, doc_path, doc_pages, image_exists, scan_user, scan_date, job_type, job_id, cdr_label, image_release) 
                        VALUES (doc_category_id, ent_no, trak_no, indexuser, dateindexed, doc_status, doc_mode, doc_path, doc_pages, image_exists, scan_user, scan_date, job_type, jobid, cdr_label, image_release);


--Update the workflow table to indicate the document approved date             
UPDATE sacro:bk_name_workflow set doc_approve_date=dateindexed where reference_no=trak_no;

END PROCEDURE;

我使用的插入語句如下：

EXECUTE PROCEDURE sacro.sp_indexdocument(30, "M1996000465", "A0814390", "HEN500", current, 1, 0, "\19990160\M\1905\021141\90\A0814386.tif", 5, 1, "DELETE", current, "CM29", "19980018", "Y","OK","N",1,today,today,"B0245",1,"TEST")

引用自：https://dba.stackexchange.com/questions/175442