PL/SQL 基础培训

1. PL/SQL基础培训 北京神州数码思特奇信息技术股份有限公司 研究院

2. Oracle 数据库开发——PL/SQL • PL/SQL基础 • PL/SQL语言的简介 • 数据类型 • 程序结构 • 流程控制 • 异常处理 • 过程与函数 • 游标 • 触发器 • SQL优化初步

3. PL/SQL基础 • SQL语句、PL/SQL块和SQL*Plus命令之间的区别: 1）SQL语句是以数据库为操作对象的语言，主要包括数据定义语言DDL、数据操纵语言DML和数据控制语言DCL以及数据存储语言DSL。当输入SQL语句后，SQL*Plus将其保存在内部缓冲区中。 2）PL/SQL块同样是以数据库中的数据为操作对象。但由于SQL不具备过程控制功能，所以，为了能够与其他语言一样具备面向过程的处理功能，在SQL中加入了诸如循环、选择等面向过程的处理功能，由此形成了PL/SQL。所有PL/SQL语句的解释均由PL/SQL引擎来完成。使用PL/SQL块可编写过程、触发器和包等数据库永久对象。 3）SQL*Plus命令主要用来格式化查询结果、设置选择、编辑及存储 SQL命令、以设置查询结果的显示格式，并且可以设置环境选项。

4. PL/SQL基础 1、PL/SQL：PL/SQL是一种过程化编程语言，运行于服务器端的编程语言，PL/SQL和SQL作无缝连接，是和C、Java一样关注于实现的细节，因此可以实现复杂的业务逻辑处理。PL/SQL是对SQL的一种扩展，把SQL语言的灵活性和过程化结构融合在一起。 2、PL/SQL改善性能： • PL/SQL是以整个语句块的形式发送给服务器的，降低了网络负载，从而提高性能。因为SQL语句是以语句为单位进行发送的，在网络环境下会占用大量的服务器时间，同时导致网络拥挤。 如图： Oracle数据库服务器 Oracle数据库服务器 SQL …… SQL SQL SQL SQL 客户端应用程序 客户端应用程序 使用SQL

5. PL/SQL基础 PL/SQL结构 PL/SQL的基本结构是块，所有的PL/SQL程序都是由块组成的， PL/SQL的块结构如下： declare /*声明部分，包括变量、常量、游标、用户定义的异常的定义等*/ begin /*可执行的部分，包括SQL语句和过程化语句，是程序的主体*/ exception /*异常处理部分，包括异常处理语句*/ end /*块结束语句*/

6. 数字型 用来存储整数或实数。NUMBER、BINARY_INTEGER、PLS_INTEGER NUMBER-----存储整数和浮点数 BINARY_INTEGER-----存储带符号的整数值，溢出时不发生错误 PLS_INTEGER-----存储带符号的整数值，溢出时发生错误 例如： V_NUM NUMBER（5）； v_binarynum binary_integer; PL/SQL数据类型 PL/SQL基础

7. PL/SQL基础 • 字符型：用类存储字符串或字符数据。包括 VARCHAR2、CHAR、LONG、NCHAR、NVARCHAR2。 • VARCHAR2-----存储可变长度的字符串 • CHAR-----存储固定长度的字符串 • LONG-----存储可变长度的字符串，其最大长度是32760字节。 • NCHAR和NVARCHAR2-----NLS字符类型用于存储来自不同于PL/SQL语言的字符集中的字符集。 • 例如： V_CHAR VARCHAR2（20）；

8. PL/SQL基础 • 日期型：用来存储日期和时间信息，包括世纪、年、月、天、小时、分钟和秒。唯一的类型为DATE。 • 例如： V_DATE DATE； • 布尔型：布尔型的类型为BOOLEAN。布尔变量在PL/SQL控制结构中使用，BOOLEAN变量只能存储TRUE、FALSE、NULL值。 • 例如： V_BOOLEAN BOOLEAN;

9. PL/SQL基础 • date、timestamp类型 • 示例演示介绍

