oracle存儲(chǔ)過程超詳細(xì)使用手冊(cè)

1、Oracle存儲(chǔ)過程總結(jié)1、創(chuàng)建存儲(chǔ)過程create or replace procedure test(var_name_1 in type,var_name_2 out type) as-聲明變量(變量名 變量類型)begin-存儲(chǔ)過程的執(zhí)行體end test;打印出輸入的時(shí)間信息E.g:create or replace procedure test(workDate in Date) isbegindbms_output.putline(The input date is:|to_date(workDate, yyyy-mm-dd);end test;2、變量賦值變量名 := 值;E.

2、g：create or replace procedure test(workDate in Date) isx number(4,2); begin x := 1;end test;3、判斷語(yǔ)句:if 比較式 then begin end; end if;E.gcreate or replace procedure test(x in number) isbegin        if x >0 then       

3、  begin        x := 0 - x;        end;    end if;    if x = 0 then       begin        x: = 1;&#

4、160;   end;    end if;end test;4、For 循環(huán)For . in . LOOP-執(zhí)行語(yǔ)句end LOOP;(1)循環(huán)遍歷游標(biāo)create or replace procedure test() asCursor cursor is select name from student; name varchar(20);beginfor name in cursor LOOPbegin dbms_output.putline(name); end;end LOOP;end tes

5、t;(2)循環(huán)遍歷數(shù)組 create or replace procedure test(varArray in myPackage.TestArray) as-(輸入?yún)?shù)varArray 是自定義的數(shù)組類型，定義方式見標(biāo)題6)i number;begini := 1;  -存儲(chǔ)過程數(shù)組是起始位置是從1開始的，與java、C、C+等語(yǔ)言不同。因?yàn)樵贠racle中本是沒有數(shù)組的概念的，數(shù)組其實(shí)就是一張-表(Table),每個(gè)數(shù)組元素就是表中的一個(gè)記錄，所以遍歷數(shù)組時(shí)就相當(dāng)于從表中的第一條記錄開始遍歷for i in 1.varArray.count LOOP 

