Oracle中視圖丶觸發器丶存儲過程/函數

視圖

視圖就是封裝了一條複雜查詢的語句。

語法 1.：CREATE VIEW 視圖名稱 AS 子查詢

語法 2：CREATE OR REPLACE VIEW 視圖名稱 AS 子查詢

語法 3：CREATE OR REPLACE VIEW 視圖名稱 AS 子查詢 WITH READ ONLY

---視圖

---視圖的概念：視圖就是提供一個查詢的窗口，所有數據來自於原表。

---查詢語句創建表

create table emp as select * from scott.emp;

select * from emp;

---創建視圖【必須有dba許可權】

create view v_emp as select ename, job from emp;

---查詢視圖

select * from v_emp;

---修改視圖[不推薦]

update v_emp set job="CLERK" where ename="ALLEN";

commit;

---創建只讀視圖

create view v_emp1 as select ename, job from emp with read only;

---視圖的作用？

---第一：視圖可以屏蔽掉一些敏感欄位。

---第二：保證總部和分部數據及時統一。

ps:物化視圖:會在資料庫中真正找到一張表,而普通視圖不是

索引

索引是用於加速數據存取的數據對象。合理的使用索引可以大大降低 i/o 次數,從而

提高數據訪問性能。

---索引

--索引的概念：索引就是在表的列上構建一個二叉樹

----達到大幅度提高查詢效率的目的，但是索引會影響增刪改的效率。

---單列索引

---創建單列索引

create index idx_ename on emp(ename);

---單列索引觸發規則，條件必須是索引列中的原始值。

---單行函數，模糊查詢，都會影響索引的觸發。

select * from emp where ename="SCOTT"

---複合索引

---創建複合索引

create index idx_enamejob on emp(ename, job);

---複合索引中第一列為優先檢索列

---如果要觸發複合索引，必須包含有優先檢索列中的原始值。

select * from emp where ename="SCOTT" and job="xx";---觸發複合索引

select * from emp where ename="SCOTT" or job="xx";---不觸發索引

select * from emp where ename="SCOTT";---觸發單列索引。

pl/sql

---pl/sql編程語言

---pl/sql編程語言是對sql語言的擴展，使得sql語言具有過程化編程的特性。

---pl/sql編程語言比一般的過程化編程語言，更加靈活高效。

---pl/sql編程語言主要用來編寫存儲過程和存儲函數等。

---聲明方法

---賦值操作可以使用:=也可以使用into查詢語句賦值

declare

i number(2) := 10;

s varchar2(10) := "小明";

ena emp.ename%type;---引用型變數

emprow emp%rowtype;---記錄型變數

begin

dbms_output.put_line(i);

dbms_output.put_line(s);

select ename into ena from emp where empno = 7788;

dbms_output.put_line(ena);

select * into emprow from emp where empno = 7788;

dbms_output.put_line(emprow.ename || "的工作為：" || emprow.job);

end;

---pl/sql中的if判斷

---輸入小於18的數字，輸出未成年

---輸入大於18小於40的數字，輸出中年人

---輸入大於40的數字，輸出老年人

declare

i number(3) := ⅈ

begin

if i<18 then

dbms_output.put_line("未成年");

elsif i<40 then

dbms_output.put_line("中年人");

else

dbms_output.put_line("老年人");

end if;

end;

---pl/sql中的loop循環

---用三種方式輸出1到10是個數字

---while循環

declare

i number(2) := 1;

begin

while i<11 loop

dbms_output.put_line(i);

i := i+1;

end loop;

end;

---exit循環

declare

i number(2) := 1;

begin

loop

exit when i>10;

dbms_output.put_line(i);

i := i+1;

end loop;

end;

---for循環

declare

begin

for i in 1..10 loop

dbms_output.put_line(i);

end loop;

end;

---游標：可以存放多個對象，多行記錄。

---輸出emp表中所有員工的姓名

declare

cursor c1 is select * from emp;

emprow emp%rowtype;

begin

open c1;

loop

fetch c1 into emprow;

exit when c1%notfound;

dbms_output.put_line(emprow.ename);

end loop;

close c1;

end;

-----給指定部門員工漲工資

declare

cursor c2(eno emp.deptno%type)

is select empno from emp where deptno = eno;

en emp.empno%type;

begin

open c2(10);

loop

fetch c2 into en;

exit when c2%notfound;

update emp set sal=sal+100 where empno=en;

commit;

end loop;

close c2;

end;

----查詢10號部門員工信息

select * from emp where deptno = 10;

存儲過程與存儲函數

--存儲過程

--存儲過程：存儲過程就是提前已經編譯好的一段pl/sql語言，放置在資料庫端

--------可以直接被調用。這一段pl/sql一般都是固定步驟的業務。

----給指定員工漲100塊錢

create or replace procedure p1(eno emp.empno%type)

is

begin

update emp set sal=sal+100 where empno = eno;

commit;

end;

select * from emp where empno = 7788;

----測試p1

declare

begin

p1(7788);

end;

----通過存儲函數實現計算指定員工的年薪

----存儲過程和存儲函數的參數都不能帶長度

----存儲函數的返回值類型不能帶長度

create or replace function f_yearsal(eno emp.empno%type) return number

is

s number(10);

begin

select sal*12+nvl(comm, 0) into s from emp where empno = eno;

