free barcode generator source code in c#.net Using PL/SQL to Predetect a Duplicate in Font

Drawer PDF-417 2d barcode in Font Using PL/SQL to Predetect a Duplicate

Using PL/SQL to Predetect a Duplicate
Print PDF 417 In None
Using Barcode creator for Font Control to generate, create PDF417 image in Font applications.
www.OnBarcode.com
Create ANSI/AIM Code 39 In None
Using Barcode creation for Font Control to generate, create USS Code 39 image in Font applications.
www.OnBarcode.com
Using additional PL/SQL code to predetect the presence of duplicate values is, in my opinion, better than catching a DUP_VAL_ON_INDEX exception, for these reasons: You maintain control of your PL/SQL program s execution. You can conditionally decide how to handle duplicate values. You can use your detection scheme to acquire the primary key for the row that is a duplicate, and then update that row. Figure 4-2 is a simple process flow diagram for predetecting a duplicate entry using PL/SQL, and then acting accordingly that is, inserting or updating as needed. In this process, you start out by selecting the primary key value from the database for the table entry in question. If you find an existing entry, you can determine whether the entry needs to be updated, and then execute an UPDATE statement. Alternatively, you execute an INSERT statement if the SELECT statement raises a NO_DATA_FOUND exception.
Generating EAN13 In None
Using Barcode generator for Font Control to generate, create EAN13 image in Font applications.
www.OnBarcode.com
Draw Barcode In None
Using Barcode generator for Font Control to generate, create Barcode image in Font applications.
www.OnBarcode.com
CHAPTER 4 WHOA ! ONE STATEMEN T A T A TIM E, PLEA SE!
Draw DataMatrix In None
Using Barcode drawer for Font Control to generate, create DataMatrix image in Font applications.
www.OnBarcode.com
Drawing Barcode In None
Using Barcode printer for Font Control to generate, create Barcode image in Font applications.
www.OnBarcode.com
SELECT
Making UPC-A In None
Using Barcode creation for Font Control to generate, create UPC Symbol image in Font applications.
www.OnBarcode.com
RM4SCC Creation In None
Using Barcode maker for Font Control to generate, create RM4SCC image in Font applications.
www.OnBarcode.com
Your program reads index blocks from the database in order to see if a duplicate row exists, so your program is in control
Printing PDF417 In None
Using Barcode drawer for Microsoft Excel Control to generate, create PDF 417 image in Excel applications.
www.OnBarcode.com
Scan PDF 417 In Visual Studio .NET
Using Barcode scanner for .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
NO_DATA_ FOUND
Code39 Drawer In Java
Using Barcode creator for Eclipse BIRT Control to generate, create ANSI/AIM Code 39 image in BIRT reports applications.
www.OnBarcode.com
Barcode Creation In VB.NET
Using Barcode generation for Visual Studio .NET Control to generate, create Barcode image in .NET framework applications.
www.OnBarcode.com
INSERT
Generate UPC-A Supplement 5 In C#
Using Barcode generator for Visual Studio .NET Control to generate, create UCC - 12 image in .NET applications.
www.OnBarcode.com
Painting EAN-13 In Java
Using Barcode creation for Java Control to generate, create European Article Number 13 image in Java applications.
www.OnBarcode.com
UPDATE
EAN-13 Recognizer In None
Using Barcode decoder for Software Control to read, scan read, scan image in Software applications.
www.OnBarcode.com
Printing UPCA In VB.NET
Using Barcode maker for .NET framework Control to generate, create Universal Product Code version A image in Visual Studio .NET applications.
www.OnBarcode.com
DUP_VAL_ ON_INDEX
Barcode Generator In None
Using Barcode creation for Microsoft Excel Control to generate, create Barcode image in Microsoft Excel applications.
www.OnBarcode.com
GTIN - 13 Creator In C#.NET
Using Barcode generator for VS .NET Control to generate, create EAN / UCC - 13 image in Visual Studio .NET applications.
www.OnBarcode.com
UPDATE rowcount = 1
QR Code Generator In VS .NET
Using Barcode printer for .NET Control to generate, create QR Code image in Visual Studio .NET applications.
www.OnBarcode.com
UPC Code Drawer In Java
Using Barcode generator for Android Control to generate, create UCC - 12 image in Android applications.
www.OnBarcode.com
COMMIT
Figure 4-2. Select, then insert or update as needed However, you cannot ignore the possibility that, even though a duplicate entry did not exist at the moment you executed a SELECT statement against the database, someone or something has since inserted a duplicate entry. This could happen in the microseconds between the raised exception and the execution of your INSERT statement, so you must still catch any possible exceptions on the execution of the INSERT statement and act accordingly. This time, if the sql%rowcount variable is not equal to one, you need to try selecting the entry again. So which solution is better Once again, let s look at an example before you decide. Listing 4-3 is another modification of Listing 4-1. This time, I ve added a nested block of PL/SQL code to detect possible duplicate values. Listing 4-3. An Insert Example, Modified to Detect Duplicates with PL/SQL, insert_with_plsql_detection.sql 015 016 017 v_first_name v_middle_name v_last_name WORKER_T.first_name%TYPE; WORKER_T.middle_name%TYPE; WORKER_T.last_name%TYPE;
CHAPTER 4 WHOA ! ONE STATEMEN T A T A TIM E, PLEA SE!
018 019 ... 027 028 029 030 031 032 033 ... 061 062 063 064 065 066 067 068 069 070 071 072 073 074 075 076 077 078 079 080 081 082 083 084 085 086 087 088 089 090 091 092 093 094 095 096
v_name d_birth_date
WORKER_T.name%TYPE; WORKER_T.birth_date%TYPE;
v_first_name := 'JOHN'; v_middle_name := 'J.'; v_last_name := 'DOE'; v_name := rtrim(v_last_name||', '||v_first_name||' '||v_middle_name); d_birth_date := to_date('19800101', 'YYYYMMDD'); -- I'm guessing -- Detect any existing entries with the unique -- combination of columns as in this constraint: -- constraint WORKER_T_UK2 -- unique ( -- name, -- birth_date, -- gender_id ) begin select count(1) into n_count from WORKER_T where name = v_name and birth_date = d_birth_date and gender_id = n_gender_id; exception when OTHERS then raise_application_error(-20005, SQLERRM|| ' on select WORKER_T_T'|| ' in filename insert_with_plsql_detection.sql'); end; -- Conditionally insert the row if n_count = 0 then -- Now, let's get the next id sequence begin select WORKER_ID_SEQ.nextval into n_id from SYS.DUAL; exception when OTHERS then raise_application_error(-20001, SQLERRM|| ' on select WORKER_ID_SEQ.nextval'|| ' in filename insert_with_plsql_detection.sql'); end; -- And then, let's get the next external_id sequence
CHAPTER 4 WHOA ! ONE STATEMEN T A T A TIM E, PLEA SE!
097 098 099 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141
begin select lpad(to_char(EXTERNAL_ID_SEQ.nextval), 9, '0') into v_external_id from SYS.DUAL; exception when OTHERS then raise_application_error(-20003, SQLERRM|| ' on select EXTERNAL_ID_SEQ.nextval'|| ' in filename insert_with_plsql_detection.sql'); end; -- Now that we have all the necessary ID values -- we can finally insert a row! begin insert into WORKER_T ( id, worker_type_id, external_id, first_name, middle_name, last_name, name, birth_date, gender_id ) values ( n_id, n_worker_type_id, v_external_id, v_first_name, v_middle_name, v_last_name, v_name, d_birth_date, n_gender_id ); n_count := sql%rowcount; exception when OTHERS then raise_application_error(-20006, SQLERRM|| ' on insert WORKER_T'|| ' in filename insert_with_plsql_detection.sql'); end; else n_count := 0; end if;
CHAPTER 4 WHOA ! ONE STATEMEN T A T A TIM E, PLEA SE!
Let s review the modifications: On lines 15 through 19, I ve added variables to hold column values. I ve done this because I need to specify the column values more than once. This way, I can set their values once, and then know that I will consistently use the same values twice. Lines 27 through 33 initialize the variables I added on lines 15 through 19. A particularly troublesome issue is the variable v_name for column WORKER_T.name. It s a computed value that will be stored in the database in order to improve performance. This tactic will cause any relational database purist to have a hissy fit. But that s not what I find troublesome. Since it s a computed value, you must make sure it s always computed the same way. Hmm, that sounds like a job for a function dedicated to the WORKER_T table. We ll create one to handle this issue in 5, so don t sweat it yet. Lines 68 through 80 contain a new nested PL/SQL block that queries the WORKER_T table against the columns that make up the unique constraint in question, to see if an entry with duplicate values already exists in the table. The SQL statement simply counts the number of rows with duplicate values, storing the count in variable n_count. Line 83 has a new IF statement, which determines whether to insert a row. If the value of the variable n_count is 0, I go ahead and insert the row. Otherwise, on line 140, I set n_count to 0, in order to correctly report the number of rows inserted. In Listing 4-3, I was able to maintain control of the execution of my PL/SQL program. But what if I wanted to update the row that already existed Then I would have coded the detection block as shown in Listing 4-4. Listing 4-4. An Insert Example, Modified to Detect Duplicates with PL/SQL for Update, insert_with_ plsql_detection_for_update.sql 069 070 071 072 073 074 075 076 077 078 079 080 081 082 083 084 begin select id into n_id from WORKER_T where name = v_name and birth_date = d_birth_date and gender_id = n_gender_id; exception when NO_DATA_FOUND then n_id := NULL; -- Is this really needed when OTHERS then raise_application_error(-20003, SQLERRM|| ' on select WORKER_T_T'|| ' in filename insert_with_plsql_detection_for_update.sql'); end;
CHAPTER 4 WHOA ! ONE STATEMEN T A T A TIM E, PLEA SE!
085 086 087 088 089 090 091 092 093 094 095 096 097 098 099 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
-- Conditionally insert the row if n_id is NULL then -- Now, let's get the next id sequence begin select WORKER_ID_SEQ.nextval into n_id from SYS.DUAL; exception when OTHERS then raise_application_error(-20004, SQLERRM|| ' on select WORKER_ID_SEQ.nextval'|| ' in filename insert_with_plsql_detection_for_update.sql'); end; -- And then, let's get the next external_id sequence begin select lpad(to_char(EXTERNAL_ID_SEQ.nextval), 9, '0') into v_external_id from SYS.DUAL; exception when OTHERS then raise_application_error(-20005, SQLERRM|| ' on select EXTERNAL_ID_SEQ.nextval'|| ' in filename insert_with_plsql_detection_for_update.sql'); end; -- Now that we have all the necessary ID values -- we can finally insert a row! begin insert into WORKER_T ( id, worker_type_id, external_id, first_name, middle_name, last_name, name, birth_date, gender_id ) values ( n_id, n_worker_type_id, v_external_id, v_first_name,
CHAPTER 4 WHOA ! ONE STATEMEN T A T A TIM E, PLEA SE!
129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155
v_middle_name, v_last_name, v_name, d_birth_date, n_gender_id ); n_inserted := sql%rowcount; exception when OTHERS then raise_application_error(-20006, SQLERRM|| ' on insert WORKER_T'|| ' in filename insert_with_plsql_detection_for_update.sql'); end; else begin update WORKER_T set worker_type_id = n_worker_type_id where id = n_id; n_updated := sql%rowcount; exception when OTHERS then raise_application_error(-20007, SQLERRM|| ' on update WORKER_T'|| ' in filename insert_with_plsql_detection_for_update.sql'); end; end if;
On lines 69 through 83, I ve recoded the detection block to retrieve the primary key value for a row with duplicate values. If a duplicate row exists, variable n_id, initially NULL, will be set to the value of the primary key. Otherwise, if a duplicate row does not exist, a NO_DATA_FOUND exception will be raised. In turn, the exception will be handled by the WHEN NO_DATA_FOUND clause in the enclosing PL/SQL block s exception-handling section. There, in the EXCEPTION clause, I set the value of variable n_id to NULL, to flag that I did not find a duplicate. I actually don t need to set it to NULL, because it remained NULL when the exception was raised. But the Oracle PL/SQL documentation does not explicitly guarantee this behavior, so I set it to NULL. Also, on line 86, now I use the n_id variable to determine if I found a duplicate row. If n_id is NULL, I insert the row. Otherwise, on lines 143 through 154, I update the duplicate row with the worker_type_id value. So if this was a better tactic, what s best Before I answer that question, let s first digress a moment so I can finally explain the syntax of all those IF statements I ve been using all along!
Copyright © OnBarcode.com . All rights reserved.