ORACLE下PLSQL编程基础.ppt

1、PL/SQL语言基础,PL/SQL是一种数据库程序设计语言，是Oracle数据库系统提供的扩展SQL语言。使用PL/SQL语言可以在各种环境下对Oracle数据库进行访问。,PL/SQL语言的结构,块（Block）是PL/SQL程序中最基本的结构。 PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成。 DECLARE -标记声明部分 -此处用来定义常量、变量、类型和游标等 BEGIN -标记程序体部分开始 -此处用来编写各种PL/SQL语句、函数和存储过程 EXCEPTION -标记异常处理部分开始 -此处用来编写异常处理代码 END; -标记程序体部分结束,PL/SQL示例程序,示

2、例程序的代码如下： SET ServerOutput ON; DECLARE /* 声明变量 */ var_UserName VARCHAR2(100); BEGIN SELECT UserName INTO var_UserName FROM Users WHERE UserId = 1; dbms_output.put_line(var_UserName); -输出变量 END;,说明： （1）SET ServerOutput ON; 用于将环境变量serveroutput设为打开状态，从而使PL/SQL程序能够在SQL*PLUS中输出结构。 （2）使用函数dbms_output.put_

3、line(var_UserName);可以输出参数的值。,PL/SQL组件,声明部分 执行部分 异常处理部分,声明部分,DECLARE对变量进行声明： DECLARE ; ; ;,常量名和变量名的定义规则： 标识符必须以字符开头。 标识符中可以包含数字（09）、下划线（_）、“$”和“#”。 标识符最大长度为30。 标识符不区分大小写，TypeName和typename是完全相同的。 不能使用PL/SQL保留字使用标识符名，例如不能声明变量名为DECLARE。,PL/SQL中常用数据类型： BLOB 二进制大对象，可以用来保存图像和文档等二进制数据。 BOOLEAN 布尔数据类型，支持TRUE

4、/FALSE值。 CHAR 固定长度字符串。 CLOB 字符大对象，可用来保存多达4GB的字符数据。 DATE 存储全部日期的固定长度字符串。 LONG 可变长度字符串。 NUMBER 可变长度数值。 integer 表示整数 RAW 二进制数据的可变长度字符串。 VARCHAR2 可变长度字符串。,（1）声明常量。声明常量的基本格式如下： constant := ; 关键字constant表示声明的是常量。要声明一个程序的版本信息常量conversion： conversion constant VARCHAR2(20) := 1.0.01;,【例】定义变量conVersion，保存指定产品

5、的版本信息。然后调用dbms_output.put_line输出常量的值： SET ServerOutput ON; DECLARE conVersion constant VARCHAR2(20) := 1.0.01; BEGIN dbms_output.put_line(conVersion); END; 程序的运行结果为： 1.0.01,（2）声明变量。声明变量的基本格式如下： (宽度) := ; 【例】声明一个变量Database保存数据库信息： SET ServerOutput ON; DECLARE Database VARCHAR2(50) := Oracle 10g ; BEG

6、IN dbms_output.put_line(Database); END; 程序的运行结果为： Oracle 10g,执行部分,1赋值语句 【例】在程序的运行过程中，对变量进行赋值操作： SET ServerOutput ON; DECLARE Database VARCHAR2(50); BEGIN Database := Oracle 10g ; dbms_output.put_line(Database); END;,执行部分,2条件语句IF IF THEN ELSIF THEN ELSE END IF;,执行部分,【例】演示IF语句的使用方法： SET ServerOutput O

7、N; DECLARE Num INTEGER := -11; BEGIN IF Num 0 THEN dbms_output.put_line(正数); ELSE dbms_output.put_line(0); END IF; END;,执行部分,3分支语句CASE CASE WHEN THEN 值1 WHEN THEN 值2 WHEN THEN 值n ELSE 值n + 1 END;,执行部分,【例】使用CASE语句根据给定的整数输出对应的星期值： SET ServerOutput ON; DECLARE varDAY INTEGER := 3; Result VARCHAR2(20);

8、BEGIN Result := CASE varDAY WHEN 1 THEN 星期一 WHEN 2 THEN 星期二 WHEN 3 THEN 星期三 WHEN 4 THEN 星期四 WHEN 5 THEN 星期五 WHEN 6 THEN 星期六 WHEN 7 THEN 星期七 ELSE 数据越界 END; dbms_output.put_line(Result); END;,执行部分,4循环语句LOOPEXITEND LOOP IF THEN EXIT END IF END LOOP;,执行部分,【例】LOOPEXITEND语句的示例程序： SET ServerOutput ON; DECL

9、ARE v_Num INTEGER := 1; v_Sum INTEGER := 0; BEGIN LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); IF v_Num = 3 THEN EXIT; END IF; dbms_output.put_line( + ); v_Num := v_Num + 1; END LOOP; dbms_output.put_line( = ); dbms_output.put_line(v_Sum); END;,执行部分,5循环语句LOOPEXIT WHENEND LOOP EXIT WHEN

