PL/SQL基础知识总结

PL/SQL总结

PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL只有 Oracle 数据库有。 MySQL 目前不支持 PL/SQL 的。

主要内容：

1、 数据类型

2、 运算

3、 分支结构

4、 循环结构

5、 异常

6、 游标

7、 函数

8、 存储过程

9、 触发器

 

二、PL/SQL 基本格式

declare                  --当不需要声明变量、常量、游标等时，可以省略

       begin             --相当于 main 方法

              exception        --有需要处理异常时写

       end                       --PL/SQL代码块结束

 

三、数据类型

number

varchar2(10)

date

long

boolean

1)声明变量：declare

                            变量名 数据类型

                     动态获取列的数据类型： 表名.列名%type

                     动态获取列的所有数据类型：表名%rowtype

2)命名规则：必须以字母开头，可包含 _ $ ￥ 数字，不超过30个，不能是关键字

                     通常：变量：v_xxx，常量：c_xxx，游标：xxx_cursor，记录类型：xxx_record(组合类型)

3）赋值

       A．变量名 := 值

       B．用 sql 语句：select 列名1，列名2  into 变量1,变量2 from ….

Eg: declare

              v_salemp.sal%type;

              begin

                     selectsal into v_sal from emp where ename = ‘SCOTT’;

                     dbms_output.put_line(v_sal);

              end;

4）记录类型：多个变量的集合

A．定义

       type记录名称 isrecord(

              变量1 数据类型，

              变量2 数据类型

       )；                //这里就相当于定义了一种数据类型

B．声明一个记录类型：对象名记录名称

C．赋值：select 列名1,列名2 into 对象名 from …

D．使用：对象名.变量名

       Eg:declare

                     typeemp_record is record(

                            name  emp.ename%type,

                            id  emp.empno%type

);

                     v_emp_record   emp_record;

              begin

                     selectename,empno into v_emp_record from emp where empno = 7369;

                     dbms_output.put_line(v_emp_record.ename|| ‘-‘ || v_emp_record.empno);

              end;

 

四、运算：+ - * / mod() qurt()

 

五、分支结构

       if条件 then 处理语句; end if;

              if-if,if-elsif-else           注意：elsif 中没有 e;

       每个if 都要用 end if 结束，每个处理语句后面都有分号

 

       case变量名 when 值 then 表达式

                            else表达式

                            end;

 

六、循环结构

       1)loop 循环体 exit when 条件 end loop;        //类似 Java 中的 do while

       2)while 条件 loop 循环体 end loop;

       3)for 变量名 in 范围 loop 循环体 end loop;

循环之质数与水仙花数

 

七、游标

       相当于 Java 集合中的迭代器。当需要使用处理查询结果，且该结果是多行数据时，可以考虑用游标。

在创建PL/SQL语言的时候，数据库会为查询出来的结果集分配一个空间，游标就是指向这个空间的指针。

(1)在创建PL/SQL语言的时候，数据库会为查询出来的结果集分配一个空间，游标就是指向这个空间的指针

 功能：可以采用游标将查询出来的结果集进行读取，相似于java中的Iterator迭代器

 (2)游标的分类

    *显示游标：完成上述读取结果集的功能(select)

    *隐式游标：系统自动提供的游标，没有办法直接使用

     update,insert,delete语句，我们只能通过游标的属性来查看它的状态，系统完成对它的使用。

 (3)游标的属性：

    *游标名%found：布尔类型的值,代表着结果集中还有没-有数据

    *游标名%notfound：代表着结果集中是否没有数据

    *游标名%isopen：布尔类型，是否被打开

    *游标名%rownum：结果集中的数据条数据

 (4)游标的格式

    取出一条数据：fetch 游标名 into 变量；

a、定义(并且声明结果集的由来):

 cursor 游标名 is select 子句;

b、打开(将结果集中的数据放入临时空间中，并让游标指向第一条数据):

 open 游标名;

c、取数据

 fetch 游标名 into 变量;(要先取一次在写循环，要不然取不出来数据)

 while 游标名%foundloop;

     fetch 游标名 into 变量;

     对变量的操作;

  endloop;

 close 游标名;(关闭游标，释放空间，不能取数据了)

(5)隐式游标查看状态：sql%属性

(6)游标可以带参数

     1）声明带参数的游标

     2）使用的时候传入实际的值。

cursor emp_cursor(参数名 参数类型) is select …..where 列名= 参数名

begin

     open emp_cursor(实际值)；

for 循环的时候是在 in 后面的游标名后面传入实际的值。

 (7)游标中关于事务的部分 for update

            作用：锁定数据

几个游标的使用实例 

八、异常

1、异常分类

A、预定义异常：常用的24种，系统提供编号和异常名称，可以直接使用

       如：no_data_found

              too_many_row              //前两种异常出现在 insert … into …中

              cursor_already_open

              invalid_cursor               //游标没有打开或已经关闭

B、非预定义异常：系统已经给定编号，但是没有名称

C、自定义异常：系统没有给定编号和名称，自己按需求自己定义的异常

2、格式

A．预定义异常：

       在begin 与 end 代码中：

       begin

              exception

                     when异常名称 then 处理;

                     whenothers then 处理;

       end;

B．非预定义异常：需先定义，连接，再使用

       Declare

              e_output_exceptionexception;      //声明

              progamexception_init(异常名称, -编号);    //连接

       begin

              …

              exception

                     …..//同理

       end;

异常实例

C．自定义异常

       declare

              e_tooLarge_exceptionexception;

              v_tempnumber;

       begin

              if(v_temp> 100) raise e_tooLarge_exception; end if;

              exception

                     when e_tooLarge_exception then

                            dbms_output.put_line(‘值太大’);

       end;

 

九、函数（function）

       这里的函数类似于Java中的方法，函数可以永久存在数据库中，以便使用。

使用方法：先定义一个函数，再使用

a.定义

       createor replace function 函数名(参数名 参数类型)

       return返回值类型

       is

       函数变量声明              //没有则省略，但 is 不能省略

       begin

              …           //方法体

       end;

b.使用：因为有返回值，所以应该定义一个变量来接收返回值

c. 参数修饰符：in 参数传入方法；out 类似return，可以把值传出；inout 参数既可以传进来，也可以传出去

 

十、存储过程(procedure)

       与函数基本相同，但是函数有返回值，存储过程没有返回值。如果确实有需要传出数据，可以用 out 修饰参数。

 

十一、触发器(trigger)

       当一个事件发生时，进行相应的操作。可以给增删改添加触发器。系统自动调用。

格式：声明—时间—范围

create or replace trigger test_trig

before/after insert on emp            //在增/删/改之前/后

for each row                               //emp 表的每一行

begin

       ….          //事件触发后所执行的操作

end

 

触发器专有的属性：（可用于备份数据）

       :old        ——表示原来的值    :old.sal

       :new      ——表示新值        :new.sal