6、60;   dbms_output.putline(The No. | i |record in varArray is: |varArray(i);    end LOOP;end test;5、While 循環(huán)while 條件語(yǔ)句 LOOPbeginend;end LOOP;E.gcreate or replace procedure test(i in number) asbeginwhile i < 10 LOOPbegin     i:= i + 1;end;

7、end LOOP; end test;6、數(shù)組首先明確一個(gè)概念：Oracle中本是沒有數(shù)組的概念的，數(shù)組其實(shí)就是一張表(Table),每個(gè)數(shù)組元素就是表中的一個(gè)記錄。使用數(shù)組時(shí)，用戶可以使用Oracle已經(jīng)定義好的數(shù)組類型，或可根據(jù)自己的需要定義數(shù)組類型。(1)使用Oracle自帶的數(shù)組類型x array; -使用時(shí)需要需要進(jìn)行初始化e.g:create or replace procedure test(y out array) is x array;   beginx := new array();y := x;end test;(2)自定

8、義的數(shù)組類型 (自定義數(shù)據(jù)類型時(shí)，建議通過創(chuàng)建Package的方式實(shí)現(xiàn)，以便于管理)E.g (自定義使用參見標(biāo)題4.2) create or replace package myPackage is  - Public type declarations   type info is record(     name varchar(20),     y number);  type TestArray is table of info index by binary_integer;   -此

9、處聲明了一個(gè)TestArray的類型數(shù)據(jù)，其實(shí)其為一張存儲(chǔ)Info數(shù)據(jù)類型的Table而已，及TestArray 就是一張表，有兩個(gè)字段，一個(gè)是name，一個(gè)是y。需要注意的是此處使用了Index by binary_integer 編制該Table的索引項(xiàng)，也可以不寫，直接寫成：type TestArray istable of info，如果不寫的話使用數(shù)組時(shí)就需要進(jìn)行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();end TestArray;7.游標(biāo)的使用 Oracle中Cursor是非常有用

10、的，用于遍歷臨時(shí)表中的查詢結(jié)果。其相關(guān)方法和屬性也很多，現(xiàn)僅就常用的用法做一二介紹：(1)Cursor型游標(biāo)(不能用于參數(shù)傳遞)create or replace procedure test() is  cusor_1 Cursor is select std_name from student where  .;  -Cursor的使用方式1   cursor_2 Cursor;beginselect class_name into cursor_2 from class where .;  -Cursor的使用方式2可使用For

11、 x in cursor LOOP . end LOOP; 來實(shí)現(xiàn)對(duì)Cursor的遍歷end test;(2)SYS_REFCURSOR型游標(biāo)，該游標(biāo)是Oracle以預(yù)先定義的游標(biāo)，可作出參數(shù)進(jìn)行傳遞create or replace procedure test(rsCursor out SYS_REFCURSOR) iscursor SYS_REFCURSOR; name varhcar(20);beginOPEN cursor FOR select name from student where . -SYS_REFCURSOR只能通過OPEN方法來打開和賦值LOOP fetc

12、h cursor into name   -SYS_REFCURSOR只能通過fetch into來打開和遍歷 exit when cursor%NOTFOUND;              -SYS_REFCURSOR中可使用三個(gè)狀態(tài)屬性：                 &#

13、160;                       -%NOTFOUND(未找到記錄信息) %FOUND(找到記錄信息)                      &#

14、160;                  -%ROWCOUNT(然后當(dāng)前游標(biāo)所指向的行位置) dbms_output.putline(name);end LOOP;rsCursor := cursor;end test;下面寫一個(gè)簡(jiǎn)單的例子來對(duì)以上所說的存儲(chǔ)過程的用法做一個(gè)應(yīng)用：現(xiàn)假設(shè)存在兩張表，一張是學(xué)生成績(jī)表(studnet)，字段為：stdId,math,article,language,music,spor

15、t,total,average,step                   一張是學(xué)生課外成績(jī)表(out_school),字段為:stdId,parctice,comment通過存儲(chǔ)過程自動(dòng)計(jì)算出每位學(xué)生的總成績(jī)和平均成績(jī)，同時(shí)，如果學(xué)生在課外課程中獲得的評(píng)價(jià)為A，就在總成績(jī)上加20分。create or replace procedure autocomputer(step in number) isrsCursor SY

16、S_REFCURSOR;commentArray myPackage.myArray;math number;article number;language number;music number;sport number;total number;average number;stdId varchar(30);record myPackage.stdInfo;i number;begini := 1;get_comment(commentArray); -調(diào)用名為get_comment()的存儲(chǔ)過程獲取學(xué)生課外評(píng)分信息OPEN rsCursor for select stdId,math,

17、article,language,music,sport from student t where t.step = step;LOOPfetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;total := math + article + language + music + sport;for i in mentArray.count LOOP  record := commentArray(i);    i

18、f stdId = record.stdId then   begin      if ment = &apos;A&apos; then       begin          total := total + 20;      go to next; -使用

19、go to跳出for循環(huán)         end;    end if;  end;  end if;end LOOP;<<continue>>  average := total / 5; update student t set t.total=total and t.average = average where t.stdId = stdId;end LOOP;end;end auto

20、computer;-取得學(xué)生評(píng)論信息的存儲(chǔ)過程create or replace procedure get_comment(commentArray out myPackage.myArray) isrs SYS_REFCURSOR；record myPackage.stdInfo;stdId varchar(30);comment varchar(1);i number;beginopen rs for select stdId,comment from out_schooli := 1;LOOP fetch rs into stdId,comment; exit when rs

21、%NOTFOUND;record.stdId := stdId; ment := comment;recommentArray(i) := record;i:=i + 1;end LOOP;end get_comment;-定義數(shù)組類型myArraycreate or replace package myPackage is begintype stdInfo is record(stdId varchar(30),comment varchar(1);type myArray is table of stdInfo index by binary_integer;end myPac

22、kage;項(xiàng)目中有涉及存儲(chǔ)過程對(duì)字符串的處理，所以就將在網(wǎng)上查找到的資料匯總，做一個(gè)信息拼接式的總結(jié)。以下信息均來自互聯(lián)網(wǎng)，貼出來一則自己保存以待以后使用，一則供大家分享。字符函數(shù)返回字符值這些函數(shù)全都接收的是字符族類型的參數(shù)(CHR除外)并且返回字符值.除了特別說明的之外,這些函數(shù)大部分返回VARCHAR2類型的數(shù)值.字符函數(shù)的返回類型所受的限制和基本數(shù)據(jù)庫(kù)類型所受的限制是相同的。字符型變量存儲(chǔ)的最大值： VARCHAR2數(shù)值被限制為2000字符(ORACLE 8中為4000字符) CHAR數(shù)值被限制為255字符(在ORACLE8中是2000) long類型為2

23、GB Clob類型為4GB1、CHR語(yǔ)法:  chr(x)功能：返回在數(shù)據(jù)庫(kù)字符集中與X擁有等價(jià)數(shù)值的字符。CHR和ASCII是一對(duì)反函數(shù)。經(jīng)過CHR轉(zhuǎn)換后的字符再經(jīng)過ASCII轉(zhuǎn)換又得到了原來的字符。使用位置：過程性語(yǔ)句和SQL語(yǔ)句。2、CONCAT語(yǔ)法： CONCAT（string1,string2）功能：返回string1，并且在后面連接string2。使用位置：過程性語(yǔ)句和SQL語(yǔ)句。3、INITCAP語(yǔ)法：INITCAP（string）功能：返回字符串的每個(gè)單詞的第一個(gè)字母大寫而單詞中的其他字母小寫的string。單詞是用.空格或給字母數(shù)字字符進(jìn)行分隔。不是字母的

24、字符不變動(dòng)。使用位置：過程性語(yǔ)句和SQL語(yǔ)句。4、LTRIM語(yǔ)法：LTRIM（string1,string2）功能：返回刪除從左邊算起出現(xiàn)在string2中的字符的string1。String2被缺省設(shè)置為單個(gè)的空格。數(shù)據(jù)庫(kù)將掃描string1，從最左邊開始。當(dāng)遇到不在string2中的第一個(gè)字符，結(jié)果就被返回了。LTRIM的行為方式與RTRIM很相似。使用位置：過程性語(yǔ)句和SQL語(yǔ)句。5、NLS_INITCAP語(yǔ)法：NLS_INITCAP（string,nlsparams）功能：返回字符串每個(gè)單詞第一個(gè)字母大寫而單詞中的其他字母小寫的string，nlsparams指定了不同于該會(huì)話缺省值的

25、不同排序序列。如果不指定參數(shù)，則功能和INITCAP相同。Nlsparams可以使用的形式是：NLS_SORT=sort這里sort制訂了一個(gè)語(yǔ)言排序序列。使用位置：過程性語(yǔ)句和SQL語(yǔ)句。6、NLS_LOWER語(yǔ)法：NLS_LOWER（string,nlsparams）功能：返回字符串中的所有字母都是小寫形式的string。不是字母的字符不變。      Nlsparams參數(shù)的形式與用途和NLS_INITCAP中的nlsparams參數(shù)是相同的。如果nlsparams沒有被包含，那么NLS_LOWER所作的處理和LOWER相同。使用位置；過

26、程性語(yǔ)句和SQL語(yǔ)句。7、NLS_UPPER語(yǔ)法：nls_upper（string,nlsparams）功能：返回字符串中的所有字母都是大寫的形式的string。不是字母的字符不變。nlsparams參數(shù)的形式與用途和NLS_INITCAP中的相同。如果沒有設(shè)定參數(shù)，則NLS_UPPER功能和UPPER相同。使用位置：過程性語(yǔ)句和SQL語(yǔ)句。8、REPLACE語(yǔ)法：REPLACE（string，search_str,replace_str）功能：把string中的所有的子字符串search_str用可選的replace_str替換，如果沒有指定replace_str，所有的string中的子字

27、符串search_str都將被刪除。REPLACE是TRANSLATE所提供的功能的一個(gè)子集。使用位置：過程性語(yǔ)句和SQL語(yǔ)句。9、RPAD語(yǔ)法：RPAD（string1,x,string2）功能：返回在X字符長(zhǎng)度的位置上插入一個(gè)string2中的字符的string1。如果string2的長(zhǎng)度要比X字符少，就按照需要進(jìn)行復(fù)制。如果string2多于X字符，則僅string1前面的X各字符被使用。如果沒有指定string2，那么使用空格進(jìn)行填充。X是使用顯示長(zhǎng)度可以比字符串的實(shí)際長(zhǎng)度要長(zhǎng)。RPAD的行為方式與LPAD很相似，除了它是在右邊而不是在左邊進(jìn)行填充。使用位置：過程性語(yǔ)句和SQL語(yǔ)句。1

28、0、RTRIM語(yǔ)法:  RTRIM（string1,string2）功能:  返回刪除從右邊算起出現(xiàn)在string1中出現(xiàn)的字符string2. string2被缺省設(shè)置為單個(gè)的空格.數(shù)據(jù)庫(kù)將掃描string1,從右邊開始.當(dāng)遇到不在string2中的第一個(gè)字符,結(jié)果就被返回了RTRIM的行為方式與LTRIM很相似.使用位置：過程性語(yǔ)句和SQL語(yǔ)句。11、SOUNDEX語(yǔ)法:  SOUNDEX（string）功能:  返回string的聲音表示形式.這對(duì)于比較兩個(gè)拼寫不同但是發(fā)音類似的單詞而言很有幫助.使用位置：過程性語(yǔ)句和SQL語(yǔ)句。12、SUBSTR

29、語(yǔ)法:  SUBSTR（string,a,b）功能:  返回從字母為值a開始b個(gè)字符長(zhǎng)的string的一個(gè)子字符串.如果a是0,那么它就被認(rèn)為從第一個(gè)字符開始.如果是正數(shù),返回字符是從左邊向右邊進(jìn)行計(jì)算的.如果b是負(fù)數(shù),那么返回的字符是從string的末尾開始從右向左進(jìn)行計(jì)算的.如果b不存在,那么它將缺省的設(shè)置為整個(gè)字符串.如果b小于1,那么將返回NULL.如果a或b使用了浮點(diǎn)數(shù),那么該數(shù)值將在處理進(jìn)行以前首先被卻為一個(gè)整數(shù).使用位置：過程性語(yǔ)句和SQL語(yǔ)句。13、TRANSLATE   語(yǔ)法:  TRANSLATE(string,from_s

30、tr,to_str)   功能:  返回將所出現(xiàn)的from_str中的每個(gè)字符替換為to_str中的相應(yīng)字符以后的string. TRANSLATE是REPLACE所提供的功能的一個(gè)超集.如果from_str比to_str長(zhǎng),那么在from_str中而不在to_str中而外的字符將從string中被刪除,因?yàn)樗鼈儧]有相應(yīng)的替換字符. to_str不能為空.Oracle把空字符串認(rèn)為是NULL,并且如果TRANSLATE中的任何參數(shù)為NULL,那么結(jié)果也是NULL.使用位置：過程性語(yǔ)句和SQL語(yǔ)句。14、UPPER語(yǔ)法: UPPER（string）功能: 返回大寫的

