关于Oracle课程小结

1、Oracle课程小结DualDual 是 Oraclle中的一个个实际存在的的表，任何用用户均可读取取，常用在没没有目标表的的Selecct语句块中中DUAL就是个个一行一列的的表，如果你你往里执行iinsertt、deleete、trruncatte操作，就就会导致很多多程序出问题题不要去执行drrop duual的操作作，否则会使使系统不能用用，数据库起起不了DUAL是属于于SYS sschemaa的一个表,然后以PUUBLIC SYNONNYM的方式式供其他数据据库USERR使用Null在Oraclee中，值nuull被看作作一个未知值值，任何包含含NULL值值的算术运算算都会得到结结果

2、NULLL,。因此，下下面的sellect语句句返回nulll select 5+7+nnull+99 fromm duall;select null*9 froom duaal;空字符串：Orracle 把空字符串串 () 当作 nnull, 因此 leength() 的的值是nulll, 而不不是 0。一个null值值与任何其它它值比较，包包括nulll值，结果都都是nulll,因此，对对NULL的的=、!=、=、=等操操作的结果都都是未知的，也也就算说，这这些操作的结结果仍然是NNULL。由于NULL是是未知，所以以NULL AND NNULL、NNULL OOR NULLL、NULLL

3、 ANDD TRUEE和NULLL OR FFALSE的的值都是未知知的，这些的的结果仍然是是NULL。但NULL AAND FAALSE和NNULL OOR TRUUE不一样declaree v_reesult booleean; vv_resuult1 bbooleaan;begin v_resullt:=nuull annd fallse; v_resullt1:=nnull oor truue; dbbms_ouutput.put(v_ressult=); dbbms_ouutput.put_lline(case when v_ressult tthen true elsee fall

4、se eend);-falsse dbbms_ouutput.put(v_ressult1=); dbbms_ouutput.put_lline(case when v_ressult1 then truee elsse faalse end); -truee end;函数函数是一种有零零个或多个参参数并且有一一个返回值的的程序。在SSQL中Orracle内内建了一系列列函，也可自自定义函数数单行函数,多多行函数（组组函数）Lower(cc)-小写写转换返回c小写字符符Concat(c1,c22) -连连接字符串c1,c2均为为字符串，函函数将c2连连接到c1的的后面，如果果c1为nuull,

5、将返返回c2.如如果c2为nnull,则则返回c1，如如果c1、cc2都为nuull，则返返回nulll,如果c11,c2其中中之一为数字字,或都为数数字Concat(10,10) Concaat(10,10)Substr( striing, sstart_posittion, lenngth )-取取字串参数1:strring 要要处理的字符符串 参数2:截取字字符串的开始始位置 参数3:截取的的字符串的长长度(而不是是字符串的结结束位置) ,如果省略略，则返回从从startt_posiition开开始到strring尾之之间的字符串串Select SUBSTTR(Heello,2) Fro

6、om DuaalInstr(ccolumnn|exprressioon,sttring,m,n)-查询指定定字符在字符符串中的位置置参数1:被搜索索的字符串或或者是表达式式参数2:要查找找的字符参数3:从什么么位置开始查查询参数4:这个字字符出现的位位置 比如说说：第一次出出现的位置，第第二次出现的的位置。如果此参数3为为正，从左到到右开始检索索，如果此参参数为负，从从右到左检索索最后两个参数是是默认的是11和1SELECT instrr(abcc,d) FROOM duaal; - 返回 00 SELECT instrr(syrran moo,a,1,2) FROMM duall; - 返回

7、 0SELECT INSTRR(oWoo, WW) FRROM DUUAL;-?Round( numbeer, decimmalplaaces )-四四舍五入函数数参数1:要处理理的数值(数数值表达式) 参数2:四舍五五入时取的小小数的位数，不不填则返回整整数select roundd(123.456) from dual; 得得到 1233 select roundd(123.456, 0) frrom duual; 得到到 123 select roundd(123.456, 1) frrom duual; 得到到 123.5 select roundd(123.456, 2) frrom

