--$理論知識(shí)$--

--$$吕晌、三大范式

第一范式（1NF）：字段是原子性的常挚，不可分;

第二范式（2NF）：有主鍵桨菜，非主鍵字段依賴主鍵硼讽。確保一個(gè)表只說明一個(gè)事物

第三范式（3NF）：非主鍵字段不能相互依賴。 每列都與主鍵有直接關(guān)系捷兰，不存在傳遞的依賴

--$$--理論知識(shí)匯總

1.數(shù)據(jù)模型組成三要素：數(shù)據(jù)結(jié)構(gòu)立叛，數(shù)據(jù)操作和完整性約束條件

2.數(shù)據(jù)庫系統(tǒng)的三級(jí)模式結(jié)構(gòu)：模式,外模式和內(nèi)模式

3.數(shù)據(jù)庫是長(zhǎng)期存儲(chǔ)在計(jì)算機(jī)內(nèi)，有組織的贡茅，可共享的大量數(shù)據(jù)的集合

4.數(shù)據(jù)庫的基本特征：數(shù)據(jù)按一定的數(shù)據(jù)模型組織秘蛇、描述和儲(chǔ)存，可為各種用戶共享顶考，冗余度較小赁还，數(shù)據(jù)獨(dú)立性較高，易擴(kuò)展

5.數(shù)據(jù)庫系統(tǒng)的構(gòu)成：數(shù)據(jù)庫驹沿，數(shù)據(jù)庫管理系統(tǒng)（及其開發(fā)工具）艘策，應(yīng)用系統(tǒng)，數(shù)據(jù)庫管理員

6.SQL語句的基本特點(diǎn)：綜合統(tǒng)一渊季，高度非過程化朋蔫，面向集合的操作方式罚渐，以同一種語法結(jié)構(gòu)提供多種使用方式，語言簡(jiǎn)潔驯妄。

7.SQL的功能：數(shù)據(jù)定義荷并、數(shù)據(jù)操縱、數(shù)據(jù)控制

8.自主授權(quán)控制實(shí)現(xiàn)機(jī)制：SQL語言青扔，GRANT和REVOKE語句實(shí)現(xiàn)

9.數(shù)據(jù)庫安全性控制的方法：用戶標(biāo)識(shí)與鑒別源织，存取控制，授權(quán)與回收

10.數(shù)據(jù)庫保護(hù)涉及的問題：安全性赎懦，完整性雀鹃，數(shù)據(jù)庫恢復(fù)和并發(fā)控制(死鎖問題)

11.加密的方法：替換、置換励两、兩種結(jié)合

12.數(shù)據(jù)庫完整性的方法：實(shí)體完整性黎茎、參照完整性和自定義完整性

13.數(shù)據(jù)的完整性和安全性是兩個(gè)不同概念：

1.數(shù)據(jù)的完整性：防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù)，也就是防止數(shù)據(jù)庫中存在不正確的數(shù)據(jù)当悔，防范對(duì)象：不合語義的傅瞻、不正確的數(shù)據(jù)

2.數(shù)據(jù)的安全性：保護(hù)數(shù)據(jù)庫防止惡意的破壞和非法的存取，防范對(duì)象：非法用戶和非法操作

14.數(shù)據(jù)庫設(shè)計(jì)的步驟：需求分析（用戶需求）盲憎，概念結(jié)構(gòu)設(shè)計(jì)（E-R圖）嗅骄，邏輯結(jié)構(gòu)設(shè)計(jì)（視圖），物理結(jié)構(gòu)設(shè)計(jì)（索引）數(shù)據(jù)庫實(shí)施饼疙，數(shù)據(jù)庫運(yùn)行和維護(hù)

15.數(shù)據(jù)字典的內(nèi)容：數(shù)據(jù)項(xiàng)溺森，數(shù)據(jù)結(jié)構(gòu)，數(shù)據(jù)流窑眯，數(shù)據(jù)存儲(chǔ)屏积，處理過程

16.E-R圖合并的沖突：屬性沖突，命名沖突磅甩，結(jié)構(gòu)沖突

17.優(yōu)化數(shù)據(jù)模型的方法：確定數(shù)據(jù)依賴炊林，消除冗余的聯(lián)系，確定所屬范式

18.引入嵌入式編程的原因：SQL是非過程的語言卷要，事務(wù)處理應(yīng)用需要高級(jí)語言

19.高級(jí)語言和SQL語言的作用：前者是負(fù)責(zé)控制數(shù)據(jù)流程渣聚，后者負(fù)責(zé)操縱數(shù)據(jù)庫

20.SQL通信區(qū)的作用：向主語言傳遞SQL語句的執(zhí)行狀態(tài)信息，使主語言能夠據(jù)此控制程序流程

21.主變量的作用：向SQL語句提供參數(shù)僧叉，將SQL語句查詢數(shù)據(jù)庫的結(jié)果交主語言進(jìn)一步處理

22.游標(biāo)：解決集合性操作語言與過程性操作語言的不匹配奕枝。(數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果)

23.為什么使用游標(biāo)：SQL語言與主語言具有不同數(shù)據(jù)處理方式瓶堕。SQL語言是面向集合的倍权，一條SQL語句原則上可以產(chǎn)生或處理多條記錄。主語言是面向記錄的，一組主變量一次只能存放一條記錄薄声。僅使用主變量并不能完全滿足SQL語句向應(yīng)用程序輸出數(shù)據(jù)的要求。嵌入式SQL引入了游標(biāo)的概念题画，用來協(xié)調(diào)這兩種不同的處理方式

24.常見的存儲(chǔ)介質(zhì)及其特征：高速緩存存儲(chǔ)器：容量小默辨，速度快，最昂貴苍息。主存儲(chǔ)器：斷電時(shí)缩幸，貯存內(nèi)容丟失【核迹快閃存儲(chǔ)器：非易失表谊。磁盤存儲(chǔ)器：長(zhǎng)期聯(lián)機(jī)數(shù)據(jù)存儲(chǔ)。光學(xué)存儲(chǔ)器：光學(xué)存儲(chǔ)到光盤上盖喷。磁帶存儲(chǔ)器：順序訪問數(shù)據(jù)

25.磁盤的請(qǐng)求模式：順序訪問模式：連續(xù)的請(qǐng)求會(huì)請(qǐng)求處于相同磁道或相鄰磁道上連續(xù)的塊爆办。只需要一次尋道時(shí)間。隨機(jī)訪問模式：連續(xù)的請(qǐng)求會(huì)請(qǐng)求隨機(jī)位于磁盤上的塊课梳，效率較低距辆。

26.優(yōu)化方法：緩沖：從磁盤讀取的數(shù)據(jù)暫時(shí)存在內(nèi)存緩沖區(qū)里。預(yù)讀：當(dāng)一個(gè)磁盤塊被訪問時(shí)暮刃，相鄰磁道的連續(xù)快也被讀入內(nèi)存緩沖區(qū)中跨算。調(diào)度：按塊經(jīng)過讀寫頭的順序發(fā)出訪問請(qǐng)求。電梯算法椭懊。

27.定長(zhǎng)記錄：多個(gè)文件诸蚕，在任意一個(gè)文件只存儲(chǔ)一個(gè)固定長(zhǎng)度的記錄。變長(zhǎng)記錄：構(gòu)造自己的文件氧猬，使之容納多種長(zhǎng)度的記錄背犯。

28.文件組織記錄的幾種方法：堆文件：任意存放。順序文件組織方式：記錄根據(jù)其 搜索碼 的值順序存儲(chǔ)狂窑。散列文件組織方式：在每條記錄的某些屬性計(jì)算一個(gè)散列函數(shù)媳板，根據(jù)其值計(jì)算。搜索碼是一個(gè)屬性或多個(gè)屬性的集合泉哈，不一定是主碼蛉幸。在物理上，盡可能接近搜索碼的順序存儲(chǔ)記錄丛晦。插入新紀(jì)錄時(shí)奕纫，若位置空閑，則直接插入烫沙，否則插入溢出塊匹层。記錄過多，則需要文件重組，保證搜索碼和物理存儲(chǔ)順序的一致性升筏。

29.緩沖區(qū)機(jī)制：當(dāng)數(shù)據(jù)庫系統(tǒng)中的程序需要磁盤上的塊時(shí)撑柔，它向緩沖區(qū)管理器發(fā)出請(qǐng)求（即調(diào)用）。如果這個(gè)塊在緩沖區(qū)中您访，緩沖區(qū)管理器將這個(gè)塊在主存中的地址傳給請(qǐng)求者铅忿。如果這個(gè)塊不在緩沖區(qū)中，緩沖區(qū)首先為這個(gè)塊分配空間（必要時(shí)灵汪，移出一些塊檀训，這個(gè)塊必須已經(jīng)被寫回磁盤），然后把請(qǐng)求的塊從磁盤讀入緩沖區(qū)中享言，并將這個(gè)塊在主存中的地址傳給請(qǐng)求者峻凫。