return s;

end;

----測試f_yearsal

----存儲函數在調用的時候，返回值需要接收。

declare

s number(10);

begin

s := f_yearsal(7788);

dbms_output.put_line(s);

end;

---out類型參數如何使用

---使用存儲過程來算年薪

create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number)

is

s number(10);

c emp.comm%type;

begin

select sal*12, nvl(comm, 0) into s, c from emp where empno = eno;

yearsal := s+c;

end;

---測試p_yearsal

declare

yearsal number(10);

begin

p_yearsal(7788, yearsal);

dbms_output.put_line(yearsal);

end;

----in和out類型參數的區別是什麼？

---凡是涉及到into查詢語句賦值或者:=賦值操作的參數，都必須使用out來修飾。

---存儲過程和存儲函數的區別

---語法區別：關鍵字不一樣，

------------存儲函數比存儲過程多了兩個return。

---本質區別：存儲函數有返回值，而存儲過程沒有返回值。

----------如果存儲過程想實現有返回值的業務，我們就必須使用out類型的參數。

----------即便是存儲過程使用了out類型的參數，起本質也不是真的有了返回值，

----------而是在存儲過程內部給out類型參數賦值，在執行完畢後，我們直接拿到輸出類型參數的值。

----我們可以使用存儲函數有返回值的特性，來自定義函數。

----而存儲過程不能用來自定義函數。

----案例需求：查詢出員工姓名，員工所在部門名稱。

----案例準備工作：把scott用戶下的dept表複製到當前用戶下。

create table dept as select * from scott.dept;

----使用傳統方式來實現案例需求

select e.ename, d.dname

from emp e, dept d

where e.deptno=d.deptno;

----使用存儲函數來實現提供一個部門編號，輸出一個部門名稱。

create or replace function fdna(dno dept.deptno%type) return dept.dname%type

is

dna dept.dname%type;

begin

select dname into dna from dept where deptno = dno;

return dna;

end;

---使用fdna存儲函數來實現案例需求：查詢出員工姓名，員工所在部門名稱。

select e.ename, fdna(e.deptno)

from emp e;

ps:call(p1):call只能調用存儲過程,不能調用存儲函數

觸發器

---觸發器，就是制定一個規則，在我們做增刪改操作的時候，

----只要滿足該規則，自動觸發，無需調用。

----語句級觸發器：不包含有for each row的觸發器。

----行級觸發器：包含有for each row的就是行級觸發器。

-----------加for each row是為了使用:old或者:new對象或者一行記錄。

---語句級觸發器

----插入一條記錄，輸出一個新員工入職

create or replace trigger t1

after

insert

on person

declare

begin

dbms_output.put_line("一個新員工入職");

end;

---觸發t1

insert into person values (1, "小紅");

commit;

select * from person;

---行級別觸發器

---不能給員工降薪

---raise_application_error(-20001~-20999之間, "錯誤提示信息");

create or replace trigger t2

before

update

on emp

for each row

declare

begin

if :old.sal>:new.sal then

raise_application_error(-20001, "不能給員工降薪");

end if;

end;

----觸發t2

select * from emp where empno = 7788;

update emp set sal=sal-1 where empno = 7788;

commit;

----觸發器實現主鍵自增。【行級觸發器】

---分析：在用戶做插入操作的之前，拿到即將插入的數據，

------給該數據中的主鍵列賦值。

create or replace trigger auid

before

insert

on person

for each row

declare

begin

select s_person.nextval into :new.pid from dual;

end;

--查詢person表數據

select * from person;

---使用auid實現主鍵自增

insert into person (pname) values ("a");

commit;

insert into person values (1, "b");

commit;

java操作oracle

jar包對應關係

----oracle10g ojdbc14.jar

----oracle11g ojdbc6.jar

簡單的操作實例

import oracle.jdbc.driver.OracleTypes;

import org.junit.Test;

import java.sql.*;

/**

* Created by SunYuqin in 2018/11/3

* Code without comments is soulless

**/

public class OracleTest {

//簡單測試連接

@Test

public void test() throws Exception {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.154.10:1521:orcl", "sun", "root");

PreparedStatement pstmt = conn.prepareStatement("select * from person where id=?");

pstmt.setObject(1,1);

ResultSet rs = pstmt.executeQuery();

while (rs.next()){

System.out.println(rs.getString("name"));

}

rs.close();

pstmt.close();

conn.close();

}

//測試存儲函數

@Test

public void testfunction() throws Exception {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.154.10:1521:orcl", "sun", "root");

CallableStatement pstmt = conn.prepareCall("{?=call f_yearsal(?)}");

pstmt.setObject(2,7788);

pstmt.registerOutParameter(1,OracleTypes.NUMBER);

pstmt.execute();

System.out.println(pstmt.getObject(1));

pstmt.close();

conn.close();

}

//測試存儲過程

@Test

public void testprocedure() throws Exception {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.154.10:1521:orcl", "sun", "root");

CallableStatement pstmt = conn.prepareCall("{call p_yearsal(?, ?)}");

pstmt.setObject(1,7788);

pstmt.registerOutParameter(2, OracleTypes.NUMBER);

pstmt.execute();

System.out.println(pstmt.getObject(2));

pstmt.close();

conn.close();

}

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！