PL_SQL基础PPT演示课件

,OraclePL/SQL,CourseOutline,PL/SQL程序结构常用数据型态及变量宣告指定变量值与变量初始化SELECT.INTO.运算常用的OracleBuild-inFunction流程控制结构循环,CourseOutline,CursorRecord与PL/SQLTableTransactionManagementExceptionHandlingStoredFunctionandProcedurePackageDatabaseTrigger练习与测验,PL/SQL是ProceduralLanguage/SQL的缩写，它是由SQL扩充而来的，它增加了一些对SQL控制的流程，以补足SQL的缺陷。PL/SQL基本语法结构如下:DECLARE变量,常数,CURSOR,使用者定义型态宣告BEGIN程序主逻辑;(包含SQL语法与PL/SQL语法)EXCEPTION例外状况处理逻辑;(当语法错误发生时会跳至此段开始执行)END;,PL/SQL程序结构,PL/SQL程序结构,PL/SQL区块可以巢状的结构层层包覆:,DECLARE.BEGIN.DECLARE.BEGIN.EXCEPTION.END;.EXCEPTION.END;,Notes每一个PL/SQL区块皆被视为完整独立的程序单元外层程序代码不能使用内层的变量,但内层程序代码可使用外层的变量若内外层都宣告同名称的变量,则内层程序代码只能使用内层的变量DECLARE及EXCEPTION区为非必要,例如,您的程序若不需要变量,则DECLARE区不需要宣告内层区块END;结束后,该区块宣告的变量即被系统回收,不复存在,PL/SQL程序结构,固定长度字符串CHAR(不建议使用)数据长度不足时,后面会自动补空白DECLAREc_nameCHAR(100);BEGIN变动长度字符串VARCHAR2注意:Oracle不会自动截去变量值尾部的空白DECLAREv_nameVARCHAR2(100);BEGIN,常用数据型态及变量宣告,变动长度数字NUMBER，38位有效数字DECLAREn_salNUMBER;BEGIN固定长度数字NUMBER，指定整数及小数字数DECLAREn_salNUMBER(10);-整数10位，无小数n_rateNUMBER(5,2);-整数3位，小数2位BEGIN,常用数据型态及变量宣告,日期DATE，Oracle以Binary方式储存，精准度到秒DECLAREd_hiredateDATE;BEGIN布尔BOOLEAN，只有TRUE，FALSE二种值DECLAREb_flagBOOLEAN;BEGIN,常用数据型态及变量宣告,使用:=作Assignment=则是相等(Equal)的比较(Comparison)运算字符串值前后需加单引号v_name:=John;固定的常数值可以加CONSTANT设定，且必须在宣告时给予初始值，后续则不可再给予任何新值，否则会发生编译错误。DECLAREn_piCONSTANTNUMBER:=3.1415926;BEGIN,常用数据型态及变量宣告,日期可用TO_DATE指定其格式，比较明确并确保不会因NSL_DATE_FORMAT，NLS_LANG的改变而导致错误。TO_DATE(20060101235959,YYYYMMDDHH24MISS);宣告变量可用%type引用Table的结构,当Table结构修改时(例如字段加宽)，该变量可自动随之调整，不必费心去查它真正的数据型态或长度。%rowtype建议尽量勿在Form用，因当Table结构修改时，Form必须重新compile，否则Form执行时会出现错误。,常用数据型态及变量宣告,在PL/SQL中除了使用:=指定变数值外，尚可用SELECT.INTO.将数据存入变量中DECLAREV_ENAMEEMP.ENAME%TYPE;BEGINSELECTENAMEINTOV_ENAMEFROMEMPWHEREEMPNO=2434;END;,SELECTINTO,适用于SQL及PL/SQL以|串接字符串V_MESSAGE:=Mynameis|V_ENAME;以+、-、*、/做四则运算，同数学运算规则。n_sal:=n_sal*1.1n_comm/5+10;以NOT、AND、OR作布尔运算，在没有括号的情况下，运算的执行先后顺序为先NOT，其次AND，最后为OR；有括号时则以括号为准。aORNOTbANDcORd?(aOR(NOTb)ANDc)ORd),运算,运算,运算,常用Built-inFunction,NumberFunctions,常用Built-inFunction,NumberFunctions,常用Built-inFunction,CharacterFunctions(回传字符串),常用Built-inFunction,CharacterFunctions(回传字符串),常用Built-inFunction,CharacterFunctions(回传Number),常用Built-inFunction,DateFunctions,常用Built-inFunction,ConversionFunctions,常用Built-inFunction,Conversion的关系,常用Built-inFunction,常用ConversionFormat,常用Built-inFunction,常用Built-inFunction,GroupFunctions,GroupFunction内的参数预设为ALL,流程控制结构,以IF叙述来控制程序的执行流程，语法结构如下:IF(boolean_expression)THEN.(ELSIF(boolean_expression)THEN.ELSIF(boolean_expression)THEN.ELSE.)ENDIF;其中ELSIF及ELSE叙述为非必要结构,流程控制结构,IF叙述可为巢状多层:IF(boolean_expression)THEN.IF(boolean_expression)THEN.ELSIF(boolean_expression)THEN.ENDIF;.ENDIF;,循环-LOOP,LOOP循环结构为无条件执行循环内容，必须用EXIT来跳离循环结构，或RETURN结束程序。语法结构如下:LOOPIF(exit_condition)THENEXIT;ENDIF;ENDLOOP;LOOP循环结构可为巢状多层，如IF叙述。,循环-LOOP,Example：取出字符串第双数字数字元DECLAREaVARCHAR2(10):=ABCDEFGHIJ;bVARCHAR2(10);cNUMBER:=1;BEGINLOOPIFcLENGTH(ABCDEFGHIJ)THENEXIT;ENDIF;IFMOD(c,2)=0THENb:=b|SUBSTR(a,c,1);ENDIF;c:=c+1;ENDLOOP;END;,循环WHILE.LOOP,WHILE.LOOP循环结构以条件决定是否继续执行循环内容，可用EXIT来跳离循环结构，或RETURN结束程序。语法结构如下:WHILE(condition)LOOPENDLOOP;WHILE.LOOP循环结构可为巢状多层，如IF叙述。,循环WHILE.LOOP,Example：取出字符串第双数字数字元DECLAREaVARCHAR2(10):=ABCDEFGHIJ;bVARCHAR2(10);cNUMBER:=1;BEGINWHILE(cSELECTSF_GET_CUSTOMER_NAME(01)FROMdual;执行StoredProcedure程序中直接呼叫：SP_CACULATE_SALARY(v_yymm,v_empno);SQL*Plus：SQLEXECSP_CACULATE_SALARY(200601,0001);,StoredFunctionandProcedure,分为Oracle内建Package与自定Package两种。通常为自定数据型态、Function或Procedure所组成。Package必须包含以下两部分Specification：宣告Package的架构。Body：Package的程序主体。Package中，若有互相呼叫的程序段，需注意在Specification与Body的先后顺序。,Package,自定Package语法结构SpecificationCREATEORREPLACEPACKAGEpackage_nameIS|ASpublictypeanditemdeclarationssubprogramspecificationsENDpackage_name;BodyCREATEORREPLACEPACKAGEBODYpackage_nameIS|ASprivatetypeanditemdeclarationssubprogrambodiesENDpackage_name;,Package,自定Package范例：SpecificationCREATEORREPLACEPACKAGEmy_pkgISPROCEDUREp_get_ename(v_noINVARCHAR2);ENDmy_pkg;BodyCREATEORREPLACEPACKAGEBODYmy_pkgISPROCEDUREp_get_ename(v_noINVARCHAR2)ISBEGIN.END;ENDmy_pkg;,Package,Oracle内建PackageOWNER为SYS，任何Schema皆可使用。常用的有DBMS_OUTPUT：SQL*Plus中响应讯息。DBMS_JOB：产生定时执行的程序段。DBMS_SQL：执行传入的SQL程序段。UTL_FILE：读取外部的File(文字文件)。,Package,Package,其它常见的Oracle内建Package,因对Table做DML(insert、update、delete)所触动的DatabaseObject。基本语法结构如下:CREATEORREPLACETRIGGERtrigger_nameBEFORE|AFTERINSERT|ORUPDATEOFcolumn(,)|ORDELETEONtable_name(REFERENCINGOLDASold|NEWASnew)FOREACHROW(WHEN(condition)DECLARE-变量宣告区.BEGIN.EXCEPTION.END;,DatabaseTrigger,DatabaseTrigger,可使用:new与:old，直接取作用的table字段值。例如，:new.column_name、:old.colu