30.應(yīng)用的技術(shù)：緩沖區(qū)替換策略:移除最近最少使用的寫回磁盤并從緩沖區(qū)移走。被釘住的塊：使數(shù)據(jù)庫系統(tǒng)從崩潰中回復(fù)览露，限制一個(gè)塊寫回磁盤的時(shí)間荧琼。塊的強(qiáng)制寫出：把塊強(qiáng)制寫回磁盤。防止數(shù)據(jù)丟失

31.查詢處理優(yōu)化：查詢-通過語法分析器與翻譯器轉(zhuǎn)化為關(guān)系代數(shù)表達(dá)式-通過優(yōu)化器優(yōu)化執(zhí)行計(jì)劃-執(zhí)行引擎得到查詢結(jié)果肛循。

32.事物的特性：原子性铭腕、一致性、持續(xù)性多糠、隔離性

33.事務(wù)是一個(gè)數(shù)據(jù)庫操作序列累舷；一個(gè)不可分割的工作單位；恢復(fù)和并發(fā)控制的基本單位

34.數(shù)據(jù)庫常見故障種類及其恢復(fù)策略：故障種類：事物內(nèi)部的故障：撤銷事務(wù)(UNDO)夹孔。系統(tǒng)故障(軟故障):若事務(wù)未提交被盈，強(qiáng)行撤銷所有未完成事務(wù)。事務(wù)提交：但緩沖區(qū)中的信息尚未完全寫回到磁盤上：重做(REDO)所有已提交的事務(wù)搭伤。介質(zhì)故障(硬故障)：裝入數(shù)據(jù)庫發(fā)生介質(zhì)故障前某個(gè)時(shí)刻的數(shù)據(jù)副本只怎；重做自此開始時(shí)的所有成功事務(wù)，將這些事務(wù)已提交的結(jié)果重新計(jì)入數(shù)據(jù)庫怜俐。計(jì)算機(jī)病毒：殺毒身堡！

-------------1.用戶管理/權(quán)限------------------

-------1.1 用戶管理

-- 創(chuàng)建

create user checker --用戶

identified by 1qaz@wsx --密碼

default tablespace users --默認(rèn)表空間

temporary tablespace temp --臨時(shí)表空間

quota 10M on users --賦予默認(rèn)表空間賦予checker用戶10M空間（超過則無法使用，除非再次申請(qǐng)）

-- 修改

alter user checker quota 20M on users;--若在建表時(shí)沒有指定拍鲤，可以通過該方法執(zhí)行

alter user checker identified by 123456;--修改密碼

alter user checker account unlock;--若checker用戶被鎖贴谎，可登入system、scott季稳、sys等具有dba權(quán)限的用戶下解鎖

-- 刪除

drop user checker cascade;--刪除用戶擅这、該用戶下所有表都會(huì)被刪掉

-------1.2 用戶權(quán)限

---- 授權(quán)

-- 系統(tǒng)權(quán)限

grant connect,resource to checker;--訪問資源權(quán)限

grant create session,create table to user2 with admin option; --再次授權(quán)

-- 對(duì)象授權(quán)

grant select,insert,delete on scott.emp to user2; --某用戶下的某張表賦予增刪改權(quán)限

--回收權(quán)限

revoke resource to user2;--收回user2的權(quán)限

revoke delete on scott.emp from user2;--收回user2的刪除權(quán)限

--查詢

DBA_USERS? ? ? --用戶基本信息表

DBA_SYS_PRIVS? --系統(tǒng)權(quán)限

DBA_TAB_PRIVS? --對(duì)象權(quán)限

USER_SYS_PRIVS --用戶系統(tǒng)權(quán)限

ROLE_SYS_PRIVS --用戶角色

ALL_TABLES? ? --可查詢的所有表

USER_TAB_PRIVS --用戶將權(quán)限授予哪些用戶

ALL_TAB_PRIVS? --哪些用戶給自己授權(quán)

-------1.3 用戶角色

----預(yù)定義

--connect/resource/dba/exp_full_database/imp_full_database

--創(chuàng)建

create role designer identified by 123456;

grant create view,create table to designer;

--授予

grant designer to user2;

--管理

select * from role_sys_privs where role='DESIGNER';

alter role designer not identified;

alter role designer identified by 9ol.0p;/; --修改role的密碼

-- set role designer;--置為生效

--刪除

drop role designer;

-------1.4 資源配置PROFILE

--管理密碼

create profile lock_account limit

failed_login_attempts 5

password_lock_time 7;

--password_life_time/password_grace_time

--password_reuse_time/password_reuse_max

--password_verify_function

alter user user profile lock_account;

--管理資源

alter system set resource_limit=true;

alter profile password_lift_time limit

cpu_per_session 20000

session_per_user 10

cpu_per_call 500

passeord_life_time 180

failed_login_attempts 10;

--刪除資源管理

drop profile password_life_time cascade;

--查詢?

select profile from dba_users where username='SCOTT';

select resource_name,resource_type,limit from dba_profiles where profile='DEFAULT';

-------------2.數(shù)據(jù)對(duì)象-----------------

----2.1? 數(shù)據(jù)表

---------2.1.1? 創(chuàng)建普通表

create table students(

? stuno number(10) not null,? --非空約束

? stuname varchar2(8),

? id varchar2(18) constraint ID_UK unique,? --唯一性約束

? sex char(2),

? age int constraint AGE_CK check(age > 0 and age < 120) disable,? --禁用

? departno varchar2(2) not null,

? classno varchar2(4) not null,

? regdate date default sysdate,

? ---blob/clob/bfile

? constraint STU_PK primary key(stuno)? ---主鍵約束

)tablespace users? --表空間

storage(initial 256k)? --存儲(chǔ)參數(shù)next/minextents(AUTOALLOCATE)

pctfree 20? --數(shù)據(jù)塊最小空閑空間比例，達(dá)到后標(biāo)記不可用

pctused 40? --數(shù)據(jù)庫是否可用界限

initrans 10? --允許并發(fā)事務(wù)數(shù)目

nologging;? --DDL操作不產(chǎn)生日志

--------2.1.2創(chuàng)建臨時(shí)表

----------2.1.2.1? 會(huì)話級(jí)別臨時(shí)表

--會(huì)話級(jí)臨時(shí)表是指臨時(shí)表中的數(shù)據(jù)只在會(huì)話生命周期之中存在景鼠，

--當(dāng)用戶退出會(huì)話結(jié)束的時(shí)候仲翎，Oracle自動(dòng)清除臨時(shí)表中數(shù)據(jù)。

create global temporary table

aaa

(

id number

) on commit oreserve rows;

insert into aaa values(100);

select * from aaa;

---------2.2.2.2事務(wù)級(jí)別的臨時(shí)表

create global temporary table

bbb

(id number

) on commit delete rows;

insert into bbb values(200);

select * from bbb;

--這時(shí)當(dāng)你執(zhí)行了commit和rollback操作的話，再次查詢表內(nèi)的數(shù)據(jù)就查不到了溯香。

----維護(hù)

--字段

alter table students add(province varchar2(10));--添加字段

alter table students drop column province;--刪除列

alter table students drop (sex,age); --刪除多列

alter table students modify departno varchar2(4);--修改字段長(zhǎng)度

--重命名

alter table students rename to students_bak;--重命名表名

alter table students rename column A to A_id;--重命名列名

--表空間

alter table students move tablespace tbsp_2;

--存儲(chǔ)參數(shù)

alter table students pctfree 25 pctused 45;

--刪除

drop table students cascade constraints;? --同時(shí)刪除視圖鲫构，約束或觸發(fā)器等

flashback table students to before drop;? --閃回

--狀態(tài)

alter table students read only;? --設(shè)置表只讀

--約束

alter table students modify stuno not null;? --修改約束

alter table students add constraint STUD_PK(stuno);--添加主鍵

alter table students drop constraint STUD_PK;--刪除主鍵

alter table students add constraint IDs_UK unique(id);--添加唯一性約束

alter table students drop constraint IDs_UK;--刪除唯一性約束

alter table students add constraint DEPART_FK foreign key(department_id)

? ? ? reference departments(department_id);? --外鍵約束

alter table students drop constraint depart_FK;--刪除外鍵約束

alter table students enable validate constraint depart_FK;? --novalidate

alter table students disable constraint depart_FK;

----2.2? 索引(Index)

---創(chuàng)建

create index emp_deptno_index

on emp(deptno)

pctfree 25? --reverse

tablespace users;

--合并

alter index emp_deptno_index coalesce deallocate unused;

--重建

alter index emp_deptno_index rebuild;

--刪除

drop index emp_deptno_index;

--查詢

select table_name.index_name,index_type from dba_indexes where owner='HR'; --表索引sys

select column_name,column_length from user_ind_columns where index_name='EMP_DEPTNO_INDEX';--索引列scott

select tablespace_name,segment_type,bytes from user_segments where segment_name='EMP_DEPTNO_INDEX';--索引段scott

select column_expression from user_ind_expressions where index_name = 'EMP_JOB_FUN';? --函數(shù)索引

----2.3? 視圖(View)

--創(chuàng)建

create or replace view emp_view as

? select d.dname,d.loc,e.empno,e.ename

? from emp e, dept d

? where e.deptno = d.deptno and d.deptno = 20