31、string.不是字母的字符不變.如果string是CHAR數(shù)據(jù)類型的,那么結(jié)果也是CHAR類型的.如果string是VARCHAR2類型的,那么結(jié)果也是VARCHAR2類型的.使用位置: 過程性語(yǔ)句和SQL語(yǔ)句。字符函數(shù)返回?cái)?shù)字這些函數(shù)接受字符參數(shù)回?cái)?shù)字結(jié)果.參數(shù)可以是CHAR或者是VARCHAR2類型的.盡管實(shí)際下許多結(jié)果都是整數(shù)值,但是返回結(jié)果都是簡(jiǎn)單的NUMBER類型的,沒有定義任何的精度或刻度范圍.16、ASCII語(yǔ)法:  ASCII（string）功能: 數(shù)據(jù)庫(kù)字符集返回string的第一個(gè)字節(jié)的十進(jìn)制表示.請(qǐng)注意該函數(shù)仍然稱作為ASCII.盡管許多字符集不是7位ASCI

32、I.CHR和ASCII是互為相反的函數(shù).CHR得到給定字符編碼的響應(yīng)字符. ASCII得到給定字符的字符編碼.使用位置: 過程性語(yǔ)句和SQL語(yǔ)句。17、INSTR語(yǔ)法:  INSTR（string1, string2a,b）功能:  得到在string1中包含string2的位置. string1時(shí)從左邊開始檢查的,開始的位置為a,如果a是一個(gè)負(fù)數(shù),那么string1是從右邊開始進(jìn)行掃描的.第b次出現(xiàn)的位置將被返回. a和b都缺省設(shè)置為1,這將會(huì)返回在string1中第一次出現(xiàn)string2的位置.如果string2在a和b的規(guī)定下沒有找到,那么返回0.位置的計(jì)算是相對(duì)于s

