1.1 Oracle數(shù)據(jù)庫系統(tǒng)的特點(diǎn)

1.1.1 Oracle的發(fā)展史

1977年，Oracle誕生。現(xiàn)CEO勞倫斯.埃里森 Lawrence (Larry) J. Ellison與女上司Robert Miner創(chuàng)立“軟件開發(fā)實(shí)驗(yàn)室”（Software Development Labs）。當(dāng)時(shí)IBM發(fā)表“關(guān)聯(lián)數(shù)據(jù)庫”的論文，埃里森以此研發(fā)新數(shù)據(jù)庫，名為Oracle。1979年，公司前往硅谷，更名為“關(guān)系軟件公司”（Relational Software Inc. , RSI）。1983年，為了突出公司的核心產(chǎn)品，RSI再次更名為Oracle。2002年4月26日，正式啟用“甲骨文”作為公司的中文注冊(cè)商標(biāo)。

Oracle是數(shù)據(jù)庫管理系統(tǒng)，是Oracle公司的核心產(chǎn)品。Oracle在管理信息系統(tǒng)、企業(yè)數(shù)據(jù)處理、因特網(wǎng)及電子商務(wù)等領(lǐng)域使用非常廣泛。由于其在數(shù)據(jù)安全性與完整性控制方面的優(yōu)越性能，以及跨操作系統(tǒng)、跨硬件平臺(tái)的數(shù)據(jù)互操作能力，使得越來越多的用戶將Oracle作為其應(yīng)用數(shù)據(jù)的處理系統(tǒng)。

Oracle數(shù)據(jù)庫系統(tǒng)的主要版本有Oracle 8i、Oracle 9i、Oracle 10g，最新版本是Oracle 11g。其中后綴i的含義是Internet，表示Oracle公司進(jìn)軍互聯(lián)網(wǎng)。后綴g的含義是Grid，表示支持網(wǎng)格計(jì)算。本書中使用的版本是Oracle 11g。

1.1.2 Oracle的功能特點(diǎn)

Oracle數(shù)據(jù)庫基于“客戶/服務(wù)器”（Client/Server，C/S）系統(tǒng)結(jié)構(gòu)。此概念將應(yīng)用程序功能處理劃分到兩個(gè)系統(tǒng)中，即客戶端和服務(wù)器端。與用戶相關(guān)的活動(dòng)在客戶端完成，數(shù)據(jù)庫相關(guān)的活動(dòng)在服務(wù)器端完成。

Oracle數(shù)據(jù)庫的主要特點(diǎn)如下：

? 支持多用戶，高并發(fā)事務(wù)處理。

? 數(shù)據(jù)安全性和完整性方面性能優(yōu)越。

? 網(wǎng)格計(jì)算，Oracle能夠把許多低成本的設(shè)備集成起來，為用戶提供高性能的共享計(jì)算能力。

? 具有高可移植性，能夠在主流的操作系統(tǒng)和硬件平臺(tái)上運(yùn)行。

1.2 如何啟動(dòng)Oracle數(shù)據(jù)庫

我們?cè)趯W(xué)習(xí)SQL Server的時(shí)候，為了正常使用SQL Server數(shù)據(jù)庫，會(huì)打開Windows操作系統(tǒng)的“服務(wù)”組件啟動(dòng)SQL Server相應(yīng)的系統(tǒng)服務(wù)。那么為了使用Oracle，該如何啟動(dòng)Oracle數(shù)據(jù)庫呢？

實(shí)際上，Oracle數(shù)據(jù)庫庫的啟動(dòng)和關(guān)閉是日常工作之一。Windows操作系統(tǒng)下的Oracle服務(wù)以后臺(tái)服務(wù)進(jìn)程的方式來進(jìn)行管理。服務(wù)是在數(shù)據(jù)庫創(chuàng)建過程創(chuàng)建的，并且與數(shù)據(jù)庫關(guān)聯(lián)，啟動(dòng)或關(guān)閉與Oracle數(shù)據(jù)庫相關(guān)的服務(wù)，即完成Oracle數(shù)據(jù)庫的啟動(dòng)和關(guān)閉。一個(gè)數(shù)據(jù)庫有一項(xiàng)或多項(xiàng)服務(wù)與之關(guān)聯(lián)。如果要查看Oracle服務(wù)，可以選擇【開始】→【設(shè)置】→【控制面板】→【管理工具】→【服務(wù)】來打開“服務(wù)”窗口，如圖1.21.1所示。

? OracleOraDb11g_home1TNSListener

