基本語法 存儲過程 觸發(fā)器介紹

1、Oracle基本語法 存儲過程 觸發(fā)器介紹PL/SQL 語言介紹PL/SQL是Oracle數(shù)據(jù)庫的編程語言。我們可以將PL/SQL看成是對標(biāo)準(zhǔn)SQL的擴展，使用它編寫的代碼通常是放在Oracle數(shù)據(jù)庫中執(zhí)行。概述PL/SQL是Oracle數(shù)據(jù)庫專用的語言，具有第三代編程語言和第四代編程語言的特性，對大小寫不敏感?；诔绦驂K的開發(fā)PL/SQL代碼使用了程序塊（block），利用模塊化的方式進行構(gòu)建，每一個程序塊都是由一組邏輯上的變量、可執(zhí)行代碼以及異常處理代碼構(gòu)成。其中，只有可執(zhí)行代碼部分是必須的。set serveroutput ondeclare l_text varchar2(100);-

2、請注意這里的分號begin l_text:='Hello,world!'-請注意這里的冒號 dbms_output.put_line(l_text);-請注意這里的點號 exception when others then dbms_output.put_line('出現(xiàn)問題啦.'); raise;end;/請注意觀察上述代碼，每一個執(zhí)行語句都是使用分號作為結(jié)束標(biāo)記。由declare部分引出的是程序塊的聲明部分，通常情況下聲明部分是定義所有變量和常量的地方，該部分是可選的。由begin、end引出的是程序塊的執(zhí)行部分，又稱為執(zhí)行體，這里通常是處理執(zhí)行邏輯的地方，

3、該部分是必須的。（注意：可以直接寫null，代表什么操作都不做）由exception部分引出的是程序塊的異常處理部分，這里是我們檢查和控制可能會在程序塊中遇到的錯誤的地方，Oracle會在錯誤發(fā)生時，自動的跳轉(zhuǎn)到這里。聲明通過上面的代碼，我們可以看到在聲明中可以使用變量和常量。請注意，變量賦初值是可選的，而常量則是必須的。變量和常量在使用之前必須在程序塊的聲明部分進行聲明（或定義）。declare l_number_variable number:=50;begin null;end;/declare l_number_constant constant number:=20;begin nu

4、ll;end;/現(xiàn)在我們嘗試下述做法，請注意Oracle的出錯提示declare l_number_constant constant number;begin null;end;/Oracle提示“常數(shù) 'L_NUMBER_CONSTANT' 的說明必須包含初始賦值”declare l_number_constant constant number :=20;begin l_number_constant:=50;end;/Oracle提示“表達(dá)式 'L_NUMBER_CONSTANT' 不能用作賦值目標(biāo)”前一種異常會在程序塊的編譯期間被捕獲，并且不能恢復(fù)，程

5、序塊的異常處理部分也不能捕獲這種錯誤。（請考慮如果在后一種情況下我們加上自己的異常處理代碼是否能夠捕獲錯誤）為變量和常量賦值的時候均是采用“:=”操作符，存儲過程存儲過程（stored procedure）從本質(zhì)上來講就是命名的PL/SQL程序塊，它可以被賦予參數(shù)。以下不經(jīng)特別說明，過程均指的是存儲過程。create procedure p_Test as begin null; end p_Test; / 請注意，在創(chuàng)建過程的時候，我們要在最后放置一個“/”（執(zhí)行）符號。標(biāo)準(zhǔn)存儲過程的語法如下CREATE OR REPLACE PROCEDURE procedure_name (parame

6、ter,parameter.) AUTHID DEFINER|CURRENT_USER IS|AS PRAGMA AUTONOMOUS_TRANSACTION; local declarations BEGIN executable statements EXCEPTION exception handlers END procedure_name;存儲過程的語法和匿名PL/SQL程序塊非常類似，只需要將PL/SQL程序塊的declare部分替換成create or replace procedure procedure_name as即可（在begin-end之間可能有些小的改動）。decl

7、arebegin dbms_output.put_line('Hello,world');end;/-create procedure p_hello asbegin dbms_output.put_line('Hello,world');end p_hello;/執(zhí)行存儲過程的方式有兩種，請參閱下述示例set serveroutput onbegin p_hello;end;/-execute p_hello;兩種方式的效果完全一樣。過程可以進行參數(shù)化的處理，過程的調(diào)用者傳遞一個值即可以使用它。參數(shù)可以是任何合法的PL/SQL類型，通常采用3種形式：IN、OU

