Oracle-PL/SQL基础

概述

PL/SQL简介

pl/sql（procedural language/sql）是Oracle在标准的sql语言上的扩展，pl/sql不仅允许嵌入Sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。

pl/sql是面向过程的语言。

不同数据库的SQL扩展

• PL/SQL 是Oracle数据的SQL扩展。
• SQL/PL 是DB2数据库的SQL扩展。
• T-SQL 是SQL Server数据库的SQL扩展。

PL/SQL的必要性：

1、提高应用程序的运行性能。
2、模块化的设计思想。
3、减少网络传输量。
4、提高安全性。

PL/SQL编写规范

1、注释

单行注释–
sql>select * from emp where empno=7788; –取得员工信息

多行注释

/………………../

2、标识符号的命名规范

（1）当定义变量时，建议用v_作为前缀 v_sal。

（2）当定义常量时，建议用c_作为前缀 c_rate。

（3）当定义游标时，建议用_cursor作为后缀emp_cursor。

（4）当定义例外时，建议用e_作为前缀 e_error。

---

PL/SQL块

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

---

块结构示意图

pl/sql块由三个部分构成：定义部分、执行部分、例外处理部分。

declear：定义部分是从declare开始的，这部分是可选的。定义常量、变量、游标例外、复杂数据类型。

begin：执行部分是从begin开始的，这部分是必须的。

exception：例外处理部分是exception开始的，该部分可选的。

---

第一个PL/SQL程序

PLSQL中的命令窗口

SQL> set serveroutput on ;--打开输出
SQL> declare  
    --定义部分 ，如果没有定义，declare可以省略。 可选 ，定义常量、变量、游标、例外、复杂数据类型
  2  begin 
     --程序
  3  dbms_output.put_line('66666666');
  4  end;
  5  /

66666666

PL/SQL procedure successfully completed

SQL> /
--表示执行上一个PL/SQL块。

66666666

PL/SQL procedure successfully completed

---

PL/SQL基础语法

---

程序结构

declare
    说明部分（变量说明\游标声明\例外说明）
begin
    语句序列(DML语句)
exception
    例外处理语句
end ;
/

基本变量类型

定义

基本变量类型：
char 、 varchar2、date、number、boolean、long

举例：

var1 char(20);
married boolean := false ;
psal number(7,2);

栗子

SQL> set serveroutput on ;
SQL> 
SQL> declare
  2   -- 定义基本变量类型
  3   v_name varchar2(20);--varchar2 字符串类型
  4   v_num  number(7,2);--number 数字类型
  5   v_date date ;--date 日期类型
  6  
  7   begin
  8  
  9     v_name :='小工匠';
 10     dbms_output.put_line('name:'||v_name);
 11  
 12     v_num :=999;
 13     dbms_output.put_line('num:'||v_num);
 14  
 15     v_date :=sysdate;
 16     dbms_output.put_line('数据库时间:'||v_date);
 17  
 18     dbms_output.put_line('明天的时间:'||(v_date+1));
 19   end ;
 20  /

name:小工匠
num:999
数据库时间:22-JUN-16
明天的时间:23-JUN-16

PL/SQL procedure successfully completed

SQL> 

---

引用型变量 %type

定义

使用%TYPE类型的变量

举例

v_name emp.ename%type ;

栗子

打印7369员工的姓名和薪水信息

SQL> set serveroutput on ;
SQL> declare
  2  --打印 7369 员工的姓名和薪水信息
  3  /* 可以使用基本类型来定义变量的类型，推荐使用引用型变量来定义变量类型*/
  4  --vname varchar2(20);
  5  --v_sal  number;
  6  
  7  --定义引用变量
  8  v_name    emp.ename%type ;
  9  v_sal      emp.sal%type;
 10  begin
 11    --业务操作，变量赋值   (两种赋值方式   1.   v_sal  := 20   第二种   into的方式 )
 12    select ename, sal into v_name, v_sal from emp e where e.empno = 7369;
 13    --输出信息
 14    dbms_output.put_line(v_name||'的薪水是'||v_sal);
 15  end;
 16  /

SMITH的薪水是800

PL/SQL procedure successfully completed

SQL> 

---

记录型变量 %rowtype

定义

%rowtype 记录型变量 代表表中的一行，而一行中有很多列。

举例

emp_rec emp%rowtype

记录型变量分量的引用

手工赋值
emp_rec.ename := 'ADMIN' 
或者 
into 赋值之后， 使用 emp_rec.enmae获取

