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

Sql代码| 伦理电影  

1. set serveroutput on;  
2. declare  
3.   --定义一个pl/sql记录类型emp_record_type  
4.   type emp_record_type is record(  
5.        name employees.last_name%type,  
6.        salary employees.salary%type,  
7.        hire_date employees.hire_date%type  
8.   );  
9.   --定义一个emp_record_type类型的变量v_record  
10.   v_record emp_record_type;  
11.     
12. begin  
13.   select last_name,salary,hire_date into v_record  
14.   from employees where employee_id=206;  
15.   dbms_output.put_line('姓名：'||v_record.name  
16.        ||' 工资：'||v_record.salary  
17.        ||' 入职日期：'||v_record.hire_date  
18.   );  
19. end;  

结果：

姓名：Gietz 工资：5555 入职日期：07-6月 -94
PL/SQL procedure successfully completed

 

 

 

2.2 复合类型-PL/SQL表

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

 

Sql代码| 点击打开链接  

1. declare  
2.   --定义一个pl/sql表类型table_type，名称随意取  
3.   --table_type类型的变量用于存放employees.last_name%type类型的数据  
4.   type table_type is table of employees.last_name%type  
5.   --数组的下标是整数，可是为负  
6.   index by binary_integer;  
7.     
8.   --声明一个table_type类型的变量v_table  
9.   v_table table_type;  
10. begin  
11.   select last_name into v_table(0) from employees   
12.   --如果返回多行，会报错，解决方法见后面的参照类型变量  
13.   where employee_id=206;  
14.   dbms_output.put_line('姓名：'||v_table(0));  
15. end;   

 

2.3 复合类型-嵌套表   用的少，略...

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

 

 

3、参照类型 reference

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

 

3.1、参照类型 reference - cursor游标变量

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

 

Sql代码  

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

Sql代码  

1.   --定义一个游标类型test_cursor_type，它是类型。  
2.   type test_cursor_type is ref cursor;  
3.     
4.   --定义一个游标变量  
5.   test_cursor test_cursor_type;  
6.     
7.   --定义一般的变量  
8.   v_name employees.last_name%type;  
9.   v_sal  employees.salary%type;  
10.     
11. begin  
12.   --把test_cursor游标变量与一个select语句结合起来  
13.   --&no 要输入值给no变量  
14.   open test_cursor for select last_name,salary  
15.   from employees where department_id=&no;  
16.     
17.   --循环取出  
18.   loop  
19.        fetch test_cursor into v_name,v_sal;  
20.        --判断test_corsor是否为空,为空时退出循环  
21.        exit when test_cursor%notfound;  
22.        dbms_output.put_line('姓名：'||v_name||' 工资：'||v_sal);  
23.   end loop;  
24.     
25.   --关闭游标  
26.   close test_cursor;  
27. 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代码  

1. create or replace procedure pro1 is  
2. begin  
3. --单行注释    
4. /*多行注释*/  
5. insert into t7 values(2,'apple');  
6. commit;  
7. end;  
8. /  

 

 

查看错误信息：

Sql代码  

1. show error;  

 

 

调用过程：

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

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

 

删除过程：

drop procedure 过程名;

 

 

 

块的结构说明：

declear

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

begin

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

exception

/*异常处理部分*/

end;

 

 

打开输出选项

  

Sql代码  

1. set serveroutput on     
2. begin  
3. dbms_output.put_line('hello ,world');  
4. end;  
5. /  

 

输出：hello ,world

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

 

 

在过程中定义一个变量：

 

Sql代码  

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

  

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

 

输入一个不存在编号，结果：

ORA-01403: 未找到数据
ORA-06512: 在 line 7

 

 

在过程中处理异常：

 

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

Sql代码  

1. declare  
2.   v_ename varchar2(25);--定义字符串变量  
3.   v_sal number(7,2);   --定义一个数值变量  
4. begin  
5.   --从输入框输入变量no，查询的结果为变量赋值，   
6.   --只适合查出一条结果，若查不到执行时会报no_data_found  
7.   select last_name,salary into v_ename,v_sal   
8.   from employees where employee_id=&no;  
9.   dbms_output.put_line('雇员名：'||v_ename||' 薪水：'||v_sal);  
10. exception  
11.   when no_data_found then  
12.        dbms_output.put_line('无查询结果！');  
13. end;  
14. /  

  

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

 

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

 

 

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

Sql代码  

