福建省电力公司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数据类型字段类型中文说明限制条件其它说明CHAR固定长度字符串最大长度2000bytesVARCHAR2可变长度的字符串最大长度4000bytes可做索引的最大长度749NCHAR根据字符集而定的固定长度字符串最大长度2000bytesNVARCHAR2根据字符集而定的可变长度字符串最大长度4000bytesDATE日期（日-月-年）DD-MM-YY（HH-MI-SS）经过严格测试，无千虫问题LONG超长字符串最大长度2G（231-1）足够存储大部头著作RAW固定长度的二进制数据最大长度2000bytes可存放多媒体图象声音等LONGRAW可变长度的二进制数据最大长度2G同上BLOB二进制数据最大长度4GCLOB字符数据最大长度4GNCLOB根据字符集而定的字符数据最大长度4GBFILE存放在数据库外的二进制数据最大长度4GROWID数据表中记录的唯一行号10bytes*为0或1NROWID二进制数据表中记录的唯一行号最大长度4000bytesNUMBER(P,S)数字类型P为整数位，S为小数位DECIMAL(P,S)数字类型P为整数位，S为小数位INTEGER整数类型小的整数FLOAT浮点数类型NUMBER(38)，双精度REAL实数类型NUMBER(63)，精度更高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条件=值1THENRETURN(翻译值1)ELSIF条件=值2THENRETURN(翻译值2)......ELSIF条件=值nTHENRETURN(翻译值n)ELSERETURN(缺省值)ENDIFPL/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_emp.comm:=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包来创建动态S