ORACLE講解內(nèi)容.docx

第一部分：ORACLE 基礎(chǔ)一、oracle體系結(jié)構(gòu)要了解oracle體系結(jié)構(gòu)必須先了解兩個(gè)基本的概念: 數(shù)據(jù)庫和實(shí)例.1、數(shù)據(jù)庫：數(shù)據(jù)庫(database)是一個(gè)數(shù)據(jù)集合.oracle數(shù)據(jù)庫都將其數(shù)據(jù)存放在數(shù)據(jù)文件中. 在其內(nèi)部,構(gòu)數(shù)據(jù)庫結(jié)數(shù)據(jù)對文件的邏輯映射, 使不同的數(shù)據(jù)分開存儲, 這些邏輯劃分稱為表空間。 表空間表空間(tablespace)是數(shù)據(jù)庫的邏輯劃分, 每個(gè)數(shù)據(jù)庫至少有一個(gè)表空間,叫做系統(tǒng)表空間(system 表空間). 一個(gè)表空間只能屬于一個(gè)數(shù)據(jù)庫。每個(gè)表空間由同一個(gè)磁盤上的一個(gè)或多個(gè)文件組成, 這些文件稱為數(shù)據(jù)文件。創(chuàng)建表空間的語句：create tablespace oraro datafile D:OracleDataoraro.dat size 512M autoextend on;增加數(shù)據(jù)文件：alter tablespase oraro add datafile D:OracleDataoraro1.dat size 512M; 刪除表空間：drop tablespace oraro including contents and datafiles; 數(shù)據(jù)文件每個(gè)表空間由同一個(gè)磁盤上的一個(gè)或多個(gè)文件組成, 這些文件叫做數(shù)據(jù)文件(datafile),數(shù)據(jù)文件只能屬于一個(gè)表空間. 數(shù)據(jù)文件創(chuàng)建后可以改變大小. 創(chuàng)建新的表空間需要?jiǎng)?chuàng)建新的數(shù)據(jù)文件. 數(shù)據(jù)文件一旦加入到表空間中, 就不能從表空間中移走, 也不能與其他表空間發(fā)生聯(lián)系.數(shù)據(jù)庫必須的三類文件是 data file, control file, redolog file. 其他文件 prameter file, password file, archived log files并不是數(shù)據(jù)庫必須的, 他們只是輔助數(shù)據(jù)庫的.查看數(shù)據(jù)庫的物理文件組成: 1)查看數(shù)據(jù)文件: SQL select * from v$datafile; 2)查看控制文件: SQL select * from v$controlfile; 3)查看日志文件: SQL select * from v$logfile; 控制文件控制文件是一個(gè)很小的二進(jìn)制文件，用于記錄數(shù)據(jù)庫的物理結(jié)構(gòu)。一個(gè)控制文件只屬于一個(gè)數(shù)據(jù)庫。創(chuàng)建數(shù)據(jù)庫時(shí)，創(chuàng)建控制文件。當(dāng)數(shù)據(jù)庫的物理結(jié)構(gòu)改變的時(shí)候，Oracle會更新控制文件。用戶不能編輯控制文件，控制文件的修改由Oracle完成。 數(shù)據(jù)庫的啟動和正常運(yùn)行都離不開控制文件。啟動數(shù)據(jù)庫時(shí)，Oracle從初始化參數(shù)文件中獲得控制文件的名字及位置，打開控制文件，然后從控制文件中讀取數(shù)據(jù)文件和聯(lián)機(jī)日志文件的信息，最后打開數(shù)據(jù)庫。數(shù)據(jù)庫運(yùn)行時(shí)，Oracle會修改控制文件，所以，一旦控制文件損壞，數(shù)據(jù)庫將不能正常運(yùn)行。 控制文件記錄的數(shù)據(jù)庫信息如下： 數(shù)據(jù)庫名稱（Database Name） 創(chuàng)建數(shù)據(jù)庫的時(shí)間戳 數(shù)據(jù)文件的名字及位置 聯(lián)機(jī)日志文件的名字及位置 表空間信息 日志歷史記錄（Log History） 歸檔日志的信息 備份信息 當(dāng)前的日志序列號（Log Sequence Number） 校驗(yàn)點(diǎn)信息（Checkpoint） 控制文件的大小由下面的幾個(gè)參數(shù)決定： MAXLOGFILES MAXLOGMEMBERS MAXLOGHISTORY MAXDATAFILES MAXINSTANCES 當(dāng)增加、重命名、刪除一個(gè)數(shù)據(jù)文件或者一個(gè)聯(lián)機(jī)日志文件時(shí)，Oracle服務(wù)器進(jìn)程（Server Process）會立即更新控制文件以反映數(shù)據(jù)庫結(jié)構(gòu)的這種變化。所以，Oracle總是告誡我們，在數(shù)據(jù)庫的結(jié)構(gòu)發(fā)生變化后，要備份控制文件。日志寫進(jìn)程LGWR負(fù)責(zé)把當(dāng)前日志序列號記錄到控制文件中。校驗(yàn)點(diǎn)進(jìn)程CKPT負(fù)責(zé)把校驗(yàn)點(diǎn)的信息記錄到控制文件中。歸檔進(jìn)程負(fù)責(zé)把歸檔日志的信息記錄到控制文件中。 初始化參數(shù)CONTROL_FILES的值記錄控制文件的位置。通常，DBA應(yīng)該鏡像控制文件，把每個(gè)控制文件分布到不同的物理磁盤，發(fā)生災(zāi)難時(shí)，即使其中一個(gè)控制文件損壞，數(shù)據(jù)不會丟失，也不會使整個(gè)數(shù)據(jù)庫陷于癱瘓。2、實(shí)例通俗的講實(shí)例就是操作oracle數(shù)據(jù)庫的一種手段.數(shù)據(jù)庫實(shí)例也稱作服務(wù)器, 是用來訪問數(shù)據(jù)庫文件集的存儲結(jié)構(gòu)及后臺進(jìn)程的集合.一個(gè)數(shù)據(jù)庫可以被多個(gè)實(shí)例訪問(稱為真正的應(yīng)用群集選項(xiàng)).決定實(shí)例的大小及組成的各種參數(shù)或者存儲在名稱init.ora的初始化文件中, 或者隱藏在數(shù)據(jù)庫內(nèi)部的服務(wù)器參數(shù)文件中. 通過spfile引用該文件, spfile存儲在spfile.ora文件中.實(shí)例啟動時(shí)讀取初始化文件, 數(shù)據(jù)庫系統(tǒng)管理員可以修改該文件, 對初始化文件的修改只有在下次啟動時(shí)才有效.3、監(jiān)聽程序監(jiān)聽器是Oracle基于服務(wù)器端的一種網(wǎng)絡(luò)服務(wù)，主要用于監(jiān)聽客戶端向數(shù)據(jù)庫服務(wù)器端提出的連接請求。既然是基于服務(wù)器端的服務(wù)，那么它也只存在于數(shù)據(jù)庫服務(wù)器端，進(jìn)行監(jiān)聽器的設(shè)置也是在數(shù)據(jù)庫服務(wù)器端完成的。二、PL/SQLPL/SQL也是一種程序語言，叫做過程化SQL語言（Procedural Language/SQL）。PL/SQL是Oracle數(shù)據(jù)庫對SQL語句的擴(kuò)展。在普通SQL語句的使用上增加了編程語言的特點(diǎn)，所以PL/SQL就是把數(shù)據(jù)操作和查詢語句組織在PL/SQL代碼的過程性單元中，通過邏輯判斷、循環(huán)等操作實(shí)現(xiàn)復(fù)雜的功能或者計(jì)算的程序語言。1 PL/SQL基本概念1.1 PL/SQL的作用 使用PL/SQL可以編寫具有很多高級功能的程序，雖然通過多個(gè)SQL語句可能也能實(shí)現(xiàn)同樣的功能，但是相比而言，PL/SQL具有更為明顯的一些優(yōu)點(diǎn)： 能夠使一組SQL語句的功能更具模塊化程序特點(diǎn)； 采用了過程性語言控制程序的結(jié)構(gòu)； 可以對程序中的錯(cuò)誤進(jìn)行自動處理，使程序能夠在遇到錯(cuò)誤的時(shí)候不會被中斷； 具有較好的可移植性，可以移植到另一個(gè)Oracle數(shù)據(jù)庫中； 集成在數(shù)據(jù)庫中，調(diào)用更快； 減少了網(wǎng)絡(luò)的交互，有助于提高程序性能。 通過多條SQL語句實(shí)現(xiàn)功能時(shí)，每條語句都需要在客戶端和服務(wù)端傳遞，而且每條語句的執(zhí)行結(jié)果也需要在網(wǎng)絡(luò)中進(jìn)行交互，占用了大量的網(wǎng)絡(luò)帶寬，消耗了大量網(wǎng)絡(luò)傳遞的時(shí)間，而在網(wǎng)絡(luò)中傳輸?shù)哪切┙Y(jié)果，往往都是中間結(jié)果，而不是我們所關(guān)心的。 而使用PL/SQL程序是因?yàn)槌绦虼a存儲在數(shù)據(jù)庫中，程序的分析和執(zhí)行完全在數(shù)據(jù)庫內(nèi)部進(jìn)行，用戶所需要做的就是在客戶端發(fā)出調(diào)用PL/SQL的執(zhí)行命令，數(shù)據(jù)庫接收到執(zhí)行命令后，在數(shù)據(jù)庫內(nèi)部完成整個(gè)PL/SQL程序的執(zhí)行，并將最終的執(zhí)行結(jié)果返饋給用戶。在整個(gè)過程中網(wǎng)絡(luò)里只傳輸了很少的數(shù)據(jù)，減少了網(wǎng)絡(luò)傳輸占用的時(shí)間，所以整體程序的執(zhí)行性能會有明顯的提高。 1.2 PL/SQL分類1) DDL語句(數(shù)據(jù)定義語言) Data Define Language CREATE TABLE 創(chuàng)建表 ALTER TABLE 更改表 DROP TABLE 刪除表 TRUNCATE TABLE 清空表 RENAME table1 TO table2 改表名 建立表 create table class(-班級表 classid number(2) primary key, cname varchar2(20) not null); create table student( -學(xué)生表 sid number(4) primary key, -學(xué)號 name varchar2(10) not null, -姓名 sex char(2) check (sex in (M,F),-性別 birthday date,-生日 classid number(2) references class(classid) -班級 );外鍵引用的列一定是主鍵或有unique約束的列 alter table student add (age number(3);2) DML語句(數(shù)據(jù)操作語言) Data Manupilate Language UPDATE 更新存儲在表中的數(shù)據(jù) DELETE 刪除行 SELECT 查詢表 INSERT 插入數(shù)據(jù) 特點(diǎn): 對數(shù)據(jù)起作用的這些語句的修改是在內(nèi)存中發(fā)生的 要想改動存入庫中必須要commit語句 delete from student： 只刪除數(shù)據(jù)，速度慢，數(shù)據(jù)在commit之前可以恢復(fù) truncate table student; 刪除 的時(shí)候不檢查表中現(xiàn)有記錄，速度快，數(shù)據(jù)不能恢復(fù)，可以自動釋放所清空記錄之前所占的表空間3) TCL(事務(wù)控制語句) Transaction Control LanguageCOMMIT 提交 修改保存到數(shù)據(jù)庫中 ROLLBACK 回滾 取消內(nèi)存中的改動 SAVEPOINT 保存點(diǎn) 分解事務(wù)的 把事務(wù)變小4) DCL(數(shù)據(jù)控制語句)Data Control Language GRANT 賦予權(quán)限-創(chuàng)建用戶create user zhangsan identified by oraro;-授權(quán)grant create table,create tablespace to zhangsan;grant select,delete,insert on tb_bulletin to zhangsan;grant connect to zhangsan;-刪除drop user zhangsan; REVOKE 收回權(quán)限r(nóng)evoke select on tb_bulletin from zhangsan;1.3 PL/SQL程序的基本結(jié)構(gòu) PL/SQL塊由四個(gè)基本部分組成：聲明、執(zhí)行體開始、異常處理、執(zhí)行體結(jié)束。 下面是四個(gè)部分的基本結(jié)構(gòu)： DECLARE可選部分 變量、常量、游標(biāo)、用戶定義異常的聲明 BEGIN必要部分 SQL語句和PL/SQL語句構(gòu)成的執(zhí)行程序 EXCEPTION可選部分 程序出現(xiàn)異常時(shí)，捕捉異常并處理異常 END；必須部分 在數(shù)據(jù)庫執(zhí)行PL/SQL程序時(shí)，PL/SQL語句和SQL語句是分別進(jìn)行解析和執(zhí)行的。PL/SQL塊被數(shù)據(jù)庫內(nèi)部的PL/SQL引擎提取，將SQL語句取出送給Oracle的SQL引擎處理，兩種語句分別在兩種引擎中分析處理，在數(shù)據(jù)庫內(nèi)部完成數(shù)據(jù)交互、處理過程。declare v_goods_type varchar2(100):=杯具;-商品類別 v_row int;-數(shù)據(jù)行數(shù)begin select count(*) into v_row from tb_goods_type where type_name=v_goods_type; if v_row0 then dbms_output.put_line(商品類型已存在); else insert into tb_goods_type (type_id, type_name) values (seq_goods_id.nextval, v_goods_type); commit; dbms_output.put_line(商品類型添加成功); end if;end; 2 PL/SQL的變量就像其他的程序語言一樣，變量是在程序中出現(xiàn)最頻繁的名詞，在PL/SQL中的學(xué)習(xí)中首先需要了解變量的一些基本概念和使用方法。 PL/SQL程序包括了四個(gè)部分，在四個(gè)部分中，聲明部分主要用來聲明變量并且初始化變量，在執(zhí)行部分可以為變量賦新值，或者在表達(dá)式中引用變量的值，在異常處理部分同樣可以按執(zhí)行部分的方法使用變量。另外，在PL/SQL程序使用時(shí)可以通過參數(shù)變量把值傳遞到PL/SQL塊中，也可以通過輸出變量或者參數(shù)變量將值傳出PL/SQL塊。 在定義變量、常量標(biāo)識符時(shí)需要注意下面的一些基本規(guī)則： 1) 定義的標(biāo)識符名稱應(yīng)該遵循命名規(guī)則，在后面將會提到主要的命名規(guī)則； 2) 在聲明常量和變量的時(shí)候可以為其設(shè)置初始化值，也可以強(qiáng)制設(shè)置not null； 3) 可以使用賦值運(yùn)算符（：=）或DEFAULT保留字來初始化標(biāo)識符，為標(biāo)識符賦初始值； 4) 在聲明標(biāo)識符時(shí)，每行只能聲明一個(gè)標(biāo)識符。 在PL/SQL中主要使用下面三種類型的變量（或者常量）： 1) 簡單變量； 2) 復(fù)合（組合）變量； 3) 外部變量。 三種變量分別用于存放不同特性的數(shù)據(jù)。 3 PL/SQL的基本語法在寫PL/SQL語句時(shí)，必須遵循一些基本的語法，下面是PL/SQL程序代碼的基本語法要求： 1.語句可以寫在多行，就像SQL語句一樣； 2.各個(gè)關(guān)鍵字、字段名稱等等，通過空格分隔； 3.每條語句必須以分號結(jié)束，包括PL/SQL結(jié)束部分的END關(guān)鍵字后面也需要分號； 4.標(biāo)識符需要遵循相應(yīng)的命名規(guī)定； （1）名稱最多可以包含30個(gè)字符； （2）不能直接使用保留字，如果需要，需要使用雙引號括起來； （3）第一個(gè)字符必須以字母開始； （4）不要用數(shù)據(jù)庫的表或者科學(xué)計(jì)數(shù)法表示； 還有一些語法相關(guān)的規(guī)則： 1.在PL/SQL程序中出現(xiàn)的字符值和日期值必須用單引號括起； 2.數(shù)字值可以使用簡單數(shù)字或者科學(xué)計(jì)數(shù)法表示； 3.在程序中最好養(yǎng)成添加注釋的習(xí)慣，使用注釋可以使程序更清晰，使開發(fā)者或者其他人員能夠很快的理解程序的含義和思路。在程序中添加注釋可以采用： （1）/*和*/之間的多行注釋； （2）以-開始的單行注釋。三、約束在數(shù)據(jù)庫中使用約束(constraints)是為了在該數(shù)據(jù)庫中實(shí)施所謂的業(yè)務(wù)規(guī)則其實(shí)就是防止非法信息進(jìn)入數(shù)據(jù)庫,滿足管理員和應(yīng)用開發(fā)人員所定義的規(guī)則集.ORACLE 使用完整性約束(integrity constraints)防止不合法的數(shù)據(jù)寫入數(shù)據(jù)庫,管理員和開發(fā)人員可以定義完整性規(guī)則,增強(qiáng)商業(yè)規(guī)則,限制數(shù)據(jù)表中的數(shù)據(jù).如果一個(gè)DML語句執(zhí)行的任何結(jié)果破壞了完整性約束,ORACLE就會回滾語句,返回錯(cuò)誤信息.約束是通過使用CREATE TABLE或ALTER TABLE語句生成的.(建立表時(shí)或者表建立后修改都可)如果相關(guān)的約束定義在單列上,可以在列這一級指定約束的定義;多列約束必須定義在數(shù)據(jù)表級,相關(guān)的列要在括號中指定,用逗號分隔.如果沒有為約束提供一個(gè)名字,那么ORACLE會分配一個(gè)系統(tǒng)生成的唯一名字,以SYS_開頭,你可以使用關(guān)鍵字 CONSTRAINTS后面跟隨相關(guān)的約束名字來為約束指定名字.ORACLE支持五種類型的完整性約束NOT NULL (非空)-防止NULL值進(jìn)入指定的列,在單列基礎(chǔ)上定義,默認(rèn)情況下,ORACLE允許在任何列中有NULL值.CHECK (檢查)-檢查在約束中指定的條件是否得到了滿足.UNIQUE (唯一)-保證在指定的列中沒有重復(fù)值.在該表中每一個(gè)值或者每一組值都將是唯一的.PRIMARY KEY (主鍵)-用來唯一的標(biāo)識出表的每一行,并且防止出現(xiàn)NULL值,一個(gè)表只能有一個(gè)主鍵約束.POREIGN KEY (外部鍵)-通過使用公共列在表之間建立一種父子(parent-child)關(guān)系,在表上定義的外部鍵可以指向主鍵或者其他表的唯一鍵.約束定義存儲在數(shù)據(jù)字典中,查詢USER_CONSTRAINTS可以獲得相關(guān)信息.定義約束CREATE TABLE schema.table(column datatype DEFAULT exprcolumn_constraint,.table_constraint,.);e.g.CREATE TABLE employees(employee_id NUMBER(6),first_name VARCHAR2(20),.job_id VARCHAR2(10) NOT NULL,CONSTRAINTS emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID);列級的約束定義column CONSTRAINT constraint_name constraint_type,表級約束的定義column,.CONSTRAINT constraint_name constraint_type (column,.)NOT NULL約束只能定義在列級,不能定義在表級.e.g.CREATE TABLE employees(employee_id NUMBER(6),last_name VARCHAR2(25) NOT NULL, /沒有指定名字,將用系統(tǒng)SYS_命名salary NUMBER(8,2),commission_pct NUMBER(2,2),hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL); /用戶定義了名字UNIQUE約束用來保護(hù)一個(gè)表中的一個(gè)或者多個(gè)列沒有任何兩行在收到保護(hù)的列中具有重復(fù)的數(shù)據(jù).ORACLE在唯一鍵列上自動生成一個(gè)唯一索引以實(shí)現(xiàn)唯一性e.g.CREATE TABLE employees(employee_id NUMBER(6),last_name VARCHAR2(25) NOT NULL,salary NUMBER(8,2),commission_pct NUMBER(2,2),hire_date DATE NOT NULL,CONSTRAINT emp_email_uk UNIQUE(email);PRIMARY KEY約束唯一鍵的所有特征都適用于主鍵約束,只是在主鍵列中不允許有NULL值.一個(gè)表只能有一個(gè)主鍵.e.g.CREATE TABLE departments(department_id NUMBER(4),department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL,manager_id NUMBER(6),location_id NUMBER(4),CONSTRAINT dept_id_pk PRIMARY KEY(department_id);FOREIGN KEY 約束用來保護(hù)一個(gè)表中的一個(gè)或者多個(gè)列,它會通過一個(gè)主鍵主鍵或者唯一鍵保證對于每個(gè)非NULL值在數(shù)據(jù)庫的其他地方都有一個(gè)數(shù)據(jù)可用.這個(gè)外部鍵就是在生成此約束的表(子表)中的一個(gè)或多個(gè)列,在父級表和子表中,相關(guān)列的數(shù)據(jù)類型必須匹配.外部鍵列和引用鍵(reference key)列可以位于相同的表中(自引用完整性約束).e.g.CREATE TABLE employees(employee_id NUMBER(6),last_name VARCHAR2(25) NOT NULL,salary NUMBER(8,2),commission_pct NUMBER(2,2),hire_date DATE NOT NULL,deparment_id NUMBER(4),CONSTRAINT emp_dept_fk FOREIGN KEY(department_id) REFERENCES departments(department_id),CONSTRAINT emp_email_uk UNIQUE(email);上例中是在表級定義外部鍵約束,如果在列級定義,不同的是:CREATE TABLE employees(.,department_id NUMBER(4) CONSTRAINT emp_deptid_fk REFERENCES departments(department_id),.);/沒有關(guān)鍵字FOREIGN KEYFOREIGN KEY約束還有兩個(gè)關(guān)鍵字是ON DELETE CASCADE -當(dāng)刪除所引用的父表記錄時(shí),刪除子表中相關(guān)的記錄ON DELETE SET NULL-與上面不同的是刪除時(shí),轉(zhuǎn)換子表中相關(guān)記錄為NULL值默認(rèn)情況下,如果沒有指定以上兩個(gè)中任一,則父表中被引用的記錄將不能被刪除.CHECK 約束CONSTRAINT CHECK ( )這里CHECK子句中的CONDITION應(yīng)該求值為一個(gè)布爾值結(jié)果,并且可以引用相同行中其他列的值;不能包含子查詢,序列,環(huán)境函數(shù)(SYSDATE, UID,USER,USERENV)和偽列(ROWNUM,LEVEL,CURRVAL,NEXTVAL),一個(gè)列上可以定義多個(gè)CHECK約束,如果所定義的條件為FALSE,則語句將回滾.CREATE TABLE employees(.,salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary0),.);添加約束ALTER TABLE employeesADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);刪除約束ALTER TABLE employeesDROP CONSTRAINT emp_manager_fk;ALTER TABLE departmentsDROP PRIMARY KEY CASCADE; /由于departments被employees.department_id引用了對于NOT NULL約束,用ALTER TABLE MODIFY子句來刪除ALTER TABLE employees MODIFY last_name NULL;關(guān)閉約束ALTER TABLE employeesDISABLE CONSTRAINT emp_emp_id_pk CASCADE; /如果沒有被引用則不需CASCADE關(guān)鍵字當(dāng)你生成一個(gè)約束時(shí),約束自動打開(除非你指定了DISABLE子句0,當(dāng)用DISABLE關(guān)閉UNIQUE或者PRIMARY KEY約束時(shí),ORACLE會自動刪除相關(guān)的唯一索引,再次打開時(shí),ORACLE又會自動建立的.打開約束ALTER TABLE employeesENABLE CONSTRAINT emp_emp_id_pk; /注意,打開一個(gè)先前關(guān)閉的被引用的主鍵約束,并不能自動打開相關(guān)的外部鍵約束可以從USER_CONSTRAINTS表和USER_CONS_COLUMNS視圖中查詢約束的信息SELECT constraint_name,constraint_type,search_conditionFROM user_constraintsWHERE table_name=EMPLOYEES;四、序列序列是一數(shù)據(jù)庫對象，利用它可生成唯一的整數(shù)。一般使用序列自動地生成主鍵值。一個(gè)序列的值是由特別的Oracle程序自動生成，因而序列避免了在運(yùn)用層實(shí)現(xiàn)序列而引起的性能瓶頸。Oracle序列允許同時(shí)生成多個(gè)序列號，而每一個(gè)序列號是唯一的。 當(dāng)一個(gè)序列號生成時(shí)，序列是遞增，獨(dú)立于事務(wù)的提交或回滾。容許設(shè)計(jì)缺省序列，不需指定任何子句。該序列為上升序列，由1開始，增量為1，沒有上限。 1） 建立序列命令CREATE SEQUENCE user.sequence_name increment by n start with n maxvalue n | nomaxvalue minvalue n | nominvalue; INCREMENT BY： 指定序列號之間的間隔，該值可為正的或負(fù)的整數(shù)，但不可為0。序列為升序。忽略該子句時(shí)，缺省值為1。 START WITH：指定生成的第一個(gè)序列號。在升序時(shí)，序列可從比最小值大的值開始，缺省值為序列的最小值。對于降序，序列可由比最大值小的值開始，缺省值為序列的最大值。 MAXVALUE：指定序列可生成的最大值。 NOMAXVALUE：為升序指定最大值為1027，為降序指定最大值為-1。 MINVALUE：指定序列的最小值。 NOMINVALUE：為升序指定最小值為1。為降序指定最小值為-1026。2） 更改序列命令A(yù)LTER SEQUENCE user.sequence_nameINCREMENT BY nMAXVALUE n| NOMAXVALUE MINVALUE n | NOMINVALUE；修改序列可以：修改未來序列值的增量。設(shè)置或撤消最小值或最大值。改變緩沖序列的數(shù)目。指定序列號是否是有序。注意：1，第一次NEXTVAL返回的是初始值2，可以alter除start至以外的所有sequence參數(shù)。如果想要改變start值，必須 drop sequence 再 re-create .3） 刪除序列命令DROP SEQUENCE user.sequence_name；用于從數(shù)據(jù)庫中刪除一序列。4）牛刀小試4.1）創(chuàng)建一個(gè)序列號的語句：- Create sequencecreate sequence NCME_QUESTION_SEQminvalue 1maxvalue 999999999999start with 1increment by 1cache 20;/4.2）SQL中取序列號的用法：SELECT NCME_QUESTION_SEQ.nextval FROM dualSELECT NCME_QUESTION_SEQ.CURRVAL FROM dualSELECT NCME_QUESTION_SEQ.nextval FROM dual SELECT NCME_QUESTION_SEQ.CURRVAL FROM dual注意：在使用序列的時(shí)候，有時(shí)需要有用戶名，就像這樣：insert into system.CONSERVATOR(CONSERVATORNAME,CONPASS,CONTRUENAME,CONSEX,CONID)values(JG,123456,000,0, system.CONID.nextval);第二部分：查詢、函數(shù)、游標(biāo)一、 查詢?yōu)槭裁词褂米釉?？查詢成績高于平均分的學(xué)生的信息l 第一種方式declare v_avg_score number;begin select avg(score) into v_avg_score from tb_mark; select stuname from tb_student stu inner join tb_mark mk on stu.stuno=mk.stuno where scorev_avg_score;end;l 第二種方式select stuname from tb_student stu inner join tb_mark mk on stu.stuno=mk.stuno where score(select avg(score) from tb_mark);子查詢在WHERE語句中的一般用法：SELECT FROM 表1 WHERE 字段1 (子查詢)外面的查詢稱為父查詢，括號中嵌入的查詢稱為子查詢 UPDATE、INSERT、DELETE一起使用，語法類似于SELECT語句 將子查詢和比較運(yùn)算符聯(lián)合使用，必須保證子查詢返回的值不能多于一個(gè) l Rownum,rowidRownum 查詢結(jié)果中的行號， ROWID是數(shù)據(jù)的物理地址二、 函數(shù)函數(shù)是數(shù)據(jù)庫中常用對象之一，與其他編程語言的函數(shù)一樣，ORACLE中的函數(shù)也必須返回一個(gè)值，這也是函數(shù)區(qū)別于存儲過程的重要特征(函數(shù)類似于JAVA中的方法)。簡介：1、 函數(shù)與功能的劃分：最大化函數(shù)復(fù)用；2、 函數(shù)的參數(shù)：函數(shù)可以沒有傳入?yún)?shù)，但是如果有的話必須明確數(shù)據(jù)類型；3、 函數(shù)的返回值：必須有返回值，必須在結(jié)尾處用return返回，類型可以是字符串，字符型，數(shù)組或?qū)ο箢愋?；?chuàng)建函數(shù)：1、 創(chuàng)建函數(shù)：create or replace function get_hello_msgreturn varchar2 asbegin return hello world;end get_hello_msg;2、 在數(shù)據(jù)字典中查看函數(shù)的信息：select object_name,object_type,status from user_objects where lower(object_name)=get_hello_msg3、 查看函數(shù)的返回值：declare msg varchar2(20);beginmsg:=get_hello_msg;dbms_output.put_line(msg);end;注：ORACLE中函數(shù)名后的括號在無參數(shù)情況下可以省略；帶參數(shù)的函數(shù)：-*- 版權(quán)所有: (C)201, 南京橙紅信息科技有限公司1。- 數(shù)據(jù)庫版本: Oracle 10G R2- 內(nèi)容摘要: 判斷指定表指定列的值是否存在- 創(chuàng) 建 人: 里斯- 創(chuàng)建日期: 2011.9.5- 備注:-*create or replace function func_Exists(v_table_name varchar2,v_column_name varchar2,v_value varchar2) return booleanas v_sql varchar2(1000);-保存SQL命令 v_rows int;-行數(shù) v_exists boolean:=false;begin v_sql:=select count(*) r from | v_table_name | where | v_column_name | = | v_value | ; execute immediate v_sql into v_rows; if(v_rows0) then v_exists:=true; end if; return v_exists; exception when others then dbms_output.put_line(執(zhí)行SQL出錯(cuò)); dbms_output.put_line(v_sql);end;三、 游標(biāo)游標(biāo)可以增強(qiáng)SQL語句的功能 ，它可以對SQL語句的處理進(jìn)行顯式的控制，可以非常方便地底幫助我們從數(shù)據(jù)庫中提取多行數(shù)據(jù)，然后對每一行數(shù)據(jù)進(jìn)行單獨(dú)處理。游標(biāo)是一種PL/SQL的控制結(jié)構(gòu)，它可以命名一個(gè)工作區(qū)來存取該工作區(qū)的存儲信息。游標(biāo)分為顯式游標(biāo)和隱式游標(biāo)， PL/SQL會為所有的DML 句創(chuàng)建一個(gè)游標(biāo)，包含那些只返回一條語句的查詢。對于返回多條記錄的查詢語句，我們應(yīng)該顯式地聲明一個(gè)游標(biāo)來逐個(gè)處理這些數(shù)據(jù)。DECLAREv_ename scott.EMP.ENAME%TYPE;-定義v_name為列ename同一種類型v_salary scott.EMP.sal%TYPE; -定義v_name為列sal同一種類型CURSOR c_emp IS SELECT ename,sal FROM scott.emp;BEGIN OPEN c_emp; LOOP FETCH c_emp INTO v_ename,v_salary; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(Salary of Employee | v_ename | is | v_salary);end loop;END;關(guān)于SQL編程中的特殊變量類型（type和rowtype）declare v_row scott.emp%rowtype;begin select empno, ename, job, mgr, hiredate,sal, comm, deptno into v_row from scott.emp where empno=7521; dbms_output.put_line(v_row.ename);end;第三部分 高級查詢、存儲過程Case when 用法select gi.goods_name,ogi.price,ogi.quantity,case gi.status when 0 then 未處理 when 1 then 缺貨 when 2 then 已發(fā)貨 when 3 then 收貨 when 4 then 退貨 end as st,order_time,order_accountfrom tb_order_goods_info ogi,tb_goods_info gi,tb_order_info oiwhere ogi.goods_id=gi.goods_id and oi.order_id=ogi.order_idand customer_id=11、復(fù)雜查詢班級表：tb_classClass_idNumber(4)班級ID主鍵Class_nameVarchar2(30)班級名稱NOT NULLGrade_tpye_codeChar(1)歸屬年級編碼NOT NULL 約定用1-6年級學(xué)生表：tb_studentStudent_idNumber(8)學(xué)號主鍵Student_nameVarchar2(50)姓名NOT_NULLClass_idNumber(4)歸屬班級NOT_NULLSex Char(1)性別NOT_NULL 0：男 1：女科目表 tb_subjectSubject_idNumber(8)科目ID主鍵 如：1000100Subject_nameVarchar2(50)科目名稱NOT_NULL,語文，數(shù)學(xué)Extend_tagChar(1)是否必選NOT_NULL 0：必選 1：否考試結(jié)果信息表 t_scoreStudent_idNumber(8)學(xué)號NOT_NULLSubject_idNumber(8)科目IDNOT_NULLScoreNumber(5,2)分?jǐn)?shù)NOT_NULLExam_noNumber(4)考試批次NOT_NULLRelease_timedate發(fā)布時(shí)間NOT_NULL注：每個(gè)批次的考試，都是按各年級統(tǒng)一參加考試，各年級必選科目固定且相同問題1：假如在t_score表中存在重復(fù)的記錄，請用一條SQL語句刪除重復(fù)的記錄。第一步：查詢重復(fù)的記錄select subject_id, student_id, score from t_scoregroup by subject_id, student_id, score having count(subject_id)1第二步：通過rowid列找出兩列中的一列select max(rowid) id, subject_id, student_id, score from t_scoregroup by subject id, student_id, score having count(subject_id)1第三步：根據(jù)rowid刪除重復(fù)的列delete from t_score where rowid in(select max(rowid) id from t_scoregroup by subject_id, student_id, scorehaving count(subject_id)1)2：寫一個(gè)語句刪除t_score非必選科目的記錄delete from t_socre where exists(select 1 from t_score sc,t_subject sb where sc.subject_id=sb.subject_id and Extend_tag=0);3：找出在相同批次考試中，相同年級，相同科目，成績相同的記錄select grade_tpye_code,subject_name,score from t_socre sc,t_student st,t_subject sb,t_class cswhere cs.class_id=st.class_id and st.student_id=sc.student_id and sb.subjecct_id=st.sbubject_idgroup by grade_tpye_code,subject_name,score,student_name,exam_nohaving count(grade_tpye_code)14：統(tǒng)計(jì)以下信息；每個(gè)必選科在各考試批次中的年級最高分，年級平均分，各班級最高分-年級最高分平均分select Grade_tpye_code,max(score) grade_max_score,avg(score) grade_avg_score from t_score sc inner join t_student st on sc.student_id=st.student_id inner join t_class cs.class_id=t_student.class_idgroup by cs.Grade_tpye_code,exam_no -班級最高分平均分select cs.class_name,max(score) class_max_score from t_score sc inner join t_student st on sc.student_id=st.student_id inner join t_class cs.class_id=t_student.class_idgroup by cs.class_id,cs.class_name,exam2、存儲過程存儲過程是PL/SQL語句的集合，類似于方法，存儲過程不必返回值，但可以有參數(shù)；存儲過程有三種參數(shù)：輸入?yún)?shù) 輸出參數(shù) 輸入輸出參數(shù)存儲過程的優(yōu)點(diǎn)： 1、 提高數(shù)據(jù)庫執(zhí)行效率在編程語言中，使用SQL接口更新數(shù)據(jù)就意味著要頻繁的連接數(shù)據(jù)庫，但連接數(shù)據(jù)庫是一個(gè)很耗時(shí)且消耗資源的的操作，這樣一來對于數(shù)據(jù)庫的性能就完全得不到保證，如果使用存儲過程來更新，則可大大的提高效率；2、 提高安全性存儲過程是以對象形式保存在數(shù)據(jù)庫中，這樣一來就可以對存儲過程分配權(quán)限來保證安全性；而且這樣一來就使操作從編程語言轉(zhuǎn)移到了數(shù)據(jù)庫，只要數(shù)據(jù)庫不被破壞，那么這些操作就能夠保存下來；3、 可復(fù)用4、節(jié)省網(wǎng)絡(luò)流