第7章

PL/SQL與應用7.1PL/SQL編程基礎 7.2存儲過程 7.3觸發器 7.4游標 本章小結7.1PL/SQL編程基礎第7章SQL（StructuredQueryLanguage）語言，又稱作結構化查詢語言。它是一種用戶操作關系數據庫的通用語言。作為關系數據庫的標準語言，它已被眾多商用數據庫管理系統產品所采用，不過不同的數據庫管理系統在其實踐過程中都對SQL規范做了某些編改和擴充，如金倉的PL/SQL，達夢的DMSQL，微軟的Transaction-SQL（簡稱T-SQL）、甲骨文的PL/SQL和PostgreSQL的PL/pgSQL。KingbaseES的PL/SQL是對SQL語句的擴展，在普通SQL語句的使用上增加了編程語言的特點，所以PL/SQL通過把數據操作和查詢語句組織在PL/SQL代碼的存儲過程模塊中，通過邏輯判斷、循環等操作實現復雜的功能或者計算的程序語言，是一種用于數據庫系統的可載入的過程語言。7.1PL/SQL編程基礎第7章PL/SQL簡介7.1.11.PL/SQL的特點⑴與SQL語言緊密集成。⑵提供模塊化程序開發功能，提高了系統可靠性。⑶可以被定義為受服務器信任的語言。服務器端程序設計，可移植性好。⑷減小網絡流量。7.1PL/SQL編程基礎第7章PL/SQL簡介7.1.12.PL/SQL的優點⑴增強了SQL語言的功能和靈活性⑵增強了數據的完整性和安全性⑶提高應用程序的運行性能⑷降低網絡的通信量⑸便于維護7.1PL/SQL編程基礎第7章PL/SQL簡介7.1.13.PL/SQL的結構

PL/SQL是一種塊結構語言，所定義的存儲過程模塊的結構分為模塊頭定義和模塊體兩大部分。模塊頭確定了定義的模塊類型（如定義的是存儲過程或用戶自定義函數）、名稱、參數等；模塊體又稱為過程體，又叫PL/SQL塊。一個PL/SQL塊主要包括：聲明部分、執行部分和異常處理部分，其定義為：[<<label>>][DECLAREdeclarations]BEGINstatements[EXCEPTION]--異常處理部分：處理捕獲到的錯誤END[label];聲明部分是可選的，主要聲明所需的變量和使用的游標。變量只有經過聲明后才能在執行部分使用。過程體主要用來具體實現該過程的功能。label可以用來標識一個塊，以便在一個EXIT語句中使用或者標識在該塊中聲明的變量名。位于END之后的label，必須與塊開始的label相匹配。異常處理部分由EXCEPTION引出，用于捕獲執行過程中發生的錯誤，并進行相應的處理。7.1PL/SQL編程基礎第7章PL/SQL簡介7.1.13.PL/SQL的結構

PL/SQL是一種塊結構語言，所定義的存儲過程模塊的結構分為模塊頭定義和模塊體兩大部分。模塊頭確定了定義的模塊類型（如定義的是存儲過程或用戶自定義函數）、名稱、參數等；模塊體又稱為過程體，又叫PL/SQL塊。一個PL/SQL塊主要包括：聲明部分、執行部分和異常處理部分，其定義為：[<<label>>][DECLAREdeclarations]BEGINstatements[EXCEPTION]--異常處理部分：處理捕獲到的錯誤END[label];在PL/SQL塊中的每個聲明和語句都由一個分號結束。塊中的語句也可以是一個嵌套的PL/SQL子塊。子塊可以被用來邏輯分組或者將變量局部化為語句的一個小組。在子塊的生命期內，子塊中聲明的變量會掩蓋外層塊中相同名稱的變量。如果這時需要訪問外層變量的名稱，可以通過塊的標簽來限定外層變量的名字來訪問。7.1PL/SQL編程基礎第7章PL/SQL簡介7.1.1【例7-1】一個簡單的PL/SQL程序示例CREATEORREPLACEFUNCTIONmyfunc7_1()RETURNSintegerAS<<outerblock>>DECLAREvarinteger:=10;BEGINRAISENOTICE'var的值為is%',var;--輸出10var:=20;

----創建子塊

--DECLAREvarinteger:=30;

BEGINRAISENOTICE'內層塊var的值為%',var;--輸出30RAISENOTICE'外層塊var的值為%',outerblock.var;--輸出20

END;RAISENOTICE'var的值%',var;--輸出20（外層塊var的值）

RETURNvar;END;--調用函數CALLmyfunc7_1();7.1PL/SQL編程基礎第7章PL/SQL簡介7.1.1【例7-1】一個簡單的PL/SQL程序示例CREATEORREPLACEFUNCTIONmyfunc7_1()RETURNSintegerAS<<outerblock>>DECLAREvarinteger:=10;BEGINRAISENOTICE'var的值為is%',var;--輸出10var:=20;

----創建子塊

--DECLAREvarinteger:=30;

BEGINRAISENOTICE'內層塊var的值為%',var;--輸出30RAISENOTICE'外層塊var的值為%',outerblock.var;--輸出20

END;RAISENOTICE'var的值%',var;--輸出20（外層塊var的值）