OracleOraDb11g_homeTNSListener服務(wù)是Oracle服務(wù)器的監(jiān)聽程序。客戶端必須先連接駐留在數(shù)據(jù)庫服務(wù)器上的監(jiān)聽程序，才能連接數(shù)據(jù)庫服務(wù)器。監(jiān)聽器接收從客戶端發(fā)出的請(qǐng)求，然后將請(qǐng)求傳遞給數(shù)據(jù)庫服務(wù)器，以此建立客戶端與服務(wù)器的連接。一旦建立了連接，客戶端便可以與Oracle數(shù)據(jù)庫服務(wù)器直接通信。

? OracleServiceSID

OracleServiceSID服務(wù)是為SID（系統(tǒng)標(biāo)識(shí)符）數(shù)據(jù)庫示例創(chuàng)建的。其中，SID是在安裝Oracle 11g時(shí)輸入的數(shù)據(jù)庫名稱。該服務(wù)是Oracle的核心服務(wù)，啟動(dòng)Oracle實(shí)例必須啟動(dòng)該服務(wù)。

? OracleJobSchedulerSID

OracleJobSchedulerSID服務(wù)是任務(wù)調(diào)度服務(wù)，負(fù)責(zé)按預(yù)先設(shè)置的時(shí)間周期性地執(zhí)行某項(xiàng)用戶自定義的功能，從而實(shí)現(xiàn)Oracle服務(wù)器的自動(dòng)管理功能。

? OracleDBConsoleSID

OracleDBConsoleSID服務(wù)負(fù)責(zé)在Windows平臺(tái)下啟動(dòng)Oracle企業(yè)管理器。Oracle 11g企業(yè)管理器是一個(gè)功能完善的Oracle數(shù)據(jù)庫管理工具，可以管理本地?cái)?shù)據(jù)庫環(huán)境和網(wǎng)絡(luò)環(huán)境。如圖1.21.2所示：

注意

Oracle服務(wù)器端，至少應(yīng)該啟動(dòng)OracleOraDb11g_homeTNSListener和OracleServiceSID兩個(gè)服務(wù)。

1.1 如何登錄Oracle數(shù)據(jù)庫

啟動(dòng)Oracle數(shù)據(jù)庫之后，就可以開始使用Oracle數(shù)據(jù)庫來完成我們需要的數(shù)據(jù)處理。啟動(dòng)的內(nèi)部過程比較復(fù)雜，本章暫且不仔細(xì)研究，先掌握在正確啟動(dòng)Oracle數(shù)據(jù)庫之后，使用SQL Plus和PL/SQL Developer兩款常用工具登錄到數(shù)據(jù)庫。

Oracle默認(rèn)創(chuàng)建多個(gè)用戶，其中包括SYS、SYSTEM和SCOTT。

? SYS用戶

SYS用戶是Oracle中的超級(jí)用戶，主要用于維護(hù)系統(tǒng)信息和管理示例，數(shù)據(jù)庫中數(shù)據(jù)字典的所有表和視圖都存儲(chǔ)在SYS中。

? SYSTEM用戶

SYSTEM用戶是Oracle中默認(rèn)的管理員，它擁有DBA權(quán)限。該用戶擁有Oracle管理工具使用的內(nèi)部表和視圖，通常通過SYSTEM用戶管理Oracle數(shù)據(jù)庫的用戶、權(quán)限和存儲(chǔ)等。

? SCOTT用戶

SCOTT是Oracle數(shù)據(jù)庫的示例用戶。SCOTT用戶包含4張示范表，該用戶的默認(rèn)口令為tiger。

注意

建議日常的管理任務(wù)使用SYSTEM用戶登錄Oracle數(shù)據(jù)庫服務(wù)器。如果需要執(zhí)行備份、恢復(fù)、更改數(shù)據(jù)庫的任務(wù)，則必須以SYS用戶登錄Oracle數(shù)據(jù)庫服務(wù)器。

1.1 使用SQL命令管理數(shù)據(jù)表

當(dāng)能夠正確啟動(dòng)并登錄Oracle數(shù)據(jù)庫之后，我們開始體驗(yàn)Oracle對(duì)數(shù)據(jù)的管理。那么在Oracle當(dāng)中，我們?nèi)绾涡陆〝?shù)據(jù)表，標(biāo)準(zhǔn)的數(shù)據(jù)類型都有哪些呢？

1.1.1 回顧SQL語言

SQL（結(jié)構(gòu)化查詢語言）是一種在關(guān)系型數(shù)據(jù)庫中定義和操作數(shù)據(jù)的標(biāo)準(zhǔn)語言，是應(yīng)用程序與數(shù)據(jù)進(jìn)行交互操作的接口。

SQL查詢語言主要包括以下4種：

（1）數(shù)據(jù)定義語言（Data Definition Language，DDL）：用于創(chuàng)建、修改和刪除數(shù)據(jù)庫對(duì)象，如CREATE TABLE、ALTER TABLE、DROP TABLE等。DDL語句可以自動(dòng)提交事務(wù)。