栗子

SQL> set serveroutput on ;
SQL> declare
  2  --打印 7369 员工的姓名和薪水信息
  3  
  4  --定义记录型变量 ，得到 7369一行的所有列的信息
  5  v_emp_rec  emp%rowtype ;
  6  begin
  7    --业务sql
  8    select * into v_emp_rec from emp a where a.empno=7369;
  9    --获取姓名和薪水，并打印
 10    dbms_output.put_line(v_emp_rec.ename||'的薪水是'||v_emp_rec.sal);
 11  end ;
 12  /

SMITH的薪水是800

PL/SQL procedure successfully completed

SQL> 

---

if语句的使用

形式一

if 条件 then 语句1;
语句2；
end if;

形式2

if 条件 then 语句1;
else 语句2;
end if;

形式3 注意elsif

elsif没有e 且是连在一起的

if 条件 then 语句;
elsif 条件 then 语句;
else 语句;
end if;

---

栗子

/*
判断用户从键盘输入的数字
1、如何使用if语句
2、接收一个键盘输入（从键盘上获取的都是字符串）
*/
set serveroutput on ;
--接收一个键盘输入
--num ：地址值，含义是：在该地址上保存了输入的值 ，获取地址值上对应的值，需要使用 &
accept num prompt'请输入一个数字';

declare
  --定义变量保存用户从键盘输入的数字  这里并没有对异常进行处理，输入字符串，会抛异常
  pnum number := #
begin
  --执行if 语句进行条件判断      提示信息 不能使用双引号，必须使用单引号，否则报错
  if pnum = 0 then dbms_output.put_line('输入的数字是'||pnum);
     elsif pnum = 1 then dbms_output.put_line('输入的数字是'||pnum);
     elsif pnum = 2 then dbms_output.put_line('输入的数字是'||pnum);
     else dbms_output.put_line('其他数字'||pnum);
  end if;
end;
/

---

循环语句的使用

形式1

while 条件  loop 
    .....
end loop;

当条件满足时，执行循环体，不满足时，退出循环体。

set serveroutput on ;

declare 
--定义循环变量  初始值为1    记得加上变量的类型  否则报错 
v_num  number  :=1 ;
begin 
  while v_num <=5 loop 
        --打印 v_num
        dbms_output.put_line(v_num);
        --变量+1  不能使用v_num++的形式，oracle不支持这种写法
        v_num :=v_num+1 ;
  end loop;
end;
/

---

形式2

loop
exit [when 条件];
.....
end loop;

条件成立时，退出循环体，不成立时执行循环体。

set serveroutput on ;
declare 
 --定义循环变量
 v_num number :=1 ;
begin 
loop 
    exit when v_num>10 ; --记得加 标点符号 
    dbms_output.put_line(v_num);
    v_num := v_num+1;
    end loop ; 
end;
/

推荐使用第二种，因为在控制游标的时候比较方便。

---

形式3

for i in 1 .. 10 loop 
    语句;
end loop;

set serveroutput on ;
declare 
v_num number :=1 ;
begin 
      for i in 1 .. 5 loop  -- .. 前后可以有空格 也可以没有
         dbms_output.put_line(v_num);
          v_num := v_num+1;
      end loop; 
end;
/

---

游标

游标的概念

游标（游标）：一个结果集
不能把一个集合赋值给一个基本型变量，否则会抛出 too many rows的异常。

可带参数 ，可不带参数。

---

游标的语法

cursor 游标名 [(参数名 数据类型[,参数名，数据类型]...)]
is select 语句;

比如

cursor c1 is select enama from emp;

---

操作游标的步骤

打开游标

open c1;(打开游标，执行查询 即执行游标的查询语句)

取一行游标的值

fetch c1 into v_name;(  取一行到变量中)

最开始时，游标指向集合的第一条记录，记录返回后，指针移动到下一条记录。

关闭游标

close c1 ;(关闭游标释放资源)

栗子

使用游标查询员工姓名和工资，并打印。

Loop循环游标

推荐写法

 ....
    Loop 
     Fetch  游标名 InTo  临时记录或属性类型变量；
     Exit  When   游标名%NotFound；
    End   Loop；
 .....

/*使用游标查询员工姓名和工资，并打印*/
set serveroutput on ;

declare
--定义 游标    推荐将游标和游标对应的变量写在一块，比较好维护
cursor cemp is select  e.ename , e.sal from emp e ;
--定义游标对应的变量   这里使用引用型变量
v_name     emp.ename%type ;
v_sal      emp.sal%type;

