項目中oracle預存程序記錄——常用文法備忘

標籤：oracle   預存程序   日期加減   遊標迴圈   

項目中oracle預存程序記錄——常用文法備忘

 

項目中需要寫一個oracle預存程序，需求是收集一個複雜查詢的內容（涉及到多張表），然後把合格記錄插入到目標表中。其中原表之一的日期欄位是timestamp類型，目標表的欄位是varchar2類型；

       其中一些內容很常用，所以做下記錄，供尋找。

1、預存程序的格式

       oracle預存程序和函數都可以實現，一般沒有返回值，則採用預存程序，函數比sqlserver的功能強大。oracle變數定義最好加上首碼如V_，查詢條件中變數名稱和欄位名稱不能重複。

CREATEOR REPLACE PROCEDURE PROC_NAME (

  v_interval IN NUMBER DEFAULT -3 –PARAM_NAME_LIST

)AS

  v_cnt            number(4); -- 定義變數及遊標

BEGIN

--商務邏輯語句

ENDPROC_NAME

 

2、遊標定義和迴圈

cursor  CURSOR_NAME is

              SELECT * FROM DUAL – SELECT 語句；

迴圈遊標有多種方式，最簡單的for方式，避免定義一些變數及開啟、關閉遊標，可以簡化很多代碼，但是如果需要訪問遊標記錄條數，就需要loop或while迴圈。

forloop 文法：

FOR curRow   IN CURSOR_NAME  -- curRow是遊標的行記錄變數

LOOP

--直接通過curRow.遊標欄位取值（省略了變數的定義）

ENDLOOP;

 

3、日期的加減計算

DATE類型是一個7位元組的定寬日期/時間資料類型。它總是包含7個屬性，包括：世紀、世紀中哪一年、月份、月中的哪一天、小時、分鐘和秒;TIMESTAMP類型與DATE非常類似，只不過另外還支援小數秒和時區。下面的N值可以為負數。

 

?  使用NUMTODSINTERVAL內建函數來增加小時、分鐘和秒。

比如：DATE+NUMTODSINTERVAL(n,‘minute‘)

 

?  加一個簡單的數來增加天。

比如：DATE+n

 

?  使用ADD_MONTHS內建函數來增加月和年。

比如：ADD_MONTHS(DATE,n)

 

4、日期類型轉成字元

       select to_char(systimestamp,‘yyyy-mm-dd hh24:mi:ssxff‘)time1 from dual;

       --年月日時分秒及6位毫秒；

       select to_char(systimestamp  ,‘yyyy-mm-dd hh24:mi:ss.ff1‘) from dual;

       --年月日時分秒及毫秒(位元由ff後面的數字決定，1~9之間，ff3表示保留三位毫秒)

 

5、if exist 替代文法

       oracel沒有sqlserver的if exist 文法，只能變形實現，建議使用下面的文法：

v_cnt            number(4); -- 聲明變數；

selectcount(*) into v_cnt from dual where exists (SELECT 語句);

樣本：

declare

  v_cnt number;

begin

  select count(*)  into v_cnt from dual

        where exists (select * from table_namewhere col_name=1);

  if v_cnt = 0 then

dbms_output.put_line(‘無記錄, 在此寫你的業務代碼‘);

endif;

end;

 

6、空值

       如果欄位不允許為空白，使用nvl函數；如：nvl(field_name,’ ’)，需要說明的兩個單引號直接是空格，如果沒有任何字元，oracle也視為null。

 

7、調試預存程序

       複雜的業務一般都需要調試，感覺PLSQLDeveloper調試比較方便。右鍵選擇需要調試的預存程序，在測試視窗即可單步調試。

項目中oracle預存程序記錄——常用文法備忘