Orcacle PL/SQL 培训

PL/SQL (procedural language/sql) 过程化语言
可以编写 存储过程，函数，包，触发器 等等
-------------------------------------运算符 部分----------------------------------------------
= 比较
:= 赋值
<> 不等于
|| 连字符
-------------------------------------定义与使用变量 部分----------------------------------------------
1、标量类型 scalar
2、复合类型 composite
3、参照类型 reference
4、lob(large object)

1、标量类型 scalar
1.1、标量类型 scalar - 常用类型
语法：
变量名 [constant] datatype [not null] [:=default | expr]
变量名:就是变量名
constant:说这是常量，需要初始值，且值以后不能改变,可选项
datatype:数据类型
not null : 指定变量不能为空，可选项
:= 给变量或是常量指定初始值，可选项
default 用于指定初始值 ，可选项
expr：指定初始值的pl/sql表达式，可是文本值、其它变量、函数等

例子：
1、定义一个变长字符串
v_name varchar2(10)
2、定义一个小数 范围-9999.99~9999.99
v_sal number(6,2)
3、定义一个小数并给个初始值5.4
v_sal2 number(6,2):=5.4
4、定义一个日期类型的变量
v_hiredate date;
5、定义一个布尔变量，不能为空，初始值为false
v_bl boolean not null default false;

1.2、标量类型 scalar - 使用%type类型
指定变量的类型与某个字段的类型相同
语法：
变量名 表名.字段名%type

例子：
v_name employees.last_name%type
声明变量 v_name的类型是是 employees表的last_name字段的类型。

2、复合类型 composite 介绍
2.1 复合类型-PL/SQL记录类型
Sql代码 收藏代码
set serveroutput on;
declare
--定义一个pl/sql记录类型emp_record_type
type emp_record_type is record(
name employees.last_name%type,
salary employees.salary%type,
hire_date employees.hire_date%type
);
--定义一个emp_record_type类型的变量v_record
v_record emp_record_type;

begin
select last_name,salary,hire_date into v_record
from employees where employee_id=206;
dbms_output.put_line('姓名：'||v_record.name
||' 工资：'||v_record.salary
||' 入职日期：'||v_record.hire_date
);
end;
结果：
姓名：Gietz 工资：5555 入职日期：07-6月 -94
PL/SQL procedure successfully completed



2.2 复合类型-PL/SQL表
相当于高级语言中的数组，下标可以为负数

Sql代码 收藏代码
declare
--定义一个pl/sql表类型table_type，名称随意取
--table_type类型的变量用于存放employees.last_name%type类型的数据
type table_type is table of employees.last_name%type
--数组的下标是整数，可是为负
index by binary_integer;

--声明一个table_type类型的变量v_table
v_table table_type;
begin
select last_name into v_table(0) from employees
--如果返回多行，会报错，解决方法见后面的参照类型变量
where employee_id=206;
dbms_output.put_line('姓名：'||v_table(0));
end;

2.3 复合类型-嵌套表 用的少，略...
2.4 复合类型-变长数组 用的少，略...


3、参照类型 reference
参照变量是用于存放数值指针的变量，可以使程序共享相同的对象，最常使用的参照变量类型之一就是 游标变量（ref cursor）

3.1、参照类型 reference - cursor游标变量
使用游标时，定义游标时不需要指定相应的select语句，但是当使用游标时（open时），需要指定select语句，这样游标就与一个select语句结合了。

Sql代码 收藏代码
--编写一个pl/sql块，输入部门编号，显示本部门所有员工的姓名与工资
declare
Sql代码 收藏代码
--定义一个游标类型test_cursor_type，它是类型。
type test_cursor_type is ref cursor;

--定义一个游标变量
test_cursor test_cursor_type;

--定义一般的变量
v_name employees.last_name%type;
v_sal employees.salary%type;

