Oracle存储过程笔记

1：PL/SQL编程

1.1：什么是PL/SQL

1.PL/SQl (Procedure Language/SQL)
2:PL/SQL 是Oracle对SQL语言的过程化扩展
3：指在SQL命令语言中增加了过程处理语句（如分支，循环等），是SQL语句具有过程处理的能力。

1.2：程序结构

注： PL/SQL语言的大小写是不区分的
Pl/SQL 可以分为三个部分：声明部分，可执行部分，异常处理部分。

declare 
  -- 声明部分，可声明变量，游标
  -- 如果没有变量，游标等存在，声明部分可省略
  i integer;
begin
  -- 执行部分
  -- 异常处理部分
end;

1.3：PL/SQL的HolleWord

begin
  dbms_output.put_line('HolleWord');
end;

dbms_output.put_line是Oracle的输出语句，在控制台上输出语句，并换行。不换行为dbms_output.put。

1.4：PL/SQL中的变量声明及赋值

声明方式： 变量名 变量类型 （变量长度）

1.4.1：普通变量

普通变量就是常用的数据库变量类型，如number，varchar2等。
变量的赋值方式：

• 直接赋值：变量名 变量类型 （变量长度）:= 值
  例：v_name varchar2(20) := ‘李四’;

• 语句赋值：select 值 into 变量 from 表；
  例：select ‘李四’ into v_name from dual;
  注： dual是Oracle中的一张伪表，只存在一个字段一条数据，并且每个用户都可以使用，当我们不希望通过具体的表去查询相应的数据的时候，可以使用dual。

1.4.2：特殊变量

特殊变量指的是引用型变量和记录型变量。
引用型变量： 变量的类型和长度取决于表中字段的类型和长度。通过

表名.列名%type

指定变量的类型和长度。
例： v_name stu.stu_name%type
其中：v_name 为属性名称
stu.stu_name%type 为v_name的类型
注： 当我们的变量有多个，并且赋值时通过语句赋值的时候，我们的变量的顺序需要和select语句中的字段的顺序一致。
记录型变量： 接受表中的一整行记录，相当于java中的一个对象。通过

表名%rowtype

注： 当我们一个表中有很多的字段，在编写程序的时候如果一个个的去进行变量的定义与引用，过于麻烦，我们可以使用记录型变量。但是如果表中字段只有少量的被引用，此时如果过分的依赖于记录型变量给我们带来的便捷性，反而会加重我们的数据库的压力。（记录型变量的赋值需要依赖于select * ）
例： student stu%rowtype --定义
select * into student from stu where name = ‘张三’; --赋值

当我们需要取出对应的字段的时候，通过

变量.字段

取出。
例： dbms.output.put_line(student.name);

1.5:流程控制

1.5.1：条件分支

语法：

begin
  if 条件1 then
    执行1;
  elsif 条件2 then
    执行2;
  else
    执行3;
  end if;
end;

1.5.2：循环分支

while循环

declare
  i number := 1;
begin
  while i <= 10 loop
    dbms_output.put_line(i);
    i := i + 1;
  end loop;
end;

loop循环

declare
  i number := 1;
begin
  loop
    exit when i > 10;
    dbms_output.put_line(i);
    i := i + 1;
  end loop;
end;

for循环

declare
  i number := 1;
begin
  for i in 1 .. 10 loop
    dbms_output.put_line(i);
  end loop;
end;

1.6：游标

游标是用于临时存储一个查询返回的多行数据（结果集），通过遍历游标，可以逐行访问处理该结果集中的数据。

游标的使用方式： 声明 --> 打开 --> 读取 --> 关闭

declare
  cursor 游标名(参数 参数类型) is 查询语句; --定义游标
begin
  open 游标名; --开启游标

  fetch 游标名
    into 变量列表; --获取游标中每行的数据

  close 游标名; --关闭游标
end;

游标一般配合循环来使用，格式如下：

declare
  cursor 游标名(参数 参数类型) is 查询语句; --定义游标
begin
  open 游标名; --开启游标
  loop
    --开启循环
    fetch 游标名
      into 变量列表; --获取游标中每行的数据
    exit when 游标名%NotFound; --每循环一次会取出一条数据，当没有数据的时候结束循环
    --执行语句放在跳出循环的条件语句前后是有差别的，需要注意
    close 游标名; --关闭游标
  end;