10. PL/SQL基础 • 原始型：用来存储二进制数据。包括RAW、LONG RAW • RAW-----存储定长的二进制数据。类似于 CHAR类型，但不在字符集之间进行转换。 • LONG RAW-----与LONG 类似，最大长度为32760字节，但不在字符集之间进行转换。 • 例如： V_LONG LONG;

11. PL/SQL基础 • 空值处理 • NULL+〈数字〉=NULL （空值加数字仍是空值) • NULL> 〈数字〉=NULL （空值与数字比较，结果仍是空值） • NULL||‘字符串’= ‘字符串’ （空值与字符串进行连接运算，结果为原字符串） • 判断一个变量的值是否为NULL的正确写法为： • if my_var is null then • .... • end if; • 错误写法为： • if my_var = null then • .... • end if;

12. PL/SQL基础 使用NULL值进行比较时，注意： 例： a:=5; b:=null; if a<>b then .... end if; 例： x:=null; y:=null; if x=y then .... end if;

13. PL/SQL基础 变量的声明：除了满足SQL基本命名规则，变量还要以v_开头，常量以 c_开头，声明变量或常量的语法如下： 标识符 [constant] datatype [not null][:=|default expr]; 例如： declare v_ch char(20); c_ch constant char(10) not null := 'World!'; begin v_ch := 'Hello'; dbms_output.put_line(v_ch); dbms_output.put_line(c_ch); exception when others then null; end; 注意：变量名和常量名不能与Oracle数据库中表名或字段名相同 ，变量如果没有初始值，默认是null。

14. PL/SQL基础 例如： declare c_pi constant number(3,2) default 3.14; v_area number(8,2); v_r number(2); begin v_r:=2; v_area:=c_pi*v_r*v_r; dbms_output.put_line('圆的面积是： '||v_area); end;

15. PL/SQL基础 例如： declare v_i number := 5; v_ch char(20) := 'How are you'; v_today char(15) := to_char(sysdate,'yyyy/mm/dd'); v_flag boolean:=true; begin dbms_output.put_line(v_i||' '||v_ch||' '||v_today); end;

16. PL/SQL基础 例如： declare v_avgprice titles.price%type; v_date char(20); begin v_date:=to_char(sysdate,'yyyy/mm/dd'); select avg(price) into v_avgprice from titles; dbms_output.put_line(v_avgprice); dbms_output.put_line(v_date); end;

17. PL/SQL基础 set serveroutput on--set serveroutput offdeclarev_empno emp.empno%Type; /*声明变量v_empno, %type: 使该变量的类型与emp表中的empno类型相同*/v_emprecord emp%Rowtype; /*声明变量v_emprecord, %rowtype: 使该变量的类型与emp表中的整行相同*/beginSelect * Into v_emprecord From emp Where empno=&v_empno;dbms_output.put_line('雇员编号 ‘ || v_emprecord.empno);dbms_output.put_line('雇员姓名 ‘ || v_emprecord.ename);dbms_output.put_line('入职日期 ‘ || v_emprecord.hiredate);dbms_output.put_line('职位 ‘ || v_emprecord.job);dbms_output.put_line('管理员编号 ‘ || v_emprecord.mgr);dbms_output.put_line('工资 ‘ || v_emprecord.sal);dbms_output.put_line('奖金 ‘ || v_emprecord.comm);dbms_output.put_line('部门编号 ‘ || v_emprecord.deptno);end;/

18. PL/SQL基础 PL/SQL 的记录类型　 把逻辑相关的数据作为一个单元存储起来,在declare 段中定义record类型数据，使某一变量使用该record型数据． 定义方法：TYPE r_record is RECORD ( v_name emp.ename%TYPE, v_job emp.job%TYPE, v_sal emp.sal%TYPE ); 变量定义r_emp r_record;变量使用　　　SELECT ename,job,sal INTO r_emp FROM empWHERE empno=7934;则，r_emp.v_ename,r_emp.v_job,r_emp.v_sal已有值； 给变量赋值： r_employee r_record;r_employee.v_ename :=‘JACK’;r_employee.v_job :=‘CLERK’; r_employee.v_sal := 890.98;