begin
--把test_cursor游标变量与一个select语句结合起来
--&no 要输入值给no变量
open test_cursor for select last_name,salary
from employees where department_id=&no;

--循环取出
loop
fetch test_cursor into v_name,v_sal;
--判断test_corsor是否为空,为空时退出循环
exit when test_cursor%notfound;
dbms_output.put_line('姓名：'||v_name||' 工资：'||v_sal);
end loop;

--关闭游标
close test_cursor;
end;

------------------程序的三大结构：判断结构、顺序结构、循环结构 ---------------------------
判断结构：
if -- 条件 -- then -- 分支1 -- end if
if -- 条件 -- then -- 分支1 -- else -- 分支2 -- end if
if -- 条件1 -- then -- 分支1 -- elsif -- 条件2 -- then -- 分支2 --else -- 分支3 -- end if

循环结构:
loop -- 被循环部分 -- exit when 终止循环的条件; -- end loop 至少执行一次
while -- 条件 -- loop -- 被循环部分 -- end loop 满足条件才执行

for i in reverse 1..10 loop
被循环部分
end loop;

顺序结构:
goto meta_name; 跳到meta_name处
<<meta_name>>

null;语句 ， 什么也不做

-------------------------------------存储过程部分-----------------------------------------------
创建一个过程:
Sql代码 收藏代码
create or replace procedure pro1 is
begin
--单行注释
/*多行注释*/
insert into t7 values(2,'apple');
commit;
end;
/


查看错误信息：
Sql代码 收藏代码
show error;


调用过程：
exec 过程名 (参数值1，参数值2...);
call 过程名 (参数值1，参数值2...);

删除过程：
drop procedure 过程名;



块的结构说明：
declear
/*定义部分--常量，变量，游标，复杂数据类型*/
begin
/*执行部分--要执行的PL/SQL语句和SQL语句*/
exception
/*异常处理部分*/
end;


打开输出选项

Sql代码 收藏代码
set serveroutput on
begin
dbms_output.put_line('hello ,world');
end;
/

输出：hello ,world
要打开输出选项才能看到。


在过程中定义一个变量：

Sql代码 收藏代码
声明部分略，直接写了执行部分
declare
v_ename varchar2(25);--定义字符串变量
v_sal number(7,2); --定义一个数值变量
begin
--从输入框输入变量no,用查询的结果为变量赋值,
--只适合查出一条结果的情况,查不到结果或查到多条结果都会报错
select last_name,salary into v_ename,v_sal
from employees where employee_id=&no;
dbms_output.put_line('雇员名：'||v_ename||' 薪水：'||v_sal);
end;
/

输入：206
输出：雇员名：Gietz 薪水：8300

输入一个不存在编号，结果：
ORA-01403: 未找到数据
ORA-06512: 在 line 7


在过程中处理异常：

声明部分略，直接写了执行部分
Sql代码 收藏代码
declare
v_ename varchar2(25);--定义字符串变量
v_sal number(7,2); --定义一个数值变量
begin
--从输入框输入变量no，查询的结果为变量赋值，
--只适合查出一条结果，若查不到执行时会报no_data_found
select last_name,salary into v_ename,v_sal
from employees where employee_id=&no;
dbms_output.put_line('雇员名：'||v_ename||' 薪水：'||v_sal);
exception
when no_data_found then
dbms_output.put_line('无查询结果！');
end;
/

输入一个不存在的编号，结果：
无查询结果！

程序走过了异常处理部分代码


创建一个带参数的过程：（无返回）
Sql代码 收藏代码
create procedure pro2 (vname varchar2,newSal number) is
begin
--根据用户名去修改工资
update employees set salary=newSal where last_name=vname;
commit;
end;
/

Procedure created

SQL> exec pro2('Gietz',5555); --调用pro2过程，并传入两个参数

PL/SQL procedure successfully completed
在java程序中调用上面的过程：
Java代码 收藏代码
//1 加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");

//2 得到连接
Connettion cn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:库名称","用户名","密码");