10、END LOOP;,执行部分,【例】用LOOPEXIT WHENEND语句来实现： SET ServerOutput ON; DECLARE v_Num INTEGER := 1; v_Sum INTEGER := 0; BEGIN LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); EXIT WHEN v_Num = 3; dbms_output.put_line( + ); v_Num := v_Num + 1; END LOOP; dbms_output.put_line( = ); dbms_output.put_line

11、(v_Sum); END;,执行部分,6循环语句WHILELOOPEND LOOP WHILE LOOP END LOOP;,执行部分,【例】用WHILELOOPEND LOOP语句来实现： SET ServerOutput ON; DECLARE v_Num INTEGER := 1; v_Sum INTEGER := 0; BEGIN WHILE v_Num = 3 LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); IF v_Num 3 THEN dbms_output.put_line( + ); END IF; v_Nu

12、m := v_Num + 1; END LOOP; dbms_output.put_line( = ); dbms_output.put_line(v_Sum); END;,执行部分,7循环语句FORINLOOPEND LOOP FOR IN . LOOP END LOOP;,执行部分,【例】用FORINLOOPEND LOOP语句来实现，代码如下： SET ServerOutput ON; DECLARE v_Num INTEGER; v_Sum INTEGER := 0; BEGIN FOR v_Num IN 1.3 LOOP v_Sum := v_Sum + v_Num; dbms_ou

13、tput.put_line(v_Num); IF v_Num 3 THEN dbms_output.put_line( + ); END IF; END LOOP; dbms_output.put_line( = ); dbms_output.put_line(v_Sum); END;,常用函数,数值型函数 字符型函数 日期型函数 统计函数,数值型函数,ABS函数返回给定数字表达式的绝对值。 【例】如果要计算4的绝对值： SET ServerOutput ON; BEGIN dbms_output.put_line(ABS(-4); END;,数值型函数,CEIL函数返回大于或等于所给数字表达

14、式的最小整数。 【例】分别对正数、负数和0计算CEIL： SET ServerOutput ON; BEGIN dbms_output.put_line(CEIL(116.24); dbms_output.put_line(CEIL(-112.75); dbms_output.put_line(CEIL(0); END;,数值型函数,FLOOR函数返回小于或等于所给数字表达式的最大整数。 【例】分别对正数、负数和0计算FLOOR： SET ServerOutput ON; BEGIN dbms_output.put_line(FLOOR(116.24); dbms_output.put_lin

15、e(FLOOR(-112.75); dbms_output.put_line(FLOOR(0); END;,数值型函数,POWER函数返回给定表达式乘指定次方的值。 【例】执行以下命令，计算15的4次方： SET ServerOutput ON; BEGIN dbms_output.put_line(POWER(15, 4); END;,数值型函数,ROUND函数返回数字表达式并四舍五入为指定的长度或精度。 【例】请执行以下命令，注意观察长度变化对结果的影响： SET ServerOutput ON; BEGIN dbms_output.put_line(ROUND(123.456, 2);

16、dbms_output.put_line(ROUND(123.456, 1); dbms_output.put_line(ROUND(123.456, 0); dbms_output.put_line(ROUND(123.456, -1); dbms_output.put_line(ROUND(123.456, -2); dbms_output.put_line(ROUND(123.456, -3); END;,字符型函数,ASCII函数返回字符表达式最左端字符的 ASCII 代码值。 【例】执行以下命令，输出字符A的ASCII码。 SET ServerOutput ON; BEGIN dbm

17、s_output.put_line(ASCII(ABC); END;,字符型函数,LENGTH函数返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格。 【例】返回教师名称的字符串长度： SELECT tname, LENGTH(tname) FROM teacher WHERE tid = 1;,字符型函数,UPPER函数返回将小写字符数据转换为大写的字符表达式。 【例】将字符串 abc 转换为大写字母： SET ServerOutput ON; BEGIN dbms_output.put_line(UPPER(abc); END;,concat( )函数 abc+def+xyz

18、 使用该函数如下： select concat(concat(abc,def),xyz) from dual;,select to_number(substr(abcde123,6,3)+5 from dual; 完成123+5的运算 to_number(substr(abcde123,length(abcde123)-3+1,3)+5 思考： 假设字符串尾部数字字符长度不相同？ abcde123 , abcd12,dhf5等，如何提取串尾部的数值？,考虑采用函数提取数值 select to_number(substr(abcde123,length(abcde123)-getNcTrail(

19、abcde123)+1,getNcTrail(abcde123)+5 from dual; 如何编写getNcTrail( )函数？,日期型函数,SYSDATE。返回当前日期和时间。 【例】输出当前的日期信息： SET ServerOutput ON; BEGIN dbms_output.put_line(SYSDATE); END; TO_CHAR。转换日期为字符串。 【例】将当前日期转换为字符串后再输出： SET ServerOutput ON; BEGIN dbms_output.put_line(TO_CHAR(SYSDATE); END;,日期型函数,LAST_DAY。返回包含日期d

20、的月份的最后一天的日期。这个函数可以被用来确定当前月中还剩下多少天。 【例】输出当前月份的最后一天： SET ServerOutput ON; BEGIN dbms_output.put_line(LAST_DAY(SYSDATE); END; MONTHS_BETWEEN。返回两个日期之间月的数目。 【例】计算2008-06-05到2008-10-05之间的月份数目： SET ServerOutput ON; DECLARE date1 VARCHAR2(20) := 2008-06-05; date2 VARCHAR2(20) := 2008-10-05; BEGIN dbms_outpu

21、t.put_line(MONTHS_BETWEEN(TO_DATE(date2,yyyy-mm-dd), TO_DATE(date1, yyyy-mm-dd); END;,统计函数,COUNT函数返回组中项目的数量。 【例】统计表teacher中教师的人数： SELECT COUNT(tname) FROM teacher; MAX。MAX函数返回表达式的最大值。 【例】统计表teacher中最大的教师编号： SELECT MAX(tid) FROM teacher; MIN。MIN函数返回表达式的最小值。 【例】统计表teacher中最小的教师编号： SELECT MIN(tid) FROM

22、 teacher;,函数,CREATE FUNCTION语句来创建函数： CREATE OR REPLACE FUNCTION ( ) RETURN IS | AS BEGIN RETURN END ;,例：查询2008年1月1日到2009年4月1日之前出身的学生信息 select * from student where birthdate between to_date(2008-1-1,YYYY-MM-DD) and to_date(2009-4-1,yyyy-mm-dd); 有个问题!,insert into student values(1011,李华,男,to_date(2009-

23、4-1 10:23:23,yyyy-mm-dd hh24:mi:ss),);,create or replace function todatemax(strDate nvarchar2) return date is Result date; str nvarchar2(100); begin str:=concat(strDate, 23:59:59); Result:=to_date(str,yyyy-mm-dd hh24:mi:ss); return(Result); end todatemax;,select * from student where birthdate betwee

24、n to_date(2008-1-1,YYYY-MM-DD) and todatemax(2009-4-1);,.null值结构 在if结构中只有相关条件为真时，相应语句才执行，如果条件为false或null时，语句都不执行。当条件为null时，对程序执行的流程有较大的影响。,declare v1 number; v2 number; v3 varchar2(7); begin if v1v2 then v3:=yes; else v3:=no; end if; End;,declare v1 number; v2 number; v3 varchar2(7); begin if v1v2 t

25、hen v3:=no; else v3:=yes; end if; End;,这两段程序是有区别的： 例如：v1为1，v2为null则，第一段程序(1null)返回null，if条件不成立，进入else，v3为no 第二段程序，同样也执行else，则v3为yes，所以结果可能不同。,解决办法： 添加对null的检测。,declare v1 number; v2 number; v3 varchar2(7); begin if v1 is null or v2 is null then v3=unknown; else if v1v2 then v3:=yes; else v3:=no; end

26、 if; End;,declare v1 number; v2 number; v3 varchar2(7); begin if v1 is null or v2 is null then v3=unknown; else if v1v2 then v3:=no; else v3:=yes; end if; End;,使用%TYPE和%ROWTYPE类型的变量,在定义变量时，除了可以使用Oracle规定的数据类型外，还可以使用%TYPE、%ROWTYPE类型变量。 %TYPE类型变量 存储从数据库列中检索到的值而创建的。对于使用%TYPE创建的变量，其数据类型由系统根据检索的数据库列的数据类型

27、决定。 %ROWTYPE类型的变量 一次存储从数据库检索的一行数据。,%TYPE变量,当使用%type定义变量时，其数据类型为指定的列的数据类型。,set serveroutput on; declare id student.sid%type; name student.sname%type; gender student.gender%type; birthday student.birthdate%type; begin select sid,sname,gender,birthday into id , name , gender , birthday from student whe

28、re sid=2; dbms_output.put_line(id|/|name|/|gender|/| birthday); end;,优点： （1）用户不必查看数据类型。 （2）当数据库结构改变时，用户不必更改变量类型。,%ROWTYPE变量,对数据库进行查询时，使用%ROWTYPE变量变量可以存储查询的一行数据（一个记录值）。,set serveroutput on; declare row student%rowtype; begin select * into row from student where sid=1; dbms_output.put_line(row.sid); d

29、bms_output.put_line(row.sname); dbms_output.put_line(row.gender); dbms_output.put_line(row.birthdate); end;,复合变量,用户自定义的由多个值组成的变量。复合变量可以将不同数据类型的多个值存储在一个单元中。 复合变量有两类：记录类型、记录表类型 .记录类型 定义格式： type record_name is record( field1_name data_type :=default_value, fieldn_name data_type :=default_value);,set se

30、rveroutput on; declare type stu is record( id number, name varchar(15), gender nchar(1), birthday date); record_var stu; begin select sid,sname,gender,birthdate into record_var from student where sid=1; dbms_output.put_line(record_var.id|record_); end;,记录类型相同的变量可以赋值，但类型不同的变量,尽管类型内的字段相同，也不能相互

31、赋值。,set serveroutput on declare type stu1 is record( id number, name varchar(15), gender nchar(1), birthday date); type stu2 is record( id number, name varchar(15), gender nchar(1), birthday date); record_var1 stu1; record_var2 stu1; record_var3 stu2;,begin select sid,sname,gender,birthdate intoreco

32、rd_var1 from student where sid=1; record_var2:=record_var1; record_var3:=record_var1; (错误) end;,.记录表类型 用于存储多行数据，相当于在内存中建立一个数据表。,一，什么是PL/SQL表？ 首先PL/SQL表和记录(Record)一样，都是复合数据类型。可以看做是一种用户自定义数据类型。 PL/SQL表由多列单行的标量构成的临时索引表对象。组成类似于一维数组。区别和联系记录(Record)的单行多列和物理存储数据的表。Record + PL/SQL表可以进行数据的多行多列存储。生命周期在程序块内。,.

33、声明PL/SQL表类型对象 语法： TYPE PL/SQL表名 IS TABLE OF 可用列类型 INDEX BY BINARY_INTEGER; 可用列类型可以为Oracle的数据类类型以及用户自定义类型;,属性方法: count -返回pl/sql表的总行数； delect -删除pl/sql表的所有内容； delect(行数) -删除pl/sql表的指定的行； delect(开始行，结束行) -删除pl/sql表的多行； first -返回表的第一个INDEX; next(行数) -这个行数的下一条的INDEX; last -返回表的最后一个INDEX;,.声明PL/SQL表类型变量：

34、 语法： PL/SQL表类型变量名 PL/SQL表类型；,.数据填充和访问 语法： PL/SQL表类型变量名(索引列值) := 填充声明类型值； PL/SQL表类型变量名.属性方法名；,Declare Type MyTabType is Table Of VarChar2(10) Index By Binary_Integer; MyTab MyTabType; vN Number(4); Begin MyTab(1) := A; MyTab(2) := B; MyTab(3) := C; vN := MyTab.First; DBMS_OUTPUT.PUT_LINE(First index:

35、| |vN | ); vN := MyTab.Last; DBMS_OUTPUT.PUT_LINE(last index:| |vN); End;,set serveroutput on declare type table_stu is table of student%rowtype index by binary_integer; stu table_stu; index_num integer; i integer:=0; begin for vare in (select * from student) Loop stu(i).sid := vare.sid; stu(i).snam

36、e:=vare.sname; stu(i).gender:=vare.gender; stu(i).birthdate:=vare.birthdate; stu(i).email:=vare.email; i:=i+1; end Loop;,index_num :=stu.First; For i in 1.stu.count Loop dbms_output.put_line(第|index_num|行数据：|stu(index_num).sid| /|stu(index_num).sname); index_num:=stu.next(index_num); end Loop; end;,

37、set serveroutput on,Declare Type MyTabType is Table Of VarChar2(10) Index By Binary_Integer; MyTab MyTabType; vN Number(4); Begin MyTab(1) := A; MyTab(2) := B; MyTab(3) := C; vN := MyTab.First; DBMS_OUTPUT.PUT_LINE(原表记录数为:| MyTab.count); MyTab.delete(2); DBMS_OUTPUT.PUT_LINE(删除记录后记录数为:| MyTab.count)

38、; MyTab.delete; DBMS_OUTPUT.PUT_LINE(剩余记录数为:| MyTab.count); End;,删除记录表中的记录数据,异常处理,oracle异常有3种类型： （1）预定义异常 oracle为用户提供了大量的在PL/SQL中使用的预定义异常，不需要用户定义，由oracle自动引发 （2）非预定义异常 其他标准的oracle错误，需要用户在程序中定义，由oracle自动引发 （3）自定义异常 用户自己设计的异常，需要用户显示地引发,WHEN语句来定义异常处理： EXCEPTION WHEN THEN WHEN THEN WHEN OTHERS THEN ,预定义异常,set serveroutput on DECLARE name VARCHAR(40); BEGIN SELECT sname INTO name FROM student WHERE sid = 7; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(没有数据); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(返回多行匹配的数据); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(错误情况不明); END;,非预