Oracle第二天

存盤程序:

是指在一些大型資料庫系統中.一組為了完成特定功能的SQL陳述句集,存放在資料庫之中,一次編譯,永久有效

這是SQL陳述句是從0-50,每次增2,代碼如下:

--這是SQL陳述句是從0-50,每次增2
--/ 
CREATE OR REPLACE PROCEDURE x1
IS 
i INT :=0;
BEGIN 
LOOP
dbms_output.put_line(i);
i :=i+2;
EXIT WHEN i>50;
END LOOP;
END;
/
CALL x1();

附圖如下:

根據x2(值)中的值洗掉該表中的deptno為50的行,代碼如下:

-- 根據x2(值)中的值洗掉該表中的deptno為50的行   
--/
CREATE OR REPLACE PROCEDURE x2(i INT)
IS
NAME VARCHAR2(20);
BEGIN 
DELETE FROM SCOTT.DEPT WHERE DEPTNO=i;
END;
/         
CALL x2(50);

附圖如下:

根據deptno來查找相應的名字,代碼如下:

--    根據deptno來查找相應的名字    
--/
CREATE OR REPLACE PROCEDURE x3(i INT)    
IS 
NAME VARCHAR2(20);
BEGIN
SELECT dname INTO NAME FROM DEPT WHERE deptno=i;
dbms_output.put_line(NAME);
END;
/       
CALL x3(20);

存盤函式 :

我們查詢的時候使用的AVG,SUM,COUNT等都算是存盤函式,是一種方便我們對資料型別統計或操作的函式
(nvl :看comm 是否有值,如果是null,就變成0)

計算工資和獎金總和,代碼如下:

--a,b分別對應了sal,comm.
--/
CREATE OR REPLACE FUNCTION mySUM(a NUMBER,b NUMBER) RETURN NUMBER
IS
i NUMBER :=0;
BEGIN
i :=nvl(a,0)+nvl(b,0);
RETURN i;
END;
/
SELECT mySUM(sal,comm) FROM EMP;

附圖如下:

根據傳過來的DEPTNO的值來找到相應的部門編號,名稱,以及位置,代碼如下:

-- plsql 創建復合函式型別      
--/
CREATE OR REPLACE PROCEDURE x4(s NUMBER)
IS
TYPE DINFO IS RECORD(
deptno NUMBER,
dname VARCHAR2(20),
loc VARCHAR2(20)
);
d DINFO;
BEGIN
SELECT deptno,dname,loc INTO d.deptno,d.dname,d.loc FROM DEPT WHERE deptno=s;
dbms_output.put_line('部門編號'||d.deptno||'部門名稱'||d.dname||'部門位置'||d.loc);
END;
/
CALL x4(10)

附圖如下:

游標
是一個SQL記憶體的一個作業區,由系統或者用戶以變數的形式定義,并使用游標主要分為兩種
(一).顯式游標
資料庫中的DML操作,以及單行的DQL操作,會自動創建顯式游標

 			SQL%ROWCOUNT     可以獲得到SQL影響到的資料行數
            SQL%FOUND        用來判斷SQL有沒有影響,如果有回傳true,如果沒有回傳false
            SQL%NOTFOUND     用來判斷SQL有沒有影響,如果有回傳false,如果沒有回傳true

根據傳進去的值來與EMPNO進行對比,然后洗掉比傳入的s的值大的資料,然后使用"SQL%ROWCOUNT" 來計算影響到的函式 ,代碼如下:

--/ 
CREATE OR REPLACE PROCEDURE x5(s NUMBER)
IS        
BEGIN
DELETE FROM EMP WHERE EMPNO>s;
IF SQL%FOUND THEN
dbms_output.put_line('成功洗掉掉員工'|| SQL%ROWCOUNT||'個');
ELSE
dbms_output.put_line('沒有洗掉掉員工'||SQL%ROWCOUNT||'個');
END IF;
END;
/
INSERT INTO EMP(EMPNO) VALUES(8908);
CALL x5(8887);        

附圖如下:



(二).隱式游標
需要用戶手動創建,專門針對SELECT陳述句
查出員工的名字,代碼如下:

--/        
DECLARE
CURSOR YB IS SELECT * FROM EMP;                         --給SELECT * FROM EMP 創建一個隱式游標
E EMP%ROWTYPE;                                           --從游標中提取表結構,然后創建了一個跟表結構一樣的物件
BEGIN
OPEN YB;                                                  --開啟隱式游標
FETCH YB INTO E;                                         --把游標中的查詢出來的結果賦值給E
WHILE YB%FOUND LOOP
dbms_output.put_line('員工名字叫:'||E.ENAME);
FETCH YB INTO E;                                         --再一次將游標內的賦值給E
END LOOP;                                                --關閉loop
CLOSE YB;                                                 --關閉隱式游標(不關倍訓一直占用記憶體)
END;
/

附圖如下:

two_many_rows 查到了多條記錄
no_data_found 找不到記錄
OTHERS 其他所有例外,需要組合其他例外使用

--根據傳入的部門編號進行查詢,(這是手寫的例外)      
--/
CREATE OR REPLACE PROCEDURE x6(i NUMBER)
IS
n VARCHAR(20);
BEGIN 
SELECT ename INTO n FROM EMP WHERE deptno=i;
EXCEPTION
WHEN too_many_rows THEN
dbms_output.put_line('查到了不止一條記錄,請修改SQL或者引數');    
WHEN no_data_found THEN
dbms_output.put_line('找不到記錄');
WHEN OTHERS THEN
dbms_output.put_line('出現了很奇妙的例外');
END;
/
CALL x6(20);

附圖如下:

觸發器

行級觸發器(你的SQL影響了多少行,觸發器就執行了多少次)
   
陳述句觸發器(你的SQL無論影響了多少次,觸發器就只執行一次)

行級觸發器,實體代碼如下:

陳述句觸發器,實體代碼如下:

--陳述句觸發器
CREATE TABLE M(
ID INT
)
INSERT INTO M VALUES(S1.nextval);
SELECT * FROM M

--/
CREATE OR REPLACE TRIGGER MM
AFTER DELETE ON M
BEGIN
dbms_output.put_line('梁進峰摸電線,又觸電了');
END;
/
DELETE FROM M WHERE M.ID>6 

附圖如下: