Oracle實(shí)戰(zhàn)練習(xí)

1、Oracle 實(shí)戰(zhàn)練習(xí)用檢用戶號 2002026364工單 2000739486命名規(guī)則：type_ 姓名全拼 _題號（如同一題內(nèi)有多個同type ，則以 A,B,C 區(qū)分）type:序列號： seq快表： qt表:t觸發(fā)器： tri函數(shù) :f存儲過程 :p數(shù)據(jù)鏈： dl同義詞： syn復(fù)習(xí)回顧：1 、 notin 、notexists 、in 、exists ，from 表的順序2 、外部連接 （+）3、子查詢4 、構(gòu)建虛表5 、排序 （ 統(tǒng)計(jì)）6 、分組7 、字段加函數(shù)8 、偽列 rowidrownum9、快表10 、并集、與集等11 、調(diào)試方法： test(pl/sqldeveloper

2、)/pub_error_log/dbms_output12 、觸發(fā)器、序列、 JOB練習(xí)題：地址同為例，1 、建立快表，字段只有三個，序號，戶名，地址，序號為number ，戶名、cim_customer 的 user_name,address 字段Createtablet_tom_1asSelect1no,user_name,addressFromcim_customerWhere1=22 、庫表記錄超過 5000 記錄，如何限制只取 5000 條，以 select*fromcim_customer完善 SQL ；select*fromcim_customerwhererownum<=

3、5000createtabletasselect*fromall_objectswhererownum<=5000;settimingonselectobject_name,object_idfrom(selectobject_name,object_idfromtwhereobject_namelike'%'c)whererownum<=6;selectobject_name,object_idfrom(selectobject_name,object_idfromtsample(1)whereobject_namelike'%'orderbydb

4、ms_random.random)whererownum<=6;bd1.html（不能對 ROWNUM 使用 （大于 1 的數(shù)值）、= （大于或等于 1 的數(shù)值）、= （大于或等 于 1 的數(shù)值），否則無結(jié)果）3 、建立序列 sqeuence ，最小值 100 ，最大值 10000 ，步長為 2，每次緩存取 10 條 createsequenceseq_tom_3minvalue100maxvalue10000startwith100incrementby2cache10order;4 、建立函數(shù)，實(shí)現(xiàn)將 cim_customer 前 10 條記錄插入到快表，序號采用序列號實(shí)現(xiàn) crea

5、tefunctionf_tom_4returnnumberasv_retnumber;beginv_ret:=-10;insertintot_tom_1（no,user_name,address） selectseq_tom_3.nextval,user_name,addressfromcim_customerwhererownum=10;commit;v_ret:=0;returnv_ret;exceptionwhenothersthenrollback;dbms_output.put_line('er!');returnv_ret;end;declarev_rnumber;

6、beginv_r:=f_tom_4;dbms_output.put_line(to_char(v_r);end;5 、電 費(fèi) 銀 行 收 費(fèi) 統(tǒng) 計(jì) ， 使 用 decode 統(tǒng) 計(jì) 顯 示 四 大 銀 行 的 收 費(fèi) 金 額 ， 庫 表 ： sf_charge_record, 四大銀行對應(yīng)： collector_id='001' (中行) ,'002', (工行) ,'003', (建行) ,'004', (農(nóng)行)，統(tǒng)計(jì)限制 pay_date 在 20080501,20080601) 之間，單位： business_place_

7、codelike'13020201%' 收費(fèi)方式： money_to='B'selectdecode(collector_id,'001',' 中行 ','002',' 工行 ','003',' 建行 ','004',' 農(nóng)行 ','其他 ') 銀 行,sum(fact_money) 電費(fèi) ,sum(fact_punish) 違約金fromsf_charge_recordawherepay_date>=to_dat

8、e('2008-5-1','yyyy-mm-dd') andpay_date<to_date('2008-6-1','yyyy-mm-dd') andbusiness_place_codelike'13020201%' anda.money_to='B'andcollector_idin('001','002','003','004')groupbycollector_id;6 、業(yè)擴(kuò)檔案：計(jì)量點(diǎn) (cim_agreement),

9、 表位置( cim_meter_position )具有一一對應(yīng)關(guān)系，也就是說計(jì)量點(diǎn)庫表中存在用戶和計(jì)量點(diǎn)屬性(字段) ，表位置也存在對應(yīng)的用戶 和計(jì)量點(diǎn)屬性(字段) 。試著找出計(jì)量點(diǎn)庫表中有，而表位置沒有的用戶、計(jì)量點(diǎn)信息 (反過來找出表位置庫表有， 而計(jì)量點(diǎn)庫表沒有的用戶、 計(jì)量點(diǎn)信息)(用兩種方法 (: + )， notexists )selecta.*,b.* fromcim_agreementa,cim_meter_positionbwherea.user_no=b.user_no(+)anda.fee_no=b.group_no(+)andb.user_noisnullselect

10、a.* fromcim_agreementawherenotexists(select1fromcim_meter_positionbwherea.user_no=b.user_noanda.fee_no=b.group_no)7 、電費(fèi)有個規(guī)則： 按戶匯總： 應(yīng)收 - 實(shí)收 = 欠費(fèi)，應(yīng)收為 df_money_files 的 should_money, 實(shí)收為 sf_charge_record 的 fact_money, 欠費(fèi)為 sf_arrearage 的 owe_money, 試找出 mon=200802 月份， business_place_code= 13220201 '，存

