oracle pl/sql编程基础


(一)pl/sql块优点:
         (1)提高运行效率
         (2)提高模块化的程序设计功能
         (3)允许标示符定义
         (4)具有过程语言控制结构
         (5)良好的兼容性(内置程序包)
         (6)处理运行错误
2.SQL语言包括:
         (1)数据查询语句(select)
         (2)DML数据操纵语句(insert,update,delete)
         (3)事务控制语言(commit,rollback,savepoint)
         (4)数据定义语言(create,alter,drop)
         (5)DCL数据控制语言(grant,revoke)等
       注意:当编写PL/SQL应用程序时,只能直接嵌入select语句,dml语句和事务控制语句
3.PL/SQL块语法:
       declare
       变量,常量,游标,例解
       begin
         执行PL/SQL,SQL语句
         exception
         end;
4.PL/SQL块分为:无名块,匿名块,有名块(包含在存储过程或者函数中)
(1)例如:有名的存储过程块无参数的存储过程
    create or replace procedure proc_name
    as 
    begin
      dbms_output.put_line(systimestamp);
      end;

    declare
    begin 
      proc_name;
      end;

  结果为:27-12月-13 06.22.00.484000000 下午 +08:00
(2)无名块
    declare 
    v_ename varchar2(20);
    begin
      select ename into v_ename from scott.emp where empno=&empno;
      dbms_output.put_line('员工姓名:'||v_ename);
      exception
        when no_data_found then 
          dbms_output.put_line('请输入正确的员工号!') ;
    end;
   输入7369,结果为:员工姓名:SMITH

(二) 标量类型(varchar2(),char(),number(p,s),date,timestamp,boolean)
(1)计算员工的工资所得税(普通版)
declare 
v_ename varchar2(20);
v_sal number(6,2);
c_tax_rate constant number(3,2) :=0.03;
v_tax_sal number(6,2);
begin
  select ename,sal into v_ename,v_sal from scott.emp where empno=&empno;
  v_tax_sal :=v_sal * c_tax_rate;
  dbms_output.put_line('员工姓名:'||v_ename);
  dbms_output.put_line('员工工资:'||v_sal);
  dbms_output.put_line('所得税:'||v_tax_sal);
  end;
  输入7369,结果为:员工姓名:SMITH,员工工资:800,所得税:24

(2)使用 %type 属性计算员工的工资所得税(%type版本)
declare 
v_ename scott.emp.ename%type;     --记录员工姓名
v_sal number(6,2);     --记录员工工资
c_tax_rate constant number(3,2):=0.03;     --设置所得税税率
v_tax_sal number(6,2);     --计算后的所得税  
begin
  /*******************************
  输入员工编号通过计算输出员工所得税
  ***********************************/
  select ename,sal into v_ename,v_sal from scott.emp where empno=&empno;
  v_tax_sal:=v_sal * c_tax_rate;
  dbms_output.put_line('员工姓名:'||v_ename);
  dbms_output.put_line('员工工资:'||v_sal);
  dbms_output.put_line('所得税:'||v_tax_sal);
  end;
输入7369,结果为:员工姓名:SMITH,员工工资:800,所得税:24

(3)record的使用
declare
type r_name is record(
     v_dname scott.dept.dname%type,
     v_loc scott.dept.loc%type
);
res r_name;
begin
  select dname,loc into res.v_dname,res.v_loc 
         from scott.dept where scott.dept.deptno=10;
  dbms_output.put_line(res.v_dname||res.v_loc);
  end;
  结果为:ACCOUNTINGNEW YORK

(三)PL/SQL 控制结构(PL/SQL不能嵌入sql语句)
(1)条件分支语句(语法)
/*
if 条件 then 
  begin
    处理语句
    end;
  elsif
  begin
    处理语句
    end;
end if;
*/
例题:为工资小于2000的员工增加工资200元
declare 
    v_sal number(6,2);
    begin
      select sal into v_sal from scott.emp where ename=trim('&ename');
      if v_sal < 2000 then
        update scott.emp set sal=v_sal + 200 where ename = trim('&ename');
        end if;
      end;

      select * from scott.emp;