begin 
  --打开游标
  open cemp ;
  --循环获取游标中的值
  loop 
       --取一条数据
       fetch cemp into  v_name,v_sal ; -- into 后变量的顺序一定要和定义游标时select的字段对应
       exit when cemp%notfound ;
       --打印
       dbms_output.put_line(v_name||'的工资是'||v_sal);
  end loop;
  --关闭游标
  if cemp%isopen then
     close cemp;
     dbms_output.put_line('Closing...');
  end if;
end;
/

---

或者

For 循环游标

循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。

     For  变量名  In  游标名  
     Loop
      数据处理语句；
     End Loop；

/*使用游标查询员工姓名和工资，并打印*/
set serveroutput on ;

declare
--定义 游标    推荐将游标和游标对应的变量写在一块，比较好维护
cursor cemp is select  e.ename , e.sal from emp e ;
--定义游标对应的变量   这里使用引用型变量
v_name     emp.ename%type ;
v_sal      emp.sal%type;

begin 
  --循环获取游标中的值
   for c in cemp  LOOP
        select  c.ename , c.sal into v_name,v_sal from dual;
        dbms_output.put_line(v_name||'的工资是'||v_sal);
    end loop;

end;
/

---

实例：给员工涨工资

/*
给员工涨工资。总裁涨1000，经理涨800，普通员工涨400；
*/
declare 
  --定义游标
  cursor c_emp is select empno,empjob from emp;
  --定义游标对应的变量
  p_empno emp.empno%type;
  p_empjob emp.empjob%type;
begin
  --打开游标
  open c_emp;
  --取出一个员工
  loop
    fetch c_emp into p_empno,p_empjob;
    exit when c_emp%notfound;
    --判断职位
    if p_empjob = 'PRESIDENT' then 
      update emp set sal=sal+1000 where empno=p_empno;
    elsif p_empjob = 'MANAGER' then 
      update emp set sal= sal+800 where empno = p_empno;
    else 
      update emp set sal=sal+400 where empno=p_empno;
    end if;
  end loop;
  --关闭游标 
  if c_emp%isopen then 
      close c_emp;
   end if ;
  --提交事务，oracle默认的隔离级别是read committed ，不同连接只能读取提交之后的；
  commit;
  dbms_output.put_line('涨工资完成');
end;
/

---

游标的属性

游标的4个属性

• %Found ：Fetch语句（获取记录）执行情况 True or False
• %NotFound : 最后一条记录是否提取出 True or False
• %ISOpen ： 游标是否打开True or False
• %RowCount ：游标当前提取的行数

---

游标数的限制

Oracle默认的一个会话最多可以打开300个游标.

可以通过使用 show parameter cursors; （表示模糊查询 %cursors%）

查看包含cursors的参数设置

修改游标数的限制

使用DBA权限的用户

alter system set open_cursors=400 scope = both;

其中scope的取值：both，memory，spfile

• memory：表示只更改当前实例，不更改参数文件
• spfile：表示只更改参数文件，不更改当前示例，数据库服务需要重启
• both：表示上边两个同时更改

---

带参数的游标

注意 定义（带参数） 和打开游标（传递参数）时的区别。其余的和无参的游标一样。

set serveroutput on ;

declare
--定义带参数的游标
cursor cemp (dno number) is select ename from emp where deptno =dno;
--定义游标中对应的变量
v_name  emp.ename%type;

begin
  --打开游标   传入对应的入参
        open cemp(10);
              --loop循环 遍历游标
             loop 
                       fetch cemp into v_name;
                       exit when cemp%notfound;
                       dbms_output.put_line(v_name);
             end loop;
        close cemp;
end;
/

---

显式游标和隐式游标

上面介绍的是显式游标，下面说下隐式游标

DML操作和单行SELECT语句会使用隐式游标，它们是：

• 插入操作：INSERT
• 更新操作：UPDATE
• 删除操作：DELETE
• 单行查询操作：SELECT … INTO …

隐式游标的名字为SQL，这是由ORACLE 系统定义的。
对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。

格式调用为： SQL%

隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。

游标的属性有四种，如下所示。

隐式游标的属性   返回值类型    意    义
SQL%ROWCOUNT    整型       代表DML语句成功执行的数据行数
SQL%FOUND       布尔型     值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND    布尔型     与SQL%FOUND属性返回值相反
SQL%ISOPEN      布尔型     DML执行过程中为真，结束后为假

