關(guān)于Oracle課程小結(jié)(doc19頁(yè))

1、關(guān)于 Oracle 課程小結(jié) （doc 19 頁(yè)） Oracle課程小結(jié) 1. Dual Dual是Oracle中的一個(gè)實(shí)際存在的表，任何用戶(hù)均可讀取，常用在沒(méi)有目標(biāo) 表的 Select語(yǔ)句塊中 DUAL就是個(gè)一行一列的表，如果你往里執(zhí)行insert delete truncate操作，就會(huì) 導(dǎo)致很多程序出問(wèn)題 不要去執(zhí)行drop dual的操作，否則會(huì)使系統(tǒng)不能用，數(shù)據(jù)庫(kù)起不了 DUAL是屬于SYS schema的一個(gè)表，然后以PUBLIC SYNONYM的方式供其他 數(shù) 據(jù)庫(kù)USER使用 2. NU11 在Oracle中，值mill被看作一個(gè)未知值，任何包含NULL值的算術(shù)運(yùn)算都會(huì)得到 結(jié)

2、果NULL，。因此，下面的select語(yǔ)句返回null select 5+7+null+9 from dual; select null*9 from dual; 空字符串：orach把空字符串（“）當(dāng)作null，因此IengthA）的值是null，而 不是0。 一個(gè)null值與任何其它值比較，包括null fi,結(jié)果都是null，因此，對(duì)NULL的 != 、二、等操作的結(jié)果都是未知的，也就算說(shuō)，這些操作的結(jié)果仍 然 是 NULLo 由于 NULL 是未知，所以 NULL AND NULL NULL OR NULL NULL AND TRUE和NULL OR FALSE的值都是未知的，這些的結(jié)

3、果仍然是NULL。 但 NULL AND FALSE 和 NULL OR TRUE 不一樣 declare v_result boolean; v_resultl boolean; begin v_result:=null and false; v_resultl:=null or true; dbins_output.put(v_result=); dbins_output.put J ine(case when v_result then true else false end); .false (lbins_output.put(v_resiiltl=); dbins_output.pu

4、t_line(case when v_resultl then Irue1 else false end); true end; 3函數(shù) 函數(shù)是一種有零個(gè)或多個(gè)參數(shù)并且有一個(gè)返回值的程序。在SQL中O“cle內(nèi)建了一 系列函，也可自定義函數(shù) 數(shù)單行函數(shù)多行函數(shù)(組函數(shù)) Lower(c)-小寫(xiě)轉(zhuǎn)換 返回C小寫(xiě)字符 Concat(cl,c2) “連接字符串 cl,c2均為字符串，函數(shù)將c2連接到d的后面，如果cl為mill,將返回c2如果c2 為! 11111測(cè)返回cl,如果cl. c2都為nulh貝！ |返回mill,如果cl,c2其中之一為 數(shù) 字，或都為數(shù)字 ConcatflOAlO) C

5、oncat(I0,10) Substr( string, start position, I length ) 一取字串 參數(shù)l:string要處換的字符串 參數(shù)2:截取字符串的開(kāi)始位置 參數(shù)3:截取的字符串的長(zhǎng)度(而不是字符串的結(jié)束位置)，如果省略，則返回從 start_position開(kāi)始到string尾之間的字符串 Select SUBSTRfHello2) From Dual Instr(columnlexpresslon/stringlniljnl)查詢(xún)指定字符在字符串中的位置 參數(shù)1：被搜索的字符串或者是表達(dá)式 參數(shù)2:要査找的字符 參數(shù)3:從什么位置開(kāi)始查詢(xún) 參數(shù)4:這個(gè)字符出現(xiàn)

6、的位置比如說(shuō)；第一次出現(xiàn)的位置，第二次出現(xiàn)的位置。 如果此參數(shù)3為正，從左到右開(kāi)始檢索，如果此參數(shù)為負(fù)，從右到左檢索最后兩個(gè) 參數(shù)是默認(rèn)的是1和1 SELECT instrCAbcVd1) FROM dual;返回 0 SELECT instrCsyranFROM dual;-返回 0 SELECT INSTR(foWo W) FROM DUAL;-? Round( number, I decimalplaces )四舍五入函數(shù) 參數(shù)1:要處理的數(shù)值(數(shù)值表達(dá) 式) 得到123得 到123得到 123.5得到 123.46得到 123.456 得到. 123.46 參數(shù)2:四舍五入時(shí)取的小數(shù)的

