oracle 提交可以应用于脚本中的每个 INSERT INTO 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14933874/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can commit be applied to every INSERT INTO in a script?
提问by Shikha
I have just moved from SQL Server to ORACLE, and I am working on a script (SQL Server style). In SQL Server, we use BEING TRAN - END TRAN after applying some logic in a script. And this can be done inside a BEGIN-END block. In Oracle, I am finding this a bit difficult. After a lot of Googling and searching on this site, I am still not clear on how can I met this requirement.
我刚刚从 SQL Server 转移到 ORACLE，我正在编写一个脚本（SQL Server 风格）。在 SQL Server 中，我们在脚本中应用了一些逻辑后使用 BEING TRAN - END TRAN。这可以在 BEGIN-END 块内完成。在 Oracle 中，我发现这有点困难。在这个网站上进行了大量的谷歌搜索和搜索之后，我仍然不清楚我如何才能满足这个要求。
When I ran the script (pasted below), this ran as one transaction. And DBMS_OUTPUT.Put_line also displays once the whole script is executed. Is there any way to print the DBMS_OUTPUT.Put_line after each and every commit?
当我运行脚本（粘贴在下面）时，它作为一个事务运行。一旦整个脚本被执行，DBMS_OUTPUT.Put_line 也会显示。有没有办法在每次提交后打印 DBMS_OUTPUT.Put_line ？
Also, I am open to ideas, if there's any other way to work on this script, so that every sub-query commits before the script moves to the next sub-query... Please let me know.
另外，我愿意接受想法，如果有任何其他方法可以处理此脚本，以便在脚本移动到下一个子查询之前提交每个子查询......请告诉我。
Here's my script:
这是我的脚本：
SET SERVEROUTPUT ON;
--spool Consolidated.log;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
SET DEFINE OFF;
ALTER SESSION SET GLOBAL_NAMES=FALSE;
DECLARE
ExtractType NUMBER(9);
RecordsExtracted NUMBER(9);
CurStatus NUMBER(9);
StartDate date;
ErrorMessage NVARCHAR2(1000);
LastExtrctTimestamp DATE;
BEGIN
-- AgreementTradeTypes
StartDate := sysdate;
ExtractType := 44;
DELETE FROM AgreementTradeTypes;
INSERT INTO AgreementTradeTypes (AgreementId,AgreementName,PrincipalId,Principal,CounterpartyId,Counterparty, TradeTypeId,TradeTypeName,BusinessLine,AdditionalCriteria)
-- From CORE DB
SELECT
IATT.AgreementId, AG.AgreementName, IATT.PRINCIPALID, Principal.ENTITYNAME Principal,
IATT.COUNTERPARTYID, Cpty.ENTITYNAME Counterparty,
IATT.TradeTypeID, TT.TradeTypeName, BusLine.ENUMTEXT BusinessLine, IATT.ADDITIONALCRITERIA
FROM IncludedAgreementTradeTypes@RPTCORE IATT
INNER JOIN Entities@RPTCORE Principal ON IATT.PRINCIPALID = Principal.ENTITYID
INNER JOIN Entities@RPTCORE Cpty ON IATT.CounterpartyId = Cpty.ENTITYID
INNER JOIN EnumValues@RPTCORE BusLine ON IATT.BusinessLine = BusLine.ENUMVALUE AND BusLine.ENUMTYPE = 'BusinessLine'
INNER JOIN Agreements@RPTCORE AG ON IATT.AGREEMENTID = AG.AgreementID
INNER JOIN TradeTypes@RPTCORE TT ON IATT.TRADETYPEID = TT.TradeTypeID
ORDER BY IATT.AgreementId;
RecordsExtracted := SQL%RowCount;
DBMS_OUTPUT.put_line('AgreementTradeTypes Records Extracted:' || RecordsExtracted);
-- On Success
CurStatus := 2;
ErrorMessage := 'AgreementTradeTypes Complete';
INSERT INTO ExtractRecords(ExtractType, RecordsExtracted, Status, ExtractTimestamp, StartDate, EndDate, ErrorMessage)
VALUES (ExtractType, RecordsExtracted, CurStatus, SysDate, StartDate, SysDate, ErrorMessage);
INSERT INTO LoadRecords (LoadType,Status,LoadTimestamp,StartDate,EndDate)
VALUES (ExtractType, CurStatus, SysDate, StartDate, SysDate);
COMMIT; /* Committing first Block */
-- INTEREST PAYMENT PERIODS
StartDate := sysdate;
ExtractType := 57;
DELETE FROM InterestPaymentPeriods;
INSERT INTO InterestPaymentPeriods (InterestPaymentPeriodId,AgreementId,AgreementName,CurrencyId,CurrencyName,InstrumentId,InstrumentName,PositionType,CollateralMarginType,PeriodStart,PeriodEnd,NextPeriodEnd,AccruedInterest,OpeningBalance,EndingBalance,MarketIndexId,MarketIndexName,Spread,DayCountConvention,CalculationType,ManagingLocation,BusinessLine)
-- From CORE DB
SELECT
IPP.INTERESTPAYMENTPERIODID, IPP.AGREEMENTID, AG.AGREEMENTNAME, IPP.CURRENCYID, CUR.CODE CurrencyName, IPP.INSTRUMENTID,
Instruments.DESCRIPTION InstrumentName, PosType.ENUMTEXT PositionType, CollMargType.ENUMTEXT CollateralMarginType,
IPP.PERIODSTART, IPP.PERIODEND, IPP.NEXTPERIODEND, IPP.ACCRUEDINTEREST, IPP.OPENINGBALANCE, IPP.ENDINGBALANCE,
IPP.MARKETINDEXID, MI.MARKETINDEXNAME, IPP.SPREAD, DCC.ENUMTEXT DayCountConvention, CalcType.ENUMTEXT CalculationType,
Cty.CITYNAME ManagingLocation, BusLine.ENUMTEXT BusinessLine
FROM
INTERESTPAYMENTPERIODS@RPTCORE IPP
INNER JOIN Agreements@RPTCORE AG ON IPP.AGREEMENTID = AG.AGREEMENTID
LEFT OUTER JOIN Currencies@RPTCORE CUR ON IPP.CURRENCYID = CUR.CURRENCYID
LEFT OUTER JOIN Cities@RPTCORE Cty ON IPP.MANAGINGLOCATIONID = Cty.CITYID
LEFT OUTER JOIN MarketIndexes@RPTCORE MI ON IPP.MARKETINDEXID = MI.MARKETINDEXID
LEFT OUTER JOIN Instruments@RPTCORE ON IPP.INSTRUMENTID = Instruments.INSTRUMENTID
LEFT OUTER JOIN EnumValues@RPTCORE PosType ON IPP.POSITIONTYPE = PosType.ENUMVALUE AND PosType.ENUMTYPE = 'PositionType'
LEFT OUTER JOIN EnumValues@RPTCORE CollMargType ON IPP.COLLATERALMARGINTYPE = CollMargType.ENUMVALUE AND CollMargType.ENUMTYPE = 'CollateralMarginType'
LEFT OUTER JOIN EnumValues@RPTCORE DCC ON MI.DAYCOUNTCONVENTION = DCC.ENUMVALUE AND DCC.ENUMTYPE = 'DayCountConvention'
LEFT OUTER JOIN EnumValues@RPTCORE CalcType ON IPP.CALCULATIONTYPE = CalcType.ENUMVALUE AND CalcType.ENUMTYPE = 'CalculationType'
LEFT OUTER JOIN EnumValues@RPTCORE BusLine ON IPP.BUSINESSLINE = BusLine.ENUMVALUE AND BusLine.ENUMTYPE = 'BusinessLine';
RecordsExtracted := SQL%RowCount;
DBMS_OUTPUT.put_line('InterestPaymentPeriods Records Extracted:' || RecordsExtracted);
-- On Success
CurStatus := 2;
ErrorMessage := 'Interest_Payment_Periods Complete';
INSERT INTO ExtractRecords(ExtractType, RecordsExtracted, Status, ExtractTimestamp, StartDate, EndDate, ErrorMessage)
VALUES (ExtractType, RecordsExtracted, CurStatus, SysDate, StartDate, SysDate, ErrorMessage);
INSERT INTO LoadRecords (LoadType,Status,LoadTimestamp,StartDate,EndDate)
VALUES (ExtractType, CurStatus, SysDate, StartDate, SysDate);
COMMIT; /* Committing Second Block */
END;
--spool off;
/
回答by David Aldridge
The usual practice in Oracle is to commit only when the business transaction is complete, so that a transaction is not part-processed. This differs from some others systems because Oracle's multiversioning and locking systems ensure that writers do not block readers and readers do not block writers.
Oracle 中通常的做法是只在业务事务完成时才提交，这样事务就不会被部分处理。这与其他一些系统不同，因为 Oracle 的多版本和锁定系统确保写入者不会阻塞读取器，而读取器不会阻塞写入器。
For the DBMS_Output issue, no you cannot get a response from the server through DBMS_Output partway through the block's execution. You might like to look at writing data to a serverside file using Utl_File for that.
对于 DBMS_Output 问题，不，在块的执行过程中，您无法通过 DBMS_Output 从服务器获得响应。为此，您可能希望使用 Utl_File 将数据写入服务器端文件。
Other thoughts:
其他想法：
Consider using TRUNCATE instead of DELETE if you're deleting every row AND you do not need to use foreign keys against that table. TRUNCATE has an implicit commit associated with it, so apply all your truncates at the beginning of the procedure.
Consider using the APPEND hint on the inserts to invoke direct path insert if you are loading bulk data, have indexes to maintain, and do not need to allow multiple simultaneous inserts into the table.
I'm guessing that the ORDER BY on the inserts is there for a reason -- usually in Oracle it would be to ensure that data rows are physically clustered on the order-by column(s), which leads to greater efficiency on index-based access via those columns. Using a direct path insert would help guarantee physical row ordering, but if you don't need that clustering then remove the ORDER BY
如果您要删除每一行并且不需要对该表使用外键，请考虑使用 TRUNCATE 而不是 DELETE。TRUNCATE 有一个与之关联的隐式提交，因此在过程开始时应用所有截断。
如果您正在加载批量数据、需要维护索引并且不需要允许多个同时插入到表中，请考虑在插入中使用 APPEND 提示来调用直接路径插入。
我猜测插入中的 ORDER BY 是有原因的——通常在 Oracle 中，它会确保数据行物理地聚集在 order-by 列上，这会导致更高的索引效率——通过这些列进行基于访问。使用直接路径插入将有助于保证物理行排序，但如果您不需要该集群，则删除 ORDER BY
回答by Alex Poole
The data will be committed in two stages. The DECLARE/BEGIN/ENDdenote the start and end of an anonymous PL/SQL block, not a transaction. If you didn't have a commit inside the block (which would be more normal; it's somewhat unusual to have transaction control inside a block) at all, then after the block completes none of your changes would have been committed - you could still roll back if you wanted to.
数据将分两个阶段提交。的DECLARE/ BEGIN/END分别表示开始和匿名的端PL / SQL块，而不是一个交易。如果您在块内根本没有提交（这会更正常；在块内进行事务控制有点不寻常），那么在块完成后，您的任何更改都不会被提交 - 您仍然可以滚动如果你想回来。
Your DBMS_OUTPUTcalls put messages into a buffer, which the client (e.g SQL*Plus) retrieves and displays after the block completes. There is no way around that - you can't get 'live' updates using that mechanism. There are alternatives, such as using UTL_FILEor setting module informationfor the session that can be viewed from another session, but that's probably overkill for what you seem to be doing.
您的DBMS_OUTPUT调用将消息放入缓冲区，客户端（例如 SQL*Plus）在块完成后检索并显示该缓冲区。没有办法解决这个问题 - 您无法使用该机制获得“实时”更新。还有其他选择，例如使用UTL_FILE或设置可以从另一个会话查看的会话的模块信息，但这对于您似乎正在做的事情来说可能有点过头了。
If you just want to see the message after each section you can split it into two anonymous blocks. The downside of that is perhaps having to declare variables twice, and that they will go out of scope between the blocks - so something you set in the first block won't be visible in the second. You could perhaps work around that using bind variables, via the SQL*Plus variablecommand.
如果您只想在每个部分之后查看消息，您可以将其拆分为两个匿名块。这样做的缺点可能是必须两次声明变量，并且它们将超出块之间的范围 - 因此您在第一个块中设置的内容在第二个块中将不可见。您也许可以通过 SQL*Plusvariable命令使用绑定变量来解决这个问题。
You don't really seem to need to do this in PL/SQL at all, at least in this example, though you said you would be applying some logic in the script at well. If that's not really the case then you could use simple SQL statements, substitution variables and prompt to achieve this, without using PL/SQL at all. Apart from the row counts, which are a little tricky unless you're happy just with the normal set feedback ondisplay, like '3 rows inserted'.
您似乎根本不需要在 PL/SQL 中执行此操作，至少在本示例中是这样，尽管您说您将在脚本中应用一些逻辑。如果情况并非如此，那么您可以使用简单的 SQL 语句、替换变量和提示来实现这一点，而根本不使用 PL/SQL。除了行数之外，这有点棘手，除非您对正常set feedback on显示感到满意，例如“插入 3 行”。
回答by Rene
The results of DBMS_OUTPUT will only be displayed after completion of the PL/SQL block. DBMS_OUTPUT and COMMIT are not related to one another. If you can split your script into multiple blocks you can get the results of each block committed and printed before the next block starts.
DBMS_OUTPUT 的结果只会在 PL/SQL 块完成后显示。DBMS_OUTPUT 和 COMMIT 彼此无关。如果您可以将脚本拆分为多个块，则可以在下一个块开始之前获得每个块提交和打印的结果。
begin
-- step 1
insert
commit ...
dbms_output ...
end;
/
begin
-- step 2
insert...
commit ...
dbms_output ...
end;
/