RETURNvar;END;--調用函數CALLmyfunc7_1();--或Selectmyfunc7_1();7.1PL/SQL編程基礎第7章PL/SQL簡介7.1.1【例7-1】一個簡單的PL/SQL程序示例注意:不要將PL/SQL中用來分組語句的BEGIN/END與用于事務控制的同名SQL命令弄混。PL/SQL的BEGIN/END只用于分組，它們不會開始或結束一個事務。函數和觸發器過程總是被執行在由一個外層查詢建立的事務中------它們不能開始或提交那個事務，因為沒有環境給它們執行。不過，一個包含EXCEPTION子句的塊實際上會形成一個子事務，它可以被回滾而不影響外層事務。7.1PL/SQL編程基礎第7章PL/SQL簡介7.1.14.PL/SQL的匿名塊和命名塊PL/SQL程序塊既可以是一個命名程序塊，也可以是一個匿名程序塊匿名塊的結構包括聲明和執行兩部分。匿名塊每次提交都被重新編譯和執行。因為匿名塊沒有名稱并不在數據庫中存儲，所以匿名塊不能從其他PL/SQL塊中調用。PL/SQL命名程序塊可獨立編譯并存儲在數據庫中，任何與數據庫相連接的應用程序都可以訪問這些PL/SQL命名程序塊。KingbaseES提供了四種類型的可存儲程序塊:函數、存儲過程、包和觸發器。PL/SQL程序塊必須包括（）部分。聲明執行異常

注釋ABCD提交單選題5分7.1PL/SQL編程基礎第7章變量聲明7.1.21.變量的命名在KingbaseES的PL/SQL中，變量都是通過標識符來命名。合法的標識符由字母、數字、下劃線組成，標識符必須以大小寫字母或下劃線開始。合法的標識符如：X，My_phone_num，Empid2，不合法的標識符如：1mysal$ary。雖然系統的保留字也是標識符，例如，BEGIN，END，IF等，但是它們具有特定的意義，無法作為PL/SQL的變量名稱，否則會出現編譯錯誤。例如：ENDint將會報錯。示例：

varCHAR(20);--合法標識符2022_nameVARCHAR(20);--非法標識符，因為以數字開頭v-name1CHAR(20);--非法標識符，因為使用了減號vname2CHAR(20);--非法標識符，因為標識符中包含空格deleteVARCHAR(20);--非法標識符，因為使用了SQL保留字7.1PL/SQL編程基礎第7章變量聲明7.1.21.變量的命名示例：

varCHAR(20);--合法標識符2022_nameVARCHAR(20);--非法標識符，因為以數字開頭v-name1CHAR(20);--非法標識符，因為使用了減號vname2CHAR(20);--非法標識符，因為標識符中包含空格deleteVARCHAR(20);--非法標識符，因為使用了SQL保留字7.1PL/SQL編程基礎第7章變量聲明7.1.22.變量的聲明在一個塊中使用的所有變量必須在該塊的聲明部分中聲明（唯一的例外是在一個整數范圍上迭代的FOR循環變量會被自動聲明為一個整數變量，類似的在一個游標結果上迭代的FOR循環變量會被自動地聲明為一個記錄變量）。語法定義：name[CONSTANT]type[COLLATEcollation_name][NOTNULL][{DEFAULT|:=|=}expression];7.1PL/SQL編程基礎第7章變量聲明7.1.22.變量的聲明【例7-2】通過PL/SQL程序塊定義變量和常量。DECLAREuser_idinteger:=10;--定義變量user_id,數據類型為integer,并賦初值為10quantity1numeric(5,1);--定義變量quantity,數據類型為numeric,長度為5,其中包含1位小數url1varchar:='abc';--定義變量url1,數據類型為varchar,并賦初值為'abc'quantity2integerDEFAULT32;--定義變量quantity2,數據類型為integer,默認值為32url2varchar:='';--定義變量url2,數據類型為varchar,并賦值為''var_idCONSTANTinteger:=20;--定義常量var_id,數據類型為integer,常量值為32BEGINNull;END;7.1PL/SQL編程基礎第7章變量聲明7.1.23.變量的別名PL/SQL允許為任何變量定義一個別名，在PL/SQL程序塊中定義別名的語法如下：newnameALIASFORoldname;使用說明：可以為任意變量聲明一個別名，而不只是函數參數。其主要實際用途是為預先固定了名稱的變量分配一個不同的名稱，例如在一個觸發器過程中的NEW或OLD。因為ALIAS為給相同的對象創建兩種不同的命名方式，如果對其使用不加限制就會導致混淆，最好只用它來覆蓋預先固定的名稱的變量。7.1PL/SQL編程基礎第7章變量聲明7.1.23.變量的別名PL/SQL允許為任何變量定義一個別名，在PL/SQL程序塊中定義別名的語法如下：newnameALIASFORoldname;使用說明：其主要實際用途是為具有預定名稱的變量分配一個不同的名稱。別名主要用于觸發器，例如：觸發器相關的特殊變量NEW和OLD。7.1PL/SQL編程基礎第7章變量聲明7.1.23.變量的別名【例7-3】定義變量的別名。Sno_idALIASFOR$1;--給$1定義一個別名為Sno_idpriorALIASFOROLD;--給old定義一個別名為priorupdatedALIASFORNEW;--給new定義一個別名為updated下列選項，屬于合法的變量名的是（）。username1_testu_id%Sid#nameABCD提交單選題5分7.1PL/SQL編程基礎第7章數據類型、表達式與運算符7.1.31.基本類型

PL/SQL與其他編程語言一致，支持多種內置、用戶自定義類型。KingbaseES的PL/SQL的基本類型指在SQL語言定義的各種基本數據類型，比如常見的數值（整數類型和浮點）類型，字符串類型、日期時間類型、數組類型等。【例7-4】聲明一個長度為8個字符的變量Tno_id，并賦值為'20220901'。