(2)case语句(单一使用)
/*语法:
      case selector         --sekector为条件选择符
        when 表达式 then 处理语句
        when 表达式 then 处理语句
        [else sequence_of_statementN+1;]
        end case;
*/

例题:在case语句中使用单一条件,更新相应部门的员工的补贴(根据deptno)
declare
v_deptno scott.emp.deptno%type;
begin 
  v_deptno := &deptno;
  case v_deptno
    when 10 then 
      update scott.emp set comm=100 where deptno=v_deptno;
    when 20 then
      update scott.emp set comm =80 where deptno=v_deptno;
    when 30 then
      update scott.emp set comm =60 where deptno=v_deptno;
      else
        dbms_output.put_line('不存在该部门');
  end case;
end;

select * from scott.dept;

  case语句中使用多种条件
  /*
  语法:
  case 条件选择符
    when 条件 then 处理语句
    when 条件 then 处理语句
      ......
    when 条件 then 处理语句
    [else sequence_of_statementN+1;]
  end case;
  */

  例题:依据不同的工资金额来更新员工的补贴(改变comm值)
  declare
  v_sal scott.emp .sal%type;
  v_ename scott.emp.ename%type;
  begin
    select ename,sal into v_ename,v_sal from scott.emp where empno=&empno;
    case
      when v_sal < 2000 then 
        update scott.emp set comm=100 where ename=v_ename;
      when v_sal < 3000 then 
        update scott.emp set comm=80 where ename=v_ename;
      when v_sal < 4000 then 
        update scott.emp set comm=50 where ename=v_ename;  
        end case; 
  end;
  select * from scott.emp;

(四)循环语句
(1)loop循环(至少会执行一次)
/*
  语法:loop  statement1
           exit when 表达式
       end loop;
*/

例题:基本循环(1-9)
declare
i int :=1;
begin
  loop
    dbms_output.put_line(i);
    i :=i+1;
    exit when i=10;
  end loop;
end;

(2)while循环
/*
  语法:
   while 表达式 loop
     处理语句
   end loop;
*/

例题:while循环(1-9)
declare 
i int :=1;
begin
  while i<10 loop
    dbms_output.put_line(i);
    i:=i+1;
    end loop;
end;

(3)for循环
/*
  语法:
    for 循环控制变量 in [reverse] lower_bound..upper_bound loop
       处理语句
    end loop;  
*/ 

例题:for循环(1-10)
declare 
i int :=1;
begin
  for i in 1..10 loop
    dbms_output.put_line(i);
    end loop;
end;   

(五)异常处理
  1.预定义异常
    (1)case_not_found:对应ORA-06529,case语句中没有分支
    (2)cursor_already_open:对应ORA-06511,游标已经打开,不能再打开
    (3)invalid_number:对应ORA-01722,数字转换异常
    (4)too_many_rows:对应ORA-01422,当执行select into字句时,如果返回超过一行
                      就出发该异常
    (5)zero_divide:对应ORA-01476,除数为0时
    (6)no_data_found:对应ORA-01403,执行select into未返回行,或引用索引表未初始化元素

 2.非预定义异常
  3.自定义异常  
  /*语法:
  exception
    when 异常 then
      ...
    when 异常 then
      ...
    when other then
  */

  (1)例题:处理预定义异常
  declare 
  v_ename scott.emp.ename%type;
  begin
    select ename into v_ename from scott.emp where empno=&empno;
    dbms_output.put_line(v_ename);
    exception
      when no_data_found then
         dbms_output.put_line('员工编号不正确,请输入正确的员工编号');
    end;
    输入一个scott.emp表中没有的编号2563,触发异常
    结果为:员工编号不正确,请输入正确的员工编号
   (2):处理自定义异常
   declare
   e_integerity exception ;--定义非预定义异常
   e_no_employee exception; --定义自定义异常
   pragma exception_init(e_integerity,-2291);--关联非预定义异常
   begin
     update scott.emp set deptno=40 where empno=-1;
     if SQL%notfound then 
       raise e_no_employee; --显示触发自定义异常
      end if;
      exception
        when e_integerity then
          dbms_output.put_line('该部门不存在');
          when e_no_employee then
            dbms_output.put_line('该员工不存在');
   end;
   结果为:该员工不存在

(六)游标