kinanson的技術回憶

前言

mssql雖然比較熟悉，但筆者一直都沒有很專注在db的領域裡面，不過團隊目前使用的是oracle，不管是工具的使用上或者是拆解落落長的stored procedure，筆者都沒有經驗，所以這篇是想記錄一 下，最近寫oracle的一些筆記和技巧

導覽

1. 關於oracle一些stored procedure的概念
2. 如何傳遞陣列參數
3. 如何有效的拆解stored procedure在sql查詢視窗

關於oracle一些stored procedure的概念

oracle因為有分成pkg的方式，所以可以用ddd(領域驅動開發)的概念，來為我們千千萬萬的sp做分類，從下圖可以看出，除了有sp之外還有一種名稱為packages的

在package裡面我們需要先建立一個類似介面的概念，接著則建立實做如下圖是一個展開的package

我們可以看到有一個body還有很多sp，我們直接看到的sp是屬於介面的部份，而PKG_CACHE Body則是實做的部份，接著點擊任一看到的sp

如何傳遞陣列參數

我們都知道，如果我們有一個陣列，比較基本的方式就是跑一個迴圈，然後去執行sp，但這樣頻繁的呼叫sp，對於效能方面可能就不是那麼的理想，其實更好的方式乾脆把陣列丟進sp裡面，這樣子一定會是最好的方式，接著來看一下如何在oracle實現，首先我們需要了解一下在oracle裡面可以定義type，或者直接定義一個package裡面放一些type

-- 底下是建立一個package
create or replace PACKAGE PKG_ARRAY AS
-- 底下則是各定義了數字和字串的陣列
  TYPE NUMBER_ARRAY IS TABLE OF number INDEX BY BINARY_INTEGER;
  TYPE STRING_ARRAY IS TABLE OF VARCHAR(200) INDEX BY BINARY_INTEGER;
END PKG_ARRAY;

或者直接建立一個type

create or replace  TYPE NUMBER_ARRAY IS TABLE OF number INDEX BY BINARY_INTEGER;

建立之後就可以使用自行建立的type當成參數傳進stored procedure了，以下的例子是使用packages裡的type

PROCEDURE SP_UPDATEMLHIDE
  (
    IN_TESTID IN PKG_ARRAY.NUMBER_ARRAY,
    IN_HIDE IN PKG_TEST.NUMBER_ARRAY
  )AS
  begin
   forall idx in 1 .. IN_TESTID.count
	 Update TEST Set
        ISHIDE = IN_HIDE(idx)
        where TESTid = IN_TESTID(idx);
  END SP_UPDATEMLHIDE;

如何有效的拆解stored procedure在sql查詢視窗

有時候stored procedure一長起來，沒有把程式碼拆開來，其實真的很難除錯，以上一個例子的sp足夠簡單，所以就不太需要把程式碼拿來sql查詢視窗除錯，但是在此我還是以上面的sp來做例子，首先我們要定義變數的話，一定要有begin和end的區段，定義變數型態是在最上面，而指定數值則是在begin and end裡面，如下示例。

DECLARE
  IN_TESTID PKG_ARRAY.NUMBER_ARRAY;
  IN_HIDE PKG_ARRAY.NUMBER_ARRAY;
BEGIN
  IN_TESTID(1)         :=2189919;
  IN_TESTID(2)         :=2189918;
  IN_HIDE(1)                 :=2192352;
  IN_HIDE(2)                 :=2192353;
  forall idx in 1 .. IN_TESTID.count
	 Update TEST Set
        ISHIDE = IN_HIDE(idx)
        where TESTid = IN_TESTID(idx);
END;