（2）數(shù)據(jù)操縱語言（Data Manipulation Language，DML）：用于操縱數(shù)據(jù)庫，包括INSERT、UPDATE、DELETE、SELECT等。

（3）數(shù)據(jù)控制語言（Data Control Language，DCL）：用于執(zhí)行授予權(quán)限和撤銷權(quán)限的操作，包括GRANT（授予權(quán)限）、REVOKE（撤銷權(quán)限）兩條命令。DCL語句可以自動(dòng)提交事務(wù)。

（4）事務(wù)控制語言（Transactional Control Language，TCL）：用于維護(hù)數(shù)據(jù)的一致性，包括COMMIT（提交事務(wù)）、ROLLBACK（回滾事務(wù)）和SAVEPOINT（設(shè)置保存點(diǎn)）3條語句。

1.1.2 Oracle常用數(shù)據(jù)庫數(shù)據(jù)類型

Oracle 支持的數(shù)據(jù)類型多達(dá)幾十種。其中，常用數(shù)據(jù)類型大致可以分為字符類型、數(shù)字類型、日期類型、大對(duì)象類型等。以下將講解常用、典型的數(shù)據(jù)類型。

1．字符類型

（1）CHAR類型

CHAR表示固定長(zhǎng)度字符串，長(zhǎng)度不足時(shí)使用空格補(bǔ)充，最多可以存儲(chǔ)2000個(gè)字節(jié)。CHAR類型區(qū)分中英文，中文在CHAR中占兩個(gè)字節(jié)，而英文只占一個(gè)字節(jié)，所以CHAR（20）只能存20個(gè)字母或10個(gè)漢字。

（2）VARCHAR2類型。

VARCHAR2表示可變長(zhǎng)度字符串，最多可以存儲(chǔ)4000個(gè)字節(jié)。在定義該數(shù)據(jù)類型時(shí)，應(yīng)該指定其大小。與CHAR類型相比，使用VARCHAR2可以節(jié)省磁盤空間。例如，某列數(shù)據(jù)定義了VARCHAR（10）的數(shù)據(jù)類型，當(dāng)用戶輸入5個(gè)字節(jié)的字符時(shí)，該列值的長(zhǎng)度將是5個(gè)字節(jié)，而非10個(gè)，但該列值最大長(zhǎng)度不能超過10個(gè)字節(jié)。

2．?dāng)?shù)值類型

NUMBER類型可以存儲(chǔ)正數(shù)、負(fù)數(shù)、零、定點(diǎn)數(shù)和精度為38位的浮點(diǎn)數(shù)，格式為：NUMBER（M，N），其中M表示精度，代表數(shù)字的總位數(shù)；N表示小數(shù)點(diǎn)右側(cè)數(shù)字的位數(shù)。以下將演示NUMBER數(shù)據(jù)類型的用法：

（1）size  number（2）：表示size列的值只能是整數(shù)，并且最多只能取兩位整數(shù)。

（2）PRice number（6，2）：表示price列的整數(shù)部分最多只能是4位，小數(shù)部分最多只能保留兩位。

3．日期類型

（1）DATE類型。

DATE數(shù)據(jù)類型用于存儲(chǔ)表中的日期和時(shí)間數(shù)據(jù)，取值范圍是公元前4712年1月1日至公元9999年12月31日。DATE類型的長(zhǎng)度是7，7個(gè)字節(jié)分別表示世紀(jì)、年、月、日、時(shí)、分、秒。

（2）TIMESTAMP類型

TIMESTAMP數(shù)據(jù)類型用于存儲(chǔ)日期的年、月、日以及時(shí)間的小時(shí)、分和秒值。其中，秒的數(shù)據(jù)值精確到小數(shù)點(diǎn)后6位，該數(shù)據(jù)類型同時(shí)包含時(shí)區(qū)信息。

4．大對(duì)象類型

（1）CLOB大字符串對(duì)象類型

CLOB（Character Large Object）數(shù)據(jù)類型用于存儲(chǔ)可變長(zhǎng)度的字符數(shù)據(jù)，最多可存儲(chǔ)4GB的數(shù)據(jù)。該數(shù)據(jù)類型用于存儲(chǔ)VARCHAR2類型不能存儲(chǔ)的長(zhǎng)文本信息。

（2）BLOB大二進(jìn)制類型

BLOB（Binary Large Object）數(shù)據(jù)類型用于存儲(chǔ)較大的二進(jìn)制對(duì)象，如圖片、視頻剪輯和聲音剪輯等，最多可存儲(chǔ)4GB的數(shù)據(jù)。

注意