with read only;

---查看

select * from emp_view;

desc emp_view;

---重新編譯

alter view emp_view compile;

---刪除

drop view emp_view;

----2.4? 同義詞(Synonym)

---創(chuàng)建

create public synonym public_dept for scott.dept;

create synonym private_dept for dept;

---刪除

drop public synonym public_dept;

drop synonym private_dept;

----2.5? 序列(Sequence)

---創(chuàng)建

alter sequence empno_seq

start with 100 --從多少開始

maxvalue 100000? --minvalue/nominvalue/nomaxvalue? --最大值（最小值/無最小值/無最大值）

increment by 200? --（增量）步長(zhǎng)

cache 100? --緩存

cycle? --nocycle 循環(huán)

order? --noorder? 順序

---管理

alter sequence empno_seq

maxvalue 500000

increment by 200;

---刪除

drop sequence empno_seq;

----2.6? 物化視圖

---基本理論知識(shí)

1、物化視圖的類型：ON DEMAND玫坛、ON COMMIT

二者的區(qū)別在于刷新方法的不同芬迄，ON DEMAND 顧名思義，僅在該物化視圖“需要”被刷新了昂秃，才進(jìn)行刷新(REFRESH)，即更新物化視圖杜窄，以保證和基表數(shù)據(jù)的一致性 肠骆；

而ON COMMIT是說，一旦基表有了COMMIT塞耕，即事務(wù)提交蚀腿，則立刻刷新，立刻更新物化視圖扫外，使得數(shù)據(jù)和基表一致莉钙。

2、ON DEMAND物化視圖

物化視圖的創(chuàng)建本身是很復(fù)雜和需要優(yōu)化參數(shù)設(shè)置的筛谚，特別是針對(duì)大型生產(chǎn) 數(shù)據(jù)庫 系統(tǒng)而言磁玉。但Oracle允許以這種最簡(jiǎn)單的，類似于普通視圖的方式來做驾讲，所以不可避免的會(huì)涉及到默認(rèn)值問題蚊伞。也就是說Oracle給物化視圖的重要定義參數(shù)的默認(rèn)值處理是我們需要特別注意的。

物化視圖的特點(diǎn)：

? (1) 物化視圖在某種意義上說就是一個(gè)物理表(而且不僅僅是一個(gè)物理表)吮铭，這通過其可以被user_tables查詢出來时迫，而得到佐證；

? (2) 物化視圖也是一種段(segment)谓晌，所以其有自己的物理存儲(chǔ)屬性掠拳；

? (3) 物化視圖會(huì)占用數(shù)據(jù)庫磁盤空間，這點(diǎn)從user_segment的查詢結(jié)果纸肉，可以得到佐證溺欧；

3、物化視圖的數(shù)據(jù)怎么隨著基表而更新毁靶？

Oracle提供了兩種方式胧奔，手工刷新和自動(dòng)刷新，默認(rèn)為手工刷新 预吆。也就是說龙填，通過我們手工的執(zhí)行某個(gè)Oracle提供的系統(tǒng)級(jí)存儲(chǔ)過程或包，來保證物化視圖與基表數(shù)據(jù)一致性。這是最基本的刷新辦法了岩遗。自動(dòng)刷新扇商，其實(shí)也就是Oracle會(huì)建立一個(gè)job，通過這個(gè)job來調(diào)用相同的存儲(chǔ)過程或包宿礁，加以實(shí)現(xiàn)案铺。

4、ON DEMAND物化視圖的特性及其和ON COMMIT物化視圖的區(qū)別

"ON DEMAND"不刷新(手工或自動(dòng))就不更新物化視圖梆靖，而"ON COMMIT"不刷新也會(huì)更新物化視圖控汉，——只要基表發(fā)生了COMMIT。

創(chuàng)建定時(shí)刷新的物化視圖：

---創(chuàng)建語法

create materialized view mv_name refresh force on demand start with sysdate

next sysdate+1 （指定物化視圖每天刷新一次）

create materialized view mv_name refresh force on commit as select * from table_name

--更新

alter materialized view mv_name

refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')

---刪除

drop materialized view mv_name

-----------------3.表/索引分區(qū)--------------------------

----3.1? 表分區(qū)

---創(chuàng)建

--范圍

create table ware_retail_part

(

? id integer primary key,

? retail_date date,

? ware_name varchar2(50)

)

partition by range(retail_date)

(

? partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TBSP_1,

? partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TBSP_1,

? partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TBSP_2,

? partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TBSP_2

);

--散列

create table goods

(

? id number,

? goodname varchar2(50)

)

storage(initial 2048k)

partition by hash(id)

(

partition par1 tablespace tbsp_1,

partition par2 tablespace tbsp_2

);

--列表

create table clients

(

? id integer primary key,

? name varchar2(50),

? province varchar2(20)

)

partition by list(province)

(

? partition shandong values('山東省'),

? partition guangdong values('廣東省'),

? partition yunnan values('云南省')

);

--組合分區(qū)

create table person2

(

? id number primary key,

? name varchar2(20),

? sex varchar2(2)

)

partition by range(id)? --范圍分區(qū)

subpartition by hash(name)? --hash子分區(qū)

subpartitions 2 store in(tbsp_1,tbsp_2)? --存儲(chǔ)在兩個(gè)不同的命名空間中

(

? partition par1 values less than(5000),

? partition par2 values less than(10000),

? partition par3 values less than(maxvalue)

);

--間隔分區(qū)

create table saleRecord

(

id number primary key,

goodsname varchar2(50),

saledate date,

quantity number

)

partition by range(saledate)

interval (numtoyminterval(1,'year'))

(

? partition par_fist values less than (to_date('2012-01-01','yyyy-mm-dd'))

);

--應(yīng)用

insert into ware_retail_part values(1,to_date('2011-01-20','yyyy-mm-dd'),'PC');

insert into ware_retail_part values(2,to_date('2011-04-15','yyyy-mm-dd'),'TV');

select * from ware_retail_part partition(par_02);

---管理

--添加

alter table clients

add partition hebei values('河北省')

storage(initial 10K next 20k) tablespace tbsp_1

nologging;

--合并

alter table person coalesce partition;

alter table person2 modify partition par3 coalesce subpartition;

--刪除

--disable constraint/drop/enable constraint

delete from ware_retail_part where retail_date>=to_date('2011-10-01','yyyy-mm-dd');? --數(shù)據(jù)

alter table ware_retail_part drop partition par_04;? --表分區(qū)

alter index ware_index rebuild;? --重建索引

--并入

alter table sales merge partitions part_sea3,part_sea4 into partition part_sea4;

alter table sales modify partition part_sea4 rebuild unusable local indexes;? --重建局部索引

----3.2? 索引分區(qū)

---創(chuàng)建

--本地

--create tablespace ts_1/ts_2/ts_3;

--create table studentgrade partition by range(grade);

create index grade_index on studentgrade(grade)

local

(

? partition p1 tablespace ts_1,

? partition p2 tablespace ts_2,

? partition p3 tablespace ts_3

);? --dba_ind_partitions

--全局

create index index_SalePrice on Books(SalePrice)

global partition by range(SalePrice)

(

? partition p1 values less than (30),

? partition p2 values less than (50),

? partition p3 values less than (maxvalue)

);

---管理

--刪除

alter index index_saleprice drop partition p2;

alter index index_saleprice drop partition p1;

alter index index_saleprice rebulid partition p3;

--重命名

alter index index_saleprice rename partition p3 to p_new;

---------------4.數(shù)據(jù)庫管理--------------------

------4.1 數(shù)據(jù)文件/表空間

---查看

select tablespace_name,file_name,bytes from dba_data_files order by tablespace_name;

---默認(rèn)

select segment_type,segment_name,owner from dba_segments where tablespace_name='USERS';

--SYSTEM/SYSAUT/UNDOTBS1/USERS/EXAMPLE/TEMP

---創(chuàng)建

--本地化管理方式

create tablespace tbs_test_1 datafile 'D:\OracleFiles\OracleData\datafile1.dbf' --創(chuàng)建表空間文件

size 10m

extent management local uniform size 256K;? --autoallocate

--段空間管理方式

create tablespace tbs_test_3 datafile 'D:\OracleFiles\OracleData\datafile3.dbf'

size 20m

extent management local autoallocate

segment space management manual;? --auto

--非標(biāo)準(zhǔn)塊

alter system set db_16k_cache_size = 16M scope=both;

create tablespace tbs_test_5 datafile 'D:\OracleFiles\OracleData\datafile5.dbf'

size 64m reuse

autoextend on next 4m maxsize unlimited

blocksize 16k

extent management local autoallocate

segment space management auto;

--大文件

create bigfile tablespace tbs_test_big datafile 'D:\OracleFiles\OracleData\datafilebig.dbf'

size 2g;

---維護(hù)

--默認(rèn)

alter database default temporary tablespace temp_1;

alter database default tablespace tbs_example;

--狀態(tài)

alter tablespace tbs_test_3 read only;? --read write

--重命名

alter tablespace tbs_test_3 rename to tbs_test_3_new;

--刪除