19. PL/SQL 的记录类型 PL/SQL的复合类型主要包括record记录类型。record复合数据类型在使 用前必须定义，类似C语言的结构体类型。也可以使用rowtype来定义。 例1： declare type recTypeStudent is record( sname varchar2(10), age number(2) ); v_recStu recTypeStudent; begin v_recStu.sname := 'zhang'; v_recStu.age := 20; dbms_output.put_line ( v_recStu.sname || ' ' || v_recStu.age ); end; 例2：定义一个记录类型,接收7369号员工的信息并打印。（独立实现） PL/SQL基础 declare type rec_emp is record ( v_empno emp.empno%type, v_ename emp.ename%type, v_sal emp.sal%type ); v_empinfo rec_emp; begin select empno,ename,sal into v_empinfo from emp where empno=7369; dbms_output.put_line(v_empinfo.v_empno||' '||v_empinfo.v_ename); end; declare v_emp emp%rowtype; begin select * into v_emp from emp where empno=7369; dbms_output.put_line(v_emp.empno); end;

20. PL/SQL基础 TABLE类型数据　 PL/SQL中的表(table)类型类似于Ｃ语言中的结构类型数组． 定义方法：TYPE table_emp IS TABLE OF emp . ename %TYPE INDEX BY BINARY_INTEGER; 一个PL/SQL表有两个列，(key, value) 1. key 列类型即是BINARY_INTEGER 2. value类型则是所定义的数据类型． table类型使用：　 定义变量 my_name为 table_emp 类型,则可以使用变量 my_name , 也可以在SQL语句中使用table类型变量．my_name table_emp; my_name(0) :=‘SCOTT’; my_name(1) :=‘SMITH’; my_name(2) :=‘SUSAN’; SELECT ename INTO my_name(10) FROM emp WHERE empno = 7934;

21. PL/SQL基础 用法演示

22. PL/SQL中数据类型之间的转换：使用to_char、to_date、to_number来进行显性PL/SQL中数据类型之间的转换：使用to_char、to_date、to_number来进行显性 的数据类型转换。 例如： declare v_i number; v_j number; begin v_i:=1; v_j:=2; dbms_output.put_line(concat('i+j=',to_char(v_i+v_j))); end; Oracle中的表达式和运算符： （1）Oracle的过程语句中使用的函数： 1）单行数值函数：mod、round、trunc、ceil、floor。 2）单行字符函数：chr、concat、initcap、length、lower、lpad、rpad、ltrim、 replace、rtrim、substr、trim、upper 3）日期函数：add_months、last_day、months_between、next_day、round、 sysdate、trunc 4）转换函数：to_char、to_number、to_date （2）在oracle中不能直接使用的函数：decode、组函数 PL/SQL基础

23. 函数用法： 1.instr函数 格式为INSTR(源字符串, 要查找的字符串, 从第几个字符开始, 要找到第几个匹配的序号)返回找到的位置，如果找不到则返回0. 例如：INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 在字符串中查找'OR'，从第三个字符位置开始查找"OR"，取第三个字后第2个匹配项的位置。 默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。 所以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "aaa" FROM DUAL的显示结果是 Instring——————14 PL/SQL基础

24. 函数用法： 2.substr函数 取得字符串中指定起始位置和长度的字符串   substr( string, start_position, [ length ] )  如:     substr(‘This is a test’, 6, 2)     would return ‘is’     substr(‘This is a test’, 6)     would return ‘is a test’     substr(‘TechOnTheNet’, -3, 3)     would return ‘Net’     substr(‘TechOnTheNet’, -6, 3)     would return ‘The’ select substr('Thisisatest', -4, 2) value from dual PL/SQL基础

25. 综合应用： 1. SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "Instring" FROM DUAL--INSTR(源字符串, 目标字符串, 起始位置, 匹配序号) SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL SELECT INSTR('32.8,63.5',',', 1, 1) "Instring" FROM DUAL SELECT SUBSTR('32.8,63.5',INSTR('32.8,63.5',',', 1, 1)+1) "INSTRING" FROM DUAL SELECT SUBSTR('32.8,63.5',1,INSTR('32.8,63.5',',', 1, 1)-1) "INSTRING" FROM DUAL PL/SQL基础

