Oracle中的pl/sql

什么是pl/sql

pl/sql是一种编程语言，是oracle对sql语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。

PL/SQL的作用

使用PL/SQL可以编写具有很多高级功能的程序，虽然通过多个SQL语句可能也能实现同样的功能，但是相比而言，PL/SQL具有更为明显的一些优点：

⒈能够使一组SQL语句的功能更具模块化程序特点；

⒉采用了过程性语言控制程序的结构；

⒊可以对程序中的错误进行自动处理，使程序能够在遇到错误的时候不会被中断；

⒋具有较好的可移植性，可以移植到另一个Oracle数据库中；

⒌集成在数据库中，调用更快；

⒍减少了网络的交互，有助于提高程序性能。

PL/SQL程序的基本结构

PL/SQL块由四个基本部分组成：声明、执行体开始、异常处理、执行体结束。

下面是四个部分的基本结构：

DECLARE—— 可选部分

变量、常量、游标、用户定义异常的声明

……

BEGIN—— 必要部分

SQL语句和PL/SQL语句构成的执行程序

……

EXCEPTION—— 可选部分

程序出现异常时，捕捉异常并处理异常

……

END；——必须部分

在数据库执行 PL/SQL 程序时， PL/SQL 语句和 SQL 语句是分别进行解析和执行的。 PL/SQL 块被数据库内部的 PL/SQL 引擎提取，将 SQL 语句取出送给 Oracle 的 SQL 引擎处理，两种语句分别在两种引擎中分析处理，在数据库内部完成数据交互、处理过程。

变量：

1.普通变量       * v_i varchar2(20);

2.列变量         * v_cos student.stu_name%type;

3.记录(行)变量     * v_row student%rowtype;

declare

      v_stut_student%rowtype;

   begin

     select *  into v_stu fromt_studentwhere sid = 1207;

     dbms_output.put_line(v_stu.sname);

   end;

 

4.全局变量    

VARIABLE 变量名  数据类型;

不能在Sql window中写要在Command window中或者黑窗口展示

用法:VAR[IABLE]  [ <variable> [ NUMBER |CHAR | CHAR (n [CHAR|BYTE]) |

                    VARCHAR2 (n [CHAR|BYTE]) |NCHAR | NCHAR (n) |

                    NVARCHAR2 (n) | CLOB |NCLOB | REFCURSOR |

                    BINARY_FLOAT |BINARY_DOUBLE ] ]

 variable v_all varchar2;

 begin

   :v_all:=:v_all||'~abc';

   dbms_output.put_line(:v_all);

 end; 

5.表(一位数组)变量

  type 类型名字 is table of类型 index bybinary_integer;

  引用名字 类型名字; 

declare

 type t_table is table ofvarchar2(20)index by binary_integer;

（typet_abcis table oft_student.sid%typeindex by binary_integer;)

 v_table t_table;

begin

  v_table(1):='abc';

  v_table(2):='def';

  v_table(3):='lll';

  v_table(4):='kkk';

  dbms_output.put_line(v_table(2));

end ;

条件控制

:单分支结构、双分支、多分支结构

单分支结构

IF  条件  THEN

     --条件结构体

END IF;

说明：

①  用IF 关键字开始，END IF关键字结束，注意END IF后面有一个分号。

②  条件部分可以不使用括号，但是必须以关键字THEN来标识条件结束，如果条件成

立，则执行THEN后到对应END IF之间的语句块内容。如果条件不成立，则不执行

条件语句块的内容。

③  java结构用一对大括号来包含条件结构体的内容。PL/SQL中关键字THEN到ENDIF之

间的内容是条件结构体内容。

④  条件可以使用关系运算符合逻辑运算符。

双分支

 IF  条件  THEN

     --条件成立结构体

ELSE

     --条件不成立结构体

END IF;

多分支结构

IF  条件1 THEN

     --条件1成立结构体

ELSIF 条件2 THEN

     --条件2成立结构体

ELSE

     --以上条件都不成立结构体

END IF;