DECLARETno_idchar(8):='20220901'；基本類型字符串日期數組數值7.1PL/SQL編程基礎第7章數據類型、表達式與運算符7.1.32.復制類型語法定義：namevariable%TYPE|table.column%TYPE使用說明：定義一個變量，其數據類型可以與已經定義的某個數據變量的類型相同，或者與數據庫表的某個列的數據類型相同，這時可以使用%TYPE。7.1PL/SQL編程基礎第7章數據類型、表達式與運算符7.1.32.復制類型【例7-5】定義一個與變量v_var1具有相同數據類型的變量v_var2，再定義一個與users表中的user_id列具有相同數據類型的變量v_user_id。v_var2v_var1%TYPE;v_user_idusers.user_id%TYPE;7.1PL/SQL編程基礎第7章數據類型、表達式與運算符7.1.32.復制類型【例7-6】查詢學號為“2501102”的學生的學號與姓名，并存儲到變量Vsno和Vsn中。CREATEORREPLACEPROCEDUREPROC7_6()ASDECLAREV_snoS.SNO%TYPE;V_snS.SN%TYPE;BEGINSELECTS.SNo,S.SNINTOV_sno,V_snFROMSWHERES.SNo='2501102';RAISENOTICE'學號姓名為:%',V_sno||''||V_sn;END;--調用函數CALLPROC7_6();7.1PL/SQL編程基礎第7章數據類型、表達式與運算符7.1.33.行類型和組合類型語法定義：nametable_name%ROWTYPE;namecomposite_type_name;使用說明：定義一個變量的類型參照自基本表的結構類型，這時可以使用%ROWTYPE。%ROWTYPE前面是表名。composite_type_name表示組合類型，它是KingbaseES在定義一個表結構時，同時也會定義一個表相關聯的具有相同名稱的數據類型。因此，通過使用table_name%ROWTYPE標記來定義一個行變量也可以直接使用組合類型來定義，即帶不帶%ROWTYPE都沒有關系，但是帶有%ROWTYPE的形式可移植性更好。7.1PL/SQL編程基礎第7章數據類型、表達式與運算符7.1.33.行類型和組合類型【例7-7】運用行類型，創建一個函數，查詢指定學號的學生信息。CREATEORREPLACEFUNCTIONFUN7_7(VSNOS.SNO%TYPE)RETURNSVOIDASDECLARES_rowS%ROWTYPE;BEGINSELECT*INTOS_rowFROMSWHERES.SNO=VSNO;RAISENOTICE'%',S_row.SNO||''||S_ROW.SN||''||S_row.SG||''||S_row.SD||''||S_row.SP;RETURN;END;--調用函數SelectFUN7_7('2501105');7.1PL/SQL編程基礎第7章數據類型、表達式與運算符7.1.33.行類型和組合類型【例7-8】運用組合類型，創建一個函數，查詢指定學號的學生信息。CREATEORREPLACEFUNCTIONFUN7_8(VSNOS.SNO%TYPE)RETURNSVOIDASDECLARES_rowS;BEGINSELECT*INTOS_rowFROMSWHERES.SNO=VSNO;RAISENOTICE'%',S_row.SNO||''||S_ROW.SN||''||S_row.SG||''||S_row.SD||''||S_row.SP;RETURN;END;--調用函數SelectFUN7_8('2501101');7.1PL/SQL編程基礎第7章數據類型、表達式與運算符7.1.34.支持的其它類型記錄類型nameRECORD;記錄變量類似于行類型變量，但是它們沒有預定義的結構，只能通過SELECT或FOR命令來獲取實際的行結構，因此記錄變量在被初始化之前無法訪問，否則將引發運行時錯誤。注：RECORD不是真正的數據類型，只是一個占位符。自定義RECORD抽象數據類型關聯數組、嵌套表、可變數組、多維抽象數據類型、抽象數據類型方法學號姓名成績RECORD變量7.1PL/SQL編程基礎第7章數據類型、表達式與運算符7.1.35.表達式KingbaseES的表達式由運算符和操作數組成。Sum:=var1-var2操作數運算符表達式7.1PL/SQL編程基礎第7章數據類型、表達式與運算符7.1.36.運算符運算符操作NOT邏輯非+,-正號，負號*,/乘號，除號+,-加號，減號=,!=,<>,<,>,<=,>=,ISNULL，ISNOTNULL比較運算符。進行大小或相等的比較。其中!=和

都表示不等于，ISNULL判斷某一列的內容是否為空AND邏輯與OR邏輯或表7.1運算符及其優先級表KingbaseES中支持的各種運算符及其優先級如表所示，其中優先級按自上而下逐漸降低。已聲明了如下變量：DECLAREiint;cchar(4);現在為i賦值10，為c賦值'abcd'，正確的語句是（）。SETi=10；c='abcd' i=10，c='abcd'i:=10; c:='abcd'; i=10,c='abcd'ABCD提交單選題5分7.1PL/SQL編程基礎第7章控制結構和語句7.1.41.基本語句（1）賦值語句在PL/SQL程序中可以通過兩種方式給變量賦值。①直接賦值

變量名:=常量或表達式;例如：v_numNUMBER:=5;②通過SELECT..INTO賦值SELECT字段名1,…,字段名nINTO變量名1,…,變量名nfrom表名WHERE條件表達式；7.1PL/SQL編程基礎第7章控制結構和語句7.1.4（2）注釋語句PL/SQL中的注釋語句，也稱為注解，注釋內容通常是一些說明性文字，對程序的結構及功能給出簡要的解釋。注釋語句不是可執行語句，不被系統編譯，也不被程序執行。使用注釋語句的目的是為了使程序代碼易讀易分析，也便于日后的管理和維護。在PL/SQL中可以使用兩類注釋符：（1）ANSI標準的注釋符“--”用于單行注釋；（2）與C語言相同的程序注釋符，即“/*……*/”，“/*”用于注釋文字的開頭，“*/”用于注釋文字的結尾，可在程序中標識多行文字為注釋。塊注釋可以嵌套。1.基本語句7.1PL/SQL編程基礎第7章控制結構和語句7.1.4（2）注釋語句BEGIN--單行注釋：賦值語句單行注釋：賦值語句

