Java Web笔记（四） - PL / SQL

最新推荐文章于 2024-01-26 10:31:54 发布

昔小年

最新推荐文章于 2024-01-26 10:31:54 发布

阅读量262

点赞数

分类专栏： Java笔记文章标签： PL SQL

本文链接：https://blog.csdn.net/abc_cba_aaa/article/details/78455905

版权

Java笔记专栏收录该内容

7 篇文章 0 订阅

订阅专栏

PL / SQL

1 PL / SQL中常用的数据类型

oracle中的数据类型PL / SQL都支持。

PL / SQL的数据类型

record类型（类似Java中的Map）注：只能存放一条记录

table类型（类似Java中的List）

varray 可变长度数组类型

%type 基于某个表中的字段的类型

%rowtype 基于某个表中的所有字段的类型（和record类似）注：只能存放一条记录

cursor 游标数据类型

2 PL / SQL结构

declare（有遍历声明写）

-- 声明变量

begin（必写）

-- 处理SQL

[exception]（有异常写）

-- 处理异常

end;（必写）

示例：

declare
-- 声明变量
    v_name varchar2(20);
    v_id number := 1; -- := 是赋值符号
    
    -- 声明一个名字为myrecord的record的类型（只能存放一条数据）
    type myrecord is record (
        id number,
        -- name varchar2(50)
        -- 当不知道name的类型应该定义成什么时，可以用这种方式定义
        name t_user.name%type -- 表示name的类型是基于t_user表中的name字段的类型
    );
    -- 声明一个myrecord的变量
    v_record myrecord;
    
    -- 当声明的record的字段与某个表的字段都相同时，可以使用%rowtype声明（只能存放一条数据）
    v_record2 t_user%rowtype;
    
    -- 声明一个名字为mytable的table的类型（可以存放多条数据）
       -- List<String>
    type mytable is table of varchar2(50); -- of后面定义类型中的数据类型
    -- 声明一个mytable的变量
    v_table mytable;
    
    -- 声明一个名字为myarray，长度最大为50的varray类型（必须要指定最大长度）
    type myarray is varray(50) of number; -- of后面定义数据类型
    -- 声明一个myarray的变量
    v_array myarray := myarray(); -- 声明
    
    -- 游标
        -- is后跟查询语句
       cursor mycursor is
           select * from t_user;
begin
-- 处理SQL
    -- 查询用户表id为1的用户名，并打印出来
      -- 注：1. 使用into将查询的结果赋值给变量
      --     2. 多个字段赋值变量，变量与字段要一一对应，不然容易出现数据类型不一致
      -- select id, name into v_id, v_name from t_user where id=1;
      -- PL/SQL打印语句
      dbms_output.put_line(v_name);
    
    -- 使用record保存查询结果
      -- 注：1. 查询字段要与v_record中定义的类型和顺序要一致
      --     2. v_record中的字段不能比查询出的字段长度短
      select id, name into v_record from t_user where id=1;
      dbms_output.put_line(v_record.id || '---' || v_record.name);
    
    -- %rowtype的使用
    select * into v_record2 from t_user where id=1;
    dbms_output.put_line(v_record2.id || '---' || v_record2.name);
    
    -- table
    --select name into v_table from t_user;
    
    -- varray
      -- 往varray中存值
      v_array.extend(50); -- 扩容，必须先扩容才能存放
      -- 注：oracle中数组下标是从1开始，存放数据时可以不连续
      v_array(1) := 1;
      v_array(2) := 2;
      v_array(3) := 3;
      dbms_output.put_line(v_array(3)); -- 获取元素值
      dbms_output.put_line(v_array.count()); -- 获取长度
      dbms_output.put_line(v_array.limit()); -- 获取最大长度
end;

流程控制语句

1）判断语句

if 条件 then

语句

elsif 条件 then

语句

else 语句

end if; -- 必须写

示例：

-- 流程控制
declare
    v_name t_user.name%type;
begin
  select name into v_name from t_user where id=&id; -- &id是可以动态输入参数，运行时oracle会提示你输入值
  
  -- 判断语句(oracle中，=就是判断相等的符号)
  if v_name = 'zhangsan' then
      dbms_output.put_line('张三');
  elsif v_name = 'lisi' then
      dbms_output.put_line('李四');
  else
      dbms_output.put_line('王五');
  end if; -- 结束要写end if
end;

2）循环语句

loop exit when 退出条件 ... end loop;

while ... loop ... end loop;

for ... in ... loop ... end loop;

示例：

declare
    v_sum number := 0;
    i number := 1;
