Oracle PL/SQL 学习

编写一个存储过程，该过程可以向某表中添加数据 . 

1. 创建一个简单的表

create table mytest(
       name varchar2(30),
       passwd varchar2(30)
);

 

2. 创建过程  

--replace 表示如果有sp_pro,则替换
create  or replace procedure sp_pro is
begin
--执行部分
insert into mytest ('crazygrass','crazy');
end;
/

 执行上面的过程时，会报错，可以同过 show error 来查看报错信息。更正如下：

--replace 表示如果有sp_pro,则替换
create  or replace procedure sp_pro is
begin
--执行部分
insert into mytest values('crazygrass','crazy');
end;
/

 

如何调用该过程。

1)        exec 过程名（参数值 1 ，参数值 2,…. ）

2)        call 过程名（参数值 1 ，参数值 2,…. ）

执行以下

 exec sp_pro;
-- 然后
select * from mytest;

 

•   简单分类

 ----- --  过程（存储过程）

 -------   函数

块（编程） -------    触发器

 -------   包

•   编写规范

1)        注视

单行注释 –

select * from emp where empno=7788;-- 取得员工信息

多行注释

/*…..*/ 来划分

2)        标志符号的命名规范

1.       当定义变量时，建议使用 v_ 作为前缀 v_sal

2.       当定义常量时，建议使用 c 最为前缀 c_rate

3.       当定义游标时，建议使用 _cursor 作为后缀 emp_cursor

4.       当定义例外时，建议使用 e 作为前缀 e_error

• 块

  块（ block ）是 pl/sql 的基本程序单元，编写 pl/sql 程序实际上就是编写 pl/sql 块。要完成相对简单的应用功能，可能只需要编写一个 pl/sql 块；但是如果想要实现发杂的功能，可能需要在一个 pl/sql 块中嵌套其他的 pl/sql 。

• 块结构示意图

        Pl/sql 块由三个部分构成：定义部分，执行部分，例外处理部分

     如下所示：

        declear

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

              begin

/* 执行部分 ---- 要执行的 pl/sq 和 sql 语句 */

exception

/* 例外处理部分 --- 处理运行的各种错误 */

          end;

       declear

  定义部分是可选的

   exception

  例外部分也是可选的

 

实例 1- 只包括执行部分的 pl/sql 块

打开输出选项

set serveroutput on
 begin
   dbms_output.put_line(‘hello’);
end;

 

实例 2— 包含定义部分和执行部分的 pl/sql 块

 

--有定义和执行部分的块
declare 
--定义变量
   v_name varchar2(5); 
begin
--执行部分
   select ename into v_name from emp where empno=&no;
--在控制台显示用户名
   dbms_output.put_line('用户名是:'||v_name);
end;

 当有两个或者多个变量需要赋值时，注意他们赋值的顺序。

--有定义和执行部分的块
declare 
--定义变量
   v_name varchar2(5); 
   v_sal  number(7,2);
begin
--执行部分
   select ename,sal into v_name, v_sal from emp where empno=&no;
--在控制台显示用户名
   dbms_output.put_line('用户名是:'||v_name||' 工资：'||v_sal);
end;

 

相关说明：&表示从控制台输入。

 

 

• 实例 3- 包含定义部分，执行部分和例外处理部分 为了避免 pl/sql 程序的运行错误，提高 pl/sql 的健壮性，应该对可能的错误进行处理，这个很有必要：
• 1)  比如在实例 2 中，如果输入了不存在的雇员号，应当做例外处理
• 2)   有时出现异常，希望用另外的逻辑处理我们看看如何完成 1 的要求

--有定义和执行部分的块
declare 
--定义变量
   v_name varchar2(5); 
   v_sal  number(7,2);
begin
--执行部分
   select ename,sal into v_name, v_sal from emp where empno=&no;
--在控制台显示用户名
   dbms_output.put_line('用户名是:'||v_name||' 工资：'||v_sal);
--异常处理
exception 
when no_data_found then
dbms_output.put_line('No datas!');
end;

 

   相关说明： oracle 事先预定义了一些例外， no_data_found 就是找不到数据的例外。

 

  过程

过程用于执行特定的操作。当建立过程时，既可以指定输入参数（ in ），也可以制定输出参数 (out) 。通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在 sqlplus 中可以使用 create procedure 命令来建立工程。

实例如下：

1 请考虑编写一个存储过程 ，可以输入雇员号，新工资，可修改雇员的工资

2 如何调用过程有两种方法

3 如何在 java 程序中调用一个存储过程

 

特别说明：

对于过程我们会在以后给大家详细具体的介绍，现在先介绍个概念。

