ORACLE基礎(chǔ)學(xué)習(xí)

1、ORACLE 基礎(chǔ)學(xué)習(xí)基礎(chǔ)學(xué)習(xí) 企業(yè)信息化部企業(yè)信息化部 2015-01-15 2015-01-15課程內(nèi)容課程內(nèi)容數(shù)據(jù)庫基本概念介紹數(shù)據(jù)庫基本概念介紹SQL程序語言程序語言SQL語言語言數(shù)據(jù)庫基本概念數(shù)據(jù)庫基本概念-（1）Oracle 服務(wù)器：Oracle 服務(wù)器中包含多種文件結(jié)構(gòu)、進(jìn)程結(jié)構(gòu)和內(nèi)存結(jié)構(gòu)；但是，處理SQL 語句時(shí)，并非所有這些結(jié)構(gòu)都會(huì)用到。某些結(jié)構(gòu)用于提高數(shù)據(jù)庫的性能，確保該數(shù)據(jù)庫在遇到軟件或硬件錯(cuò)誤時(shí)可以恢復(fù)，或者執(zhí)行維護(hù)該數(shù)據(jù)庫所需的其它任務(wù)。Oracle 服務(wù)器包括一個(gè)Oracle 例程和一個(gè)Oracle 數(shù)據(jù)庫。 Oracle 例程：Oracle 例程是后臺(tái)進(jìn)程和內(nèi)存結(jié)

2、構(gòu)的組合。只有啟動(dòng)例程后，才能訪問數(shù)據(jù)庫中的數(shù)據(jù)。每次啟動(dòng)例程時(shí)，會(huì)分配系統(tǒng)全局區(qū)(SGA) 并啟動(dòng)Oracle 后臺(tái)進(jìn)程。后臺(tái)進(jìn)程代表調(diào)用進(jìn)程執(zhí)行各種功能。它們把為每個(gè)用戶運(yùn)行的多個(gè)Oracle 程序所處理的功能統(tǒng)一起來。后臺(tái)進(jìn)程執(zhí)行輸入/輸出(I/O)，并監(jiān)視其它Oracle 進(jìn)程來提高并行性，從而使性能和可靠性更加優(yōu)越。數(shù)據(jù)庫基本概念數(shù)據(jù)庫基本概念-（2）Oracle 數(shù)據(jù)庫：Oracle 數(shù)據(jù)庫包含操作系統(tǒng)文件（也稱為數(shù)據(jù)庫文件），這些文件為數(shù)據(jù)庫信息提供了實(shí)際的物理存儲(chǔ)。數(shù)據(jù)庫文件用于確保數(shù)據(jù)一致性并能在例程失敗時(shí)得以恢復(fù)。表空間：表空間：是實(shí)際存儲(chǔ)數(shù)據(jù)的地方。數(shù)據(jù)庫存儲(chǔ)分配就是表空

3、間的定義過程，因此表空間的設(shè)計(jì)對(duì)數(shù)據(jù)庫物理設(shè)計(jì)極為重要，其參數(shù)設(shè)定好壞對(duì)數(shù)據(jù)庫的性能及維護(hù)影響巨大。數(shù)據(jù)庫基本概念數(shù)據(jù)庫基本概念-（3）表：表是由確定的列數(shù)和可變的行數(shù)組成的邏輯結(jié)構(gòu)。是存放數(shù)據(jù)的基本單元。數(shù)據(jù)庫基本概念數(shù)據(jù)庫基本概念-（4）視圖：視圖提供了在一個(gè)表或多個(gè)表中查看數(shù)據(jù)的替代方法，它是表上的一個(gè)動(dòng)態(tài)窗口。視圖并沒有存儲(chǔ)數(shù)據(jù)，它只是將一個(gè)查詢結(jié)果集表示成一個(gè)虛擬表。索引：索引是為了加快查詢的速度而在表上創(chuàng)建的對(duì)象。課程內(nèi)容課程內(nèi)容數(shù)據(jù)庫基本概念介紹數(shù)據(jù)庫基本概念介紹SQL程序語言程序語言SQL語言語言SQL數(shù)據(jù)類型數(shù)據(jù)類型數(shù)據(jù)類型描述描述VARCHAR2(SIZE)可變長(zhǎng)字符串類型

