oracle实战第六天pl/sql介绍和基础

第六天

内容介绍

1.pl/sql的介绍

2.pl/sql的基础

期望目标

1.理解oracle的pl/sql概念

2.掌握pl/sql编程技术（包括编写过程、函数、触发器…）

Pl/sql的介绍

Pl/sql是什么？

Pl/sql（procedural language/sql）是oracle在标准的sql语言上的扩展。Pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。

为什么学？

学习的必要性：

1.提高应用程序的运行性能。

2.模块化的设计思想。

3.减少网络传输量。

4.提高安全性。

不好的地方：

1.移植性不好。

用什么编写pl/sql

Sqlplus开发工具

Sqlplus开发工具是oracle公司提供的一个工具，这个因为我们在以前介绍过的：

举一个简单的案例：

编写一个存储过程，该过程可能向某表中添加记录

create table mytest(
       name varchar2(30),
       passwd varchar2(30)
);

--创建一个存储过程
create or replace procedure sp_addUser
(v_name varchar2,v_passwd varchar2)
is
begin
insert into mytest values(v_name,v_passwd);
end;

--查看错误
show error;

--调用存储过程的两种方式

exec sp_addUser('zhangsan','123');

call sp_addUser('lisi','123');

Pl/sql developer开发工具

Pl/sql developer是用于开发pl/sql块的集成开发环境（ide），他是一个独立的产品，而不是oracle的一个附带品。

举一个例子：

编写一个存储过程，该过程可以删除某表记录。

create procedure sp_delUser
(v_name varchar2)
is
begin
delete from mytest where name = v_name;
end;

Pl/sql基础

开发人员使用pl/sql编写应用模块时，不仅需要掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。Pl/sql编程可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转帐存储过程模块..而且如果使用pl/sql编程，我们可以轻松的完成非常复杂的查询要求。

简单分类

块（编程）：存储过程、函数、触发器、包。

编写规范

1.注释

单选注释 --

Select * from emp where empno=7788;--取得员工信息

多行注释

/*……*/来划分

2.标识符的命名规范

1）当定义变量时，建议用v_作为前缀v_sal

2）当定义常量时，建议用c_作为前缀 c_rate

3）当定义游标时，建议用_cursor作为后缀emp_cursor;

4）当定义例外时，建议用e_作为前缀e_error;

Pl/sql块

块（block）是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能，可能只需要编写一个pl/sql块；但是如果想实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。

Pl/sql块的结构

pl/sql由三个部分组成：定义部分、执行部分、例外处理部分。

如下所示：

Declare

/*定义部分――定义常量、变量、游标、例外、复杂数据类型*/

Begin

/*执行部分――要执行的pl/sql语句和sql语句*/

Exception

/*例外处理部分――处理运行的各种错误*/

end;

特别说明：

定义部分是从declare开始的，该部分是可选的。

执行部分是从begin开始的，该部分是必须的。

例外处理部分是从exception开始的，该部分是可选的。

实例1――只包括执行部分的pl/sql块

set serveroutput on;--打开输出选项

begin
     dbms_output.put_line('Hello,World');
end;

相关说明：

Dbms_output是oralce所提供的包（类似java的开发包），该包包含一些过程，put_line就是dbms_output包的一个过程。

实例2――包含定义部分和执行部分的pl/sql块

declare
     v_ename varchar2(5);--定义字符串变量
begin
     select ename into v_ename from emp
     where empno = &no;
     dbms_output.put_line('雇员名：'||v_ename);
end;

多个变量的例子：

declare
     v_ename varchar2(5);
     v_sal number(7,2);
begin
     select ename,sal into v_ename,v_sal from emp
     where empno = &no;
     dbms_output.put_line('雇员名：'||v_ename||' 薪水: '||v_sal);
end;

相关说明：

& 表示要接收从控制台输入的变量

实例3――包含定义部分、执行部分和例外处理部分

         为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要：

1）比如在实例中，如果输入了不存在的雇员号，应当做例外处理。

2）有时出现异常，希望用另外的逻辑处理