drop tablespace tbs_test_1 including contents cascade constraint;

--文件

alter tablespace users add datafile 'e:\app\Administrator\oradata\orcl\users02.dbf' size 10M autoextend on next 5M maxsize unlimited;? --添加

alter tablespace users drop datafile 'e:\app\Administrator\oradata\orcl\users02.dbf';? --刪除

alter database datafile 'D:\OracleFiles\OracleData\datafile2.dbf'

autoextend on next 10m maxsize unlimited;? --自動(dòng)擴(kuò)展

---撤銷表空間

--創(chuàng)建

create undo tablespace undo_tbs_1

datafile 'D:\OracleFiles\OracleData\undotbs1.dbf'

size100M;

--修改

alter tablespace undo_tbs_1

add datafile 'D:\OracleFiles\OracleData\undotbs_add.dbf'

size 2g;

--切換

alter system set undo_tablespace=undo_tbs_1;

--刪除

alter system set undo_tablespace=undotbs1;

drop tablespace undo_tbs_1;

--查詢

show parameter undo_tablespace;? --undo_management/undo_retention

select tablespace_name from dba_tablespaces where contents = 'UNDO';

select to_char(begin_time,'hh24:mi:ss'), to_char(end_time,'hh24:mi:ss'), undoblks

from v$undostat order by begin_time;? --表空間統(tǒng)計(jì)信息

select rn.name,rs.xacts,rs.writes,rs.extents

from v$rollname rn,v$rollstat rs where rn.usn = rs.usn;? --段統(tǒng)計(jì)信息

select name,status from v$transaction;? --活動(dòng)事務(wù)

select segment_name, extent_id,bytes,status from dba_undo_extents

where segment_name='_SYSSMU3_991555123$';? --UNDO區(qū)信息

---臨時(shí)表空間

--創(chuàng)建

create temporary tablespace temp_01 tempfile 'D:\OracleFiles\tempfiles\temp_01.tpf' size 300m;

alter database default temporary tablespace temp_01;

--重命名/刪除同上

--查詢

select file_name,bytes,tablespace_name from dba_temp_files;

---臨時(shí)表空間組

create temporary tablespace tp1 tempfile 'D:\OracleFiles\tempfiles\tp1.tpf'

size 10m tablespace group group1;

create temporary tablespace tp2 tempfile 'D:\OracleFiles\tempfiles\tp2.tpf'

size 20m tablespace group group1;? --創(chuàng)建

create temporary tablespace tp3 tempfile 'D:\OracleFiles\tempfiles\tp3.tpf'

size 10m tablespace group group3;

alter tablespace tp1 tablespace group group3;? --轉(zhuǎn)移

alter user hr temporary tablespace group3;? --分配

alter database orcl default temporary tablespace group3;? --默認(rèn)

drop tablespace tp1 including contents and datafiles;

------4.2 控制文件

---多路復(fù)用

alter system set control_file=

? 'D:\PROGRAM\ORACLE\ORADATA\ORCL\CONTROL01.CTL',

? 'D:\PROGRAM\ORACLE\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL',

? 'D:\OracleFiles\ControlFiles\CONTROL03.CTL'

scope=spfile;? --參數(shù)設(shè)置后復(fù)制文件

select name from v$controlfile;? --查看

---創(chuàng)建

select member from v$logfile;? --查看日志文件

select name from v$datafile;? --查看數(shù)據(jù)文件

select name from v$controlfile;? --查看控制文件

shutdown immediate;? --關(guān)閉數(shù)據(jù)庫返吻，然后備份文件

startup nomount;? --啟動(dòng)數(shù)據(jù)庫實(shí)例姑子，不加載數(shù)據(jù)庫

create controlfile reused --創(chuàng)建新控制文件

database "orcl"

logfile

group 1 'D:\PROGRAM\ORACLE\ORADATA\ORCL\REDO01.LOG',

group 2 'D:\PROGRAM\ORACLE\ORADATA\ORCL\REDO02.LOG',

group 3 'D:\PROGRAM\ORACLE\ORADATA\ORCL\REDO03.LOG'

datafile

'D:\PROGRAM\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',

'D:\PROGRAM\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',

'D:\PROGRAM\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',

'D:\PROGRAM\ORACLE\ORADATA\ORCL\USERS01.DBF',

'D:\PROGRAM\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',

'D:\PROGRAM\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\KPLAYER\KPLAYER.DBF'

maxlogfiles 50

maxlogmembers 3

maxinstances 6

maxdatafiles 200

noresetlogs

noarchivelog;

alter system set control_files=? --編輯參數(shù)

? 'E:\PROG\ADMIN\ORADATA\ORCL\CONTROL01.CTL',

? 'E:\PROG\ADMIN\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL'

scope=spfile;

alter database open;? --打開數(shù)據(jù)庫 [resetlogs]

---備份

alter database backup controlfile

to 'D:\OracleFiles\ControlFiles\ctf.bak';? --二進(jìn)制文件

alter database backup controlfile to trace;? --腳本文件

show parameter user_dump_dest;

---恢復(fù)

--關(guān)閉->復(fù)制覆蓋->重啟

--關(guān)閉->編輯CONTROL_FILES->重啟

---刪除

--關(guān)閉->編輯CONTROL_FILES->重啟

---查詢

--v$controlfile? 所有控制文件名稱和狀態(tài)

--v$controlfile_record_section? 控制文件各記錄文檔段信息

--v$parameter? 系統(tǒng)所有初始化參數(shù)

------4.3 重做日志文件

---增加

alter database add logfile group 5

('D:\OracleFiles\LogFiles\REDO4_A.LOG',

'E:\OracleFiles\LogFiles\REDO4_B.LOG')

size 20M;? --添加新的重做日志文件組

alter database add logfile member

'E:\OracleFiles\LogFiles\REDO4_C.LOG' to group 4;? --創(chuàng)建日志成員文件

alter database add logfile member

'D:\OracleFiles\LogFiles\REDO1_new.LOG' to

('E:\app\Administrator\oradata\orcl\REDO01.LOG') ;? --指定成員名稱

---刪除

--日志成員

alter database drop logfile member 'E:\OracleFiles\LogFiles\REDO4_C.LOG';

--日志文件組

alter database drop logfile group 5;

--清空

alter database clear logfile group 4;

---更改

--關(guān)閉->復(fù)制源文件到目標(biāo)位置->啟動(dòng)加載但不打開mount

alter database rename file

? 'D:\OracleFiles\LogFiles\REDO1_new.LOG',

? 'D:\OracleFiles\LogFiles\REDO4_A.LOG'

to

? 'E:\OracleFiles\LogFiles\REDO1_new.LOG',

? 'E:\OracleFiles\LogFiles\REDO4_A.LOG';

--打開數(shù)據(jù)庫

--查看

--v$log v$logfile v$log_history

------4.4 歸檔日志文件

---切換

select log_mode from v$database;? --noarchivelog/archivelog

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

---進(jìn)程

alter system set log_archive_max_processes = 3;

---位置

--本地

alter system set log_archive_dest_1='location=D:\OracleFiles\archive1 optional';

alter system set log_archive_dest_2='location=D:\OracleFiles\archive2 mandatory';

alter system set log_archive_dest_3='location=D:\OracleFiles\archive3 mandatory reopen=400';

alter system set log_archive_min_succeed_dest=3;? --最小歸檔數(shù)

alter system set log_archive_dest_state_4=defer;? --禁用位置

--遠(yuǎn)程

alter system set log_archive_dest_1='service=MRKJ';

---查看

--v$database v$archived_log v$archive_dest v$archive_processes v$backup_redolog

archive log list;

----------5. 數(shù)據(jù)庫維護(hù)-------------

------5.1 數(shù)據(jù)庫控制

---事務(wù)

set transaction read only;? --read write

exec dbms_transaction.read_only;

set transaction use rollback segment system;? --分配回滾段

savepoint sp01;? --設(shè)置保存點(diǎn)

insert into jobs_temp values('DESIGN','DS',3000,5000);

rollback to sp01;? --回滾

---鎖

lock table dept_temp in row share mode;

--row exclusive mode/share mode/share row exclusive mode/exclusive mode

------5.2 數(shù)據(jù)導(dǎo)入/導(dǎo)出

---導(dǎo)出

create directory dump_dir as 'd:\dump';

grant read,write on directory dump_dir to scott;

--表

expdp scott/1qaz2wsx directory = dump_dir dumpfile=tab.dmp tables=emp,dept

--模式

expdp system/1qaz2wsx directory = dump_dir dumpfile=schema.dmp schemas=scott,hr

--表空間

expdp system/1qaz2wsx directory = dump_dir dumpfile = tablespace.dmp tablespaces=tbsp_1

--數(shù)據(jù)庫

expdp system/1qaz2wsx directory=dump_dir dumpfile=fulldatabase.dmp full=y

--content/query/logfile/status

---導(dǎo)入

impdp system/1qaz2wsx directory=dump_dir dumpfile=tab.dmp tables=scott.dept,scott.emp remap_schema=scott:system? --表

