使用PLSQL语言.ppt

《使用PLSQL语言.ppt》由会员分享，可在线阅读，更多相关《使用PLSQL语言.ppt（39页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、使用PLSQL语言第四章第四章使用使用PLSQLPLSQL语言语言所有的所有的PL/SQLPL/SQL程序都以块作为基本单位程序都以块作为基本单位块中包含过程化语句和块中包含过程化语句和SQLSQL的的DMLDML语句。这些块可以按顺序出现，语句。这些块可以按顺序出现，也可以相互嵌套（一个块在另一个块的内部）也可以相互嵌套（一个块在另一个块的内部）块的分类块的分类匿名块匿名块匿名块是出现在应用程序中的没有名字且不存储到数据库中的块匿名块是出现在应用程序中的没有名字且不存储到数据库中的块匿名块出现在匿名块出现在SQLSQL语句可以出现的地方，它们可以调用其他程序，却不语句可以出现的地方，它们可以

2、调用其他程序，却不能被其他程序调用能被其他程序调用命名块命名块命名块是一种带有标签的匿名块，标签为块指定了一个名称命名块是一种带有标签的匿名块，标签为块指定了一个名称子程序子程序子程序是存储在数据库中的过程（子程序是存储在数据库中的过程（procedureprocedure）、函数（）、函数（functionfunction），生成之后可以），生成之后可以被多次执被多次执行行程序包程序包程序包是存储在数据库中的一组子程序、变量定义程序包是存储在数据库中的一组子程序、变量定义程序包中的子程序可以被其他程序包或者子程序调用程序包中的子程序可以被其他程序包或者子程序调用触发器触发器触发器是一种存储在

3、数据库中的命名块，生成之后可以被多次执行触发器是一种存储在数据库中的命名块，生成之后可以被多次执行在相应的触发器事件发生之前或之后就会被执行一次或多次在相应的触发器事件发生之前或之后就会被执行一次或多次PL/SQLPL/SQL块块2023/2/263.2.1 3.2.1 组成部分组成部分定义部分 定义部分用于定义常量、变量、游标、异常和复杂数据类型定义部分用于定义常量、变量、游标、异常和复杂数据类型 执行部分执行部分用于实现应用模块功能，该部分包含需要执行的执行部分用于实现应用模块功能，该部分包含需要执行的PL/SQLPL/SQL语句语句和和SQLSQL语句语句 异常处理部分异常处理部分用于处

4、理执行部分可能出现的运行错误异常处理部分用于处理执行部分可能出现的运行错误PL/SQL块的基本结构块的基本结构程序结构程序结构DECLARE 定义部分定义部分BEGIN 执行部分执行部分EXCEPTION 异常处理部分异常处理部分END;DECLAREE、BEGIN和和EXCEPTION后面没后面没有有“；”（分号），而（分号），而END后则必须要带后则必须要带“；”（分号）。（分号）。2023/2/27匿名块示例：匿名块示例：创建一个匿名程序块，该程序块用于接收用户输入的员工编号，查创建一个匿名程序块，该程序块用于接收用户输入的员工编号，查询并输出该员工的姓名，还用于处理用户输入的员工编号不

5、存在的询并输出该员工的姓名，还用于处理用户输入的员工编号不存在的异常异常 程序结构程序结构set serveroutput onset verify offDECLARE v_ename varchar2(50);BEGINselect ename into v_ename from emp where empno=&eno;dbms_output.put_line(您要查找的姓名是：您要查找的姓名是：|v_ename);EXCEPTIONWHEN NO_DATA_FOUND THENdbms_output.put_line(输入的员工编号不存在！输入的员工编号不存在！);END;1.set

6、serveroutput on设置输出、显示环境变量设置输出、显示环境变量2.set verify off:设置是否进行数据校验。设置是否进行数据校验。2.定义块变量定义块变量3.执行业务逻辑执行业务逻辑4.异常处理部分异常处理部分2023/2/28PL/SQLPL/SQL注释注释单行注释单行注释单行注释可以在一行的任何地方由两个短横线（单行注释可以在一行的任何地方由两个短横线（-）开始，并且）开始，并且直到该行的最后直到该行的最后多行注释多行注释多行注释由多行注释由“/*”/*”开始、开始、“*“*/”/”结束，可以跨越多行，但不允许结束，可以跨越多行，但不允许嵌套嵌套2023/2/29常量