33、tring1的開始位置的,不管a和b的取值是多少.使用位置: 過程性語(yǔ)句和SQL語(yǔ)句。18、INSTRB語(yǔ)法:  INSTRB（string1, string2a,b）功能:  和INSTR相同,只是操作的對(duì)參數(shù)字符使用的位置的是字節(jié).使用位置: 過程性語(yǔ)句和SQL語(yǔ)句。19、LENGTH語(yǔ)法:  LENGTH（string）功能:  返回string的字節(jié)單位的長(zhǎng)度.CHAR數(shù)值是填充空格類型的,如果string由數(shù)據(jù)類型CHAR,它的結(jié)尾的空格都被計(jì)算到字符串長(zhǎng)度中間.如果string是NULL,返回結(jié)果是NULL,而不是0.使用位置: 過程性語(yǔ)句和

34、SQL語(yǔ)句。20、LENGTHB語(yǔ)法:  LENGTHB（string）功能:  返回以字節(jié)為單位的string的長(zhǎng)度.對(duì)于單字節(jié)字符集LENGTHB和LENGTH是一樣的.使用位置: 過程性語(yǔ)句和SQL語(yǔ)句。21、NLSSORT語(yǔ)法: NLSSORT（string,nlsparams）功能: 得到用于排序string的字符串字節(jié).所有的數(shù)值都被轉(zhuǎn)換為字節(jié)字符串,這樣在不同數(shù)據(jù)庫(kù)之間就保持了一致性. Nlsparams的作用和NLS_INITCAP中的相同.如果忽略參數(shù),會(huì)話使用缺省排序.使用位置: 過程性語(yǔ)句和SQL語(yǔ)句。oracle 存儲(chǔ)過程的基本語(yǔ)法1.基本結(jié)構(gòu) CR

