Shulou(Shulou.com)05/31 Report--

This article mainly explains the "PostgreSQL program batch binding timing how to use save exceptions to record error data", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "batch binding timing in PostgreSQL programs how to use save exceptions to record error data"!

First, using the emp table under scott to construct data-- construction data Create Table T1 As Select * From scott.emp Where 1 destroy 2 alter Table T1 Add Constraint pk_emp_empno Primary Key (empno); Alter Table T1 Modify (ename Not Null); Alter Table T1 Add Constraint chk_sql check (Sal > = 800); Create Table T2 As Select * FROM scott.emp;Insert Into T2 Select * FROM scott.emp Where empno In (73697499); Update T2 Set sal = 700Where empno In (7521m7566); Update T2 Set ename = Null Where empno = 7698 Create Table t_error As Select * From scott.emp Where 1 / 2: alter Table t_error Add (error_idx number,ErrorCode Varchar2 (50)); II: program processing block

-- get records of all ORA-24381 errors at once Declare-- define exception Excp_Bulk_Errors Exception; corresponding to ORA-24381-- associate exception and error number with Pragma Exception_Init (Excp_Bulk_Errors,-24381);-- define variable n_Err_Idx Number; Vc_Err_Code Varchar2 (50) that stores Error Index and Error Code; Cur_Ref_Emp Sys_Refcursor; Pi_Fetch_Limit Pls_Integer: = 1000 Type Typ_Emp Is Table Of Emp%Rowtype Index By Binary_Integer; Typ_Emp_Rec Typ_Emp;Begin Open Cur_Ref_Emp For Select * From T2;-- batch obtain Fetch Cur_Ref_Emp Bulk Collect Into Typ_Emp_Rec Limit Pi_Fetch_Limit;-- batch execute Forall i In 1. Typ_Emp_Rec.Count Save Exceptions Execute Immediate 'insert into T1 values (: empno,:ename,:job,:mgr,:hiredate,:sal,:comm,:deptno)' Using Typ_Emp_Rec (I). Empno, Typ_Emp_Rec (I). Ename, Typ_Emp_Rec (I). Job, Typ_Emp_Rec (I). Mgr, Typ_Emp_Rec (I). Hiredate Typ_Emp_Rec (I) .Sal, Typ_Emp_Rec (I) .Sal, Typ_Emp_Rec (I) .Deptno Exception When Excp_Bulk_Errors Then-clear the error log table Execute Immediate 'delete from tasking errors; For i In 1.. Sql%Bulk_Exceptions.Count () Loop n_Err_Idx: = Sql%Bulk_Exceptions (I) .Error_Index; Vc_Err_Code: = Sql%Bulk_Exceptions (I) .Error_Code Execute Immediate 'insert into t_error values (: empno,:ename,:job,:mgr,:hiredate,:sal,:comm,:deptno,:idx,:code)' Using Typ_Emp_Rec (n_Err_Idx). Empno, Typ_Emp_Rec (n_Err_Idx). Ename, Typ_Emp_Rec (n_Err_Idx). Job, Typ_Emp_Rec (n_Err_Idx). Mgr Typ_Emp_Rec (n_Err_Idx). Hiredate, Typ_Emp_Rec (n_Err_Idx). Sal, Typ_Emp_Rec (n_Err_Idx). Sal, Typ_Emp_Rec (n_Err_Idx). Deptno, n_Err_Idx, Vc_Err_Code End Loop;End; thank you for your reading, the above is the "PostgreSQL program batch binding time how to use save exceptions to record error data" content, after the study of this article, I believe you on the PostgreSQL program batch binding time how to use save exceptions to record error data this problem has a deeper understanding, the specific use also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.