26. 综合应用： 2. DECLARE  -- LOCAL VARIABLES HERE  T   VARCHAR2(2000);  S   VARCHAR2(2000);  NUM INTEGER;  I   INTEGER;  POS INTEGER;BEGIN  -- TEST STATEMENTS HERE  T := '12.3,23.0;45.6,54.2;32.8,63.5;';  SELECT LENGTH(T) - LENGTH(REPLACE(T, ';', '')) INTO NUM FROM DUAL;  DBMS_OUTPUT.PUT_LINE('NUM:' || NUM);  POS := 0;  FOR I IN 1 .. NUM LOOP    DBMS_OUTPUT.PUT_LINE('I:' || I);    DBMS_OUTPUT.PUT_LINE('POS:' || POS);    DBMS_OUTPUT.PUT_LINE('==:' || INSTR(T, ';', 1, I));    DBMS_OUTPUT.PUT_LINE('INSTR:' || SUBSTR(T, POS + 1, INSTR(T, ';', 1, I) - 1));    POS := INSTR(T, ';', 1, I);  END LOOP;END; PL/SQL基础

27. 函数用法： 3.decode函数 格式 decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) Eg1: 假设我们想给职员加工资，其标准是：工资在8000元以下的将加 20％；工资在8000元以上的加15％ select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2) as salary from employee PL/SQL基础

28. 函数用法： 4. substr与substrb函数 substr是按照字来算的，而substrb()是按照字节来计算。 SQL> select substr('今天是个好日子',3,5) from dual;----------是个好日子 SQL> select substrb(‘今天是个好日子’,3,5) from dual;-----天是 length与lengthb 长度计算函数  select length('你好') from dual         ----output:2 select lengthb('你好') from dual       ----output :4 Instr与Instrb 字符串查找函数 instr(原字符串,查的字符串,起始位置, 第几个匹配) 返回字符串位置,找不到返回0 . select instr('日日花前长病酒','花前',1,1) from dual     ----output:3 select instrb('日日花前长病酒','花前',1,1) from dual     ----output:5 PL/SQL基础

29. Oracle中可以使用的操作符： 1）算术操作符：＋、－、*、/、**（乘方） 2）关系运算符：<、<=、>、>=、=、<>、!= 3）其他的比较运算符：is null、like、between…and、in 4）逻辑运算符：and、or、not 5）其他操作符||、:= PL/SQL中的控制结构： （1）条件语句： if 条件1 then 语句体； elsif 条件2 then 语句体； ………. else 语句体； endif; 例：用上面的结构，写一个分时问候的程序 PL/SQL基础 set serveroutput on declare v_time number(2); begin v_time := to_char(sysdate,'hh24'); if v_time >= 6 and v_time <= 8 then dbms_output.put_line('起床。'); elsif v_time > 8 and v_time < 17 then dbms_output.put_line('工作'); elsif v_time >= 18 and v_time <= 22 then dbms_output.put_line('下班'); else dbms_output.put_line('睡觉'); end if; end;

30. （2）条件语句的嵌套使用：在if或if…else中可以嵌套if或if…else（2）条件语句的嵌套使用：在if或if…else中可以嵌套if或if…else 例1：取出雇员ID为7369的薪水，如果<1200，则输出‘low’，如果<2000， 则输出'middle'，否则输出'high' 例2：判断当前年分是否是闰年。 declare v_year number; begin v_year : =to_char(sysdate,'yyyy'); if mod(v_year,4)=0 and mod(v_year,100)!=0 or mod(v_year,400)=0 then dbms_output.put_line('The year is '||v_year); dbms_output.put_line('The year is leap year'); else dbms_output.put_line('The year is not leap year'); end if; end; 例2：打印今年当前月份的天数。（独立实现） PL/SQL基础

