Oracle PL/SQL

( 一 )PL/SQL

    PL/SQL 是过程语言 (Procedural Language) 与结构化查询语言 (SQL) 结合而成的编程语言

    PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分

    [DECLARE

    declarations]

    BEGIN

        executable statements

    [EXCEPTION

    handlers]

END;

• 常用命令

 

set serviceout on/off;-- 显示设置

set auto on/off;-- 提交自动设置

 

1. 变量和常量

    在声明部分声明，使用前必须先声明；

    声明时必须指定类型；

       给变量赋值有两种方式： [:=] 或 [select into]

       定义常量： [ 常量变量名 ] CONSTANT [ 数据类型 ] := [] ；

      

       1.1 数字数据类型

           常用的 NUMBER （ decimal 、 float/integer/real ）

       1.2 字符数据类型

           char(<2000)/varchar2(<4000)/long(<2G)/raw(<2000)/long raw(<2G)

       1.3 用于存储大文本、图像、视频等非结构化数据

           LOB （ <4G ）

       1.4 属性类型

•    %TYPE 表示引用或数据库列的数据类型

 

•    record 定义记录类型变量，将多个基本数据类型捆绑在一起

 

              SQL> declare

              2      type rectype is record (

              3          dnnm scott.dept.dname%type ,

              4          locc scott.dept.loc%type

              5      );

              6      rec rectype;

              7  begin

              8       select dname,loc into rec from dept where deptno = &no;

              9      dbms_output.put_line(rec.dnnm);

             10      dbms_output.put_line(rec.locc);

             11  end;

             12  /

 

you

•    %ROWTYPE 表示表中一行的记录类型

 

SQL> declare

  2      arow emp%rowtype;

  3  begin

  4       select * into arow from emp where emp.empno = &no;

  5      dbms_output.put_line(arow.empno||arow.ename);

  6  end;

   7  /

        1.5 定义表类型变量

           表类型变量和数据表是有区别的，这里的表类型类似数组，定义表类型变量的语句如下：

           ---------------------------------------------------------

           type [myTableType] is table of [varchar2(4)] index by binary_integer;

           mytable myTableType;

           ---------------------------------------------------------

            1.5.1 一维表

           SQL> declare

             2      type tabletype is table of varchar2(8) index by binary_integer ;

             3 

             4      table1 tabletype;

             5  begin

             6       table1(1):=' 成都 ';

             7       table1(2):=' 长沙 ';

             8       table1(3):=' 武汉 ';

             9       table1(4):=' 西安 ';

            10       table1(5):=' 北京 ';

            11       dbms_output.put_line(table1.count);

            12       dbms_output.put_line(table1.first);

            13       dbms_output.put_line(table1.last);

            14       dbms_output.put_line(table1.prior(2));

            15       dbms_output.put_line(table1.next(2));

            16  end;

            17   /

           * 可以对表使用 count,first,last,prior,next ，如上。都会返回数字。

             

           1.5.2 多维表

declare
       type tabletype is table of dept%rowtype index by binary_integer ;
   
       table1 tabletype;
      num number := 1;
begin
     while num > 0 loop
         num := &n;
         select * into table1(num) from dept where dept.deptno = num;
         dbms_output.put_line(table1(num).deptno||table1(num).dname);
     end loop ;
end ;

 

 

2. 控制结构

    2.1 条件控制

       if 语句根据条件执行一系列语句，有三种形式： if-then 、 if-then-else 和 if-then-elseif

      

       SQL> declare

         2     sall number;

         3  begin

         4     select nvl(sal,0)+nvl(comm,0) into sall from emp where empno = &no;

         5     if sall < 1000 then

         6         dbms_output.put_line(' 那么低的工资，饿死你 ');

         7     end if;

         8     if sall >= 1000 and sall < 1500 then

         9         dbms_output.put_line(' 温饱，继续努力 ');

        10     end if;

        11     if sall >= 1500 then

        12         dbms_output.put_line(' 有钱人儿 ');

        13     end if;

        14  end;

        15  /

 

