PL/SQL

PL/SQL（ProcedureLanguage/SQL）

定义：PLSQL是Oracle对sql语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。

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

PL/SQL块的结构如下：

DECLARE 

/* 声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数 */

BEGIN

    /*  执行部分:  过程及SQL 语句  , 即程序的主要部分  */

EXCEPTION

  /* 执行异常部分: 错误处理  */

END;

其中执行部分是必须的。

 

例：输出helloworld

setserveroutput on    默认是不显示结果的，要设置serveroutput后才能显示。

 declare

    --变量常量的说明

 begin

    --程序体

    dbms_output.put_line('Hello World');    调用dbms_output程序包的put_line方法进行打印

 end;

 /      执行上一个PLSQL

 

变量的数据类型的定义

变量类型（char,varchar2, date, number, boolean, long) 与创建数据库表的类型一样

例：varl  char(15);    

         married boolean:=true;    

         psal number(7,2);   基本变量类型

     my_name emp.ename%type;     

         emp_rec  emp%rowtype          

 

例1：查询并打印7839的姓名和薪水（引用型变量）

set serveroutput on

declare

  pename  emp.ename%type;   引用型变量

  psal  emp.sal%type;    引用型变量

begin

  --7839的姓名和薪水

  select ename,sal into pename,psalfrom emp where empno=7839;

  dbms_output.put_line(pename||'的薪水是'||psal);打印

end;

/

例2：查询并打印7839的姓名和薪水（记录型变量）

set serveroutput on

declare

  emp_rec emp%rowtype;

begin

  select * into emp_rec from emp whereempno=7839;

  dbms_output.put_line(emp_rec.ename ||'的薪水是'||emp_rec.sal);

end;

/

 

IF语句

三种形式

1、IF   条件  THEN 语句1;

        语句2;

            END IF;

2、 IF  条件  THEN  语句序列1；  

            ELSE   语句序列 2；

           END  IF；

3、 IF   条件  THEN 语句;

   ELSIF  语句  THEN  语句;

  ELSE   语句;

 END  IF;

 

例：判断用户输入的数字

set serveroutput on

accept num prompt '请输入一个数字';

declare

  --

  --

  pnum number  := & num;

begin

  if pnum=0 then dbms_output.put_line('您输入的是0');

    elsif pnum=1 then dbms_output.put_line('您输入的是1');

    elsif pnum=2 then dbms_output.put_line('您输入的是2');

    else dbms_output.put_line('其他数字');

  end if;

end;

/

 

循环语句

三种形式

1、 WHILE  total <= 25000    

LOOP

total : = total + salary;

END  LOOP;

 

2、Loop

EXIT [when  条件];

……

End loop

 

3、FOR  I   IN   1 . . 3  

LOOP

语句序列 ;

END  LOOP ;

 

光标(Cursor)

语法：CURSOR 光标名  [ (参数名  数据类型[,参数名 数据类型]...)]

      IS SELECT   语句;

光标的属性:

%isopen: 是否打开

%rowcount: 行数

%notfound: 没有记录

例：cursor c1 is select ename from emp;

打开光标： open c1; (打开光标执行查询)

取一行光标的值：fetch c1 into pjob; (取一行到变量中)

关闭光标： close c1;(关闭游标释放资源)

例：使用游标查询员工姓名和工资，并打印

set serveroutput on

declare

  cursor cemp is select ename,sal from emp;

  pename emp.ename%type;

  psal  emp.sal%type;

begin

  open cemp;

  loop

    fetchcemp into pename,psal;

   exit when cemp%notfound; 

    dbms_output.put_line(pename||'的薪水是'||psal);

  end loop;

  close cemp;

end;

/

 

例：按员工的工种长工资,总裁1000元，经理长800元其，他人员长400元。

set serveroutput on

declare

  cursor cemp is select empno,empjob from emp;

  pempno emp.empno%type;

  pjob  emp.empjob%type;

begin

  open cemp;

  loop

    fetch cemp into pempno,pjob;

    exit when cemp%notfound;

    if pjob = 'PRESIDENT' then update emp setsal=sal+1000 where empno =pempno;

      elsif pjob = 'MANAGER' then update empset sal=sal+800 where empno =pempno;

      else update emp set sal=sal+400 whereempno =pempno;

    end if;

  end loop;

  close cemp;

  commit;

  dbms_output.put_line('涨工资完成');

end;

/

 

带参数的光标

例：查询某个部门的员工姓名

set serveroutput on

declare

  --带参数的光标

  cursor cemp(dno number) isselect ename from emp where deptno=dno;

  pename emp.ename%type;

begin

  open cemp(20);

  loop

    fetch cemp into pename;

    exitwhen cemp%notfound;

    dbms_output.put_line(pename);

  end loop;

  close cemp;

end;

/

 

例外（异常）

例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。

面试题：用语言表述一下java中的异常处理机制

         答：java中的异常采用父类处理机制，这里的父类不是继承关系，在A类调用B类的时候，A类就是B类的父类，在B类中没有处理的异常（处理了就trycatch了）就交给A类来处理（抛了）。如果A还不处理就再次向A的父类抛，如果所有的类都没有处理，那么就将异常交给虚拟机来处理。之后打印出了错误信息。

oracle的例外分类

         系统定义的例外

No_data_found   (没有找到数据)

Too_many_rows          (select …into语句匹配多个行)

Zero_Divide  (被零除)

Value_error    (算术或转换错误)

Timeout_on_resource      (在等待资源时发生超时)

         用户定义的例外

例：被0除例外

set serveroutput on

declare

  pnum number;

begin

  pnum := 1/0;

exception

  when   Zero_Divide   then dbms_output.put_line('1:0不能做被除数');

                        dbms_output.put_line('2:0不能做被除数');

  when Value_error   then dbms_output.put_line('算术或转换错误');

  when others then dbms_output.put_line('其他例外');

end;

/

 

 

 

例：自定义例外—查询50号部门的员工姓名(如果没有找到，抛出例外)

set serveroutput on

declare

  --自定义例外

  no_emp_found  exception; 

  cursor cemp is select ename from emp wheredeptno=50;

  pename emp.ename%type;

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;