para1:=20;para2:=30*para1;para3:=40+para2*para1;para4:='test';IF(para1<para2)AND(para2<para3)THENINSERTINTOtempVALUES(para3,para4);ENDIF;END;SELECT*FROMtemp;示例：/*

多行注釋：多行注釋：創建所需表結構創建所需表結構*/CREATETABLEIFNOTEXISTStemp(col1INT,lnameVARCHAR(10));DECLARE

--單行注釋：聲明變量單行注釋：聲明變量

para1INT;para2INT;para3INT;para4VARCHAR(10);1.基本語句7.1PL/SQL編程基礎第7章控制結構和語句7.1.4（3）打印語句語法格式：RAISE[level]'format'[,expression[,...]][USINGoption=expression[,...]];RAISE[level]condition_name[USINGoption=expression[,...]];RAISE[level]SQLSTATE'sqlstate'[USINGoption=expression[,...]];RAISE[level]USINGoption=expression[,...];RAISE;PL/SQL中要發出錯誤和消息，可使用RAISE語句;在RAISE語句之后是用于指定錯誤嚴重性的級別選項[level]。[level]有以下級別:DEBUG(調試)、LOG(日志)、NOTICE(注意)、INFO(信息)、WARNING(警告)、EXCEPTION(例外)如果未指定級別，則默認情況下，RAISE語句將使用EXCEPTION級別，這會引發錯誤并停止當前事務。格式是指定消息的字符串。該格式使用百分比(%)占位符，這些占位符將由下一個參數替換。占位符的數量必須與參數的數量匹配，否則，

將報告錯誤消息。1.基本語句7.1PL/SQL編程基礎第7章控制結構和語句7.1.4（3）打印語句1.基本語句語法格式：RAISE[level]'format'[,expression[,...]][USINGoption=expression[,...]];RAISE[level]condition_name[USINGoption=expression[,...]];RAISE[level]SQLSTATE'sqlstate'[USINGoption=expression[,...]];RAISE[level]USINGoption=expression[,...];RAISE;USING將其后的option=expression作為額外的消息附加到輸出的消息中。option關鍵字可以是MESSAGE、DETAIL、HINT和ERRCODE，expression可以是任意的字符串表達式。MESSAGE:設置錯誤消息文本。HINT:提供提示信息，以便更容易發現錯誤的根本原因。DETAIL︰提供有關錯誤的詳細信息。ERRCODE:標識錯誤代碼，可以通過條件名稱或直接由五個字符組成的SQLSTATE代碼。在格式二和格式三中不能指定格式串，可以在USING子句中提供需要顯示的錯誤信息。如果在RAISEEXCEPTION命令中沒有指定異常名或SQLSTATE，默認使用RAISE_EXCEPTION（P0001）。如果沒有指定要顯示的錯誤信息，默認使用異常名或SQLSTATE作為錯誤信息。7.1PL/SQL編程基礎第7章控制結構和語句7.1.4（3）打印語句1.基本語句語法格式：RAISE[level]'format'[,expression[,...]][USINGoption=expression[,...]];RAISE[level]condition_name[USINGoption=expression[,...]];RAISE[level]SQLSTATE'sqlstate'[USINGoption=expression[,...]];RAISE[level]USINGoption=expression[,...];RAISE;當通過SQLSTATE碼指定錯誤碼時，不局限于預定義的錯誤碼，它可以是由數字（0-9）或大寫字母（A-Z）任意組合成的長度為5的串。建議避免使用以3個0結束的錯誤碼，因為這種錯誤碼是某一類錯誤碼，只能通過捕獲整個類來捕獲。詳細條件名可查看KingbaseES錯誤代碼查詢手冊第五種格式的RAISE沒有任何參數，它只能用于EXCEPTION子句中，它將當前處理的錯誤拋給在該塊的上層塊來處理。7.1PL/SQL編程基礎第7章控制結構和語句7.1.4（3）打印語句1.基本語句【例7-9】使用函數SYSDATE和NOW()，在當前時間報告5個級別消息。BEGINRAISEINFO'信息級別消息%',SYSDATE;RAISEWARNING'警告級別消息%',SYSDATE;RAISENOTICE'注意級別消息%',NOW();RAISELOG'日志級別消息%',SYSDATE;RAISEDEBUG'調試級別消息%',NOW();END;注意：并非所有消息都報告給客戶端，只有INFO，WARNING和NOTICE級別的消息報告給客戶端。這由client_min_messages和

