如何处理XMLTable插入中的序列? [英] How to deal with sequence in insert from XMLTable?

问题描述

我已经编写了一个PL/SQL函数，该函数接受XML格式的输入 下表:

I have write a PL/SQL function that takes input in XML format for the following table:

TABLE: TBL_MEDICAL_CENTER_BILLS

Name          Null     Type          
------------- -------- ------------- 
MED_RECORDNO  NOT NULL NUMBER       
MED_EMPID              NVARCHAR2(10) 
MED_BILL_HEAD          NVARCHAR2(20) 
MED_DATE               DATE          
MED_AMOUNT             FLOAT(126)   

这是功能代码:

FUNCTION save_medical_center_bills(medical_bill_data NVARCHAR2 ) RETURN clob IS ret clob;
   xmlData XMLType;

   v_code  NUMBER;
   v_errm  VARCHAR2(100);

   BEGIN 
   xmlData:=XMLType(medical_bill_data);
   INSERT INTO TBL_MEDICAL_CENTER_BILLS SELECT x.* FROM XMLTABLE('/medical_center_bill'
                                                PASSING xmlData

                                                COLUMNS  MED_RECORDNO NUMBER  PATH 'MED_RECORDNO' default null,
                                                         MED_EMPID    NVARCHAR2(11)     PATH   'employee_id',
                                                         MED_BILL_HEAD  NVARCHAR2(20)     PATH   'bill_head' ,
                                                         MED_DATE DATE  PATH  'effective_date',
                                                         MED_AMOUNT    FLOAT       PATH    'bill_amount'
                                                        ) x;


     ret:=to_char(sql%rowcount);
COMMIT;

RETURN '<result><status affectedRow='||ret||'>success</status></result>';
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE (v_code || ' ' || v_errm);
-- '<result><status>Error</status> <error_message>'|| 'Error Code:' || v_code || ' ' || 'Error Message:' || v_errm ||'</error_message> </result>';
RETURN '<result><status>Error</status> <error_message>'|| 'Error Message:' || v_errm ||'</error_message> </result>';

END save_medical_center_bills;

但是，我想将表的第一列MED_RECORDNO保留为递增序列(由于我不知道如何将序列放入XMLTable子句中，因此我将其保留为空)，其余部分 输入[MED_EMPID，MED_BILL_HEAD，MED_DATE和MED_AMOUNT]将从传递给函数的XML中获取.

However, I want to keep table's first column MED_RECORDNO as incrementing sequence (at the moment I am keeping it null since I don't know how to put the sequence in the XMLTable clause) and the rest of the inputs [MED_EMPID, MED_BILL_HEAD , MED_DATE , MED_AMOUNT] will be taken from the XML passed to the function.

我为该表列MED_RECORDNO创建了一个序列和一个触发器，以保持该序列递增:

I created a sequence and a trigger to keep this sequence incremented for that table column MED_RECORDNO:

CREATE SEQUENCE MED_RECORDNO_SEQ;

create or replace TRIGGER MED_RECORDNO_TRIGGER 
BEFORE INSERT ON TBL_MEDICAL_CENTER_BILLS  FOR EACH ROW
WHEN (new.MED_RECORDNO is null)
DECLARE
  v_id TBL_MEDICAL_CENTER_BILLS.MED_RECORDNO%TYPE;
BEGIN
SELECT  MED_RECORDNO_seq.nextval INTO v_id FROM DUAL;
 :new.MED_RECORDNO  := v_id;

END;

如您所见，我的XMLTable在5列表中插入4列值，因为列MED_RECORDNO将使用TRIGGER MED_RECORDNO_TRIGGER从序列MED_RECORDNO_SEQ中获取其值.

As you can see, my XMLTable is inserting 4 column values in a 5 column table, because columns MED_RECORDNO will take its value from sequence MED_RECORDNO_SEQ using TRIGGER MED_RECORDNO_TRIGGER.

我对此一无所知.如果您曾经经历过这样的事情，请分享您的想法.

I don't know any thing about doing this. If you have ever experience such things, then please share your idea.

推荐答案

我在较早的答案中对此有所暗示.您应该在表中指定要插入的列的名称.即使您填充了所有表，这也是一个好习惯，因为这样可以避免表结构更改(或环境之间有所不同)时出现意外情况，并使查找列或值顺序错误的顺序变得更加容易.

I sort of hinted at this in an earlier answer. You should specify the names of of the columns in the table you are inserting into; this is good practice even if you are populating all of them, as it will avoid surprises if the table structure changes (or differs between environments), and makes it much easier to spot like having columns or values in the wrong order.

   INSERT INTO TBL_MEDICAL_CENTER_BILLS (MED_EMPID, MED_BILL_HEAD, MED_DATE, MED_AMOUNT)
   SELECT x.MED_EMPID, x.MED_BILL_HEAD, x.MED_DATE, x.MED_AMOUNT
   FROM XMLTABLE('/medical_center_bill'
      PASSING xmlData
      COLUMNS  MED_EMPID    NVARCHAR2(11)     PATH   'employee_id',
               MED_BILL_HEAD  NVARCHAR2(20)     PATH   'bill_head' ,
               MED_DATE DATE  PATH  'effective_date',
               MED_AMOUNT    FLOAT       PATH    'bill_amount'
              ) x;

您拥有的插入应该可以实际使用(如果表中的列顺序匹配)；触发器仍将您从XMLTable获得的空值替换为序列值.至少，直到您使MED_RECORDNO列不为null为止，并且您可能想知道它是否是主键.

The insert you have should actually work (if the column order in the table matches); the trigger will still replace the null value you get from the XMLTable with the sequence value. At least, until you make the MED_RECORDNO column not-null, and you probably want to if it's the primary key.

顺便说一句，如果您使用的是11g或更高版本，则触发器可以直接将序列分配给NEW伪记录:

Incidentally, if you're on 11g or higher your trigger can assign the sequence straight to the NEW pseudorecord:

create or replace TRIGGER MED_RECORDNO_TRIGGER 
BEFORE INSERT ON TBL_MEDICAL_CENTER_BILLS
FOR EACH ROW
BEGIN
   :new.MED_RECORDNO  := MED_RECORDNO_seq.nextval;
END;

when null检查表示您有时希望允许指定一个值；这是一个坏主意，因为手动插入的值可能会与序列值冲突，从而给您重复项或唯一/主键异常.

The when null check implies you sometimes want to allow a value to be specified; that is a bad idea as manually inserted values can clash with sequence values, either giving you duplicates or a unique/primary key exception.

这篇关于如何处理XMLTable插入中的序列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！