//3 创建CallableStatement
CallableStatement cs=cn.prepareCall("{call pro2(?,?)}");

//4 给问号赋值
cs.setString(1,"Gietz");
cs.setInt(2,5555);

//5 执行
cs.execute();

//6 关闭
cs.close();
cn.close();


编写一个有返回值的过程(单行记录)：
Sql代码 收藏代码
--有输入与输出的存储过程
--in 表示这是输入变量,如果不写默认是in
--out表示这是输出变量
create or replace procedure pro8
(v_no in number,v_name out varchar2) is
begin
--通过编号找到姓名
select last_name into v_name
from employees where employee_id=v_no;
end;
在java程序中调用上面的过程：
Java代码 收藏代码
//1 加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");

//2 得到连接
Connettion cn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:库名称","用户名","密码");

//3 创建CallableStatement
CallableStatement cs=cn.prepareCall("{call pro8(?,?)}");

//4 给问号赋值
cs.setInt(1,206);




//5 说明这是一个返回值,是varchar类型。
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);

//6 执行
cs.execute();



//7 取出返回值
String name=cs.getString(2);

//8 关闭
cs.close();
cn.close();


编写一个有返回值的过程(返回结果集)：
Sql代码 收藏代码
--创建一个包
create or replace package test_package as
type test_cursor is ref cursor;
end ;
Sql代码 收藏代码
--创建一个过程
create or replace procedure pro9(
departID in number,
--定义一个游标类型的返回变量
p_cursor out test_package.test_cursor)
is
begin
--把select语句与游标关联起来
open p_cursor for
select * from employees where department_id=departID;
end;

在java程序中调用上面的过程：
Java代码 收藏代码
//1 加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");

//2 得到连接
Connettion cn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:库名称","用户名","密码");

//3 创建CallableStatement
CallableStatement cs=cn.prepareCall("{call pro9(?,?)}");

//4 给问号赋值
cs.setInt(1,10);

//5 说明这是一个返回值,游标类型的。
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);

//6 执行
cs.execute();

//7 取出返回值
ResultSet rs = (ResultSet )cs.getObject(2);
while(rs.next()){
System.out.println( rs.getInt(1) + " " + rs.getString(2) );
}

//8 关闭
cs.close();
cn.close();


-----------------------------------分页的存储过程 ，过程的高级部分-----------------------------------------
分页的SQL ,取第6-10条记录（两头包含）：
select * from (select rownum rn,t1.* from employees t1 where rownum <=10) where rn>=6;

下面是一个简单的分页，无where条件，无页数越界检查
Sql代码 收藏代码
--创建一个包
create or replace package test_package as
type test_cursor is ref cursor;
end ;

--创建分页过程
create or replace procedure fenye(
v_tableName in varchar2,--表名
v_pageSize in number,--每页显示的记录数
v_pageNum in number,--当前页码
v_countRows out number,--总记录数
v_countPages out number,--总页数
p_cursor out test_package.test_cursor --返回的结果集
) is
v_sql varchar2(1000);
v_begin number:=(v_pageNum-1) * v_pageSize + 1;
v_end number:=v_pageNum*v_pageSize;
begin
v_sql:='select * from (select rownum rn,t1.* from '||v_tableName||' t1 where rownum <='||v_begin||') where rn>='||v_end;
--把游标与select语句关联起来
open p_cursor for v_sql;
--计算总记录数，总页数
v_sql:='select count(*) from '||v_tableName;
execute immediate v_sql into v_countRows; --执行sql
if mod(v_countRows,v_pageSize)=0 then
v_countPages:=v_countRows / v_pageSize;
else
v_countPages:=v_countRows / v_pageSize + 1;
end if;
--关闭游标
close p_cursor;
end;

在java程序中调用上面的过程：
Java代码 收藏代码
//1 加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");

//2 得到连接
Connettion cn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:库名称","用户名","密码");

