如何使用數據庫12.2簡化數據驗證代碼？

無論你做了多少測試（嗯，它實際上是這樣，但這是一個完全不同的問題），你幾乎可以保證，在某些時刻，你那些漂亮的數據驗證代碼，在解析從Web表單輸入的數據或從外部加載數據文件時，會彈出錯誤：

 

 

當然，這裏真正令人討厭的是，你不知道哪個列的值（假設你有多個數字列）。

 

在數據加載期間管理轉換錯誤

 

怎麼辦？ 當然，明智的是，在你的代碼中添加大量的數據驗證檢查，以嚐試捕獲來自數據源的錯誤類型數據所到達的情況。很可能所有的附加驗證檢查將減慢插入數據的過程，但這不是一個很好的結果。

 

如果您的數據通過外部文件到達，那麼您可以使用BADFILE子句捕獲那些由於數據類型錯誤而無法加載的記錄。 但是，如果插入語句的數據源是由ETL作業填充的中間表或來自網頁表單的一係列值，又該怎麼辦？

 

如何在INSERT期間管理轉換錯誤

​

Panic over - Database 12c版本2包含了對CAST和TO_xxx函數的重要更改，以管理最常見的數據轉換錯誤。 如果存在轉換錯誤，CAST函數現在可以返回用戶指定的值。

 

例如，讓我們在模式中構建一個簡單的計劃表：

 

並讓我們插入一些數據，其中包括在我們嚐試將值添加到目標表中時可能導致數據轉換錯誤的值：

 

現在讓我們試著將數據從我們的分期表插入到EMP表，看看會發生什麼：

 

 

……毫不意外的，我得到了以下錯誤：

 

 

我可以用幾種不同的方式來處理這種情況。首先，嚐試並發現臨時表中的哪些行和列會包含導致數據轉換錯誤的值。為此，我將使用新的VALIDATE_CONVERSION（）函數，該函數標識無法轉換為所需數據類型的問題數據。如果給定的表達式可以轉換為指定的數據類型，則返回1，否則返回0。

 

 

這將產生一個表，其中我可以輕鬆地選擇數據轉換將要成功（列值為1）和失敗（列值為0）的行：

 

 

我可以使用此信息過濾臨時表中的數據，因為我將其插入到我的EMP表或我可以使用INSERT INTO ... .. SELECT語句中增強的CAST和TO_xxx函數。

 

當發生數據類型轉換錯誤時，CAST函數（以及TO_NUMBER，TO_BINARY_FLOAT，TO_BINARY_DOUBLE，TO_DATE，TO_TIMESTAMP，TO_TIMESTAMP_TZ，TO_DSINTERVAL和TO_YMINTERVAL函數）現在可以返回用戶指定的值，而不是錯誤。這減少了數據轉換和數據加載過程中的故障。

 

因此，我新的12.2版本驗證SELECT語句如下所示：

 

 

這五行結果插入到我的EMP表中 - 顯然這意味著在插入過程（行1,4,6和8）期間拒絕了第4行，因為它們包含將內容轉換為empno鍵的一個數字錯誤。 這裏是加載的數據：

 

 

我們可以看到在第1行，HIERDATE無效，所以它被替換為sys日期（07-JUL-16）的值。 第2行，DEPTNO的值是轉換默認值99，而在第4行，MGR的值是轉換默認值9999。

 

總結

 

增強的CAST函數（以及TO_NUMBER，TO_BINARY_FLOAT，TO_BINARY_DOUBLE，TO_DATE，TO_TIMESTAMP，TO_TIMESTAMP_TZ，TO_DSINTERVAL和TO_YMINTERVAL函數）可幫助您處理數據轉換錯誤，而無需使用複雜的PL / SQL代碼或在應用程序代碼中寫入數據驗證例程。

 

新的VALIDATE_CONVERSION（）函數可用於幫助您標識無法轉換為所需數據類型的列值。

 

這兩個功能都很有用。希望你會喜歡！

原文發布時間為：2017-03-24

本文來自雲棲社區合作夥伴DBAplus