Oracle数据库-03 PL/SQL编程、游标、存储过程

Kathy66·

已于 2022-12-01 09:31:11 修改

阅读量783

点赞数 2

分类专栏： Oracle 文章标签： Oracle 数据库存储过程

于 2019-03-26 12:50:07 首次发布

本文链接：https://blog.csdn.net/weixin_43094361/article/details/88815602

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、PL/SQL基础知识

1、PL/SQL简介

PL/SQL概述

PL/SQL（Procedural Language）是Oracle在关系数据库结构化查询语言SQL 基础上扩展得到的一种过程化查询语言。
SQL与PL/SQL

SQL与编程语言之间的不同在于，SQL没有变量，没有流程控制（分支、循环）。而PL/SQL是结构化和过程化的结合体，而且最为重要的是，在用户执行多条SQL语句时，每条SQL语句都是逐一的发送给数据库，而PL/SQL可以一次性将多条SQL语句一起发送给数据库，减少网络流量。
PL/SQL的工作原理
（1）PL/SQL 引擎接收PL/SQL块并对其进行编译执行
（2）该引擎执行所有过程语句
（3）将SQL语句发送给Oracle的SQL语句执行器
PL/SQL优点

（1）与SQL紧密集成，简化数据处理

支持SQL，在PL/SQL中可以使用：数据操纵命令、事务控制命令、游标控制、SQL函数和SQL运算符。
支持所有SQL数据类型
支持NULL值
支持 %TYPE 和 %ROWTYPE 属性类型

（2）更佳的性能，PL/SQL经过编译执行
（3）支持面向编程（OOP）

2、PL/SQL块

语法结构：

DECLARE
     --声明部分：如定义变量、游标和自定义异常
BEGIN
     --程序编写，包含SQL和PL/SQL语句的可执行部分
     
EXCEPTION /*异常处理语句*/
  
     --指定出现错误时，需要执行的操作
END;
 /

说明：

声明部分（DECLARE）：包含变量定义、用户定义的PL/SQL类型、游标、引用的函数或过程。
执行部分（BEGIN）：包含变量赋值、对象初始化、条件结构、迭代结构、嵌套的PL/SQL匿名块，或是对局部或存储PL/SQL命名块的调用。
异常部分（EXCEPTION）：包含错误处理语句，该语句可以像执行部分一样使用所有项。
结束部分（END）：程序执行到END表示结束，分号用于结束匿名块，而正斜杠 （/） 执行块程序。

PL/SQL块范例：


DECLARE
        qty_on_hand NUMBER(5);
BEGIN
        SELECT quantity INTO qty_on_hand
           FROM Products
        WHERE product = '玩具熊';
        IF qty_on_hand > 0 THEN
             UPDATE Products SET quantity = quantity + 1
              WHERE product = '玩具熊';
             INSERT INTO purchase_record
                     VALUES ('已购买玩具熊', SYSDATE);
        END IF;
        COMMIT;
EXCEPTION  /* 异常处理语句 */
        WHEN OTHERS THEN
                 DBMS_OUTPUT.PUT_LINE('出错:'|| SQLERRM);  
END;

3、PL/SQL变量

声明变量语法：

-- 声明变量语法结构
DECLARE variable_name [CONSTANT] type [NOT NULL] [:=value];

说明：

使用 DECLARE 关键字进行声明
variable_name：表示变量名称
CONSTANT：表示是否是常量
type：表示变量的数据类型
NOT NULL ：是否为空
:=value ：初始化变量
[ ]方括号表示可选项

变量命名规则：

变量名首字母必须是英文字母
其后可以是字母、数字或者特殊符号$、#和下划线
变量名长度不超过30个字符
变量名中不能有空格
不能是SQL保留字

PL/SQL支持的内置数据类型如图：
在这里插入图片描述

4、表达式和运算符

（1）表达式的分类

数值型
字符型
日期型
布尔型

（2）运算符分类

算术运算符
关系运算符
逻辑运算符
其他运算符

5、变量的赋值及注释的使用