Java 调用存储过程：

 

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class TestOracleProcedure {

	public static void main(String[] args) {
		try {
                        //1.加载驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2.获得连接
                        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ora92","scott","tiger");
			//3.创建CallableStatement
			CallableStatement cs = conn.prepareCall("{call sp_pro4(?,?)}");
			//4.给？赋值
			cs.setString(1, "alex");
			cs.setInt(2, 10);
			//5.执行
			cs.execute();
			cs.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		print("ok!");
	}
	
	public static void print(Object o){
		System.out.println(o);
	}
}

• 函数

函数用于放回特定的数据，当建立函数时，在函数头部必须包含 return 子句，而在函数体内必须包含 return 语句返回的数据。我们可以使用 create function 来建立函数，实际案例：

--输入雇员的姓名，返回雇员的年薪

create function sp_fun2(spName varchar2) return

number is annual number(7,2);

begin

--执行部分

select sal*12+nvl(comm,0) into annual from emp where ename=spName;

return annual;

end;

--通过sqlplus调用

Sql>var money number;

Sql>call sp_fun2(‘alex’) into:money;

Sql>print money;

• 包

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

  1 我们可以使用 create package 命令来创建包：

--创建一个包

--声明该包有个过程

create package sp_package is

procedure update_sal(name varchar2,newSal number);

function annual_income(name varchar2) return number;

end;

 

2 建立包体可以使用 create package body 命令

 

--给包 sp_package 实现包体
create or replace 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 number;
begin
select sal*12+nvl(comm,0) into annual from emp
where ename=name;
return annual;
end;
end; 

3 如何调用包的过程或是函数

  当调用包的过程或函数时，在过程和函数前需要带有报名，如果要访问其他方案的包，还需要在报名前方加上方案名。

如：

  Sql> call sp_package.update_sal(‘alex’,55);

特别说明：

包是 pl/sql 中非常重要的部分，我们可以在使用过程分页时，将再次体验它的威力。

•   触发器

触发器是指银行的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括 insert ， update ， delete 语句，而触发操作实际就是一个 pl/sql 块。可以使用 create trigger 来建立触发器

 

特别说明：

  触发器 是非常非常重要的。

•   介绍

  在编写 pl/sql 程序时，可以定义变量和常量；在 pl/sql 程序中包括有：

1.         标量类型

标量案例 ：

1.       定义一个边长字符串

V_name varhcar2(10);

2.       定义一个小数 范围 -9999..99~9999.99

v_sal number(6,2)

3.       定义一个小数并给一个初始值为 5.4:= 是 pl/sql 的赋值符号

V_sal2 number(6,2):=5.4;

4.       定义一个日期类型的数据

V_hiredate date;

5.       定义一个布尔变量，不能为空，初始值为 false

V_valid boolean not null default fase;

 

输入员工的号，显示雇员姓名，工资，个人所得税（税率 0.03 ）为例。

--create procedure tax is 
declare
c_tax_rate number(3,2):=0.03;
--用户名
v_name varchar2(20);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
   select ename,sal into v_name,v_sal 
   from emp
   where empno=&id;
--计算所得税
v_tax_sal := v_sal * c_tax_rate;  
dbms_output.put_line('姓名：'||v_name||' 工资：'||v_sal||'交税：'||v_tax_sal);
end;

  注意：定义标量时，如果取回的数据长度大于定义的长度，则会发生缓存区溢出。解决方法，更改为这样 v_name emp.ename% type ; 不仅可以解决问题，而且还可以提高效率。

2.         复合类型

用于存放多个值的变量。主要包括以下几种：

1 pl/sql 记录（常用）

类似与高级语言的结构体 ，需要注意的是当引用 pl/sql 记录成员时，必须要加记录变量作为前缀（记录变量 . 记录成员）如下：

 

--pl/sql 记录实例
declare
--定义一个pl/sql记录类型 emp_record_type,类型包含三个字段
type emp_record_type is
record(name emp.ename%type,
       salary emp.sal%type,
       title emp.job%type);
--定义了一个sp_record变量，这个变量的类型是emp_record_type      
sp_record emp_record_type;
begin
select ename,sal,job into sp_record
from emp where empno=7698;
dbms_output.put_line('姓名'||sp_record.name);
end;

3 .pl/sql 表（常用）

  相当于高级语言中的数据。但是需要注意的是，在高级语言中数组的下标不能为负数，而 pl/sql 是可以为负数的，并且表元素的下标没有限制。实例如下：

--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_type 类型的变量
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7698;
dbms_output.put_line('员工姓名：'||sp_table(0));
end;

     一次只能返回一行，那么怎么解决这个问题呢？ 那么就要使用我们的参照变量类型。

 

4. 参照类型

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

1.       参照变量 -ref cursor 游标变量

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

1 请使用 pl/sql 编写一个块，可以输入部门号，并显示该部门所有员工姓名和对应的工资。

 

 

--使用pl/sql编写一个块，可以输入部门号，并显示该部门所有姓名和工资。
declare
--定义一个游标
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义变量
v_name 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_name,v_sal;
     --判断是否test_cursor为空
     exit when test_cursor%notfound;
     dbms_output.put_line('名字:'||v_name);
end loop;
end;