Oracle課程小結(jié).doc

Oracle課程小結(jié)1. DualDual 是 Oracle中的一個實際存在的表，任何用戶均可讀取，常用在沒有目標(biāo)表的Select語句塊中DUAL就是個一行一列的表，如果你往里執(zhí)行insert、delete、truncate操作，就會導(dǎo)致很多程序出問題不要去執(zhí)行drop dual的操作，否則會使系統(tǒng)不能用，數(shù)據(jù)庫起不了DUAL是屬于SYS schema的一個表,然后以PUBLIC SYNONYM的方式供其他數(shù)據(jù)庫USER使用2. Null在Oracle中，值null被看作一個未知值，任何包含NULL值的算術(shù)運(yùn)算都會得到結(jié)果NULL,。因此，下面的select語句返回null select 5+7+null+9 from dual;select null*9 from dual;空字符串：Oracle 把空字符串 () 當(dāng)作 null, 因此 length() 的值是null, 而不是 0。一個null值與任何其它值比較，包括null值，結(jié)果都是null,因此，對NULL的=、!=、=、=等操作的結(jié)果都是未知的，也就算說，這些操作的結(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ù)是一種有零個或多個參數(shù)并且有一個返回值的程序。在SQL中Oracle內(nèi)建了一系列函，也可自定義函數(shù)數(shù)單行函數(shù),多行函數(shù)（組函數(shù)）Lower(c)-小寫轉(zhuǎn)換返回c小寫字符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:截取字符串的開始位置 參數(shù)3:截取的字符串的長度(而不是字符串的結(jié)束位置) ,如果省略，則返回從start_position開始到string尾之間的字符串Select SUBSTR(Hello,2) From DualInstr(column|expression,string,m,n)-查詢指定字符在字符串中的位置參數(shù)1:被搜索的字符串或者是表達(dá)式參數(shù)2:要查找的字符參數(shù)3:從什么位置開始查詢參數(shù)4:這個字符出現(xiàn)的位置 比如說：第一次出現(xiàn)的位置，第二次出現(xiàn)的位置。如果此參數(shù)3為正，從左到右開始檢索，如果此參數(shù)為負(fù)，從右到左檢索最后兩個參數(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ù)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) -返回一個number除以divisor的余數(shù)參數(shù)1:為被除數(shù)。 參數(shù)2:為除數(shù)。如果 divisor 為零，函數(shù) MOD 返回值 為原來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表示一個數(shù),.打印一個小數(shù)點,打印一個千位指示 SELECT TO_CHAR(12345.529,999,999.99) FROM DUAL;- 12,345.53 Replace(text, search_string, replacement_string) -替換字符串從字符串(text)查找一個文本表達(dá)式(search_string)，如果找到，用指定的置換串(replacement_string)代替它如果replacement_string為空,那么所有的search_string都被移除。如果search_string為null,那么就返回原來的SELECT REPLACE(hello,ello,i) FROM DUAL; -hiTo_number(, , )是將一些處理過的按一定格式編排過的字符串變回數(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)-返回字符串的長度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)使用過程put_line時,會自動在行的尾部追加行結(jié)束符;當(dāng)使用過程put時,需要使用過程 new_line追加行結(jié)束符. set serverout on begin dbms_output.put_line(偉大的中華民族); dbms_output.put(中國); dbms_output.put(,偉大的祖國); dbms_output.new_line; end; / 偉大的中華民族 中國,偉大的祖國 new_line 該過程用于在行的尾部追加行結(jié)束符.當(dāng)使用過程PUT時,必須調(diào)用NEW_LINE過程來結(jié)束行.5. PL/SQL 塊DECLARE/*定義部分（可選）定義常量、變量、復(fù)雜數(shù)據(jù)類型、游標(biāo)、例解*/BEGIN/*執(zhí)行部分（必須）PL/SQL語句和SQL語句*/EXCEPTION/*異常處理部分（可選）處理運(yùn)行錯誤*/END; /*塊結(jié)束標(biāo)記*/命名塊:指具有特定名稱標(biāo)識的PL/SQL塊，在PL/SQL塊前使用加以標(biāo)記 begin dbms_output.put_line(Hello,World!); begin dbms_output.put_line(Good); end; - end;-賦值操作符“:=”變量名:=值定義變量 變量名 數(shù)據(jù)類型StudScore NUMBER(5,2);StudName VARCHAR(20);定義常量添加關(guān)鍵字 CONSTANT 并賦值PI CONSTANT NUMBER(8,7):=3.1415926;條件控制 有三種語句形式 IF-THENIF-THEN-ELSEIF-THEN-ELSIFIF THEN語句;END IF;IF THEN語句;ELSE語句;END IF;IF THEN語句;ELSIF THEN語句;ELSIF THEN語句;ELSE語句;END IF;CASE WHEN THEN 語句; WHEN THEN 語句; WHEN THEN 語句; Else 語句;End Case;CASE WHEN THEN 語句; WHEN THEN 語句; WHEN THEN 語句; Else 語句;End Case;有三種形式的 LOOP 語句 LOOPWHILE-LOOPFOR-LOOPLOOP語句; EXIT EXIT-WHENEND LOOP;EXIT 強(qiáng)制循環(huán)無條件完成 立即退出循環(huán)EXIT WHEN ;EXIT 語句時對 WHEN 子句中的條件進(jìn)行判斷 如果判定條件為 TRUE,則循環(huán)完成 WHILE LOOP語句;END LOOP;FOR IN Reverse lower_bound . higher_bound LOOP語句;END LOOP;6. 子程序過程用于執(zhí)行某項操作函數(shù)用于執(zhí)行某項操作并返回值存儲過程語法CREATE OR REPLACE PROCEDURE (參數(shù)1, 參數(shù)N) IS|AS局部聲明BEGIN 可執(zhí)行語句；EXCEPTION 例外處理程序；END ;參數(shù)說明 變量名 IN|OUT|IN OUT IN：輸入，接受值，默認(rèn)模式OUT：輸出，將值返回給子程序的調(diào)用程序IN OUT：輸入輸出，接受值并返回已更新的值對于IN模式的實參可以是常量或變量，但對于OUT和IN OUT模式的實參必須是變量。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ù)語法CREATE OR REPLACE FUNCTION (參數(shù)1, 參數(shù)N) RETURN datatype IS局部聲明BEGIN可執(zhí)行語句 ;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è)置每行顯示的長度SET LINESIZE 100start或 告訴SQLPLUS執(zhí)行已經(jīng)存儲到sql文件中語句執(zhí)行一個SQL腳本文件SQLstart file_nameSQL file_name我們可以將多條 sql語句保存在一個文本文件中，這樣當(dāng)要執(zhí)行這個文件中的所有的sql語句時，用上面的任一命令即可修改用戶密碼ALTER USER 用戶名 IDENTIFIED BY 密碼; oracle中邏輯運(yùn)算符(not,and,or)及其優(yōu)先級這3種邏輯運(yùn)算符中，NOT運(yùn)算符的優(yōu)先級最高，而后是AND，最后是OROracle中的SPOOL命令是假脫機(jī)命令，可用于轉(zhuǎn)儲執(zhí)行的命令和結(jié)果，其效果有點像DOS命令中的轉(zhuǎn)向命令。SPOOL命令也可以用于轉(zhuǎn)出數(shù)據(jù)庫系統(tǒng)中的數(shù)據(jù)。 例如在SQL*Plus命令行中可以直接輸入：SPOOL 假脫機(jī)文件名 啟動假脫機(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盤中找到SpoolFile.txt,打開后可以看到通過Spool假脫機(jī)命令轉(zhuǎn)向到該文件中的內(nèi)容。8. 記錄類型定義一個 RECORD 類型 TYPE IS RECORD( fieldname1 NOT NULL : fieldnameN NOT NULL);屬性的類型%TYPE引用數(shù)據(jù)庫列%ROWTYPE代表表中的行%type和%rowtype 可以用來指定列的數(shù)據(jù)類型SName StudInfo.StudName%Type;Stud_Rec StudInfo%ROWTYPE;Type VIPStudRec is record ( VIPID Varchar2(15), VIPName varchar2(20) );聲明記錄類型變量StudRec VIPStudRec;引用字段的語法是recordname.columnname 用于給列賦值的語法是recordname.columnname: = expression; 語法 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 語句引用這些語句之前必須對游標(biāo)進(jìn)行聲明OPEN open mycur;FETCH INTO var1, , varN;ORFETCH INTO record_variable;fetch mycur into TID,TName;每個游標(biāo)有四個屬性可以用于訪問游標(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ù)類型和列的大小數(shù)據(jù)類型VARCHAR2(size)可變長度的字符數(shù)據(jù)長度可以到4000字節(jié)CHAR(size) 固定長度的字符數(shù)據(jù)NUMBER(p,s) 可變長度的數(shù)字?jǐn)?shù)據(jù)DATE 日期和時間值NCHAR、NVARCHAR2，國家字符集，與環(huán)境變量NLS指定的語言集密切相關(guān)，使用方法和CHAR、VARCHAR2相同。在創(chuàng)建表時，為一個列指定一個默認(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;約束類型NOT NULLUNIQUE PRIMARY KEYFOREIGN KEYCHECK 列級約束column CONSTRAINT constraint_name constraint_type,表級約束column,. CONSTRAINT constraint_name constraint_type (column, .),NOT NULL約束只能定義在列級,不能定義在表級.防止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 約束用來保護(hù)一個表中的一個或者多個列沒有任何兩行在收到保護(hù)的列中具有重復(fù)的數(shù)據(jù).ORACLE在唯一鍵列上自動生成一個唯一索引以實現(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 約束通過使用公共列在表之間建立一種父子(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 約束檢查在約束中指定的條件是否得到了滿足.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 (男,女);添加一個新行到表中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. 視圖視圖是一個 SQL 查詢，它永久存在于數(shù)據(jù)庫中，并被賦予了一個名稱,視圖是從基表中生成的虛擬表 CREATE OR REPLACE FORCE|NOFORCE VIEW view (alias, alias.) AS subqueryWITH CHECK OPTION CONSTRAINT constraintWITH READ ONLY CONSTRAINT constraint;with check option:表名只有子查詢檢索的行才能被插入、刪除、更新with read only:說明只能對基表中的行進(jìn)行只讀訪問onstraint_name:指定with check option或read only約束的名稱。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)建視圖是一個帶有別名(或相關(guān)名) 的可以在SQL 語句中使用的子查詢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. 綜合知識1學(xué)生信息表（StudInfo） 字段名稱數(shù)據(jù)類型字段長度是否為空PK約束字