35、EATE OR REPLACE PROCEDURE 存儲(chǔ)過程名字(    參數(shù)1 IN NUMBER,    參數(shù)2 IN NUMBER) IS變量1 INTEGER :=0;變量2 DATE;BEGINEND 存儲(chǔ)過程名字2.SELECT INTO STATEMENT  將select查詢的結(jié)果存入到變量中，可以同時(shí)將多個(gè)列存儲(chǔ)多個(gè)變量中，必須有一條  記錄，否則拋出異常(如果沒有記錄拋出NO_DATA_FOUND)  例子：   BEGIN  SELECT col1,col2 in

36、to 變量1,變量2 FROM typestruct where xxx;  EXCEPTION  WHEN NO_DATA_FOUND THEN      xxxx;  END;  .3.IF 判斷  IF V_TEST=1 THEN    BEGIN        do something    END;  END IF;4.while 循環(huán)  WHIL

37、E V_TEST=1 LOOP  BEGIN XXXX  END;  END LOOP;5.變量賦值  V_TEST := 123;6.用for in 使用cursor  .  IS  CURSOR cur IS SELECT * FROM xxx;  BEGIN FOR cur_result in cur LOOP  BEGIN   V_SUM :=cur_result.列名1+cur_result.列名2  END;

38、60;END LOOP;  END;7.帶參數(shù)的cursor  CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;  OPEN C_USER(變量值);  LOOP FETCH C_USER INTO V_NAME; EXIT FETCH C_USER%NOTFOUND;    do something  END LOOP;  CLOSE C_USER;8.用pl/sql developer de

39、bug  連接數(shù)據(jù)庫(kù)后建立一個(gè)Test WINDOW  在窗口輸入調(diào)用SP的代碼,F9開始debug,CTRL+N單步調(diào)試關(guān)于oracle存儲(chǔ)過程的若干問題備忘1.在oracle中，數(shù)據(jù)表別名不能加as，如：select a.appname from appinfo a;- 正確select a.appname from appinfo as a;- 錯(cuò)誤 也許，是怕和oracle中的存儲(chǔ)過程中的關(guān)鍵字as沖突的問題吧2.在存儲(chǔ)過程中，select某一字段時(shí)，后面必須緊跟in