11、在不滿足規(guī)則的記錄 數(shù) selectuser_no,sum(should_money),sum(fact_money),sum(owe_money) from(selectuser_no,sum(should_money)should_money,0fact_money,0owe_money fromdf_money_fileswheremon=200802-anduser_no='6014001547'andbusiness_place_code='13220201'groupbyuser_nounionall selectuser_no,0should_mo

12、ney,sum(fact_money)fact_money,0owe_money fromsf_charge_recordwheremon=200802-anduser_no='6014001547'andbusiness_place_code='13220201'groupbyuser_nounionallselectuser_no,0should_money,0fact_money,sum(owe_money)owe_moneyfromsf_arrearagewheremon=200802-anduser_no='6014001547'and

13、business_place_code='13220201'groupbyuser_no)tgroupbyuser_nohavingsum(should_money)-sum(fact_money)<>sum(owe_money)-havingabs(sum(should_money)-sum(fact_money)-sum(owe_money)>0.108 、編寫存儲過程，要求存儲過程體內(nèi)有兩個子beginend ，第一個 beginend 沒有exception 處理，第二個、存儲過程主體有 exception( 分別輸出” err2! ”，“ err0!

14、 ”。 每個 beginend 主體內(nèi)語句自由編寫。出錯語句處理：存儲過程變量定義 v_user_namevarchar2(8); 出錯語句： selectuser_nameintov_user_namefromcim_customerwherecustomer_id='5014002956'將出錯語句分別放在第一個 beginend 中，第二個 beginend 中，存儲過程主體 beginend 中，輸出分別是什么出錯語句位置提示第一個beginend中這里輸入結(jié)果第二個beginend中這里輸入結(jié)果存儲過程主體begi nend 中這里輸入結(jié)果把錯誤輸出改為 pub_er

15、ror_log錯誤陷進(jìn)捕獲表形式格式：(v_resule nu mber )P_pub_error_log(v_result,sqlcode,sqlerrm,存儲過程名);createorreplaceprocedurep_tom_8isv_user_ namevarchar2(8);v_datedate;beginbeginselectsysdate in tov_datefromdual;en d;beginselectsysdate in tov_datefromdual;excepti onwhe no thersthe ndbms_output.put_li ne('er2!

16、');en d;selectuser_ name in tov_user_ namefromcim_customerwherecustomer_id='5014002956：dbms_output.put_li ne('ok!');excepti onwhe no thersthe ndbms_output.put_li ne('er 0!');p_pub_error_log(v_ret,sqlcode,sqlerrm,'p_tom_8');en d;declarebeginp_tom_8;en d;9、觸發(fā)器：建立快表，字段只有

17、三個，序號，戶名，地址，序號為number，戶名、地址同cim_customer 的user_name,address字段，在1、的庫表上建立 update 觸發(fā)器，將update前的序號，戶名，地址行數(shù)據(jù)都插入到新建立的快表中。(通過pl/sql對快表某行三個字段都做修改并保存)。INSERTUPDATEDELETE:OLDVV:NEWVVcreateorreplacetriggertri_tom_10beforeupdate on t_tom_1foreachrowdeclarebegininsertin tot_tom_10 (no ,user_ name,address)values(

18、:old. no,:old.user_ name,:old.address);en dtri_tom_10;10、上下級關(guān)系：依據(jù)庫表rs_dept (字段：dept_id,upper_dept_id )查找13221202 的所有上級，查找 1303的所有下級Oracle數(shù)據(jù)庫中Startwith.connectbyPrior 是一個遍歷一棵樹型表的遞歸函數(shù)， Level 就是一個樹中，各節(jié)點(diǎn)的層數(shù)。向下查詢一棵樹（知道一棵子樹的根，把下面的所有節(jié)點(diǎn)遍歷出來）：selectidfromtab_namestartwithid= 要查詢的根節(jié)點(diǎn)connectbypriorid=fatherid;

19、向上（倒查）查詢一棵樹（知道一棵子樹的末節(jié)點(diǎn)，把這個末節(jié)點(diǎn)的的所有上級（一至到整棵樹的根節(jié)點(diǎn)）遍歷出來）：selectidfromtab_namestartwithid= 要查詢的末節(jié)點(diǎn)connectbypriorfatherid=id;tmlselectdept_code,dept_namefromrs_deptstartwithdept_code='13221202'connectbyPriorupper_dept_id=dept_idselectdept_code,dept_namefromrs_deptstartwithdept_code='130302'connectbyPriordept_id=upper_dept_id11 、建立一個 JOB 把 8 、內(nèi)容做成每周五晚上 11 點(diǎn)執(zhí)行的一個任務(wù)12 、在 hzyd 當(dāng)前用戶下建立全局同義詞（快表1：假如： t_tom ），通過 hyyd/j2yd 登錄，可以通過直接輸入 select*fromt_tom_1 進(jìn)行訪問13 、 EXP/IMP 支持列表EXP/IMP 一般 EXPORT 時使用低版本， IMPORT 時使用與目標(biāo)庫相同的