Oracle也支持INTEGER、FLOAT、DOUBLE等數(shù)值類型，但建議采用Oracle自身的NUMBER數(shù)據(jù)類型；Oracle也支持VARCHAR字符類型，但建議采用Oracle自身的VARCHAR2類型。

1.1.3 在SCOTT方案中創(chuàng)建數(shù)據(jù)庫表

Oracle中，可以使用CREATE TABLE命令創(chuàng)建數(shù)據(jù)庫表，具體語法如下。

問題：

某移動(dòng)通信公司需要建設(shè)網(wǎng)上營業(yè)廳，需要使用Oracle數(shù)據(jù)庫存儲(chǔ)用戶個(gè)人信息，內(nèi)容包括：機(jī)主姓名、手機(jī)號(hào)碼、地市歸屬、入網(wǎng)時(shí)間、消費(fèi)品牌名稱（如動(dòng)感地帶）等基本信息。如何創(chuàng)建數(shù)據(jù)庫表以保存?zhèn)€人信息呢？

以SCOTT用戶登錄SQL Plus，輸入示例21.1的SQL代碼。

示例1.1

CREATE TABLE PERSONAL_INFO

 (

OWNER_NAME VARCHAR2(20) NOT NULL,

TEL_CODE VARCHAR2 (15) NOT NULL,

CITY VARCHAR2 (20) NOT NULL,

NETWORK_DATE DATE NOT NULL,

BRAND_NAME VARCHAR2 (20) NOT NULL

 );

執(zhí)行后，使用DESC 命令查看表結(jié)構(gòu)。運(yùn)行結(jié)果如圖1.4.11.7所示：

圖1.4.11.7 創(chuàng)建個(gè)人信息表的顯示結(jié)果

我們?cè)谥皩W(xué)習(xí)SQL Server知識(shí)的時(shí)候，已經(jīng)學(xué)習(xí)過對(duì)表字段添加各種約束，例如唯一約束，在我們新建的個(gè)人信息表中，TEL_CODE（手機(jī)號(hào)碼）需要添加唯一約束，怎么實(shí)現(xiàn)呢？修改表結(jié)構(gòu)，并添加唯一約束，如示例1.2所示：

示例1.2

SQL> ALTER TABLE PERSONAL_INFO ADD CONSTRAINT U_TEL_CODE UNIQUE (TEL_CODE);

SQL>COL COLUMN_NAME FOR A20;

該代碼表示在接下來的查詢中，列“COLUMN_NAME”按照20個(gè)字符寬度顯示。

SQL> SELECT CONSTRAINT_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME='PERSONAL_INFO';

該代碼使用了數(shù)據(jù)庫視圖USER_CONS_COLUMNS，此視圖存儲(chǔ)了數(shù)據(jù)庫表中所有的約束信息，對(duì)應(yīng)的列描述見表1-41-1。

表1-41-1 列信息

通過對(duì)圖1.4.21.8的觀察，發(fā)現(xiàn)在TEL_CODE列上，建立了U_TEL_CODE唯一約束。

1.1 使用SQL命令管理序列

已經(jīng)創(chuàng)建了數(shù)據(jù)庫表，下一步就要錄入數(shù)據(jù)。需要解決的一個(gè)問題是：個(gè)人信息表沒有主鍵，如果有一ID字段并能夠?qū)崿F(xiàn)自動(dòng)編號(hào)功能，就方便多了。Oracle中是如何解決主鍵自動(dòng)增長(zhǎng)的呢？

Oracle并沒有類似SQLServer中的Identity來定義主鍵自增屬性。如果要實(shí)現(xiàn)主鍵自增，常用的方法是定義一個(gè)序列Sequence，然后在插入數(shù)據(jù)記錄時(shí)取序列中的下一個(gè)值nextval即可。

序列（Sequence）是Oracle提供的的用于產(chǎn)生一組等間隔整型數(shù)值的數(shù)據(jù)庫對(duì)象，可以通過在插入語句中使用序列值來實(shí)現(xiàn)主鍵自增。

Oracle中的CREATE  SEQUENCE命令用于創(chuàng)建序列。

說明如下：

? START WITH  用于指定序列的起始值。

? INCREMENT BY  用來指定序列的增量，如果取負(fù)值，則為遞減序列。

? MINVALUE | NOMINVALUE  指定序列是否有最小值。

? MAXVALUE | NOMAXVALUE 用來說明序列是否有最大值。

? CYCLE | NOCYCLE  用于指定序列值達(dá)到最大之后，是否重新從最小值開始產(chǎn)生。

? CACHE | NOCACHE  用來指定是否在緩存中保存預(yù)先分配的序列值，如果選擇CACHE，可以提高獲取序列的速度。