//3 创建CallableStatement
CallableStatement cs=cn.prepareCall("{call fenye(?,?,?,?,?,?)}");

//4 给问号赋值
cs.setString(1,'employees');

cs.setInt(2,20);

cs.setInt(3,1);

//5 说明这是一个返回值,游标类型的。
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);

//6 执行
cs.execute();

//7 取出返回值
int countRows=cs.getInt(4);//总记录数
int countPages=cs.getInt(5);//总页数
ResultSet rs = (ResultSet )cs.getObject(6);
while(rs.next()){
System.out.println( rs.getInt(1) + " " + rs.getString(2) );
}

//8 关闭
cs.close();
cn.close();

-----------------------------------------函数部分-------------------------------------------
创建函数：
Sql代码 收藏代码
SQL> --函数例子
SQL> --输入雇员的姓名，返回该雇员的年薪
SQL> --函数的头，vname是要传入的型参，返回一个number类型的yearSal变量
SQL> create function fun1(vname varchar2) return number
2 is yearSal number(7,2);
3 begin
4 select salary*12 into yearSal from employees where last_name=vname;
5 return yearSal;--返回结果
6 end;
7 /

Function created


调用函数：

在SQLplus中调用函数：

Java代码 收藏代码
SQL> var income number
SQL> call fun1('Gietz') into:income
2 /

Method called
income
---------
66660



在java程序中调用函数：
Java代码 收藏代码
select fun1('Gietz') from dual;
可以通过rs.getInt(1)得到返回结果


----------------------------------包 部分--------------------------------------------------
包用于在逻辑上组合过程和函数，它由包规范声明和包体两部分组成。
包的规范只包含了过程和函数的说明，没有实现。要在包体中实现之前声明的过程与函数。

创建一个包规范声明：
Sql代码 收藏代码
SQL> --创建一个包pack1
SQL> --这里只起到声明的作用
SQL> create package pack1 is
2 procedure pro2 (vname varchar2,newSal number) ; -- 一个过程的声明
3 function fun1(vname varchar2) return number; -- 一个函数的声明
4 end;
5 /

Package created


创建一个包体：
Sql代码 收藏代码
SQL> create package body pack1 is
2
3 --实现过程，根据用户名去修改工资
4 procedure pro2 (vname varchar2,newSal number) is
5 begin
6 update employees set salary=newSal where last_name=vname;
7 commit;
8 end;
9
10 --实现函数，输入雇员的姓名，返回该雇员的年薪
11 function fun1(vname varchar2) return number
12 is yearSal number(7,2);
13 begin
14 select salary*12 into yearSal from employees where last_name=vname;
15 return yearSal;
16 end;
17 end;
18 /

Package body created


如何调用包中的过程与函数：
就是在过程与函数名前带包名。


------------------------------------触发器 部分------------------------------------------------
触发器是一个隐含的存储过程，一般在insert,update,delete语句执行时触发某个pl/sql块。




-------------------------------------异常处理-----------------------------------------------
预定义异常 ，大约有20多个
no_data_found 没有找到数据时抛出
case_no_found case语句中的when子句没有包含必须的条件分支时抛出
cursor_already_open 重复打开已打开的游标时抛出
dup_val_on_index 在唯一索引的列上插入重复的值时抛出
invaild_cursor 在不合法的游标上操作时会抛出， 如：试图重没有打开游标取数据，或关闭没有打开的游标
invaild_number 无效的数值
too_many_rows 返回的结果是多行，但接收的变量只是单行变量 时抛出
zero_divide 当执行2/0时
value_error 变量的长度不足以容纳实际的数据时招聘

其它预定义异常
login_denide 非法登录时抛出
not_logged_on 用户没登录就执行dml操作时抛出

自定义异常
略。。。
处理导演的例子
begin
......
exception
when no_data_found then
dbms_output.put_line('无查询结果！');

end;
------------------------------------------------------------------------------------