log_min_messages配置參數控制。7.1PL/SQL編程基礎第7章控制結構和語句7.1.42.IF語句IF和CASE語句讓你可以根據某種條件執行二選其一的命令。PL/SQL有三種式的IF：IF...THEN...ENDIFIF...THEN...ELSE...ENDIFIF...THEN...ELSIF...THEN...ELSE...ENDIF判斷條件語句1成立語句2不成立判斷條件語句1成立語句3不成立語句2判斷條件1語句1成立語句3不成立判斷條件2不成立語句2成立語句47.1PL/SQL編程基礎第7章控制結構和語句7.1.42.IF語句【例7-10】使用簡單分支(IF…THEN…ENDIF)結構，當輸入值i大于100時，輸出i的值。CREATEORREPLACEFUNCTIONtest_if7_10(iint)RETURNSVOIDASDECLAREBEGINifi>100then raisenotice'i的值為：%',i;endif;END;7.1PL/SQL編程基礎第7章控制結構和語句7.1.42.IF語句【例7-11】使用二重分支(IF…THEN…ELSE…ENDIF)結構，當輸入值i大于100時，輸出“i的值大于100”，否則，輸出“i的值小于等于100”。CREATEORREPLACEFUNCTIONtest_if7_11(iint)RETURNSVOIDASDECLAREBEGINifi>100then raisenotice'i的值大于100';else raisenotice'i的值小于等于100';endif;END;7.1PL/SQL編程基礎第7章控制結構和語句7.1.42.IF語句【例7-12】從E表中求出學號為2501105同學的平均成績，如果此平均成績大于或等于60分，則輸出“Pass!”信息，否則，輸出“Fail”。BEGINIF(SELECTAVG(GR)FROMEWHERESNo='2501105')>=60THENRAISENOTICE'Pass!';ELSERAISENOTICE'Fail!';ENDIF;END;7.1PL/SQL編程基礎第7章控制結構和語句7.1.42.IF語句【例7-13】從S數據表中讀取學號為2501105同學的數據記錄，如果存在，則輸出“存在學號為2501105的學生”，否則，輸出“不存在學號為S1的學生”。DECLARENAMEVARCHAR(200);BEGINIFEXISTS(SELECT*FROMSWHEREs.SNo='2501105')THENNAME:='存在學號為2501105的學生';ELSENAME:='不存在學號為2501105的學生';ENDIF;RAISENOTICE'%',NAME;END;7.1PL/SQL編程基礎第7章控制結構和語句7.1.42.IF語句【例7-14】使用多重分支(IF…THEN…ELSIF…THEN…ELSE…ENDIF)結構，當輸入值i大于100時，輸出i的值大于100，當輸入值為66時，輸出'i的值為66'，否則，輸出i的值小于等于100。CREATEORREPLACEFUNCTIONtest_if7?_14(iint)RETURNSVOIDASDECLAREBEGINifi>100then raisenotice'i的值大于100';elsifi=66then raisenotice'i的值為66';else raisenotice'i的值小于等于100';endif;END;注意：關鍵詞ELSIF可以寫成ELSEIF。ELSIF也可以寫多個。7.1PL/SQL編程基礎第7章控制結構和語句7.1.43.CASE語句（1）格式1：CASEsearch-expressionWHENexpression[,expression[...]]THENstatements[WHENexpression[,expression[...]]THENstatements...][ELSEstatements]ENDCASE;利用CASE表達式可以進行多分支選擇。在KingBaseES中，CASE表達式分為簡單表達式和搜索表達式兩種。CASE的簡單形式提供了基于操作數等值判斷的有條件執行。search-expression會被計算（一次）并且一個接一個地與WHEN子句中的每個expression比較。如果找到一個匹配，那么相應的statements會被執行，并且接著控制會被交給ENDCASE之后的下一個語句（后續的WHEN表達式不會被計算）。如果沒有找到匹配，ELSE語句會被執行。但是如果ELSE不存在，將會拋出一個CASE_NOT_FOUND異常。（1）簡單CASE7.1PL/SQL編程基礎第7章控制結構和語句7.1.43.CASE語句【例7-15】從學生表S中，選取學生2501103的Sno、SN和Sg，如果Sg為“男”則輸出“M”，如果為“女”則輸出“F”。CREATEORREPLACEPROCEDUREMYPROC7_15()ASDECLAREVsnoS.SNO%TYPE;VsnS.SN%TYPE;VSexS.SG%TYPE;VVSexCHAR(1);（1）簡單CASEBEGINSELECTS.SNo,S.SN,S.SGINTOVsno,Vsn,VSexFROMSWHERES.SNo='2501103';CASEVSexWHEN'男'THENVVSex:='M';WHEN'女'THENVVSex:='F';ENDCASE; RAISENOTICE'學號為:%',Vsno;RAISENOTICE'姓名為:%',Vsn;RAISENOTICE'性別為:%',VVSex;END;7.1PL/SQL編程基礎第7章控制結構和語句7.1.43.CASE語句（2）搜索CASE（2）格式2：CASEWHENboolean-expressionTHENstatements[WHENboolean-expressionTHENstatements...][ELSEstatements]ENDCASE;CASE的搜索形式基于布爾表達式真假的有條件執行。每一個WHEN子句的boolean-expression會被依次計算，直到找到一個得到真的。然后相應的statements會被執行，并且接下來控制會被傳遞給ENDCASE之后的下一個語句（后續的WHEN表達式不會被計算）。如果沒有找到為真的結果，ELSEstatements會被執行。但是如果ELSE不存在，那么將會拋出一個CASE_NOT_FOUND異常。7.1PL/SQL編程基礎第7章控制結構和語句7.1.43.CASE語句【例7-16】將E表中的學生平均成績轉變為成績等級。程序代碼如下：CREATEORREPLACEPROCEDUREMYPROC7_16()ASDECLAREv_gradeINT;v_resultVARCHAR(16);BEGINSELECTAVG(GR)INTOv_gradeFROMEWHEREsno='2501109';（2）搜索CASECASEWHENv_grade>=90ANDv_grade<=100THENv_result:='優秀';WHENv_grade>=80ANDv_grade<90THENv_result:='良好';

WHENv_grade>=70ANDv_grade<80THENv_result:='中等';WHENv_grade>=60ANDv_grade<70THENv_result:='及格';WHENv_grade>=0ANDv_grade<60THENv_result:='不及格';ELSEv_result:='無’;ENDCASE;RAISENOTICE'學號為2501105的平均成績:%',v_result;END;7.1PL/SQL編程基礎第7章控制結構和語句7.1.44.循環控制語句⑴簡單循環LOOP

簡單循環使程序不經過判斷就進入循環，在循環體中判斷條件是否滿足，一旦滿足條件，立即退出循環。簡單循環LOOP的基本語法格式如下:LOOP<循環體>/*執行循環體*/IF<條件表達式>THEN/*測試條件表達式是否符合退出條件*/EXIT;/*滿足退出條件，退出循環*/ENDIF;ENDLOOP;