DECLARE  
   v_rows NUMBER;  
BEGIN  
   --更新数据  
   UPDATE employees SET salary = 30000  
     WHERE department_id = 90 AND job_id = 'AD_VP';  
   --获取默认游标的属性值  
   v_rows := SQL%ROWCOUNT;  
   DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');  

    --删除指定雇员；如果部门中没有雇员，则删除部门  
    DELETE FROM employees WHERE department_id=v_deptno;  
    IF SQL%NOTFOUND THEN  
        DELETE FROM departments WHERE department_id=v_deptno;  
    END IF;  
END;  

---

例外

例外的概念

在oracle中错误被叫做例外：分为系统例外和自定义例外。

系统例外 比如：
- No_data_found 没有找到数据
- Too_many_rows select..into语句匹配多个行
- Zero_Divide 被零除
- Value_error 算数或转换错误，算术错误比如说负数开平方
- Timeout_on_resource 在等待资源时发生超时，常见于分部署数据库。

---

系统例外之no_data_found

/*系统例外  no_data_found*/ 
set serveroutput on ;
--查询empno为222的姓名
declare 
--定义引用型变量
v_name emp.ename%type;

begin 
  --业务sql
    select ename into v_name from emp where empno=222; 
    exception 
      when no_data_found  then dbms_output.put_line('no data found');
      when others then dbms_output.put_line('others exception');
end;
/

---

系统例外之too_many_rows

/*系统例外  too_many_rows */
set serveroutput on ;
--查询 10号部门的员工
declare 
--定义引用型变量
v_name emp.ename%type;

begin 
  --业务sql
    select ename into v_name from emp where deptno=10; 
    exception 
      when too_many_rows  then dbms_output.put_line('too_many_rows');
      when others then dbms_output.put_line('others exception');
end;
/

---

系统例外之zero_divide

/*系统例外  zero_divide */
set serveroutput on ;
declare 
--定义引用型变量
v_num number;

begin 
  --业务sql
      v_num := 1/0;
    exception 
      when zero_divide  then dbms_output.put_line('zero_divide');
dbms_output.put_line('0不能做除数');
      when others then dbms_output.put_line('others exception');
end;
/

---

系统例外之value_error

/*系统例外  value_error */
set serveroutput on ;
declare 
--定义引用型变量
v_num number;

begin 
  --业务sql
      v_num := 'xiaogongjiang';
    exception 
      when value_error  then dbms_output.put_line('value_error');
      when others then dbms_output.put_line('others exception');
end;
/

---

自定义例外

步骤

1. 定义变量，类型是exception
2. 使用raise抛出自定义意外

栗子

--自定义例外：（没有查找到的例外no_emp_found）
declare 
  cursor c_emp is select ename from emp where deptno=50;
  p_ename emp.ename%type;
  --定义一个例外
  no_emp_found exception;
begin
  open c_emp;
    --获取一条记录
    fetch c_emp into p_ename;
    --如果没有查到则抛出自定义例外
    if c_emp%notfound then 
      raise no_emp_found;
    end if;
  --此处，当前一句抛出例外执行完exception后，
  --oracle会自动启动一个pmon(process monitor)的一个进程
  --将pl/sql程序中未关闭的资源释放
  --所以  close c_emp; 还是会执行的
  close c_emp;
  --捕获例外
  exception 
    when no_emp_found then  dbms_output.put_line('没有该部门下的员工');
    when others then  dbms_output.put_line('其他例外');
end;
/

---

PL/SQL调测

可以在pl/sql工具中 新建测试窗口，调测过程和调测存过的方式一样，可以一步一步的跟踪sql执行的过程。

案例

运用瀑布模型完成PLSQL程序的设计

瀑布模型

• 1.需求分析
• 2.设计
  
  • 2.1概要设计
  • 2.2详细设计
• 3.编码coding
• 4.测试Testing
• 5.上线（部署）
  

拿到一个需求后，不找着急写程序，先分析明白了

• sql语句
• 变量初始值
• 变量如何获取
• ….

---

案例：统计每年入职的员工人数

分析过程：
每年入职的员工人数
1.所有的年份集合–>定义cursor保存
2.每个员工的入职年份–>定义v_hiredate保存
3.每年入住的人数总和–>定义v_count_XX保存

先把大框架打起来

