Oracle課程小結(jié).doc

Oracle課程小結(jié)1. DualDual 是 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. Null在Oracle中，值null被看作一個(gè)未知值，任何包含NULL值的算術(shù)運(yùn)算都會(huì)得到結(jié)果NULL,。因此，下面的select語(yǔ)句返回null select 5+7+null+9 from dual;select null*9 from dual;空字符串：Oracle 把空字符串 () 當(dāng)作 null, 因此 length() 的值是null, 而不是 0。一個(gè)null值與任何其它值比較，包括null值，結(jié)果都是null,因此，對(duì)NULL的=、!=、=、=等操作的結(jié)果都是未知的，也就算說(shuō)，這些操作的結(jié)果仍然是NULL。由于NULL是未知，所以NULL AND NULL、NULL OR NULL、NULL AND TRUE和NULL OR FALSE的值都是未知的，這些的結(jié)果仍然是NULL。但NULL AND FALSE和NULL OR TRUE不一樣declare v_result boolean; v_result1 boolean;begin v_result:=null and false; v_result1:=null or true; dbms_output.put(v_result=); dbms_output.put_line(case when v_result then true else false end);-false dbms_output.put(v_result1=); dbms_output.put_line(case when v_result1 then true else false end); -true end;3. 函數(shù)函數(shù)是一種有零個(gè)或多個(gè)參數(shù)并且有一個(gè)返回值的程序。在SQL中Oracle內(nèi)建了一系列函，也可自定義函數(shù)數(shù)單行函數(shù),多行函數(shù)（組函數(shù)）Lower(c)-小寫(xiě)轉(zhuǎn)換返回c小寫(xiě)字符Concat(c1,c2) -連接字符串c1,c2均為字符串，函數(shù)將c2連接到c1的后面，如果c1為null,將返回c2.如果c2為null,則返回c1，如果c1、c2都為null，則返回null,如果c1,c2其中之一為數(shù)字,或都為數(shù)字Concat(10,10) Concat(10,10)Substr( string, start_position, length )-取字串參數(shù)1:string 要處理的字符串 參數(shù)2:截取字符串的開(kāi)始位置 參數(shù)3:截取的字符串的長(zhǎng)度(而不是字符串的結(jié)束位置) ,如果省略，則返回從start_position開(kāi)始到string尾之間的字符串Select SUBSTR(Hello,2) From DualInstr(column|expression,string,m,n)-查詢(xún)指定字符在字符串中的位置參數(shù)1:被搜索的字符串或者是表達(dá)式參數(shù)2:要查找的字符參數(shù)3:從什么位置開(kāi)始查詢(xún)參數(shù)4:這個(gè)字符出現(xiàn)的位置 比如說(shuō)：第一次出現(xiàn)的位置，第二次出現(xiàn)的位置。如果此參數(shù)3為正，從左到右開(kāi)始檢索，如果此參數(shù)為負(fù)，從右到左檢索最后兩個(gè)參數(shù)是默認(rèn)的是1和1SELECT instr(abc,d) FROM dual; - 返回 0 SELECT instr(syran mo,a,1,2) FROM dual; - 返回 0SELECT INSTR(oWo, W) FROM DUAL;-?Round( number, decimalplaces )-四舍五入函數(shù)參數(shù)1:要處理的數(shù)值(數(shù)值表達(dá)式) 參數(shù)2:四舍五入時(shí)取的小數(shù)的位數(shù)，不填則返回整數(shù)select round(123.456) from dual; 得到 123 select round(123.456, 0) from dual; 得到 123 select round(123.456, 1) from dual; 得到 123.5 select round(123.456, 2) from dual; 得到 123.46 select round(123.456, 3) from dual; 得到 123.456 select round(-123.456, 2) from dual; 得到 -123.46Mod(number,divisor) -返回一個(gè)number除以divisor的余數(shù)參數(shù)1:為被除數(shù)。 參數(shù)2:為除數(shù)。如果 divisor 為零，函數(shù) MOD 返回值 為原來(lái)numberMOD(3, 2) 等于1 MOD(3, 0) 等于3 MOD(2,3) 等于2 To_char(date, format_model)-日期轉(zhuǎn)為字符To_char(number, format_model)-數(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)查找一個(gè)文本表達(dá)式(search_string)，如果找到，用指定的置換串(replacement_string)代替它如果replacement_string為空,那么所有的search_string都被移除。如果search_string為null,那么就返回原來(lái)的SELECT REPLACE(hello,ello,i) FROM DUAL; -hiTo_number(, , )是將一些處理過(guò)的按一定格式編排過(guò)的字符串變回?cái)?shù)值型的格式SELECT TO_NUMBER(20)*5 FROM DUAL;-100To_date( string1, format_mask , nls_language )-轉(zhuǎn)換為日期Format_mask yyyy-MM-dd HH24:mi:ssselect To_Date(1981-1-1,YYYY-MM-DD) from dual -1981-1-1Length(string)-返回字符串的長(zhǎng)度select length(我) from dual -1select length(AB) from dual -2 4. dbms_output如果dbms_output.put_line的內(nèi)容不能顯示，需要在命令行中先敲入set serveroutput on;put和put_line 當(dāng)使用過(guò)程put_line時(shí),會(huì)自動(dòng)在行的尾部追加行結(jié)束符;當(dāng)使用過(guò)程put時(shí),需要使用過(guò)程 new_line追加行結(jié)束符. set serverout on begin dbms_output.put_line(偉大的中華民族); dbms_output.put(中國(guó)); dbms_output.put(,偉大的祖國(guó)); dbms_output.new_line; end; / 偉大的中華民族 中國(guó),偉大的祖國(guó) new_line 該過(guò)程用于在行的尾部追加行結(jié)束符.當(dāng)使用過(guò)程PUT時(shí),必須調(diào)用NEW_LINE過(guò)程來(lái)結(jié)束行.5. PL/SQL 塊DECLARE/*定義部分（可選）定義常量、變量、復(fù)雜數(shù)據(jù)類(lèi)型、游標(biāo)、例解*/BEGIN/*執(zhí)行部分（必須）PL/SQL語(yǔ)句和SQL語(yǔ)句*/EXCEPTION/*異常處理部分（可選）處理運(yùn)行錯(cuò)誤*/END; /*塊結(jié)束標(biāo)記*/命名塊:指具有特定名稱(chēng)標(biāo)識(shí)的PL/SQL塊，在PL/SQL塊前使用加以標(biāo)記 begin dbms_output.put_line(Hello,World!); begin dbms_output.put_line(Good); end; - end;-賦值操作符“:=”變量名:=值定義變量 變量名 數(shù)據(jù)類(lèi)型StudScore NUMBER(5,2);StudName VARCHAR(20);定義常量添加關(guān)鍵字 CONSTANT 并賦值PI CONSTANT NUMBER(8,7):=3.1415926;條件控制 有三種語(yǔ)句形式 IF-THENIF-THEN-ELSEIF-THEN-ELSIFIF THEN語(yǔ)句;END IF;IF THEN語(yǔ)句;ELSE語(yǔ)句;END IF;IF THEN語(yǔ)句;ELSIF THEN語(yǔ)句;ELSIF THEN語(yǔ)句;ELSE語(yǔ)句;END IF;CASE WHEN THEN 語(yǔ)句; WHEN THEN 語(yǔ)句; WHEN THEN 語(yǔ)句; Else 語(yǔ)句;End Case;CASE WHEN THEN 語(yǔ)句; WHEN THEN 語(yǔ)句; WHEN THEN 語(yǔ)句; Else 語(yǔ)句;End Case;有三種形式的 LOOP 語(yǔ)句 LOOPWHILE-LOOPFOR-LOOPLOOP語(yǔ)句; EXIT EXIT-WHENEND LOOP;EXIT 強(qiáng)制循環(huán)無(wú)條件完成 立即退出循環(huán)EXIT WHEN ;EXIT 語(yǔ)句時(shí)對(duì) WHEN 子句中的條件進(jìn)行判斷 如果判定條件為 TRUE,則循環(huán)完成 WHILE LOOP語(yǔ)句;END LOOP;FOR IN Reverse lower_bound . higher_bound LOOP語(yǔ)句;END LOOP;6. 子程序過(guò)程用于執(zhí)行某項(xiàng)操作函數(shù)用于執(zhí)行某項(xiàng)操作并返回值存儲(chǔ)過(guò)程語(yǔ)法CREATE OR REPLACE PROCEDURE (參數(shù)1, 參數(shù)N) IS|AS局部聲明BEGIN 可執(zhí)行語(yǔ)句；EXCEPTION 例外處理程序；END ;參數(shù)說(shuō)明 變量名 IN|OUT|IN OUT IN：輸入，接受值，默認(rèn)模式OUT：輸出，將值返回給子程序的調(diào)用程序IN OUT：輸入輸出，接受值并返回已更新的值對(duì)于IN模式的實(shí)參可以是常量或變量，但對(duì)于OUT和IN OUT模式的實(shí)參必須是變量。Create Or Replace Procedure ProcGetName(StudName in out varchar2) is Begin StudName:=substr(StudName,instr(StudName,明,2); End;/declare StudName varchar2(15):=明小明;begin ProcGetName(StudName); DBMS_OUTPUT.PUT_LINE(StudName);end;函數(shù)語(yǔ)法CREATE OR REPLACE FUNCTION (參數(shù)1, 參數(shù)N) RETURN datatype IS局部聲明BEGIN可執(zhí)行語(yǔ)句 ;EXCEPTION例外處理程序END ;僅接受 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 return 0; end if; for i in 1.LenCustor loop if instr(stand_ans,substr(custor_ans,i,1)=0 then return 0; end if; end loop; return LenCustor;end;/Select GetItemScore(ABC,AC) From Dual; 7. Oracle SQL*Plus 命令set linesize 設(shè)置每行顯示的長(zhǎng)度SET LINESIZE 100start或 告訴SQLPLUS執(zhí)行已經(jīng)存儲(chǔ)到sql文件中語(yǔ)句執(zhí)行一個(gè)SQL腳本文件SQLstart file_nameSQL 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，最后是OROracle中的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)假脫機(jī)操作。 關(guān)閉假脫機(jī)操作可以在SQL*Plus中輸入：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)中找到SpoolFile.txt,打開(kāi)后可以看到通過(guò)Spool假脫機(jī)命令轉(zhuǎn)向到該文件中的內(nèi)容。8. 記錄類(lèi)型定義一個(gè) RECORD 類(lèi)型 TYPE IS RECORD( fieldname1 NOT NULL : fieldnameN NOT NULL);屬性的類(lèi)型%TYPE引用數(shù)據(jù)庫(kù)列%ROWTYPE代表表中的行%type和%rowtype 可以用來(lái)指定列的數(shù)據(jù)類(lèi)型SName StudInfo.StudName%Type;Stud_Rec StudInfo%ROWTYPE;Type VIPStudRec is record ( VIPID Varchar2(15), VIPName varchar2(20) );聲明記錄類(lèi)型變量StudRec VIPStudRec;引用字段的語(yǔ)法是recordname.columnname 用于給列賦值的語(yǔ)法是recordname.columnname: = expression; 語(yǔ)法 SELECT INTO FROM WHERE ;SELECT StudNo,StudName INTO StudRec FROM StudInfoWHERE StudNo=99070470;fetch mycur into StudRec ;TID TeacherInfo.TeacherNo%Type;TName TeacherInfo.TeacherName%type;9. 游標(biāo)CURSOR IS;CURSOR mycur is select TeacherNo,TeacherName From TeacherInfo Where TeacherBirthDay=To_Date(1981-2-5,YYYY-MM-DD);控制顯式游標(biāo)OPENFETCHCLOSE在使用 OPEN、 CLOSE 或 FETCH 語(yǔ)句引用這些語(yǔ)句之前必須對(duì)游標(biāo)進(jìn)行聲明OPEN open mycur;FETCH INTO var1, , varN;ORFETCH INTO record_variable;fetch mycur into TID,TName;每個(gè)游標(biāo)有四個(gè)屬性可以用于訪(fǎng)問(wèn)游標(biāo)的環(huán)境區(qū)域%NOTFOUND%FOUND%ROWCOUNT%ISOPEN循環(huán)取記錄 while mycur%found loop dbms_output.put_line(TID|TNAME); fetch mycur into TID,TName; end loop;CLOSE ; close mycur;10. 表CREATE TABLE schema.table (column datatype DEFAULT expr, .);指定：表名列名、列數(shù)據(jù)類(lèi)型和列的大小數(shù)據(jù)類(lèi)型VARCHAR2(size)可變長(zhǎng)度的字符數(shù)據(jù)長(zhǎng)度可以到4000字節(jié)CHAR(size) 固定長(zhǎng)度的字符數(shù)據(jù)NUMBER(p,s) 可變長(zhǎng)度的數(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 (c1 char(10) DEFAULT 1111) alter table test add syd date default sysdate; alter table t modify(ccc default ccc ); alter table t modify(ccc default null); INSERT INTO departments (department_id, department_name, manager_id) VALUES (300, Engineering, DEFAULT);UPDATE departments SET manager_id = DEFAULT WHERE department_id = 10;約束類(lèi)型NOT NULLUNIQUE PRIMARY KEYFOREIGN KEYCHECK 列級(jí)約束column CONSTRAINT constraint_name constraint_type,表級(jí)約束column,. CONSTRAINT constraint_name constraint_type (column, .),NOT NULL約束只能定義在列級(jí),不能定義在表級(jí).防止NULL值進(jìn)入指定的列,在單列基礎(chǔ)上定義,默認(rèn)情況下,ORACLE允許在任何列中有NULL值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 CONSTRAINT emp_hire_date_nn NOT NULL,. UNIQUE 約束用來(lái)保護(hù)一個(gè)表中的一個(gè)或者多個(gè)列沒(méi)有任何兩行在收到保護(hù)的列中具有重復(fù)的數(shù)據(jù).ORACLE在唯一鍵列上自動(dòng)生成一個(gè)唯一索引以實(shí)現(xiàn)唯一性CREATE TABLE employees( employee_id NUMBER(6) unique, last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL,. CONSTRAINT emp_email_uk UNIQUE(email);PRIMARY KEY 約束唯一鍵的所有特征都適用于主鍵約束,只是在主鍵列中不允許有NULL值CREATE TABLE departments( department_id NUMBER(4), email VARCHAR2(25) , location_id NUMBER(4), CONSTRAINT dept_id_pk PRIMARY KEY(department_id);FOREIGN KEY 約束通過(guò)使用公共列在表之間建立一種父子(parent-child)關(guān)系,在表上定義的外部鍵可以指向主鍵或者其他表的唯一鍵.CREATE TABLE employees( employee_id NUMBER(6), . department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id),CHECK 約束檢查在約束中指定的條件是否得到了滿(mǎn)足.create table employees (sal number(7,2) constraint emp_sal_ck1 check (sal 0) ) create table y(id varchar2(7) check (ascii(substr(id,1,1) between 65 and 90 and substr(id,1,1) is not null and ascii(substr(id,2,1) between 65 and 90 and substr(id,2,1) is not null and ascii(substr(id,3,1) between 65 and 90 and substr(id,3,1) is not null and ascii(substr(id,4,1) between 65 and 90 and substr(id,4,1) is not null and ascii(substr(id,5,1) between 65 and 90 and substr(id,5,1) is not null);CREATE TABLE TT( department_id NUMBER(4) check(department_id like trj%), email VARCHAR2(25) PRIMARY KEY );CREATE TABLE TT( department_id NUMBER(4) , email VARCHAR2(25) PRIMARY KEY, STUDSEX VARCHAR2(2) CHECK (STUDSEX IN (男,女);添加一個(gè)新行到表中INSERT INTOtable (column , column.)VALUES(value , value.);INSERT INTOdepartmentsVALUES(100, Finance, NULL, NULL,SYSDATE,TO_DATE(FEB 3, 1999, MON DD, YYYY);改變表中的數(shù)據(jù)UPDATEtableSETcolumn = value , column = value, .WHERE condition;UPDATE copy_empSET department_id = 110WHERE department_name=HELLO;從表中刪除行DELETE FROM tableWHERE condition; DELETE FROM departments WHERE department_name = Finance;11. 視圖視圖是一個(gè) SQL 查詢(xún)，它永久存在于數(shù)據(jù)庫(kù)中，并被賦予了一個(gè)名稱(chēng),視圖是從基表中生成的虛擬表 CREATE OR REPLACE FORCE|NOFORCE VIEW view (alias, alias.) AS subqueryWITH CHECK OPTION CONSTRAINT constraintWITH READ ONLY CONSTRAINT constraint;with check option:表名只有子查詢(xún)檢索的行才能被插入、刪除、更新with read only:說(shuō)明只能對(duì)基表中的行進(jìn)行只讀訪(fǎng)問(wèn)onstraint_name:指定with check option或read only約束的名稱(chēng)。CREATE VIEW salvu50 AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 50;SELECT *FROMsalvu50;內(nèi)建視圖是一個(gè)帶有別名(或相關(guān)名) 的可以在SQL 語(yǔ)句中使用的子查詢(xún)Top-N 分析SELECT ROWNUM as RANK, last_name, salary FROM (SELECT last_name,salary FROM employees ORDER BY salary DESC) AWHERE ROWNUM 8000GROUP BY department_id;14. 綜合知識(shí)1學(xué)生信息表（StudInfo） 字段名稱(chēng)數(shù)據(jù)類(lèi)型字段長(zhǎng)度是否為空PK約束字