/*
|| 在注释开始的首行只放斜线星号，标志注释开始，
|| 然后注释块的每一行以双垂直线开头，突出注释后面
|| 的内容是注释部分，最后，将星号斜线单置于一行
*/
DECLARE
       v_ename VARCHAR2(20);
       v_rate NUMBER(7,2);
       c_rate_incr CONSTANT NUMBER(7,2):=1.10;
BEGIN
       --方法一：通过SELECT INTO给变量赋值
       SELECT ename, sal* c_rate_incr  
             INTO   v_ename, v_rate
          FROM   employee 
       WHERE empno='7788';
      --方法二：通过赋值操作符“:=”给变量赋值
      v_ename:='SCOTT';
END;

6、控制语句

（1）条件控制

IF语句

-- 例1
DECLARE
  v_str varchar2(10):='b';
BEGIN
  IF(v_str='a') THEN dbms_output.put_line('if');
  ELSIF(v_str='b') THEN  dbms_output.put_line('elsif');
  ELSE dbms_output.put_line('else');
  END IF;
END;


-- 例2
DECLARE
   i constant number:=5;
BEGIN
  IF(i=4) THEN null; --如果什么都不做的话，那么补null来保证语句的完整性
  ELSIF(i=4) THEN dbms_output.put_line(i);
  ELSE dbms_output.put_line('else');
  END IF;
END;


-- 例3：验证如果不给字符变量赋值时默认是空字符串还是为空
DECLARE
  i varchar2(10);	--字符类型
BEGIN
  IF(i='') THEN dbms_output.put_line('空字符串');
  ELSIF(i is null) THEN dbms_output.put_line('null');
  ELSIF(i='a') THEN dbms_output.put_line('a');
  ELSE dbms_output.put_line('b');
  END IF;
END;



-- 例4
DECLARE
  i number;	-- 数值类型
BEGIN
  IF(i='') THEN dbms_output.put_line('空字符串');
  ELSIF(i is null) THEN dbms_output.put_line('null');
  ELSIF(i='a') THEN dbms_output.put_line('a');
  ELSE dbms_output.put_line('b');
  END IF;
END;

CASE语句

-- 例1：CASE语句
--方法一
DECLARE
       grade char:='A';
       remark varchar2(20);
BEGIN
       CASE grade
            WHEN 'A' THEN remark:='is Excellent';
            WHEN 'B' THEN remark:='is Good';
            WHEN 'C' THEN remark:='is Normal';
            WHEN 'D' THEN remark:='is Bad';
            ELSE remark:='big Problem';
       END CASE;
       dbms_output.put_line(remark);
END;



--例2：CASE语句
--方法二
DECLARE
       grade NUMBER:=70;
       remark varchar2(20);
BEGIN
       CASE
            WHEN grade=100 THEN remark:='is Excellent';
            WHEN grade>=80  THEN remark:='is Good';
            WHEN grade>=60  THEN remark:='is Normal';
            WHEN grade>=50 THEN remark:='is Bad';
            ELSE remark:='big Problem';
       END CASE;
        dbms_output.put_line(remark);
END;

（2）循环控制

LOOP循环

- 例1：简单循环loop
declare
   i number:=0;
begin
  loop
    i:=i+1; --循环为i+1
    dbms_output.put_line('i='||i);          --输出i的值
    if(i=30) then exit;       --i的值到30，则退出
    end if;
   end loop;
end;


-- 例2：简单循环loop
declare
   i number:=0;
begin
  loop
    i:=i+1;
    dbms_output.put_line('i='||i);
    exit when (i=30);		--使用when关键字
    end loop;
end;


-- 例3：嵌套循环
declare
   i number:=0;
   b number:=0;
begin
   loop
     i:=i+1;
     dbms_output.put_line('i>>>>>>='||i);
     exit when i=5;
     b:=0;
     --嵌套循环
     loop
       b:=b+1;
       dbms_output.put_line('b='||b);
       exit when b=2;
       end loop;
     end loop;