? ORDER | NOORDER 是否確保序列值是否唯一和有序。

下面定義SEQ_PERSONAL_INFO序列用來實(shí)現(xiàn)PERSONAL_INFO表的主鍵自動(dòng)編號(hào)，如示例1.3所示。

示例1.3

CREATE SEQUENCE SEQ_PERSONAL_INFO

START WITH 1

INCREMENT BY 1

NOMAXVALUE

CACHE 20;

使用序列時(shí)，需要用到序列的兩個(gè)偽列NEXTVAL和CURRVAL，其中NEXTVAL返回序列的下一個(gè)值，而CURRVAL返回序列的當(dāng)前值。

至此，我們只需要修改PERSONAL_INFO表，添加ID列，之后就可以使用序列實(shí)現(xiàn)自增了。如示例1.4所示。

示例1.4

ALTER TABLE PERSONAL_INFO ADD ID NUMBER PRIMARY KEY;

INSERT INTO PERSONAL_INFO VALUES('張三','19900000000','北京','18-7月-2011',

'動(dòng)感地帶',SEQ_PERSONAL_INFO.NEXTVAL);

INSERT INTO PERSONAL_INFO VALUES('李四','19900007777','北京','18-7月-2011',

'全球通',SEQ_PERSONAL_INFO.NEXTVAL);

首先修改了PERSONAL_INFO表，添加了主鍵字段ID。然后往表中新增了2條記錄。

1.1 使用SQL命令完成數(shù)據(jù)增刪改查

在本章前一節(jié)的內(nèi)容中，我們接觸了INSERT命令，會(huì)發(fā)現(xiàn)與之前學(xué)習(xí)的SQL Server操作很相似。事實(shí)上，雖然Oracle數(shù)據(jù)庫功能非常強(qiáng)大，體系結(jié)構(gòu)與其他數(shù)據(jù)庫有較大差異，但其同樣遵循標(biāo)準(zhǔn)的SQL語言。因此，對(duì)于修改和表數(shù)據(jù)，刪除表數(shù)據(jù)不再在過多介紹，只給出語法。

使用SELECT編寫子查詢

在數(shù)據(jù)庫數(shù)據(jù)操作語句（DML）中，查詢是較復(fù)雜的一類操作，也是體現(xiàn)數(shù)據(jù)庫技能掌握是否良好的重要指標(biāo)。在涉及基于數(shù)據(jù)庫編程方面，查詢也是至關(guān)重要的知識(shí)。本節(jié)我們著重講解Oracle數(shù)據(jù)庫數(shù)據(jù)查詢的知識(shí)。

1，.SCOTT示例方案簡(jiǎn)介

Oracle數(shù)據(jù)庫安裝的時(shí)候會(huì)提示是否安裝示例數(shù)據(jù)庫，如果選擇安裝了，會(huì)提供SCOTT方案供學(xué)習(xí)Oracle數(shù)據(jù)庫使用。SCOTT方案有表如1-6-11-2所示的數(shù)據(jù)表。

表1-1-12

2，.單行子查詢

子查詢是指嵌套在其他SQL語句中的查詢，它可以出現(xiàn)在SELETE、WHERE、FROM等語句中。使用子查詢，可以用一系列簡(jiǎn)單的查詢構(gòu)成復(fù)雜的查詢，從而增強(qiáng)SQL語句的功能。

單行子查詢不向外層查詢返回記錄或者只返回一條記錄。子查詢可以放在SELECT語句的WHERE或FROM等子句中。

示例1.5要求查詢工資最高的雇員信息。

示例1.5

     SELECT EMPNO, ENAME, SAL, HIREDATE FROM EMP

WHERE SAL= (SELECT MAX (SAL) FROM EMP);

多行子查詢

多行子查詢可以向父查詢返回多行記錄。在WHERE語句中使用多行子查詢時(shí)，必須使用多行運(yùn)算符（IN、NOT IN、EXISTS、NOT EXISTS、ALL和ANY等）。例如，查詢工資高于部門20中所有員工的雇員信息，先利用子查詢獲得部門20中所有員工的工資，再利用父查詢獲得其工資大于等于這些員工的雇員信息。

示例1.6

     SELECT ENAME, JOB, SAL, DEPTNO FROM EMP

WHERE SAL >= ALL (SELECT SAL FROM EMP WHERE DEPTNO=20);

相關(guān)子查詢

