role.png

] args) {

                try (Connection conn = DriverManager.getConnection(
                        "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
                        OracleCallableStatement prepareCall = (OracleCallableStatement) conn
                                .prepareCall("{call pack_emp2.proc_get_emp(?,?)}");) {
                    prepareCall.setInt(1, 10);
                    // 如果有輸出參數(shù)需要進(jìn)行住注冊(cè)
                    prepareCall.registerOutParameter(2, OracleTypes.CURSOR);
                    // 然后執(zhí)行
                    prepareCall.execute();
                    // 然后獲取到輸出參數(shù)
                    ResultSet rs = prepareCall.getCursor(2);
                    if (rs != null) {
                        while (rs.next()) {
                            String ename = rs.getString("ename");
                            double sal = rs.getDouble("sal");
                            String job = rs.getString("job");
                            System.out.println(ename + "的職位是:" + job + " 工資是:" + sal);
                        }
                    }
        
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }

1.3 刪除包

1. 刪除包體:drop package body 包名;
2. 刪除包:drop package 包名;

2 觸發(fā)器

2.1 概念

1. 觸發(fā)器就是一個(gè)可以自動(dòng)執(zhí)行的PLSQL塊.當(dāng)數(shù)據(jù)庫(kù)或者是數(shù)據(jù)庫(kù)對(duì)象的狀態(tài)發(fā)生改變的時(shí)候,Oracle數(shù)據(jù)庫(kù)就會(huì)自動(dòng)點(diǎn)燃某一個(gè)觸發(fā)器,然后執(zhí)行該觸發(fā)器中的PLSQL塊.

2.2 常見的觸發(fā)器

1. 系統(tǒng)觸發(fā)器
   • 打開或者關(guān)閉數(shù)據(jù)庫(kù),可以觸發(fā)的事件
2. DDL觸發(fā)器
   • 表的定義語(yǔ)言觸發(fā)
3. DML觸發(fā)器
   • 對(duì)數(shù)據(jù)庫(kù)表的增刪改操作創(chuàng)造觸發(fā)器
   • 系統(tǒng)和DDL觸發(fā)器創(chuàng)建的,我們接觸的是DML觸發(fā)器.

2.3 創(chuàng)建DML觸發(fā)器

1. 一個(gè)觸發(fā)器由三部分組成

   • 觸發(fā)時(shí)間
   • 觸發(fā)事件
   • 觸發(fā)動(dòng)作(PLSQL塊)

2. 語(yǔ)法格式:

    create or replace tigger 觸發(fā)器
    before|after  -- 觸發(fā)的時(shí)間:是在DML操作之前還是之后觸發(fā)
    insert|update|delete [of column_name] on table_name -- 觸發(fā)的事件:可以針對(duì)某一個(gè)表的某一列的某操作觸發(fā)
    [for each row]
    begin
        -- 觸發(fā)的動(dòng)作
    end;
   

   • 如果指定了for each row參數(shù),該觸發(fā)器就是一個(gè)行級(jí)觸發(fā)器,如果沒有指定該參數(shù)的話,該觸發(fā)器就是一個(gè)列級(jí)的觸發(fā)器;如果是行級(jí)觸發(fā)器,每影響一行數(shù)據(jù)就會(huì)點(diǎn)燃一次觸發(fā)器;如果是列級(jí)觸發(fā)器,無(wú)論影響多少數(shù)據(jù)都只會(huì)點(diǎn)燃一次觸發(fā)器.

3. 示例:

    -- 創(chuàng)建觸發(fā)器
    create or replace trigger tri_add_emp
    before -- 觸發(fā)的時(shí)間
    insert on emp
    for each row
    begin
      -- 觸發(fā)動(dòng)作
        dbms_output.put_line('觸發(fā)器被點(diǎn)燃了....');
    end;
    
    insert into emp (empno,ename) values (2003,'狗狗狗');
   

2.4 應(yīng)用的場(chǎng)景

1. 場(chǎng)景一:執(zhí)行DML操作之前對(duì)數(shù)據(jù)進(jìn)行校驗(yàn):

   • 需求:老板給員工漲工資,但是要求漲后的工資必須要大于漲之前的工資.

   • :old 引用DML操作前的那一行記錄

   • :new 引用DML操作后的那一行記錄

     create or replace trigger tri_add_sal
     before
     update of sal on emp
     for each row
     begin
     -- 如果漲后工資小于漲之前的工資
     if :new.sal <= :old.sal then
     -- 阻止DML語(yǔ)句的執(zhí)行(拋出異常)
     raise_application_error(-20001,'哎,工資越來(lái)越少了');
     else
     dbms_output.put_line('謝謝老板!');
     end if;
     end;

2. 場(chǎng)景二:實(shí)現(xiàn)主鍵列的自增長(zhǎng)

    -- 實(shí)現(xiàn)主鍵列的自增長(zhǎng)
    create or replace trigger tri_generate_gk
    before
    insert on emp
    for each row
    begin
      --給新插入的數(shù)據(jù)的empno列設(shè)置一個(gè)序列值
      select seq_emp_empno.nextval into :new.empno from dual;
    
    end;
    
    insert into emp (ename) values ('狗哥' || seq_emp_empno.nextval);
   

3. 場(chǎng)景三:對(duì)刪除的數(shù)據(jù)進(jìn)行備份

    -- 創(chuàng)建emp的備份表
    create table emp_bak
    as
    select * from emp where 1=2;
    
    -- 需求:當(dāng)我們刪除emp表的數(shù)據(jù),刪除的數(shù)據(jù)會(huì)自動(dòng)保存在emp_bak表中
    create or replace trigger tri_backup_emp 
    before
    delete on emp 
    for each row
    begin 
      insert into emp_bak (empno,ename)
      values(:old.empno,:old.ename);
    end;
    
    
    
    delete from emp where sal is null;
   

2.5 禁用或者是啟用觸發(fā)器

1. 禁用或者是啟用觸發(fā)器 alter trigger 觸發(fā)器名字 disable|enable

    -- 禁用觸發(fā)器
    alter trigger tri_delete_emp disable;
    
    -- 啟用觸發(fā)器
    alter trigger tri_delete_emp enable;
   

2. 禁用或者是啟用表中所有的觸發(fā)器 alter table 表名 disable|enable all triggers

    -- 禁用emp中所有的觸發(fā)器
    alter table emp disable all triggers;
    
    
    -- 啟用emp中所有的觸發(fā)器
    alter table emp disable all triggers;

3. 刪除觸發(fā)器drop trigger 觸發(fā)器名字

2.6 總結(jié)

1. 表
2. 視圖: 1)限制某些列的訪問 2)簡(jiǎn)化select語(yǔ)句
3. 同義詞: 1)簡(jiǎn)化對(duì)象訪問 2)提高數(shù)據(jù)安全性
4. 索引: 1)提高表查詢效率;會(huì)降低增刪改的效率
5. 序列:實(shí)現(xiàn)主鍵的自增長(zhǎng),只能夠保證唯一,但是不能夠保證序列值是連續(xù)的.
6. 存儲(chǔ)過程:命名了的PLSQL塊
7. 存儲(chǔ)函數(shù):有返回值的存儲(chǔ)過程
8. 程序包:1)對(duì)數(shù)據(jù)庫(kù)對(duì)象更好的管理 2)更有利于對(duì)PLSQL模塊化的開發(fā)
9. 觸發(fā)器:會(huì)自動(dòng)執(zhí)行的PLSQL塊