8、T和IN OUT。IN-通過調(diào)用者傳入，只能夠由過程讀取。 OUT-參數(shù)能夠由過程寫入。 IN OUT-過程能夠讀取或?qū)懭胨鼈兊闹?。SEQUENCE 對象介紹CREATE SEQUENCE seq_slof_apply INCREMENT BY 1 START WITH 2 MAXVALUE 999999999999999/SQL> select seq_slof_apply.nextval from dual; NEXTVAL- 2-我們要使用的存儲過程create or replace procedure get_maxid( sign_str in varchar2, -這里的標(biāo)志

9、前綴由每個小組自己控制,調(diào)用時從外部傳入 new_id out varchar2 -使用OUT參數(shù)，準(zhǔn)備用來獲得新的id)asnew_count number;begin select count(*) into new_count from slof_apply; -請注意，在PL/SQL程序塊中，此處必須加into if new_count=0 then -第一次調(diào)用時數(shù)據(jù)庫沒有數(shù)據(jù),我們應(yīng)該做初始化設(shè)置 new_id:=sign_str|'000000000000001' -請注意這里賦值的寫法 else select seq_slof_apply.nextval int

10、o new_id from dual; while length(new_id)<15 loop new_id:='0'|new_id; end loop; new_id:=sign_str|new_id; end if;end get_maxid;/Create table slof_apply(Apply_id number(15)set serveroutput on declare v_num1 varchar2(20); begin get_maxid('LL_',v_num1); dbms_output.put_line('v_num1

11、='|v_num1); end;/觸發(fā)器分為語句級觸發(fā)器和行級觸發(fā)器我們先看一個AFTER-INSERT-STATEMENT觸發(fā)器：CREATE OR REPLACE TRIGGER temp_aisAFTER INSERT ON TEMPBEGIN dbms_output.put_line('executing temp_ais');END;看一下下面語句的結(jié)果：SQL> set feedback offSQL> INSERT INTO temp VALUES (1); - insert 1 rowexecuting temp_aisSQL> INS

12、ERT INTO temp VALUES (1); - insert 1 rowexecuting temp_aisSQL> INSERT INTO temp SELECT * FROM temp; - insert 2 rowsexecuting temp_ais每個SQL插入語句將觸發(fā)一次，行級觸發(fā)器最后一條語句要觸發(fā)兩次。一、事件順序如果一個update SQL語句更新三行，那么行級觸發(fā)器觸發(fā)三次，語句級觸發(fā)器觸發(fā)一次。二、insert 語句級觸發(fā)器定義語法語法如下：CREATE OR REPLACE TRIGGER trigger_nameAFTER | BEFORE INSER

13、T ON table_nameDECLARE Local declarationsBEGIN Body written PL/SQLEND;語句級和行級觸發(fā)器在語法上關(guān)鍵的不同在于：FOR EACH ROW字句。在行級觸發(fā)器中指定這個子句而語句級觸發(fā)器中不需要指定。 在語句級觸發(fā)器中： ·引用:NEW.COLUMN_NAME :OLD.COLUMN_NAME是不正確的。(1)第一種方法 ORACLE一般的做法是同時使用序列和觸發(fā)器來生成一個自增字段.CREATE SEQUENCE SEQname INCREMENT BY 1 START WITH 1 MAXVALUE 9999999

14、9/CREATE TRIGGER TRGnameBEFORE INSERT ON table_nameFOR EACH ROWBegin SELECT SEQname.NEXTVAL INTO :NEW.FIELDname FROM DUAL;End;(2)第二種方法：CREATE OR REPLACE TRIGGER TR1 BEFORE INSERT ON temp_table FOR EACH ROWdeclare com_num NUMBER;BEGINSELECT MAX(ID) INTO COM_NUM FROM TEMP_TABLE; :NEW.ID:=COM_NUM+1;END

15、 TR1;package test;import java.sql.*;import connection.MyConnection;import oracle.jdbc.*;public class test_proc public static void main(String g)Connection conn;MyConnection myconn = new MyConnection();conn = myconn.getConnection();String sign_str = "LL_"String new_id = ""String s

16、trSQL = "call get_maxid(?,?)"tryCallableStatement callstmt = conn.prepareCall(strSQL);callstmt.setString(1,sign_str);callstmt.registerOutParameter(2,Types.VARCHAR);callstmt.execute();new_id = callstmt.getString(2);System.out.println("new_id = "+new_id);catch(Exception e)CREATE OR

17、 REPLACE TRIGGER delete_plcAFTER DELETE ON whl_plmFOR EACH ROWBEGINdelete from whl_plc where plc_pickinglistid=:old.plm_pickinglistid;END;create or replace procedure get_maxid( sign_str in varchar2, -這里的標(biāo)志前綴由每個小組自己控制,調(diào)用時從外部傳入 new_id out varchar2 -使用OUT參數(shù)，準(zhǔn)備用來獲得新的id)asnew_count number;begin select count(*) into new_count from whl_plm; -請注意，在PL/SQL程序塊中，此處必須加into if to_