第八章PLSQL.docx
《第八章PLSQL.docx》由会员分享，可在线阅读，更多相关《第八章PLSQL.docx（25页珍藏版）》请在冰豆网上搜索。
第八章PLSQL
PL/SQL编程
1.概述
什么是PL/SQL？
pl/sql（procedurallanguage/sql）是oracle在标准的sql语言上的扩展.pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误,这样使得它的功能变得更加强大
PL/SQL有什么优点？
⏹支持SQL【DML,TCL,游标，函数，运算符，伪列等】
⏹提高应用程序的运行性能
⏹模块化的设计思想【块结构，如存储过程，函数，触发器等】
⏹减少网络传输量
⏹提高安全性
2.PL/SQL体系结构
PL/SQL主要结合了ORACLE的过程语言PL和结构化查询语言SQL，当我们执行PL/SQL块时，请求发送到ORACLE服务器，有PL/SQL引擎来编译和执行PL/SQL块或者子程序，该引擎驻留在Oracle服务器中，其中PL与SQL执行不同分工，过程语句有PL语句执行器执行，SQL语句有SQL语句执行器执行。
3.PL/SQL块
PL/SQL块概述
PL/SQL块指的就是将逻辑上相关的声明和语句组合在一起。
块（block）是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块。
要完成相对简单的应用功能，可能只需要编写一个pl/sql块；但是如果要想实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块
PL/SQL块的分类
根据块的定义和使用，我们可以简单将块分为匿名块和非匿名块，匿名块执行时传入到PL/SQL引擎即可，之前没有定义，非匿名块主要包括过程，函数，触发器，程序包等，事先已经在服务器定义好，可直接根据名称执行。
注意：
在块中不能直接使用DDL语句，但可以通过动态SQL来解决
PL/SQL块的结构
PL/SQL分为三个部分，声明部分、可执行部分和异常处理
⏹声明部分
声明部分包含了变量和常量的数据类型和初始值，由declare开始，如果块中不需要变量和常量，可以忽略声明部分。
⏹可执行部分
执行部分是PL/SQL块中的指令部分，由关键字begin开始，所有可以执行语句都放在这一部分，其他块也可以嵌套在这一部分
⏹异常处理
异常处理部分是可选的，用来处理可以执行部分引发的异常或错误
PL/SQL基础
编写规范
Ø注释
--单行注释
/*块注释*/
Ø标识符的命名规范
1.定义变量：
建议用v_作为前缀v_price
2.定义常量：
建议用c_作为前缀c_pi
3.定义游标：
建议用_cursor作为后缀emp_cursor
4.定义例外：
建议用e_作为前缀e_error
4.PL/SQL编程
✓定义第一个ORACLE程序块，在控制台输出HelloWorld!
--仅包含执行部分的PL/SQL块
begin
dbms_output.put_line（'HelloWorld!
'）;
end;
✓说明:
dbms_output.put_line（）是Oracle提供的一个执行输出操作的过程,其中dbms_ouput是包名，put_line（）是过程名
✓SETSERVEROUTPUTON:
开启控制台输出选项
✓定义包含定义和执行部分的PL/SQL块
declare
namevarchar2（30）;--定义变量name存储姓名
agenumber;--定义变量age存储年龄
sexvarchar2（10）default'男';--定义变量sex存储性别，默认男
minAgeconstantnumber:
=18;--定义常量，存储最小年龄
begin
name:
='zhangSan';
age:
=20;
dbms_output.put_line（'姓名:
'||name||',年龄:
'||age||'性别:
'||sex）;
dbms_output.put_line（'学生最小年龄是:
'||minAge）;
end;
注意：
在PL/SQL块中，可执行部分中所有使用的变量必须先声明再使用。
⏹声明变量
Ø语法
变量名数据类型[NOTNULL][:
=|默认值]
例如:
Namevarchar2（30）;--定义变量name
Sexvarchar（10）notnulldefault‘男’--notnull必须设置默认值
minAgenumber:
=18–设置默认值
minAgenumberdefault18–设置默认值
Ø为变量赋值
1：
使用赋值运算符赋值
例如:
name:
=’Jack’;
2：
使用selectinto动态赋值
例如：
selectenameintomynamefromempwhereempno=7788;
Selectename,salintomyname,mysalfromempwhereempno=7788
Selectsum（sal）intoallSalfromemp
⏹声明常量
Ø语法
常量名Constant数据类型:
=常量值
例如:
minAgeConstantnumber:
=18
⏹PL/SQL的数据类型
每个PL/SQL变量都具有一个指定存储格式，值的有效范围和约束条件的数据类型，PL/SQL提供了各种内置数据类型，包括varchar2,number,date,recode引用类型，大数据类型以及用户自定义类型等。
PL/SQL将这些类型分成了4个大类，分别为：
Ø标量数据类型
ØLOB数据类型
Ø组合【复合】数据类型
Ø引用【参照】数据类型
a）标量数据类型
标量类型是非常常用的一种类型，没有内部组件，仅包含单个值，主要包括number，character,date/time,boolean类型
案例一:
根据用户传入的工号，查询出用户姓名。
declare
namevarchar2（4）;
begin
--&代表由用户根据提示手动输入
selectenameintonamefromempwhereempno=&empno;
dbms_output.put_line（'姓名为:
'||name）;
end;
案例二:
如果上面的案例中，查询出的用户姓名长度超过4时会如何，程序将出错，如何解决这种问题？
可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度
declare
nameemp.ename%type;
begin
selectenameintonamefromempwhereempno=&empno;
dbms_output.put_line（'姓名为:
'||name）;
end;
emp.ename%type:
和emp表中ename的数据类型，长度一致
案例三：
如果查询返回一个结果集，结果集中列的个数，类型不知道，该如何解决呢？
%RowType:
返回一个记录类型，其数据类型和数据表的数据结构一致
declare
v_noemp.empno%type:
=&empno;
recemp%rowtype;
begin
select*intorecfromempwhereempno=v_no;
dbms_output.put_line（'姓名:
'||rec.ename||'工资'||rec.sal）;
end;
b）组合数据类型record
用来存储多个值的变量称之为组合或者复合变量，其中存储的多个值可以是PL/SQL记录,也可以是PL/SQL中的表
PL/SQL记录:
类似与高级语言中的结构体,需要注意的是,当引用pl/sql记录成员时,必须要加记录变量作为前缀（记录变量.记录成员）
案例一:
查询出编号为7788员工的姓名，岗位，工资。
declare
typeemp_mytypeisrecord
（
nameemp.ename%type,
jobemp.job%type,
salemp.sal%type
）;
einfoemp_mytype;
begin
selectename,job,salintoeinfofromempwhereempno=7788;
dbms_output.put_line（'姓名:
'||einfo.name||'岗位:
'||einfo.job||'待遇:
'||einfo.sal）;
end;
不足：
一次只能存储一条记录的值
c）组合数据类型table
PL/SQL表:
相当于高级语言中的数组.但是需要注意的是在高级语言中数组的下标不能为负数,而pl/sql是可以为负数的,并且表元素的下标没有限制
案例:
查询出部门编号为7788,7900,7902的员工姓名
declare
typemy_tableistableofemp.ename%typeindexbybinary_integer;
einfomy_table;
begin
selectenameintoeinfo
（1）fromempwhereempno=7788;
selectenameintoeinfo
（2）fromempwhereempno=7900;
selectenameintoeinfo（3）fromempwhereempno=7902;
dbms_output.put_line（'姓名1:
'||einfo
（1）||'姓名2:
'||einfo
（2）||'姓名3:
'||einfo（3））;
end;
注意:
可以使用bulkcollect一次将符合条件的数据全部写入表中
d）引用数据类型
e）LOB数据类型
✓定义包含定义，执行过程和例外处理部分的PL/SQL块
declare
--声明变量
v_namevarchar2（20）;
v_salnumber（7,2）;
begin
--执行查询,条件中的&表示从控制接受数据
selectename,salintov_name,v_sal
fromempwhereempno=&no;
--控制台输出
dbms_output.put_line（'用户名：
'||v_name）;
dbms_output.put_line（'工资：
'||v_sal）;
exception
--例外处理（no_data_found）
whenno_data_foundthen
dbms_output.put_line（'执行查询没有结果'）;
end;
预定义例外
1）case_not_found预定义例外
在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found例外。
2）cursor_already_open预定义例外
当重新打开已经打开的游标时，会隐含的触发cursor_already_open例外。
3）dup_val_on_index预定义例外
在唯一索引所对应的列上插入重复的值时，会隐含的触发例外
4）invalid_cursorn预定义例外
当试图在不合法的游标上执行操作时，会触发该例外
5）invalid_number预定义例外
当输入的数据有误时，会触发该例外
6）no_data_found预定义例外
当执行selectinto没有返回行，就会触发该例外
7）too_many_rows预定义例外
当执行selectinto语句时，如果返回超过了一行，则会触发该例外
8）zero_divide预定义例外
当执行2/0语句时，则会触发该例外
9）value_error预定义例外
当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外value_error
5.PL/SQL控制语句
✓条件分支语句
if—then
declare
--声明变量
v_empnoemp.empno%type;
v_salemp.sal%type;
begin
--根据雇员编号查询工资
selectempno,salintov_empno,v_salfromempwhereempno=&no;
--如果工资小于2000就加100
ifv_sal<2000
then
--工资加100
updateempsetsal=sal+100whereempno=v_empno;
--提交
commit;
endif;
end;
if—then—else
declare
--声明变量
v_loginnamevarchar2（10）;
v_passwordvarchar2（10）;
begin
--从控制台接收数据
v_loginname:
='&ln';
v_password:
='&pw';
ifv_loginname='admin'andv_password='123456'
then
dbms_output.put_line（'用户登录成功！
'）;
else
dbms_output.put_line（'用户登录失败！
'）;
endif;
end;
if—then—elsif—else
declare
--声明变量
v_empnoemp.empno%type;
v_jobemp.job%type;
begin
--根据雇员编号查询职位
selectempno,jobintov_empno,v_jobfromempwhereempno=&no;
/*如果雇员所属职位是manager工资加1000
职位是salesman工资加500
其他职位加200
*/
ifv_job='MANAGER'then
--MANAGER职位工资加1000
updateempsetsal=sal+1000whereempno=v_empno;
elsifv_job='SALESMAN'then
--SALESMAN职位工资加500
updateempsetsal=sal+500whereempno=v_empno;
else
--其他职位工资加200
updateempsetsal=sal+200whereempno=v_empno;
endif;
--提交
commit;
end;
case
declare
--声明变量
v_marknumber（4）;
v_outstrvarchar2（40）;
begin
--从控制台接收成绩
v_mark:
=&m;
case
whenv_mark<=100andv_mark>=90then
v_outstr:
='优秀';
whenv_mark<90andv_mark>=80then
v_outstr:
='良好';
whenv_mark<80andv_mark>=70then
v_outstr:
='中等';
whenv_mark<70andv_mark>=60then
v_outstr:
='及格';
whenv_mark<60andv_mark>=0then
v_outstr:
='不及格';
else
v_outstr:
='成绩输入有误';
endcase;
--控制台输出
dbms_output.put_line（v_outstr）;
end;
✓循环语句
1）loop
LOOP要执行的语句;
EXITWHEN<条件语句>/*条件满足，退出循环语句*/
ENDLOOP;
其中：
EXITWHEN子句是必须的，否则循环将无法停止。
declare
v_numnumber（4）:
=1;
begin
--从控制台接收数据并插入到account表中
loop
insertintoaccountvalues（v_num,'&name'）;
exitwhenv_num=10;
v_num:
=v_num+1;
endloop;
end;
2）while
WHILE<布尔表达式>LOOP要执行的语句;ENDLOOP;
其中：
✓循环语句执行的顺序是先判断<布尔表达式>的真假，如果为真则循环执行，否则退出循环
✓在WHILE循环语句中仍然可以使用EXIT或EXITWHEN子句
declare
v_numnumber（4）:
=1;
begin
--从控制台接收数据并插入到account表中
whilev_num<11
loop
insertintoaccountvalues（v_num,'&name'）;
v_num:
=v_num+1;
endloop;
end;
--处理复杂问题也可以使用嵌套循环，如下，打印九九乘法表
declare
v_iint:
=1;
v_jint:
=1;
begin
whilev_i<=9
loop
v_j:
=1;--每行都从第一列开始打印
whilev_j<=v_i
loop
dbms_output.put（v_j||'*'||v_i||'='||v_i*v_j||''）;
v_j:
=v_j+1;
endloop;
v_i:
=v_i+1;
dbms_output.put_line（''）;--打印换行
endloop;
end;
3）for
FOR循环计数器IN[REVERSE]下限..上限LOOP要执行的语句;ENDLOOP;
其中：
✓每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1
✓跟在INREVERSE后面的数字必须是从小到大的顺序，但不一定是整数，可以是能够转换成整数的变量或表达式
✓可以使用EXITWHEN子句退出循环
--输出1到10的数
begin
foriin1..10
loop
--控制台输出
dbms_output.put_line（i）;
endloop;
end;
--输出一组数据
declare
--声明表类型
typeemp_table_typeistableofvarchar2（20）
indexbyPLS_INTEGER;--表示表按整数来排序
v_enamesemp_table_type;--定义变量引用表类型
begin
--所有值赋予v_enames
selectenamebulkcollectintov_enamesfromemp;
--输出
foriin1..v_enames.count
loop
dbms_output.put_line（v_enames（i））;
endloop;
end;
--记录类型可以和表类型结合使用
--定义一个表类型，要求存储所有的员工姓名和工资
declare
--声明记录类型
typeemp_typeisrecord
（
v_nameemp.ename%type,--存储姓名
v_salemp.sal%type--存储工资
）;
--声明表类型
typeemp_table_typeistableofemp_type
indexbyPLS_INTEGER;--表示表按整数来排序
v_infosemp_table_type;--定义变量引用表类型
begin
--所有值赋予v_enames
selectename,salbulkcollectintov_infosfromemp;
--输出
foriin1..v_infos.count
loop
dbms_output.put_line（'姓名'||v_infos（i）.v_name||',工资'||v_infos（i）.v_sal）;
endloop;
end;
6.动态SQL
在PL/SQL块中，可以执行DML和TCL,但是不可以直接执行DDL以及DCL，如果想在块中使用，必须使用动态SQL
ORACLE的编译程序块方式有2种，分别是:
1）前期联编
SQL语句在程序编译期间就以及确定，大多数的编译情况属于这种类型，也称为静态SQL语句
2）后期联编
SQL语句只有在运行阶段才建立并执行，也称为动态SQL语句
Oracle中动态SQL可以通过本地动态SQL来执行，也可以通过DBMS_SQL包来执行
Ø本地动态执行SQL语法:
Executeimmediatedynamic_sql_string
[intodefine_variable_list]
[usingbind_argument_list]
说明:
dynamic_sql_string:
动态SQL语句
define_variable_list:
用于接受select查询记录值的变量列表
bind_argument_list:
绑定输入参数的列表
案例一:
本地动态SQL执行DDL语句
Declare
table_namevarchar2（50）:
='&table_name';
str_sqlvarchar2（500）;
v_countint;
begin
--查询表中是否有记录
str_sql:
='selectcount（*）from'||table_name;
--执行动态语句，并将结果赋值给变量v_count
executeimmediatestr_sqlintov_count;
ifv_count>0then
dbms_output.put_line（'表中有数据，不能删除!
'）;
else
--构建动态DDL语句
str_sql:
='droptable'||table_name||'purge';
executeimmediatestr_sql;
dbms_output.put_line（'表已经删除!
'）;
commit;
endif;
end;
案例二:
本地执行DML语句
Declare
empnovarchar2（50）:
='7902';
str_sqlvarchar2（500）;
begin
str_sql:
='deleteempwhereempno=:
1';
--动态执行DML语句，并赋予动态参数值
executeimmediatestr_sqlusingempno;
co
升级会员 