end;

WHILE循环

-- while循环
declare
  a constant number:=1;
  b constant number:=10;
  i number:=0;
begin
  while (b>a) loop
    i:=i+1;
    dbms_output.put_line('i='||i);
    exit when i=5;
    end loop;
end;

FOR 循环

-- 例1：for循环
declare
  a constant number:=4;
  b constant number:=10;
begin
  for i in a ..b loop
      dbms_output.put_line('i：'||i);
   end loop;
end;


-- 例2：for循环
declare
  a constant number:=1;
  b constant number:=10;
begin
  for i in reverse a ..b loop		--使用reverse关键字实现倒序遍历
    dbms_output.put_line('i+>>>>>'||i);
    end loop;
end;

7、编码规则

利用缩进排列展现逻辑结构
利用大小写增强可读性
格式化单独语句
格式化SQL语句
规范注释

二、PL/SQL异常处理

1、异常的分类

在运行程序时出现的错误称为异常。
发生异常后，语句将停止执行，控制权转移到PL/SQL块的异常处理部分。
异常有两种类型：
（1）预定义异常：当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发。
（2）用户定义异常：用户可以在PL/SQL块的声明部分定义异常，自定义的异常通过RAISE语句显式引发。

2、如何使用

PL/SQL预定义异常语法：

--预定义异常
BEGIN
        --过程及SQL语句;
EXCEPTION
        WHEN --异常名称THEN
                -- 过程及SQL语句;
        WHEN OTHERS THEN
                --过程及SQL语句;
END;

预定义异常分类如图：
在这里插入图片描述
预定义异常范例：

declare
    v_ename char(1);
begin
      select ename into v_ename
      from emp
      where empno=7788;
      dbms_output.put_line('员工姓名为：'||v_ename);
      Exception
         when no_data_found then    --处理没有数据的异常
              dbms_output.put_line('没有该员工');
         when value_error then
              dbms_output.put_line('该员工名称过长');
         when others then
               dbms_output.put_line('异常编码：'||sqlcode||'，异常信息：'||sqlerrm);         
end;

PL/SQL中的用户自定义异常处理：

使用 RAISE 关键字引发异常
使用 EXCEPTION 关键字处理异常

用户自定义异常范例：

-- 演示自定义异常
declare
   v_eno Scott.emp.empno%type:=7839;
   v_deptno  Scott.emp.deptno%type;
   v_sal  Scott.emp.sal%type;
   e_money_big exception;
begin
   --根据员工编号查询员工所属部门和工资
   select deptno,sal into v_deptno,v_sal
   from Scott.emp
   where empno=v_eno;
   --判断，如果部门编号为10
     if v_deptno=10 then
         --判断工资是否低于10000
         if v_sal<10000 then
              update Scott.emp set sal=10000 where empno=v_eno;
              commit;
         else
              raise e_money_big;
         end if;
     end if;
     
     --异常处理
     exception
       when e_money_big then
           dbms_output.put_line('工资不低于10000元');
       when others then
           dbms_output.put_line('异常编码：'||sqlcode||'，异常信息：'||sqlerrm);     
end;

三、游标

1、游标概述

在使用SQL编写查询语句时，所有的查询结果会直接显示给用户，但是在很多情况下，用户需要对返回结果中的每一条数据分别进行操作，则这个时候普通的查询语句就无法使用了，那么就可以通过结果集（由查询语句返回完整的行集合叫做结果集）来接收，之后就可以利用游标来进行操作。

2、游标分类

在Oracle数据库之中，游标分为以下两种类型：
（1）静态游标： 结果集已经存在（静态定义）的游标。分为隐式游标和显式游标。

隐式游标：所有DML语句为隐式游标，通过隐式游标属性可以获取SQL语句信息。
显式游标：用户显式声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标。

（2）REF游标： 动态关联结果集的临时对象。

游标的属性如图：
在这里插入图片描述
3、显式游标语法和特点

游标流程：

定义游标
打开游标
提取数据
关闭游标

（1）游标的声明：