impdp system/1qaz2wsx directory=dump_dir dumpfile=schema.dmp schemas=scott remap_schema=scott:system;? --模式

impdp system/1qaz2wsx directory=dump_dir dumpfile=tablespace.dmp tablespaces=tbsp_1? --表空間

impdp system/1qaz2wsx directory=dump_dir dumpfile=fulldatabase.dmp full=y? --數(shù)據(jù)庫

--remap_schema/remap_tablesapce/sqlfile/table_exists_action/transport_datafiles

------SQL Loader

sqlldr --用法

---自由格式

/*? --student.ctl

load data

? infile 'd:\data\student.txt'

? into table student

? (stuno position(01:04) integer external,

? stuname position(11:14) char,

? sex position(21:22) char,

? old position(29:30) integer external

? )

*/

sqlldr system/1qaz2wsx control=d:\data\student.ctl log=d:\data\stu_log

---固定格式

/*? --persons.ctl

load data

infile 'd:\data\persons.csv'

append into table persons

fields terminated by ','

(code,name,sex,old)

*/

sqlldr system/1qaz2wsx control=d:\data\persons.ctl

------6.0? 數(shù)據(jù)庫對(duì)象

---6.1 游標(biāo)

一、游標(biāo)概述

游標(biāo)（cursor）是數(shù)據(jù)庫系統(tǒng)在內(nèi)存中開設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)测僵，存放SQL語句的執(zhí)行結(jié)果街佑。

每個(gè)游標(biāo)都有一個(gè)名字，用戶可以用SQL語句逐一從游標(biāo)中獲取記錄捍靠，并賦給變量做進(jìn)一步處理沐旨。

作用：用于定位結(jié)果集的行 和 遍歷結(jié)果集。

游標(biāo)產(chǎn)生時(shí)間：當(dāng)執(zhí)行DML SQL語句時(shí)榨婆；

游標(biāo)用于存放：結(jié)果集

游標(biāo)有名字嗎：有磁携，SQL或用戶給他取名

游標(biāo)如何操作：用FETCH語句逐一從游標(biāo)中提取記錄，并賦給變量進(jìn)一步處理

同時(shí)可打開幾個(gè)游標(biāo)：多個(gè)纲辽，具體數(shù)量由數(shù)據(jù)庫初始化參數(shù)OPEN_CURSOR決定颜武。

二、游標(biāo)分類

游標(biāo)分為：靜態(tài)游標(biāo)（隱式和顯式）和REF游標(biāo)（+游標(biāo)變量）

1. 游標(biāo)的屬性

--顯式游標(biāo)

游標(biāo)的屬性? 返回值類型? ? ? ? ? ? ? 意? ? 義

%ROWCOUNT? ? ? 整型? ? ? ? ? 獲得FETCH語句返回的數(shù)據(jù)行數(shù)

%FOUND? ? ? ? 布爾型? ? ? ? 最近的FETCH語句返回一行數(shù)據(jù)則為真拖吼，否則為假

%NOTFOUND? ? 布爾型? ? ? ? 與%FOUND屬性返回值相反

%ISOPEN? ? ? 布爾型? ? ? ? 游標(biāo)已經(jīng)打開時(shí)值為真鳞上，否則為假

2. 靜態(tài)游標(biāo)

靜態(tài)游標(biāo)是指結(jié)果集已經(jīng)確實(shí)（靜態(tài)定義）的游標(biāo)。

靜態(tài)游標(biāo)分：隱式游標(biāo) 和 顯示游標(biāo)吊档。

2.1 隱式游標(biāo)

在PL/SQL中隱式游標(biāo)在執(zhí)行DML SQL(Insert/Delete/Update/Select)語句時(shí)自動(dòng)創(chuàng)建篙议、自動(dòng)聲明、打開和關(guān)閉怠硼，其名SQL（注：所有的隱式游標(biāo)名都叫“SQL”）鬼贱；

隱式游標(biāo)的屬性 返回值類型? ? 意? ? 義

SQL%ROWCOUNT? ? 整型? ? ? ? 代表DML語句成功執(zhí)行的數(shù)據(jù)行數(shù)

SQL%FOUND? ? ? 布爾型? ? ? 值為TRUE代表插入、刪除香璃、更新或單行查詢操作成功

SQL%NOTFOUND? ? 布爾型? ? ? 與SQL%FOUND屬性返回值相反

SQL%ISOPEN? ? ? 布爾型? ? ? DML執(zhí)行過程中為真这难，結(jié)束后為假

示例：

使用隱式游標(biāo)的屬性。

BEGIN

UPDATE emp SET sal=5000 WHERE empno=7369;

IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE('表已更新');

COMMIT;

ELSE

DBMS_OUTPUT.PUT_LINE('更新失敗');

END IF;

END;

------------------------------------------

declare

? cursor c_name(cust_name varchar) is

? ? select * from YXD.ZJZB_N_20180131_03 where 客戶名 = cust_name;

? Cust_Nm YXD.ZJZB_N_20180131_03%ROWTYPE;

BEGIN

FOR i in c_name('王東生') LOOP

? ? DBMS_OUTPUT.put_line(i.客戶名||','||i.行業(yè)板塊);

? END LOOP;

END;

------------------------------------------

--1.不帶參的游標(biāo)

--查詢每個(gè)人的薪水

declare

? --定義光標(biāo)（游標(biāo)）

? cursor cemp is select ename,sal from emp;

? pename emp.ename%type;

? psal? emp.sal%type;

begin

? --打開游標(biāo)

? open cemp;

? loop

? ? ? --取當(dāng)前記錄

? ? ? fetch cemp into pename,psal;

? ? ? --exit when 沒有取到記錄;

? ? ? exit when cemp%notfound;

? ? ? dbms_output.put_line(pename||'的薪水是'||psal);

? end loop;

? --關(guān)閉游標(biāo)

? close cemp;

end;

/

--2. 帶參的游標(biāo)

--查詢某個(gè)部門的員工姓名

declare

? --形參（定義）

? cursor cemp(dno number) is select ename from emp where deptno=dno;

? pename emp.ename%type;

begin

? --實(shí)參

? open cemp(20);--傳入?yún)?shù)葡秒，打開游標(biāo)部門號(hào)為20

? dbms_output.put_line('該部門的員工有：');

? loop

? ? ? ? fetch cemp into pename;

? ? ? ? exit when cemp%notfound;? ? ?

? ? ? ? dbms_output.put_line(pename);

? end loop;

? close cemp;

end;

/

----------------------------------------------------------------------

--6.2 事務(wù)（TRANSACTION）

---6.2.1 什么是事務(wù)?

在數(shù)據(jù)庫中事務(wù)是工作的邏輯單元姻乓，一個(gè)事務(wù)是由一個(gè)或多個(gè)完成一組的相關(guān)行為的SQL語句組成嵌溢，通過事務(wù)機(jī)制確保這一組SQL語句所作的操作要么都成功執(zhí)行，完成整個(gè)工作單元操作蹋岩，要么一個(gè)也不執(zhí)行赖草。

如：網(wǎng)上轉(zhuǎn)帳就是典型的要用事務(wù)來處理，用以保證數(shù)據(jù)的一致性剪个。

--6.2.2 事務(wù)特性

原子性(Atomicity)：一個(gè)事務(wù)里面所有包含的SQL語句是一個(gè)執(zhí)行整體秧骑，不可分割，要么都做扣囊，要么都不做乎折。

一致性(Consistency)：事務(wù)開始時(shí)，數(shù)據(jù)庫中的數(shù)據(jù)是一致的侵歇，事務(wù)結(jié)束時(shí)笆檀，數(shù)據(jù)庫的數(shù)據(jù)也應(yīng)該是一致的。

隔離性(Isolation)：是指數(shù)據(jù)庫允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其中的數(shù)據(jù)進(jìn)行讀寫和修改的能力盒至，隔離性可以防止事務(wù)的并發(fā)執(zhí)行時(shí)，由于他們的操作命令交叉執(zhí)行而導(dǎo)致的數(shù)據(jù)不一致狀態(tài)士修。

持久性 (Durability) : 是指當(dāng)事務(wù)結(jié)束后枷遂，它對(duì)數(shù)據(jù)庫中的影響是永久的，即便系統(tǒng)遇到故障的情況下棋嘲，數(shù)據(jù)也不會(huì)丟失酒唉。

--6.2.3 數(shù)據(jù)異常

因?yàn)镺racle中支持多個(gè)事務(wù)并發(fā)執(zhí)行，所以會(huì)出現(xiàn)下面的數(shù)據(jù)異常沸移。

? ? --6.2.3.1 臟讀

當(dāng)一個(gè)事務(wù)修改數(shù)據(jù)時(shí)痪伦，另一事務(wù)讀取了該數(shù)據(jù)，但是第一個(gè)事務(wù)由于某種原因取消對(duì)數(shù)據(jù)修改雹锣，使數(shù)據(jù)返回了原狀態(tài)网沾，這是第二個(gè)事務(wù)讀取的數(shù)據(jù)與數(shù)據(jù)庫中數(shù)據(jù)不一致，這就叫臟讀蕊爵。