3. 數(shù)據(jù)字典

3.1 概念

1. 數(shù)據(jù)字典是由一些系統(tǒng)表和視圖組成,安裝Oracle數(shù)據(jù)庫(kù)的時(shí)候,Oracle數(shù)據(jù)庫(kù)就已經(jīng)自動(dòng)創(chuàng)建了一些系統(tǒng)表.這些系統(tǒng)表就是用來(lái)記錄系統(tǒng)里面的一些信息.如果需要查詢系統(tǒng)的信息,就需要查詢這些系統(tǒng)表.
2. 但是這些系統(tǒng)表是不允許直接訪問的.因此,Oracle就為這些系統(tǒng)表創(chuàng)建了對(duì)應(yīng)的視圖,如果需要訪問系統(tǒng)表的數(shù)據(jù),就可以通過這些系統(tǒng)表的視圖進(jìn)行訪問.
3. 使用select * from dict可以獲取到所有的視圖,這些視圖大多是以USER,ALL,DBA開頭

sjzd.png

- 以`USER`開頭的視圖,那么這些視圖是用來(lái)保存當(dāng)前用戶的信息,只允許當(dāng)前用戶訪問
- 以`ALL`開頭的視圖,那么這些視圖是用來(lái)保存所有用戶的信息,允許所有的用戶訪問
- 以`DBA`開頭的視圖,那么這些視圖是用來(lái)保存系統(tǒng)相關(guān)的信息,只允許管理員訪問.