31. declare v_year number; v_days number; v_month number; v_leapyear boolean; begin v_year:=to_char(sysdate,'yyyy'); v_month:=to_char(sysdate,'mm'); v_leapyear:=( mod(v_year,4)=0 and mod(v_year,100)!=0 or mod(v_year,400)=0); …….. dbms_output.put_line(v_days); end; if v_leapyear then if (v_month=1 or v_month=3 or v_month=5 or v_month=7 or v_month=8 or v_month=10 or v_month=12) then v_days:=31; elsif (v_month=4 or v_month=6 or v_month=8 or v_month=10) then v_days:=30; else v_days:=29; end if; else if (v_month=1 or v_month=3 or v_month=5 or v_month=7 or v_month=8 or v_month=10 or v_month=12) then v_days:=31; elsif (v_month=4 or v_month=6 or v_month=8 or v_month=10 )then v_days:=30; else v_days:=28; end if; end if;

32. PL/SQL基础 (3) PL/SQL： CASE 结构 CASE WHEN 条件表达式1 THEN 语句段1 WHEN 条件表达式2 THEN 语句段2 …… ELSE 语句段N END CASE;

33. declare v_grade char :='A';begin case v_gradewhen 'A' then DBMS_OUTPUT.put_line('Excellent');when 'B' then DBMS_OUTPUT.put_line('Very good');when 'C' then DBMS_OUTPUT.put_line('Good');else DBMS_OUTPUT.put_line('No such grade');endcase; end; PL/SQL基础 示例：CASE 结构

34. 循环结构： 1）简单循环：循环体至少循环一次，loop语法如下： loop 语句体； [exit;] end loop; 例1：打印1，2，3…20; 例2：求1＋3＋5＋…25; 注意：也可以使用exit when来结束循环。 例3：将上面的两个例子用exit when改写。 PL/SQL基础 declare v_x number; begin v_x:=1; loop dbms_output.put_line(v_x); v_x:=v_x+1; if v_x>20 then exit; end if; end loop; end; declare v_x number; v_sum number; begin v_sum:=0; v_x:=1; loop v_sum:=v_sum+v_x; v_x:=v_x+1; if v_x>=25 then exit; end if; end loop; dbms_output.put_line(v_sum); end;

35. 2）while 循环语法： while 条件 loop 语句体； end loop; PL/SQL基础 declare v_x number; v_i number; begin v_x:=1; v_i:=0; while v_x<=100 loop dbms_output.put(v_x||' '); v_x:=v_x+1; v_i:=v_i+1; if mod(v_i,10)=0 then dbms_output.put_line(''); end if; end loop; end;

36. 2) for 循环语法： 例1： declare v_i number:=1; begin for counter in 1..10 loop dbms_output.put_line(v_i); v_i:=v_i+1; end loop; end; PL/SQL基础

37. 2) for 循环语法： 例2： set serveroutput on declare v_x number; begin v_x:=0; for counter in 1..10 loop dbms_output.put_line(v_x); v_x:=v_x+1; end loop; dbms_output.put_line(v_x); end; PL/SQL基础

38. 练习： 例1：如想获得7369号员工的信息，并打印出来。 例2：打印bu1032图书的基本信息和销售信息，包括书号、书名、类型、价格、销售日期和销售量、及出版社名称和作者信息。（独立实现）。 PL/SQL基础 • declare • v_empno number(4); • v_ename varchar2(10); • v_job varchar2(9); • v_mgr number(4); • v_hiredate date; • v_sal number(7,2); • v_comm number(7,2); • v_deptno number(2); • begin • select * • into v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno • from emp where empno=7369; • dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_hiredate||' '||v_sal||' '||v_deptno); • exception • when others then • null; • end;

39. PL/SQL基础 • INSERT语句的使用Declare v_empno EMP . empno%TYPE :=1234; v_ename EMP . ename%TYPE :=‘SCOTT’; v_job VARCHAR2(15) :=‘MANAGER’; v_deptno EMP . deptno%TYPE :=20; v_sal NUMBER(7,2) :=890.50; Begin INSERT INTO emp(empno, ename, job, hiredate, sal, deptno) VALUES(v_empno, v_ename, v_job, SYSDATE, v_sal, v_deptno);dbms_output.put_line(sql%rowcount || "条记录被影响！"); END; / 注意:非空(NOT NULL) 必须有值