或：

CASE [selector]

WHEN 表达式1 THEN语句序列1；

WHEN 表达式2 THEN语句序列2；

WHEN 表达式3 THEN语句序列3；

……

[ELSE 语句序列N]；

END CASE;

或

    CASE

WHEN 判断1 THEN语句序列1；

WHEN 判断2 THEN语句序列2；

WHEN 判断3 THEN语句序列3；

……

[ELSE 语句序列N]；

END CASE;

循环结构

PL/SQL提供了丰富的循环结构来重复执行一些列语句。Oracle提供的循环类型有：

1. 无条件循环LOOP-ENDLOOP语句

 LOOP

    --循环体

    （exit when ***；       if  *** then exid；）

        END LOOP;

2.  WHILE循环语句

 WHILE 条件  LOOP

    --循环体

        END LOOP;

3. FOR循环语句

      FOR循环变量  IN [REVERSE] 循环下限..循环上限  LOOP LOOP

--循环体

END LOOP；

 

在上面的三类循环中EXIT用来强制结束循环，相当于java循环中的break。

 

例：使用loop：计算1到100的和；   

    declare

     v_i binary_integer := 1;

     v_sum binary_integer := 0;

     begin

        loop

          v_sum := v_sum + v_i;

          v_i := v_i + 1;

          if v_i = 101 then

            exit;

         end if;

         end loop;

         dbms_output.put_line(v_sum);

      end;

 

使用while   

     declare

     v_i binary_integer := 1;

     v_sum binary_integer := 0;

     begin

       while v_i <= 50 loop

          v_sum := v_sum + v_i;

          v_i := v_i + 1;

       

         end loop;

          dbms_output.put_line(v_sum);

      end;

使用for       

     declare

     v_i binary_integer := 1;

     v_sum binary_integer := 0;

     begin

       for v_i in 1..100loop

          v_sum := v_sum + v_i;

         end loop;

          dbms_output.put_line(v_sum);

      end ;

顺序结构

在程序顺序结构中有两个特殊的语句。GOTO和NULL

  GOTO语句

    GOTO语句将无条件的跳转到标签指定的语句去执行。标签是用双尖括号括起来的标示

符，在PL/SQL块中必须具有唯一的名称，标签后必须紧跟可执行语句或者PL/SQL块。

GOTO不能跳转到IF语句、CASE语句、LOOP语句、或者子块中。 

  NULL语句

    NULL语句什么都不做，只是将控制权转到下一行语句。NULL语句是可执行语句。NULL

语句在IF 或者其他语句语法要求至少需要一条可执行语句，但又不需要具体操作的地

方。比如GOTO的目标地方不需要执行任何语句时。

declare

v_i number := 10;  

begin

  if v_i > 5 then

    goto f_label;

    else

      goto s_label;

      end if;

      <<f_label>>

      dbms_output.put_line('哈哈');

      <<s_label>>

      null

执行ddl语句

     begin

       execute immediate 'createtable T(id number)';

       end;

PL/SQL的异常处理

  BEGIN

         --可执行部分

  EXCEPTION    --  异常处理开始

     WHEN 异常名1 THEN

        --对应异常处理

      WHEN异常名2 THEN

        --对应异常处理

                  ……

     WHEN OTHERS THEN

        --其他异常处理

  END;

 

    declare

     v_i number;

     begin

       select sid into v_i fromt_studentwhere sename ='Orange';

     exception

         whenno_data_found then

              dbms_output.put_line('哈哈');

     end;

     declare

      v_inumber;

     begin

       v_i := 10/0;

     exception

         whenno_data_found then

              dbms_output.put_line('哈哈');

         when others then

             dbms_output.put_line('ok');

     end ;

注意：

Set serveroutputon(显示输出)

 

/ 可以执行前一次的sql语句

 

begin

  dbms_output.put_line('hello world!');

end;

 

dbms_output.put('hello world!');

这句后面必须有dbms_output.put_line ( 'hello world!' ); 才会输出put中的内容（必须有put_line）