PL/SQL编程

一、PL/SQL块结构

由三部分组成：声明部分、执行部分、异常处理部分。即：

DECLARE
      --声明部分（可选），PL/SQL用到的变量、类型及游标，以及局部的存储过程及函数
BEGIN
      --执行部分（必须），过程及SQL语句，即程序的主要部分
      --SQL语句的操作：select ... into... from ...where...
EXCEPTION
     --异常处理部分（可选）
     --使用when  ...  then ...语句
END；   --end后必须加“；”号

 二、使用

 

(1)使用前必须先使用 set serveroutput on命令，否则，执行完之后没有输出结果

(2)输出语句：

 

//输出helloworld,类似于Java中的system.out.println();
dbms_output.put_line('helloworld');

（3）赋值 用 ：= 值 

（4）%ROWTYPE操作符

返回一个记录类型，其数据类型和其数据库表中列的类型相一致

例如：

declare
      v_stu student%rowtype;

 优点：

1、所引用的数据库表中列的个数和数据类型可以不必知道；

2、所引用的数据库表中列的个数和数据类型可以实时改变，对此代码不受影响

 

三、变量的声明

 

示例：（以student表为例）

方式一：

 

DECLARE
v_name  varchar2(20);//v_name 为变量名
v_age  number(2);
BEGIN
SELECT NAME,EMAIL INTO V_NAME,V_EMAIL FROM STUDENT WHERE ID = 6;
DBMS_OUTPUT.PUT_LINE(V_NAME||'-----'||V_EMAIL);
END;

 方式二：

 

 

v_name  student.name%type;//表示v_name这个变量的类型和student表中name列的数据类型相同
v_age student.age%type;

 方式三：（记录类型的方式，相当于java中的类，把这些需要声明的变量包在里面）

 

DECLARE
type stu_record is record(//stu_record为记录类型名,后面是圆括号
v_name student.name%type，//此处有逗号
v_name student.age%type//此处为最后一行，没符号
);//此处有分号
v_stu_record stu_record;//还需要声明一个记录类型的变量，相当于java中的对象
BEGIN
SELECT NAME,EMAIL INTO v_stu_record  FROM STUDENT WHERE ID = 6;//查询的行必须和声明的顺序一致
DBMS_OUTPUT.PUT_LINE(v_stu_record.V_NAME||'-----'||v_stu_record.V_EMAIL);//调用对应的变量
END;

四、流程控制

 （1）条件语句

//第一种,注意：实际上if后面直接是布尔表达式，并不是放在<>里面的
if<布尔表达式>then
PL/SQL语句和SQL语句；
end if;

//第二种
if<布尔表达式>then
PL/SQL语句和SQL语句；
else
其他语句；
end if;

//第三种,注意其中的elsif没有e,不能写成elseif
if<布尔表达式>then
PL/SQL和SQL语句；
elsif<其它布尔语句>then 
其它语句；
elsif<其它布尔语句>then
其它语句；
else
其它语句；
end if;

 （2）case表达式

//结果中的变量要声明在前面，如:v_temp := ,结果1，结果2...为它的值；情况1，情况2等不能为变量
可为1,2,3...这样的值
case <表达式>
      when 情况1 then 结果1
      when 情况2 then 结果2  
                  ...
      when 情况n then 结果n
      [else 结果n+1]  //可以没有此语句
end;

 五、循环

 （1）简单循环

loop
    要执行的语句；
exit when 条件语句；//条件满足，退出循环语句
end loop;

 例如：

//打印1-100
declare 
      //初始化条件
     v_i number(5) :=1;
begin
    loop
//循环体
    dbms_output.put_line(v_i);
//循环条件
    exit when v_i >=100;
//迭代条件
      v_i = v_i + 1;
    end loop;
end;

  (2)while循环                              

while <布尔表达式> loop
要执行的语句；
end loop;

 (3)for循环

for 循环变量 in [reverse] 下限 .. 上限 loop
要执行的语句；
end loop;

注意：每循环一次，循环变量自动加1；使用关键字reverse,循环变量自动减1。跟在
in reverse 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或者
表达式。可以使用exit退出循环。

 六、游标

1、

//1、定义游标
cursor 游标名 is select ... from ... where...;
//2、打开游标
open 游标名；
//3、提取游标
fetch 游标名 into 变量名；
//4、获取游标中的值
while 游标名%found  loop  //类似于java集合中的hasNext()方法
dbms_output.put_line("变量名");//输出语句
fetch 游标名 into 变量名；//
end loop;
//5、关闭游标
close 游标名；

 例如：

//打印80号部门的所有人的工资：salary
declare
      v_sal employees.salary%type;
     //定义游标
     cursor emp_sal_cursor is select salary from employees where       department =80;
begin
     //打开游标
    open emp_sal_cursor;
    //提取游标
    fetch emp_sal_cursor into v_sal;
while emp_sal_cursor%found loop
dbms_output.put_line(v_sal);
fetch emp_sal_cursor into v_sal; 
  end loop;