? ? 如：事務(wù)T1修改了一條數(shù)據(jù)辉哥，但是還未提交，事務(wù)T2恰好讀取到了這條修改后了的數(shù)據(jù)攒射，此時(shí)T1將事務(wù)回滾醋旦，這個(gè)時(shí)候T2讀取到的數(shù)據(jù)就是臟數(shù)據(jù)。

--6.2.3.2 不可重復(fù)讀

是指一個(gè)事務(wù)讀取數(shù)據(jù)庫中的數(shù)據(jù)后会放，另一個(gè)事務(wù)則更新了數(shù)據(jù)饲齐，當(dāng)?shù)谝粋€(gè)事務(wù)再次讀取其中的數(shù)據(jù)時(shí)，就會(huì)發(fā)現(xiàn)數(shù)據(jù)已經(jīng)發(fā)生了改變咧最，這就是不可重復(fù)讀取捂人。不可重復(fù)讀取所導(dǎo)致的結(jié)果就是一個(gè)事務(wù)前后兩次讀取的數(shù)據(jù)不相同御雕。

? ? 如：事務(wù)T1讀取一行記錄，緊接著事務(wù)T2修改了T1剛剛讀取的記錄先慷，然后T1再次查詢饮笛，發(fā)現(xiàn)與第一次讀取的記錄不同。

--6.2.3.3 幻讀

如果一個(gè)事務(wù)基于某個(gè)條件讀取數(shù)據(jù)后论熙，另一個(gè)事務(wù)則更新了同一個(gè)表中的數(shù)據(jù)福青，這時(shí)第一個(gè)事務(wù)再次讀取數(shù)據(jù)時(shí)，根據(jù)搜索的條件返回了不同的行脓诡，這就是幻讀无午。

? ? 如：事務(wù)T1讀取一條指定where條件的語句，返回結(jié)果集祝谚。此時(shí)事務(wù)T2插入一行新記錄宪迟，恰好滿足T1的where條件。然后T1使用相同的條件再次查詢交惯，結(jié)果集中可以看到T2插入的記錄次泽，這條新紀(jì)錄就是幻讀。

事務(wù)中遇到的這些異常與事務(wù)的隔離性設(shè)置有關(guān)席爽，事務(wù)的隔離性設(shè)置越多意荤，異常就出現(xiàn)的越少，但并發(fā)效果就越低只锻，事務(wù)的隔離性設(shè)置越少玖像，異常出現(xiàn)的越多，并發(fā)效果越高齐饮。

--6.2.4 事務(wù)隔離級(jí)別

針對(duì)讀取數(shù)據(jù)時(shí)可能產(chǎn)生的不一致現(xiàn)象捐寥，在SQL92標(biāo)準(zhǔn)中定義了4個(gè)事務(wù)的隔離級(jí)別：

? ? ? 隔離級(jí)別 ? ? ? ? ? ? ? ? ? 臟讀? 不可重復(fù)讀 幻讀

Read uncommitted(讀未提交) ? ? ? 是 ? 是 ? ? 是

Read committed（讀已提交） ? ? ? 否 ? 是 ? ? 是

Repeatable read（可重復(fù)讀） ? ? ? 否 ? 否 ? ? 是

Serializable（串行讀） ? ? ? ? ? 否 ? 否 ? ? 否

Oracle默認(rèn)的隔離級(jí)別是read committed。

Oracle支持上述四種隔離級(jí)別中的兩種:read committed 和serializable祖驱。除此之外握恳，Oralce中還定義Read only和Read write隔離級(jí)別。

Read only：事務(wù)中不能有任何修改數(shù)據(jù)庫中數(shù)據(jù)的操作語句捺僻，是Serializable的一個(gè)子集睡互。

Read write：它是默認(rèn)設(shè)置，該選項(xiàng)表示在事務(wù)中可以有訪問語句陵像、修改語句就珠，但不經(jīng)常使用。

設(shè)置隔離級(jí)別

設(shè)置一個(gè)事務(wù)的隔離級(jí)別：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET TRANSACTION READ ONLY;

SET TRANSACTION READ WRITE;

set transaction read write;

注意：這些語句是互斥的醒颖，不能同時(shí)設(shè)置兩個(gè)或兩個(gè)以上的選項(xiàng)妻怎。

設(shè)置單個(gè)會(huì)話的隔離級(jí)別：

ALTER SESSION SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

ALTER SESSION SET TRANSACTION ISOLATION SERIALIZABLE;

-------------------------------------------------------------------------------------

--------------------------------銀行轉(zhuǎn)賬業(yè)務(wù)流程事務(wù)---------------------------------

-------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE BANK_TRANSFER_TRANSACTION(

? I_MONEY IN NUMBER,--輸入?yún)?shù)，轉(zhuǎn)賬金額

? I_OUT_ACCOUNT IN INTEGER,--輸入?yún)?shù)泞歉，轉(zhuǎn)出賬戶

? I_IN_ACCOUNT IN INTEGER, --輸入?yún)?shù)逼侦，轉(zhuǎn)入賬號(hào)

? V_BALANCE OUT account.balance%TYPE--輸出參數(shù)匿辩，返回當(dāng)前賬戶余額

? ) IS

--從一個(gè)賬戶向另一個(gè)賬戶轉(zhuǎn)賬

BEGIN

? --從轉(zhuǎn)出賬戶扣錢

? UPDATE account set balance = balance - I_MONEY WHERE ID=I_OUT_ACCOUNT;

? select balance into V_BALANCE from account where ID=I_OUT_ACCOUNT;

? IF SQL%NOTFOUND THEN

? ? RAISE_APPLICATION_ERROR(-20001,'對(duì)不起，沒有該賬戶榛丢，可能未開戶:'||40000000000);

? END IF;

? IF V_BALANCE<0 THEN

? ? RAISE_APPLICATION_ERROR(-20002,'賬戶余額不足铲球！');

? END IF;

? --向轉(zhuǎn)入賬戶加錢

? UPDATE account set balance=balance + I_MONEY WHERE ID=I_IN_ACCOUNT;

? IF SQL%NOTFOUND THEN

? ? RAISE_APPLICATION_ERROR(-20001,'對(duì)不起，沒有該賬戶:'||I_IN_ACCOUNT);

? END IF;

? -- 如果沒有異常晰赞，則提交事務(wù)

? COMMIT;

? DBMS_OUTPUT.PUT_LINE('轉(zhuǎn)賬成功!'||'當(dāng)前可用余額為：'||V_BALANCE);

? EXCEPTION

? ? WHEN OTHERS THEN

? ? ? ROLLBACK; -- 出現(xiàn)異常則回滾事務(wù)

? ? ? DBMS_OUTPUT.PUT_LINE('轉(zhuǎn)賬失敿诓　：');

? ? ? DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

--6.3? 存儲(chǔ)過程中的自治事務(wù)（PRAGMA AUTONOMOUS_TRANSACTION）

定義：PRAGMA AUTONOMOUS_TRANSACTION中文翻譯過來叫“自治事務(wù)”（翻譯的還算好理解），

? ? ? 對(duì)于定義成自治事務(wù)的Procedure掖鱼，實(shí)際上相當(dāng)于一段獨(dú)立運(yùn)行的程序段然走，這段程序不依賴于主程序，也不干涉主程序戏挡。

特點(diǎn)：

? ? 第一,這段程序不依賴于原有Main程序芍瑞，比如Main程序中有未提交的數(shù)據(jù)，那么在自治事務(wù)中是查找不到的褐墅。

? ? 第二,在自治事務(wù)中拆檬，commit或者rollback只會(huì)提交或回滾當(dāng)前自治事務(wù)中的DML，不會(huì)影響到Main程序中的DML妥凳。

------------------------------------------------------

------------------專題-ORACLE連續(xù)值問題 ----------------------

? ? ? ? ? ? ? ? --常見于并列排名等情況--

-----------------------------------------------------

--現(xiàn)有需求秩仆，找出這組數(shù)據(jù)中的連續(xù)中斷后的最小值和最大值

name num

A? ? 1

A? ? 2

A? ? 4

B? ? 5

B? ? 7

B? ? 8

C? ? 9

C? ? 11

----------------------------------------------------

創(chuàng)建測(cè)試表：

create table test

(name varchar2(1),

num int);

insert into test values ('A',1);

insert into test values ('A',2);

insert into test values ('A',4);

insert into test values ('B',5);

insert into test values ('B',7);

insert into test values ('B',8);

insert into test values ('C',9);

insert into test values ('C',11);

commit;

執(zhí)行結(jié)果：

with t as

(select name,

? ? ? ? num,

? ? ? ? row_number() over(partition by name order by num desc) rn

? ? from test)

select s.name, s.num

? from (select t2.*

? ? ? ? ? from t t1, t t2

? ? ? ? where t1.rn = t2.rn - 1

? ? ? ? ? and t1.num = t2.num + 1

? ? ? ? ? and t1.rn = 1

? ? ? ? union all

? ? ? ? select * from t where rn = 1) s

order by name, num

------------------------------------------------------------------

---------------------正則表達(dá)式-----------------------------------

------------------------------------------------------------------