多數數據庫服務器的過程化SQL都提供EXIT、BREAK或LEAVE等循環結束語句，保證LOOP語句塊能夠結束KingBaseES只提供EXIT循環結束？不成立其它語句成立設置循環初始化條件循環體代碼修改循環結束條件7.1PL/SQL編程基礎第7章控制結構和語句7.1.44.循環控制語句【例7-17】計算1～200之間所有能被3整除的數的個數及總和。用簡單循環LOOP完成相應功能的程序代碼如下：DECLAREsSMALLINT:=0;iSMALLINT:=1;numsSMALLINT:=0;BEGIN<<LABLE1>>⑴簡單循環LOOPLOOPIFi>200THENEXIT;--退出循環

ENDIF;IFi%3=0THENs:=s+i;nums:=nums+1;ENDIF;i:=i+1;ENDLOOPLABLE1RAISENOTICE'%',s;RAISENOTICE'%',nums;7.1PL/SQL編程基礎第7章控制結構和語句7.1.44.循環控制語句⑵WHILE-LOOP循環使用WHILE循環同樣可以執行循環，WHILE循環在條件滿足時執行循環體，當條件不再滿足，退出循環。WHILE循環的特點是先判斷循環條件，當循環條件滿足時才執行循環體操作,格式如下：WHILE<條件表達式>LOOP/*測試是否符合退出條件*/<循環體>/*執行循環體*/ENDLOOP;每次執行循環體語句之前，首先對條件進行求值如果條件為真，則執行循環體內的語句序列如果條件為假，則跳過循環并把控制傳遞給下一個語句循環結束？不成立其他語句成立設置循環初始化條件循環體代碼修改循環結束條件7.1PL/SQL編程基礎第7章控制結構和語句7.1.44.循環控制語句【例7-18】用WHILE-LOOP循環實現例7-17的功能，程序代碼如下：DECLAREsSMALLINT:=0;iSMALLINT:=1;numsSMALLINT:=0;BEGIN<<LABLE1>>⑵WHILE-LOOP循環WHILEi<=200LOOP IFi%3=0THENs:=s+i;nums:=nums+1;ENDIF;i:=i+1;ENDLOOPLABLE1;RAISENOTICE'%',s;RAISENOTICE'%',nums;END;7.1PL/SQL編程基礎第7章控制結構和語句7.1.44.循環控制語句⑶FOR-LOOP循環語法格式如下：[<<label>>]FORnameIN[REVERSE]expression1..expression2[BYexpression]LOOPstatementsENDLOOP[label];說明：簡單LOOP循環和WHILE循環的循環次數都是不確定的，FOR循環的循環次數是固定的；變量name會自動定義為類型integer并且只在循環內存在（任何該變量名的現有定義在此循環內都將被忽略）。REVERSE表示循環變量從最大值向最小值疊代。給出范圍上下界的兩個表達式在進入循環的時候計算一次。如果沒有指定BY子句，迭代步長為1，否則步長是BY中指定的值，該值也只在循環進入時計算一次。7.1PL/SQL編程基礎第7章控制結構和語句7.1.44.循環控制語句【例7-19】用FOR-LOOP循環實現例7-17的功能，程序代碼如下：DECLAREsSMALLINT:=0;iSMALLINT;numsSMALLINT:=0;BEGIN<<LABLE1>>⑶FOR-LOOP循環FORIIN1..200LOOPIFi%3=0THENs:=s+i;nums:=nums+1;ENDIF;ENDLOOPLABLE1;RAISENOTICE'%',s;RAISENOTICE'%',nums;END;7.1PL/SQL編程基礎第7章控制結構和語句7.1.45.跳轉語句⑴EXIT語句EXIT[label][WHENboolean-expression];①如果沒有給出label，那么最內層的循環會被終止，然后跟在ENDLOOP后面的語句會被執行。如果給出了label，那么它必須是當前或者更高層的嵌套循環或者語句塊的標簽。然后該命名循環或塊就會被終止，并且控制會轉移到該循環/塊相應的END之后的語句上。②如果指定了WHEN，只有boolean-expression為真時才會發生循環退出。否則，控制會轉移到EXIT之后的語句。③EXIT可以被用在所有類型的循環中，它并不限于在無條件循環中使用。【例7-20】計算100以內的所有正整數的和，但是當正整數為50時結束循環。其實現代碼如下:DECLARECOUNTSMALLINT:=0;V_sumSMALLINT:=0;BEGINFORIIN1..100LOOPIFI=50THENCOUNT:=I;EXIT;--退出循環

ENDIF;V_sum:=V_sum+I;ENDLOOP;RAISENOTICE'%,%',COUNT,V_sum;END;7.1PL/SQL編程基礎第7章控制結構和語句7.1.45.跳轉語句⑵CONTINUE語句CONTINUE[label][WHENboolean-expression];①如果沒有給出label，最內層循環的下一次迭代會開始。也就是，循環體中剩余的所有語句將被跳過，并且控制會返回到循環控制表達式（如果有）來決定是否需要另一次循環迭代。如果label存在，它指定應該繼續執行的循環的標簽。②如果指定了WHEN，該循環的下一次迭代只有在boolean-expression為真時才會開始。否則，控制會傳遞給CONTINUE后面的語句。③CONTINUE可以被用在所有類型的循環中，它并不限于在無條件循環中使用。【例7-21】計算100以內的所有正整數的和，但是當正整數為50時跳出當前循環，累加和不加50這個值。其實現代碼如下：DECLARECOUNTSMALLINT:=0;V_sumSMALLINT:=0;BEGINFORIIN1..100LOOPIFI=50THENCOUNT:=I;CONTINUE;--退出當前循環

