oracle最簡(jiǎn)單的學(xué)習(xí)筆記增刪改查PLSQL基本語法游標(biāo)函數(shù)存儲(chǔ)過程的實(shí)現(xiàn)

-----?創(chuàng)建序列?creat?eseq?uence?book?_idI?NCREM?ENTB?Y1-?-每次加?幾個(gè)?STAR?TWIT?H001?--從?1開始計(jì)數(shù)?N?OMAXV?ALUE?--不設(shè)?置最大值?NO?CYCLE?--一?直累加，不?循環(huán)?CACH?E10;??-----?-創(chuàng)建bo?oks表?creat?etab?lebo?oks(?books?_idv?archa?r2(10?00),?books?_name?varc?har2(?100),?pric?enum?ber,?qtyn?umber?,pub?varc?har2(?200)?);?-----?-修改bo?oks表的?字段al?tert?able?books?modi?fy(bo?oks_i?dnum?ber)?----?-----?----往?books?表中插入數(shù)?據(jù)ins?erti?ntob?ooks?value?s(boo?k_id.?nextv?al,'中?國文學(xué)1'?,39,1?2,'人民?文學(xué)');?inse?rtin?tobo?oksv?alues?(book?_id.n?extva?l,'中國?文學(xué)2',?30,32?,'人民文?學(xué)');?inser?tint?oboo?ksva?lues(?book_?id.ne?xtval?,'中國文?學(xué)3',5?9,22,?'清華大學(xué)?');i?nsert?into?book?sval?ues(b?ook_i?d.nex?tval,?'中國文學(xué)?4',33?,52,'?清華大學(xué)'?);in?sert?into?books?valu?es(bo?ok_id?.next?val,'?中國文學(xué)5?',99,?62,'電?子工業(yè)')?;--?-----?----跟?新book?s中的信息?upda?tebo?okss?etpr?ice=1?00wh?ereb?ooks_?id=1?-?-----?----按?出版社分組?查詢每個(gè)出?版社金額的?情況se?lect?pub,s?um(pr?ice*q?ty)f?romb?ooks?group?byp?ub?-----?-----?按出版社、?書籍名稱分?組查詢每個(gè)?出版社金額?的情況s?elect?pub,?books?_name?,sum(?price?*qty)?from?book?sgro?upby?pub,?books?_name?---?-----?--按出版?社、書籍名?稱分組查詢?每個(gè)出版社?金額的情況?>5?0sel?ectp?ub,bo?oks_n?ame,s?um(pr?ice*q?ty)f?romb?ooks?group?byp?ub,bo?oks_n?ameh?aving?sum(?price?)>50?----?-----?-查詢相同?出版社的記?錄數(shù)se?lect?pub,c?ount(?pub)?from?books?grou?pby?pubh?aving?coun?t(pub?)>1?---?--標(biāo)的內(nèi)?鏈接se?lect?eid,?ename?,six,?name?from?e,dw?here?a.id=?d.id?sele?ctei?d,en?ame,s?ix,na?mefr?ome?join?don?a.id=?d.id?----?-做外連接?sele?ctei?d,en?ame,s?ix,na?mefr?ome?join?don?a.id=?d.id(?+)--?--右外連?接sel?ecte?id,e?name,?six,n?amef?rome?join?don?a.id?(+)=d?.id?----無?關(guān)子查詢?selec?t*f?rome?wher?eid?in(s?elect?eid?from?d)-?---相關(guān)?子查詢s?elect?*fr?ome?where?idi?n(se?lect?eidf?romd?wher?eid=?d.id?andi?d='00?3')?selec?t*f?rome?wher?eid?noti?n(se?lect?eidf?romd?wher?eid=?d.id?andi?d='00?3')?----?-存在則顯?示sel?ect*?from?ewh?eree?xists?(sele?ctid?from?dwh?erei?d=d.i?d)-?----不?存在則顯示?sele?ct*?from?ewhe?reno?texi?sts(s?elect?idf?romd?wher?eid=?d.id)??-----?-----?-----?-----?---PL?SQL基本?語法---?-----?-----?-----?-----?-----?-----?-----?-----?-----?-----?-----?-----?-----?-----?-----?-----?-----?-----?-----?-----?---s?etse?rvero?utput?ons?ize1?0000?decla?rex?varch?ar2(1?00);?begin?x:='?This?is..?..';?DBMS_?OUTPU?T.PUT?_LINE?('xv?alue?is'|?|x);?end;?---?--if?elsi?fe?lsed?eclar?ea?numb?er;?bva?rchar?2(10)?;beg?in?a:=?2;?ifa?=1t?hen?b?:='A?';?elsif?a=?2the?n?b:=?'B';?el?se?b:?='C'?;e?ndif?;D?BMS_O?UTPUT?.put_?line(?b);e?nd;?----?-----?-----?--cas?edec?lare?an?umber?;b?varc?har2(?10);?begin?a?:=2;?ca?se?whe?na=?1th?en?b?:='?A';?wh?ena?=2t?hen??b:=?'B';?end?case?;D?BMS_O?UTPUT?.put_?line(?b);e?nd;?----?-----?-----?-----?-----?-PLSQ?L循環(huán)?-----?-----?-----?-----?-----?-----?-----?-----?----?----?--loo?pdec?lare?xn?umber?;beg?in?x:=?1;?loop?x?:=x?+1;??ifx?>3t?hen??exit;??endi?f;?DBM?S_OUT?PUT.p?ut_li?ne(x)?;e?ndlo?op;?DBMS?_OUTP?UT.pu?t_lin?e(x);?end;?---?-----?-----?-whil?edec?lare?xn?umber?;beg?in?x:=?1;?while?x>?3loo?p?x:=?+1;?D?BMS_O?UTPUT?.put_?line(?x);?end?loop;?DB?MS_OU?TPUT.?put_l?ine(x?);en?d;-?-----?-for?begi?nf?orx?in1?..10?loop?-----?-從小到大??DBMS_?OUTPU?T.put?_line?(x);?end?loop?;D?BMS_O?UTPUT?.put_?line(?'end?offo?rloo?p');?end;?begi?nf?orx?inr?evers?e1.?.10?loop-?-----?從大到小?D?BMS_O?UTPUT?.put_?line(?x);?end?loop;?DB?MS_OU?TPUT.?put_l?ine('?endo?ffor?loop?');e?nd;?---?-----?-----?---做標(biāo)?簽dec?lare?xn?umber?;beg?in?x:=?0;?<<rep?eat_l?oop>>?x?:=x?+1;?DBM?S_OUT?PUT.p?ut_li?ne(x)?;i?fx<?3th?en?got?orep?eat_l?oop;?end?if;?end;?-?-----?-----?-----?excep?tion?處理---?-----?-----?-----?-----?-----?-----?----?decla?re?test?varch?ar2(1?00);?begin?se?lect?books?_name?into?test?from?book?swhe?rebo?oks_i?d=1?;D?BMS_O?UTPUT?.put_?line(?test)?;e?xcept?ion?when?no_d?ata_f?ound?then?D?BMS_O?UTPUT?.put_?line(?'沒有找到?數(shù)據(jù)');??end;?--?-----?----自?定義異常?decla?re?test?varch?ar2(1?00);?ee?xcept?ion;?begin?se?lect?books?_name?into?test?from?book?swhe?rebo?oks_i?d=1?;i?ftes?t<>?'中國文學(xué)?1'th?en?rai?see;?en?dif;?DB?MS_OU?TPUT.?put_l?ine(t?est);?exce?ption?wh?ene?then?D?BMS_O?UTPUT?.put_?line(?'不是需要?的書籍名稱?');e?nd;?----?-----?-----?-----?----記?錄的聲明-?-----?-----?-----?-----?-----?-----?decl?are?type?myrec?ordi?srec?ord(?bname?varc?har2(?100),?bpub?varc?har2(?100)?);re?al_re?cord?myrec?ord;?begin?sel?ectb?ooks_?name,?pubi?ntor?eal_r?ecord?from?book?swhe?rebo?oks_i?d=1;?DBMS_?OUTPU?T.put?_line?(real?_reco?rd.bn?ame|?|rea?l_rec?ord.b?pub);?end;?dec?lare?typ?emyr?ecord?isr?ecord?(?bnam?eboo?ks.bo?oks_i?d%typ?e,--?-----?-----?---聲明?的字段和表?中的字段類?型一樣?bp?ubv?archa?r2(10?0));?rea?l_rec?ordm?yreco?rd;b?egin?sel?ectb?ooks_?name,?pub?into?real_?recor?dfro?mboo?kswh?ereb?ooks_?id=?1;?DBMS_?OUTPU?T.put?_line?(real?_reco?rd.bn?ame|?|rea?l_rec?ord.b?pub);?end;?de?clare?my?recor?dboo?ks%ro?wtype?;beg?in?selec?t*i?ntom?yreco?rdfr?ombo?oksw?here?books?_id=?1;?DBMS?_OUTP?UT.pu?t_lin?e(myr?ecord?.book?s_nam?e||?myrec?ord.p?ub);?end;?-?-----?-----?-----?-----?--游標(biāo)-?-----?-----?-----?----?----?顯示游標(biāo)的?使用方法?decla?re?curso?rmyc?ursor?is?se?lect?*fro?mboo?ks;?myre?cord?books?%rowt?ype;?begin?op?enmy?curso?r;?fetch?mycu?rsor?i?ntom?yreco?rd;?whil?emyc?ursor?%foun?dloo?p?DBMS?_OUTP?UT.pu?t_lin?e(myr?ecord?.book?s_nam?e||?myrec?ord.p?ub);?f?etch?mycur?sor??into?myrec?ord;?end?loop?;c?lose?mycur?sor;?end;?---?---帶參?數(shù)的游標(biāo)?decla?re?curso?rmyc?ursor?(book?idnu?mber)?is?se?lect?*fro?mboo?kswh?ereb?ooks.?books?_id=?book?id;?myre?cord?books?%rowt?ype;?begin?op?enmy?curso?r(1);?fe?tchm?ycurs?or?int?omyr?ecord?;w?hile?mycur?sor%f?ound?loop?D?BMS_O?UTPUT?.put_?line(?myrec?ord.b?ooks_?name?||my?recor?d.pub?);?fet?chmy?curso?r?in?tomy?recor?d;?endl?oop;?clo?semy?curso?r;en?d;-?-----?使用for?做游標(biāo)的循?環(huán)dec?lare?curs?ormy?curso?r(boo?kidn?umber?)is?selec?tboo?ks_na?mefr?ombo?oksw?here?books?.book?s_id=?booki?d;be?ginf?orcu?rin?mycur?sor(1?)loo?pDBM?S_OUT?PUT.p?ut_li?ne(cu?r.boo?ks_na?me);?endl?oop;?end;??----?isop?ende?clare?bo?oknam?eboo?ks.bo?oks_n?ame%t?ype;?cur?sorm?ycurs?or(bo?oksid?numb?er)i?s?sele?ctbo?oks_n?amef?romb?ooks?where?book?s_id?=boo?ksid;?begi?ni?fmyc?ursor?%isop?enth?en?DBM?S_OUT?PUT.p?ut_li?ne('c?ursor?iso?pened?');?else??open?mycur?sor(1?);?endi?f;?fetch?mycu?rsor?i?ntob?ookna?me;?clos?emyc?ursor?;d?bms_o?utput?.put_?line(?bookn?ame);?end;?---?----r?owcou?ntde?clare?bo?oknam?eboo?ks.bo?oks_n?ame%t?ype;?cur?sorm?ycurs?oris??selec?tboo?ks_na?mefr?ombo?oks;?begin?op?enmy?curso?r;?loop?f?etch?mycur?sor??into?bookn?ame;?e?xitw?henm?ycurs?or%no?tfoun?dor?mycur?sor%n?otfou?ndis?null?;?DBMS?_OUTP?UT.pu?t_lin?e(myc?ursor?%rowc?ount)?;e?ndlo?op;?clos?emyc?ursor?;end?;?---?--游標(biāo)跟?新數(shù)據(jù)d?eclar?ec?ursor?mycu?rsor?is?sel?ectb?ooks_?name?from?books?for?updat?e;?text?varch?ar2(1?00);?begin?op?enmy?curso?r;?fetch?mycu?rsor?i?ntot?ext;?whi?lemy?curso?r%fou?ndlo?op?upd?ateb?ooks??set?book?s_nam?e=b?ooks_?name?||'_?t'?wh?erec?urren?tof?mycur?sor;?f?etch?mycur?sor??into?text;?en?dloo?p;?close?mycu?rsor;?end;?---?-----?-----?---隱式?游標(biāo)?不需要聲明?begi?nf?orcu?rin?(sele?ctbo?oks_n?amef?romb?ooks