SQL語句還可以搭配正則表達(dá)式作為查詢條件，很是有用猾封。

REGEXP_LIKE(匹配)

REGEXP_INSTR (包含)

REGEXP_REPLACE(替換)

REGEXP_SUBSTR(提取)

--＊*************＊--------------------

-- 表 1：定位元字符(常在notepad++中用到)

元字符 ? ? ? ? ? ? ? 說明

^ ? ? ? ? ? ? 使表達(dá)式定位至一行的開頭

$ ? ? ? ? ? ? 使表達(dá)式定位至一行的末尾

-- 表 2：量詞或重復(fù)操作符

量詞 ? ? ? ? ? ? ? 說明

* ? ? ? ? ? ? 匹配 0 次或更多次

? ? ? ? ? ? ? 匹配 0 次或 1 次

+ ? ? ? ? ? ? 匹配 1 次或更多次

{m} ? ? ? ? ? ? 正好匹配 m 次

{m,} ? ? ? ? 至少匹配 m 次

{m, n} ? ? ? ? 至少匹配 m 次但不超過 n 次

-- 表 3：預(yù)定義的 POSIX 字符類

字符類 ? ? ? ? ? ? ? 說明

[:alpha:] ? ? 字母字符

[:lower:] ? ? 小寫字母字符

[:upper:] ? ? 大寫字母字符

[:digit:] ? ? 數(shù)字

[:alnum:] ? ? 字母數(shù)字字符

[:space:] ? ? 空白字符（禁止打印）噪珊，如回車符晌缘、換行符、豎直制表符和換頁符

[:punct:] ? ? 標(biāo)點(diǎn)字符

[:cntrl:] ? ? 控制字符（禁止打恿≌尽）

[:print:] ? ? 可打印字符

-- 表 4：表達(dá)式的替換匹配和分組

元字符 ? ? ? ? ? ? ? 說明

|替換 ? ? ? ? 分隔替換選項(xiàng)磷箕，通常與分組操作符 () 一起使用

( )分組 ? ? ? ? 將子表達(dá)式分組為一個(gè)替換單元、量詞單元或后向引用單元（參見“后向引用”部分）

[char]字符列表 表示一個(gè)字符列表阵难；一個(gè)字符列表中的大多數(shù)元字符（除字符類岳枷、^ 和 - 元字符之外）被理解為文字

-- 表 5：REGEXP_LIKE 操作符

語法:REGEXP_LIKE(source_string, pattern[, match_parameter])

說明：source_string 支持字符數(shù)據(jù)類型（CHAR、VARCHAR2呜叫、CLOB空繁、NCHAR、NVARCHAR2 和 NCLOB朱庆，但不包括 LONG）盛泡。

? ? ? pattern 參數(shù)是正則表達(dá)式的另一個(gè)名稱。

? match_parameter 允許可選的參數(shù)（如處理換行符娱颊、保留多行格式化以及提供對(duì)區(qū)分大小寫的控制）傲诵。

-- 表 6：REGEXP_SUBSTR 操作符

語法：regexp_substr(source_string,pattern[,position[,occurrence[,match_parameter]]])

說明：? source_string：源串缚够，可以是常量痢士，也可以是某個(gè)值類型為串的列

? position：從源串開始搜索的位置。默認(rèn)為1。

? occurrence：指定源串中的第幾次出現(xiàn)悉抵。默認(rèn)值1.

? match_parameter：文本量，進(jìn)一步訂制搜索姓建，取值如下：

? ? 'i'? ? 用于不區(qū)分大小寫的匹配呀邢。

? ? 'c'? ? 用于區(qū)分大小寫的匹配。

? ? 'n'? ? 允許將句點(diǎn)“.”作為通配符來匹配換行符菱属。如果省略改參數(shù)钳榨，句點(diǎn)將不匹配換行符。

? ? 'm'? 將源串視為多行纽门。即將“^”和“$”分別看做源串中任意位置任意行的開始和結(jié)束薛耻，而不是看作整個(gè)源串的開始或結(jié)束。如果省略該參數(shù)赏陵，源串將被看作一行來處理饼齿。

? ? 如果取值不屬于上述中的某個(gè)，將會(huì)報(bào)錯(cuò)蝙搔。如果指定了多個(gè)互相矛盾的值缕溉，將使用最后一個(gè)值。如'ic'會(huì)被當(dāng)做'c'處理吃型。

? ? 省略該參數(shù)時(shí)：默認(rèn)區(qū)分大小寫证鸥、句點(diǎn)不匹配換行符、源串被看作一行勤晚。

案例：

? ? select regexp_substr('MY INFO: Anxpp,22枉层，and boy','my',1,1,'i') from users;

? ? 將返回MY，如果將match_parameter改為'c'將不反悔任何內(nèi)容（null）||

-- 表 7：REGEXP_INSTR 操作符

語法：REGEXP_INSTR(source_string, pattern[, start_position[, occurrence[, return_option[, match_parameter]]]])

說明：該函數(shù)查找 pattern 赐写，并返回該模式的第一個(gè)位置鸟蜡。您可以隨意指定您想要開始搜索的 start_position。 occurrence 參數(shù)默認(rèn)為 1挺邀，除非您指定您要查找接下來出現(xiàn)的一個(gè)模式揉忘。

? ? ? return_option 的默認(rèn)值為 0，它返回該模式的起始位置端铛；值為 1 則返回符合匹配條件的下一個(gè)字符的起始位置泣矛。

-- 表 8：REGEXP_REPLACE 操作符

語法：REGEXP_REPLACE(source_string, pattern[, replace_string [, position[,occurrence, [match_parameter]]]])

說明：該函數(shù)用一個(gè)指定的 replace_string 來替換匹配的模式，從而允許復(fù)雜的“搜索并替換”操作禾蚕。

--表 9：后向引用元字符

元字符 ? ? ? ? ? 說明

\digit 反斜線 緊跟著一個(gè) 1 到 9 之間的數(shù)字乳蓄，反斜線匹配之前的用括號(hào)括起來的第 digit 個(gè)子表達(dá)式。

（注意：反斜線在正則表達(dá)式中有另一種意義夕膀，取決于上下文虚倒，它還可能表示 Escape 字符美侦。

2.32萬/s *60*60=8352*24=2.00448億

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

用戶名正則(--4到16位（字母，數(shù)字魂奥，下劃線菠剩，減號(hào)--)

var uPattern = /^[a-zA-Z0-9_-]{4,16}$/;

console.log(uPattern.test("iFat3"));