40、to，如果select整個(gè)記錄，利用游標(biāo)的話就另當(dāng)別論了。  select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;- 有into，正確編譯  select af.keynode from APPFOUNDATION af where af.appid=aid&

41、#160;and af.foundationid=fid;- 沒有into，編譯報(bào)錯(cuò)，提示：Compilation   Error: PLS-00428: an INTO clause is expected in this SELECT statement3.在利用o.語(yǔ)法時(shí)，必須先確保數(shù)據(jù)庫(kù)中有該條記錄，否則會(huì)報(bào)出"no data found"異常。   可以在該語(yǔ)法之前，先利用se

42、lect count(*) from 查看數(shù)據(jù)庫(kù)中是否存在該記錄，如果存在，再利用o.4.在存儲(chǔ)過程中，別名不能和字段名稱相同，否則雖然編譯可以通過，但在運(yùn)行階段會(huì)報(bào)錯(cuò) select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;- 正確運(yùn)行select af.keynode into kn from APPFOUNDA

43、TION af where af.appid=appid and af.foundationid=foundationid;- 運(yùn)行階段報(bào)錯(cuò)，提示ORA-01422:exact fetch returns more than requested number of rows5.在存儲(chǔ)過程中，關(guān)于出現(xiàn)null的問題假設(shè)有一個(gè)表A，定義如下：create table A(id varchar2(50) primary&#

44、160;key not null,vcount number(8) not null,bid varchar2(50) not null - 外鍵 );如果在存儲(chǔ)過程中，使用如下語(yǔ)句：select sum(vcount) into fcount from A where bid='xxxxxx'如果A表中不存在bid="xxxxxx"的記錄，則fcount=null(即使fcoun

45、t定義時(shí)設(shè)置了默認(rèn)值，如：fcount number(8):=0依然無效，fcount還是會(huì)變成null)，這樣以后使用fcount時(shí)就可能有問題，所以在這里最好先判斷一下：if fcount is null then    fcount:=0;end if;這樣就一切ok了。6.Hibernate調(diào)用oracle存儲(chǔ)過程        this.pnumberManager.getHibernateTemplate().ex

46、ecute(                new HibernateCallback() .                    public Object doInHibernate(Sessi

47、on session)                            throws HibernateException, SQLException .          

48、              CallableStatement cs = session                            

49、60;   .connection()                                .prepareCall("call modifyapppnumber_remain(?)");  

50、;                      cs.setString(1, foundationid);                      

51、  cs.execute();                        return null;                   

52、                 ); oracle 存儲(chǔ)過程語(yǔ)法總結(jié)及練習(xí)-1.存儲(chǔ)過程之ifclear;create or replace procedure mydel(in_a in integer)asbeginif in_a<100 thendbms_output.put_line('小于100.');elsif in_a<200 thendbms_output.pu