declare
     v_ename varchar2(5);
     v_sal number(7,2);
begin
     select ename,sal into v_ename,v_sal from emp
     where empno = &no;
     dbms_output.put_line('雇员名：'||v_ename||' 薪水: '||v_sal);
exception
     when no_data_found
     then dbms_output.put_line('您输入的编号有误！');    
end;

相关说明：

oracle事先预定义了一些例外，no_data_found就是找不到数据的例外。

过程

         过程用于执行特定的操作。当建立过程时，既可以指定输入参数（in），也可以指定输出参数（out）。通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。

实例如下：

1）写一个过程，可以输入雇员名，新工资，可修改雇员的工资。

create procedure sp_updateUser
(spName varchar2,newSal number)
is
--定义过程要使用的变量
begin
update emp set sal = newSal where ename = spName;
end;

2）如何调用过程有两种方法：

Exec sp_update(‘scott’,1350);

Call sp_update(‘scott’,1650);

3）如何在java程序中调用一个存储过程

package com.anllin.jdbc.oracle;

 

import java.sql.*;

 

public class NoReturnValProc

{

    public static void main(String[] args)

    {

       Connection conn = null;

       CallableStatement stmt = null;

       try

       {

           Class.forName("oracle.jdbc.driver.OracleDriver");

           conn = DriverManager

                  .getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myorcl",

                         "scott", "tiger");

           stmt = conn.prepareCall("{call sp_addBooks(?,?,?)}");

           stmt.setInt(1, 1);

           stmt.setString(2, "水是最好的药");

           stmt.setString(3, "人民出版社");

           stmt.execute();

       }

       catch (Exception e)

       {

           throw new RuntimeException(e);

       }

       finally

       {

           try

           {

              if (null != stmt)

                  stmt.close();

              if (null != conn)

                  conn.close();

           }

           catch (Exception ex)

           {

              throw new RuntimeException(ex);

           }

       }

    }

}

特别说明：

对于过程我们会在后面做更详细的说明，这里先简单介绍一下。

函数

函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句返回的数据，我们可以使用create function来建立函数，实际案例：

create function annual_income(name varchar2)
return number is
annual_salary number(7,2);
begin
select sal*12+nvl(comm,0) into annual_salary
from emp
where ename = name;
return annual_salary;
end;

在sqlplus中调用函数

var income number
call annual_income('SCOTT') into:income;
print income;

同样我们可以在java程序中调用该函数

select annual_income('SCOTT') from dual;

可以通过rs.getInt(1)得到返回的结果。

包

包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。

1）我们可以使用create package命令来创建包。

实例：

create package sp_package is
       procedure update_sal(name varchar2,newsal number);
       function annual_income(name varchar2) return number;
end;

包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现规范中的过程和函数

2）建立包体可以使用create package body命令

create package body sp_package is
--implement procedure update_sal
procedure update_sal(name varchar2,newsal number)
is
begin
     update emp
     set sal=newsal
     where ename=name;
end;
--implement function annual_income
function annual_income(name varchar2)
return number is
annual_salary number;
begin
     select sal*12+nvl(comm,0) into annual_salary
     from emp
     where ename=name;
     return annual_salary;
end;
end;

3）如何调用包的过程或是函数

当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要包名前加方案名。

如：

call sp_package.update_sal('SCOTT',1500);

特别说明：

包是pl/sql中非常重要的部分，我们在使用过程从页时，将会再次体验它的威力。

触发器

触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用 的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块，可以使用create trigger来建立触发器。

特别说明：

我们会在后面详细为大家介绍触发器的使用，因为触发器是非常有用的，可维护数据库的安全和一致性。

定义并使用变量

介绍：

在编写pl/sql程序时，可以定义变量和常量；在pl/sql包括有：

1）标量类型（scalar）

2）复合类型（composite）

3）参照类型（reference）

4）lob（large object）

标量――常用类型

在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。

Pl/sql中定义变量和常量的语法如下：

Indentifier [constant] datatype [not null] [:=| default expr]