8、 duual; 得到到 123.46 select roundd(123.456, 3) frrom duual; 得到到 123.456 select roundd(-1233.456, 2) ffrom ddual; 得到到 -1233.46Mod(nummber,ddivisoor) -返回一个个numbeer除以diivisorr的余数参数1:为被除除数。 参数2:为除数数。如果 ddivisoor 为零，函函数 MODD 返回值 为原来nuumberMOD(3, 2) 等于于1 MOD(3, 0) 等于于3 MOD(2,33) 等于22 To_charr(datee, foormat

9、_modell)-日日期转为字符符To_charr(numbber, formaat_moddel)-数字转为为字符格式化元素，用用于显示字符符形式的数字字值: 99表示一个个数,.打印一个小小数点,打印一个千千位指示 SELECT TO_CHHAR(122345.5529,9999,9999.99) FROOM DUAAL;- 12,3345.533 Replacee(textt, seaarch_sstringg, repplacemment_sstringg) -替替换字符串从字符串(teext)查找找一个文本表表达式(seearch_strinng)，如果果找到，用指指定的置换串串(r

10、epllacemeent_sttring)代替它如果replaacemennt_strring为空空,那么所有有的searrch_sttring都都被移除。如果searcch_strring为nnull,那那么就返回原原来的SELLECT REPLAACE(helllo,elloo,i) FROMM DUALL; -hhiTo_numbber(, , )是将一些处理过过的按一定格格式编排过的的字符串变回回数值型的格格式SELECT TO_NUUMBER(20)*5 FROM DDUAL;-100To_datee( strring1, foormat_mask , nls_llanguaage

11、)-转换换为日期Format_mask yyyyy-MM-ddd HH224:mi:ssselect To_DDate(1981-1-1,YYYYY-MM-DDD) fromm duual -1981-1-1Length(strinng)-返返回字符串的的长度select lenngth(我) fromm duual -1select lenngth(AB) froom ddual -2 dbms_ouutput如果dbms_outpuut.putt_linee的内容不能能显示，需要要在命令行中中先敲入seet serrverouutput on;put和putt_linee 当使用过过程pu

12、t_line时时,会自动在在行的尾部追追加行结束符符;当使用过过程put时时,需要使用用过程 new_lline追加加行结束符. sett servveroutt on beggin ddbms_ooutputt.put_line(伟大的中中华民族); ddbms_ooutputt.put(中国); ddbms_ooutputt.put(,伟大的的祖国); ddbms_ooutputt.new_line; endd; / 伟大的的中华民族 中国,伟大的祖国国 new_linne 该过程用于在行行的尾部追加加行结束符.当使用过程程PUT时,必须调用NNEW_LIINE过程来来结束行.PL/SQL

13、 块DECLAREE/*定义部分（可可选）定义常量量、变量、复复杂数据类型型、游标、例例解*/BEGIN/*执行部分（必必须）PL/SSQL语句和和SQL语句句*/EXCEPTIION/*异常处理部分分（可选）处理运行行错误*/END; /*块结束束标记*/命名块:指具有有特定名称标标识的PL/SQL块，在在PL/SQQL块前使用用加加以标记 begin dbmss_outpput.puut_linne(Heello,WWorld!); begiin dbmms_outtput.pput_liine(GGood); end; - end;-赋值操作符“:=”变量名:=值定义变量 变量名 数数据

14、类型StudScoore NUUMBER(5,2);StudNamme VARRCHAR(20);定义常量添加关键字 CCONSTAANT 并赋赋值PI CONNSTANTT NUMMBER(88,7):=3.14115926;条件控制 有三种语句形式式 IF-THENNIF-THENN-ELSEEIF-THENN-ELSIIFIF THENN语句;END IF;IF THENN语句;ELSE语句;END IF;IF THEEN语句;ELSIF THEN语句;ELSIF THEN语句;ELSE语句;END IF;CASE WHEN THENN 语句句; WHEN THENN 语句句; WHEN

15、 THEN 语句句; Elsee 语句;End Casse;CASE WHEN TTHEN 语句句; WHEN TTHEN 语句句; WHEN TTHEN 语句句; Elsee 语句;End Casse;有三种形式的 LOOP 语句 LOOPWHILE-LLOOPFOR-LOOOPLOOP语句; EXIT EXIT-WHENEND LOOOP;EXIT 强制制循环无条件件完成 立即即退出循环EXIT WHHEN ;EXXIT 语句句时对 WHHEN 子句句中的条件进进行判断 如如果判定条件件为 TRUUE,则循环环完成 WHILE LOOP语句;END LOOOP;FOR INN Revver

