1. PL/SQL 基础知识
什么是 PL/SQL
PL/SQL (Procedural Language/SQL，过程语言/SQL）是结合了 Oracle 过程语言和结构化查询语言 (SQL) 的一种扩展语言。使用 PL/SQL 可以编写具有很多高级功能的程序.虽然通过多个 SQL 语句也能实现同样的功能，但是相比而言，PL/SQL具有更为明显的一些优点。
(1) PL/SQL 具有编程语言的特点，它能把一组 SQL 语句放到一个模块中，使其更具模块化程序的特点。
(2) PL/SQL 可以采用过程性语言控制程序的结构，也就是说，我们可以在 PL/SQL 中增加逻辑结构，如判断、循环等程序结构。
(3)同其他的编程语言一样，PL/SQL 可以对程序中的错误进行自动处理，使程序能够在遇到错误时不会中断．即它的异常处理机制。
(4) PL/SQL 程序块具有更好的可移植性．可以移植到另一个Oracle数据库中。
(5) PL/SQL 程序减少了网络的交互，有助于提高程序性能。
-
PL/SQL 体系结构
PL/SQL 引擎用来编译和执行 PL/SQL 块或子程序，该引擎驻留在 Oracle 服务器中。PL/SQL 引擎仅执行过程语句, 而将 SQL 语句发送给 Oracle 服务器上的 SQL 语句执行器，由 SQL 语句执行器执行这些 SQL 语句。
-
PL/SQL 块简介
PL/SQL 是一种块结构的语言，它将一组语句放在一个块中。PL/SQL 块将逻辑上相关的声明和语句组合在一起。匿名块是一个未在数据库中命名的 PL/SQL 块，在运行时被传递到 PL/SOL 引擎以便执行。在 PL/SQL 块中可以使用 SELECT、INSERT、UPDATE、DELETE 等 DML 语句、事务控制语句及SQL函数等。PL/SQL 块中不允许直接使用CREATE、DROP 或 ALTER 等DDL语句，但可以通过动态SQL 来执行它们。一个 PL/SQL 块由三部分组成，即声明部分、执行部分、异常处理部分。
PL/SQL 块的结构：[declare] --声明部分: 在此声明 PL/SQL 用到的变量、类型及游标，以及局部的存储过程函数 begin --执行部分: 过程及 SQL 语句, 即程序的主要部分 [exception] --异常处理部分: 错误处理 end;其中,执行部分不能省略
-
运算符和表达式
关系运算符
一般运算符
逻辑运算符
-
常量和变量声明
声明变量: variable_name data_type [ (size) ] [ :=init_value ] 其中 variable_name：表示变量名称。 data_type：表示变量的 SQL、PL\SQL 数据类型。 size：指定变量范围。 init_value：指变量的初始值。 声明常量： variable_name constant data_type := value变量命名规则
（1）变量名首字母必须是英文字母
（2）其后可以是字母、数字或者特殊字符$、#和下划线
（3）变量名长度不超过30个字符
（4）变量名中不能有空格
（5）不能是SQL保留字
DECLARE
v_ename varchar2(20); --变量
v_rate number(7,2); --变量
c_rate_incr constant number(7,2):=1.10; --常量
begin
--方法1：通过 select into 给变量赋值
select enameinto v_ename from employee where empno='7788'; --查询结果只能返回一条语句赋值到变量中保存
--方法2：通过赋值操作符“ := ”给变量赋值
v_ename:='scott';
end;
- 注释
v_ename varchar2(20); --变量
/*
多
行
注释
*/
PL/SQL 数据类型
标量数据类型
包括数字、字符、布尔值和日期时间值四类
LOB 数据类型
Oracle 提供了 LOB （Large Object）类型，用于存储大的数据对象的类型。Oracle 目前主要支持 BFILE、BLOB、CLOB 及 NCLOB 类型。
属性类型
属性用于引用变量或数据库列的数据类型，以及表示表中一行的记录类型。PL\SQL 支持一下两种属性类型。
-
%type
定义一个变量，其数据类型与已经定义的某个数据变量（尤其是表的一列）的数据类型相一致，这时我们可以使用 %type 。
使用 %type 属性的优点在于:
（1）可以不必知道所引用的数据库列的数据类型。
（2）所引用的数据库列的数据类型可以实时改变，容易保持一致，不用修改 PL/SQL 程序。 -
%rowtype
返回一个记录类型，其数据类型和数据库表的数据结构相一致，这时可以使用 %rowtype 。
使用 %rowtype 属性的优点在于:
（1）可以不必知道所引用的数据库中列的个数和数据类型。
（2）所引用的数据库中列的个数和数据类型可以实时改变，容易保持一致，不用修改 PL/SQL程序。
declare
v_userid user.userid%type := 7788; --变量的类型不用写死，和某表某字段的类型保持一致
v_user user%rowtype; --记录类型（行类型）
begin
select * into v_user from user where userid=v_userid;
dbms_output.put_line('姓名:'||v_user.name||'工资:'||v_user.sal||'工作时间:'||v_user.hiredate); --输出
end;
PL/SQL控制语句
-
条件控制
条件控制用于根据条件执行一系列语句。条件控制包括 if 语句和 else 语句。if <布尔表达式> then pl/sql或sql语句； end if; ---------------------------------------- if <布尔表达式> then pl/sql或sql语句； else pl/sql或sql语句； end if; ----------------------------------------- if <布尔表达式> then pl/sql或sql语句； elsif <布尔表达式> then pl/sql或sql语句； elsif <布尔表达式> then pl/sql或sql语句； else pl/sql或sql语句； end if;注意是elsif ,不是elseif
这里顺便提一个 case when then 语句
case 条件表达式 when 条件表达式结果1 then pl/sql语句 when 条件表达式结果2 then pl/sql语句 when 条件表达式结果n then pl/sql语句 [else pl/sql语句] end case； -
循环控制
loop 要执行的语句 exit when<条件语句> --满足条件则退出循环 end loop; while <布尔表达式> loop 要执行的语句； end loop； for 循环计数器 in [reverse] 下限 .. 上限 loop 要执行的语句； end loop -
顺序控制
顺序控制用于按顺序执行语句。顺序控制包括 NULL 语句和 GOTO 语句。GOTO 语句不推荐使用。下面介绍NULL 语句。NULL 语句: 是一个可执行语句，相当于一个占位符或不执行任何操作的空语句，它可以使某些语句变得有意义，提高程序的可读性，保证其他语句结构的完整性和正确性。
--需求:显示变量v_counter的值，如果该变量小于10，则增加10并显示该变量改变后的值。
declare
v_counter number :=5;
begin
dbms_output.put_line('v_counter的当前值为:'||v_counter);
if v_counter>=10 then
null; --为了使语法变得有意义，去掉null会报语法错误
else
v_counter :=v_counter+10;
dbms_output.put_line('v_counter的改变后的值为:'||v_counter);
end if;
end;
2. 异常处理
在运行程序时出现的错误叫做异常。
发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分。
异常有两种类型
-
预定义异常
当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发。
begin 过程及sql语句; exception when <异常名称> then 过程及sql语句; when others then --others 处理程序确保不会漏过任何异常 过程及sql语句; END; -
用户定义异常
用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发。
PL/SQL中的用户自定义异常处理
（1）使用 RAISE 关键字引发异常
（2）使用 EXCEPTION 关键字处理异常
declare v_comm employee.comm%type; e_comm_is_null exception; begin select comm into v_comm from employee where empno=7788; if v_comm is null then raise e_comm_is_null; end if; exception where no_data_found then dbms_output.put_line('雇员不存在！错误为:'||sqlcode||sqlerrm); where e_comm_is_null then dbms_output.put_line('该雇员无补助'); where othres then dbms_output.put_line('出现其他异常'); end;
3. 游标
用来处理使用select语句从数据库中检索到的多行记录的工具
游标的分类
-
显示游标
返回多条记录时，使用显示游标逐行读取
（1）声明游标cursor cursor_name [ (parameter [,parameter]···') ] [return return_type] is select_statement; 其中 cursor_name：指游标的名称。 parameter：用于为游标指定输入参数。在指定数据类型时，不能使用长度约束。例如：number(4)、char(10) 等都是错误的。 return_type：用于定义游标提取的行的数据。 select_statement：指游标定义的查询语句。（2）打开游标
open cursor_name [ (parameters) ];（3）提取游标
fetch cursor_name into variables; 其中 cursor_name：指游标的名称。 variables：指变量名。（4）关闭游标
close cursor_name;显示游标属性
使用显示游标输出每个员工的姓名和薪水:declare name employee.ename%type; sal employee.sal%type; --1.声明游标 cursor emp_cyrsor is select ename.sal from employee; begin --2.打开游标 open emp_cursor loop --3.提取游标 fetch emp_cursor into name,sal; exit where emp_cursor%notfound; --如果游标己经没有下一行了就退出循环 dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员:'||name||sal); end loop; --4.关闭游标 close emp_cursor; end;使用显示游标删除或更新
使用游标时，如果处理过程中需要删除或更新行，在定义游标时必须使用 SELECT…FOR UPDATE 语句，而在执行 DELETE 和 UPDATE 时使用 WHERE CURRENT OF 子句指定游标的当前行。声明更新游标的语法如下所示。
cursor cursor_name is select_statement for update [of columns]; 其中 for update [of columns]为更新查询，锁定选择的行。 (1）当选择单表更新查询时，可以省略 of 子句; (2）当选择多个表更新查询时，被锁定的行来源于 of 子句后声明的列所在的表中的行。例如: 使用 for update of sal; ，则锁定 employee 表中的行，使用 for update of dname，则锁定dept表中的行。 在使用 for update 子句声明游标之后可以使用以下语法更新或删除行。 update table_name set column_name = column_value where current of cursor_name; delete table_name where current of cursor_name; 其中 多表查询更新时, 更新表为锁定行所在的表declare cursor emp_cursor is select ename,sal from employee e inner join dept d on e.deptno=d.deptno for update of sal; begin if not emp_cursor%isopen then open emp_cursor; end if; loop update employee set sal=sal+200 where current of emp_cursor; end loop; close emp_cursor; end;上例只能更新 employee 表, 不能更新 dept 表。
-
隐式游标
PLSQL自动为DML语句创建隐式游标，包含一条返回记录
4. 存储过程
字程序的组成
子程序是已命名的 RL/SQL 块，它们存储在数据库中，可以为它们指定参数，也可以从任何数据库客户端和应用程序中调用它们。子程序包括存储过程和函数。使用存储过程执行操作，使用函数执行操作并返回值，就像应用程序中的方法—样。
与匿名的 PL/SQL 块一样，子程序具有声明部分、可执行部分、异常处理部分（可选)。
- 声明部分
声明部分包括类型、游标．常量、变量、异常和嵌套子程序的声明。这些项是局部的，退出子序后将不复存在。 - 可执行部分
可执行部分包括赋值、控制执行过程及操纵 Oracle 数据的语句。 - 异常处理部分
异常处理部分包括异常处理程序，负责处理执行存储过程中出现的异常。
子程序的优点如下。
（1）模块化：通过子程序，可以将程序分解为可管理的、明确的逻辑模块。
（2）可重用性：子程序在创建并执行后，就可以在任意数目的应用程序中使用。
（3）可维护性：子程序可以简化维护操作，如果一个子程序受到影响，则只需修改该子程序的定义。
（4）安全性：用户可以设置权限，使得访问数据的唯一方式就是通过用户提供的存储过程和函数。这不仅可以让数据更加安全，而且可以保证它的正确性。
存储过程用法
-
创建存储过程
create [or replace] procedure procedure _name [ (parameter_list) ] {is|as} [ local_declarations ] begin executable_statements [exeception] [ exeception_handlers ] end [ procedure_name ]; 其中 procedure _name：存储过程的名称。 parameter_list：参数列表，可选。 local_declarations：局部声明，可选。 executable_statements：可执行语句。 exeception_handlers：异常处理程序，可选。 or replace：可选。如果不包含 or replace 语句，则表示仅仅新建一个存储过程，如果系统存在该存储过程，则会报错；如果包含 or replace 语句，则表示如果系统中没有此存储过程则新建，有就用现在的替换原来的存储过程。--添加员工记录 create or replace procedure add_employee( eno number, name varchar2, salary number, job varchar2 default 'clerk', dno number ) is begin insert into employee(empno,ename,sal,job,deptno)values(eno,name,salary,job,dno); end; -
调用存储过程
begin --按位置传递参数 add_employee(2111,'mary',2000,'manager',10); --按名字传递参数 add_employee(dno>=10,name>='mary',salary>=2000,eno>=2112,job>='manager'); --混合方式传递参数 add_employee(3111,dno>=10,name>='mary',salary>=2000,job>='manager'); end; -
存储过程的参数模式
调用程序是通过参数向被调用的存储讨程传递值的。参数传递的模式有三种：IN、 OUT 和 IN OUT，即输入参数，输出参数和输入/输出参数。in 模式只能将实参传递给形参，进入函数内部，但只能读不能写，函数返回时实参的值不变，out 模式会忽略调用时的实参值（或说该形参的初始值总是NULL)，但在函数内部可以被读或写，函数返回时形参的值会赋给实参。in out 具有前两种模式的特性，即调用时，实参的值总是传递给形参；结束时，形参的值传递给实参。parameter_name [ in | out | in out ] datatype [ {:= | default} expression ]in 模式是默认参数传递模式。如果未指定参数的模式，则认为该参数是 in 参数。而对于 out 和 in out 参数，必须明确指定 out 和 in out。
在返回到调用环境之前，必须先给 out 或 in out 参数赋值。
可以在参数列表中为 in 参数赋予一个默认值，但不能为 out 、 in out 参数赋予默认值。--创建存储过程 create or replace procedure queryemp( v_empno in employee.empno%type, v_ename out employee.ename%type, v_sal in out employee.sal%type, ) as begin select ename,sal into v_ename,v_sal from employee where empno = v_empno; end; --调用存储过程 declare v1 employee.ename%type; v2 employee.sal%type; begin v2 := 5000; queryemp(7788,v1,v2); end; -
存储过程的访问权限
存储过程创建之后，只有创建该存储过程的用户和管理员才有权调用它。其他用户如果要调用该存储过程，需要得到存储过程的 execute 权限。--授予 a_oe 执行 employee 的权限 grant execute on add_employee to a_oe; --撤销权限 revoke execute on add_employee to a_oe; -
删除存储过程
drop procedure procedure_name; --procedure_name:存储过程名称
存储过程规则
- 存储过程中不可以直接使用 DDL 语句，可以通过动态 SQL 实现。但不建议频繁地使用 DDL 语句。
- 存储过程必须有相应的出错处理功能。
- 存储过程中变量在引用表字段的时候，需使用 %type 和 %rowtype 类型。
- 必须在存储过程中做异常捕获，并将异常信息通过 os_Msg 变量输出。
- -19999~-1 的异常为 Oracle 定义的异常代码。
- 存储过程必须包含两个输出参数，即 on_Flag (number) 和 os_Msg (varchar2) ，分别用于标识过程的执行状态及过程提示信息（包括异常情况下的异常信息)。
- “when others” 必须放置在异常处理代码的最后面，作为默认处理器处理没有显示处理的异常
--最终示例
create or replace procedure add_employee(
eno employee.empno%type,
name employee.ename%type,
salary employee.sal%type,
job employee.job%type default 'clerk',
dno employee.deptno%type,
on_flag out number,
os_msg out varchar2
)
is
begin
insert into employee(empno,ename,sal,job,deptno)values(eno,name,salary,job,dno);
on_flag:=1;
os_msg:='添加成功!';
exception
where dup_val_on_index then
on_flag:=-1;
os_msg:='该员工也存在.';
where others then
on_flag:=-2;
os_msg:='其他错误，与管理员联系。';
end;
2726
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