ENDIF;V_sum:=V_sum+I;ENDLOOP;RAISENOTICE'%,%',COUNT,V_sum;END;7.1PL/SQL編程基礎第7章控制結構和語句7.1.45.跳轉語句⑶GOTO語句GOTO標識符;GOTO命令用來改變程序執行的流程，使程序跳到標有標識符的指定的程序行再繼續往下執行。作為跳轉目標的標識符可為數字與字符的組合。【例7-22】求1+2+3+…+10的總和。其實現代碼如下：DECLAREsSMALLINT:=0;iSMALLINT:=1;BEGIN<<BEG>>IF(i<=10)THENs:=s+i;i:=i+1;GOTOBEG;ENDIF;RAISENOTICE'%',s;END;7.1PL/SQL編程基礎第7章控制結構和語句7.1.46.返回語句返回語句RETURN用于函數中，主要包括單值返回和集合返回。（1）單值返回語句RETURNexpression;如果沒有使用表達式RETURN命令用于告訴這個函數已經完成執行了。如果返回標量類型，那么可以使用任何表達式。要返回一個復合(行)數值，必須寫一個記錄或者行變量的expression。7.1PL/SQL編程基礎第7章控制結構和語句7.1.46.返回語句（1）單值返回語句【例7-23】創建不返回值的函數，其實現代碼如下：CREATEORREPLACEFUNCTIONmyfunreturn7_23(in_var1int)RETURNSvoidASBEGINifin_var1>0thenRAISENOTICE'thereis%',in_var1;elseRETURN;endif;END;--調用函數CALLmyfunreturn7_23(11);7.1PL/SQL編程基礎第7章控制結構和語句7.1.46.返回語句（1）單值返回語句【例7-24】創建返回復合(行)數值函數示例。先創建復合結構類型F_STRU,命令如下：CREATETYPEF_STRUAS(col1int,col2text);再創建函數，返回復合(行)數值，其實現代碼如下：CREATEORREPLACEFUNCTIONmyfunreturn7_24(in_var1int,in_var2TEXT)RETURNSF_STRUASDECLAREF_varF_STRU;BEGINF_var.col1:=in_var1+1;F_var.col2:='結果為：'||in_var2;RETURNF_var;END;--調用函數CALLmyfunreturn7_24(1,2);7.1PL/SQL編程基礎第7章控制結構和語句7.1.46.返回語句（2）集合返回有三個語句可以用來從函數中返回集合數據，集合返回格式如下：RETURNNEXTexpression;可以用于標量和復合數據類型；對于復合類型，將返回一個完整的結果"table"。RETURNQUERYquery;將一條查詢的結果追加到一個函數的結果集中。RETURNQUERYEXECUTEcommand-string[USINGexpression[,...]];執行動態SQL。7.1PL/SQL編程基礎第7章控制結構和語句7.1.46.返回語句（2）集合返回【例7-25】使用RETURNNEXT示例。先創建表T_F,并插入3行記錄，命令如下：CREATETABLET_F(COL1INT,COL2INT,COL3TEXT);INSERTINTOT_FVALUES(1,2,'三');INSERTINTOT_FVALUES(4,5,'六');INSERTINTOT_FVALUES(7,8,'九');再創建函數，返回使用RETURNNEXT語句，實現將上述3條記錄輸出，其實現代碼如下：CREATEORREPLACEFUNCTIONmyfunrn7_25()RETURNSSETOFT_FASDECLAREr_varT_F%rowtype;BEGINFORr_varINSELECT*FROMT_FWHERECOL1>0LOOP--可在此處加入需要實現的功能代碼

RETURNNEXTr_var;--此語句返回SELECT的當前行

