Oracle 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记录类型

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表

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

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语句结合了。

--编写一个pl/sql块，输入部门编号，显示本部门所有员工的姓名与工资
declare

 --定义一个游标类型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 -- 被循环部分 -- endloop 满足条件才执行

for i in reverse 1..10 loop

被循环部分

end loop;

顺序结构:

goto meta_name; 跳到meta_name处

<<meta_name>>

null;语句 ， 什么也不做

-------------------------------------存储过程部分-----------------------------------------------

创建一个过程:

create or replace procedure pro1 is
begin
--单行注释  
/*多行注释*/
insert into t7 values(2,'apple');
commit;
end;
/

查看错误信息：

show error;

调用过程：

exec 过程名 (参数值1，参数值2...);

call 过程名 (参数值1，参数值2...);

删除过程：

drop procedure 过程名;

块的结构说明：

declear

/*定义部分--常量，变量，游标，复杂数据类型*/

begin

/*执行部分--要执行的PL/SQL语句和SQL语句*/

exception

/*异常处理部分*/

end;

打开输出选项

set serveroutput on   
begin
dbms_output.put_line('hello ,world');
end;
/

输出：hello ,world

要打开输出选项才能看到。

在过程中定义一个变量：

声明部分略，直接写了执行部分
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

在过程中处理异常：

声明部分略，直接写了执行部分

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;
/

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

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

创建一个带参数的过程：（无返回）

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程序中调用上面的过程：

//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();

编写一个有返回值的过程(单行记录)：

--有输入与输出的存储过程
--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程序中调用上面的过程：

//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();

编写一个有返回值的过程(返回结果集)：

--创建一个包
create or replace package test_package as
 type test_cursor is ref cursor;
end ;

--创建一个过程
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程序中调用上面的过程：

//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条件，无页数越界检查

--创建一个包
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程序中调用上面的过程：

//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> --函数的头，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中调用函数：

SQL> var income number
SQL> call fun1('Gietz') into:income
  2  /
 
Method called
income
---------
66660

在java程序中调用函数：

select fun1('Gietz') from dual;
可以通过rs.getInt(1)得到返回结果

----------------------------------包 部分--------------------------------------------------

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

包的规范只包含了过程和函数的说明，没有实现。要在包体中实现之前声明的过程与函数。

创建一个包规范声明：

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> 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;

------------------------------------------------------------------------------------