1. create procedure pro2 (vname varchar2,newSal number) is  
2. begin  
3. --根据用户名去修改工资  
4. update employees set salary=newSal where last_name=vname;  
5. commit;  
6. end;  
7. /  
8.    
9. Procedure created  
10.    
11. SQL> exec pro2('Gietz',5555);   --调用pro2过程，并传入两个参数  
12.    
13. PL/SQL procedure successfully completed  

在java程序中调用上面的过程： 

Java代码  

1. //1 加载驱动  
2. Class.forName("oracle.jdbc.driver.OracleDriver");   
3.   
4. //2 得到连接  
5. Connettion cn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:库名称","用户名","密码");  
6.   
7. //3 创建CallableStatement  
8. CallableStatement cs=cn.prepareCall("{call pro2(?,?)}");   
9.   
10. //4 给问号赋值  
11. cs.setString(1,"Gietz");  
12. cs.setInt(2,5555);  
13.    
14. //5 执行  
15. cs.execute();  
16.    
17. //6 关闭  
18. cs.close();  
19. cn.close();  

 

 

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

Sql代码  

1. --有输入与输出的存储过程  
2. --in 表示这是输入变量,如果不写默认是in  
3. --out表示这是输出变量  
4. create or replace procedure pro8  
5. (v_no in number,v_name out varchar2) is  
6. begin  
7.       --通过编号找到姓名  
8.       select last_name into v_name   
9.       from employees where employee_id=v_no;  
10. end;  

在java程序中调用上面的过程： 

Java代码  

1. //1 加载驱动  
2. Class.forName("oracle.jdbc.driver.OracleDriver");   
3.   
4. //2 得到连接  
5. Connettion cn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:库名称","用户名","密码");  
6.   
7. //3 创建CallableStatement  
8. CallableStatement cs=cn.prepareCall("{call pro8(?,?)}");   
9.   
10. //4 给问号赋值  
11. cs.setInt(1,206);  
12.   
13.   
14.   
15.   
16. //5 说明这是一个返回值,是varchar类型。  
17. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);  
18.    
19. //6 执行  
20. cs.execute();  
21.   
22.   
23.   
24. //7 取出返回值  
25. String name=cs.getString(2);  
26.    
27. //8 关闭  
28. cs.close();  
29. cn.close();  

  

 

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

Sql代码  

1. --创建一个包  
2. create or replace package test_package as  
3.        type test_cursor is ref cursor;  
4. end ;  

Sql代码  

1. --创建一个过程  
2. create or replace procedure pro9(  
3.        departID in number,  
4.        --定义一个游标类型的返回变量  
5.        p_cursor out test_package.test_cursor)   
6. is  
7. begin  
8.        --把select语句与游标关联起来  
9.        open p_cursor for   
10.        select * from employees where department_id=departID;  
11. end;  

 

在java程序中调用上面的过程： 

Java代码  

1. //1 加载驱动  
2. Class.forName("oracle.jdbc.driver.OracleDriver");   
3.   
4. //2 得到连接  
5. Connettion cn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:库名称","用户名","密码");  
6.   
7. //3 创建CallableStatement  
8. CallableStatement cs=cn.prepareCall("{call pro9(?,?)}");   
9.   
10. //4 给问号赋值  
11. cs.setInt(1,10);  
12.   
13. //5 说明这是一个返回值,游标类型的。  
14. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);  
15.    
16. //6 执行  
17. cs.execute();  
18.   
19. //7 取出返回值  
20. ResultSet rs = (ResultSet )cs.getObject(2);  
21. while(rs.next()){  
22.      System.out.println( rs.getInt(1) + " " + rs.getString(2) );  
23. }  
24.    
25. //8 关闭  
26. cs.close();  
27. cn.close();  

  

 

-----------------------------------分页的存储过程 ，过程的高级部分-----------------------------------------

分页的SQL ,取第6-10条记录（两头包含）：

select * from (select rownum rn,t1.*  from  employees t1 where rownum <=10) where rn>=6;

 

下面是一个简单的分页，无where条件，无页数越界检查

Sql代码  

