福建省电力公司oracle培训教材-PLSQL语言篇.ppt

ORACLE入门PL/SQL语言篇,技术支持部汤庆锋福州磬基电子有限公司,本课程学习内容,PL/SQL简介PL/SQL数据类型(ORACLE的数据类型)ORACL内置的SQL函数PL/SQL中使用SQLPL/SQL中游标的使用动态PL/SQLPL/SQL的异常处理,PL/SQL简介,PL/SQL(ProceduralLanguage/SQL)即模块化的程序设计语言，用于从各种环境中访问ORACLE数据库。它具备了许多SQL中所没有的过程化属性方面的特点。主要包括：变量和类型控制结构（条件语句、循环语句)过程、函数游标异常处理,PL/SQL程序的用途,无名块就是没有命名的PL/SQL块，它可以嵌入某一个应用之中.存储过程、函数也就是命名了的PL/SQL块，它可以接收参数，并且可以重复的被调用。触发器是与数据库中的表相关的PL/SQL块，可以自动的触发。包命名了的PL/SQL块，由一组相关的过程、函数和标识符组成。,PL/SQL的程序结构,PL/SQL的基本单位是“块”(Block)。所有的PL/SQL程序都是由一个或多个PL/SQL块构成的，这些块可以相互进行嵌套。通常一个块完成程序的一个单元的工作。一个基本的块由三个部分组成：定义部分定义变量、常量、游标、异常处理可执行部分包括对数据库进行操作的SQL语句，以及对块中的语句进行组织、控制的PL/SQL语句。异常处理（Exception)部分可执行部分中的语句，在执行过程中出错或出现非正常现象时，所做的响应处理,PL/SQL数据类型,PL/SQL数据类型,常用的数据类型CHAR：存放固定长度的字符串VARCHAR2：存放可变长度的字符串NUMBER：存放0、正负数、浮点数DATE：存放时间数据（包括日期和时间）LONG：存放变长字符串。一般用来存储大文本RAWLONG存放多媒体数据,如声音、图片例如：创建一雇员表CREATETABLEemp(empnonumber(4),enamevarchar2(10),hiredatedate,salnumber(7,2),deptnonumber(2);,ORACLE内置的SQL函数,SQL函数按照传入参数的类型，可分为字符串函数、数值函数、日期函数、其他函数。以下分别列举较常用的部分进行说明。字符串函数：UPPER(s)将字符串s转换成大写的形式返回。LOWER(s)将字符串s转换成小写的形式返回。SUBSTR(s,a,b)返回从字符位置a开始有b个字符长的s的一部分。若a为正数:从左边向右边计算若a为负数:从右边向左边计算实例：Selectsubstr(abcdefg123,4)fromdual;结果返回：defg123Selectsubstr(abcdefg123,4,2)fromdual;结果返回：deSelectsubstr(abcdefg123,-4,2)fromdual;结果返回：g1RTRIM(s1,s2)返回删除从最右边算起出现在s2中的字符的s1。s2缺省为空格实例：Selectrtrim(aabbccdd,cd)fromdual;结果返回：aabbSelectrtrim(aabbccdd,dc)fromdual;结果返回：aabb,ORACL内置的SQL函数,Concat(s1,s2)返回串接上s2之后的s1.该函数与|运算符作用相同。实例：selectconcat(abc,def)fromdual;返回结果：abcdefselectabc|deffromdual;返回结果：abcdefLength(s)以字节为单位返回字符串s的长度。,ORACL内置的SQL函数,数值函数Ceil(n)返回大于或等于n的整数Selectceil(18.6),ceil(-18.6)fromdual;Floor(n)返回小于或等于n的整数Selectfloor(18.6),floor(-18.6)fromdual;Mod(x,y)返回x除以y得余数，若y为0，则返回x。Selectmod(23,5),mod(4,1.3)fromdual;返回结果：1.1,0.1Round(x,y)返回舍入到小数点右边y为的x值。Selectround(1.56),round(1.56,1),round(123.4,-1)fromdual;返回结果：1.1,0.1,120,ORACL内置的SQL函数,日期函数Sysdate返回当前的日期和时间Add_months(D,x)Last_day(D)返回日期D的月份的最后一天的日期Months_Between(D1,D2)返回在D1和D2之间月的数目。Trunc(D,format)返回结尾由format指定的单位的日期。示例：Selecttrunc(sysdate,year)fromdual;返回今年的第一天Selecttrunc(sysdate,mm)fromdual;返回本月的第一天Selecttrunc(sysdate,D)fromdual;返回本周的第一天,ORACL内置的SQL函数,转换函数To_char(D，format)将日期转换为指定格式的字符串。示例：Selectto_char(sysdate,yyyy/mm/ddhh:mi:ss)fromdual;To_Date(string,format)将字符串转换成日期格式示例：Selectto_date(2000/10/01,yyyy/mm/dd)fromdual;Last_day(D)返回日期D的月份的最后一天的日期To_Number(string,format),ORACL内置的SQL函数,其它函数Nvl(a,b)空值替换函数，若a为空，则替换成b。示例：Selectename,sal,sal+nvl(comm,0)fromdual;DECODE(条件,值1,翻译值1,值2,翻译值2,.值n,翻译值n,缺省值)该函数的含义如下：IF条件=值1THENRETURN(翻译值1)ELSIF条件=值2THENRETURN(翻译值2).ELSIF条件=值nTHENRETURN(翻译值n)ELSERETURN(缺省值)ENDIF,PL/SQL的注释,注释增强了可阅读性，使得程序更易于理解。单行注释-comment多行注释/*comment*/注意：此注释不能作用在SQL语言上。示例：DECLAREv_deptnonumber(2);-与雇员表中部门代码字段交互的变量v_salnumber(7,2);-与雇员表中工资字段交互的变量BEGIN/*thisisatest!*/selectdeptno,salintov_deptno,v_salfromempwhereempno=7788;END;,PL/SQL块的定义部分,在PL/SQL块中引用的所有标识符，都必须在定义部分中明确定义。定义常量格式：标识符CONSTANT数据类型：=表达式例：定义一常量PI，值为3.14。PICONSTANTNUMBER(3,2):=3.14;定义标量型变量标量型数据类型，是指数据类型为个体型。格式：NOTNULL:=|DEFAULT例：定义一宽度为10个字符的字符串变量X。DECLAREXCHAR(5)；yCHAR(5):=ORACLE;ZCHAR(5)defaultoracle;,代表数据库列的变量,先看一个示例：创建一PL/SQL块，根据部门号，返回部门名称.DECLAREv_dnamedept.dname%type;BEGINSELECTdnameINTOv_dnameFROMDEPTWHEREdeptno=10;DBMS_OUTPUT.PUT_LINE(v_dname);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(sorry:nodatafound!);END;问题：所引用的数据库表中的数据类型不知道？所引用的数据库表中的数据类型将来改变改变怎么办？,PL/SQL块的定义部分,另一种定义标量型变量的方法%TYPE定义一个变量,其数据类型与已知变量的数据类型相同，或者与数据库表的某个列的数据类型相同。%TYPE的优点在于：所引用的数据库表中的数据类型可以不必知道。所引用的数据库表中的数据类型可以实时改变。格式：NOTNULL:=|DEFAULT.%TYPE例：定义一个变量，其数据类型基于另一个变量DECLAREV_1NUMBER(7,2);V_11V1%TYPE:=12345.6;例：定义一个变量，其数据类型基于数据库中表的列DECLAREv_enameEMP.ENAME%TYPE;V_SALEMP.SAL%TYPE;,PL/SQL块的定义部分,另一种定义组合型变量的方法%ROWTYPE定义一个变量,其数据类型与数据库表的数据结构相同。%ROWTYPE的优点在于：所引用的数据库表中的数据类型可以不必知道。所引用的数据库表中的数据类型可以实时改变。简易格式：%ROWTYPE例：DECLAREv_empemp%rowtype;BEGINSELECT*INTOv_empFROMempWHEREempno=7788;DBMS_OUTPUT.PUT_LINE(v_emp.empno);DBMS_OUTPUT.PUT_LINE(v_emp.ename);DBMS_OUTPUT.PUT_LINE(v_emp.job);DBMS_OUTPUT.PUT_LINE(v_emp.sal);END;,变量的引用和赋值,标量变量赋值格式：:=；例：V_NAME:=JOAN;v_demptno:=10;组合型变量赋值格式：变量.域名（主键值）：=表达式；例：v_emp.sal:=8888;v_m:=8888;,PL/SQL中使用SQL,在PL/SQL块中，通过SQL语句对ORACLE数据库中的数据进行存取。在PL/SQL中：可以使用的SQL语句有：SELECT、INSERT、DELETE、UPDATE、COMMIT、ROLLBACK不可以直接使用的SQL语句有：数据定义语句（DDL），如：CREATETALBE，DROPTABLE数据控制语句（DCL），如：GRANT、REVOKE备注：在PL/SQL2.1以上版本，允许通过DBMS_SQL包来创建动态SQL语句。,PL/SQL中使用SQLSELECT语句,SELECT语句：将数据从数据库中检索出来并放入PL/SQL变量中。格式：SELECTINTOFROM例：查询某个雇员的姓名及工资。DECLAREv_empnoemp.empno%type:=7788;v_enameemp.ename%type;v_salemp.sal%type;BEGINselectename,salintov_ename,v_salfromempwhereempno=v_empno;DBMS_OUTPUT.PUT_LINE(v_empno|v_ename|v_sal);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(sorry:nodatafound!);END;/PL/SQL中的SELECT语句中必须包含INTO子句，而且对应的个数要相同，位置要一一对应。查询结果只返回一条记录，否则会产生异常情况。（1）查询结果多于一条记录异常变量：TOO_MANY_ROWS（2）查询结果没有返回记录异常变量：NO_DATA_FOUND,PL/SQL中使用SQL,在PL/SQL中，对数据库进行插入(INSERT)、删除(DELETE)、修改（UPDATE）语句，其语法形式与SQL中的是完全一样的。例：在EMP表中删去某个雇员。BEGINDELETEempWHEREempno=7788;COMMIT;END;,PL/SQL的执行部分流程控制语句,流程控制语句主要有三种：条件控制循环控制跳转控制,流程控制语句条件控制,语法格式：IF条件THEN语句；ELSIF条件THEN语句；ELSE语句；ENDIF；,例：根据职务浮动工资IFv_job=MANAGERTHENv_sal:=v_sal*1.3;ELSIFv_job=SALESMANTHENv_sal:=v_sal*1.2;ELSEv_sal:=v_sal*1.1;ENDIF；updateempsetsal=v_salwhereempno=1234;,流程控制语句循环控制,在PL/SQL中循环控制的有以下四种：简单循环FOR循环WHERE循环用于游标的FOR循环,循环控制简单循环,语法格式：LOOP语句1；语句2；EXITWHEN条件；ENDLOOP；,例：把数值1到50顺序插入表中。V_counter:=1;LOOPINSERTINTOtemp_tableVALUES(v_counter);EXITWHENv_counter50；V_count:=v_count+1;ENDLOOP；,循环控制FOR循环,语法格式：FOR循环变量INREVERSE下界.上界LOOP语句1；语句2；ENDLOOP；REVERSE:使计数器由上界到下界递减计数,例：把数值1到50顺序插入表中。FORv_counterIN1.50LOOPINSERTINTOtemp_tableVALUES(v_counter);ENDLOOP；,循环控制WHILE循环,语法格式：WHILE条件LOOP语句1；语句2；ENDLOOP；,例：把数值1到50顺序插入表中。V_counter:=1;WHILEv_counterGOTO;在进行PL/SQL编程时,尽量避免或不用GOTO语句,因为这种无条件的跳转语句打破了程序的逻辑性,有悖于自顶向下的编程风格.,PL/SQL游标的使用,游标(CURSOR)的功能,是ORALCE系统为了将所有查询结果返回给用户程序而提供的。一个游标,实际上是在内存中开辟一个工作区,它对应一条SELECT语句。当打开游标时，就是执行游标所对应的SELECT语句，并将其查询结果放入工作区，并且指针指向工作区的首部。通过光标上的操作可以把这些记录检索到客户端的应用程序。,SELECTINTO:只能查询数据库的单条记录，并把记录的数据赋给变量。,游标定义和操纵游标,步骤：定义游标打开游标从游标中取值关闭游标,定义游标定义游标，就是定义一个游标名，以及与其相对应的SELECT语句。语法格式：CURSOR游标名ISSELECT子句；示例：定义一个包含所有雇员记录的游标。cursorcur_empisselect*fromemp;,打开游标打开游标，就是执行游标所对应的SELECT语句，将其查询结果放入工作区，并且指针指向工作区的首部。语法格式：OPEN游标名；,从游标中取值取值工作是将游标工作区中的数据取出一行，放入指定的输出变量中。语法格式：FETCH游标名INTO变量1,变量2；示例：fetchcur_empintov_empno,v_ename,v_sal,v_comm,v_deptno关闭游标释放与该游标相关的资源。语法格式：CLOSE;示例：closecur_emp;,游标的属性,从游标工作区中逐一地取数据，可以在循环中完成。但循环的开始以及结束，需以游标属性为依据。游标属性有：%ISOPEN：判断游标是否被打开%NOTFOUND：判断何时中断循环%FOUND：与%NOTFOUND相反%ROWCOUNT：实际从游标工作区抽取的记录数示例：Opencur_emp;Loopfetchcur_empintov_empno,v_ename,v_sal,v_deptno;exitwhencur_emp%NOTFOUND;Endloop;,游标用于游标的FOR循环,游标的FOR循环，是一种简单的游标操作方法，系统隐式地进行游标的打开、提取数据、循环、关闭。格式：FOR记录变量IN游标名LOOP语句;ENDLOOP;:由系统隐含定义的记录名示例：Declarecursorcur_empisselect*fromemp;Beginforv_empincur_emploopDBMS_OUTPUT.PUT_LINE(v_emp.ename);DBMS_OUTPUT.PUT_LINE(v_emp.sal);endloop;End;,一个完整的示例,例：建立一存储过程，根据职务修改工资CREATEORREPLACEPROCEDUREp_update_salASCURSORcur_empISSELECT*FROMemp;v_empcur_emp%ROWTYPE;BEGINOPENcur_emp;LOOPFETCHcur_empINTOv_emp;EXITWHENcur_emp%NOTFOUND;IFv_emp.job=MANAGERTHENv_emp.sal:=v_emp.sal*1.3;,ELSIFv_emp.job=SALESMANTHENv_emp.sal:=v_emp.sal*1.2;ELSEv_emp.sal:=v_emp.sal*1.1;ENDIF；UPDATEempSETsal=v_emp.salWHEREempno=v_emp.empno;ENDLOOP;CLOSEcur_emp;COMMIT;END;,一个完整的示例(用FOR循环),CREATEPROCEDUREp_update_salASCURSORcur_empISSELECT*FROMemp;BEGINFORv_empINcur_empLOOPIFv_emp.job=MANAGERTHENv_emp.sal:=v_emp.sal*1.3;ELSIFv_emp.job=SALESMANTHENv_emp.sal:=v_emp.sal*1.2;ELSEv_sal:=v_sal*1.1;ENDIF；UPDATEempSETsal=v_emp.salWHEREempno=v_emp.empno;ENDLOOP;COMMIT;END;,示例,DECLARECURSORc1isSELECTename,empno,salFROMempORDERBYsalDESC;-startwithhighestpaidemployeemy_enameCHAR(10);my_empnoNUMBER(4);my_salNUMBER(7,2);BEGINOPENc1;FORiIN1.5LOOPFETCHc1INTOmy_ename,my_empno,my_sal;EXITWHENc1%NOTFOUND;INSERTINTOtempVALUES(my_sal,my_empno,my_ename);COMMIT;ENDLOOP;CLOSEc1;END;,异常处理,PL/SQL中，将程序执行过程中的一个警告或错误称为一个异常(EXCEPTION)。异常情况的种类有三种：预定义的ORACLE错误ORACLE预定一的异常情况大约有24个。对这种异常情况的处理，无须在程序中定义，由ORACLE自动将其引发。非预定义的ORACLE错误即其他标准的ORACLE错误。对这种异常情况的处理，需在定义部分定义，然后由ORACLE自动将其引发。用户定义的错误程序执行过程中，出现编程人员认为非正常的。对这种异常情况的处理，需在定义部分定义，然后显式由地将其引发。,异常处理,语法格式：EXCEPTIONWHENTHEN语句；WHEN异常情况2THEN语句；WHENOTHERSTHEN语句；OTHERS：指没有列在异常处理部分中的其他异常情况。,DECLARE,BEGIN,EXCEPTION,END,PL/SQL块执行过程,异常发生异常处理,异常处理,预定义的ORACLE错误,异常处理,预定义异常示例：BEGINinsertintoemp(empno,ename)values(7788,testuser);EXCEP