4、。SIZE默認(rèn)單位為BYTE，最大長(zhǎng)度為4000，最小為1NUMBER(PRECISION,SCALE)可變長(zhǎng)度的數(shù)字類型，PRECISION是數(shù)字可用的最大位數(shù)。如有不指定PRECISION,SCALE，可以提供PRECISION和SCALE為38位的數(shù)字DATE日期類型，從公元前4712年1月1日到公元9999年12月31日ROWID用一個(gè)十六進(jìn)制的數(shù)來標(biāo)明當(dāng)前行在表內(nèi)的唯一地址BLOB存儲(chǔ)非結(jié)構(gòu)化二進(jìn)制文件。常用于圖片，視頻的存儲(chǔ)CLOB存儲(chǔ)單字節(jié)或者多字節(jié)字符數(shù)據(jù)。常用語文本存儲(chǔ)SQL語言SQL語言創(chuàng)建表使用CREATE TABLE語句創(chuàng)建表，需指定表名、列名和數(shù)據(jù)類型。可通過tabl

5、espace指定表空間。SQL語言創(chuàng)建表CREATE建表示例：比較常用的建表方法改為“ulocal_tbs”SQL語言創(chuàng)建視圖使用CREATE VIEW語句創(chuàng)建視圖，以限制對(duì)表的數(shù)據(jù)訪問或代替常用的SQL查詢語句視圖查詢方式SQL語言修改表SQL語言修改表SQL語言刪除表在在ORACLE10版本以后，如果版本以后，如果DROP不帶不帶PURGE選項(xiàng)，則會(huì)把表先放在回選項(xiàng)，則會(huì)把表先放在回收站收站DROP TABLE DEPT PURGE;SELECT * FROM USER_RECYCLEBIN;PURGE TABLE DEPT;SQL語言RENAME要更改表、視圖、序列或同義詞的名稱，可以執(zhí)

6、行RENAME語句RENAME的另一種寫法。SQL語言注釋可以使用COMMENT語句向表或列添加備注SQL語言約束常用的約束有四種：非空約束，主鍵約束，外鍵約束和唯一性約束。SQL語言約束非空約束：為表的列添加非空約束后，再為該表添加數(shù)據(jù)時(shí)就不可以插入空置了。主鍵約束：主鍵約束在一個(gè)表中，只能定義一個(gè)，定義為主鍵約束的可以是列或者列的組合，其中不能包含重復(fù)值，并且不能包含NULL值。外鍵約束：是讓兩個(gè)表通過外鍵建立關(guān)系。在使用外鍵約束時(shí)，被引用的列應(yīng)該具有主鍵約束或者具有唯一性約束。唯一性約束：用來保證表中的某一列或者表中的某幾列組合起來不重復(fù)的一種手段。SQL語言約束除了在表創(chuàng)建時(shí)，定義約束

7、關(guān)系，也可以在表創(chuàng)建后建立或取消約束關(guān)系，語句如下：SQL語言-插入數(shù)據(jù)使用INSERT語句為表添加數(shù)據(jù)SQL語言-插入數(shù)據(jù)更新數(shù)據(jù)更新數(shù)據(jù)刪除數(shù)據(jù)查詢語句Subselect語法：Select-cause：查詢語句-選擇列查詢語句-選擇行查詢語句-選擇行查詢語句-選擇行可以使用AND和OR來指定任意多個(gè)查詢條件。查詢語句-謂詞查詢語句-謂詞查詢語句-謂詞查詢語句-排序查詢語句-去除重復(fù)行查詢語句-運(yùn)算次序查詢語句的運(yùn)算次序如下，一個(gè)子句的輸出是下一個(gè)子句的輸入。只有運(yùn)算次序在后面的子句才能引用運(yùn)算次序在前面的子句。查詢語句-給表達(dá)式命名查詢語句-子查詢查詢語句-分組函數(shù)分組函數(shù)對(duì)列中的一組值進(jìn)

