PL/SQL编程语言

一、PL/SQL编程语言

PL/SQL编程语言是对SQL编程语言的扩展，使得SQL语言具有过程化的编程特性，是面向过程的。PL/SQL编程语言比一般的过程化编程语言更加灵活高效，主要用来编写存储过程和存储函数。

二、PL/SQL编程语言：定义变量

（1）PL/SQL编程语言可以声明一个方法，格式如下：

          declare  --声明方法关键字

                        --declare 和 begin 关键字中间可以定义一些变量

          begin     --begin关键字类似于其它编程语言代码块开发的 {

                        --begin 和 end 关键字中间处理业务逻辑

          end        --end关键字类似于其它编程语言代码块开发的 }

 

（2）例如以下代码段

declare 
  a number(2) := 10;  -- := 为PL/SQL编程语言的赋值符号，这行代码表示定义一个 number类型，长度为2 的变量a，并赋值为10
  b varchar2(10) := '张无忌';
  username fe_base5.sys_users.su02%type; -- 这行代码表示定义username 引用型变量 ，并使用fe_base5.sys_users.su02字段的类型和长度
  userrow fe_base5.sys_users%rowtype; --这行代码表示定义userrow 记录型变量，用来保存fe_base5.sys_users表中的一行记录
begin
  dbms_output.put_line(a); --PL/SQL编程语言的打印语句
  dbms_output.put_line(b);
  select su02 into username from fe_base5.sys_users where su00 = 39540; --这行代码表示查询 fe_base5.sys_users 表中 su00 = 39540 的su00 字段并赋值给username
  dbms_output.put_line(username);
  select * into userrow from fe_base5.sys_users where su00 = 39540; --这行代码表示的意思是 查询 fe_base5.sys_users 表中 su00 = 39540 的一行数据，赋值给变量userrow
  dbms_output.put_line('员工' || userrow.su02 || '所在的部门是' || userrow.su03); --userrow.su02 为记录型变量的取值方式，PL/SQL编程语言采用 || 作为字符串拼接符
end;

--赋值语句可以使用 := 也可以使用 into 查询语句赋值

运行效果控制台打印如下：

三、PL/SQL编程语言：if判断

（1）PL/SQL编程语言中if判断和其它编程语言类似，语法稍有不同，如下代码块

--以下SQL代码可以实现
--输入<=0的数字，控制台打印：想入非非
--输入<=25,>0的数字，控制台打印：貌美如花
--输入<=40,>25的数字，控制台打印：风韵犹存
--输入>40的数字，控制台打印：夕阳无限好只是近黄昏
declare 
  age number(3) := &input; --&input  &表示输入 后边的input字符可以任意
begin
  if age <= 0 then
      dbms_output.put_line('想入非非');
  elsif age <= 25 then
      dbms_output.put_line('貌美如花');
  elsif age <= 40 then
      dbms_output.put_line('风韵犹存');
  else
      dbms_output.put_line('夕阳无限好只是近黄昏');
  end if;
end;

执行以上SQL，在弹框内数人数字，查看打印台

四、PL/SQL编程语言：循环

（1）PL/SQL编程语言有三种循环，while循环、exit循环、for循环，如下代码实现三种循环打印1到10

--一次只能执行一个declare函数，所以以下三个循环需要分开执行

--while循环
declare
  i number(2) := 1;
begin
  dbms_output.put_line('-------------------while循环开始-------------------');
  while i < 11 loop
     dbms_output.put_line(i);
     i := i+1; --和其它编程语言的 i++ 等价
  end loop;
  dbms_output.put_line('-------------------while循环结束-------------------');
end;


--exit循环
declare
    i number(2) := 1;
begin
  dbms_output.put_line('-------------------exit循环开始-------------------');
  loop
     exit  when i > 10;
     dbms_output.put_line(i);
     i := i+1; --和其它编程语言的 i++ 等价
  end loop;
  dbms_output.put_line('-------------------exit循环结束-------------------');
end;


--for循环
declare
    
begin
  dbms_output.put_line('-------------------for循环开始-------------------');
  for i in 1..10 loop  --此处和swift中的区间类似
     dbms_output.put_line(i);
  end loop;
  dbms_output.put_line('-------------------exit循环结束-------------------');
end;

五、PL/SQL编程语言：游标

（1）游标：可以存放多个对象，多行记录，如下是简单游标使用

--游标：可以存放多个对象，多行记录
--输出用户表中用户id小于7860的员工姓名 其中fe_base5.sys_users 为用户表 su00为用户id字段，su02为用户姓名
declare 
   cursor cu is select * from fe_base5.sys_users where su00 < 7860; --查出用户id小于7860的用户，赋值给游标对象cu
   userrow fe_base5.sys_users%rowtype;--定义记录型变量userrow用于存储fe_base5.sys_users表的一行信息
begin 
   open cu;--游标的使用需要开启和关闭
   loop  --循环游标中的数据
         fetch cu into userrow;--抓取游标中的一行数据到userrow
         exit when cu%notfound;--如果游标对象cu中的数据被循环完了，则退出循环
         dbms_output.put_line(userrow.su02);
   end loop;
   close cu;--游标的使用需要开启和关闭
