oracle学习3

PL/SQL编程

PL/SQL是oracle在标准的sql语言上的扩展，是非常强大的数据库过程语言
过程、函数、触发器是pl/sql编写的，并存储在oracle中，Java程序可以调用

PL/SQL编程之所以能提高应用程序的运行性能，原因如下：
传统的操作数据库方法是：
将sql语句写到Java程序中，首先得到一个链接，接着将sql语句发给数据库，数据库要对sql语句进行编译，编译完成后再执行，
最后再把结果返回给Java程序
PL/SQL的操作数据库方法是：
Java程序可以直接调用放在oracle中的过程，而这些过程已经事先编译过了

PL/SQL编程缺点：
移植性不好，万一换了数据库，那就不行了

开发工具：sqlplus 和 pl/sql developer

ORACLE 过程、函数、包的区别与联系:

一、过程与函数区别

1.过程可以有0~N个返回参数，通过OUT or IN OUT参数返回；函数有且仅有1个返回值，通过return语句返回。

2.调用过程时，可做为单独的语句执行；调用函数时，函数必须把返回结果赋值给一个变量。

3.在SQL语句中，不能调用过程；在SQL语句中，可以直接调用函数。

二、过程与函数

过程[procedure]及函数[function]这两种方案对象均由 SQL 语句及 PL/SQL 语言构件组合而成，存储于数据库中，运行时作为一个整体，用于解决某个问题，或完成一组相关的操作。调用者可以在调用过程及函数时为其提供参数，参数可以专用于输入值，专用于输出值，或同时用于输入及输出值。在过程与函数中，用户既可以发挥 SQL 的灵活性与易用性，也能够发挥结构化编成语言的过程控制能力。过程与函数基本类似，唯一区别在于函数总会向调用者返回一个值，而过程无此特性。

三、存储过程在以下方面具有优势：

1.利用定义者权限过程[definer’s rights procedure]确保数据安全
使用存储过程有助于确保数据安全。具备数据库对象访问权限的用户可以定义存储过程或函数，其他用户通过已定义的过程访问数据，从而达到限制用户对数据库操作的目的。

例如，现有一个过程能够更新某数据表，管理员不必授予用户直接访问数据表的权限，而是授予用户访问此过程的权限。当用户调用此过程时，过程以其拥有者的权限运行。具备执行此过程权限（但不具备查询，更新，及删除底层表数据权限）的用户可以调用过程对表进行操作，但不能以其他方式操作表数据。

2.通过调用者权限过程[invoker’s rights procedure]使用调用者的权限及方案上下文
调用者权限过程可以继承其调用者的权限及方案上下文。即调用者权限过程不与特定的用户或方案绑定，每次执行时利用当前用户的权限操作当前用户的方案对象。应用程序开发者可以通过调用者权限过程使应用逻辑集中化，即便底层数据分散于不同的用户方案中。

例如，以经理身份登录的用户运行针对 employees 表的更新过程时可以修改薪水数据，而以职员身份登录的用户运行同样的的更新过程时只能修改地址数据。

3.提升性能
与向 Oracle 提交 SQL 语句或 PL/SQL 块代码相比，使用存储过程时通过网络传输的数据量较小，因为存储过程只有在定义时需要被传输到服务器，而使用时只需进行调用。
数据库以编译后的形式保存过程，因此执行期间无需进行编译。
如果过程已经被加载到系统全局区[system global area，SGA]的共享池[shared pool]内，则过程可以直接执行，而不必从磁盘获取。

4.内存分配
存储过程可以利用 Oracle 的共享内存特性，多个用户执行同一个过程时只需将一份过程副本加载到内存中。通过在多个用户间共享相同的代码，能够显著地减少应用程序所需的 Oracle 内存。
5.提高开发生产率
利用存储过程能够提高开发生产率。在一组公用的存储过程的基础上开发应用程序，能够避免冗余代码从而提高开发生产率。

例如，开发者可以编写存储过程，分别对 employees 表内的员工数据进行插入，更新及删除操作。任何应用程序都可以调用这些过程，而无需重写 SQL 语句就可以完成相应的工作。如果数据管理的方式发生变化，只需修改存储过程，而不必修改调用过程的应用程序。