Identifier：名称

Constant：指定常量。需要指定它的初始值，且其值是不能改变的

Datatype：数据类型

Not null：指定变量值不能为null

:= 给变量或是常量指定初始值。

Default 用于指定初始值。

Expr: 指定初始值的pl/sql表达式，可是文本值，其它变量，函数等。

标量定义的案例：

1）定义一个变长的字符串

v_ename varchar2(10);

2）定义一个小数范围－9999.99~9999.99

v_sal number(6,2);

3）定义一个小数并给一个初始值为5.4 。:= 是pl/sql的赋值号

v_sal2 number(6,2) := 5.4;

4）定义一个日期类型的数据。

v_hiredate date;

5）定义一个布尔变量，不能为空，初始值为false

v_valid boolean not null default false;

标量――使用标量

在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前加冒号（:=）

下面以输入员工号，显示雇员姓名，工资，个人所得税（税率为0.03）为例。说明变量的使用，看看如何编写。

Set serveroutput on;

declare
c_tax_rate number(3,2) := 0.03;
v_ename varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
v_tax_sal := v_sal * c_tax_rate;
dbms_output.put_line('name:'||v_ename||'sal:'||v_sal||' tax_sal:'||v_tax_sal);
end;

标量――使用%type类型

对于上面的 pl/sql块有一个问题：

就是如果员工的姓名超过了5个字符的话，就会有错误，为为降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。

标识符号 表名.列名%type

Set serveroutput on;

declare
c_tax_rate number(3,2) := 0.03;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_tax_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
v_tax_sal := v_sal * c_tax_rate;
dbms_output.put_line('name:'||v_ename||'sal:'||v_sal||' tax_sal:'||v_tax_sal);

end;

复合变量（composite）--介绍

用于存放多个值的变量。主要包括这几种：

1）pl/sql记录

2）pl/sql表

3）嵌套表

4）varray

复合类型—pl/sql记录

类似与高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须加记录变量作为前缀（记录变量.记录成员）如下：

declare
--declare record type
type emp_record_type is record(
     name emp.ename%type,
     salary emp.sal%type,
     title emp.job%type
);
--reference record type
sp_record emp_record_type;
begin
select ename,sal,job into sp_record
from emp
where empno=7788;
dbms_output.put_line('name: '||sp_record.name);
end;

复合类型—pl/sql表

相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。实例如下：

declare
type sp_table_type is table of emp.ename%type
index by binary_integer;
sp_table sp_table_type;
begin
     select ename into sp_table(0)
     from emp
     where empno=7788;
     dbms_output.put_line('name : ' || sp_table(0));
end;

说明：

Sp_table_type 是pl/sql表类型

emp.ename%type 指定了表元素的类型和长度

sp_table 为pl/sql表变量

sp_table(0) 则表示下标为0的元素。

复合变量――嵌套表

复合变量――变长数组

参照变量

介绍

参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量（ref cursor）和对象类型变量（ref obj_type）两种参照变量类型。

游标变量（ref cursor）

使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open时）需要指定select 语句，这样一个游标就与一个select语句结合了。实例如下：

1）请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。

declare
type sp_emp_cursor is ref cursor;
sp_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
  open sp_cursor
  for
  select ename,sal
  from emp
  where deptno=&no;
loop
  fetch sp_cursor into v_ename,v_sal;
  exit when sp_cursor%notfound;
  dbms_output.put_line('name: ' || v_ename || ' sal: ' || v_sal);
end loop;
end;

2）在1）的基础上，如果某个员工的工资低于200元，就增加100元。

declare
type sp_emp_cursor is ref cursor;
sp_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
  open sp_cursor
  for
  select ename,sal
  from emp
  where deptno=&no;
loop
  fetch sp_cursor into v_ename,v_sal;
  if
       v_sal<200
  then
       update emp set sal=sal+100
       where ename = v_ename;
  end if;
  exit
       when sp_cursor%notfound;
  dbms_output.put_line('name: ' || v_ename || ' sal: ' || v_sal);
end loop;
end;