declare 
---定义变量balabala
begin 
---业务逻辑
end;
/

然后再一步一步的填充业务逻辑。

set serveroutput on ;
declare 

--入职年份的游标
cursor hiredate_cursor  is  select to_char(hiredate,'yyyy') from emp;

--定义入职年份
v_hiredate varchar2(4);

--定义每个年份入职人员总数
v_count_80 number :=0;
v_count_81 number :=0;
v_count_82 number :=0;
v_count_87 number :=0;

begin
   --打开游标
  open  hiredate_cursor ;
  --loop循环遍历游标
  loop 
        --取值
      fetch hiredate_cursor into  v_hiredate ;
      exit when  hiredate_cursor%notfound ;

         if v_hiredate = '1980' then v_count_80 :=v_count_80+1 ;
         elsif v_hiredate = '1981' then v_count_81 :=v_count_81+1 ;
         elsif v_hiredate ='1982'   then v_count_82 :=v_count_82+1;
         elsif v_hiredate ='1987' then v_count_87 :=v_count_87+1;
         end if ;
    end loop;
  --关闭游标
  close hiredate_cursor;

   dbms_output.put_line('总共入职人数:'||(v_count_80+v_count_81+v_count_82+v_count_87));--加括号
    dbms_output.put_line('80入职的:'||v_count_80);
    dbms_output.put_line('81入职的:'||v_count_81);
    dbms_output.put_line('82入职的:'||v_count_82);
    dbms_output.put_line('87入职的:'||v_count_87);
end;
/

---

案例：员工涨工资问题

案例2：涨工资问题，从最低工资的员工开始涨起，没人涨10%，工资总额不能超过50000，返回涨工资的人数和涨后的工资总额.

/*
分析：
1、用到的sql语句：
      select empno,sal from emp order by sal;
      select sum(sal) into totalsal from emp;
2、需要声明的变量：
      工资总额：totalsal   涨工资人数：count
3、循环推出的条件：
      工资总额>5W   or  全部员工都涨完工资*/

declare
  cursor cemp is select empno,sal from emp order by sal;
  p_no emp.empno%type;
  p_sal emp.sal%type;
  countemp number:=0;--涨工资人数
  totalsal emp.sal%type;
begin
  --获取初始工资总额
  select sum(sal) into totalsal from emp;
  open cemp;
    --判断当前工资总额是否大于5W
    if totalsal<50000 then 
      loop
        fetch cemp into p_no,p_sal;
        exit when cemp%notfound;
        --获取当前员工涨工资后的工资总额
        --如果工资总额超过5W直接退出循环
        exit when (totalsal+p_sal*0.1)>50000;
        update emp set sal=sal*1.1 where empno=p_no;
        --涨工资人数加1
        countemp:=countemp+1;
      end loop;
    end if;
  close cemp;
  commit;
  dbms_output.put_line('共有'countemp'人涨工资，工资总额为：'totalsal);
end;
/

---

案例：涉及两张表的员工涨工资问题

declare
  --获取所有部门
  cursor c_dept is select deptno from dept;
  --各部门编号
  p_dno dept.deptno%type;
  --各部门总金额
  p_totalsal number;
  --各工资分段人数：
  num1 number;
  num2 number;
  num3 number;
  --定义一个游标存放该部门下所有员工(带参数)
  cursor c_emp(dno number) is select sal from emp where deptno = dno;
  --员工的薪水
  p_sal number;
begin
  --打开部门游标
  open c_dept;
  loop
    --部门循环
    fetch c_dept
      into p_dno;
    exit when c_dept%notfound;
    --初始化变量：
    p_totalsal := 0;
    num1       := 0;
    num2       := 0;
    num3       := 0;
    --  --获取本部门下所有员工,打开员工游标
    open c_emp(p_dno);
    loop
      --员工循环
      fetch c_emp into p_sal;
      exit when c_emp%notfound;
      if p_sal < 3000 then
        num1 := num1 + 1;
      elsif p_sal >= 3000 and p_sal <= 6000 then
        num2 := num2 + 1;
      elsif p_sal > 6000 then
        num3 := num3 + 1;
      end if;
      --获取总金额
      p_totalsal := p_totalsal + p_sal;
    end loop;
    close c_emp;
    --保存统计结果到sal_msg
    insert into sal_msg values (p_dno, num1, num2, num3, p_totalsal);
  end loop;
  close c_dept;
  commit;
  dbms_output.put_line('统计完成');
end;
/

---