-- 游标的声明
CURSOR cursor_name [ ( parameter [ , parameter]……)]
[ RETURN return_type ] IS selectsql

说明：

CURSOR：声明游标关键字
cursor_name ：游标名称
parameter：可选参数，用于指定参数类型、模式等
return：可选，指定游标的返回类型
selectsql：需要处理的select语句，不能包含 into 子句。

（2）打开游标：

	--使用open语句开启一个游标
	open cursor_name;

（3）提取游标

使用FETCH语句实现对游标内容的读取
variable_list 必须从游标提取的结果集类型相同

	-- 提取游标
	FETCH cursor_name INTO variable_list

（4）关闭游标

使用close语句关闭游标
关闭游标后，所有资源都将被释放

	-- 关闭游标
	close cursor_name

游标范例： 使用fetch提取游标的数据到变量中必须手动打开关闭游标

     declare
   	    --声明游标 查询所有员工，将结果集存放到cursor_emp变量中
        cursor cursor_emp is select * from Scott.emp;
        
        --定义变量，类型为cursor_emp游标中的数据的行类型
         v_emp cursor_emp%rowtype;
     begin
        --判断游标是否打开，如果没有则打开
        if cursor_emp%isopen=false then
           open cursor_emp;  --打开游标
        end if;
        --提取游标中的数据
        loop
        	 --逐行提取游标中每行的数据到v_emp变量中
           fetch cursor_emp into v_emp;   
           --判断如果没有数据则退出
           exit when cursor_emp%notfound;  
           --输出当前数据
           dbms_output.put_line('员工编号：'||v_emp.empno||'员工姓名：'||v_emp.ename||'工资：'||v_emp.sal);
        end loop;
        --关闭游标
        close cursor_emp;
     end;

（5）允许使用游标删除或更新活动集中的行

声明游标时必须使用select …for update语句

	CURSOR <cursor_name> IS SELECT statement FOR UPDATE;

使用以下语法更新行

	UPDATE <table_name> SET <set_clause> WHERE CURRENT OF <cursor_name>

使用以下语法删除行

	DELETE FROM <table_name> WHERE CURRENT OF <cursor_name>

游标范例： 使用foreach提取游标的数据到变量中自动打开关闭游标

declare
  	--声明游标
    cursor cursor_emp1 
    is select * from Scott.emp for update;
begin
    --提取游标中的数据
    for v_emp in cursor_emp1 loop
          delete Scott.emp  where current of cursor_emp1;
         --输出当前数据
          dbms_output.put_line('员工编号：'||v_emp.empno||'员工姓名：'||v_emp.ename||'工资：'||v_emp.sal);
    end loop;
end;

四、存储过程

1、子程序

子程序：命名的PL/SQL块，编译并存储在数据库中
子程序的各个部分
（1）声明部分
（2）可执行部分
（3）异常处理部分（可选）
子程序的分类
（1）过程：执行某些操作
（2）函数：执行操作并返回值

2、存储过程的用法

（1）创建存储过程

语法：


CREATE [OR REPLACE] PROCEDURE 	 --使用PROCEDURE关键字创建存储过程
   <procedure name> [(<parameter list>)]
IS|AS 							 -- 创建存储过程，可指定运行过程中需传递的参数
   <local variable declaration>
BEGIN
   <executable statements>		 --包括在存储过程中要执行的语句
[EXCEPTION
   <exception handlers>]		 --处理异常
END;

补充：

存储过程的参数模式：

IN：用于接收调用程序的值，相当于方法中的形参（默认的参数模式）。
OUT：用于向调用程序返回值，相当于return。
IN OUT：用于接收调用程序的值，并向调用程序返回更新的值。

创建存储过程范例：

-- 例1：创建一个根据empno查询指定用户信息的存储过程
 create or replace procedure pro_getEmpByNo(
       v_empno number,
       v_emp  out scott.emp%rowtype    
 )
 as
 begin
      select * into v_emp from scott.emp where empno=v_empno;
 end;


