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