PL/SQL

PL/SQL

PL/SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。由

于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。除此之外，可以在

ORACLE数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点。

PL/SQL是对SQL语言存储过程语言的扩展。

PL/SQL程序由三个块组成，即声明部分，执行部分，异常处理部分。

declare 
    /*声明部分  声明PL/SQL用到的变量，类型及游标，以及局部的存储过程和函数*/
begin
    /*执行部分  过程及SQL语句*/
exception
    /*异常处理部分  错误处理*/
end;

其中执行部分是必须有的 (begin…..end;)

PL/SQL块可以分为三类

• 无名块：动态构造，只能执行一次
• 子程序：存储在数据库中的存储过程、函数及包等。在数据库建立之后可在其他程序调用它们。
• 触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

    declare 
    v_name varchar2(20) := 'zhangsan';
    v_age number := 33;
    begin 
      dbms_output.put_line(v_name||'  '||v_age);
    end;

命名：

标识符 命名规则
程序变量 v_name
程序常量 c_name
记录类型 name_record
游标变量 name_cursor
异常标识 e_name

:= 赋值号 => 关系号

||连接字符 null+<数字>=null null+<字符串>=<字符串>

boolean : true,false,null

记录类型

记录类型是把逻辑相关的数据作为一个单元存储起来，称为PL/SQL record的域(field),其作用是存放互不相同但逻辑相关的信息。

记录类型是把逻辑相关的数据作为一个单元存储起来，称为PL/SQL record的域(field),其作用是存放互不相同但逻辑相关的信息。

语法： (在 declare 中定义)

type name_record is record(

field1 type ( := exp1),

field2 type ( := exp2)

);

declare 
type test_record is record(
v_name varchar2(20),
v_sal number
);
v_emp test_record;
v_id number := 100;
begin 
  select last_name,salary into v_emp from employees where employee_id=v_id;
  dbms_output.put_line(v_emp.v_name||' id:'||v_id||'  salary:'||v_emp.v_sal);
end;

%rowtype 各行数据类型

%type 对于数据类型

使用%rowtype 特性的优点在于：

• 所引用的数据库中列的个数和数据类型可以不必知道

• 所引用的数据库中列的个数和数据类型可以实时改变

 declare 
    emp_record employees%rowtype;
    v_id employees.employee_id%type := 100;
    v_pay number;
    begin
      select * into emp_record from employees where employee_id=v_id;
      dbms_output.put_line(emp_record.last_name||' id:'||emp_record.employee_id||'  salary:'||emp_record.salary);

      v_pay:=emp_record.salary*(1+emp_record.commission_pct);
      ---- number + null = null          varchar2 + null = varchar2     ----------
      dbms_output.put_line(v_pay);
      dbms_output.put_line('voer!!!');

      v_pay:=emp_record.salary*(1+nvl(emp_record.commission_pct,0));
      dbms_output.put_line(v_pay);
      dbms_output.put_line('really voer!!!');
    end;

这里注意1+emp_record.commission_pct与1+nvl(emp_record.commission_pct,0)结果的区别

– number + null = null varchar2 + null = varchar2

–if 条件 then

– sql 和 pl/sql

– end if;

declare 
  v_id employees.employee_id%type;
  v_sal employees.salary%type;
  v_comment varchar2(50);
begin
  v_id := 104;
  select salary into v_sal from employees where employee_id=v_id;

  if v_sal<1500 then
    v_comment := 'fairly less';
    elsif v_sal<3000 then
      v_comment :='a little more';
      else
        v_comment :='good';
  end if;

  dbms_output.put_line(v_comment);
end;

–case selector when 条件1 then 结果1

– when 条件2 then 结果2

– else 结果3

–end;

declare 
v_garde char(1);
v_comment varchar2(50);
begin
  v_garde := 'A';

    v_comment:=
    case v_garde when 'A' then 'excellent'
                 when 'B' then 'good'
                 else 'so so'
    end;

    dbms_output.put_line(v_comment);
end;

–loop

–要执行语句;

–exit when 条件; 条件满足，立即直接退出循环

–end loop;

declare 
i number :=0;
begin

  loop
    exit when i =10;
    i := i+1;
    dbms_output.put_line(i);
  end loop;

end;

–whlie 条件 loop

– 要执行语句;

–end loop;

declare 
i number := 0;
begin

  while i <10 loop
    i := i+1;
    dbms_output.put_line(i);
  end loop;

end;

–for 循环计数器 in (reverse)下限 .. 上限 loop

– 要执行语句;

–end loop;

–每循环一次，循环变量自动加1。in 后面的 下限，上限必须从小到大。可以使用exit退出循环

注意：for后面的循环变量相当于新声明的变量，且可以和declare里的同名，与declare里的变量无关

declare 
i number :=0;
minnum number :=3;
maxnum number :=7;
begin 
  dbms_output.put_line(i);

  for i in minnum..maxnum loop
    dbms_output.put_line(i);
  end loop;

  dbms_output.put_line(i);
  --for后面的循环变量相当于新声明的变量，且可以和declare里的同名，与declare里的变量无关

  for ii in minnum..maxnum loop
    dbms_output.put_line(i);
  end loop;

end;

标号和GOTO

PL/SQL中的GOTO语句：无条件跳转到指定的标号去

goto label;

……

<< label >>

declare
i number := 0;
begin
  loop
    i:=i+1;
    dbms_output.put_line(i);
    if i=10 then
      goto test_label;
    end if;
  end loop;
  <<test_label>>
  dbms_output.put_line('end!!!');
end;

异常处理

异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件，程序块的异常处理预定义的错误和自定义错误，由于 PL/SQL 程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行。

三种类型的异常错误

• 预定义错误
  ORACLE 预定义的异常情况大约有 24 个。对这种异常情况的处理，无需在程序中定义，由 ORACLE 自动
  将其引发。
• 非预定义错误
  即其他标准的 ORACLE 错误。对这种异常情况的处理，需要用户在程序中定义，然后由 ORACLE 自动将
  其引发。
• 用户定义错误
  程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，
  然后显式地在程序中将其引发。

预定义错误处理：只需在 PL/SQL 块的异常处理部分，直接引用相应的异常情况名

begin ....
exception
  when no_data_found then dbms_output.put_line('没找到数据');
  when others then dbms_output.put_line('不明原因');
end;

非预定义错误处理：

1.在PL/SQL块定义部分定义异常

异常名 exception;

2.将定义好的异常与标准的ORACLE错误联系起来

pragma exception_init ( 异常名, 错误代码 );

3.在 PL/SQL 块的异常处理部分对异常情况做出相应的处理

declare
v_deptno number :=&deptno;
deptno_remaining exception ;
pragma exception_init(deptno_remaining,-2292);
begin
  delete from departments where department_id=v_deptno;
exception
  when deptno_remaining then dbms_output.put_line('不让删');
  when others then dbms_output.put_line('不明原因');
end;

用户定义错误处理

1.在 PL/SQL 块的定义部分定义异常情况：

异常名 exception;

2.raise 异常;

3.在 PL/SQL 块的异常处理部分对异常情况做出相应的处理

declare
v_empno number := &empno;
no_result exception;
begin
  update employees set salary=6666 where employee_id=v_empno;
  if sql%notfound then
    raise no_result;
  end if;
exception
  when no_result then dbms_output.put_line('没找到员工');
  when others then dbms_output.put_line('不明原因');
end;