16、se loweer_bouund . highher_boound LOOP语句;END LOOOP;子程序过程用于执行某项操操作函数用于执行某项操操作并返回值值存储过程语法CREATE OR RREPLACCE PRROCEDUURE (参数1, 参数N) ISS|AS局部声明BEGIN 可执执行语句；EXCEPTIION 例例外处理程序序；END ;参数说明 变量名 IN|OOUT|INN OUT IN：输入，接接受值，默认认模式OUT：输出，将将值返回给子子程序的调用用程序IN OUT：输入输出，接接受值并返回回已更新的值值对于IN模式的的实参可以是是常量或变量量，但对于OOUT和INN

17、 OUT模模式的实参必必须是变量。Create Or Reeplacee Procceduree ProccGetNaame(SttudNamme in out vvarchaar2) is Begin StuudNamee:=subbstr(SStudNaame,innstr(SStudNaame,明明,2); End;/declaree StuddName varchhar2(115):=明小明;begin ProcGGetNamme(StuudNamee); DBMS_OUTPUUT.PUTT_LINEE(StuddName);end;函数语法CREATE OR RREPLACCE FU

18、UNCTIOON (参数1, 参数N) REETURN datattype IIS局部声明BEGIN可执行语句句 ;EXCEPTTION例外处理理程序END ;仅接受 IN 参数Create Or Reeplacee Funcction GetIttemScoore(Sttand_AAns vaarcharr2,Cusstor_AAns vvarchaar2) returnn int is LenCuustor int:=lengtth(Cusstor_AAns); begin if LeenCusttorleength(standd_ans) or CCustorr_Ans IS NUULL

19、 thhen reeturn 0; end iif; for ii in 11.LennCustoor loop iff insttr(staand_anns,subbstr(ccustorr_ans,i,1)=0 thhen retuurn 0; ennd if; end lloop; returrn LennCustoor;end;/Select GetIttemScoore(AABC,AC) From Dual; Oracle SQL*PPlus 命命令set linnesizee 设置每行行显示的长度度SET LINNESIZEE 100start或 告诉SSQLPLUUS执行已经经存

20、储到sqql文件中语语句执行一个SQLL脚本文件SSQLsttart ffile_nnameSQQL ffile_nname我们可以将多条条 sql语语句保存在一一个文本文件件中，这样当当要执行这个个文件中的所所有的sqll语句时，用用上面的任一一命令即可修改用户密码ALTER UUSER 用用户名 IDDENTIFFIED BBY 密码; oracle中中逻辑运算符符(not,and,oor)及其优优先级这3种逻辑运算算符中，NOOT运算符的的优先级最高高，而后是AAND，最后后是OROracle中中的SPOOOL命令是假假脱机命令，可可用于转储执执行的命令和和结果，其效效果有点像DDOS命

21、令中中的转向命令令。SPOOOL命令也可可以用于转出出数据库系统统中的数据。 例如在SQLL*Pluss命令行中可可以直接输入入：SPOOOL 假脱机机文件名 启动假脱机操操作。 关闭假脱机操操作可以在SSQL*Pllus中输入入：SPOOOL OFFF 例：假脱机命命令的使用。 spool c:SppoolFiile.txxt set paagesizze 3000 set liinesizze 3000 selectt * frrom emmp; selectt * frrom deept; spool off； 执行完毕后，可可以在C盘中中找到SpooolFille.txtt,打开后可可

22、以看到通过过Spooll假脱机命令令转向到该文文件中的内容容。记录类型定义一个 REECORD 类型 TYPE IIS RECCORD( fieldnname1 NOOT NULLL : fieldnnameN NOOT NULLL);属性的类型%TYPE引用数据库库列%ROWTYPPE代表表中中的行%type和%rowtyype 可以以用来指定列列的数据类型型SName StudIInfo.SStudNaame%Tyype;Stud_Reec SttudInffo%ROWWTYPE;Type VIIPStuddRec iis reccord ( VVIPID Varcchar2(15), V

23、VIPNamme varrchar22(20) );声明记录类型变变量StudRecc VIPSStudReec;引用字段的语法法是recordnname.ccolumnnname 用于给列赋值的的语法是recordnname.ccolumnnname: = exxpresssion; 语法 SELECT INTO FROM WHHERE ;SELECT StudNNo,StuudNamee INTO SttudRecc FROMM StuddInfoWHERE SStudNoo=990700470;fetch mmycur into StudRRec ;TID TeaacherIInfo.T

24、TeacheerNo%TType;TName TTeacheerInfoo.TeaccherNaame%tyype;游标CURSOR IS;CURSOR mycurr is sselectt TeaacherNNo,TeaacherNName FFrom TTeacheerInfoo Wheere TeeacherrBirthhDay=To_Daate(11981-22-5,YYYY-MM-DDD);控制显式游标OPENFETCHCLOSE在使用 OPEEN、 CCLOSE 或 FETTCH 语句句引用这些语语句之前必须须对游标进行行声明OPEN open myycur;FETCH INTO

25、var1, , vvarN;ORFETCH INTO recorrd_varriablee;fetch mmycur into TID,TTName;每个游标有四个个属性可以用用于访问游标标的环境区域域%NOTFOUUND%FOUND%ROWCOUUNT%ISOPENN循环取记录 whille myccur%foound loopp ddbms_ooutputt.put_line(TID|TNAMEE); ffetch mycurr intoo TID,TNamee; end loop;CLOSE ; cloose myycur;表CREATE TABLEE schhema.tablee (c

26、columnn dataatype DEFAAULT eexpr, .);指定：表名列名、列数据类类型和列的大大小数据类型VARCHARR2(sizze)可变长度的的字符数据长长度可以到44000字节节CHAR(siize) 固定长度的的字符数据NUMBER(p,s) 可变长度的的数字数据DATE 日日期和时间值值NCHAR、NNVARCHHAR2，国国家字符集，与与环境变量NNLS指定的的语言集密切切相关，使用用方法和CHHAR、VAARCHARR2相同。在创建表时，为为一个列指定定一个默认值值create tablee c (cc1 chaar(10) DEFAAULT 1111) alt

27、er ttable test add ssyd daate deefaultt sysddate; alter tablle tt moodify(ccc defaault cccc ); alter tablle tt moodify(ccc defaault nulll); INSERT INTO deparrtmentts (depaartmennt_id, depaartmennt_namme, maanagerr_id) VALUES (300, Enggineerring, DEFAAULT);UPDATE deparrtmentts SET mannager_id = DEFAU

28、ULT WHHERE ddeparttment_id = 10;约束类型NOT NULLLUNIQUE PRIMARYY KEYFOREIGNN KEYCHECK 列级约束column CONSSTRAINNT connstraiint_naame cconstrraint_type,表级约束column,. CONSSTRAINNT connstraiint_naame cconstrraint_type (coluumn, .),NOT NULLL约束只能定义在列级级,不能定义义在表级.防防止NULLL值进入指定定的列,在单单列基础上定定义,默认情情况下,ORRACLE允允许在任何列列中有

29、NULLL值CREATE TABLEE emplloyeess( empployeee_id NUMMBER(66), lasst_namme VARRCHAR22(25) NOT NNULL, sallary NUMMBER(88,2), commmissiion_pcct NUMMBER(22,2), hirre_datte DATTE CONNSTRAIINT emmp_hirre_datte_nn NOTT NULLL,. UNIQUE 约束用来保护一个表表中的一个或或者多个列没没有任何两行行在收到保护护的列中具有有重复的数据据.ORACCLE在唯一一键列上自动动生成一个唯唯一索引以实

30、实现唯一性CREATE TABLEE emplloyeess( empployeee_id NNUMBERR(6) uuniquee, lasst_namme VVARCHAAR2(255) NOTT NULLL, emaail VVARCHAAR2(255), sallary NNUMBERR(8,2), commmissiion_pcct NNUMBERR(2,2), hirre_datte DDATE NNOT NUULL,. CONNSTRAIINT emmp_emaail_ukk UNIQQUE(emmail);PRIMARYY KEY 约束唯一键的所有特特征都适用于于主键约束,只是

31、在主键键列中不允许许有NULLL值CREATE TABLEE deepartmments( deppartmeent_idd NUUMBER(4), emaail VVARCHAAR2(255) , loccationn_id NUUMBER(4), CCONSTRRAINT dept_id_pkk PRIMMARY KKEY(deepartmment_iid);FOREIGNN KEY 约束通过使用公共列列在表之间建建立一种父子子(pareent-chhild)关关系,在表上上定义的外部部键可以指向向主键或者其其他表的唯一一键.CREATE TABLEE emplloyeess( emppl

32、oyeee_id NNUMBERR(6), . deppartmeent_idd NNUMBERR(4), CONNSTRAIINT emmp_deppt_fk FOREIIGN KEEY (deepartmment_iid) RREFEREENCES deparrtmentts(deppartmeent_idd),CHECK 约约束检查在约束中指指定的条件是是否得到了满满足.create tablee emplloyeess (sal nuumber(7,2) constraaint eemp_saal_ck11 checck (saal 0) ) create tablee y(id va

33、rcchar2(7) check (asciii(subsstr(idd,1,1) bettween 65 annd 90 and ssubstrr(id,11,1) iis nott nulll andd asciii(subbstr(iid,2,11) beetweenn 65 aand 900 and substtr(id,2,1) is noot nulll annd asccii(suubstr(id,3,1) bbetweeen 65 and 990 andd subsstr(idd,3,1) is nnot nuull aand asscii(ssubstrr(id,44,1)

34、betweeen 655 and 90 annd subbstr(iid,4,11) is not nnull and aascii(substtr(id,5,1) betwween 665 andd 90 aand suubstr(id,5,1) iss not null );CREATE TABLEE TTT( deppartmeent_idd NUUMBER(4) chheck(ddeparttment_id liike ttrj%), emaail VVARCHAAR2(255) PRIIMARY KEY );CREATE TABLEE TTT( deppartmeent_idd NU

35、MBEER(4) , emaail VVARCHAAR2(255) PRIIMARY KEY, STUUDSEX VARCHHAR2(22) CHEECK (SSTUDSEEX IN (男,女);添加一个新行到到表中INSERT INTOtablee (coolumn , coolumn.)VALUES(valuue , valuee.);INSERT INTOdeparrtmenttsVALUES(100, Finnance, NULLL, NUULL,SYYSDATEE,TO_DDATE(FEB 33, 19999, MON DDD, YYYYY);改变表中的数据据UPDATEtablee

36、SETcoolumn = vallue , coluumn = valuee, .WHERE condiition;UPDATE copy_empSET deparrtmentt_id = 110WHERE deparrtmentt_namee=HELLLO;从表中删除行DELETE FROMM tabbleWHERE connditioon; DELETEE FROMM depaartmennts WHERE depaartmennt_namme = Finannce;视图视图是一个 SSQL 查询询，它永久存存在于数据库库中，并被赋赋予了一个名名称,视图是是从基表中生生成的虚拟表表 CRE

37、ATE OR RREPLACCE FFORCE|NOFORRCE VVIEW vview (aliias, aliass.) AS subbqueryyWITH CCHECK OPTIOON COONSTRAAINT cconstrraintWITH RREAD OONLY CONSTTRAINTT consstrainnt;with chheck ooptionn:表名只有有子查询检索索的行才能被被插入、删除除、更新with reead onnly:说明明只能对基表表中的行进行行只读访问onstraiint_naame:指定定with checkk optiion或reead onnly约束

38、的的名称。CREATE VIEW salvuu50 AS SELLECT emplooyee_iid ID_NUMBEER, laast_naame NAAME, salarry*12 ANN_SSALARYY FROOM emplooyees WHEERE deparrtmentt_id = 50;SELECT *FROMsaalvu500;内建视图是一个个带有别名(或相关名) 的可以在在SQL 语语句中使用的的子查询Top-N 分分析SELECT ROWNUUM as RANK, lastt_namee, sallary FROM (SELECCT lasst_namme,sallary FFROM eemployyees ORDERR BY ssalaryy DESCC) AWHERE RROWNUMM 80000GROUP BBY deppartmeent_idd;综合知识1学生信息表表（StuddInfo） 字段名称数据类型字段长度是否为空PK