7、位數(shù)，不填則返回整數(shù) select round(123456) from dual; select round(123.456, 0) from dual; select round(123.456,1) from dual; select round(123.456, 2) from dual; select round(123.4569 3) from dual; select round(-123.456, 2) from dual; Mod(number,divisor) 返回一個(gè)number除以divisor的余數(shù) 參數(shù)1:為被除數(shù)。 參數(shù)2:為除數(shù)。如果divisor為零,函數(shù)MO

8、D返回值為原來(lái)number MOD(3,2)等于 1 MOD(3,0)等于 3 MOD(2,3)等于 2 To_char(date, fforniat_moder) - H 期轉(zhuǎn)為字符 To_char(numbers TormaCmoder)-數(shù)字轉(zhuǎn)為字符 格化元素，用于顯示字祜形式的數(shù)字值:”9“表示一個(gè)數(shù)”打印一個(gè)小數(shù)點(diǎn)擰打 印一個(gè)千位指示 SELECT TO_CHAR(12345.529； 999,999.99 ) FROM DUAL;“12,345.53 Replace(text, search_string, replacement.string)-替換字符串 從孝符串(text)査

9、找一個(gè)文本表達(dá)式(search_string)，如果找到，用指定的置換串 (replacement_string)代替它 如果replacement_string為空，那么所有的search_string都被移除。 如果search_string為null,那么就返回原來(lái)的 SELECT REPLACE (】hello 1 1 ello 1 , 1 i 1 ) FROM DUAL; -hi To_number(, , THEN 語(yǔ)句； ELSIF v 條件 2 THEN 語(yǔ)句； ELSIF v 條件 3 THEN 語(yǔ)句； ELSE 語(yǔ)句; END IF; CASE v變量 WHEN v 值 1

10、 THEN 語(yǔ)句； WHEN v 值 2 THEN 語(yǔ)句； “ THEN 語(yǔ)句； Else 語(yǔ)句； End Case; CASE WHEN v邏輯表達(dá)式1 THEN語(yǔ)句; WHEN v邏輯表達(dá)式2 THEN語(yǔ)句; WHEN v邏輯表達(dá)式N THEN語(yǔ)句; Else 語(yǔ)句； Encl Case; 有三種形式的LOOP語(yǔ)句 LOOP WHILE-LOOP FOR-LOOP LOOP語(yǔ)句； EXIT EXIT-WHEN END LOOP; EXIT強(qiáng)制循環(huán)無(wú)條件完成立即退出循環(huán) EXIT WHEN V條件;EXIT語(yǔ)句時(shí)對(duì)WHEN子句中的條件進(jìn)行判斷如果判 定條 件為T(mén)RUE,則循環(huán)完成 WHILE

11、 v 條件 LOOP 語(yǔ)句； END LOOP; FOR v 計(jì)數(shù)器 IN Reverse lower.bound higher bound LOOP 語(yǔ)句； END LOOP; 5子程序 過(guò)程 用于執(zhí)行某項(xiàng)操作 函數(shù) 用于執(zhí)行某項(xiàng)操作并返回值 存儲(chǔ)過(guò)程語(yǔ)法 CREATE OR REPLACE PROCEDURE v 過(guò)程名（參數(shù) 1,參數(shù) N） ISIAS 局部聲明 BEGIN 可執(zhí)行語(yǔ)句； EXCEPTION 例外處理程序; ENDv過(guò)程名; 參數(shù)說(shuō)明 變量名INIOUTIIN OUT IN：輸入，接受值，默認(rèn)模式 OUT：輸出，將值返回給子程序的調(diào)用程序 IN OUT：輸入輸出，接受值并

12、返回已更新的值 對(duì)于IN模式的實(shí)參可以是常量或變母，但對(duì)于OUT和IN OUT模式的實(shí)參必 須是 變量。 Create Or Replace Procedure ProcGetName(StudName in out varchar2) is Begin StudName:=substr(StudName4nstr(StudName/ 明 2); End; / declare StudName varchar2(15):=f 明小明； begin ProcGetName(StudName); DBMS_OUTPUT.PUT_LINE(StudName); end; 函數(shù) 語(yǔ)法 CREATE O

13、R REPLACE FUNCTION v 函數(shù)名(參數(shù) 1,參數(shù) N) RETURN datatype IS 局部聲明 BEGIN 可執(zhí)行語(yǔ)句； EXCEPTION 例外處理程序 END v函數(shù)名：； 僅接受IN參數(shù) Create Or Replace Function GetItemScore(Stand_Ans varchar2,Custor_Ans varchar2) return int is LenCustor int:=length(Custor_Ans); begin if LenCustorlength(stand_ans) or Custor_Ans IS NULL then

14、 return 0; end if; for 1 in L.LenCustor loop if instr(stand_ans,substr(ciistor_ans44)=0 then return 0; end if; end loop; return LenCustor; end; / Select GetltemScoreABC7AC1) From Dual; 6.Oracle SQL*Plus 命令 set linesize設(shè)置每行顯示的長(zhǎng)度 SET LINESIZE 100 start或告訴SQLPLUS執(zhí)行己經(jīng)存儲(chǔ)到sql文件中語(yǔ)句 執(zhí)行一個(gè)SQL腳本文件 SQLstart fil

15、e_name SQL file_name 我們可以將事條sql語(yǔ)句保存在一個(gè)文本文件中，這樣當(dāng)要執(zhí)行這個(gè)文件中的所有 的sql語(yǔ)句時(shí)，用上面的任一命令即可 修改用戶(hù)密碼 ALTER USER 用戶(hù)名 IDENTIFIED BY 密碼； oracle中邏輯運(yùn)算(not,and,or)及其優(yōu)先級(jí) 這3種邏輯運(yùn)算符中，NOT運(yùn)算符的優(yōu)先級(jí)最高，而后是AND,最后是OR Orach中的SPOOL命令是假脫機(jī)命令，可用于轉(zhuǎn)儲(chǔ)執(zhí)行的命令和結(jié)果，其效 果有 點(diǎn)像DOS命令中的轉(zhuǎn)向命令。SPOOL命令也可以用于轉(zhuǎn)出數(shù)據(jù)庫(kù)系統(tǒng)中的數(shù)據(jù)。 例如在SQL*Plus命令行中可以直接輸入：SPOOL假脫機(jī)文件名 啟動(dòng)假

16、脫機(jī)操作。 關(guān)閉假脫機(jī)操作可以在SQLPlus中輸入：SPOOL OFF 例：假脫機(jī)命令的使用。 spool c:SpoolFile.txt set pagesize 300 set linesize 300 select * from emp; select * from dept; spool off； 執(zhí)行完畢后，可以在C盤(pán)中找到SpooIFilelxt,打開(kāi)后可以看到通過(guò)Spool假脫 機(jī) 命令轉(zhuǎn)向到該文件中的內(nèi)容。 7記錄類(lèi)型 定義一個(gè)RECORD類(lèi)型 TYPE v 類(lèi)型名IS RECORD fleldnamel v 字段類(lèi)型NOT NULL fieldnameN v 字段類(lèi)型NOT

17、 NULL ); 屬性的類(lèi)型 % TYPE一引用數(shù)據(jù)庫(kù)列 % ROWTYPE代表表中的行 % type和1*0丫衛(wèi)6可以用來(lái)指定列的數(shù)據(jù)類(lèi)型 SName Studliifo.StudNaine % Tvpe; Stud.Rec Studinfo% ROWTYPE; 1 pe VIPStudRec is record ( VIPID Varchar2(15), VIPName varchar2(20) )； 聲明記錄類(lèi)型變量 Stud Rec VIPStudRec; 引用字段的語(yǔ)法是 recordname.coluinnnaine 用 于給列賦值的語(yǔ)法是 recordname.coluinnna

18、ine: = expression; 語(yǔ)法 SELECT v 歹ij名INTO v 變量名FROM v 表名WHERE v 條件； SELECT StudNoAStudName INTO Stud Rec FROM Studinfo WHERE StudNo= “99070470,; fetch mycur into StudRec ; TID Teacherlnfo.TeacherNo % pe; TName Teacherlnfo.TeacherName % type; 8.游標(biāo) CURSOR v 游標(biāo)名IS ; CURSOR mycur is select TeacherNo,Teach

19、erName From Teacherinfo Where TeacherBirthDay=To_Date(4981-2-5 , YYYY-MM-DD); 控制顯式游標(biāo) OPEN FETCH CLOSE 在使用OPEN、CLOSE或FETCH語(yǔ)句引用這些語(yǔ)句之前必須對(duì)游標(biāo)進(jìn)行 聲明 OPEN v游標(biāo)名 open my cur; FETCH v 游標(biāo)名INTO varl,.varN; OR FETCH v 游標(biāo)名INTO record,variable; fetch mycur into TID,TName; 每個(gè)游標(biāo)有四個(gè)屬性可以用于訪問(wèn)游標(biāo)的環(huán)境區(qū)域 %NOTFOUND %FOUND % R

20、OWCOUNT % ISOPEN 循環(huán)取記錄 while mycur% found loop (lbins_outpiit.put_line(TIDIITNAME); fetch mycur into TID,TName; end loop; CLOSE v游標(biāo)名； close mycur; 9表 CREATE TABLE schema.table (column datatype DEFAULT expr,.); 指定： 表名 列名、列數(shù)據(jù)類(lèi)型和列的大小 數(shù)據(jù)類(lèi)型 VARCHAR2ze)可變長(zhǎng)度的字符數(shù)據(jù)長(zhǎng)度可以到4000字節(jié) CHAR(size)固定長(zhǎng)度的字符數(shù)據(jù) NUMBER,s)可變長(zhǎng)