7、与变量常量与变量在声明部分中可以声明需要使用的常量、变量、函数、在声明部分中可以声明需要使用的常量、变量、函数、游标、异常处理名称游标、异常处理名称 在PL/SQL中，每一行中能声明一个常量或变量。在引用一个常量或变量之前，必须先声明该常量或变量declarev_pi constant number(6,5):=3.14;-定义圆周率常量定义圆周率常量v_r number(1):=2;v_area number(6,2);-定义保存圆面积的变量定义保存圆面积的变量begin v_area:=v_pi*v_r*v_r;dbms_output.put_line(半径：半径：|v_r);-输出半径值

8、输出半径值 dbms_output.put_line(面积：面积：|v_area);-输出圆的面积输出圆的面积end;常量声明专用名词常量声明专用名词:=赋值符号赋值符号2023/2/210常量与变量常量与变量除了可以使用常量来给变量赋值之外，还可通过除了可以使用常量来给变量赋值之外，还可通过SELECT SELECT INTOINTO语句将从数据库表中查询的结果赋予变量语句将从数据库表中查询的结果赋予变量 set serveroutput onset verify offDECLARE v_ename varchar2(50);BEGINselect ename into v_ename f

9、rom emp where empno=&eno;dbms_output.put_line(您要查找的姓名是：您要查找的姓名是：|v_ename);EXCEPTIONWHEN NO_DATA_FOUND THENdbms_output.put_line(输入的员工编号不存在！输入的员工编号不存在！);END;2023/2/211数据类型数据类型2023/2/212%TYPE数据类型当定义当定义PL/SQLPL/SQL变量存放值时，必须确保变量使用合适的数据类型变量存放值时，必须确保变量使用合适的数据类型和长度，否则可能会在运行过程中出现和长度，否则可能会在运行过程中出现PL/SQLPL/SQL

10、运行错误运行错误 为了避免这种不必要的错误，可以使用为了避免这种不必要的错误，可以使用%TYPE%TYPE属性来定义变量属性来定义变量 当使用当使用%TYPE%TYPE属性定义变量时，属性定义变量时，OracleOracle会自动地按照会自动地按照数据库表列数据库表列或或其他变量其他变量来确定新变量的类型和长度来确定新变量的类型和长度%ROWTYPE数据类型如果一张表中包含较多的列，则可以使用如果一张表中包含较多的列，则可以使用%ROWTYPE%ROWTYPE来定义一个来定义一个表示表中一行记录的变量表示表中一行记录的变量 示例：根据输入的员工编号查找该员工信息，及其所在部门的信息。根据输入的

11、员工编号查找该员工信息，及其所在部门的信息。数据类型数据类型declarev_ename emp.ename%type;v_deptno dept.deptno%type;v_dept_row dept%rowtype;begin select ename,deptno into v_ename,v_deptno from emp where empno=&eno;select*into v_dept_row from dept where deptno=v_deptno;dbms_output.put_line(您要查找的员工是：您要查找的员工是：|v_ename|，所在部门信息为：，所在部

12、门信息为：);dbms_output.put_line(部门编号：部门编号：|v_deptno|部门名称：部门名称：|v_dept_row.dname|所在地：所在地：|v_dept_row.loc);end;declarev_ename emp.ename%type;-定义和定义和emp表的表的ename相同的类型相同的类型declarev_dept_row dept%rowtype;-定义和定义和dept相同的行类型相同的行类型2023/2/213%record数据类型类似高级语言中的结构类似高级语言中的结构首先需要定义记录类型和记录变量首先需要定义记录类型和记录变量当引用记录成员时，必须

13、将记录变量作为前缀当引用记录成员时，必须将记录变量作为前缀示例：根据输入的员工编号输出该员工的姓名、基本工资、奖金及实发根据输入的员工编号输出该员工的姓名、基本工资、奖金及实发工资。工资。数据类型数据类型declare type emp_record_type is RECORD(ename emp.ename%type,sal emp.sal%type,comm m%type,total_sal sal%type);v_emp_record emp_record_type;begin select ename,sal,nvl(comm,0),sal+nvl(comm,0)into v_emp

