Oracle数据库 存储过程（学习-1）

1: Oracle 数据库下的存储过程

1.1 ： PLSQL编程

• PLSQL : producure language / SQL
• 是 Oracle 对 SQL 语言的扩展
• 在SQL 语句中增强了 过程处理语句， 使SQL 语言具备了过程处理能力。

1.2： PLSQL 的结构

• 通过 plSQL Developer 工具 test Window 创建程序模块 或者在 SQL windows 中编写
• 通过在 program windows 中创建 producure 程序
• PLSQL 中不区分大小写
• PLSQL 有三个部分组成
  • 声明部分： declare
  • 可执行部分： begin … end
  • 异常处理部分：

declare
--声明
begin
		-- 可执行部分
		-- 异常处理
end

1.3：PLSQL 中常见的类型分类

1： 数据类型分类

• 普通的数据类型（char , varchar2, date, number, boolean, long）
• 特殊变量类型（引用类型， 记录型变量）

2： 声明变量常用的方式

• 变量名 变量类型（变量长度） ：v_name varchar(20);

3: 变量赋值的方式

• 变量赋值的两种方式
  • 1： 直接赋值： := for example: v_name := ‘zhangwang’;
  • 2: 语句赋值 ， 使用 select … to… : (语法： select 值 into 变量)

4: 引用类型变量

• 变量的类型和长度取决于表中字段的类型和长度
• 通过表名.列名%type 指定类型和长度， 例如： v_name emp.ename%type;

5: 记录型变量

• 接受表中的一整行记录， 相当于Java中的一个对象
• 语法：变量名 表名%ROWTYPE, 例如： v_emp emp%rowtype;
• 使用场景： 一个表中的字段有大量时， 不需要一个一个进行声明：可以使用记录型变量

-- PL_SQL 程序的学习 变量的使用和赋值
declare
  -- 定义普通变量类型
  v_name varchar2(20) := '王记勇';
  v_sal  number;
  v_addr varchar2(200);
  
  -- 定义为引用类型，
  v_name1 emp.ename%type;               
  v_sal1 emp.sal%type;
  
  -- 定义记录型数据类型
  v_emp emp%Rowtype;
begin
 
  -- 普通变量： 赋值
  v_sal  := 7500;

  -- 变量插入赋值
  select '河南省周口市淮阳县' into v_addr from dual;
  select ename, sal into v_name1, v_sal1 from emp where empno = 7369;     -- 从数据库表中查出数据进行赋值
  
  -- 记录型数据赋值
  Select * Into v_emp From emp Where empno = 7369;
  
  -- 进行数据的输出 set serveroutput on: 打开输出对象。
  dbms_output.put_line('姓名1：' || v_emp.ename || ',薪水1：' || v_emp.sal ||',地址：' || v_addr);
end;

---

1. : 流程控制

• 条件分支

  begin
  
  	if condition1 then execu1
  		elsif condition2 then execu2
  		else execu3
     end if;
  end;
  

• 循环

  • Oracle 中有三种循环方式： 这里：loop

  declare
  	-- 声明循环变量
  	v_num number := 1;
  begin
  	loop 
  		exit when  v_num > 10; -- 退出条件
  	end loop;
  	
  	-- 循环自增
  	v_num := v_num+1;
  end;
  

2: 游标

2.1：游标

• 用于临时存储一个查询返回的多行数据（结果集， 类型与 Java 的Jdbc 连接返回的ResultSet 集合），通过遍历游标， 可以逐行访问处理该结果集的数据。
• 游标的使用方式：声明–>打开—>读取—>关闭。

2.2 游标的语法

• 游标的声明：
  • CURSOR 游标名（参数列表） IS 查询语句；
• 游标的打开
  • OPEN 游标名
• 游标的取值
  • FETCH 游标名 INTO 变量列表
• 游标的关闭
  • CLOSE 游标名

2.3 游标的属性

游标的属性	返回值的类型	说明
%Rowcount	整型	获得fetch语句返回的数据行数
%found	布尔型	最近的fetch 语句返回一行数据则为真， 否则为假
%notfound	布尔型	与%Found 属性返回值相反
%isopen	布尔型	游标已经打开时为真，否则为假

2.4： 有参游标

-- 在定义游标时， 有参和无参的区别
declare

cursor c_emp(v_deptno emp.deptno%type) is 
	select ename, sal emp where deptno = v_deptno;

begin
	open c_emp(10);		// 进行参数的传入
	close c_emp;
end
   

---

3： 存储过程

3.1 存储过程的简介

1： 概念：

• 编写的PLSQL 程序可以进行表的操作， 判断， 循环逻辑处理的工作，但无法实现程序的 重复调用。
• PLSQL 是将一个个PLSQL的业务处理过程存储起来进行复用， 这些被存储起来的 PLSQL 程序称之为存储过程
• 代码的复用

2： 存储过程的作用

• 程序开发过程中， 实现一个特定的业务功能， 会向数据库进行多次的连接和关闭（连接和关闭很是消耗资源）， 需要对数据库进行多次I/O 读写， 性能较低。
  • 解决： 业务实现放到 PLSQL中， 在应用程序中调用 PLSQL 就可以连接关闭 一次数据库，提高效率。
• Oracle官方建议：能够让数据库操作的不要放到程序中， 在数据库中实现基本不会出现错误， 在程序中可能会存在错误，（因为： 在数据库中操作数据时， 可以有一定的日志恢复功能）

---

3.2 : 存储过程的语法

1: 语法

create or replace procedure 过程名称（参数列表） is
begin

end(存储过程名称)；

2： 存储过程的分类

• 不带参数

  -- 不带参数的存储过程
  create or replace procedure P_Prictise_test1 as           -- is 可以替换为 as
  -- 可以直接进行类型的声明
  v_addr Varchar(200) ;
  
  Begin
         Select  '河南省周口市淮阳县'  into v_addr from dual;
        dbms_output.put_line('hello world ');
        dbms_output.put_line('v_addr: ' || v_addr);
  end P_Prictise_test1;
  

• 带参数

  -- 带参数的存储过程
  create or replace procedure P_Prictise_test2(v_empno In emp.empno%Type) as
  
  v_name emp.ename%Type;
  v_sal emp.sal%Type;
  
  -- 定义一个参数游标
  Cursor c_emp(v_deptno emp.deptno%Type) Is 
         Select  ename, sal From emp Where deptno = v_deptno;
         
  begin
    Select ename, sal Into v_name, v_sal From emp Where empno = v_empno;
    dbms_output.put_line('姓名:' || v_name || '薪水' || v_sal );
    
    -- 读取游标中的数据
    Open c_emp(10);
    Loop
      Fetch c_emp Into v_name, v_sal ;
       dbms_output.put_line('姓名:' || v_name || '薪水' || v_sal );
      Exit When c_emp%Notfound;
    End Loop;
    Close c_emp;
    
  end p_prictise_test2;
  

• 带输入和输出参数

– 含有 输入和输出参数的存储过程
create or replace procedure p_prictise_test3(v_empno In emp.empno%Type, v_sal Out emp.sal%Type) As

begin
Select sal Into v_sal From emp Where empno = v_empno;
end p_prictise_test3;
```

3：调用 producure

• 通过程序调用

  Declare
  v_sal emp.sal%Type;
  
  begin
    -- Test statements here
        P_prictise_test1;
        P_prictise_test2(7369);
        p_prictise_test3(7369,v_sal );
        dbms_output.put_line('传出参数值' || v_sal);
  end;
  

• 通过窗口调用