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 程序减少了网络的交互，有助于提高程序性能。

1. PL/SQL 体系结构
   PL/SQL 引擎用来编译和执行 PL/SQL 块或子程序，该引擎驻留在 Oracle 服务器中。PL/SQL 引擎仅执行过程语句, 而将 SQL 语句发送给 Oracle 服务器上的 SQL 语句执行器，由 SQL 语句执行器执行这些 SQL 语句。
   

2. 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;
   

   其中,执行部分不能省略

3. 运算符和表达式

   关系运算符
   
   一般运算符
   
   逻辑运算符
   

4. 常量和变量声明

    声明变量: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保留字
   

DECLAREv_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;

5. 注释

v_ename varchar2(20);  --变量/*多行注释
*/

PL/SQL 数据类型

标量数据类型
包括数字、字符、布尔值和日期时间值四类

LOB 数据类型
Oracle 提供了 LOB （Large Object）类型，用于存储大的数据对象的类型。Oracle 目前主要支持 BFILE、BLOB、CLOB 及 NCLOB 类型。

属性类型
属性用于引用变量或数据库列的数据类型，以及表示表中一行的记录类型。PL\SQL 支持一下两种属性类型。

1. %type
   定义一个变量，其数据类型与已经定义的某个数据变量（尤其是表的一列）的数据类型相一致，这时我们可以使用 %type 。
   使用 %type 属性的优点在于:
   （1）可以不必知道所引用的数据库列的数据类型。
   （2）所引用的数据库列的数据类型可以实时改变，容易保持一致，不用修改 PL/SQL 程序。

2. %rowtype
   返回一个记录类型，其数据类型和数据库表的数据结构相一致，这时可以使用 %rowtype 。
   使用 %rowtype 属性的优点在于:
   （1）可以不必知道所引用的数据库中列的个数和数据类型。
   （2）所引用的数据库中列的个数和数据类型可以实时改变，容易保持一致，不用修改 PL/SQL程序。

	declare v_userid user.userid%type := 7788;	--变量的类型不用写死，和某表某字段的类型保持一致v_user user%rowtype;	--记录类型（行类型）beginselect * 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控制语句

1. 条件控制
   条件控制用于根据条件执行一系列语句。条件控制包括 if 语句和 else 语句。

    if <布尔表达式> thenpl/sql或sql语句；end if;----------------------------------------if <布尔表达式> thenpl/sql或sql语句；elsepl/sql或sql语句；end if;-----------------------------------------if <布尔表达式> thenpl/sql或sql语句；elsif <布尔表达式> thenpl/sql或sql语句；elsif <布尔表达式> thenpl/sql或sql语句；elsepl/sql或sql语句；end if;	
   

   注意是elsif ,不是elseif

   这里顺便提一个 case when then 语句

    case 条件表达式when 条件表达式结果1 thenpl/sql语句when 条件表达式结果2 thenpl/sql语句when 条件表达式结果n thenpl/sql语句[else pl/sql语句]end case；
   

2. 循环控制

    loop要执行的语句exit when<条件语句>	 --满足条件则退出循环end loop;while <布尔表达式>  loop要执行的语句；end loop； for 循环计数器 in [reverse] 下限 .. 上限 loop要执行的语句；end loop
   

3. 顺序控制
   顺序控制用于按顺序执行语句。顺序控制包括 NULL 语句和 GOTO 语句。GOTO 语句不推荐使用。下面介绍NULL 语句。

   NULL 语句: 是一个可执行语句，相当于一个占位符或不执行任何操作的空语句，它可以使某些语句变得有意义，提高程序的可读性，保证其他语句结构的完整性和正确性。

--需求:显示变量v_counter的值，如果该变量小于10，则增加10并显示该变量改变后的值。
declarev_counter number :=5;
begindbms_output.put_line('v_counter的当前值为:'||v_counter);if v_counter>=10 thennull;	--为了使语法变得有意义，去掉null会报语法错误elsev_counter :=v_counter+10;dbms_output.put_line('v_counter的改变后的值为:'||v_counter);end if;
end;

2. 异常处理

在运行程序时出现的错误叫做异常。
发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分。

异常有两种类型

1. 预定义异常
   当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发。
   

    begin过程及sql语句;exceptionwhen <异常名称> then过程及sql语句;when others then			--others 处理程序确保不会漏过任何异常过程及sql语句;END;
   