游标的参数会带入定义游标后面的那条查询语句的条件当中。举例如下：

cursor student(v_name stu.name%type) is select * from stu where name = v_name;

游标的属性

属性	类型	解释
%rowcount	整数	获取fecth语句返回的数据行数
%found	布尔	最近的fecth语句返回一条数据返回真，否则为假
%notfound	布尔	与上一条相反
%isopen	布尔	游标打开为真，关闭为假

注：%NOTFOUND一般用于游标找不到元素的时候退出循环使用，默认值为false。

1.7：存储过程

1.7.1：什么是存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。

简单的说就是专门干一件事一段sql语句。

可以由数据库自己去调用，也可以由java程序去调用。

在oracle数据库中存储过程是procedure。

1.7.2：为什么要写存储过程

1.效率高

存储过程编译一次后，就会存到数据库，每次调用时都直接执行。而普通的sql语句我们要保存到其他地方（例如：记事本 上），都要先分析编译才会执行。所以想对而言存储过程效率更高。

2.降低网络流量

存储过程编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的sql语句。

3.复用性高

存储过程往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该存储过程。

4.可维护性高

当功能要求发生小的变化时，修改之前的存储过程比较容易，花费精力少。

5.安全性高

完成某个特定功能的存储过程一般只有特定的用户可以使用，具有使用身份限制，更安全。

1.7.3：存储过程的定义与使用

a:不带参的存储过程

create or replace procedure p_print is/as
参数1 参数类型
参数2 参数类型
.....
begin
  --业务逻辑
   exception --异常处理
end p_print;

注：参数可以是普通参数也可以游标，上例中的p_print为存储过程的名称。其中的is和as取其一就行。
b:带参的存储过程

create or replace procedure p_print (
参数1 in 参数类型，
参数2 out 参数类型
) is/as
参数1 参数类型
参数2 参数类型
.....
begin
  --业务逻辑
  exception --异常处理
end p_print;

上面脚本中：
其中参数IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去

1.7.4：相关运算符

这里s，m，n是变量，类型是number；

分类	运算符	含义	表达式
算术运算符	+	加	s := 2 + 2;
	-	减	s := 3 – 1;
	*	乘	s := 3 * 1;
	/	除	s := 6 / 2;
	mod(,)	取模	s := mod(3,2);
	**	平方	10**2=100
关系运算符	=	等于	s=2
	<>或!=或~=	不等于	s!=2
	<	小于	s<2
	>	大于	s>2
	<=	小于等于	s<=2
	>=	大于等于	s>=2
比较运算符	LIKE	满足匹配为true	‘li’ like ‘%i’返回true
	BETWEEN	是否处于一个范围中	2 between 1 and 3 返回true
	IN	是否处于一个集合中	‘x’ in (‘x’,’y’) 返回true
	IS NULL	判断变量是否为空	若：n:=3,n is null,返回false
逻辑运算符	AND	逻辑与	s=3 and c is null
	OR	逻辑或	s=3 or c is null
	NOT	逻辑非	not c is null
其他	:=	赋值	s := 0;
	..	范围	1..9，即1至9范围
	||	字符串连接	‘hello’||’world

1.8：java程序调用存储过程

现在先在PL/SQL中定义存储过程如下：

create or replace procedure p_in_out(i in integer,j out integer) is
begin
  select 1 into j from dual where 2=i;
end p_in_out;

当输入的参数i为2的时候将1赋值给j进行输出。

在java代码中进行调用
引入驱动jar包

    <dependency>
        <groupId>com.oracle.ojdbc</groupId>
        <artifactId>ojdbc8</artifactId>
        <version>19.3.0.0</version>
    </dependency>

public class test {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //thin为协议名，localhost为ip，1521为端口，ORCW为库名
        String url = "jdbc:oracle:thin:@localhost:1521:ORCW";
        String user = "admin";
        String password = "admin";
        Connection connection = DriverManager.getConnection(url, user, password);
        //调用存储过程
        String sql = "{call p_in_out(?,?)}";
        CallableStatement call = connection.prepareCall(sql);
        //设置输入参数  注意参数是从1开始的
        call.setInt(1,2);
        //注册输出参数
        call.registerOutParameter(2, OracleTypes.INTEGER);
        //执行存储过程
        call.execute();
        int anInt = call.getInt(2);
        System.out.println(anInt);
        call.close();
        connection.close();
    }
}