20個案例掌握PL SQL 基礎(chǔ)

20個案例掌握PL/SQL 基礎(chǔ) 有MS SQL基礎(chǔ),學習了兩周多的PL/SQL,做了一些事例,但是很多信息在網(wǎng)上難以找到太多正確的答案,看到一篇又一篇的PL/SQL博文,案例方面的博文一篇又一篇的雷同,一看就是是Ctrl+C的復制.給一些博主留言希望得到解答,但是等到卻是我也是復制來的,具體的沒測試. 狠心之下,花了不少時間學習.做了一些例子,搞定之余,留下點供參考的例子. 閱讀本篇博文之前,建議你學習一下基礎(chǔ),這是推薦的兩個PL/SQL博客,EricHu 胡勇:Oracle編程詳解 =傳送門liulun:PL/SQL學習筆記(索引帖)=傳送門測試:SQL Navigator 3+Oracle;遠程連接用以下案例來總結(jié) PL/SQL相關(guān)知識:1.將2010-12-06轉(zhuǎn)換成Dec-06-2010的格式.錯解:1 select to_char(to_date(2010-12-06,yyyy-mm-dd),mon-dd-yyyy) from dualPS:如果安裝的系統(tǒng)默認語言為EN,那么這種執(zhí)行結(jié)果正確,但是若是CN那就錯了.所以,需要設(shè)置一下顯示的語言才能保證Dec的出現(xiàn).正解:1 select to_char(to_date(2010-12-06,yyyy-mm-dd),mon-dd-yyyy,NLS_DATE_LANGUAGE=American) from dual2.PL/SQL中常用round函數(shù),trunc函數(shù),instr函數(shù),substr函數(shù)的區(qū)別.正解:(1)round(x,y)以小數(shù)點右邊第y位四舍五入x;(2)trunc(x,y)舍去小數(shù)點右邊第y位以后的數(shù)字;(不要去看網(wǎng)上的所謂負數(shù)出現(xiàn)的解釋,沒什么用,以后出現(xiàn)的機會也不會存在)(3)instr(str1,str2,num) Str1:原字符串,str2要查找的字符串,num第幾次出現(xiàn) 返回要查找的str2在原字符串str1中第num次出現(xiàn)的位置,若不存在,則返回0;(4)substr(str1,num1,num2)截取字符串str中 從num1位開始之后的num2個,num1為負數(shù)時反向3.外連接與內(nèi)連接:正解:oracle外連接中左.右.全連接的區(qū)別=傳送門4.如何刪除重復的記錄.錯解:1 select * from 表 where Id in (select Id from 表 group by Id having count(Id) 1)PS:仔細看了一下代碼,自己居然粗心地把所有重復記錄數(shù)據(jù)的都按 id 刪除了,但是是不是該保留一行呢?!正解: 1 delete from table 2 where id in 3 ( 4 select id from table 5 group by id 6 having count(id)1 7 and rowid not in 8 ( 9 select min(rowid) from table10 -記住oracle中獨有的標識列字段rowid,11 -查詢所有重復id但是不包括最小(min)的id,刪除之;最大的效果同理.12 -但是是不是還有個問題,id必須是遞增而且是設(shè)為主鍵的,要不然,這道例子.13 )14 )5.返回今天星期幾(5.1返回 星期五;5,2返回Friday)正解:5.11 select to_char(sysdate,day,nls_date_language=simplified chinese) from dual5.21 select to_char(sysdate,day,nls_date_language=American) from dualPS:這個和和第一題類似.注意5.1的simplified chinese后面的3個單引號,因為是字符串.所以在simplified chinese要加上單引號simplified chinese,但是因為兩個單詞中間有空格,再加上一層simplified chinese,再有一個單引號就是轉(zhuǎn)義符,變成了 simplified chinese.反正有點混亂,看我在論壇問的這個解答:/topics/3904397566.返回當前月的最后一天.正解:1 select last_day(add_months(sysdate,0) from dualPS:如果把sysydate加法或者減法,結(jié)果就是求出加法或者減法之后的月份的最后一天7.使用Oracle自帶函數(shù)實現(xiàn)輸入5.5,分別得到6和5;正解:1 select round(5.5,0) from dual-得到62 select trunc(5.5,0) from dual-得到5PS:參看第2題已有解釋.8.給現(xiàn)有日期加上2年.正解:1 select add_months(sysdate,24) from dualPS:天真的以為add_years存在,試了試,居然沒有這個函數(shù).9.搜索出 users 表中工號以 s 開頭的,前 10 條記錄.錯解:select * from users where id like S* and rownum=10PS:沒有*吧,以前在Windows系統(tǒng)中搜索文件常用* ?等,結(jié)果現(xiàn)在忘了是在寫PL/SQL.慣例,rownum是Oracle特有.正解:1 select * from users where id like S% and rownum=1010.插入全年日期進入mgs_psd_report表F1欄.正解:測試表test4,類似mgs_psd_report表.創(chuàng)建test4表語句(我在這里創(chuàng)建test4以備語句測試,實際上一樣,不要拘泥于具體的表名): 1 -(實驗表test4 2 select * from test4 3 drop table test4 4 create table test4 5 ( 6 F1 number, 7 F2 varchar(20), 8 F3 number 9 ) 10 -)插入語句(mgs_psd_report): 1 -存儲過程.以前存儲過程真是沒怎么寫過,現(xiàn)在補習雖然說有點不習慣.不過還好,基本格式對了,居然寫出來了. 2 create or replace procedure Sp_ShowDate(v_year in varchar2) 3 as 4 v_datecount number:=0;-從0開始,因為要算上第一天 5 v_datelength number; 6 v_datestart date;-第一天 7 v_dateend date;-最后一天 8 begin 9 -select to_char(sysdate,yyyy)|0101 into v_datestart from dual10 select to_date(v_year|0101),yyyymmdd) into v_datestart from dual;-第一天11 select to_date(v_year|1231),yyyymmdd) into v_dateend from dual;-最后一天12 13 select v_dateend-v_datestart into v_datelength from dual;-不加1,因為第一天加上364或者365相當于365或者366天14 15 while v_datecount=v_datelength loop16 insert into mgs_psd_report(F1) values(to_char(to_date(to_char(v_year|0101),yyyymmdd)+v_datecount,yyyymmdd);17 v_datecount:=v_datecount+1;18 end loop;19 end;20 -執(zhí)行.以2012年為例21 begin22 Sp_ShowDate(2012);23 end;PS:居然還真對了,蠻高興的.11.寫一個存儲過程,更新上一題中的F2欄位(可以見我上題創(chuàng)建的test4表語句),更新所有的.要求:若當天星期六.星期日為N; 5月1日到5月3日,10月1日到10月3日為N2; 其他日期為P.錯解就不貼了,有點長.正解: 1 -創(chuàng)建 2 create or replace procedure Sp_UpdateDate(v_year in varchar2)-年份 3 as 4 v_datecount number:=0;-從0開始,因為要算上第一天 5 v_datelength number;-總天數(shù) 6 v_datestart date;-第一天 7 v_dateend date;-最后一天 8 v_datetemp varchar2(20);-日期 9 v_datetemp2 varchar(20);-星期幾10 begin11 select to_date(v_year|0101),yyyymmdd) into v_datestart from dual;-第一天12 select to_date(v_year|1231),yyyymmdd) into v_dateend from dual;-最后一天13 select v_dateend-v_datestart into v_datelength from dual;-不加1,因為第一天加上364或者365相當于365或者366天14 15 while v_datecount16 1 -函數(shù) 2 create or replace function fun_10to16(v_num in number) 3 return varchar2 4 as 5 v_temp varchar2(20); 6 begin 7 select to_char(v_num,xxxxx) into v_temp from dual; 8 return v_temp; 9 end fun_10to16;10 -執(zhí)行11 declare12 v_test number:=16;13 v_temp varchar(20):=;14 begin15 v_temp:=fun_10to16(v_test);16 dbms_output.put_line(v_temp);17 end; 18 select fun_10to16(16) from dual16=10 1 -函數(shù) 2 create or replace function fun_16to10(v_num in varchar2) 3 return varchar2 4 as 5 v_temp varchar2(20); 6 begin 7 select to_number (v_num,xxxxx) into v_temp from dual; 8 return v_temp; 9 end fun_16to10;10 -執(zhí)行 11 select fun_16to10(1E) from dualPS:這個沒多大難度,但是不能忘了function的寫法步驟.14.編寫一個函數(shù),實現(xiàn)加減乘除,要求有異常處理.正解: 1 create or replace function fun_getresult(v_num1 in number,v_num2 in number,v_symbol in varchar2) 2 return number 3 as 4 ex_error exception; 5 v_temp number; 6 begin 7 if v_symbol=+ then 8 v_temp:=v_num1+v_num2; 9 return v_temp;10 end if;11 if v_symbol=- then12 v_temp:=v_num1-v_num2;13 return v_temp;14 end if;15 if v_symbol=* then16 v_temp:=v_num1*v_num2;17 return v_temp;18 end if;19 if v_symbol=/ then20 if v_num2=0 then21 raise ex_error;22 else23 v_temp:=v_num1/v_num2;24 return v_temp;25 end if;26 end if;27 exception28 when ex_error then29 dbms_output.put_line(o cannot be used here!);30 end;31 32 -執(zhí)行33 select fun_getresult(12,3,/) from dualPS:開始寫的時候沒有異常處理,只用了 if 判斷被除數(shù)是否為0,這個就不算是異常處理了.15.寫一個觸發(fā)器,操作一個表(emp_info)時,向另一個表(emp_info_bk)插入操作的內(nèi)容.測試向其插入 , | 字符。正解: 1 -創(chuàng)建 2 create or replace trigger tr_replace 3 before insert or update or delete 4 on emp_info 5 for each row 6 begin 7 insert into emp_info_bk values(:new.creator,:new.creation_date,:new.id,:,:new.address); 8 end tr_replace; 9 -執(zhí)行10 insert into emp_info values(23,test22,test22,to_date(20130426,yyyymmdd),dong2)11 insert into emp_info values(24,|,to_date(20130426,yyyymmdd),dong3)PS:觸發(fā)器,寫的更少了,誒.-select | from dual|正常引到引號中，就是字符了。而單引號，需要前邊再加一個單引號轉(zhuǎn)義。 四個單引號，前后兩個表示字符串兩端的單引號，中間部分是字符串。而中間有兩個單引號，第一個是轉(zhuǎn)義字符，表示把第二個轉(zhuǎn)成字符串的單引號。第二個，就是外圍兩個單引號引住的實際的字符串的單引號。16.用一條sql實現(xiàn)以下轉(zhuǎn)換如student subject grade-student1 語文 80student1 數(shù)學 70student1 英語 60student2 語文 90student2 數(shù)學 80student2 英語 100.轉(zhuǎn)換為: 語文 數(shù)學 英語student1 80 70 60student2 90 80 100正解:select student 姓名,sum(decode(subject,語文,grade,null) 語文,sum(decode(subject,數(shù)學,grade,null) 數(shù)學,sum(decode(subject,英語,grade,null) 英語from teststugroup by studentPS:decode用法,要注意了.17.調(diào)用sen_email過程把某個數(shù)據(jù)發(fā)送到正解:create or replace procedure Sp_SendMyEmail(v_From in varchar2,v_To in varchar2,v_Subject in varchar2,v_Body in varchar2)as v_Cc VARCHAR2(20) := NULL; v_Bcc VARCHAR2(20):= NULL; v_ContentType VARCHAR2(40) := text/plain;charset=gb2312; v_MailIp VARCHAR2(20) := 這里是服務(wù)器的IP地址xx.xxx.xx.x; v_Port NUMBER := 25;begin send_email(v_From,v_To,v_Subject,v_Body,v_Cc,v_Bcc,v_ContentType,v_MailIp,v_Port);end Sp_SendMyEmail;-執(zhí)行begin Sp_SendMyEmail(2,,test1,11111111111111111111111111111111);end;PS:提示錯誤:ORA-29278: SMTP transient error: 421 Service not available.說明這個寫的正確,另外想問一點:如果 v_Cc VARCHAR2(20) := NULL; v_Bcc VARCHAR2(20):= NULL; v_ContentType VARCHAR2(40) := text/plain;charset=gb2312; v_MailIp VARCHAR2(20) := xx.xxx.x.x; v_Port NUMBER := 25;在存儲過程中已經(jīng)初始化了,但是存儲過程傳遞的參數(shù)中還有這些變量,難道必須得我這么做,在外面調(diào)用時候還得初始化一下?不然怎么傳參?求解.18.列出總分成績處于第5位的學生;另寫一個sql語句得到大于或者等于80的為優(yōu)秀,大于或者等于60的為及格,小于60分顯示不及格Stu 數(shù)學 語文 化學student1 50 100 99student2 80 60 100student3 60 70 20student4 90 80 80student5 100 67 85student6 100 77 81.正解:select * from( select rownum id,stu from( select stu,sum(yw+sx+hx) result from test3 group by stu order by result asc )where id=5-因為數(shù)據(jù)中沒有rownum這個列,不能直接寫出rownum=5這樣的查詢,所以為了可以使用rownum,不斷查詢,把rownum保存入id用來1 select stu,2 case when sx80 then 優(yōu)秀 else 及格 end) end as sx,3 case when yw80 then 優(yōu)秀 else 及格 end) end as yw,4 case when hx80 then 優(yōu)秀 else 及格 end) end as hx5 from test319.寫一個函數(shù)傳送的值是:nameflyherworker_idS0135EMAIL 等 以+value形式的一串有規(guī)則的字符要求根據(jù)中的內(nèi)容得到value如果 輸入name,則得到flyher 輸入worker_id,則得到S0135正解: 1 -函數(shù) 2 create or replace function fun_getmystr(v_str in varchar2,v_input in varchar2)-v_str總字符,v_input查找字符 3 return varchar2 4 as 5 -v_strinput varchar2(10);-輸入字符 6 v_strlen number;-輸入字符串總長度 7 8 v_strinputlen number;-查找字符長度 9 v_strinputpos number;-查找字符串所在位置10 11 v_strtemp varchar2(100);-臨時字符串12 v_strend number;-下一個所在位置13 14 v_stroutput varchar2(20);-查找的結(jié)果15 begin16 select length(v_input),length(v_str) into v_strinputlen,v_strlen from dual;-傳入字符串總長度和查找字符所在位置17 -find position of v_input18 select instr(v_str,v_input,1) into v_strinputpos from dual;-傳入字符串第一次出現(xiàn)所在位置19 20 select substr(v_str,v_strinputpos+v_strinputlen,v_strlen-v_strinputpos-v_strinputlen+1) into v_strtemp from dual;-去掉前面的21 select instr(v_strtemp,1) into v_strend from dual;-查詢下一個 所在位置,若沒有返回022 if v_strend0 then-后面還有字符串23 select substr(v_strtemp,0,v_strend-1) into v_stroutput from dual;24 return v_stroutput;25 else26 return v_strtemp;27 end if;28 end fun_getmystr;29 -執(zhí)行30 select fun_getmystr(nameflyherworker_idS0135EMAIL,worker_id) from dualPS:實驗一下以下代碼,對instr,substr 分割字符串 將會是一個很好的掌握. select instr(nameflyherworker_idS0135EMAIL,worker_id,1) from dual; select substr(nameflyherworker_idS0135EMAIL,length(worker_id)+12,length(nameflyherworker_idS0135EMAIL)-12-length(worker_id)+1) from dual; select instr(flyherworker_idS0135EMAIL,1) from dual; select substr(flyherworker_idS0135EMAIL,0,6-1) from dual;20.寫一個函數(shù),將數(shù)字人民幣金額轉(zhuǎn)為大寫 (a)輸入?yún)?shù)為數(shù)字,如 123456789 輸入?yún)?shù)為數(shù)字,如 一億二千三百四十五萬六千七百八十九 (b)輸入?yún)?shù)為數(shù)字,如 123456789.01 輸入?yún)?shù)為數(shù)字,如 一億二千三百四十五萬六千七百八十九元一角 (c)輸入?yún)?shù)為數(shù)字,如 10023 輸入?yún)?shù)為數(shù)字,如 一萬零二十三元錯誤: 1 create or replace function fun_tra(v_num1 in number) 2 return varchar2 3 as 4 v_num2 varchar2(20):=to_char(v_num1);-初始化 5 v_temp varchar2(20);-數(shù)字大小寫轉(zhuǎn)換 6 -v_tempout varchar(20);- 7 v_tempdol varchar(10);-人民幣判斷 8 v_count1 number:=0;-當輸入為小數(shù)時 9 v_count2 number:=0;-當輸入為小數(shù)時10 -v_show varchar(40);-11 v_count number:=length(v_num2);-輸入數(shù)字位數(shù)12 13 begin14 select reverse(v_num2) into v_num2 from dual;-反轉(zhuǎn)15 if instr(v_num2,.)0 then-帶小數(shù)部分,?16 while v_count0 loop17 select substr(v_num2,v_count,1) into v_temp from dual;18 if v_temp=. then v_count1:=v_count-1 ; v_count2:=length(v_num2)-v_count;end if;-記住小數(shù)點之前和之后的長度19 v_count:=v_count-1;20 end loop;21 while v_count10 loop22 select substr(v_num2,v_count,1) into v_temp from dual;23 if v_temp=1 then v_temp:=一;end if;24 if v_temp=2 then v_temp:=二;end if;25 if v_temp=3 then v_temp:=三;end if;26 if v_temp=4 then v_temp:=四;end if;27 if v_temp=5 then v_temp:=五;end if;28 if v_temp=6 then v_temp:=六;end if;29 if v_temp=7 then v_temp:=七;end if;30 if v_temp=8 then v_temp:=八;end if;31 if v_temp=9 then v_temp:=九;end if;32 -if v_temp=. then v_temp:=角;end if;33 -if v_count=length(v_num2)+1 then v_temp:=元;end if;34 if v_count=1+v_count1 then v_tempdol:=元;end if;35 if v_count=2+v_count1 or v_count1=6+v_count1 then v_tempdol:=十;end if;36 if v_count=3+v_count1 or v_count1=7+v_count1 then v_tempdol:=百;end if;37 if v_count=4+v_count1 or v_count1=8+v_count1 then v_tempdol:=千;end if;38 if v_count=5+v_count1 then v_tempdol:=萬;end if;39 if v_count=9+v_count1 then v_tempdol:=億;end if;40 v_count:=v_count-1;41 dbms_output.put(v_temp);42 dbms_output.put_line(v_tempdol);43 end loop;44 dbms_output.put_line(角);45 else-不帶小數(shù)部分讀取46 while v_count0 loop47 select substr(v_num2,v_count,1) into v_temp from dual;48 if v_temp=1 then v_temp:=一;end if;49 if v_temp=2 then v_temp:=二;end if;50 if v_temp=3 then v_temp:=三;end if;51 if v_temp=4 then v_temp:=四;end if;52 if v_temp=5 then v_temp:=五;end if;53 if v_temp=6 then v_temp:=六;end if;54 if v_temp=7 then v_temp:=七;end if;55 if v_temp=8 then v_temp:=八;end if;56 if v_temp=9 then v_temp:=九;end if;57 if v_temp=. then v_temp:=角;end if;58 -if v_count=length(v_num2)+1 then v_temp:=元;end if;59 if v_count=1 then v_tempdol:=元;end if;60 if v_count=2 or v_count=6 then v_tempdol:=十;end if;61 if v_count=3 or v_count=7 then v_tempdol:=百;end if;62 if v_count=4 or v_count=8 then v_tempdol:=千;end if;63 if v_count=5 then v_tempdol:=萬;end if;64 if v_count=9 then v_tempdol:=億;end if;65 v_count:=v_count-1;66 dbms_output.put(v_temp);67 dbms_output.put_line(v_tempdol);68 end loop;69 end if;70 return null;71 end;72 73 declare74 v_num number:=213192312.2;75 v_show number;76 begin77 v_show:=fun_tra(v_num);78 -dbms_output.put_line(v_show);79 end;PS:我寫的一個,但是不能讀取含有小數(shù)的,而且還不能一行輸出,但是我感覺這樣的邏輯還算可以理解,這道題,頭大,真希望有個好的講解,誒.真是暈這個正確的參考了一下網(wǎng)上的,但是還是有點暈.看來我需要加強一下decode函數(shù).正確: 1 create or replace function fun_tra(v_num1 in number) 2 return varchar2 3 as 4 v_num2 varchar2(20):=to_char(v_num1);-初始化 5 v_temp varchar2(20);-數(shù)字大小寫轉(zhuǎn)換 6 -v_tempout varchar(20);- 7 v_tempdol varchar(10);-人民幣判斷 8 v_count1 number:=0;-當輸入為小數(shù)時 9 v_count2 number:=0;-當輸入為小數(shù)時10 -v_show varchar(40);-11 v_count number:=length(v_num2);-輸入數(shù)字位數(shù)12 13 begin14 select reverse(v_num2) into v_num2 from dual;-反轉(zhuǎn)15 if instr(v_num2,.)0 then-帶小數(shù)部分,?16 while v_count0 loop17 select substr(v_num2,v_count,1) into v_temp from dual;18 if v_temp=. then v_count1:=v_count-1 ; v_count2:=length(v_num2)-v_count;end if;-記住小數(shù)點之前和之后的長度19 v_count:=v_count-1;20 end loop;21 while v_count10 loop22 select substr(v_num2,v_count,1) into v_temp from dual;23 if v_temp=1 then v_temp:=一;end if;24 if v_temp=2 then v_temp:=二;end if;25 if v_temp=3 then v_temp:=三;end if;26 if v_temp=4 then v_temp:=四;end if;27 if v_temp=5 then v_temp:=五;end if;28 if v_temp=6 then v_temp:=六;end if;29 if v_temp=7 then v_temp:=七;end if;30 if v_temp=8 then v_temp:=八;end if;31 if v_temp=9 then v_temp