3.2 常用的數(shù)據(jù)字典

4. 角色

4.1 概念

1. 角色就是一些權(quán)限的集合,如果用戶具有了某一個(gè)角色,那么這個(gè)用戶就具有了該角色的所有的權(quán)限.

4.2 創(chuàng)建角色

1. 第一步創(chuàng)建角色create role 角色名
2. 第二步給角色授予權(quán)限:grant 權(quán)限 to 角色
3. 第三步把角色授予給用戶:grant 角色 to 用戶

grant.png

4.2 Oracle數(shù)據(jù)庫(kù)提供的角色

1. CONNECT:具有create session權(quán)限.
2. RESOURCE:具有了create xxx的權(quán)限
3. 查詢一個(gè)角色的所有的權(quán)限

role.png

-  注意:如果某個(gè)用戶有某個(gè)對(duì)象的createXXX的權(quán)限,那么就有權(quán)對(duì)該對(duì)象進(jìn)行任何的操作

4. 查詢一個(gè)用戶的角色 dba_role_privs

5. 回收角色 revoke 角色名 from 用戶名

revoke.png

5. 閃回

5.1 概念

1. 閃回技術(shù)可以把之前的數(shù)據(jù)恢復(fù)回來(lái).

5.2 閃回技術(shù)分類

1. 閃回查詢
2. 閃回表
3. 閃回刪除

5.3 閃回查詢

1. 閃回查詢就是允許用戶查詢某個(gè)表在過去某一個(gè)時(shí)間點(diǎn)或者SCN狀態(tài).

   • SCN:系統(tǒng)改變號(hào).它是Oracle數(shù)據(jù)庫(kù)里面的一個(gè)計(jì)數(shù)器,當(dāng)用戶去操作數(shù)據(jù)庫(kù)的時(shí)候,Oracle數(shù)據(jù)庫(kù)就會(huì)給該計(jì)數(shù)器自動(dòng)+1,計(jì)數(shù)器就是代表了某一個(gè)時(shí)間點(diǎn).

2. 執(zhí)行閃回查詢的方式一:(推薦)

   • 語(yǔ)法select * from 表名 as of timestamp 時(shí)間點(diǎn)

       --閃回查詢
       select * from emp_bak as of timestamp sysdate - interval '15' minute ;
     

3. 執(zhí)行閃回查詢方式二:

   • 語(yǔ)法select * from 表名 as of scn 系統(tǒng)改變號(hào)

       -- 獲取當(dāng)前時(shí)間的scn
       select timestamp_to_scn(systimestamp) from dual;
       -- 根據(jù)scn獲取到指定的時(shí)間
       select scn_to_timestamp(1568877) from dual;
     

4. 閃回查詢的應(yīng)用:

        insert into emp_flash
            (
              --閃回查詢
              select * from emp_flash as of timestamp sysdate - interval '25' minute where deptno = 10
            );
   

5.4 閃回表

1. 用戶可以把某一張表的數(shù)據(jù)恢復(fù)到某一個(gè)時(shí)間點(diǎn)或者是某個(gè)scn的狀態(tài).

2. 閃回表和閃回查詢的區(qū)別:

   • 閃回查詢可以把某個(gè)表的某些數(shù)據(jù)恢復(fù)到某個(gè)時(shí)間點(diǎn)
   • 閃回表只能把整張表的所有數(shù)據(jù)恢復(fù)到某個(gè)時(shí)間點(diǎn)

