第4章-使用PLSQL语言PPT课件
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
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||' 名称：'
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_emp.sal*0.1 where empno=v_emp.empno; elsIf v_m<1000 then
||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;
2021/1/11
.
5
PL/SQL块
所有的PL/SQL程序都以块作为基本单位
块中包含过程化语句和SQL的DML语句。这些块可以按顺序出现 ，也可以相互嵌套（一个块在另一个块的内部）
块的分类
匿名块
匿名块是出现在应用程序中的没有名字且不存储到数据库中的块
匿名块出现在SQL语句可以出现的地方，它们可以调用其他程序，却不 能被其他程序调用
dbms_output.put_line('您要查找的姓名是：'|| v_ename); EXCEPTION
WHEN NO_DATA_FOUND THEN dbms_output.put_line('输入的员工编号不存在！'); END;
2021/1/11
.
11
数据类型
2021/1/11
.
12
数据类型
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;
2021/1/11
.
17
条件控制
条件控制——case
常量声明专用名词 : = 赋值符号
declare v_pi constant number(6,5):=3.14; --定义圆周率常量 v_r number(1):=2; v_area number(6,2); --定义保存圆面积的变量
begin 在PL/vS_QaLr中ea，:=每v_一p行i*v中_能r*声v_明r;一个常量或变量。在引用一 个常量db或m变s量_o之u前tp，ut必.p须u先t_声lin明e(该'半常径量：或变'||v量_r); --输出半径值 dbms_output.put_line('面积：'||v_area); --输出圆的面积
begin如se果lec一t 张en表am中e 包,de含pt较no多in的to列v_，en则am可e以,v使_d用ep%tnRoOWTYPE来定义一个 表示表中一行fro记m录e的mp变w量here empno=&eno;
示例se：lect * into v_dept_row from dept where deptno=v_deptno;
BEGIN 执行部分
EXCEPTION 异常处理部分
END;
2021/1/11
DECLAREE、BEGIN和EXCEPTION后面没 有“；”（分号），而END后则必须要带 “；”（分号）。
.
7
程序结构
• 匿名块示例：
创建一个匿名程序块，该程序块用于接收用户输入的员工编号，查
询并输出该员工的姓名，还用于处理用户输入的员工编号不存在的
select ename into v_ename from emp where empno=&eno;
dbms_output.put_line('您要查找的姓名是：'|| v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
4. 异常处理部分
dbms_output.put_line('输入的员工编号不存在！');
• %TYPE数据类型
当定义PL/SQL变量存放值时，必须确保变量使用合适的数据类型 和长度，否则可能会在运行过程中出现PL/SQL运行错误
为了避免这种不必要的错误，可以使用%TYPE属性来定义变量
d%ecRlavvvO当或___rededW其使neeappT他用mttn_Y变%eroPoTedw量EmYedv-数-Pdp来_pe定Eetd.c.e据确p属d义elnatep%a定性r类p和tmert_on新定der型woo%e变义%twptyytt量变py相pdepee的量;e同;p;类时t的%型，r行o和Ow类r长ta型ycp度lee会; 自动地按照数据库表列
根据部门编号输出部门所在地
10 纽约
20 达拉斯
C3A0SWE H芝表E加N达条式哥件表达式结果1 THEN
declare40语句波段士1顿;
v_deptWnoHEdNep条t.d件e表ptn达o式%结typ果e2:=T&HdEeNptno;
2021/1/11
.
15
流程控制
3.1 条件控制
I语E语IEIEFFFLNL句句条S语条条SDI段段E件F句件件IF12条表段表表; 件达语语达达表式句 句式式达1段段TTT式12HHHEE2ENNTNHEN EELNSDIFIF条; 件表达式3 THEN 语句段3
...... ELSIF 条件表达式n 语句段n
根ddbb'据mm部输ss门__入oo名uu的tt称ppdv--员uu_|e：定|tt'ec，..工'ppn义l||auu所va编r和tt_me__在d号lleeiie部nnmp查eeet门((m_p''找您部r表信op该要门w.息的e.查编nd员为ena找号n工：amam的：'me信)e;%员'||e息||v't相工y_，pd所是同ee及在：p的; t其地n'类||ov所：|_型| e'在||nva部_md门eep的t_r信ow息.l。oc);
示例：
将部门编号是10和20的部门信息存储到table类型中，然后输出其信 declare 息。
type dedpet_ctlaabrele_type is table of dept%rowtype index btypbeindareyp_ti_ntaegbeler;_type is ta一b维le数o组f的d方ep式t%rowtype v_depti_ntadbelxe bdyepbti_ntabryle__itnytpeeg;er; begin v_dept_table dept_table_type;
命名块
»
被多次执行
» »
» »
命名块是一种带有标签的匿名块，标签为块指定了一个名称
子程序
子程序是存储在数据库中的过程（procedure）、函数（function），生成之后可以
程序包
程序包是存储在数据库中的一组子程序、变量定义 程序包中的子程序可以被其他程序包或者子程序调用
触发器
触发器是一种存储在数据库中的命名块，生成之后可以被多次执行 在相应的触发器事件发生之前或之后就会被执行一次或多次
异常
1. set serveroutput on设置输出、显示环境变量 2. set verify off : 设置是否进行数据校验。
set serveroutput on set verify off
DECLARE
2. 定义块变量
v_ename varchar2(50); BEGIN
3. 执行业务逻辑
Oracle数据库高级
查询和管理
第四章 使用PLSQL语言
本章内容
PL/SQL基本语法 PL/SQL异常处理 Oracle事务
2021/1/11
.
3
本章目标
掌握PL/SQL基本语法 掌握PL/SQL异常处理 掌握Oracle事务的使用
2021/1/11
.
4
PL/SQL语言简介
PL/SQL是Oracle在标准SQL语言上的过程性扩展 允许嵌入SQL语句，定义变量和常量， 允许过程语言结构（条件分支语句和循环语句）， 允许使用异常来处理Oracle错误 可以用于创建存储过程、触发器和程序包等， 也可以用于处理业务 规则、数据库事件或给SQL命令 的执行添加程序逻辑
end;
2021/1/11
.
13
数据类型
• %record数据类型
类似高级语言中的结构 declatyr首pee先em需p要_r定ec义ord记_t录yp类e 型is 和RE记CO录R变D(量
e当na引m用e e记m录p.e成na员m时e%，ty必pe须, 将记录变量作为前缀
• 示例sal：emp.sal%type, comm m%type, t根ota据l_输sa入l s的al%员ty工pe编号输出该员工的姓名、基本工资、奖金及实发 ); 工资。
v_debmmps__reocuotrpdute.pmupt__rlienceo('r实d_发ty工pe资;-：-声'||明v_记em录p类_r型ec变ord量.total_sal);
end;
2021/1/11
.
14
数据类型
• TABLE数据类型
TABLE(索引表)相当于一个键值集合，键是唯一的，用于查找对 应的值。键可以是整数或字符串
END IF;
注意：是elsif 不是elseif， 也不是else if
2021/1/11
.
16
流程控制
• 员工奖金发放：
输入员工编号，如果该员工
declare 原来没有奖金，则按照工资的10%发放 v_emp原em来p有%r奖ow金ty但pe不; 超过1000的，补到1000；
begin 其余的按照原来奖金基础再加上10%发放；
declare v_emp_record emp_record_type; begitnype emp_record_type is RECORD(--定义一个记录类型，包含员工信息
esnealemcet eenmapm.een,saaml,ne%vl(tcyopme,m,0),sal+nvl(comm,0) into v_emp_record sfraol memepm.spawl%hteyrpeee, mpno=7369; cdobmmms_eomutpp.ucot.mpumt_%litnyep(e'员, 工姓名：'|| v_emp_record.ename); tdobtaml_ss_aolustaplu%t.tpyupte_line('基本工资：'|| v_emp_record.sal); );dbms_output.put_line('奖金：'|| v_emp_m);
2021/1/11
.
6
程序结构
3.2.1 组成部分
定义部分
定义部分用于定义常量、变量、游标、异常和复杂数据类型
执行部分
执行部分用于实现应用模块功能，该部分包含需要执行的PL/SQL语句 和SQL语句
异常处理部分
异常处理部分用于处理执行部分可能出现的运行错误
PL/SQL块的基本结构
DECLARE 定义部分
END;
2021/1/11
.
8
PL/SQL注释
单行注释
单行注释可以在一行的任何地方由两个短横线（--）开始，并 且直到该行的最后
多行注释
多行注释由“/*”开始、“*/”结束，可以跨越多行，但不允许嵌 套
2021/1/11
.
9
常量与变量
在声明部分中可以声明需要使用的常量、变量、函数、 游标、异常处理名称
end;
2021/1/11
.
10
常量与变量
除了可以使用常量来给变量赋值之外，还可通过SELECT INTO语句将从数据库表中查询的结果赋予变量
set serveroutput on set verify off
DECLARE v_ename varchar2(50); BEGIN
select ename into v_ename from emp where empno=&eno;
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_emp.sal*0.1 where empno=v_emp.empno; elsIf v_m<1000 then
||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;
2021/1/11
.
5
PL/SQL块
所有的PL/SQL程序都以块作为基本单位
块中包含过程化语句和SQL的DML语句。这些块可以按顺序出现 ，也可以相互嵌套（一个块在另一个块的内部）
块的分类
匿名块
匿名块是出现在应用程序中的没有名字且不存储到数据库中的块
匿名块出现在SQL语句可以出现的地方，它们可以调用其他程序，却不 能被其他程序调用
dbms_output.put_line('您要查找的姓名是：'|| v_ename); EXCEPTION
WHEN NO_DATA_FOUND THEN dbms_output.put_line('输入的员工编号不存在！'); END;
2021/1/11
.
11
数据类型
2021/1/11
.
12
数据类型
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;
2021/1/11
.
17
条件控制
条件控制——case
常量声明专用名词 : = 赋值符号
declare v_pi constant number(6,5):=3.14; --定义圆周率常量 v_r number(1):=2; v_area number(6,2); --定义保存圆面积的变量
begin 在PL/vS_QaLr中ea，:=每v_一p行i*v中_能r*声v_明r;一个常量或变量。在引用一 个常量db或m变s量_o之u前tp，ut必.p须u先t_声lin明e(该'半常径量：或变'||v量_r); --输出半径值 dbms_output.put_line('面积：'||v_area); --输出圆的面积
begin如se果lec一t 张en表am中e 包,de含pt较no多in的to列v_，en则am可e以,v使_d用ep%tnRoOWTYPE来定义一个 表示表中一行fro记m录e的mp变w量here empno=&eno;
示例se：lect * into v_dept_row from dept where deptno=v_deptno;
BEGIN 执行部分
EXCEPTION 异常处理部分
END;
2021/1/11
DECLAREE、BEGIN和EXCEPTION后面没 有“；”（分号），而END后则必须要带 “；”（分号）。
.
7
程序结构
• 匿名块示例：
创建一个匿名程序块，该程序块用于接收用户输入的员工编号，查
询并输出该员工的姓名，还用于处理用户输入的员工编号不存在的
select ename into v_ename from emp where empno=&eno;
dbms_output.put_line('您要查找的姓名是：'|| v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
4. 异常处理部分
dbms_output.put_line('输入的员工编号不存在！');
• %TYPE数据类型
当定义PL/SQL变量存放值时，必须确保变量使用合适的数据类型 和长度，否则可能会在运行过程中出现PL/SQL运行错误
为了避免这种不必要的错误，可以使用%TYPE属性来定义变量
d%ecRlavvvO当或___rededW其使neeappT他用mttn_Y变%eroPoTedw量EmYedv-数-Pdp来_pe定Eetd.c.e据确p属d义elnatep%a定性r类p和tmert_on新定der型woo%e变义%twptyytt量变py相pdepee的量;e同;p;类时t的%型，r行o和Ow类r长ta型ycp度lee会; 自动地按照数据库表列
根据部门编号输出部门所在地
10 纽约
20 达拉斯
C3A0SWE H芝表E加N达条式哥件表达式结果1 THEN
declare40语句波段士1顿;
v_deptWnoHEdNep条t.d件e表ptn达o式%结typ果e2:=T&HdEeNptno;
2021/1/11
.
15
流程控制
3.1 条件控制
I语E语IEIEFFFLNL句句条S语条条SDI段段E件F句件件IF12条表段表表; 件达语语达达表式句 句式式达1段段TTT式12HHHEE2ENNTNHEN EELNSDIFIF条; 件表达式3 THEN 语句段3
...... ELSIF 条件表达式n 语句段n
根ddbb'据mm部输ss门__入oo名uu的tt称ppdv--员uu_|e：定|tt'ec，..工'ppn义l||auu所va编r和tt_me__在d号lleeiie部nnmp查eeet门((m_p''找您部r表信op该要门w.息的e.查编nd员为ena找号n工：amam的：'me信)e;%员'||e息||v't相工y_，pd所是同ee及在：p的; t其地n'类||ov所：|_型| e'在||nva部_md门eep的t_r信ow息.l。oc);
示例：
将部门编号是10和20的部门信息存储到table类型中，然后输出其信 declare 息。
type dedpet_ctlaabrele_type is table of dept%rowtype index btypbeindareyp_ti_ntaegbeler;_type is ta一b维le数o组f的d方ep式t%rowtype v_depti_ntadbelxe bdyepbti_ntabryle__itnytpeeg;er; begin v_dept_table dept_table_type;
命名块
»
被多次执行
» »
» »
命名块是一种带有标签的匿名块，标签为块指定了一个名称
子程序
子程序是存储在数据库中的过程（procedure）、函数（function），生成之后可以
程序包
程序包是存储在数据库中的一组子程序、变量定义 程序包中的子程序可以被其他程序包或者子程序调用
触发器
触发器是一种存储在数据库中的命名块，生成之后可以被多次执行 在相应的触发器事件发生之前或之后就会被执行一次或多次
异常
1. set serveroutput on设置输出、显示环境变量 2. set verify off : 设置是否进行数据校验。
set serveroutput on set verify off
DECLARE
2. 定义块变量
v_ename varchar2(50); BEGIN
3. 执行业务逻辑
Oracle数据库高级
查询和管理
第四章 使用PLSQL语言
本章内容
PL/SQL基本语法 PL/SQL异常处理 Oracle事务
2021/1/11
.
3
本章目标
掌握PL/SQL基本语法 掌握PL/SQL异常处理 掌握Oracle事务的使用
2021/1/11
.
4
PL/SQL语言简介
PL/SQL是Oracle在标准SQL语言上的过程性扩展 允许嵌入SQL语句，定义变量和常量， 允许过程语言结构（条件分支语句和循环语句）， 允许使用异常来处理Oracle错误 可以用于创建存储过程、触发器和程序包等， 也可以用于处理业务 规则、数据库事件或给SQL命令 的执行添加程序逻辑
end;
2021/1/11
.
13
数据类型
• %record数据类型
类似高级语言中的结构 declatyr首pee先em需p要_r定ec义ord记_t录yp类e 型is 和RE记CO录R变D(量
e当na引m用e e记m录p.e成na员m时e%，ty必pe须, 将记录变量作为前缀
• 示例sal：emp.sal%type, comm m%type, t根ota据l_输sa入l s的al%员ty工pe编号输出该员工的姓名、基本工资、奖金及实发 ); 工资。
v_debmmps__reocuotrpdute.pmupt__rlienceo('r实d_发ty工pe资;-：-声'||明v_记em录p类_r型ec变ord量.total_sal);
end;
2021/1/11
.
14
数据类型
• TABLE数据类型
TABLE(索引表)相当于一个键值集合，键是唯一的，用于查找对 应的值。键可以是整数或字符串
END IF;
注意：是elsif 不是elseif， 也不是else if
2021/1/11
.
16
流程控制
• 员工奖金发放：
输入员工编号，如果该员工
declare 原来没有奖金，则按照工资的10%发放 v_emp原em来p有%r奖ow金ty但pe不; 超过1000的，补到1000；
begin 其余的按照原来奖金基础再加上10%发放；
declare v_emp_record emp_record_type; begitnype emp_record_type is RECORD(--定义一个记录类型，包含员工信息
esnealemcet eenmapm.een,saaml,ne%vl(tcyopme,m,0),sal+nvl(comm,0) into v_emp_record sfraol memepm.spawl%hteyrpeee, mpno=7369; cdobmmms_eomutpp.ucot.mpumt_%litnyep(e'员, 工姓名：'|| v_emp_record.ename); tdobtaml_ss_aolustaplu%t.tpyupte_line('基本工资：'|| v_emp_record.sal); );dbms_output.put_line('奖金：'|| v_emp_m);
2021/1/11
.
6
程序结构
3.2.1 组成部分
定义部分
定义部分用于定义常量、变量、游标、异常和复杂数据类型
执行部分
执行部分用于实现应用模块功能，该部分包含需要执行的PL/SQL语句 和SQL语句
异常处理部分
异常处理部分用于处理执行部分可能出现的运行错误
PL/SQL块的基本结构
DECLARE 定义部分
END;
2021/1/11
.
8
PL/SQL注释
单行注释
单行注释可以在一行的任何地方由两个短横线（--）开始，并 且直到该行的最后
多行注释
多行注释由“/*”开始、“*/”结束，可以跨越多行，但不允许嵌 套
2021/1/11
.
9
常量与变量
在声明部分中可以声明需要使用的常量、变量、函数、 游标、异常处理名称
end;
2021/1/11
.
10
常量与变量
除了可以使用常量来给变量赋值之外，还可通过SELECT INTO语句将从数据库表中查询的结果赋予变量
set serveroutput on set verify off
DECLARE v_ename varchar2(50); BEGIN
select ename into v_ename from emp where empno=&eno;