2. 用户定义异常
   用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发。
   PL/SQL中的用户自定义异常处理
   （1）使用 RAISE 关键字引发异常
   （2）使用 EXCEPTION 关键字处理异常

   

   	declarev_comm employee.comm%type;e_comm_is_null exception;beginselect comm into v_comm from employee where empno=7788;if v_comm is null thenraise e_comm_is_null;end if;exceptionwhere no_data_found thendbms_output.put_line('雇员不存在！错误为:'||sqlcode||sqlerrm);where e_comm_is_null thendbms_output.put_line('该雇员无补助');where othres thendbms_output.put_line('出现其他异常');end;
   

3. 游标

用来处理使用select语句从数据库中检索到的多行记录的工具

游标的分类

1. 显示游标
   返回多条记录时，使用显示游标逐行读取
   （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;
   

   显示游标属性
   
   使用显示游标输出每个员工的姓名和薪水:

   declarename employee.ename%type;sal employee.sal%type;--1.声明游标cursor emp_cyrsor is select ename.sal from employee;
   begin --2.打开游标open emp_cursorloop--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其中 多表查询更新时, 更新表为锁定行所在的表
   

   declarecursor 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 thenopen emp_cursor;end if;loopupdate employee set sal=sal+200 where current of emp_cursor;end loop;close emp_cursor;
   end;
   

   上例只能更新 employee 表, 不能更新 dept 表。

2. 隐式游标
   PLSQL自动为DML语句创建隐式游标，包含一条返回记录

4. 存储过程

字程序的组成

子程序是已命名的 RL/SQL 块，它们存储在数据库中，可以为它们指定参数，也可以从任何数据库客户端和应用程序中调用它们。子程序包括存储过程和函数。使用存储过程执行操作，使用函数执行操作并返回值，就像应用程序中的方法—样。
与匿名的 PL/SQL 块一样，子程序具有声明部分、可执行部分、异常处理部分（可选)。

1. 声明部分
   声明部分包括类型、游标．常量、变量、异常和嵌套子程序的声明。这些项是局部的，退出子序后将不复存在。
2. 可执行部分
   可执行部分包括赋值、控制执行过程及操纵 Oracle 数据的语句。
3. 异常处理部分
   异常处理部分包括异常处理程序，负责处理执行存储过程中出现的异常。
   子程序的优点如下。
   （1）模块化：通过子程序，可以将程序分解为可管理的、明确的逻辑模块。
   （2）可重用性：子程序在创建并执行后，就可以在任意数目的应用程序中使用。
   （3）可维护性：子程序可以简化维护操作，如果一个子程序受到影响，则只需修改该子程序的定义。
   （4）安全性：用户可以设置权限，使得访问数据的唯一方式就是通过用户提供的存储过程和函数。这不仅可以让数据更加安全，而且可以保证它的正确性。

存储过程用法

1. 创建存储过程

    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)isbegininsert into employee(empno,ename,sal,job,deptno)values(eno,name,salary,job,dno);end;	
   

2. 调用存储过程

   	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;
   

3. 存储过程的参数模式
   调用程序是通过参数向被调用的存储讨程传递值的。参数传递的模式有三种：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 out employee.sal%type,)asbeginselect ename,sal into v_ename,v_sal from employee where empno = v_empno;end;--调用存储过程 declarev1 employee.ename%type;v2 employee.sal%type;beginv2 := 5000;queryemp(7788,v1,v2);end;
   

4. 存储过程的访问权限
   存储过程创建之后，只有创建该存储过程的用户和管理员才有权调用它。其他用户如果要调用该存储过程，需要得到存储过程的 execute 权限。

   --授予 a_oe 执行 employee 的权限 
   grant execute on add_employee to a_oe;
   --撤销权限
   revoke execute on add_employee to a_oe;
   

5. 删除存储过程

   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
begininsert into employee(empno,ename,sal,job,deptno)values(eno,name,salary,job,dno);on_flag:=1;os_msg:='添加成功!';
exceptionwhere dup_val_on_index thenon_flag:=-1;os_msg:='该员工也存在.';where others thenon_flag:=-2;os_msg:='其他错误，与管理员联系。';
end;