密碼強(qiáng)度正則(--//密碼強(qiáng)度正則，最少6位耻煤，包括至少1個(gè)大寫字母具壮，1個(gè)小寫字母，1個(gè)數(shù)字哈蝇，1個(gè)特殊字符--)

var pPattern = /^.*(?=.{6,})(?=.*\d)(?=.*[A-Z])(?=.*[a-z])(?=.*[!@#$%^&*?]).*$/;

console.log("該密碼符合要求"+pPattern.test("iFat3#"));

整數(shù)正則

? ? //正整數(shù)正則

? ? ? var posPattern = /^\d+$/;

? ? //負(fù)整數(shù)正則

? var negPattern = /^-\d+$/;

? ? //整數(shù)正則

? ? ? var intPattern = /^-?\d+$/;

數(shù)字正則(整數(shù)或浮點(diǎn))

? ? //正數(shù)正則

? ? ? var posPattern = /^\d*\.?\d+$/;

? ? //負(fù)數(shù)正則

? ? ? var negPattern = /^-\d*\.?\d+$/;

? ? //數(shù)字正則

? var numPattern = /^-?\d*\.?\d+$/;

Email正則

var ePattern = /^([A-Za-z0-9_\-\.])+\@([A-Za-z0-9_\-\.])+\.([A-Za-z]{2,4})$/;

console.log(ePattern.test("65974040@qq.com"));

手機(jī)號(hào)正則

var mPattern = /^1[3456789]\d{9}$/;

console.log(mPattern.test("18516370162"));

身份證正則

var card = /^[1-9]\d{5}[1-9]\d{3}((0\d)|(1[0-2]))(([0|1|2]\d)|3[0-1])\d{4}$/;

console.log(card.test("4123445565656657565"));

--------------------------------------------------------------------

------------------------專題-ORACLE行轉(zhuǎn)列的幾種方式 ----------------

--------------------------------------------------------------------

--行轉(zhuǎn)列的幾種方式

--1. pivot

with temp as(

select '四川省' nation ,'成都市' city,'第一' ranking from dual union all

select '四川省' nation ,'綿陽市' city,'第二' ranking from dual union all

select '四川省' nation ,'德陽市' city,'第三' ranking from dual union all

select '四川省' nation ,'宜賓市' city,'第四' ranking from dual union all

select '湖北省' nation ,'武漢市' city,'第一' ranking from dual union all

select '湖北省' nation ,'宜昌市' city,'第二' ranking from dual union all

select '湖北省' nation ,'襄陽市' city,'第三' ranking from dual

)

select * from （select nation,city,ranking from temp）pivot (max(city) for ranking in ('第一' as 第一,'第二' AS 第二,'第三' AS 第三,'第四' AS 第四));

*說明：pivot（聚合函數(shù) for 列名 in（類型））棺妓，其中 in(‘’) 中可以指定別名，in中還可以指定子查詢炮赦，比如 select distinct ranking from temp*

常用的分析函數(shù)如下所列：

row_number() over(partition by ... order by ...)

rank() over(partition by ... order by ...)

dense_rank() over(partition by ... order by ...)

count() over(partition by ... order by ...)

max() over(partition by ... order by ...)

min() over(partition by ... order by ...)

sum() over(partition by ... order by ...)

avg() over(partition by ... order by ...)

first_value() over(partition by ... order by ...)

last_value() over(partition by ... order by ...)

lag() over(partition by ... order by ...)

lead() over(partition by ... order by ...)

------------------------------------------------------

------------DBMS_JOB 作業(yè)（定時(shí)器） ------------

------------------------------------------------------

一怜跑、dbms_job涉及到的知識(shí)點(diǎn)

1、創(chuàng)建job：（需在命令行窗口執(zhí)行）

variable jobno number;

dbms_job.submit(:jobno, --job號(hào),系統(tǒng)默認(rèn)隨機(jī)賦值

'過程名（parameter）;',--執(zhí)行的存儲(chǔ)過程, ';'不能省略 ,如果入?yún)饔凶址畡t需用雙單引號(hào)（''parameter''）

next_date, --下次執(zhí)行時(shí)間

'interval' --每次間隔時(shí)間吠勘，interval以天為單位

);

2性芬、刪除job: dbms_job.remove(jobno);

3、修改要執(zhí)行的操作: job:dbms_job.what(jobno, what);

4剧防、修改下次執(zhí)行時(shí)間：dbms_job.next_date(jobno, next_date);

5植锉、修改間隔時(shí)間：dbms_job.interval(jobno, interval);

6、啟動(dòng)job: dbms_job.run(jobno);

7峭拘、停止job: dbms.broken(jobno, broken, nextdate); --broken為boolean值

二俊庇、初始化相關(guān)參數(shù)job_queue_processes

1、job_queue_process表示oracle能夠并發(fā)的job的數(shù)量鸡挠，當(dāng)job_queue_process值為0時(shí)表示全部停止oracle的job辉饱。

2、查看job_queue_processes參數(shù)

方法一：

show parameter job_queue_process;

方法二：

select * from v$parameter where name='job_queue_processes';

3宵凌、修改job_queue_processes參數(shù)

alter system set job_queue_processes = 10;

三、user_jobs表結(jié)構(gòu)

字段（列） 類型 描述

job number 任務(wù)的唯一標(biāo)示號(hào)

log_user varchar2(30) 提交任務(wù)的用戶

priv_user varchar2(30) 賦予任務(wù)權(quán)限的用戶

schema_user varchar2(30) 對(duì)任務(wù)作語法分析的用戶模式

last_date date 最后一次成功運(yùn)行任務(wù)的時(shí)間

last_sec varchar2(8) 如hh24:mm:ss格式的last_date日期的小時(shí)止后，分鐘和秒

this_date date 正在運(yùn)行任務(wù)的開始時(shí)間瞎惫，如果沒有運(yùn)行任務(wù)則為null

this_sec varchar2(8) 如hh24:mm:ss格式的this_date日期的小時(shí)，分鐘和秒

next_date date 下一次定時(shí)運(yùn)行任務(wù)的時(shí)間

以下使用一個(gè)案例來演示dbms_job的使用

一译株、在plsql中創(chuàng)建表：

create table t(

id varchar2(30),

name varchar2(30)

);

二瓜喇、在plsql中創(chuàng)建存儲(chǔ)過程：

create or replace procedure proce_t is

begin

insert into t(id, name) values('1', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));

commit;

end proce_t;

/

三、創(chuàng)建job任務(wù)(1分鐘執(zhí)行一次)：

在sql>后執(zhí)行：

variable jobno number;

begin

dbms_job.submit(:jobno,'proce_t;', sysdate, 'sysdate+1/24/60');

commit;

end;

/

提交后提示：

pl/sql procedure successfully completed

jobno

---------

25

四歉糜、跟蹤任務(wù)的情況(查看任務(wù)隊(duì)列)：

sql> select job, next_date, next_sec, failures, broken from user_jobs;

job next_date next_sec failures broken

---------- ----------- ---------------- ---------- --------

25 2012/9/14 1 10:59:46 0 n

說明任務(wù)已創(chuàng)建成功乘寒。

執(zhí)行select * from t;查看定時(shí)任務(wù)的結(jié)果》瞬梗可以看出定時(shí)任務(wù)是正常執(zhí)行了的伞辛。

五烂翰、停止定時(shí)任務(wù)

1、查看定時(shí)任務(wù)的job號(hào)蚤氏。

sql> select job, next_date, next_sec, failures, broken from user_jobs;

job next_date next_sec failures broken

---------- ----------- ---------------- ---------- ------

25 2012/9/14 1 11:01:48 0 n

2甘耿、停止一個(gè)已啟動(dòng)的定時(shí)任務(wù)：

begin

dbms_job.broken(25, true, sysdate);

commit;

end;

/

表示停止job為25的任務(wù)。

執(zhí)行后顯示如下：

pl/sql procedure successfully completed

3竿滨、查看定時(shí)任務(wù)是否已停止成功

sql> select job, next_date, next_sec, failures, broken from user_jobs;

job next_date next_sec failures broken

---------- ----------- ---------------- ---------- ------

25 4000/1/1 00:00:00 0 y

broken值為y佳恬，表示定時(shí)任務(wù)已停止。

六于游、啟動(dòng)定時(shí)任務(wù)

1毁葱、查看停止定時(shí)任務(wù)

sql> select job, next_date, next_sec, failures, broken from user_jobs;

job next_date next_sec failures broken

---------- ----------- ---------------- ---------- ------

25 4000/1/1 00:00:00 0 y

broken值為y，表示定時(shí)任務(wù)已停止贰剥。

2倾剿、啟動(dòng)定時(shí)任務(wù)

begin

dbms_job.run(25);

commit;

end;

/

3、查看定時(shí)任務(wù)是否已啟動(dòng)

sql> select job, next_date, next_sec, failures, broken from user_jobs;

job next_date next_sec failures broken

---------- ----------- ---------------- ---------- ------

25 2012/9/14 1 11:06:17 0 n

broken值為n鸠澈，表示定時(shí)任務(wù)啟動(dòng)成功柱告。

七、查看進(jìn)程數(shù)

show parameter job_queue_processes;

必須大于0笑陈，否則執(zhí)行下面的命令修改：

alter system set job_queue_processes=10;

八际度、再創(chuàng)建一個(gè)任務(wù)(每5分鐘執(zhí)行一次)：

variable jobno number;

begin

dbms_job.submit(:jobno, 'proce_t;', sysdate, 'sysdate+1/24/12'); --interval是以天為單位的

commit;

end;

/

九、 執(zhí)行

select job,next_date,next_sec,failures,broken from user_jobs;

結(jié)果：

sql> select job,next_date,next_sec,failures,broken from user_jobs;

job next_date next_sec failures broken

---------- ----------- ---------------- ---------- ------

26 2012/9/14 1 11:12:08 0 n

25 2012/9/14 1 11:07:18 0 n

十涵妥、總結(jié)

關(guān)于job運(yùn)行時(shí)間

1:每分鐘執(zhí)行

Interval => TRUNC(sysdate,'mi') + 1/(24*60)

2:每天定時(shí)執(zhí)行

例如：每天的凌晨1點(diǎn)執(zhí)行

Interval => TRUNC(sysdate) + 1 +1/(24)

3:每周定時(shí)執(zhí)行

例如：每周一凌晨1點(diǎn)執(zhí)行

Interval => TRUNC(next_day(sysdate,'星期一'))+1/24

4:每月定時(shí)執(zhí)行

例如：每月1日凌晨1點(diǎn)執(zhí)行

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

5:每季度定時(shí)執(zhí)行

例如每季度的第一天凌晨1點(diǎn)執(zhí)行

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

6:每半年定時(shí)執(zhí)行

例如：每年7月1日和1月1日凌晨1點(diǎn)

Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

7:每年定時(shí)執(zhí)行

例如：每年1月1日凌晨1點(diǎn)執(zhí)行

Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'), 12)+1/24

job的運(yùn)行頻率設(shè)置

1.每天固定時(shí)間運(yùn)行乖菱，比如早上8:10分鐘：Trunc(Sysdate+1) + (8*60+10)/24*60

2.Toad中提供的：

每天：trunc(sysdate+1)

每周：trunc(sysdate+7)

每月：trunc(sysdate+30)

每個(gè)星期日：next_day(trunc(sysdate),'星期日')

每天6點(diǎn)：trunc(sysdate+1)+6/24

半個(gè)小時(shí)：sysdate+30/(24*60)

3.每個(gè)小時(shí)的第15分鐘運(yùn)行，比如：8:15蓬网，9:15窒所，10:15…：trunc(sysdate,'hh')+(60+15)/(24*60) 。

--------------------------------------------------