21、度的數(shù)字?jǐn)?shù)據(jù) DATE日期和時(shí)間值 NCHAR、NVARCHAR2,國(guó)家字符集，與環(huán)境變量NLS指定的語(yǔ)言集密切相關(guān), 使用方法和CHAR- VARCHAR2相同。 在創(chuàng)建表時(shí)，為一個(gè)列指定一個(gè)默認(rèn)值create table c (cl char(10) DEFAULT lll1) alter table test add syd date default sysdate; altertabletmodify(cccdefaultccc); altertabletmodify(cccdefaultnull); INSERT INTO departments (departmentdepartm

22、ent_name, managerjd) VALUES (300, ,Engineeriiig DEFAULT); UPDATE departments SET managerjd = DEFAULT WHERE departmentjd = 10; 約束類(lèi)型 NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK 列級(jí)約束 column CONSTRAINT constraint_name constraint_type, 表級(jí)約束 column, CONSTRAINT constraint_name constraint_type (column, ),

23、 NOT NULL 約束 只能定義在列級(jí)，不能定義在表級(jí). 防止NULL值進(jìn)入指定的列，在單列基礎(chǔ)上定義，默認(rèn)情況下,ORACLE允許在任 何列中有NULL值 CREATE TABLE employees( employeejdNUMBER(6), last_nameVARCHAR2(25) NOT NULL, salaryNUMBER(8,2), cominlssion_pct NUMBER22), hire_dateDATE CONSTRAINT emp_hlre_date_nn NOT NULL, UNIQUE約束 用來(lái)保護(hù)一個(gè)表中的一個(gè)或者多個(gè)列沒(méi)有任何兩行在收到保護(hù)的列中具有重復(fù)的數(shù)