相關(guān)子查詢是指引用了父查詢中某些列的子查詢。在相關(guān)子查詢中，父查詢所處理的每一行都先被傳遞給子查詢。子查詢依次處理這些行，即將其應(yīng)用到子查詢，如果滿足子查詢中的條件，則父查詢中的這一行就是最終結(jié)果集中的一行，直到父查詢中的每一行都處理完成為止。一般可以在SELECT或WHERE語句中使用相關(guān)子查詢。例如，查詢負(fù)責(zé)管理其他雇員的管理員信息，父查詢提供一個(gè)雇員編號(hào)（EMPNO）給子查詢，子查詢利用該雇員編號(hào)查詢是否存在一個(gè)管理者（MGR）與父查詢提供的雇員編號(hào)（EMPNO）對(duì)應(yīng)。若存在，則說明該EMPNO就是管理者，將其顯示出來。

示例1.7

     SELECT EMPNO, ENAME, DEPTNO, SAL FROM EMP A

WHERE EXISTS (SELECT * FROM EMP B WHERE B.MGR = A.EMPNO);

添加數(shù)據(jù)

數(shù)據(jù)庫用來保存數(shù)據(jù)的，添加數(shù)據(jù)使用insert語句

（1）添加一條語句

SQL>insert into dept(deptno,dname,loc) values (60,’市場(chǎng)部’,’Beijing’);

插入多行

將SELECT語句檢索出來的所有數(shù)據(jù)行都插入到表中.這條語句通常在從一個(gè)表向另一個(gè)表快速復(fù)制數(shù)據(jù)行.

  SQL> create table dept1(deptno number(2) primary key,dname varchar2(14),loc varchar2(13));

  SQL> insert into dept1 select * from dept;

修改語句

SQL> update dept set dname = ‘國內(nèi)市場(chǎng)部’, loc=’HongKong’ where deptno = 60;

刪除語句

SQL> delete from dept where deptno = 60 or dname = ‘銷售部’;

Delete語句中如果沒有where子句表示刪除表中的所有數(shù)據(jù)

8．使用Truncate 刪除所有數(shù)據(jù)

SQL> truncate table shopinfo;

使用delete語句可以刪除表中的全部數(shù)據(jù)，但是delete操作會(huì)被記錄在日志文件中，

9．Oracle分頁查詢

Rownum是一個(gè)偽列，它會(huì)根據(jù)返回的記錄生成一個(gè)序列化的數(shù)字。可以使用ROWNUM列返回查詢結(jié)果集中前N條記錄

SQL> select rownum,deptno,ename,job,mgr,hiredate from emp where rownum<10

分頁實(shí)現(xiàn)

比如每5行為一頁，現(xiàn)在要顯示第2頁，就是要顯示第6行到第10行

SQL> select e.*  from

( select rownum r,deptno,ename,job,mgr,hiredate from emp )  e

Where  r>5  and  r<11;

10. rowid偽列

rowid就是唯一標(biāo)志記錄物理位置的一個(gè)id,內(nèi)容是當(dāng)前行位于哪個(gè)文件，塊，行的詳細(xì)信息。

SQL> select rownum,deptno,dname,loc  from dept

dual虛表

dual是系統(tǒng)定義的表,只有一行一列數(shù)據(jù). 寫成select   100*200   from   dual   這里的dual只是為了完善語義的 。對(duì)于虛表（dual）來說，其中的列往往是不相關(guān)的或無關(guān)緊要的。

SQL> select 5*9 from dual;

SQL> select sysdate from dual;

1.1 使用JDBC訪問Oracle數(shù)據(jù)

在第二學(xué)期，我們?cè)敿?xì)的學(xué)習(xí)了JDBC相關(guān)知識(shí)，在這里我們簡(jiǎn)要的回顧一下關(guān)鍵步驟和核心對(duì)象。

? 第一步，加載驅(qū)動(dòng)。關(guān)鍵代碼：Class.forName(“……”);

? 第二步，建立連接。關(guān)鍵代碼：DriverManager.getConnection(“……”);

? 第三步，執(zhí)行SQL。核心對(duì)象：PreparedStatement

? 第四步，處理結(jié)果。核心對(duì)象：ResultSet

? 第五步，釋放資源。

本章前部分，新建了個(gè)人信息數(shù)據(jù)庫表，現(xiàn)在需要查詢北京地區(qū)動(dòng)感地帶用戶數(shù)量。需要使用JDBC訪問Oracle數(shù)據(jù)庫實(shí)現(xiàn)。

示例1.8