53、t_line('大于100小于200.');elsedbms_output.put_line('大于200.');end if;end;/set serveroutput on;beginmydel(1102);end;/-2.存儲(chǔ)過程之case1clear;create or replace procedure mydel(in_a in integer)asbegincase in_awhen 1 thendbms_output.put_line('小于100.');when 2 thendbms_output.put_line('大

54、于100小于200.');elsedbms_output.put_line('大于200.');end case;end;/set serveroutput on;beginmydel(2);end;/-1.存儲(chǔ)過程之loop1clear;create or replace procedure mydel(in_a in integer)asa integer;begina:=0;loopdbms_output.put_line(a);a:=a+1;exit whena>301;end loop;end;/set serveroutput on;beginmyde

55、l(2);end;/-1.存儲(chǔ)過程之loop2clear;create or replace procedure mydel(in_a in integer)asa integer;begina:=0;while a<300 loopdbms_output.put_line(a);a:=a+1;end loop;end;/set serveroutput on;beginmydel(2);end;-1.存儲(chǔ)過程之loop3clear;create or replace procedure mydel(in_a in integer)asa integer;beginfor a in 0.

56、300loopdbms_output.put_line(a);end loop;end;/set serveroutput on;beginmydel(2);end;/clear;select ename,cc:=(casewhen comm=null then sal*12;else (sal+comm)*12;end case from emp order by salpersal;-clear;create or replace procedure getstudentcomments(i_studentid in int,o_comments out varchar)asexams_s

57、at int;avg_mark int;tmp_comments varchar(100);beginselect count(examid) into exams_sat from studentexamwhere studentid=i_studentid;if exams_sat=0 thentmp_comments:='n/a-this student did not attend the exam!'elseselect avg(mark) into avg_mark from studentexamwhere studentid=i_studentid;casewh

58、en avg_mark<50 then tmp_comments:='very bad'when avg_mark<60 then tmp_comments:='bad'when avg_mark<70 then tmp_comments:='good'end case;end if;o_comments:=tmp_comments;end;/set serveroutput on;declarepp ments%type;begingetstudentcomments(8,pp);dbms_output.put_line(pp

59、);end;/-delete from emp where empno<6000;clear;create or replace procedure insertdata(in_num in integer)asmyNum int default 0;emp_no emp.empno%type:=1000;beginwhile myNum<in_num loopinsert into emp values(emp_no,'hui'|myNum,'coder',7555,current_date,8000,6258,30);emp_no:=emp_no

60、+1;myNum:=myNum+1;end loop;end;/set serveroutput on;begininsertdata(10);end;/select * from emp;-clear;select studentname,averageMark,casewhen averageMark<60 then '不及格'when averageMark<70 then '考得好'when averageMark<80 then '考得很好'end casefrom (select (select fro

61、m student b where b.studentid=a.studentid) as studentname,round(avg(mark),2) as averageMark from studentexam a group by a.studentid); ORACLE 查詢練習(xí)emp員工表(empno員工號(hào)/ename員工姓名/job工作/mgr上級(jí)編號(hào)/hiredate受雇日期/sal薪金/comm傭金/deptno部門編號(hào))dept部門表(deptno部門編號(hào)/dname部門名稱/loc地點(diǎn))工資 薪金 傭金1列出至少有一個(gè)員工的所有部門。2列出薪金比“SMITH”多

62、的所有員工。3列出所有員工的姓名及其直接上級(jí)的姓名。4列出受雇日期早于其直接上級(jí)的所有員工。5列出部門名稱和這些部門的員工信息，同時(shí)列出那些沒有員工的部門6列出所有“CLERK”（辦事員）的姓名及其部門名稱。7列出最低薪金大于1500的各種工作。8列出在部門“SALES”（銷售部）工作的員工的姓名，假定不知道銷售部的部門編號(hào)。9列出薪金高于公司平均薪金的所有員工。10列出與“SCOTT”從事相同工作的所有員工。11列出薪金等于部門30中員工的薪金的所有員工的姓名和薪金。12列出薪金高于在部門30工作的所有員工的薪金的員工姓名和薪金。13列出在每個(gè)部門工作的員工數(shù)量、平均工資和平均服務(wù)期限。14

63、列出所有員工的姓名、部門名稱和工資。15列出所有部門的詳細(xì)信息和部門人數(shù)。16列出各種工作的最低工資。17列出各個(gè)部門的MANAGER（經(jīng)理）的最低薪金。18列出所有員工的年工資,按年薪從低到高排序。-1-select dname from dept where deptno in(select deptno from emp);-2-select * from emp where sal>(select sal from emp where ename='SMITH');-3-select a.ename,(select ename from emp b where b

64、.empno=a.mgr) as bossname from emp a;-4-select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);-5-select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,m,b.deptnofrom dept a left join emp b on a.deptno=b.deptno;-6-select a.ename,b.dname from emp a join d

65、ept bon a.deptno=b.deptno and a.job='CLERK'-7-select distinct job as HighSalJob from emp group by job having min(sal)>1500;-8-select ename from emp where deptno=(select deptno from dept where dname='SALES');-9-select ename from emp where sal>(select avg(sal) from emp);-10-select ename from emp where job=(select job from emp where ename='SCOTT');-11-select a.ename,a.sal from emp a wh