6.完整性
存储过程能够提高应用程序所处理数据时的一致性与完整性。由于应用程序是在一组 公用存储过程的基础上开发的，因此能够减少提交代码发生错误的可能性。
例如，用户可以对过程或函数进行测试，确保其返回准确的结果，一旦验证则可以在其他应用程序中重复使用而无需再次测试。如果过程所引用的数据结构发生变化，只需修改存储过程，而不必修改调用过程的应用程序。

例1. 编写一个存储过程，向表里添加记录
create [or replace] procedure sp_pro1 is
begin
–执行部分
insert into mytest values(‘lou’,’123’);
end;
/

如何查看错误信息：
show error；

如何调用该过程，有两种方式：
1. exec 过程名（参数值1，参数值2…）
2. call 过程名（参数值1，参数值2…）

编写规范：
1. 注释
单行注释 –
多行注释 /……/
2. 标识符号的命名规范
当定义变量时，建议使用v_为前缀 v_sal
当定义常量时，建议使用c_为前缀 c_sal
当定义游标时，建议使用_cursor为后缀 emp_cursor
当定义例外时，建议使用e_为前缀 e_error

块

过程、函数、触发器、包都是由块构成的，可能需要在pl/sql中嵌套很多其它块
块由三个部分构成：定义部分、执行部分、例外处理部分
declare 可选的
/*定义部分：定义常量、变量、游标、例外、复杂数据类型
begin 必须的
/*执行部分：要执行的sql语句
exception 可选的
/*例外处理部分：处理各种运行的错误

例2. 编写一个块
set serveroutput on –打开输出选项
begin
dbms_output.put_line(‘hello’);
end;
/
注：dbms_output是oracle提供的包，包含一些过程，put_line就是这个包中的一个过程

例3. 编写一个复杂的块
declare
v_ename varchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&aa;
dbms_output.put_line(‘雇员’||v_ename||v_sal);
end;
/

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

例4. 编写一个有例外部分的块
比如在例三中，如果输入不存在的编号，那么会有什么后果？
说明：oracle事先预定义了一些例外，no_data_found 就是找不到数据的例外

declare
v_ename varchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&aa;
dbms_output.put_line(‘雇员’||v_ename||v_sal);
exception
when no_data_found then
dbms_output.put_line(‘输入有误’)
end;
/

过程 procedure

例5. 编写一个过程，可以输入雇员名、新工资 可修改雇员的工资

create procedure sp_pro3(spname varchar2,newsal number) is
begin
–执行部分,根据用户名取修改工资
update emp set sal=newsal where ename=spname;
end;
/

exec sp_pro3(‘scott’,4678);

例6. 如何在Java程序中调用存储过程？

import java.sql.*;
public static void main(String[] args)
{
//1.加载驱动
Class.forname(“oracle.jdbc.driver.oracledriver”);
//2.得到链接
Connection ct=DriverManager.getConnection(“jdbc:oracle:thin:@127.0.0.1:1521:myorac1”,”scott”,”m123’)
//3.创建Callablestatement
Callablestatement cs=ct.prepareCall(“{call sp_pro3(?,?)}”);
//4.给问号赋值
cs.setString(1,’smith’);
cs.setInt(2,10);
//5.执行
cs.execute();
//6.关闭资源
cs.close();
ct.close();
}

函数 function

函数用于返回特定的数据，当建立函数时，在函数头部必须包含return 子句，而在函数体中必须包含return语句返回的数据

例7. 建立函数，输入雇员的姓名，返回该雇员的年薪
create function sp_fun2(spname varchar2) return
number is
yearsal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearsal from emp where ename=spname;
return yearsal;
end;
/
在sql中调用函数：
sql>var abc number;
sql>call sp_fun2(‘scott’) into:abc; //不能用exec
sql>print abc;

包 package

包用于在逻辑上组合过程和函数，由包规范和包体两部分组成
1.包的规范
create package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end sp_package;

注：包的规范只包含了过程和函数的说明，并没有实现代码
2.建立包体
create package body sp_package is
procedure update_sal(name varchar2,newsal number) is
begin
–执行部分,根据用户名取修改工资
update emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2) return
number is
annual_salary number(7,2);
begin
select sal*12+nvl(comm,0)*12 into annual_salary from emp where ename=name;
return annual_salary;
end;
end;

3.调用包的函数或是过程
sql>call sp_package.update_sal(‘scott’,120);

触发器 trigger

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

pl/sql变量

1.标量 scalar 常用类型
在定义好变量后，就可以使用这些变量了，这里需要说明的是pl/sql块为变量赋值时，需要在等号前加冒号 :=
例1. 输入员工号，显示雇员姓名、工资、个人所得税（税率0.03）

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(‘姓名是’||v_ename||’工资是’||v_sal||’交税’||v_tax_sal)；
end;
/

标量 scalar 使用%type类型

如上例中如果员工的姓名超过了5个字符的话，就会有错误，所以可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的
变量的类型和长度

标识符 表名.列名%type；

v_ename varchar2(5); 改成 v_ename emp.ename%type 这样就会跟表字段的类型大小一样了

2.复合变量 composite
用于存放多个值的变量 类似于数组

（1）复合变量 pl/sql记录
当引用 pl/sql记录成员时，必须要加记录变量作为前缀（记录变量.记录成员）如下：

declare
–定义一个pl/sql记录类型 emp_record_type ，类型包含三个数据，分别是name ，salary，title
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type
);
–定义了一个变量，这个变量的类型是emp_record_type
sp_record emp__record_type；
–执行
begin
select ename,sal,job into sp_record
from emp where empno=7788;
dbms_output.put_line(‘员工名’||sp_record.name);
end;
/

（2）复合变量 pl/sql表

相当于高级语言中的数组，但下标可以为负数

declare
–定义了一个pl/sql表类型sp_table_type,该类型用于存放emp.ename%type
– index by binary_integer表示下标是整数
type sp_table_type is table of emp.ename%type index by binary_integer;
–定义了一个sp_table变量，类型是sp_table_type
sp_table sp_table_type;
–执行
begin
select ename into sp_table(0) from emp where empno=7700;
dbms_output.put_line(‘员工名’||sp_table(0));
end;
/

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

参照变量 游标变量(ref cursor)

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

例1. 编写一个块，可以输入部门号，并显示该部门所有员工姓名和它的工资

declare
–定义游标类型
type sp_emp_cursor is ref cursor;
–定义一个游标变量
test_cursor sp_emp_cursor；
–定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
–执行
begin
– 把test_cursor 和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no;
–循环取出
loop
fetch test_cursor into v_ename,v_sal;
–判断是否test_cursor为空了
exit when test_cursor%notfound;
dbms_output.put_line(‘员工名’||v_ename||’工资’||v_sal);
end loop;

end;

4.控制结构

条件语句
if ————then
if————then——else
if————then——elsif——else

例1.编写一个过程，输入一个雇员名，如果该雇员的工资低于2000，就给该雇员工资增加10%

create or replace procedure sp_pro6(spname varchar2) is
–定义
v_sal emp.sal%type;
–执行
begin
select sal into v_sal from emp where ename=spname;
–判断
if v_sal<2000 then
update emp set sal=sal*10%+sal where ename=spname;
end if;

end;

例2.编写一个过程，输入一个雇员名，如果该雇员的补助不是0，就在原来的基础上增加100，如果补助为0就把补助设为200

create or replace procedure sp_pro7(spname varchar2) is
–定义
v_comm emp.comm%type;
–执行
begin
select comm into v_comm from emp where ename=spname;
–判断
if v_comm=0 then //不等于的话写成 v_comm<>0
update emp set comm =200 where ename=spname;
else
update emp set comm =comm+100 where ename=spname;
end if;

end;

例3.编写一个过程，输入一个雇员编号，如果该雇员的职位是president就给他工资增加1000，如果职位是manager就给他
工资增加500，其它职位的雇员工资增加200

create or replace procedure sp_pro7(spno number) is
–定义
v_job emp.job%type;
–执行
begin
select job into v_job from emp where empno=spno;
–判断
if v_job=’president’ then
update emp set sal=sal+1000 where empno=spno;
elsif v_job=’manager’ then
update emp set sal=sal+500 where empno=spno;
else
update emp set sal=sal+200 where empno=spno;
end if;

end;

循环语句 loop
loop …..end loop

例1. 编写一个过程，可输入用户名，并循环添加10个用户到user表中，用户编号从1开始加

create or replace procedure sp_pro8(spname varchar2) is
–定义
v_num number:=1;
–执行
begin

loop
insert into user values(v_num,spname);
–判断是否要退出循环
exit when v_num=10；
v_num:=v_num+1;
end loop;

end;

循环语句 while
以while ..loop 开始 以end loop结束

例2.

create or replace procedure sp_pro8(spname varchar2) is
–定义
v_num number:=11;
–执行
begin

while v_num<=20 loop
insert into user values(v_num,spname);
v_num:=v_num+1;
end loop;

end;

循环语句 for （不是很好）

begin
for i in reverse 1…10 loop
insert into users values values(i,’lou’);
end loop;
end

顺序控制语句 goto,null

goto 跳出多层循环时才使用，建议不使用

null 不会执行任何操作，并且会直接跳到下一条语句

编写分页过程

无返回值的存储过程
例1.有一张book表 有书号，书名，出版社。编写一个过程，向book表添加书，要求通过Java程序调用

create or replace sp_pro9(spbookid in number,spbookname in varchar2,sppublisghouse in varchar2) is //in代表输入变量，假如不写，就默认是in

begin

insert into book values(spbookid,spbookname,sppublisghouse);

end;
/

–在Java中调用

//1.加载驱动
class.forname(“oracle.jdbc.driver.oracledriver”);
connection ct=drivermanager.getconnection(“jdbc:oracle:thin:@127.0.0.1:1527:myorac”,”scott”,”m123”);
//2.创建callablestatement 用来调用存储过程
callablestatement cs=ct.preparecall(“{call sp_pro7(?,?,?)}”);
//给？赋值
cs.setInt(1,10);
cs.setString(2,”笑傲江湖”)；
cs.setString(3,”人民出版社”)；
// 执行
cs.execute();
// 关闭各种打开的资源

有返回值的存储过程 （有输入和输出的）
例2.编写一个过程，可以输入员工的编号，返回该员工的姓名

create or replace sp_pro9
(spno in number,spname out varchar2) is
begin
select ename into spname from emp where empno=spno;
end;

–在Java中调用

//1.加载驱动
class.forname(“oracle.jdbc.driver.oracledriver”);
connection ct=drivermanager.getconnection(“jdbc:oracle:thin:@127.0.0.1:1527:myorac”,”scott”,”m123”);
//2.创建callablestatement 用来调用存储过程
callablestatement cs=ct.preparecall(“{call sp_pro9(?,?)}”);
//给第一个？赋值
cs.setInt(1,7788);
//给第二个？赋值
cs.registeroutparameter(2,oracle.jdbc.oracletypes.varchar);
// 执行
cs.execute();
//取出返回值,注意问号的顺序
String name=cs.getString(2);

system.out.println(“7788的名字是”+name)；
// 关闭各种打开的资源

扩展：假如返回姓名、工资、岗位等多个

create or replace sp_pro9
(spno in number,spname out varchar2，spsal out number,spjob out varchar2) is
begin
select ename,sal,job into spname,spsal,spjob from emp where empno=spno;
end;

–在Java中调用

//1.加载驱动
class.forname(“oracle.jdbc.driver.oracledriver”);
connection ct=drivermanager.getconnection(“jdbc:oracle:thin:@127.0.0.1:1527:myorac”,”scott”,”m123”);
//2.创建callablestatement 用来调用存储过程
callablestatement cs=ct.preparecall(“{call sp_pro9(?,?,?,?)}”);
//给第一个？赋值
cs.setInt(1,7788);
//给另外的？赋值 一定要全部关联
cs.registeroutparameter(2,oracle.jdbc.oracletypes.varchar);
cs.registeroutparameter(3,oracle.jdbc.oracletypes.double);
cs.registeroutparameter(4,oracle.jdbc.oracletypes.varchar);

// 执行
cs.execute();
//取出返回值,注意问号的顺序
String name=cs.getString(2);

system.out.println(“7788的名字是”+name)；
// 关闭各种打开的资源

返回结果集的过程
例2.编写一个过程，输入部门号，返回该部门所有雇员信息 （一个结果集）

由于oracle存储过程并没有返回值，它所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，
所以不能用一般的参数，所以要用package；

//1.创建一个包,在包中定义了一个类型 test_cursor，是个游标类型
create package testpackage AS
type test_cursor is ref cursor;
end testpackage;

//2.建立一个存储过程
create or replace procedure sp_pro9(spno in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=spno;
end;

//3.如何在Java中调用

//1.加载驱动
class.forname(“oracle.jdbc.driver.oracledriver”);
connection ct=drivermanager.getconnection(“jdbc:oracle:thin:@127.0.0.1:1527:myorac”,”scott”,”m123”);
//2.创建callablestatement 用来调用存储过程
callablestatement cs=ct.preparecall(“{call sp_pro9(?,?)}”);
//给第一个？赋值
cs.setInt(1,10);
//给第二个？赋值
cs.registeroutparameter(2,oracle.jdbc.oracletypes.cursor);

// 执行
cs.execute();
//得到结果集
Result rs=(ResultSet)cs.getObject(2);
while(rs.next()){

system.out.println(rs.getInt(1)+rs.getString(2));
}

// 关闭各种打开的资源

编写分页过程
编写一个存储过程，要求可以输入表名，每页显示记录数、当前页，返回总记录数，总页数，和返回的结果集

//1.oracle的分页 复习
(select * from emp) t1
会返回一列编号
select t1.,rownum rn from (select from emp) t1
选择前10条数据
select t1.,rownum rn from (select from emp) t1 whererownum<=10;
选择第6条到第10条数据
select * from
(select t1.,rownum rn from (select from emp) t1 whererownum<=10)
where rn>=6;

在分页时，可以把上面的sql语句当做一个模板使用，只是要改变表名，以及10跟6两个数字

//开始编写分页的过程

1.开发一个包
create package testpackage as
type test_cursor is ref cursor;
end testpackage;

2.编写过程
create or replace procedure fenye
(tablename in varchar,
pagesize in number, –每页显示数目
pagenow in number, –第几页
myrows out number, –总记录数
mypagecount outnumber –总页数
p_cursor out testpackage.test_cursor –返回的记录集
) is
–定义部分
–定义sql语句 字符串
v_sql varchar2(1000);
–定义两个整数
v_begin number:=(pagenow-1)*pagesize+1;
v_end number:=pagenow*pagesize;
–执行部分
begin
v_sql:= ‘select * from
(select t1.,rownum rn from (select from ‘||tablename||’) t1 where rownum<=’|| v_end ||’)
where rn>=’|| v_begin’;

–把游标和sql关联
open p_cursor for v_sql;
–计算myrows和mypagecount
–组织一个sql语句，并把返回值赋给myrows
v_sql:=’select count(*) from ’ || tablename;
execute immediate v_sql into myrows;
–计算mypagecount
if mod(myrow,pagesize)=0 then
mypagecount:=myrow/pagesize;
else
mypagecount:=myrow/pagesize+1;
end if;
–关闭游标
close p_cursor;
end;

3.使用Java测试

try{
//1.加载驱动
class.forname(“oracle.jdbc.driver.oracledriver”);
connection ct=drivermanager.getconnection(“jdbc:oracle:thin:@127.0.0.1:1527:myorac”,”scott”,”m123”);
//2.创建callablestatement 用来调用存储过程
callablestatement cs=ct.preparecall(“{call fenye(?,?,?,?,?,?)}”);
//给输入？赋值
cs.setString(1,’emp’); –显示表名
cs.setint(2,5); –显示一个显示五条记录
cs.setint(3,1); – 显示第几页
//注册总记录数
cs.registeroutparameter(4,oracle.jdbc.oracletypes.INTEGER);
//注册总页数
cs.registeroutparameter(5,oracle.jdbc.oracletypes.INTEGER);
//注册返回的结果集
cs.registeroutparameter(6,oracle.jdbc.oracletypes.cursor);

cs.execute();
//取出总记录数、总页数、结果集
int rownum=cs.getint(4); –getint(4)中4是由该参数所处位置决定的
int pagecount=cs.getint(5);
Result rs=(ResultSet)cs.getObject(6);

system.out.println(“rownum”+rownum);
system.out.println(“总页数”+pagecount);

while(rs.next()){

system.out.println(“编号”+rs.getInt(1)+”名字”rs.getString(2)); –rs.getInt(1),1表示集合第一列
}

}
catch(Exception e){
e.printstacktrace();
}