8、行運(yùn)算以得到單個(gè)結(jié)果值。函數(shù)名稱函數(shù)名稱功能功能AVG返回某一組中的值除以該組中值得個(gè)數(shù)的和COUNT返回一組行或值的個(gè)數(shù)MAX返回一組值中的最大值MIN返回一組值中的最小值SUM返回一組值的和查詢語句-數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)函數(shù)名稱函數(shù)名稱功能功能ABS(N)返回?cái)?shù)字N的絕對(duì)值CEIL(N)返回大于等于數(shù)字N的最小整數(shù)FLOOR(N)返回小于等于數(shù)字N的最大整數(shù)MOD(M,N)返回兩個(gè)數(shù)字相除后的余數(shù)。如果N為0，則返回MROUND(N,M)執(zhí)行四舍五入運(yùn)算SIGN(N)N大于0，返回1，N等于0，返回0，N小于0，返回-1TRUNC(N,M)對(duì)數(shù)字進(jìn)行截取操作查詢語句-字符函數(shù)字符函數(shù)函數(shù)名稱函

9、數(shù)名稱功能功能CONCAT(S1,S2)連接兩個(gè)字符串INSTR(S1,S2,n,m)取得子串S2在字符串S1中的位置，N為起始搜索位置，M為子串出現(xiàn)次數(shù)，N,M默認(rèn)為1LENGTH(S)返回字符串的長(zhǎng)度LOWER返回字符串,并將所有的字符小寫UPPER返回字符串,并將所有的字符大寫SUBSTR (S,N,M)取子字符串,從N開始,取 M個(gè)REPLACE (S,S1,S2)將字符串S中的子串S1用S2替換查詢語句-日期函數(shù)日期函數(shù)函數(shù)名稱函數(shù)名稱功能功能ADD_MONTHS (D,N)返回指定日期時(shí)間D之前或者之后的N個(gè)月所對(duì)應(yīng)的時(shí)間LAST_DAY (D)返回指定日期所在月份的最后一天MON

10、THS_BETWEEN (D1,D2)返回日期D1和D2之間相差的月數(shù)NEXT_DAY (D,day)返回指定日期D后的第一個(gè)工作日（由day指定）所對(duì)應(yīng)的日期SYSDATE用來得到系統(tǒng)的當(dāng)前日期TRUNC(D,FMT)按指定格式格式化日期查詢語句-轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)函數(shù)名稱函數(shù)名稱功能功能TO_CHAR(D,FMT)按指定格式將日期轉(zhuǎn)變?yōu)樽址甌O_CHAR(N,FMT)按指定格式將數(shù)字轉(zhuǎn)變?yōu)樽址甌O_DATE(S,FMT)按指定格式將符合特定日期格式的字符串轉(zhuǎn)變?yōu)镈ATE類型的值TO_NUMBER (S,FMT) 按指定格式將符合特定數(shù)字格式的字符串轉(zhuǎn)變?yōu)閿?shù)字值查詢語句字符串使用單引號(hào),如

11、:ClerkORDER BY 指定多列時(shí),先按第一列排序,對(duì)于第一列相同的行,再按第二列排序.不同列可以指定不同的排序,如:SELECT DISTINCT DEPT, JOBFROM STAFFWHERE DEPT SET SERVEROUTPUT ONSQL BEGINUPDATE prod SET price=270WHERE pid= 0005;IF SQL%FOUND THENDBMS_OUTPUT.PUT_LINE(數(shù)據(jù)已修改);END IF; END;/游標(biāo)簡(jiǎn)介游標(biāo)簡(jiǎn)介隱式游標(biāo) 循環(huán)游標(biāo)用于簡(jiǎn)化游標(biāo)處理代碼當(dāng)用戶需要從游標(biāo)中提取所有記錄時(shí)使用循環(huán)游標(biāo)的語法如下：FOR IN LOO

