Oracl中PL/SQL编程（10级学员 张帅鹏课堂总结）

Oracl中PL/SQL编程

概述： PL/SQL是oracle的专用语言,它对标准的SQL语言的扩展.SQL语句可以嵌套在PL/SQL语言中，并结合处理语句。举例，一般在银行系统中，都是事先写好的sql语句来处理业务，为了保证安全性，这些都是不可见的。就可以使用PL/SQL来完整我们需要的功能处理。

一、PL/SQL程序结构及组成

使用了程序块的结构组织的代码.最简单的程序块是一种被称为”匿名块”的程序块,匿名块是指不会被oracle存储并且不能够重用程序块。

PL/SQL程序通常包括3部分:Declare部分、exception部分、Begin和end部分.

Begin和end是PL/SQL的必须部分,总是begin开始end结束.

 

Declare部分包含定义变量、常量和游标等类型的代码.

 

Begin与end之间可以嵌套begin与end及exception.

 

//打开服务器的输出

 set serveroutput on;

程序结构：

declare

      说明部分    （变量说明，光标申明，例外说明〕

begin

      语句序列   （DML语句〕…

exception

      例外处理语句  

End;

变量与常量的声明：

说明变量类型：char, varchar2, date, number, boolean, long

引用变量emp.ename%type:引用emp表中ename字段的类型

emp%rowtype 记录型变量，可直接加点加字段名访问字段值

 

     变量名  类型(字符，日期，数字，布尔)[:=缺省值];

     常量名 constant 类型(字符，日期，数字，布尔) [:=缺省值];

 

变量与常量的区别：

     变量可以在任何部分进行赋值.

     而常量在declare中声明并且赋值.

注意：都是在declare中声明

作用域：

   是能够引用变量名称这样的标识符的程序块。

二、逻辑语言

a)         条件判断语句

i.          If语句

 IF   条件  THEN 语句1;

       语句2;

        end if;

ii.        If .. Else语句

IF  条件  THEN  语句序列1；  

    ESLE   语句序列 2；

    END   IF；

iii.      IF   elsif  else 语句;

IF   条件  THEN 语句;

   ELSIF  语句  THEN  语句;

  ELSE    语句;

 END  IF;

b)        循环语句

i.          While循环

WHILE  total  <= 25000  LOOP

.. .

total : = total + salary;

END  LOOP;

ii.        根据条件跳出循环

Loop

EXIT [when   条件];

……

End loop

iii.      For循环

FOR   I   IN   1 . . 3    LOOP

语句序列 ;

END    LOOP ;

三、游标(光标) cursor

概述：游标(cursor)可以被看作指向结果集(a set of rows)中一行的指针(pointer)。在oracle数据库中可以使用显示或隐式两种游标。

a)         隐式游标

在执行一个sql语句时,oracle服务器将自动创建一个隐式游标,这个游标是内存中处理该语句的工作区域，其中存储了执行SQL语句的结果.通过游标的属性可获得sql语句执行的结果以及游标状态信息。

   游标的主要属性如下：

      %found 布尔属性  如果sql语句至少影响一行则为true 否则为false

      %notfound 布尔属性与%found相反。

      %isopen 布尔属性游标是否打开  打开为true 否则为false

      %rowcount 数字属性返回受sql语句影响的行数

b)        显示游标

是在PL/SQL程序中使用包含select语句来声明的游标。如果需要处理从数据库中检索的一组记录,则可以使用显示游标.使用显示游标处理数据需要四个步骤：声明游标,打开游标，检索数据，关闭游标。

i.          声明游标

 声明游标就是通过定义游标的名称,游标的特性来声明游标,以及打开游标后就可调用查询语句,声明的语法如下：

     Cursor cursor_name[parameter[,parameter]….]

     [return return_type] is select_statement;

Parameter作为游标的输入参数,它可以让用户在打开游标式,向游标传递值；语法如下：

  Parameter_name [in] datatype[{:=|default} expression]\

举例：

  declare

   cursor emp_cursor (pno in number(4) default 7369)

   is select * from emp

       where empno=pno;

ii.        打开游标

 就是指执行声明游标时指定的查询语句。打开的方式只需使用open打开语法：

 Open cursor_name(参数);

 如果没有指定参数就采用默认值执行select语句

iii.      检索数据

检索数据就是从检索到的结果集中获取数据保存到变量中,以便变量进行处理。

使用fetch语句找出结果集中的单行,并从中提取单个值传递给主变量。

 语法如下：

   Fetch cursor_name into [variable_list[record_variable]]

变量用于存储检索的数据

iv.       关闭游标

close 游标名称

v.         游标for循环

