OraclePLSQL语言学习2021-4-22

PL/SQL语言简介

PL/SQL(Procedual Language/SQL)是Oracle在标准SQL语言上进行过程性扩展后形成的程序设计语言，是一种Oracle数据库特有的、支持应用开发的语言

PL/SQL支持面向对象的编程，在PL/SQL中可以创建类型，可以对类型进行继承，可以在子程序中重载方法等。

PL/SQL的基本结构

DECLARE

--声明变量、常量、用户定义的数据类型以及游标等（可选）

BEGIN

--主程序体，可以加入各种合法的PL/SQL语句

EXCEPTION

--程序异常处理，当程序中出现错误时执行这一部分

END; --主程序体结束

注意 ：该结构最后的分号是必需的

数据类型及操作符

 ：= 赋值符号
  => 链接操作符
  << 起始标签分界符
  >> 终结标签分界符

数字类型：
它包含NUMBER、PLS_INTEGER和BINARY _INTEGER 3种
其中，NUMBER类型的变量可以存储整数或浮点数，而BINARY_INTEGER或PLS_INTEGER类型的变景只存储整数

字符串类型：
VARCHAR2、 CHAR、LONG（常用）

第一个程序：
新建SQL window

DECLARE
b CONSTANT LONG :='hello word!'; 
BEGIN
dbms_output.put_line(b);
END; 

dbms_output.put不显示输出内容

dbms_output.put 在该行输出，没有回车则该行尚未结束，故没有输出，因此需要调用dbms_output.put_line(’’)结束该行才能显示内容

PL/SQL特殊符号及说明

声明部分

变量名 变量类型 := 初始值
变量名 emp.sal % TYPE  -- 引用类型的变量
        emp % rowtype -- 记录型变量 

就是指psal这个变量是引用了表emp中的sal字段的类型(emp为表名，sal为该表的字段名)
实例:

-- 输出GDS0504299的商品名称
DECLARE
   s_name fd_goods.goods_name % TYPE;  --- 未知数据类型的类型声明
   t_name fd_goods % ROWTYPE;   --记录型变量声明与赋值
BEGIN
  --给变量赋值
 SELECT a.* INTO t_name FROM fd_goods a WHERE a.goods_id='GDS0504299';
 dbms_output.put_line('商品名称：'||t_name.goods_name);
END;

循环

主要三种循环

DECLARE
     j NUMBER :=1;
     k NUMBER :=1;
BEGIN
     --for循环
  FOR i IN 1..10 LOOP
    dbms_output.put(i);
    END LOOP;
    dbms_output.put_line('for循环完成');
    
     -- while 循环
    WHILE j<=10 LOOP
      dbms_output.put_line(j);
      j:=j+1;
    END LOOP;
    
    --简单循环
    LOOP
      EXIT WHEN k=5;
      dbms_output.put_line(k);
      k:=k+1;
      END LOOP;
END;

判断

case when…then…else…end

UPDATE FD_PARAMETER
   SET PARM_VALUE = CASE
                      WHEN PARM_VALUE = 'Y' THEN
                       'N'
                      ELSE
                       'Y'
                    END
 WHERE LC_CODE = 'LC002'
   AND PARM_NO = 'YTHJX_FLG';

游标

游标: (光标/指针) 是对查询结果集的封装, 相当于是jdbc中的ResultSet
语法：

 -- 声明游标
CURSOR 游标名 IS 查询语句;
CURSOR 游标名(参数名 参数类型) IS 查询语句 WHERE 列名 = 参数名;

程序包

用于将逻辑相关的PL/SQL块或元素（变量、常量、自定义数据类型、异常、过程、函数、游标）等组织在一起，作为一个完整的单元存储在数据库中。
它具有面向对象的程序设计语言的特点，是对PL/SQL块或元素的封装。程序包类似于面向对象中的类，其中变量相当于类的成员变量，而过程和函数就相当于类中的方法

程序包有两个独立的部分：说明部分和包体部分
说明部分是包与应用程序之间的接口、包体部分才是这些过程、函数、游标等的具体实现

包说明创建格式

create package <包名＞

IS
变量、 常量及数据类型定义;

游标定义头部;

函数、 过程的定义和参数列表以及返回类型;

END <包名>;

包体

create package body <包名＞
AS
游标、 函数、 过程的具体定义;
END <包名＞;

Oracle 存储过程 PROCEDURE

1、概述　　
　　存储过程: 实际上是将一段已经编译好的PLSQL代码片断,封装在数据库中。
　作用:1. 提高执行效率 2. 提高代码复用性

基本结构

create [or replace] procedure 过程名称[(参数1 in|out 参数类型,参数2 in|out 参数类型)]
is | as
　　-- 声明
begin
 　 -- 业务
end;

2.使用实例

-- 给指定员工涨薪,并打印涨薪前和涨薪后的工资
-- 员工编号 : 输入参数
-- 涨多少 : 输入参数
/*
   1. 查询当前工
   2. 打印涨薪前工资
   3. 涨工资
   4. 打印涨薪后的工资
   5. 提交数据
*/
create or replace procedure proc_updatesal(vempno in number,vcount in number)
is
   -- 声明变量记录当前工资
   vsal number;
begin
   --1. 查询当前工资
   select sal into vsal from emp where empno=vempno;
   --2. 打印涨薪前工资
   dbms_output.put_line('涨薪前:'||vsal);
   --3. 涨工资
   update emp set sal=vsal+vcount where empno=vempno;
   -- 4. 打印涨薪后的工资
   dbms_output.put_line('涨薪后:'||(vsal+vcount));
   --5. 提交数据
   commit;
end;

-- 调用存储过程
-- 方式1:
call proc_update_sal(7369,100);

-- 方式2:
declare

begin
   proc_updatesal(7369,100);
end;

s
-- 获取指定编号员工的年薪
/*
   编号: in  输入
   年薪: out 输出
*/
create or replace procedure proc_getyearsal(vempno in number,vyearsal out number)
is

begin
  select sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;
end;

-- plsql代码片断中调用
declare
   yearsal number;
begin
   proc_getyearsal(7369,yearsal);
   dbms_output.put_line(yearsal);
end;


-- 封装存储过程,输出的是游标类型, 所有员工
/*
   sys_refcursor : 系统引用游标
*/
create or replace procedure proc_getemps(vemps out sys_refcursor)
is

begin
    -- 打开游标, 谁调用谁关闭
    open vemps for select * from emp;
end;

declare
  vemps sys_refcursor;
  vrow emp%rowtype;
begin
  -- 调用存储过程
  proc_getemps(vemps);

  loop
     fetch vemps into vrow;
     exit when vemps%notfound;
     dbms_output.put_line(vrow.ename);
  end loop;
  -- 关闭游标
  close vemps;
end;

存储函数

实际上是将一段已经编译好的PLSQL代码片断,封装在数据库中

存储过程和存储函数区别:

1. 函数有返回值,过程没有返回值

2. 函数可以直接在SQL语句中使用,过程不可以

3. 函数能实现的功能,过程能实现

4. 过程能实现的功能,函数也能实现

5. 函数和过程本质上没有区别 通常情况下,我们自己开发封装的是存储过程