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;