begin
  -- 求1到10的和
  -- 方式1
  loop
    exit when i >10; -- 退出循环的条件（不写退出条件默认是死循环）
    v_sum := v_sum + i;
    i := i + 1;
  end loop;
  -- 输出和
  dbms_output.put_line(v_sum);
  
  -- 方式2
  v_sum := 0;
  i := 1;
  while i<=10 loop
    v_sum := v_sum +i;
    i := i + 1;
  end loop;
  dbms_output.put_line(v_sum);
  
  -- 方式3
  v_sum := 0;
  i := 1;
  for k in 1..10 loop
    v_sum := v_sum +i;
    i := i + 1;
  end loop;
  dbms_output.put_line(v_sum);
end;

数据库游标

1）游标的四个属性

%found：表示后面还有记录

%notfound：表示已经提取完毕

%isopen：游标是否已经打开

%rowcount：代表fetch到的记录个数

2）遍历游标

示例1：

declare
    -- 1. 声明游标
    cursor cur is select * from t_user;
    myrecord t_user%rowtype; -- 声明保存一条t_user数据的变量
begin
    -- 2. 打开游标
    open cur;
    -- 3. 提取游标
    loop
      fetch cur into myrecord;
      exit when cur%notfound;
      dbms_output.put_line(myrecord.name);
    end loop;
    -- 4. 关闭游标
    close cur;
end;

示例2（掌握）：

declare
    -- 声明游标
    cursor cur is select * from t_user;
begin
    -- 注：使用for循环遍历游标不需要打开和关闭游标
    -- for循环会自动打开和关闭
    -- 提取游标
    for c in cur loop -- c：元素遍历名；cur：遍历对象
      dbms_output.put_line(c.name);
    end loop;
end;

存储过程

语法：

create or replace procedure cunqian ( -- create or replace代表创建或者替换

-- 传递的参数

-- 不允许指定参数类型的长度

-- out只能作为赋值和打印

) as

-- 声明变量

begin

-- 程序体

end;

参数的模式

in：表示参数只能输入（默认为in）

out：表示参数只能输出，out只能作赋值和打印

in out：表示参数既可以作为输入，也可以作为输出

示例：

-- 创建存储过程
create or replace procedure cunqian (
    v_id in number, -- 不允许指定类型的长度，默认参数模式是in类型
    v_email out varchar2, -- out只能做赋值和打印
    v_addmoney in out number -- 要增加的钱
) as
    v_money t_user_info.money%type; -- 保存查出的钱
begin
  -- 查询用户姓名和钱数
  select email, money into v_email, v_money from t_user_info where id=v_id;
  -- 将用户的钱加上添加的钱数
  v_addmoney := v_money + v_addmoney;
  -- 修改该用户的数据
  update t_user_info set money=v_addmoney where id=v_id;
  -- 提交事务
  commit;
end;
-- 调用存储过程
declare
    v_email varchar2(50);
    v_addmoney number := 10;
begin
  cunqian(1, v_email, v_addmoney);
  dbms_output.put_line(v_email);
end;

函数

语法

函数的参数模式一般为输入参数。

create or replace function 函数名(参数1, 参数2, ...)

return 数据类型

as/is

PRAGMA AUTONOMOUS_TRANSACTION; --开启自治事务，用来解决在查询语句中不能使用DML语句

-- 声明变量

begin

-- 程序

end;

示例：

-- 创建函数
create or replace function zhuanzhang(
    v_id number, -- 转账用户
    u_id number, -- 到账用户
    v_money t_user_info.money%type -- 转账金额
)
return number
is
    PRAGMA AUTONOMOUS_TRANSACTION; -- 开启自治事务
    v_m t_user_info.money%type; -- 存储用户金额
    v_n number; -- 存储用户个数
begin
    -- 查询转账用户
    select money into v_m from t_user_info where id=v_id;
    /*判断转账用户的金额是否满足转账*/
    if v_money < v_m then
      -- 满足转账
      v_m := v_m - v_money;
      update t_user_info set money=v_m where id=v_id;
      -- 判断到账用户是否存在
      select count(1) into v_n from t_user_info where id=u_id;
      if v_n > 0 then
        -- 到账用户存在
        update t_user_info set money=v_money+money where id=u_id;
        commit;
      else
        rollback;
      end if;
    end if;
    return (v_m);
end;
-- 调用函数
select zhuanzhang(1,2,36) from dual; -- 基于伪表

触发器（一般不推荐使用）

语法

create or replace trigger 触发器名

before/after insert/update/delete on 表名 [for each row]

-- for each row：行级触发，不写默认是表级触发

