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

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)部分可执行部分中的语句，在执行过程中出错或出现非正常景象时，所做的呼应处置DECLAREBEGINEXCEPTIONENDPL/SQL块构造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;结果前往：‘defg123’Selectsubstr(‘abcdefg123’,4,2)fromdual;结果前往：‘de’Selectsubstr(‘abcdefg123’,-4,2)fromdual;结果前往：‘g1’RTRIM(s1,s2)前往删除从最右边算起出如今s2中的字符的s1。s2缺省为空格实例：Selectrtrim(‘aabbccdd’,’cd’)fromdual;结果前往：‘aabb’Selectrtrim(‘aabbccdd’,’dc’)fromdual;结果前往：‘aabb’ORACL内置的SQL函数Concat(s1,s2)前往串接上s2之后的s1.该函数与||运算符作用一样。实例：selectconcat(‘abc’,’def’)fromdual;前往结果：‘abcdef’select‘abc’||’def’fromdual;前往结果：‘abcdef’Length(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,120ORACL内置的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条件=值1THEN

RETURN(翻译值1)

ELSIF条件=值2THEN

RETURN(翻译值2)

......

ELSIF条件=值nTHEN

RETURN(翻译值n)

ELSE

RETURN(缺省值)

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)default‘oracle’;代表数据库列的变量先看一个例如：创建一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_empm:=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中运用SQL－SELECT语句SELECT语句：将数据从数据库中检索出来并放入PL/SQL变量中。格式：SELECT<表列>INTO<变量>FROM<表>例：查询某个雇员的姓名及工资。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_FOUNDPL/SQL中运用SQL在PL/SQL中，对数据库进展插入(INSERT)、删除(DELETE)、修正〔UPDATE〕语句，其语法方式与SQL中的是完全一样的。例：在EMP表中删去某个雇员。BEGINDELETEempWHEREempno=7788;COMMIT;END;PL/SQL的执行部分——流程控制语句流程控制语句主要有三种：条件控制循环控制跳转控制流程控制语句——条件控制语法格式：IF〈条件〉THEN〈语句〉；[ELSIF〈条件〉THEN〈语句〉；][ELSE〈语句〉；]ENDIF；例：根据职务浮开工资IFv_job=‘MANAGER’THENv_sal:=v_sal*1.3;ELSIFv_job=‘SALESMAN’THENv_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_counter>50；V_count:=v_count+1;ENDLOOP；循环控制——FOR循环语法格式：FOR〈循环变量〉IN[REVERSE]〈下界〉..〈上界〉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_counter<=50LOOPINSERTINTOtemp_tableVALUES(v_counter);V_count:=v_count+1;ENDLOOP；跳转控制语句语法格式：<<标号>>……GOTO<<标号>>;

在进展PL/SQL编程时,尽量防止或不用GOTO语句,由于这种无条件的跳转语句突破了程序的逻辑性,有悖于自顶向下的编程风格.PL/SQL游标的运用游标(CURSOR)的功能,是ORALCE系统为了将一切查询结果前往给用户程序而提供的。一个游标,实践上是在内存中开辟一个任务区,它对应一条SELECT语句。当翻开游标时，就是执行游标所对应的SELECT语句，并将其查询结果放入任务区，并且指针指向任务区的首部。经过光标上的操作可以把这些记录检索到客户端的运用程序。CURSOR内存区POINTERSELECT…INTO…:只能查询数据库的单条记录，并把记录的数据赋给变量。游标——定义和支配游标步骤：定义游标翻开游标从游标中取值封锁游标定义游标定义游标，就是定义一个游标名，以及与其相对应的SELECT语句。语法格式：CURSOR〈游标名〉IS〈SELECT子句〉；例如：定义一个包含一切雇员记录的游标。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=‘MANAGER’THENv_emp.sal:=v_emp.sal*1.3;ELSIFv_emp.job=‘SALESMAN’THENv_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=‘MANAGER’THENv_emp.sal:=v_emp.sal*1.3;ELSIFv_emp.job=‘SALESMAN’THENv_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自动将其引发。用户定义的错误程序执行过程中，出现编程人员以为非正常的。对这种异常情况的处置，需在定义部分定义，然后显式由地将其引发。异常处置语法格式：EXCEPTIONWHEN<异常情况1>THEN〈语句〉；[WHEN<异常情况2〉THEN〈语句〉；…[WHENOTHERSTHEN〈语句〉；]OTHERS：指没有列在异常处置部分中的其他异常情况。DECLAREBEGINEXCEPTIONENDPL/SQL块执行过程异常发生异常处置异常处置预定义的ORACLE错误预定义的异常名称错误号说明CURSOR_ALREADY_OPENORA-6511试图打开一个已打开的光标LOGIN_DENIEDORA-1017无效的用户名或者口令NO_DATA_FOUNDORA-1403查询未找到数据NOT_LOGGED_ONORA-1012还未连接就试图数据库操作DUP_VAL_ON_INDEXORA-0001试图破坏一个唯一性限制TIMEOUT_ON_RESOURCEORA-0051发生超时TRANSACTION_BACKED_OUTORA-006由于死锁提交被退回TOO_MANY_ROWSORA-1422SELECTINTD命令返回的多行异常处置预定义异常例如：BEGINinsertintoemp(empno,ename)valu