ENDLOOP;RETURN;END;--調用函數CALLmyfunrn7_25();7.1PL/SQL編程基礎第7章控制結構和語句7.1.46.返回語句（2）集合返回【例7-26】使用RETURNQUERY示例。先創建表T_S,命令如下：CREATETABLET_S(SnoCHAR(7),SnVARCHAR(18),SgCHAR(3));再創建函數，返回使用RETURNQUERY語句，實現將S表的男生輸出，其實現代碼如下：CREATEORREPLACEFUNCTIONmyfunrn7_26()RETURNSSETOFT_SASBEGINRETURNQUERYSELECTSno,Sn,SgFROMSWHERESg='男';END;--調用函數CALLmyfunrn7_26();7.1PL/SQL編程基礎第7章控制結構和語句7.1.46.返回語句（2）集合返回【例7-27】使用RETURNQUERYEXECUTE示例。CREATEORREPLACEFUNCTIONmyfunrn7_27(filterINT)RETURNSSETOFT_FASBEGINRETURNQUERYEXECUTE'SELECT*FROMT_FWHERECOL1>$1'USINGfilter;END;--調用函數CALLmyfunrn7_27(1);在循環語句中，退出循環的關鍵字是（）。BREAKEXITLEAVEGOABCD提交單選題5分7.1PL/SQL編程基礎第7章常用函數7.1.5為了讓用戶更方便的對數據庫進行操作，KingbaseES提供了非常豐富的內置函數，足以滿足開發應用程序的需要。PL/SQL中的函數其實就是一段程序代碼，以編譯的形式存放在數據庫中，并為后續的程序塊調用。用戶可以通過調用內置函數并為其提供所需的參數來執行一些特殊的運算或完成復雜的操作。KingbaseES提供的函數有數學函數（隨機函數、三角函數）、字符串函數（大對象函數、內建轉換）、二進制串函數、位串函數、數據類型格式化函數、日期和時間函數、枚舉支持函數、幾何函數、網絡地址函數、文本搜索函數、XML函數、JSON函數、序列操作函數、數組函數、范圍函數、聚集函數、窗口函數、集合返回函數、系統信息函數、系統管理函數、觸發器函數、事件觸發器函數、其他函數等。7.1PL/SQL編程基礎第7章常用函數7.1.51.數學函數【例7-28】使用數學函數完成絕對值、平方根、返回最小整數、冪運算和四舍五入載取運算。BEGINRAISENOTICE'abs(-10.51)絕對值=%',abs(-10.51);RAISENOTICE'sqrt(1024)平方根=%',sqrt(1024);RAISENOTICE'ceiling(5+7/5+8.0)最小整數=%',ceiling(5+7/5+8.0);RAISENOTICE'power(2,3)2的3次方=%',power(2,3);RAISENOTICE'round(2.870560,2)四舍五入截取2位=%',round(2.870560,2);RAISENOTICE'round(5.910569,5)四舍五入截取5位=%',round(5.910569,5);END;7.1PL/SQL編程基礎第7章常用函數7.1.52.字符串函數(1)CONCAT(s1,s2,…)函數CONCAT(s1,s2,…)返回結果為連接參數產生的字符串。任何參數為NULL，將被忽略。【例7-29】連接參數產生字符串示例。SELECTCONCAT('KingBaseES','8.3'),CONCAT('KingBaseES',NULL,'PL/SQL');7.1PL/SQL編程基礎第7章常用函數7.1.52.字符串函數(2)CONCAT_WS(x,s1,s2,…)函數CONCAT_WS(x,s1,s2,…)將除了第一個參數外的其他參數用分隔符串接在一起，其中的CONCAT_WS代表CONCAT帶分隔符（WithSeparator），是CONCAT()的派生函數。第一個參數x是其他參數的分隔符。分隔符的位置放在要連接的兩個字符串之間。分隔符既可以是一個字符串，也可以是其他參數。如果分隔符為NULL，結果就為NULL。【例7-30】帶分隔符連接參數產生字符串示例。SELECTCONCAT_ws('---','1st','2nd','3rd'),CONCAT_ws('***','1st',NULL,'3rd');7.1PL/SQL編程基礎第7章常用函數7.1.52.字符串函數(3)SUBSTRING(s,n,len)函數SUBSTRING(s,n,len)表示從字符串s返回一個長度為len的子字符串，起始于位置n。也可對n使用一個負值，則子字符串的位置起始于字符串結尾的n字符，即倒數第n個字符。【例7-31】返回子字符串示例。SELECTSUBSTRING('數據庫原理與技術',4)AS列1,SUBSTRING('數據庫原理與技術',1,5)AS列2,SUBSTRING('數據庫原理與技術',-2)AS列3;7.1PL/SQL編程基礎第7章常用函數7.1.52.字符串函數⑷UPPER函數與LOWER函數UPPER函數將小寫字符數據轉換為大寫的字符，LOWER將大寫字符數據轉換為小寫的字符。【例7-32】大小寫轉換示例。代碼如下。SELECTUPPER('hello'),LOWER('GOOD');7.1PL/SQL編程基礎第7章常用函數7.1.53.日期函數(1)獲取當前日期的函數CURRENT_DATE和獲取當前時間的函數CURRENT_TIME①CURRENT_DATE函數的作用是將當前日期按照‘YYYY-MM-DD’格式的值返回，具體格式根據函數用在字符串或是數字語境中而定。②CURRENT_TIME函數的作用是將當前時間以‘HH:MM:SS’的格式返回，具體格式根據函數用在字符串或是數字語境中而定。【例7-33】獲取當前日期和當前時間函數示例。SELECTCURRENT_DATEAS當前日期,CURRENT_TIMEAS當前時間;7.1PL/SQL編程基礎第7章常用函數7.1.53.日期函數(2)EXTRACT(typeFROMdate)獲取日期指定值的函數EXTRACT(typeFROMdate)函數從指定日期中提取指定的值。【例7-34】獲取當前年、月份和日示例。SELECTEXTRACT(YEARFROMCURRENT_DATE)AS年,EXTRACT(MONTHFROMCURRENT_DATE)AS月份,EXTRACT(DAYFROMCURRENT_DATE)AS日;7.1PL/SQL編程基礎第7章常用函數7.1.53.日期函數(3)如何計算年齡當表中的字段為出生日期，而需要年齡時，可通過當前年份減去出生年份獲得年齡。【例7-35】查詢T表中陳建設老師的年齡。SELECTTN,(EXTRACT(YEARFROMCURRENT_DATE)-EXTRACT(YEARFROMTD))AS年齡FROMTWHERETN='陳建設';7.1PL/SQL編程基礎第7章用戶自定義函數7.1.6KingBaseES函數也稱為KingBaseES存儲過程.KingBaseES函數或存儲過程是存儲在數據庫服務器上并可以使用SQL界面調用的一組SQL和過程語句(聲明，分配，循環，控制流程等)。可以利用PL/SQL命令來創建（CREATEFUNCTION）、修改（ALTERFUNCTION）和刪除（DROPFUNCTION）。7.1PL/SQL編程基礎第7章用戶自定義函數7.1.61.創建函數和調用函數語法格式：CREATE[ORREPLACE]FUNCTIONfunction_name(arguments)RETURNSreturn_datatypeASDECLAREdeclaration;[...]BEGIN<function_body>[...]RETURN{variable_name|value}END;LANGUAGEplsql;參數說明function_name:指定函數的名稱。[ORREPLACE]:是可選的，它允許修改/替換現有函數。DECLARE:定義參數(參數名寫在前面類型寫在后面)。BEGIN~END:在中間寫方法主體。RETURN:指定要從函數返回的數據類型(它可以是基礎，復合或域類型，或者也可以引用表列的類型)。LANGUAGE:它指定實現該函數的語言的名稱。7.1PL/SQL編程基礎第7章用戶自定義函數7.1.61.創建函數和調用函數【例7-36】自定義一個標量值函數，判斷一個整數是