【PL/SQL】数据库测试脚本

PL/SQL 语言

　　面向过程语言，是对sql语言的扩展，主要用来编写存储过程和存储函数等。

 

格式

--- 声明方法
-- Created on 2022/6/14 
declare 
  -- Local variables here
  -- 变量
begin
  -- Test statements here
  -- 一段命令结束后加;
end;

 

 

 

输出内容

declare
    -- number 数值 包含小数等，int 整数
    -- := 给 变量赋值
    i number := 0;
begin
    -- 输出 相当于python中的print
    dbms_output.put_line(i);
end;

 

 

变量

declare
    -- 引用型变量
    x 表名.字段%type;
    -- 记录型变量
    y 表名%rowtype;
begin
    select 字段 into x from 表名 where 字段1=查询条件；
    dbms_output.put_line(x);
    select * into y from 表名 where 字段2=查询条件；
    dbms_output.put_line('查询为一行记录');
    dbms_output.put_line(y.字段 || '*字符串连接*' || y.字段);
end

 

 

if 判断

　　小于18 未成年
　　大于18 小于40 中年人
　　大于40 老年人

-- 声明函数
declare
    -- 输入值
    i number(3) := $i;
    
begin
    -- if 判断 依次执行
    if i<18 then
        dbms_output.put.line('未成年')；
    elsif i<40 then
        dbms_output.put.line('中年人')；
    else
        dbms_output.put.line('老年人')；
    -- 结束if判断
    end if;
end;

 

 

while 循环

declare
   i number(2) := 1;  

begin
  while i<11 loop
    dbms_output.put_line(i);
    -- 无i+1 则进入会死循环
    i := i + 1;
  end loop;
end;

 

 

exit 退出循环

declare
   i number(2) := 1;  

begin
  loop
    exit when i>10;
    dbms_output.put_line(i);
    -- 无 i+1 则进入会死循环
    i := i + 1;
  end loop;
end;

 

 

for 循环

declare
  -- 无需定义变量 
begin
  -- 1..10 从1到10循环
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

 

 

游标：可以存放多个对象，多行记录

-- 声明函数
declare
   -- 定义游标
   cursor cu is select * from 表名;
   x 表名%rowtype; -- 记录型变量 
begin
  -- 打开
  open cu;
       -- 循环
       loop
         -- 取值（按行取值）
         fetch cu into x;
         -- 找不到数据时退出
         exit when cu%notfound;
         -- 输出
         dbms_output.put_line(x.字段);
       end loop;
  close cu;
end;

 

 

带参数的游标

declare
   -- 带参数的游标并赋值
   cursor c(变量1 表名.字段%type)
   is select 字段 from 表名 where 条件;
   
   变量2 表名.字段%type;   -- 引用型变量
begin
  --  仅在打开游标时赋值一次；变量1 = 10
  open c(10);
       loop
         -- 取值
         fetch c into 变量2;
         -- 找不到值时退出
         exit when c%notfound;
         -- 更新数据
         update 表名 set 字段 = 新值 where 条件;
         -- 修改语句结束后需要提交
         commit;
       end loop;
  close c;
end;

 

 

 

存储过程　　

　　提交已经编写好的一段pl/sql语言，放在数据库端中可直接调用 常为固定步骤的业务

-- []内数据可写可不写 默认in参数
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
-- 相当于declare 声明
AS
begin
   -- PLSQL 子程序体
End;

 

 

实例

-- 存储过程参数 in out 默认未in
-- or replace 当过程名存在时 可以直接修改不会被占用
create or replace procedure p(e 表名.字段%type)
-- AS相当于declare 声明 也可以写成 IS
is

begin
   -- PLSQL 子程序体
   update XXX set xxx where xxx;
   commit;
End;


-- 测试p 调用存储过程
declare

begin
  p(001);
end;

 

 

存储函数

语法

create or replace function 函数名(参数 in 类型, 参数 in 类型) return 数据类型 is
    结果变量 数据类型;
begin
    -- 
    return(结果变量);
end 函数名;

 

 

实例

-- 存储过程和存储函数的参数都不能带长度
-- 存储函数中返回值的类型（return 的数据类型）不能带长度
create or replace function fun_u(e 表名.字段%type) return number
is
    -- 定义变量
    s number(10);
begin
    -- 
    select xxx into s from xxx where xxx;
    return s;
End;


-- 测试fun_u 调用存储过程
-- 存储函数在调用的时候，返回值需要接收即定义变量
declare
    s number(10);
begin
　　s := fun_u(1122);
　　dbms_output.put_line(s)
end;