40. PL/SQL基础 • DELETE 语句的使用Declare v_empno EMP.empno%TYPE :=1234; Begin DELETE FROM emp WHERE empno=v_empno; End; • 事务处理语句的使用在PL/SQL中可以使用SQL的 COMMIT,ROLLBACK及SAVEPOINT语句．Declare v_empno EMP.empno%TYPE := 1234;Begin DELETE FROM emp WHERE empno = v_empno; COMMIT; End; /

41. PL/SQL基础 • 在PL/SQL中执行DDL语句 begin execute immediate 'create table t ( num varchar2(20) default ''hello'')'; end

42. 例外处理 (EXCEPTION) 许多编写得很好的程序都必须能够正确处理各种出错情况，并且尽 可能地从错误中进行恢复。异常处理方法是程序对运行时刻错误作 出反应并进行处理的方法。当引发一个异常情况时，控制便会转给 块的异常处理部分。 异常处理部分的语法如下： EXCEPTION WHEN 异常情况1 THEN 语句序列1； WHEN 异常情况2 THEN 语句序列2； ... WHEN OTHERS THEN 语句序列3; END; OTHERS 异常处理将对所有引发的异常情况执行 其代码。它应该是块中最后一个处理语句，确保 所有的错误都被检测到。但OTHERS 只是简单地 记录发生了错误，而没有记录发生的是哪一个错 误。我们可以在OTHERS 中用预定义函数SQLCODE 和SQLERRM来决定引发异常处理的是哪个错误。 异常情况包括系统预定义的异常情况、用户自定 义的异常情况。

43. 例外处理 (EXCEPTION) ⑴ 系统预定义的异常情况 ORACLE有一些预定义的异常情况和大多数通常的ORACLE错误是对 应的。这些异常情况所使用的标识符在包STANDARD90中进行定义。 在程序的异常处理部分直接对它们进行处理。

46. ⑵ 用户自定义的异常情况 用户自定义的异常情况是程序定义的一个错误。程序所定义的这个错误并不一定非是一个ORACLE错误，它可能是与数据相关的一个错误。 用户定义的异常情况的处理分三步：

47. ⑵ 用户自定义的异常情况 · 定义异常情况 用户定义的异常情况是在PL/SQL块的说明部分进行定义的，和变量相类似，异常情况有一个类型和作用域。 例如： my_exception exception; · 触发异常情况 当一个异常情况相关的错误出现时，就会引发该异常情况。用户定义的异常情况是通过显式使用RAISE 语句来引发的，而预定义的异常情况是当相关的ORACLE错误发生时被隐式触发的。 例如： RAISE MY_EXCEPTION； · 在程序的异常处理部分对定义的异常情况进行处理。 例如： WHEN MY_EXCEPTION THEN ...

48. ⑵ 用户自定义的异常情况 例1： DECLARE tin_rec tin % rowtype ; v_passwd user.passwd % type ; err_ps EXCEPTION ; BEGIN select * into tin_rec from tin ; select passwd into v_passwd from user where userid = tin_rec.uid ; if tin_rec.ps = v_passwd then insert into tout values(‘ login ok’ ); else raise err_ps ; end if ;

49. ⑵ 用户自定义的异常情况 exception when err_ps then insert into tout values(‘ password error’〕； when no_data_found then insert into tout values(‘ userid error’〕； end;

50. ⑵ 用户自定义的异常情况 例2： declare e_toomanystudent exception; v_currentstudent number(3); v_maxstudent number(3); v_errorcode number; v_errortext varchar2(200); begin select current_student,max_students into v_currentstudent,v_maxstudent from classes where department=’HIS’ and course=101; if v_currentstudent > v_maxstudent then raise e_toomanyexception; end if;