14、_record from emp where empno=7369;dbms_output.put_line(员工姓名：员工姓名：|v_emp_record.ename);dbms_output.put_line(基本工资：基本工资：|v_emp_record.sal);dbms_output.put_line(奖金：奖金：|v_emp_m);dbms_output.put_line(实发工资：实发工资：|v_emp_record.total_sal);end;declare type emp_record_type is RECORD(-定义一个记录类型，包含员工信息定义一个记录类型，包含员

15、工信息 ename emp.ename%type,sal emp.sal%type,comm m%type,total_sal sal%type);v_emp_record emp_record_type;-声明记录类型变量声明记录类型变量2023/2/214数据类型数据类型一维数组的方式TABLETABLE数据类型数据类型TABLE(TABLE(索引表索引表)相当于一个键值集合，键是唯一的，用于查找对应相当于一个键值集合，键是唯一的，用于查找对应的值。键可以是整数或字符串的值。键可以是整数或字符串示例：示例：将部门编号是将部门编号是1010和和2020的部门信息存储到的部门信息存储到tabl

16、etable类型中，然后输出其信类型中，然后输出其信息。息。declaretype dept_table_type is table of dept%rowtypeindex by binary_integer;v_dept_table dept_table_type;begin select*into v_dept_table(0)from dept where deptno=10;select*into v_dept_table(1)from dept where deptno=20;dbms_output.PUT_LINE(编号：编号：|v_dept_table(0).deptno|名称

17、：名称：|v_dept_table(0).dname|所在地：所在地：|v_dept_table(0).loc);dbms_output.PUT_LINE(编号：编号：|v_dept_table(1).deptno|名称：名称：|v_dept_table(1).dname|所在地：所在地：|v_dept_table(1).loc);end;declaretype dept_table_type is table of dept%rowtypeindex by binary_integer;v_dept_table dept_table_type;2023/2/215流程控制流程控制3.1 3.

18、1 条件控制条件控制IF 条件表达式条件表达式 THEN 语句段语句段END IF;IF 条件表达式条件表达式 THEN语句段语句段1ELSE语句段语句段2END IF;IF 条件表达式条件表达式1 THEN语句段语句段1ELSIF 条件表达式条件表达式2 THEN语句段语句段2ELSIF 条件表达式条件表达式3 THEN语句段语句段3.ELSIF 条件表达式条件表达式n语句段语句段nEND IF;注意：是注意：是elsif 不是不是elseif，也不是也不是else if2023/2/216员工奖金发放：员工奖金发放：输入员工编号，如果该员工输入员工编号，如果该员工原来没有奖金，则按照工资的

19、原来没有奖金，则按照工资的10%10%发放发放 原来有奖金但不超过原来有奖金但不超过10001000的，补到的，补到10001000；其余的按照原来奖金基础再加上其余的按照原来奖金基础再加上10%10%发放；发放；流程控制流程控制declarev_emp emp%rowtype;begin v_emp.empno:=&no;select*into v_emp from emp where empno=v_emp.empno;dbms_output.PUT_LINE(更新前的奖金更新前的奖金|nvl(v_m,0);if v_m is null then update emp set comm=v

20、_emp.sal*0.1 where empno=v_emp.empno;elsIf v_m1000 then update emp set comm=1000 where empno=v_emp.empno;else update emp set comm=comm+comm*0.1 where empno=v_emp.empno;end if;end;2023/2/217条件控制条件控制条件控制条件控制casecase根据部门编号输出部门所在地根据部门编号输出部门所在地10 10 纽约纽约20 20 达拉斯达拉斯30 30 芝加哥芝加哥40 40 波士顿波士顿CASE 表达式表达式WHEN

21、 条件表达式结果条件表达式结果1 THEN语句段语句段1;WHEN 条件表达式结果条件表达式结果2 THEN语句段语句段2;.ELSE语句段语句段n;END CASE;declare v_deptno dept.deptno%type:=&deptno;begin case v_deptno when 10 then dbms_output.put_line(部门所在地：纽约部门所在地：纽约);when 20 then dbms_output.put_line(部门所在地：达拉斯部门所在地：达拉斯);when 30 then dbms_output.put_line(部门所在地：芝加哥部门所在

22、地：芝加哥);when 40 then dbms_output.put_line(部门所在地：波士顿部门所在地：波士顿);else dbms_output.put_line(不存在该部门不存在该部门);end case;end;2023/2/218条件控制条件控制条件控制条件控制casecase根据员工编号输出员工工资级别根据员工编号输出员工工资级别sal2000 sal=2000 and sal=2000 and sal3000 B B级工资级工资其余其余 C C级工资级工资CASEWHEN 条件表达式条件表达式1 THEN语句段语句段1;WHEN 条件表达式条件表达式2 THEN语句段语句

23、段2;.ELSE语句段语句段n;END CASE;declare v_sal emp.SAL%type;begin select sal into v_sal from emp where empno=&empno;case when v_sal=2000 and v_sal2 then exit;end if;insert into dept values(v_dept_table(i).deptno,v_dept_table(i).dname,v_dept_table(i).loc);i:=i+1;end loop;end;定义表结构定义表结构声明循环控制变量声明循环控制变量2023/2/

24、220条件控制条件控制whilewhile循环循环定义一个定义一个deptdept类型的表结构类型的表结构手工添加手工添加3 3条数据条数据然后用循环将其数据添加到然后用循环将其数据添加到deptdept表中。表中。WHILE 条件表达式条件表达式 LOOP语句段语句段;END LOOP;declare type dept_table_type is table of dept%rowtype index by binary_integer;i number(1):=0;v_dept_table dept_table_type;begin v_dept_table(0).deptno:=50;

25、v_dept_table(0).dname:=研发部研发部;v_dept_table(0).loc:=北京北京;v_dept_table(1).deptno:=60;v_dept_table(1).dname:=开发部开发部;.while i set autocommit on;SQL show autocommit;2023/2/231 事务事务4.5.34.5.3 事务控制语句事务控制语句CommitCommit 语句用于提交事务语句用于提交事务RollbackRollback 语句可以将事务回滚到事务的起点或某个保存点开始语句可以将事务回滚到事务的起点或某个保存点开始Savepoint

26、Savepoint RollbackRollback to to 2023/2/232 事务事务查看事务查看事务以以sys身份登录并给身份登录并给scott.dept表录入数据表录入数据当前事务是匿名事务，所以当前事务是匿名事务，所以name字段值为空字段值为空2023/2/233 事务事务查看事务查看事务命名事务命名事务beginbeginset transaction name deptTran;set transaction name deptTran;insert into scott.dept values(35,insert into scott.dept values(35,工程

27、部工程部,天津天津););end;end;select name,status,addr,start_time from v$transaction;select name,status,addr,start_time from v$transaction;在此之前一定要将以前在此之前一定要将以前的事务提交或回滚的事务提交或回滚2023/2/234 事务事务事务死锁事务死锁大型数据库应用系统存在并发机制，也就是说可能同时有多个大型数据库应用系统存在并发机制，也就是说可能同时有多个会话访问同一个数据，这时可能不可避免出现死锁。会话访问同一个数据，这时可能不可避免出现死锁。例如有两个事务例如有两个

28、事务A A和和B,B,如果事务如果事务A A已经锁定了数据库对象已经锁定了数据库对象A(A(数数据库对象据库对象A A可能是一张表或表中的几条记录可能是一张表或表中的几条记录)。如果事务。如果事务B B也已也已经锁定了数据库对象经锁定了数据库对象B(B(数据库对象数据库对象B B可能是一张表或表中的几可能是一张表或表中的几条记录条记录)。此时事务。此时事务A A再去申请锁定数据库对象再去申请锁定数据库对象B B，而同时时事，而同时时事务务B B再去申请锁定数据库对象再去申请锁定数据库对象A A。两者互不相让，就产生了死。两者互不相让，就产生了死锁锁2023/2/235 事务事务事务死锁案例事务

29、死锁案例创建一个示例表并录入两条数据创建一个示例表并录入两条数据打开两个打开两个sqlplussqlplus窗口窗口1 1、在第一个窗口执行修改第一条的语句、在第一个窗口执行修改第一条的语句2 2、在第二个窗口执行修改第二条的语句、在第二个窗口执行修改第二条的语句3 3、在第一个窗口再执行修改第二条的语句、在第一个窗口再执行修改第二条的语句4 4、在第二个窗口再执行修改第一条的语句、在第二个窗口再执行修改第一条的语句2023/2/236总结总结PL/SQLPL/SQL基本语法基本语法常量、变量命名常量、变量命名结构、表类型结构、表类型条件判断、循环条件判断、循环PL/SQLPL/SQL异常处理异常处理预定义异常、自定义异常预定义异常、自定义异常OracleOracle事务事务事务的事务的ACIDACIDcommitcommit、savepointsavepoint、rollback torollback to事务的死锁事务的死锁2023/2/2372023/2/238结束结束