-- 例2：创建获取所有员工的薪水的存储过程
 create or replace procedure get_sals(
 	   --存放所有员工薪水的游标；注意此处使用OUT关键字，该参数作为输出参数
       cur_salary OUT SYS_REFCURSOR,  
       on_Flag OUT number,       --执行状态
       os_Msg OUT VARCHAR2       --提示信息
    ) 
    as
    begin
       open cur_salary for
           select empno,sal from Scott.emp;
       on_Flag:=1;
       os_Msg:='成功';  
    
    exception
       when others then
      on_Flag:=-1;
      os_Msg:='其他错误，与管理员联系。';
    end;

（2）调用存储过程
语法：

	EXEC[UTE] procedure_name (param_list);

说明：

使用EXEC或 EXECUTE关键字调用
procedure_name ：表示调用的存储过程的名称
param_list：参数列表

执行存储过程范例：

 -- 调用例2中的get_sals存储过程
 declare
        v_empno  scott.emp.empno%type;
        v_sal scott.emp.sal%type;
        emp_salary1 SYS_REFCURSOR;
        on_Flag1 number(1);     --执行状态
        os_Msg1 VARCHAR2(200);  --提示信息 
   
     begin
         --执行存储过程
         get_sals(emp_salary1,on_Flag1,os_Msg1);
         if on_flag=1 then
          loop         --由于存储过程中已经打开游标，所以不可以使用foreach
             fetch emp_salary into v_empno,v_sal;
             exit when emp_salary%notfound;
             dbms_output.put_line(v_empno||'的薪水是' ||v_sal);
          end loop;
         else
           dbms_output.put_line(os_Msg);
         end if;
         if emp_salary%ISOPEN THEN
            close emp_salary;
          end if;
     end;

补充：参数传递的三种方式

按位置传递参数

-- 按位置传递参数
declare
        v_emp scott.emp%rowtype;
begin
        pro_showAllEmp(7788,v_emp);
        dbms_output.put_line(v_emp.ename);
end;

按名称传递参数

   --按名称传递参数
   declare
       v_emp1 scott.emp%rowtype;
   begin
   	   -- 被调存储过程的参数指向变量或值
       pro_showAllEmp(v_emp=>v_emp1,v_empno => 7788);
       dbms_output.put_line(v_emp1.ename);
   end;

混合方式传递参数

（3）将过程的执行权限授予/撤销其他用户

	-- 将权限授予指定用户
	SQL> GRANT EXECUTE ON find_emp TO MARTIN;
	
	--将权限授予所有用户
	SQL> GRANT EXECUTE ON swap TO PUBLIC;
	
	 --撤销权限
     revoke execute on 存储过程名 to 用户名/public;

（4）删除存储过程语法

	DROP PROCEDURE  procedure_name;

3、存储过程使用规则

存储过程中不可以直接使用DDL语句，可以通过动态SQL实现。但不建议频繁的使用DDL语句。
存储过程中必须有相应的出错处理功能。
存储过程变量使用%type和%rowtype类型。
必须在存储过程中做异常捕获，并将异常信息通过os_Msg变量输出。
-1~-19999的异常为Oracle定义的异常代码。
存储过程必须包含两个输出参数分别用于标识过程的执行状态及过程提示信息。
“WHEN OTHERS”语句必须放置在异常处理代码的最后面，作为缺省处理器处理没有显式异常的处理。

关注一下吧～【Pretty Kathy】

Kathy66·

关注

2
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
Oracle数据库-03 PL/SQL编程、游标、存储过程

一、PL/SQL基础知识1、PL/SQL简介PL/SQL概述PL/SQL（Procedural Language）是Oracle在关系数据库结构化查询语言SQL 基础上扩展得到的一种过程化查询语言。SQL与PL/SQLSQL与编程语言之间的不同在于，SQL没有变量，没有流程控制（分支、循环）。而PL/SQL是结构化和过程化的结合体，而且最为重要的是，在用户执行多条SQL语句时，每...
复制链接

扫一扫