import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Date;  public class PagingEmpInfo {public static void main(String[] args) {showPagingEmp(2,5);}/** * 分頁查詢員工信息 * @param pageNum 第幾頁 * @param pageSize  每頁記錄數(shù) * */public static void showPagingEmp(int pageNum,int pageSize){Connection con = null;PreparedStatement stmt = null;ResultSet rs = null;//數(shù)據(jù)庫連接信息String driverClass = "oracle.jdbc.driver.OracleDriver";String url = "jdbc:oracle:thin:@localhost:1521:orcl";String user = "scott";String passWord = "tiger";try {//加載驅(qū)動(dòng)Class.forName(driverClass);} catch (ClassNotFoundException e) {e.printStackTrace();}try {//建立數(shù)據(jù)庫連接con = DriverManager.getConnection(url,user,password);//準(zhǔn)備將要執(zhí)行的SQL語句，？號(hào)表示占位符，執(zhí)行是會(huì)被具體值替代String sql  = "select e.* from "+"(select rownum r,ename,job,hiredate from emp) e"+" where r> ? and r<?";//創(chuàng)建語句對(duì)象System.out.println(sql);stmt = con.prepareStatement(sql);//替換占位符int begin = (pageNum-1)*pageSize;int end = pageNum*pageSize+1;stmt.setInt(1, begin);stmt.setInt(2, end);//執(zhí)行SQL語句，得到結(jié)果集rs = stmt.executeQuery();//處理數(shù)據(jù)while(rs.next()){int rownum = rs.getInt(1);String ename = rs.getString(2);String job = rs.getString(3);Date hiredate = rs.getDate(4);System.out.println(rownum+"/t"+ename+"/t"+job+"/t"+hiredate);}} catch (SQLException e) {e.printStackTrace ();}finally {//釋放數(shù)據(jù)庫連接if(con != null){try {con.close ();} catch (SQLException e) {e.printStackTrace ();}}} }}
執(zhí)行結(jié)果如圖1.1.24所示
 
圖1.1.24北京動(dòng)感地帶用戶數(shù)
從示例1.78可以看出，使用JDBC訪問Oracle數(shù)據(jù)庫的步驟與訪問SQLServer的步驟相同，只是具體連接參數(shù)不同。數(shù)據(jù)庫驅(qū)動(dòng)類名稱字符串為“oracle.jdbc.driver.OracleDriver”，數(shù)據(jù)庫連接字符串為“jdbc:oracle:thin:@localhost:1521:orcl”，其中“l(fā)ocalhost”是Oracle服務(wù)器IP地址或者機(jī)器名，“1521”是監(jiān)聽端口，“orcl”是數(shù)據(jù)庫實(shí)例示例名稱。
? 本章總結(jié)
? Oracle 11g實(shí)例啟動(dòng)必須啟動(dòng)OracleOraDb11g_home1TNSListener，OracleServiceSID服務(wù)
? Oracle內(nèi)存可分為系統(tǒng)全局區(qū)(SGA)和程序全局區(qū)(PGA)
? SYS用戶是Oracle中的超級(jí)用戶，SYSTEM是Oracle中默認(rèn)的管理員，它擁有DBA權(quán)限，SCOTT是Oracle數(shù)據(jù)庫的一個(gè)示范帳戶
? Oracle管理工具主要包括SQL*PLUS、PL/SQL Developer和Oracle Enterprise Manager
? 使用JDBC訪問Oracle數(shù)據(jù)庫
? rownum是一個(gè)偽列，它會(huì)根據(jù)返回的記錄生成一個(gè)序列化的數(shù)字，rowid就是唯一標(biāo)志記錄物理位置的一個(gè)id,內(nèi)容是當(dāng)前行位于哪個(gè)文件，塊，行的詳細(xì)信息。
? Oracle 支持的數(shù)據(jù)類型多達(dá)幾十種。其中，常用數(shù)據(jù)類型大致可以分為字符類型、數(shù)字類型、日期類型、大對(duì)象類型等
? 序列（Sequence）是Oracle提供的的用于產(chǎn)生一組等間隔整型數(shù)值的數(shù)據(jù)庫對(duì)象，可以通過在插入語句中使用序列值來實(shí)現(xiàn)主鍵自增。Oracle中的CREATE  SEQUENCE命令用于創(chuàng)建序列
 
 
 
任務(wù)實(shí)訓(xùn)部分
 
1：使用SQL命令創(chuàng)建數(shù)據(jù)表
訓(xùn)練技能點(diǎn)
? 能夠正確使用SQL Plus登錄到Oracle數(shù)據(jù)庫
? 熟練使用Oracle數(shù)據(jù)類型
? 熟練使用CREATE TABLE命令創(chuàng)建表
需求說明
在Oracle中使用SQL命令創(chuàng)建一個(gè)聯(lián)系人表，表結(jié)構(gòu)如下所示。
表1-2-1 聯(lián)系人表
列名
數(shù)據(jù)類型
約束
id
number
主鍵，自增
name
varchar2
非空
address
varchar2
 
phone
varchar2
 
 
實(shí)現(xiàn)步驟
（1） 啟動(dòng)相關(guān)Oracle服務(wù)
（2） 打開SQL Plus登錄到Oracle數(shù)據(jù)庫
（3） 使用CREATE TABLE命令按照表1-2-1創(chuàng)建表
2：創(chuàng)建序列
訓(xùn)練技能點(diǎn)
? 為聯(lián)系人表的id字段創(chuàng)建序列
? 在insert語句中使用序列
需求說明
聯(lián)系人表的id字段是自動(dòng)增長(zhǎng)列，所以在Oracle中需要?jiǎng)?chuàng)建序列，要求從1開始，每次自增1。
實(shí)現(xiàn)步驟
（1） 使用CREATE  SEQUENCE命令創(chuàng)建序列
（2） 使用insert命令向聯(lián)系人表中插入測(cè)試數(shù)據(jù)
 
