項目中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調試比較方便。右鍵選擇需要調試的存儲過程，在測試窗口即可單步調試。