12、PEND LOOP;游標(biāo)簡(jiǎn)介顯式游標(biāo)在 PL/SQL 塊的聲明部分定義查詢，該查詢可以返回多行顯式游標(biāo)的操作過程： 數(shù)據(jù)庫打開游標(biāo)30George344Roger245James1Stud_mrksStud_namestud_no提取行變量 關(guān)閉游標(biāo) 顯式游標(biāo) 游標(biāo)簡(jiǎn)介帶參數(shù)的顯式游標(biāo)聲明顯式游標(biāo)時(shí)可以帶參數(shù)以提高靈活性聲明帶參數(shù)的顯式游標(biāo)的語法如下：CURSOR ( ) IS select_statement;游標(biāo)簡(jiǎn)介使用顯式游標(biāo)更新行 2-1允許使用游標(biāo)刪除或更新活動(dòng)集中的行聲明游標(biāo)時(shí)必須使用 SELECT FOR UPDATE語句 CURSOR IS SELECT statement F

13、OR UPDATE;UPDATE SET WHERE CURRENT OF 程序子程序命名的 PL/SQL 塊，編譯并存儲(chǔ)在數(shù)據(jù)庫中。子程序的各個(gè)部分：聲明部分可執(zhí)行部分異常處理部分(可選)子程序的分類：過程 執(zhí)行某些操作函數(shù) 執(zhí)行操作并返回值過程創(chuàng)建過程的語法：CREATE OR REPLACE PROCEDURE ()IS|AS BEGIN EXCEPTION END;程序程序過程 過程參數(shù)的三種模式：IN用于接受調(diào)用程序的值默認(rèn)的參數(shù)模式OUT用于向調(diào)用程序返回值 IN OUT用于接受調(diào)用程序的值，并向調(diào)用程序返回更新的值程序函數(shù)函數(shù)是可以返回值的命名的 PL/SQL 子程序。 創(chuàng)建函數(shù)

14、的語法： CREATE OR REPLACE FUNCTION (param1,param2)RETURN IS|AS local declarationsBEGIN Executable Statements; RETURN result;EXCEPTION Exception handlers;END;過程和函數(shù)的比較過過 程程函函 數(shù)數(shù)作為 PL/SQL 語句執(zhí)行作為表達(dá)式的一部分調(diào)用在規(guī)格說明中不包含 RETURN 子句必須在規(guī)格說明中包含 RETURN 子句不返回任何值必須返回單個(gè)值可以包含 RETURN 語句，但是與函數(shù)不同，它不能用于返回值必須包含至少一條 RETURN 語句程序

15、程序程序包程序包是對(duì)相關(guān)過程、函數(shù)、變量、游標(biāo)和異常等對(duì)象的封裝程序包由規(guī)范和主體兩部分組成聲明程序包中公共對(duì)象。包括類型、變量、常量、異常、游標(biāo)規(guī)范和子程序規(guī)范等聲明程序包私有對(duì)象和實(shí)現(xiàn)在包規(guī)范中聲明的子程序和游標(biāo)程序包規(guī)范主體程序創(chuàng)建程序包程序包規(guī)范CREATE OR REPLACE PACKAGE package_name IS|ASPublic item declarationsSubprogram specificationEND package_name;程序包主體CREATE OR REPLACE PACKAGE BODY package_name IS|ASPrivate item declarationsSubprogram bodiesBEGINInitializationEND package_name;系統(tǒng)視圖V$SESSIONV$VERSIO