PL/SQL学习与总结

PL/SQL

1 概述

1. 什么是PL/SQL

   • Procedure Language/SQL
   • 是oracle对sql语言的过程化拓展
   • 指在SQL命令语言中增加了过程处理语句（分支，循环等），使SQL语言具备过程处理能力。

2. 程序结构

   通过PLSQL Developer工具Test Windows创建 程序模板

   PL/SQL可以分为三个部分：声明部分，可执行部分，异常处理部分。

   大小写不区分

   -- Created on 2020/5/24 by PSZ 
   declare 
     -- Local variables here 声明变量和游标，如果没有可以省略
     i integer;
   begin
     -- Test statements here 执行语句，异常处理
     dbms_output.put_line('hello world');
     
   end;
   

   如果在SQL Plus中执行没有结果，需要开启set serveroutput on

2 变量

PL/SQL 变量分为两大类型：

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

变量声明方式语法

变量名 变量类型（长度）       表如 v_name varchar2(20)

普通变量

• 普通类型包括：char，varchar2，date，number，boolean，long

• 赋值方式

1. 直接赋值 ： := (比如 v_name := ‘张三’ )
2. 语句赋值 ： select 值 into 变量

-- Created on 2020/5/24 by PSZ 
declare 
  -- 声明变量
  v_name varchar2(20):='张三';
  v_addr varchar2(200);
  v_salary number;
  
begin
  dbms_output.put_line('hello world');
  --直接赋值
  v_salary :=1000;
  --语句赋值
  select '江苏南京' into v_addr from dual;
  --打印
  dbms_output.put_line('姓名:'||v_name||',薪水:'||v_salary||',地址:'||v_addr);
  
end;

备注：控制台打印语句dbms_output.put_line()，字符串拼接||

引用型变量

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

【示例】: 查询emp表中7777号员工的个人信息

declare 

  --声明引用变量
  v_name emp.ename%TYPE;
  V_salary emp.sal%TYPE;
  
begin

  --查询
  select ename,sal into v_name,v_salary from emp where empno = 7777;
  
  --打印
  dbms_output.put_line('姓名:'||v_name||',薪水:'||v_salary);
  
end;

记录型变量

• 接受表中的一整行记录，相当于java的一个对象
• 语法： 变量名 表名%ROWTYPE ，比如 v_emp emp%rowtype

【示例】: 查询emp表中7777号员工的个人信息

declare 
  --声明记录型变量
  v_emp emp%ROWTYPE;
begin

  --查询
  select * into v_emp from emp where empno = 7777;
  
  --打印
  dbms_output.put_line('姓名:'||v_emp.ename||',薪水:'||v_emp.salary||',地址:'||v_emp.addr);
  
end;

3 流程控制

条件分支

begin

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

end;

循环

begin

  loop
     exit when 退出循环条件;
     执行体;
  end loop;
  
end;

【示例】：打印1~10

--打印1~10
declare 
  vnum number :=1; 
begin

  loop
     exit when vnum>10;
     dbms_output.put_line(vnum);
     vnum:=vnum+1;
  end loop;
  
end;

4 游标

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

游标语法

声明：

​ cursor 游标名[(参数列表)] is查询语句;

打开：

​ open 游标名

读取：

​ fetch 游标名 into 变量列表

关闭：

​ close 游标名

游标属性

游标属性	返回值类型	说明
%ROWCOUNT	整型	获得FETCH语句返回的数据行数
%FOUND	布尔型	最近的FETCH语句放回一行数据为真，否则为假
%NOTFOUND	布尔型	与%FOUND相反，找不到数据返回true，通常用来退出循环
%ISOPEN	布尔型	游标已经打开时为真，否则为假

创建和使用

声明—打开—读取—关闭

declare 
  --声明游标：从emp表中去除所有的enmae和sal
  cursor cemp is select ename,sal from emp;
  --声明变量接受游标中的数据
  v_name emp.enmae%TYPE;
  v_sal  emp.sal%TYPE;
  
begin
  --打开游标
  open cursor;
  --遍历游标
  loop
     exit when ecmp%NOTFOUND;
     --读取游标
     fetch cemp into v_name,v_sal;
     
     dbms_output.put_line(v_name||v_sal);
         
  end loop;
  
  --关闭游标
  close cursor;
 
end;

带参数游标

-- Created on 2020/5/24 by PSZ 
declare 
  --声明游标：从emp表中查询所有的enmae和sal
  cursor cemp(v_age emp.age%TYPE) is select ename,sal from emp where age=v_age ;
  --声明变量接受游标中的数据
  v_name emp.enmae%TYPE;
  v_sal  emp.sal%TYPE;
  
  
begin
  --打开游标,传入具体的值
  open cursor(20);
  --遍历游标
  loop
     exit when ecmp%NOTFOUND;
     --读取游标
     fetch cemp into v_name,v_sal;
     
     dbms_output.put_line(v_name||v_sal);
         
  end loop;
  
  --关闭游标
  close cursor;
 
end;

5 存储过程

之前写的代码，都无法重复调用。可以将一个个的PLSQL业务处理过程存储起来进行复用，这些被存储起来的PLSQL程序称之为存储过程。

语法

create or replace procedure 过程名称[(参数列表)] is

begin


end [过程名称];

根据参数类型：

• 不带参数
• 带参数
• 带输入输出（返回值）

不带参数

create or replace procedure P_hello is

--可以直接声明变量

begin
  
dbms_output.put_line('hello');
  
end P_hello;

begin
  --调用存储过程
  P_hello; 
end;

带参数的存储过程

• in：输入
• out：输出

create or replace procedure p_querynameandsal(v_empno in emp.empno%TYPE) as

--声明变量
v_name emp.ename%TYPE
v_sal emp.salary%TYPE


begin
  --查询
  select ename,salary into v_name,v_sal from emp where empno=v_empnp;
    
  dbms_output.put_line(v_name||v_sal);
  
end p_querynameandsal;

begin
 p_querynameandsal(1234)
end;

带输出参数的存储过程

• 添加out标签