24、 據(jù)ORACLE在唯一鍵列上自動(dòng)生成一個(gè)唯一索引以實(shí)現(xiàn)唯一性 CREATE TABLE employees( employeejd last_name email salary NUMBER(6) unique, VARCHAR2(25) NOT NULL, VARCHAR2(25), NUMBER(8,2), coininission_pct NUMBER(2,2), hire_dateDATE NOT NULL, CONSTRAINT emp_email_uk UNIQUE(email); PRIMARY KEY 約束 唯一鍵的所有特征都適用于主鍵約束，只是在主鍵列中不允許有NULL值 C

25、REATE TABLE departments( departmentjd NUMBER(4), email location Jd VARCHAR2(25), NUMBER(4)， CONSTRAINT dept_id_pk PRIMARY KEYfdepartinentJd); FOREIGN KEY 約束 通過(guò)使用公共列在表之間建立一種父子(parenbchlld)關(guān)系，在表上定義的外部鍵可 以指向主鍵或者其他表的唯一鍵 CREATE TABLE employees( employeejd NUMBER(6), departmentjcl NUMBER(4), CONSTRAINT em

26、p_dept_fk FOREIGN KEY (department Jd) REFERENCES departments(department_id), CHECK約束 檢查在約束中指定的條件是否得到了滿(mǎn)足 create table employees (sal number(7,2) constraint emp_sal_ckl check (sal 0) create table y( id varchar2(7) check (asciiCsubstrCidybl) between 65 and 90 and substr(id,191) is not null and ascii(su

27、bstr(id9291) between 65 and 90 and substr(id9291) is not null and ascii(substr(id34) between 65 and 90 and substr(id3J) is not null and ascii(substr(id944) between 65 and 90 and substr(id94J) is not null and ascii(substr(id,5,l) between 65 and 90 and substr(id,5,I) is not null) 片 CREATE TABLE depart

28、mentjd email TT( NUMBER(4) check(department_id like ftrJ%r), VARCHAR2(25) PRIMARY KEY )； CREATE TABLE TT( departmentjd NUMBER(4), emailVARCHAR2(25) PRIMARY KEY, STUDSEX VARCHAR2(2) CHECK (STUDSEX IN C 男 T 女 J); 添加一個(gè)新行到表中 INSERT INTO table (column , column) (value , value.); VALUES INSERT INTO depart

29、ments VALUES (100, Finance, NULL, NULL，SYSDATE，TO_DATE( FEB 3, 1999，MON DD, YYYY ); 改變表中的數(shù)據(jù) UPDATEtable SETcolumn = value , column = value, WHERErnnditinnl- UPDATE copy_emp SETdepar tmentjd = 110 WHERE cIepartment_name=tHELLOt; 從表中刪除行 DELETE FROM table WHERE condition; DELETE FROM departments WHERE

30、lepartment_name = Tinance1; 10視圖 視圖是一個(gè)SQL査詢(xún)，它永久存在于數(shù)據(jù)庫(kù)中，并被賦予了一個(gè)名稱(chēng)，視圖是從 基表中生成的虛擬表 CREATE OR REPLACE FORCEINOFORCE VIEW view (alias9 alias.) AS subquerv WITH CHECK OPTION CONSTRAINT constrain 切 WITH READ ONLY CONSTRAINT constrain 切； with check option:表名只有子?xùn)嗽?xún)檢索的行才能被插入、刪除、更新 with read only:說(shuō)明只能對(duì)基表中的行進(jìn)行只讀

31、訪問(wèn) onstralnt_name:指定 with check option 或 read only 約束的名稱(chēng)。 CREATE VIEW salvuSO AS SELECT employee_id ID_NUMBER, last_name NAME, salary* 12 ANN.SALARY FROM employees WHERE departmentjd = 50; SELECT * FROM salvuSO; 內(nèi)建視圖是一個(gè)帶有別名(或相關(guān)名)的可以在SQL語(yǔ)句中使用的子?xùn)嗽?xún) Top-N分析 SELECT ROWNUM as RANK, , salary FROM

32、(SELECT Iast_nameA salary FROM employees ORDER BY salary DESC) A WHERE ROWNUM 8000 GROUP BY departmentjd; 13綜合知識(shí) L學(xué)生信息表(Studinfo) 字段名稱(chēng) 數(shù)據(jù)類(lèi)型 鼻沒(méi) 是否為 空 pK 約束 字段描述 舉例 StudNo Varchar 2 15 Y 學(xué)生學(xué)號(hào) 20010704 70 StudName Varchar 2 20 學(xué)生姓名 李明 StudSex Char 2 男汝 學(xué)生性別 男 StudBirth Dav Date Y 出生年月 1980-10-3 ClassName Varchar 2 50 班級(jí)名稱(chēng) 計(jì)本01 2.課程信息表(Courseinfo) 字段名稱(chēng) 數(shù)據(jù)類(lèi)型 字段長(zhǎng)