3：使用PL/SQL Developer工具
訓(xùn)練技能點(diǎn)
? 使用PL/SQL Developer工具登錄Oracle數(shù)據(jù)庫
? 使用PL/SQL Developer工具創(chuàng)建表
? 使用PL/SQL Developer工具創(chuàng)建序列
? 使用PL/SQL Developer工具插入測(cè)試數(shù)據(jù)
需求說明
前兩個(gè)實(shí)訓(xùn)任務(wù)都是通過SQL Plus工具實(shí)現(xiàn)的，現(xiàn)在要求把聯(lián)系人表刪掉并使用PL/SQL Developer工具實(shí)現(xiàn)前兩個(gè)實(shí)訓(xùn)任務(wù)
4：JDBC連接Oracle數(shù)據(jù)庫
訓(xùn)練技能點(diǎn)
? JDBC連接Oracle數(shù)據(jù)庫
? JDBC查詢數(shù)據(jù)
需求說明
使用JDBC連接聯(lián)系人表，并查詢表中的所有數(shù)據(jù)顯示到j(luò)sp頁面上
實(shí)現(xiàn)步驟
（1） 創(chuàng)建DAO類，在該類中實(shí)現(xiàn)兩個(gè)方法：連接數(shù)據(jù)庫和查詢數(shù)據(jù)
（2） 在JSP頁面中調(diào)用DAO類把查詢到的數(shù)據(jù)顯示到頁面上
（3） 顯示頁面時(shí)使用分頁方式
 
 
鞏固練習(xí)
 
一、選擇題
1. 在使用Oracle數(shù)據(jù)庫時(shí)至少需要啟動(dòng)（）服務(wù)。
A. OracleOraDb11g_home1TNSListener		
B. OracleServiceSID 	
C. OracleDBConsoleSID
D. OracleJobSchedulerSID
2. 下列（）不是Oracle默認(rèn)的用戶。
A. system		
B. sys
C. scott	
D. sa
3. 關(guān)于jdbc:oracle:thin:@localhost:1521:orcl說法正確的是（）。
A. 1521是Oracle的默認(rèn)端口號(hào)
B. orcl是表名
C. localhost表示應(yīng)用程序所在的機(jī)器
4. 下列（）用于支持OEM服務(wù)。
A. OracleOraDb11g_home1TNSListener		
B. OracleServiceSID 	
C. OracleDBConsoleSID
D. OracleJobSchedulerSID
5. 下列關(guān)于序列的說法正確的是（）。
A. 序列一旦創(chuàng)建，就可以立即使用CURRVAL列		
B. 在引用序列的CURRVAL列前，必須引用過一次NEXTVAL列
C. 可以修改序列中的起始值
D. 序列的初始值可以從0開始
 
二、上機(jī)練習(xí)
在Oracle中使用SQL Plus創(chuàng)建新聞表news，表結(jié)構(gòu)如下所示。
表1-3-1 新聞表
列名
數(shù)據(jù)類型
約束
id
number
主鍵，自增
title
varchar2
非空
content
varchar2
 
author
varchar2
 
創(chuàng)建好后在PL/SQL Developer工具中插入測(cè)試數(shù)據(jù)。
 
 
Oracle擴(kuò)展進(jìn)階
 
 
Sqlplus常用命令
 
1． 顯示當(dāng)前用戶
show user 
2． 建立連接
connect   scott/tiger
connect   sys/admin as sysdba
3．?dāng)嚅_連接
disconnect
4．退出sqlplus
exit 
quit
5．執(zhí)行sql 文件
Start 或者@d:/aa.sql
6．編輯上一次的SQL語句
ed
7．保存輸入的代碼
Spool  
spool d:/b.sql  
spool off
8． 設(shè)置行大小(linesize) 默認(rèn)80字符
set linesize 150
9． 顯示行大小
show linesize
10． 設(shè)置頁大小,顯示頁大小
set pagesize 500
show pagesize
11． select * from scott.emp 
12． desc user_tables
13．select table_name from user_tables;
16．SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = ‘T1’