close emp_sal_cursor;
end 

 2、游标的属性

%found   布尔属性，当最近一次读记录时成功返回，则为true

%notfound 布尔属性，于%found相反

%isopen 布尔属性，当游标已打开时返回true

%rowcount 数字型属性，返回已从游标中读取的记录数

 3、游标的for循环

        PL/SQL语言提供了游标for循环语句，自动执行游标的open、fetch、close语句和循环语句的功能；当进入for循环时，游标for循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标for循环语句就会自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标、

格式：

for index_variable in cursor_name[value[,value]] loop
游标数据处理代码
end loop;

 其中，index_variable 为游标for循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句

返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标for循环语句中索引变量来访问这些列数据

例如：

for c in emp_sal_cursor loop
dbms_output.put_line(c.salary);
end loop;

 七、异常处理

1、结构

exception
    when ... then...
    when ... then...
          ...
    when others then ...
end;
    

 2、非预定义异常（预定义的异常可以直接通过oracle已经定义好的异常名，进行直接处理）

declare
   异常名  exception;
   pragma exception_init(异常名，异常代号);//异常代号为所执行的语句报出的代号
begin
   执行的语句
exception
when 异常名 then...
end;

 3、自定义异常

declare
异常名 exception;//声明一个异常变量
声明的其它变量    ；    //声明的其它变量
begin
 //执行的语句,举例如下
select  列名 into 声明的其它变量 from 表明 where ...
if 其它变量的表达式 then
raise 异常名；//抛出异常
end if;
exception
  when ... then ...()
end;

 八、存储函数和存储过程

1、概念

oracle提供可以把PL/SQL程序存储在数据库中，并可以在任何地方运行它，这样就叫做存储过程或函数。

过程和函数的唯一区别是：函数总是向调用者返回数据，而过程则不返回数据。

2、创建函数

create or replace function fun_name(参数列表){
return 返回值类型
is
            --函数使用过程中，需要声明的变量，记录类型、cursor(游标)

begin 
            --函数的执行体
exception
             --处理函数执行过程中的异常

end;
}

例如：

//返回helloworld
create or replace function hello_world  //hello_world为函数名
return  varchar2
is
     
begin
     return 'helloworld';
end;

  3、函数调用

//第一种方式
select 函数名 from dual
//第二种方式
declare
     //声明的变量
begin
     执行体

exception
  异常处理部分
end;

 例如：

调用hello_world方法

//由于不需要声明变量，可以将声明变量的部分省略
begin
  dbms_output.put_line(hello_world);
end;

 4、in、out或in out标记修饰函数的参数列表，参数之间用逗号隔开

（1）in参数标记表示传递给函数的值，在该函数执行过程中不改变，也就是说在函数的执行过程中，引用参数对应的变量时，它的值不改变，为传入的值 。

（2）out参数标记表示一个值在函数中进行计算并通过该参数传递给调用语句，因为函数只有一个返回值，但是PL/SQL通过out标记可以实现多个返回值

（3）in out参数标记表示传递给函数的值可以改变并且传递给调用语句

（4）若省略，表示默认为in参数标记

5、创建存储过程

--虽然存储过程没有返回值，但是可以通过out标记实现返回
create or replace procedure 存储过程名（参数列表）

is
      --声明变量、记录类型、游标
begin
      --执行体
exception
       --异常处理部分
end;

 九、触发器

 1、概念

（1）触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL代码块

（2）触发器在数据库中以独立的对象存储，它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐士运行。并且触发器不能接收参数，所以运行触发器就叫做触发或点火（ firing）。

（3）Oracle事件指的是对数据库表进行的insert、update及delete操作或对视图进行类似的操作

2、触发器的组成

（1）触发事件：即在何种情况下触发trigger,例如增删改查的操作

（2）触发事件：即该trigger是在触发事件之前（before）还是之后（after）触发,也就是触发事件和该trigger的操作顺序

（3）触发器本身：即该trigger被触发之后的目的和意图，也就是触发器本身要做的事情，例如：PL/SQL块

（4）触发频率：说明触发器内定义的动作被执行的次数。即语句级（statement）触发器和行级(row)触发器

         ①语句级触发器：是指当某触发事件发生时，该触发器只执行一次

         ②行级触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

 3、创建触发器

create or replace trigger 触发器名
after(或before)   --在触发事件之前用before，之后用after
update on employees --触发事件
for each row --有此语句表明是行触发器，没有表示语句级触发器
begin
   dbms_output.put_line('helloworld'); --触发器本身
end;

 4、使用 :new和:old修饰符

create or replace trigger 触发器名
after(或before)   --在触发事件之前用before，之后用after
update on employees --触发事件
for each row --有此语句表明是行触发器，没有表示语句级触发器
begin
   dbms_output.put_line(:old.列名||:new.列名); --触发器本身，表示这个列名的执行操作之后的旧值
和新值
end;