温饱，继续努力

      

    2.2 循环控制

       PL/SQL 有 4 中循环结构

       2.2.1 while..loop..end loop

           SQL> declare

             2     i integer := 0;

             3    begin

             4       while i < 5 loop -- 循环条件

             5         insert into dept(deptno,dname) values (i,'you');

             6         i:=i+1;

             7       end loop;        -- 结束循环

             8  end;

             9  /

      

2.2.2 loop..exit..end loop

    SQL> declare

      2      i integer:=21;

      3  begin

      4      loop

      5         insert into dept(deptno,dname) values(i,'java');

      6         i:=i-2;

      7         if i < 15 then

      8            exit;

      9         end if;

10       end loop;

     11  end;

     12  /

2.2.3 loop..exit when..end loop

           SQL> declare

             2      i integer:=9;

             3  begin

             4      loop

             5          insert into dept(deptno,dname) values(i,'ermao');

             6          dbms_output.put_line(i);

             7          i:=i-1;

             8          exit when i=5;

             9      end loop;

            10  end;

            11  /

       2.2.4 for..in..loop..end

           SQL> declare

             2      i integer:=1;

             3  begin

             4      for i in 1..9 loop – 包括 1 和 9

             5      insert into dept(deptno,dname) values(i,'ermao');

             6      end loop;

             7  end;

             8  /

 

3. 游标

    游标式从数据表中提取出来的数据以临时表的形式存放在内存中，在游标中有一个数据指针，在初始状态下指向首记录，利用 fetch 语句可以移动指针，从而对游标中的数据进行各种操作，然后将结果写回到数据表中。

•    游标的分类：隐式游标、显示游标， REF 游标

 

•    游标的属性有：

 

           –        %FOUND – SQL 语句影响了一行或多行时为 TRUE

           –        %NOTFOUND – SQL 语句没有影响任何行时为 TRUE

           –        %ROWCOUNT – SQL 语句影响的行数

           –        %ISOPEN  - 游标是否打开，始终为 FALSE

 

    3.1 隐式游标

1) 在 PL/SQL 中使用 DML 语句时自动创建隐式游标

2) 隐式游标自动声明、打开和关闭，其名为 SQL

3) 通过检查隐式游标的属性可以获得最近执行的 DML 语句的信息

 

只有在 DML 语句影响一行，或多行时，才返回 True

 

SQL> set serveroutput on;

SQL> declare

  2 

  3  begin

  4      delete from dept where deptno = 2;

  5      if sql%found then

  6          dbms_output.put_line(' 成功删除 ');

  7      else if sql%notfound then

  8         dbms_output.put_line(' 查无此条 ');

  9      else

  10         dbms_output.put_line(' 异常 ');

  11      end if;

  12      end if;

  13  end;

  14  /

 

成功删除

    3.2 显式游标

显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行

•    操作过程：打开游标—— > 提取行—— > 给变量—— > 关闭游标

 

•    定义方法： cousor [ 游标名 ] is select 语句 ; ==>cousor 是定义游标的关键词

 

•    打开游标 open [ 游标名 ]

 

•    提取游标中的数据： fetch [ 游标名 ] into [ 变量名 1, 变量名 2,... 或 记录型变量名 record]

 

•    关闭游标： close [ 游标名 ]

 

SQL> declare

  2      cursor cur_getdname

  3      is select dept.dname from dept;

  4 

  5      deptname scott.dept.dname%type;

  6  begin

  7      open cur_getdname;

  8      loop

  9          fetch cur_getdname into deptname;

10          if cur_getdname%notfound then

11          exit;                                               

12          end if;

13          dbms_output.put_line(deptname);

14      end loop;

15      close cur_getdname;

16  end;

17  /

 

过程(procedure)

1.过程的语法结构：

create or replace procedure [过程名] as

        ...声明语句段;

begin

        ...执行语句段;

exception

        ... 异常处理;

end;

例如：

 

create procedure pro_sel
    as
        cursor cur_sel is select * from emp;
        arow scott.emp%rowtype;
begin
    open cur_sel;
    loop 
        fetch cur_sel into arow;
        exit when cur_sel%notfound ;
        dbms_output.put_line(arow.ename);
    end loop;
    close cur_sel;
end;

 
begin 
   pro_sel;
end;