依次读取结果集中的行,当for循环开始时,游标会自动打开(不需要使用open方法开启),每循环读取一次,系统自动读取当前数据(不需要使用fetch),当退出for循环时,游标也会自动关闭(不需要使用close方法)。

vi.       游标变量

游标变量也可以处理多行查询结果集。

 游标变量的定义包括两个步骤：

1、  定义cursor类型的指针

语法：

  Type ref_cursor_name is ref cursor[return return_type]

举例：

   Type var_cursor_name is ref cursor;

2、  定义ref cursor类型的变量

  v_rc  var_cursor_name;

综合写法如下：

   Type var_cursor_name is ref cursor;

   v_rc  var_cursor_name;

上面的综合声明的游标变量称为弱的ref cursor类型,因为它没有指明游标返回的结果,因此它可以指向任何一个具有多列的select查询结果.

 

相对于上面还有一种称为:强ref cursor类型.

声明方式如下：

   Type varcursorName is ref cursor return emp%rowtype; //指明了返回的结果

   Vcn varcursorName; //声明一个强的ref cursor类型的变量

使用游标变量与游标使用方式一样,也需要声明,打开,检索，关闭游标变量。

vii.     使用游标更新数据库

定位游标之后进行删除|修改指定的数据行更新的时候需要使用for update选项语法如下：

Cursor cursor_name is select_statement;

For update[of column[,column],[nowait]]

Of用来指定要锁定的列,如果忽略of那么表中选择的数据行都将锁定。如果被锁定行已经被锁定了,那么必须等待释放才能锁定对于这种情况我们可以使用nowait语句。

 

当使用for update语句声明游标后,可以再delete|update语句中使用where current of子句，修改|删除游标结果集中当前行对应的表中的数据。

 语法如下：

   Where { current of cursor_name|search_condition}

 

四、实例代码：

实例1：统计每年入职的员工个数

 

 

 

declare

cursor cl isselectcount(*),to_char(hiredate,'yyyy')from emp groupby to_char(hiredate,'yyyy');

emp_num number;

emp_year char(4);

emp_count number:=0;

emp_row1 varchar2(100):='total';

emp_row2 varchar2(100):='';

begin

  selectcount(*)into emp_count from emp;

  emp_row2:=emp_row2||' '||emp_count;

open cl;

loop

  fetch cl into emp_num,emp_year;

  exitwhen cl%notfound;

  emp_count:=emp_count+emp_num;

  emp_row1:=emp_row1||' '||emp_year;

  emp_row2:=emp_row2||'    '||emp_num;

  endloop;

  close cl;

  dbms_output.put_line(emp_row1);

  dbms_output.put_line(emp_row2);

  end;

实例二：为员工长工资。从最低工资调起每人长10％，但工资总额不能超过50万元,请计算长工资的人数和长工资后的工资总额，并输出输出长工资人数及工资总额。

declare

cursor cl isselect empno,sal from emp orderby sal;

salCount number;

empCount number:=0;

emp_no emp.empno%type;

emp_sal emp.sal%type;

begin

  selectsum(sal)into salCount from emp;

  open cl;

  while salCount<50000

  loop

    fetch cl into emp_no,emp_sal;

    exitwhen cl%notfound;

    update emp set sal=sal*1.1where empno=emp_no;

    salCount:=salCount+emp_sal*0.1;

    empCount:=empCount+1;

    endloop;

    close cl;

    dbms_output.put_line('长工资人数'||empCount||'长工资后总额'||salCount);

    end;

实例三：用PL/SQL语言编写一程序，实现按部门分段（6000以上、(6000，3000)、3000元以下)统计各工资段的职工人数、以及各部门的工资总额(工资总额中不包括奖金)，参考如下格式：

部门      小于3000数           3000-6000                 大于6000  工资总额

 10              2                  1                       0           8750

  20             3                  2                       0         10875   

  30             5                  0                       0           8450

declare

cursor cl isselect deptno,sum(sal)from emp groupby deptno orderby deptno asc;

emp_deptno emp.deptno%type;

emp_salsum number;

sal1 number;

sal2 number;

sal3 number;

begin

  open cl;

  loop

    fetch cl into emp_deptno,emp_salsum;

    exitwhen cl%notfound;

    selectcount(*)into sal1 from emp where deptno=emp_deptno and sal<3000;

    selectcount(*)into sal2 from emp where deptno=emp_deptno and(sal between3000and600);

    selectcount(*)into sal3 from emp where deptno=emp_deptno and sal>6000;

    dbms_output.put_line(emp_deptno||' '||sal1||' '||sal2||' '||sal3||' '||emp_salsum);

    endloop;

    end;