declare

begin

end;

示例1（使用触发器设置插入数据的主键id）：

create or replace trigger batchInsertUser
before insert on t_user for each row
declare
begin
  /* 往t_user表中插入数据时，使用触发器给id赋值 */
  -- :new代表赋值给新的id
  -- :old代表老值，一般更新、删除使用
  select t_user_seq.nextval into :new.id from dual;
  :new.name := '123'; -- 插入时将名字设置为’123’
end;
insert into t_user (name, password) values('zhouliu', '333');

-- 查看序列当前值
select t_user_seq.currval from dual;

示例2（备份数据）：

/*
    场景二：备份数据，在出入数据是备份用户id和插入时间
*/
-- 创建备份表
create table t_user_bak (
    userId number, -- 用户id
    createTime date -- 插入时间
)
-- 创建触发器
create or replace trigger user_bak
before insert on t_user for each row
declare
begin
  select t_user_seq.nextval into :new.id from dual;
  insert into t_user_bak(userId, createTime)
  values(:new.id, sysdate); -- sysdate: 获取系统当前时间
end;
insert into t_user (name, password) values('liming', '555');

select * from t_user;
select * from t_user_bak;

示例三：

/*
    场景三：修改数据前，将老数据备份到备份表中
*/
-- 创建备份表
create table t_user_bak2 as
select * from t_user where 1=2;
-- 创建触发器
create or replace trigger user_bak2
before update on t_user for each row
declare
begin
  insert into t_user_bak2(id, name, password, ip)
  values(:old.id, :old.name, :old.password, :old.ip);
end;
-- 修改t_user表的数据
update t_user set name='456' where id=13

select * from t_user;
select * from t_user_bak2;

动态SQL

语法

execute immediate ‘语句’ [using 参数1, 参数2, ...]

示例：

declare
  v_str varchar(100);
begin
  -- :v1是占位符
  v_str := 'update t_user set password=:v1 where id=:v2';
  execute immediate v_str using '555', 19;
end;

select * from t_user;

异常处理

语法

declare

v_name t_user.name%type;

myexception exception; -- 自定义异常

begin

exception

where 异常名 then

处理语句（例如打印）

end;

1）预定义异常的名字

ZERO_DIVIDE (除数不能为0)

NO_DATA_FOUND (没有查询到数据的异常)

注：异常名字可以通过文档查询。

示例：

declare
  v_name t_user.name%type;
begin
  select name into v_name from t_user where id=100;
  dbms_output.put_line(v_name);
exception
  when NO_DATA_FOUND then
    dbms_output.put_line('未找到数据');
end;

2）对其他异常的处理

when others then ...

示例：

declare
  v_name t_user.name%type;
begin
  select name into v_name from t_user where id=100;
  dbms_output.put_line(v_name);
exception
  when others then
    dbms_output.put_line('未找到数据');
end;

3）自定义异常

在声明变量的部分声明一个异常异常名 exception

raise 异常名; 抛出异常

示例：

declare
  v_name t_user.name%type;
  myexception exception; -- 定义自定义异常
begin
  raise myexception; -- raise: 抛出异常
exception
  when myexception then
    dbms_output.put_line('自定义处理异常');
end;

4）raise_application_error 来指定异常的 code 和 msg

其中code的设置范围是 -20000 到 -20999

获取code使用 SQLCODE

获取消息使用 SQLERRM

示例：

declare
begin
  raise_application_error(-20001,'错误信息');
exception
  when others then
    dbms_output.put_line(SQLCODE || SQLERRM);
end;

merge into语句

作用

既可以更新操作，也可以插入操作。

语法

merge into 表名1 别名1

using (table | view | sub_query) 别名2

on (join condition)

when matched then

update 表名

set col1 = col_val1

col2 = col_val2

when not matched then

insert (column_list) values (column_values);

Oracle中查询树结构的SQL语句

表结构：

create table t_user_tree (
    userId number primary key,
    username varchar2(50),
    password varchar2(32),
    pid number references t_user_tree(userId)
);

SQL语句：

select * from t_user_tree
start with userId = 1 // 根节点id
connect by prior userId = pid // 根节点=父节点

昔小年

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Java Web笔记（四） - PL / SQL

PL / SQL1 PL / SQL中常用的数据类型oracle中的数据类型PL / SQL都支持。PL / SQL的数据类型 record类型（类似Java中的Map）注：只能存放一条记录 table类型（类似Java中的List） varray 可变长度数组类型 %type 基于某个表中的字段的类型 %rowtype 基于某个表中的
复制链接

扫一扫