1. --创建一个包  
2. create or replace package test_package as  
3.        type test_cursor is ref cursor;  
4. end ;  
5.   
6. --创建分页过程  
7. create or replace procedure fenye(  
8.        v_tableName in varchar2,--表名  
9.        v_pageSize in number,--每页显示的记录数  
10.        v_pageNum in number,--当前页码  
11.        v_countRows out number,--总记录数  
12.        v_countPages out number,--总页数  
13.        p_cursor out test_package.test_cursor --返回的结果集  
14. ) is  
15. v_sql varchar2(1000);  
16. v_begin number:=(v_pageNum-1) * v_pageSize + 1;  
17. v_end number:=v_pageNum*v_pageSize;  
18. begin  
19.       v_sql:='select * from (select rownum rn,t1.*  from  '||v_tableName||' t1 where rownum <='||v_begin||') where rn>='||v_end;  
20.       --把游标与select语句关联起来  
21.       open p_cursor for v_sql;  
22.       --计算总记录数，总页数  
23.       v_sql:='select count(*) from '||v_tableName;  
24.       execute immediate v_sql into v_countRows; --执行sql  
25.       if mod(v_countRows,v_pageSize)=0 then  
26.          v_countPages:=v_countRows / v_pageSize;  
27.       else  
28.          v_countPages:=v_countRows / v_pageSize + 1;  
29.       end if;  
30.       --关闭游标  
31.       close p_cursor;  
32. end;  

 

在java程序中调用上面的过程： 

Java代码  

1. //1 加载驱动  
2. Class.forName("oracle.jdbc.driver.OracleDriver");   
3.   
4. //2 得到连接  
5. Connettion cn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:库名称","用户名","密码");  
6.   
7. //3 创建CallableStatement  
8. CallableStatement cs=cn.prepareCall("{call fenye(?,?,?,?,?,?)}");   
9.   
10. //4 给问号赋值  
11. cs.setString(1,'employees');  
12.   
13. cs.setInt(2,20);  
14.   
15. cs.setInt(3,1);  
16.   
17. //5 说明这是一个返回值,游标类型的。  
18. cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);  
19. cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);  
20. cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);  
21.    
22. //6 执行  
23. cs.execute();  
24.   
25. //7 取出返回值  
26. int countRows=cs.getInt(4);//总记录数  
27. int countPages=cs.getInt(5);//总页数  
28. ResultSet rs = (ResultSet )cs.getObject(6);  
29. while(rs.next()){  
30.      System.out.println( rs.getInt(1) + " " + rs.getString(2) );  
31. }  
32.    
33. //8 关闭  
34. cs.close();  
35. cn.close();  

   

-----------------------------------------函数部分-------------------------------------------

创建函数：

Sql代码  

1. SQL> --函数例子  
2. SQL> --输入雇员的姓名，返回该雇员的年薪  
3. SQL> --函数的头，vname是要传入的型参，返回一个number类型的yearSal变量  
4. SQL> create function fun1(vname varchar2) return number  
5.   2  is yearSal number(7,2);  
6.   3  begin  
7.   4  select salary*12 into yearSal from employees where last_name=vname;  
8.   5  return yearSal;--返回结果  
9.   6  end;  
10.   7  /   
11.     
12.   Function created  

 

 

调用函数：

 

在SQLplus中调用函数：

 

Java代码  

1. SQL> var income number  
2. SQL> call fun1('Gietz') into:income  
3.   2  /  
4.    
5. Method called  
6. income  
7. ---------  
8. 66660  

 

 

 

在java程序中调用函数：

Java代码  

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

 

 

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

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

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

 

创建一个包规范声明：

Sql代码  

1. SQL> --创建一个包pack1  
2. SQL> --这里只起到声明的作用  
3. SQL> create package pack1 is  
4.   2        procedure  pro2 (vname varchar2,newSal number) ; -- 一个过程的声明  
5.   3        function fun1(vname varchar2) return number;     -- 一个函数的声明  
6.   4  end;  
7.   5  /  
8.    
9. Package created  

 

 

创建一个包体：

Sql代码  

1. SQL> create package body pack1 is  
2.   2    
3.   3    --实现过程，根据用户名去修改工资  
4.   4    procedure pro2 (vname varchar2,newSal number) is  
5.   5    begin  
6.   6    update employees set salary=newSal where last_name=vname;  
7.   7    commit;  
8.   8    end;  
9.   9    
10.  10    --实现函数，输入雇员的姓名，返回该雇员的年薪  
11.  11    function fun1(vname varchar2) return number  
12.  12    is yearSal number(7,2);  
13.  13    begin  
14.  14    select salary*12 into yearSal from employees where last_name=vname;  
15.  15    return yearSal;  
16.  16    end;  
17.  17  end;  
18.  18  /  
19.    
20. 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;

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