《某电力公司oracle培训教材.ppt》由会员分享，可在线阅读，更多相关《某电力公司oracle培训教材.ppt（46页珍藏版）》请在金锄头文库上搜索。
1、ORACLE入门 PL/SQL语言篇,技术支持部 汤庆锋 福州磬基电子有限公司,本课程学习内容,PL/SQL简介 PL/SQL数据类型(ORACLE的数据类型) ORACL内置的SQL函数 PL/SQL中使用SQL PL/SQL中游标的使用 动态PL/SQL PL/SQL的异常处理,PL/SQL简介,PL/SQL(Procedural Language/SQL)即模块化的程序设计语言，用于从各 种环境中访问ORACLE数据库。它具备了许多SQL中所没有的过程化属性方面 的特点。主要包括： 变量和类型 控制结构（条件语句、循环语句) 过程、函数 游标 异常处理,PL/SQL程序的用途,无名块 就
2、是没有命名的PL/SQL块，它可以嵌入某一个应用之中. 存储过程、函数 也就是命名了的PL/SQL块，它可以接收参数，并且可以重复的被调用。 触发器 是与数据库中的表相关的PL/SQL块，可以自动的触发。 包 命名了的PL/SQL块，由一组相关的过程、函数和标识符组成。,PL/SQL的程序结构,PL/SQL的基本单位是“块”(Block)。所有的PL/SQL程序都是由一个或多 个PL/SQL块构成的，这些块可以相互进行嵌套。通常一个块完成程序的一个 单元的工作。一个基本的块由三个部分组成： 定义部分 定义变量、常量、游标、异常处理 可执行部分 包括对数据库进行操作的SQL语句，以及 对块中的语
3、句进行组织、控制的PL/SQL语句。 异常处理（Exception) 部分 可执行部分中的语句，在执行过程中 出错或出现非正常现象时，所做的响应 处理,PL/SQL数据类型,PL/SQL数据类型,常用的数据类型 CHAR： 存放固定长度的字符串 VARCHAR2：存放可变长度的字符串 NUMBER： 存放0、正负数、浮点数 DATE： 存放时间数据（包括日期和时间） LONG： 存放变长字符串。一般用来存储大文本 RAW LONG 存放多媒体数据,如声音、图片 例如：创建一雇员表 CREATE TABLE emp ( empno number(4), ename varchar2(10), h
4、iredate date, sal number(7,2), deptno number(2) );,ORACLE内置的SQL函数,SQL函数按照传入参数的类型，可分为字符串函数、数值函数、日期函数、 其他函数。以下分别列举较常用的部分进行说明。 字符串函数： UPPER(s) 将字符串s转换成大写的形式返回。 LOWER(s) 将字符串s转换成小写的形式返回。 SUBSTR(s,a ,b) 返回从字符位置a开始有b个字符长的s的一部分。 若a为正数:从左边向右边计算 若a为负数:从右边向左边计算 实例： Select substr(abcdefg123,4) from dual; 结果返回：
5、defg123 Select substr(abcdefg123,4,2) from dual; 结果返回：de Select substr(abcdefg123,-4,2) from dual; 结果返回：g1 RTRIM(s1,s2) 返回删除从最右边算起出现在s2中的字符的s1。s2缺省为空格 实例：Select rtrim(aabbccdd,cd) from dual; 结果返回：aabb Select rtrim(aabbccdd,dc) from dual; 结果返回：aabb,ORACL内置的SQL函数,Concat(s1,s2) 返回串接上s2之后的s1.该函数与|运算符作用相
6、同。 实例：select concat(abc,def) from dual; 返回结果：abcdef select abc|def from dual; 返回结果：abcdef Length(s) 以字节为单位返回字符串s的长度。,ORACL内置的SQL函数,数值函数 Ceil(n) 返回大于或等于n的整数 Select ceil(18.6),ceil(-18.6) from dual; Floor(n) 返回小于或等于n的整数 Select floor(18.6),floor(-18.6) from dual; Mod(x,y) 返回x除以y得余数，若y为0，则返回x。 Select mo
7、d(23,5),mod(4,1.3) from dual; 返回结果：1.1 , 0.1 Round(x,y) 返回舍入到小数点右边y为的x值。 Select round(1.56),round(1.56,1),round(123.4,-1) from dual; 返回结果：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) 返回结尾由forma
8、t指定的单位的日期。 示例： Select trunc(sysdate,year) from dual; 返回今年的第一天 Select trunc(sysdate,mm) from dual; 返回本月的第一天 Select trunc(sysdate,D) from dual; 返回本周的第一天,ORACL内置的SQL函数,转换函数 To_char(D，format) 将日期转换为指定格式的字符串。 示例： Select to_char(sysdate,yyyy/mm/dd hh:mi:ss) from dual; To_Date(string,format) 将字符串转换成日期格式 示例
9、： Select to_date(2000/10/01,yyyy/mm/dd) from dual; Last_day(D) 返回日期D的月份的最后一天的日期 To_Number(string,format),ORACL内置的SQL函数,其它函数 Nvl(a,b) 空值替换函数，若a为空，则替换成b。 示例： Select ename,sal,sal+nvl(comm,0) from dual; DECODE(条件,值1,翻译值1,值2,翻译值2,.值n,翻译值n,缺省值) 该函数的含义如下： IF 条件=值1 THEN RETURN(翻译值1) ELSIF 条件=值2 THEN RETURN
10、(翻译值2) ELSIF 条件=值n THEN RETURN(翻译值n) ELSE RETURN(缺省值) END IF,PL/SQL的注释,注释增强了可阅读性，使得程序更易于理解。 单行注释 - comment 多行注释 /* comment */ 注意：此注释不能作用在SQL语言上。 示例： DECLARE v_deptno number(2); -与雇员表中部门代码字段交互的变量 v_sal number(7,2); -与雇员表中工资字段交互的变量 BEGIN /*this is a test! */ select deptno,sal into v_deptno,v_sal from
11、emp where empno=7788; END;,PL/SQL块的定义部分,在PL/SQL块中引用的所有标识符，都必须在定义部分中明确定义。 定义常量 格式：标识符 CONSTANT数据类型：= 表达式 例：定义一常量PI，值为3.14。 PI CONSTANT NUMBER(3,2) := 3.14; 定义标量型变量 标量型数据类型，是指数据类型为个体型。 格式： NOT NULL :=|DEFAULT 例：定义一宽度为10个字符的字符串变量X。 DECLARE X CHAR(5)； y CHAR(5):=ORACLE; Z CHAR(5) default oracle;,代表数据库列的
12、变量,先看一个示例：创建一PL/SQL块，根据部门号，返回部门名称. DECLARE v_dname dept.dname%type; BEGIN SELECT dname INTO v_dname FROM DEPT WHERE deptno=10; DBMS_OUTPUT.PUT_LINE(v_dname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(sorry:no data found!); END; 问题： 所引用的数据库表中的数据类型不知道？ 所引用的数据库表中的数据类型将来改变改变怎么办？,PL/SQL块的定义部
13、分,另一种定义标量型变量的方法%TYPE 定义一个变量,其数据类型与已知变量的数据类型相同，或者与数据库 表的某个列的数据类型相同。 %TYPE的优点在于： 所引用的数据库表中的数据类型可以不必知道。 所引用的数据库表中的数据类型可以实时改变。 格式： NOT NULL :=|DEFAULT .%TYPE 例：定义一个变量，其数据类型基于另一个变量 DECLARE V_1 NUMBER(7,2); V_11 V1%TYPE := 12345.6; 例：定义一个变量，其数据类型基于数据库中表的列 DECLARE v_ename EMP.ENAME%TYPE; V_SAL EMP.SAL%TYPE
14、;,PL/SQL块的定义部分,另一种定义组合型变量的方法%ROWTYPE 定义一个变量,其数据类型与数据库表的数据结构相同。 %ROWTYPE的优点在于： 所引用的数据库表中的数据类型可以不必知道。 所引用的数据库表中的数据类型可以实时改变。 简易格式： %ROWTYPE 例： DECLARE v_emp emp%rowtype; BEGIN SELECT * INTO v_emp FROM emp WHERE empno=7788; DBMS_OUTPUT.PUT_LINE(v_emp.empno); DBMS_OUTPUT.PUT_LINE(v_emp.ename); DBMS_OUTPU
15、T.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 不可以直接使用的
16、SQL语句有： 数据定义语句（DDL），如：CREATE TALBE，DROP TABLE 数据控制语句（DCL），如：GRANT、REVOKE 备注：在PL/SQL2.1以上版本，允许通过DBMS_SQL包来创建动态SQL语句。,PL/SQL中使用SQLSELECT语句,SELECT语句：将数据从数据库中检索出来并放入PL/SQL变量中。 格式：SELECT INTO FROM 例：查询某个雇员的姓名及工资。 DECLARE v_empno emp.empno%type:=7788; v_ename emp.ename%type; v_sal emp.sal%type; BEGIN select ename,sal into v_ename,v_sal from emp where