end;

（2）稍微复杂一点的带参数游标用法

--带参数游标用法
--打印 fe_base5.sys_users表中，用户id小于10000 ， 并且用户名包含 张 的用户
declare
     cursor cu(maxid fe_base5.sys_users.su00%type , username fe_base5.sys_users.su02%type) --游标对象cu带参数，多个参数使用,分割
     is select * from fe_base5.sys_users where su00 <= maxid and su02 like '%张%';
     userrow fe_base5.sys_users%rowtype;
begin
     open cu(10000,'张');--打开游标时候传入参数
       loop
          fetch cu into userrow;
          exit when cu%notfound;
          dbms_output.put_line(userrow.su02);
       end loop;
     close cu;
end;

执行结果：

六、PL/SQL编程语言：存储过程

存储过程是提前编译好的一段PL/SQL语言，放置在数据库端，无需获取数据库连接对象就可以直接调用，这一段PL/SQL一般都是固定步骤的业务。

（1）如下代码创建一个简单业务的存储过程

--创建一个存储过程
create or replace procedure classinfoUpdate(class_id classinfo.classid%type)
is --此处可以写is也可以写as
begin
   update classinfo set classname = '小二班' where classid = class_id;--根据传入的classid,更改班级名为 小二班
   commit;
end;

创建好后，可以在图形化界面工具中看到如下图

如果创建失败，在存储过程列表会有红色叉号，如下图

存储过程的使用，存储过程可以在后台代码中直接调用该存储方法，在数据库图形化界面中，可以在声明方法中测试，如下

insert into classinfo (classid,classname) values (1,'少年班')

declare 

begin 
       userUpdate(1);
end;

数据结果被更改

（2）存储过程的 in / out 修饰

--创建有返回值的存储过程
create or replace procedure class_year_money(class_id classinfo.classid%type , money out classinfo.month_money%type)--参数的默认修饰符是in，如果是返回值则必须使用out修饰
as
       toalmoney classinfo.month_money%type;
begin
       select month_money * 12 into toalmoney from classinfo where classid = class_id;
       money := toalmoney;
end;


declare 
      name_money number(10); 
begin
      class_year_money(1,name_money);
      dbms_output.put_line(name_money);
end;

 

七、PL/SQL编程语言：存储函数

存储函数和存储过程类似，存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。所以存储函数因为有返回值可以嵌套在其它SQL语句中使用。

创建一个简单的存储函数

--创建一个存储函数
create or replace function class_year_money(class_id classinfo.classid%type ) return number --存储函数必须有return语句 ，用于返回值
is
       year_money number(10);
begin

       select month_money * 12 into year_money   from classinfo where classid = class_id;
       return year_money;  --返回值
end; 

在声明方法中条用效果如下

declare 
       totalMoney number(11);
begin
       totalMoney := class_year_money(1);
       dbms_output.put_line(totalMoney);
end;

八、PL/SQL编程语言：触发器

触发器就是制定一个规则，在我们做增删改操作的时候，只要满足该规则，就自动触发，无需调用。触发器分为语句级的触发器和行级触发器，语句级触发器不包含 for each row，行级触发器包含for each row。

行级触发器可以使用:old 和 :new对象或者一行记录。

（1）语句级触发器：插入一条记录，输出一个新建一个班级

--创建一个语句级的触发器

create or replace trigger insertClassinfo --创建触发器
after --规定在语句前执行还是语句后执行
insert --数据库操作语句关键字
on fe_base5.classinfo --作用在哪张表上
declare
begin
   dbms_output.put_line('新建一个班级');
end;

当执行插入语句时候，触发器就会被执行。

（2）行级触发器：禁止降低班级的班费

create or replace trigger forbidCutDown
before --更新语句执行前执行
update --更新操作
on classinfo --作用表
for each row --行级触发器
declare
   
begin
 if :old.money > :new.money then  -- :old.money 更新前的字段值  :new.money 更新后的字段值
      raise_application_error(-20001,'不能减少班费'); --自定义SQL异常，-20001 异常序号，在-20001到-20999之间
    end if;
end;

（3）行级触发器实现主键自增

create or replace trigger classinfo_key
before
insert
on classinfo
for each row
declare
begin
    select classionfo_id.nextval into :new.id from dual;
end;

 

九、PL/SQL编程语言：序列

 create sequence classionfo_id
 minvalue 1  --最小值
 nomaxvalue  --不设置最大值(由机器决定)，或 根据表字段的值范围设置 maxvalue
 maxvalue 999  -- 最大值
 start with 1   --从1开始计数，数值可变
 increment by 1  --每次加1，数值可变
 nocycle  --一直累加，不循环；cycle：达到最大值后，将从头开始累加
 nocache；  --不建缓冲区。   如果建立cache那么系统将自动读取cache值个seq，这样会加快运行速度；如果在单机中使用cache，或者oracle死了，那么下次读取的seq值将不连贯，所以不建议使用cache。