3. 執(zhí)行閃回表(方式一)

   • 語(yǔ)法:flashback table 表名 to timestamp 時(shí)間點(diǎn)

   • 注意:如果需要執(zhí)行閃回表操作,必須要啟動(dòng)行移動(dòng)功能,如果沒有啟動(dòng),就會(huì)出錯(cuò).

   • 啟動(dòng)方式:alter table 表名 enable row movement;

   • 示例:

       -- 啟動(dòng)該表的行移動(dòng)功能
       alter table emp_bak enable row movement;
       -- 閃回表
       flashback table emp_bak to timestamp timestamp '2017-4-6 15:00:00' ;
     

   • 注意:但是并不是所有的數(shù)據(jù)都可以通過閃回操作表操作恢復(fù)回來(lái),為什么呢?因?yàn)橛脩魟h除數(shù)據(jù)的時(shí)候,這些被刪除的數(shù)據(jù)是保存在撤銷表空間里面,但是撤銷表空間里面是有一個(gè)保留時(shí)間的.默認(rèn)的保留時(shí)間是900秒(15分鐘左右).如果超過了900秒.

   • 如何修改?

flash.png

5.5 閃回刪除

1. 閃回刪除允許用戶把刪除的表從回收站中恢復(fù)過來(lái).

2. 語(yǔ)法:flashback table to before drop [rename to new_table_name]

3. 如果指定了rename參數(shù),那么執(zhí)行閃回刪除的時(shí)候,Oracle數(shù)據(jù)庫(kù)就會(huì)對(duì)該表進(jìn)行重命名.

        -- 刪除表
        drop table emp_bak;
        -- 執(zhí)行閃回刪除
        flashback table emp_bak to  before drop 
   

4. 注意.閃回刪除只能對(duì)回收站里面的表進(jìn)行閃回,如果刪除表的時(shí)候指定了purge參數(shù).那么就不能夠使用閃回刪除把表恢復(fù)過來(lái).

5. 清空回收站

        -- 清空回收站
        purge recyclebin;
   

6. 數(shù)據(jù)庫(kù)的備份與還原

1. 數(shù)據(jù)庫(kù)的備份

   • 由于是oracle的命令,需要在cmd中輸入
   • exp 用戶名/密碼@數(shù)據(jù)庫(kù)名 file='備份路徑' [tables=(表...)]
   • 注意:備份文件要以.dmp結(jié)尾

2. 數(shù)據(jù)庫(kù)恢復(fù)

   • 在cmd命令輸入命令
   • imp 用戶名/密碼@數(shù)據(jù)庫(kù)名 file='備份文件的路徑' tabels=(表...)

7.分析函數(shù)

7.1 概念

1. 分析函數(shù):和分組函數(shù)的功能非常的相似,它同樣可以對(duì)表的某一列進(jìn)行分組.它還可以對(duì)分組后的數(shù)據(jù)進(jìn)行排序和編號(hào).

7.2 常用的分析函數(shù)

1. rank():如果排序字段值相等,那么該行記錄的編號(hào)就會(huì)初夏你重復(fù),并且編號(hào)是不重復(fù)的.
2. dense_rank():如果排序字段的值相等,那么該行記錄編號(hào)就會(huì)出現(xiàn)重復(fù),但是編號(hào)是連續(xù)的
3. row_number(): 即使排序字段值是相等的,該行記錄編號(hào)也不會(huì)出現(xiàn)重復(fù),編號(hào)也是連續(xù)的

8.總結(jié)

8.1 第一天

1. 數(shù)據(jù)庫(kù)安裝
2. 數(shù)據(jù)庫(kù)服務(wù)
3. 連接數(shù)據(jù)庫(kù)
4. sqlplus命令
5. ddl
6. dml
7. 運(yùn)算符

8.2 第二天

1. 函數(shù)
2. 表空間
3. 用戶管理
4. 視圖
5. 同義詞
6. 索引
7. 序列

8.3 第三天

1. PLSQL塊
2. 存儲(chǔ)函數(shù)
3. 存儲(chǔ)過程

8.4 第四天

1. 程序包
2. 觸發(fā)器
3. 數(shù)據(jù)字典
4. 閃回
5. 角色
6. 備份和還原