首页 > WEB开发 > 数据库 > 常见Oracle数据库对象及PL/SQL编程
2014
11-17

常见Oracle数据库对象及PL/SQL编程

三、PL/SQL编程

PL/SQL(Procedure Language/SQL),它是Oracle对SQL语言的过程化扩展,即在SQL中增加了过程处理语句(如分支、循环),使得SQL语言也可以进行过程处理

试着想想如何完成这样的需求:按职工的职称涨工资,总裁涨1000元,经理涨800元,其他人员涨400元。很明显,单纯的SQL语句实现不了这样的需求,这时可以:

  • 使用Java + SQL,用Java完成判断部分,SQL完成数据操作部分
  • 使用PL/SQL

1、HelloWorld

04. 常见Oracle数据库对象及PLSQL编程3047

上面是在命令行下完成的Helloworld程序,如果回车了就不能修改上一行,对于pl/sql编程很不方便,因此后文的操作都是在Oracle SQL Developer中。

1)如果需要看到输出信息,需要手动开启:set serveroutput on

2)pl/sql程序的结构如下图所示,分为三个部分,declare和exception部分在pl/sql中不是必须的:

04. 常见Oracle数据库对象及PLSQL编程3235

3)变量的声明

04. 常见Oracle数据库对象及PLSQL编程3245

4)引用型变量

Demo:查询并打印7839的姓名和薪水

declare
  --定义变量保存姓名和薪水
  pename emp.ename%type;
  psal emp.sal%type;
begin
  select ename,sal into pename,psal from emp where empno=7839;
  --打印
  dbms_output.put_line(pename||'的薪水是:'||psal);
end;
/

5)记录型变量

declare
  --定义记录型变量,代表一行
  emp_rec emp%rowtype;
begin
  --查询一行记录并赋值给emp_rec
  select * into emp_rec from emp where empno=7839;
  --打印
  dbms_output.put_line(emp_rec.ename||'的薪水是:'||emp_rec.sal);
end;
/

Demo:查询并打印7839的姓名和薪水

2、if语句

基本语法如下图(注意多分支的关键词是elsif,不是elseif):

04. 常见Oracle数据库对象及PLSQL编程3753

Demo:判断用户从键盘输入的数字

  • 从键盘输入:accept num prompt ‘请输入一个数字’;
  • 得到键盘输入的值:pnum number := #
--num:变量地址(有点像C语言里scanf的参数)
accept num prompt '请输入一个数字';

declare
  --定义变量保存输入的数字
  pnum number := # --隐式转换
begin
  if pnum=0 then dbms_output.put_line('您输入的是0');
    elsif pnum = 1 then dbms_output.put_line('您输入的是1');
                    dbms_output.put_line('1是个正数');
    else dbms_output.put_line('其他数字');
  end if;
end;
/

3、循环语句

语法(重点掌握下面这种就够用了):

loop
  exit when 条件;
  语句序列1;
end loop;

Demo:打印1~10

declare
  pnum number := 1;
begin
  loop
    --退出条件
    exit when pnum > 10;
    --打印
    dbms_output.put_line(pnum);
    --修改循环变量
    pnum := pnum + 1;
  end loop;
end;
/

4、光标(Cursor)

光标用于存储一个查询返回的多行数据(等同JDBC编程中的ResultSet),声明光标的语法为:cursor cursorName [(参数名 类型[,参数名 类型...])] is select语句

1)光标的相关操作:

  • 声明光标c1:cursor c1 is select ename from emp;
  • 打开光标执行查询:open c1
  • 取一行到变量中:fetch c1 into pename;
  • 关闭光标释放资源:close c1;

2)带参数的光标

  • 声明带参数的光标:cursor c2 (jobcvarchar2) is select ename,sal from emp where job=jobc;
  • 打开光标执行查询:open c2(‘CLERK’);

后面的操作同上。

Demo(综合):完成开始提出的那个需求:按职工的职称涨工资,总裁涨1000元,经理涨800元,其他人员涨400元。

declare
  cursor cemp is select empno,ejob from emp;
  --定义变量存放empno、ejob
  pempno emp.empno%type;
  pjob emp.ejob%type;
begin
  --打开光标进行遍历
  open cemp;
  --对查询的结果进行遍历
  loop
    --取一个员工涨工资
    fetch cemp into pempno,pjob;
    exit when cemp%notfound;
    --根据不同的职称涨工资
    if pjob='PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
      elsif pjob='MANAGER' then update emp set sal=sal+800 where empno=pempno;
      else update emp set sal=sal+400 where empno=pempno;
    end if;
  end loop;
  close cemp;
  commit;
  dbms_output.put_line('完成了');
end;
/

5、Oracle的异常处理

1)系统定义异常:如no_data_found、too_many_rows(select…into语句匹配多个行)、zero_divide、value_eroor(算术或转换错误)、timeout_on_resource(等待资源时超时)

Demo:演示1/0的异常

declare
  pnum number;
begin
  pnum := 1/0;
exception
  when zero_divide then dbms_output.put_line('除数为0');
  when value_error then dbms_output.put_line('算术或者转换错误');
  when others then dbms_output.put('其他例外');
end;
/

2)猿类自定义异常

Demo:查询50号部门的员工(其实数据库中没有这个部门),没有找到员工则抛出no_emp_found异常。

declare
  cursor cemp is select ename from emp where deptno=50;
  pename emp.ename%type;

  --自定义异常
  no_emp_found exception;
begin
  open cemp;
    fetch cemp into pename;
    if cemp%notfound then
      --抛出异常
      raise no_emp_found;
    end if;
  close cemp;
exception
  --捕获异常
  when no_emp_found then dbms_output.put_line('没有找到员工');
  when others then dbms_output.put_line('其他例外');
end;
/

关于上面程序的一点说明:

  • 在declare部分中定义异常:no_emp_found exception;
  • 手动抛出异常:raise no_emp_found;
  • 在exception部分捕获处理异常:when no_emp_found then …

留下一个回复

你的email不会被公开。