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公司提供的一个工具，这个因为我们在以前介绍过的：

举一个简单的案例：

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

createtablemytest(
namevarchar2(30),
passwdvarchar2(30)
);

--创建一个存储过程
createorreplaceproceduresp_addUser
(v_namevarchar2,v_passwdvarchar2)
is
begin
insertintomytestvalues(v_name,v_passwd);
end;

--查看错误
showerror;

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

execsp_addUser('zhangsan','123');

callsp_addUser('lisi','123');

Pl/sql developer开发工具

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

举一个例子：

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

createproceduresp_delUser
(v_namevarchar2)
is
begin
deletefrommytestwherename= 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块

setserveroutputon;--打开输出选项

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

相关说明：

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

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

declare
v_enamevarchar2(5);--定义字符串变量
begin
selectenameintov_enamefromemp
whereempno = &no;
dbms_output.put_line('雇员名：'||v_ename);
end;

多个变量的例子：

declare
v_enamevarchar2(5);
v_salnumber(7,2);
begin
selectename,salintov_ename,v_salfromemp
whereempno = &no;
dbms_output.put_line('雇员名：'||v_ename||' 薪水: '||v_sal);
end;

相关说明：

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

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

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

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

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

declare
v_enamevarchar2(5);
v_salnumber(7,2);
begin
selectename,salintov_ename,v_salfromemp
whereempno = &no;
dbms_output.put_line('雇员名：'||v_ename||' 薪水: '||v_sal);
exception
whenno_data_found
thendbms_output.put_line('您输入的编号有误！');
end;

相关说明：

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

过程

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

实例如下：

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

createproceduresp_updateUser
(spNamevarchar2,newSalnumber)
is
--定义过程要使用的变量
begin
updateempsetsal = newSalwhereename = spName;
end;

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

Exec sp_update(‘scott’,1350);

Call sp_update(‘scott’,1650);

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

packagecom.anllin.jdbc.oracle;

importjava.sql.*;

publicclassNoReturnValProc

{

publicstaticvoidmain(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)

{

thrownewRuntimeException(e);

}

finally

{

try

{

if(null!= stmt)

stmt.close();

if(null!= conn)

conn.close();

}

catch(Exception ex)

{

thrownewRuntimeException(ex);

}

}

}

}

特别说明：

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

函数

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

createfunctionannual_income(namevarchar2)
returnnumberis
annual_salarynumber(7,2);
begin
selectsal*12+nvl(comm,0)intoannual_salary
fromemp
whereename =name;
returnannual_salary;
end;

在sqlplus中调用函数

var incomenumber
callannual_income('SCOTT')into:income;
printincome;

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

selectannual_income('SCOTT')fromdual;

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

包

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

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

实例：

createpackagesp_packageis
procedureupdate_sal(namevarchar2,newsalnumber);
functionannual_income(namevarchar2)returnnumber;
end;

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

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

createpackagebodysp_packageis
--implement procedure update_sal
procedureupdate_sal(namevarchar2,newsalnumber)
is
begin
updateemp
setsal=newsal
whereename=name;
end;
--implement function annual_income
functionannual_income(namevarchar2)
returnnumberis
annual_salarynumber;
begin
selectsal*12+nvl(comm,0)intoannual_salary
fromemp
whereename=name;
returnannual_salary;
end;
end;

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

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

如：

callsp_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：数据类型

Notnull：指定变量值不能为null

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

Default用于指定初始值。

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

标量定义的案例：

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

v_enamevarchar2(10);

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

v_salnumber(6,2);

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

v_sal2number(6,2) := 5.4;

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

v_hiredatedate;

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

v_validbooleannotnulldefaultfalse;

标量――使用标量

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

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

Set serveroutput on;

declare
c_tax_ratenumber(3,2) := 0.03;
v_enamevarchar2(5);
v_salnumber(7,2);
v_tax_salnumber(7,2);
begin
selectename,salintov_ename,v_salfromempwhereempno=&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_ratenumber(3,2) := 0.03;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_tax_salnumber(7,2);
begin
selectename,salintov_ename,v_salfromempwhereempno=&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
typeemp_record_typeisrecord(
nameemp.ename%type,
salary emp.sal%type,
title emp.job%type
);
--reference record type
sp_record emp_record_type;
begin
selectename,sal,jobintosp_record
fromemp
whereempno=7788;
dbms_output.put_line('name: '||sp_record.name);
end;

复合类型—pl/sql表

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

declare
typesp_table_typeistableofemp.ename%type
indexbybinary_integer;
sp_table sp_table_type;
begin
selectenameintosp_table(0)
fromemp
whereempno=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
typesp_emp_cursorisrefcursor;
sp_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
opensp_cursor
for
selectename,sal
fromemp
wheredeptno=&no;
loop
fetchsp_cursorintov_ename,v_sal;
exitwhensp_cursor%notfound;
dbms_output.put_line('name: ' || v_ename || ' sal: ' || v_sal);
endloop;
end;

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

declare
typesp_emp_cursorisrefcursor;
sp_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
opensp_cursor
for
selectename,sal
fromemp
wheredeptno=&no;
loop
fetchsp_cursorintov_ename,v_sal;
if
v_sal<200
then
updateempsetsal=sal+100
whereename = v_ename;
endif;
exit
whensp_cursor%notfound;
dbms_output.put_line('name: ' || v_ename || ' sal: ' || v_sal);
endloop;
end;