PL/SQL编程2

最新推荐文章于 2023-12-06 20:07:34 发布

菖草

最新推荐文章于 2023-12-06 20:07:34 发布

阅读量1.1k

点赞数 1

分类专栏： PL/SQL 文章标签： oracle PLSQL select into TYPE ROWTYPE

本文链接：https://blog.csdn.net/com185272358/article/details/21481493

版权

PL/SQL 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

需求：根据订单进行发货

第一步：检索商品表，根据订单中的商品id检索该商品的库存
第二步：判断库存量是否大于订单需求
第三步：更新商品库存
第四步：更新订单状态

分号的注意事项：

DECLARE,BEGIN和EXCEPTION后没有分号。而END以及所有SQL语句，和PL/SQL语句必须以分号结束

变量的定义语法：

DECLARE
variable_name type [NOT NULL] [:= value]

变量的命名规则：
1、变量名首字母必须是英文字母，其后可以是字母、数字或者特殊字符$、#号和下划线
2、变量名长度不超过30个字符
3、变量名中不能有空格
4、变量名不能使用关键字命名

SELECT INTO语法

SELECT select_list
INTO {variable_name[,variable_name]...}
FROM table_name
[WHERE condition];

注意：
1、查询只能返回一条记录，多条或0条都会产生异常
2、INTO语句必须在SELECT和FROM子句之间
3、INTO后需要赋值的变量的数据类型必须和SELECT子句中检索出的数据类型一致

4、INTO后需要赋值的变量个数需要和SELECT查询出的字段数一致

第一步：检索商品表，根据订单中的商品id检索该商品的库存

DECLARE
  v_stock_count NUMBER;
  v_id NUMBER :=&ID;
BEGIN
  SELECT STOCKCOUNT INTO v_stock_count 
  FROM es_product
  WHERE ID = v_id;
  Dbms_Output.put_line(v_stock_count);
END;

通过SELECT INTO进行变量赋值，假设查询字段非常多，或者你根本不知道数据库字段数据类型的时候怎么办呢？
以列名或变量名作为前缀来声明变量：
变量名表名.列名%TYPE;
变量名其他变量名%TYPE;

--根据商品id查询商品名称、商品价格、上架时间、库存，并进行连接输出

DECLARE
  v_name       es_product.name%TYPE;
  v_price      es_product.price%TYPE;
  v_saledate   es_product.saledate%TYPE;
  v_stockcount es_product.stockcount%TYPE;
  v_id NUMBER := 1;
BEGIN
  SELECT NAME,price,saledate,stockcount 
  INTO v_name,v_price,v_saledate,v_stockcount
  FROM es_product
  WHERE ID = v_id;
  Dbms_Output.put_line('商品名称：' || v_name
    || '商品价格：' || v_price
    || '上架时间：' || v_saledate
    || '库存：' || v_stockcount);
END;

针对上面的单个变量，可不可以声明一个变量存储多个数据呢？
以某张表的完整行来声明变量：
变量名表名%ROWTYPE;

DECLARE
  v_es_proc_record es_product%ROWTYPE;
  v_id NUMBER := 1;
BEGIN
  SELECT NAME,price,saledate,stockcount 
  INTO v_es_proc_record.name,v_es_proc_record.price,
  v_es_proc_record.saledate,v_es_proc_record.stockcount
  FROM es_product
  WHERE ID = v_id;
  Dbms_Output.put_line('商品名称：' || v_es_proc_record.name
    || '商品价格：' || v_es_proc_record.price
    || '上架时间：' || v_es_proc_record.saledate
    || '库存：' || v_es_proc_record.stockcount);
END;

第二步：判断库存量是否大于订单需求
--使用 IF THEN实现购买商品时更新库存量

DECLARE
  v_id es_product.id%TYPE := &ID;
  v_stock_count es_product.stockcount%TYPE;
BEGIN
  SELECT STOCKCOUNT INTO v_stock_count 
  FROM es_product
  WHERE ID = v_id;
  IF v_stock_count > 0 THEN
    UPDATE es_product
    SET stockcount = stockcount - 1
    WHERE ID = v_id;
    COMMIT;
    Dbms_Output.put_line('id：' || v_id || '库存量已更新');
  ELSIF v_stock_count < 0 THEN
    Dbms_Output.put_line('id：' || v_id || '库存数量 < 0 不正常的数据');
  ELSE
    Dbms_Output.put_line('id：' || v_id || '已经没有库存');
  END IF;
END;

--根据订单的状态值输出对应的状态(IF结构)

DECLARE
  v_id es_order.id%TYPE := &ID;
  v_status es_order.status%TYPE;
  v_status_name VARCHAR2(20);
BEGIN
  SELECT status
  INTO v_status
  FROM es_order
  WHERE ID = v_id;
  IF v_status = '1' THEN
    v_status_name := '订单已提交';
  ELSIF v_status = '2' THEN
    v_status_name := '已付款';
  ELSIF v_status = '3' THEN
    v_status_name := '货物已发出';
  ELSIF v_status = '4' THEN
    v_status_name := '已完成';
  ELSE
    v_status_name := '未知';
  END IF;
  Dbms_Output.put_line('订单id：' || v_id || '，订单状态：' || v_status_name);
END;

--根据订单的状态值输出对应的状态(CASE等值)

DECLARE
  v_id es_order.id%TYPE := &ID;
  v_status es_order.status%TYPE;
  v_status_name VARCHAR2(20);
BEGIN
  SELECT status
  INTO v_status
  FROM es_order
  WHERE ID = v_id;
  CASE v_status
    WHEN '1' THEN
      v_status_name := '订单已提交';
    WHEN '2' THEN
      v_status_name := '已付款';
    WHEN '3' THEN
      v_status_name := '货物已发出';
    WHEN '4' THEN
      v_status_name := '已完成';
    ELSE
      v_status_name := '未知';
  END CASE;
  Dbms_Output.put_line('订单id：' || v_id || '，订单状态：' || v_status_name);
END;

--根据订单的状态值输出对应的状态(CASE等值另外写法)

DECLARE
  v_id es_order.id%TYPE := &ID;
  v_status es_order.status%TYPE;
  v_status_name VARCHAR2(20);
BEGIN
  SELECT status
  INTO v_status
  FROM es_order
  WHERE ID = v_id;
  CASE
    WHEN v_status = '1' THEN
      v_status_name := '订单已提交';
    WHEN v_status =  '2' THEN
      v_status_name := '已付款';
    WHEN v_status =  '3' THEN
      v_status_name := '货物已发出';
    WHEN v_status =  '4' THEN
      v_status_name := '已完成';
    ELSE
      v_status_name := '未知';
  END CASE;
  Dbms_Output.put_line('订单id：' || v_id || '，订单状态：' || v_status_name);
END;

CASE区别：
第一种常用在等值判断的情况
第二种CASE结构除了可以用在等值的情况下还可以用在不等值情况

--根据商品ID查询商品价格，价格大于1000输出'很贵'，价格在100~1000之间输出'一般'，
--价格在100以下输出'便宜'，怎么实现呢？

DECLARE
  v_id es_product.id%TYPE := &ID;
  v_price es_product.price%TYPE;
  v_message VARCHAR2(100);
BEGIN
  SELECT price
  INTO v_price
  FROM es_product
  WHERE ID = v_id;
  CASE
    WHEN v_price > 1000 THEN
      v_message := '很贵';
    WHEN v_price >= 100 THEN
      v_message := '一般';
    ELSE
      v_message := '便宜';
  END CASE;
  Dbms_Output.put_line('id：' || v_id || '价格：' || v_message);
END;

LOOP循环语法

LOOP
  代码块;
  ...
  EXIT [WHEN condition];
END LOOP;

弊端
1、就是循环体可读性差，必须通过EXIT退出循环；
2、就是EXIT容易忘记，或者退出循环的条件如果设置不正确就可能造成死循环。

FOR循环语法
FOR 循环变量 IN 循环开始 ... 循环结束 LOOP
--需要循环执行的代码块
END LOOP;

--异常处理

DECLARE
  v_id es_product.id%TYPE := &ID;
  v_stock_count es_product.stockcount%TYPE;
BEGIN
  SELECT STOCKCOUNT INTO v_stock_count 
  FROM es_product
  WHERE ID = v_id;
  IF v_stock_count > 0 THEN
    UPDATE es_product
    SET stockcount = stockcount - 1
    WHERE ID = v_id;
    COMMIT;
    Dbms_Output.put_line('id：' || v_id || '库存量已更新');
  ELSE
    Dbms_Output.put_line('id：' || v_id || '已经没有库存');
  END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
     Dbms_Output.put_line('数据库中没有id为：' || v_id || '的数据');
     ROLLBACK;
    WHEN TOO_MANY_ROWS THEN
      Dbms_Output.put_line('数据库存在多条记录');
      ROLLBACK;
    WHEN OTHERS THEN
      Dbms_Output.put_line('发生其它错误');
      ROLLBACK;
END;

--自定义异常处理
--捕捉"删除订单"的异常

DECLARE
  v_id es_order.id%TYPE := &ID;
  e_no_exception EXCEPTION;
  --使用常量定义异常信息
  v_exception1 CONSTANT VARCHAR(20) := '删除的数据不成功';
  v_exception2 CONSTANT VARCHAR(20) := '发生其它异常';
BEGIN
  DELETE es_order WHERE ID = v_id;
  IF SQL%NOTFOUND THEN
    RAISE e_no_exception;--触发异常
  END IF;
  EXCEPTION
    WHEN e_no_exception THEN
      Dbms_Output.put_line(v_exception1);
      ROLLBACK;
    WHEN OTHERS THEN
      Dbms_Output.put_line(v_exception2);
      ROLLBACK;
END;

表结构：

DROP TABLE es_orderdetail;
DROP TABLE es_order;
DROP TABLE es_product;
DROP TABLE es_sort;
DROP TABLE es_user;

--创建会员表
create table es_user(
  id number primary key,--用户编号
  username varchar2(20) not null,--用户名
  password varchar2(20) not null,--密码
  realname varchar2(20),--用户姓名
  tel varchar2(20),--用户电话
  address varchar2(100),--用户地址
  zip varchar(6),--邮政编码
  email varchar2(50),--电子邮件
  usertype number(5,0) --用户类型 0：管理员 1：普通会员 2：VIP会员
);
--创建商品表
create TABLE es_product(
  id number primary key,--商品编号
  sort_id number not null,--商品分类编号
  name varchar2(20) not null,--商品名称
  private number not null,--商品价格
  description varchar2(500) not null,--商品描述
  contents varchar2(2000),--商品详细介绍
  saledate date,--上货日期
  stockcount number--库存数量
);
--创建商品类别表
create TABLE es_sort(
  id number primary key,--商品分类编号
  sortname varchar2(40)--商品分类名称
);
--创建订单表
create table es_order(
  id number primary key,--订单编号
  user_id number not null,--用户编号
  realname varchar2(20) not null,--收货人姓名
  address varchar2(100),--收货人地址
  zip varchar2(6),--邮政编码
  tel varchar2(20),--联系电话
  email varchar2(50),--电子邮件
  payment varchar2(20),--付款方式
  createtime date not null,--订单生成时间
  totalamount number(10,2) not null,--订单总额
  status number not null--订单状态 1：订单已提交 2：已付款 3：货物已发出 4：已完成
);
--创建订单明细表
create TABLE es_orderdetail(
  id number primary key,--订单明细编号
  order_id number not null,--订单编号
  prod_id number not null,--商品编号
  price number not null,--订购价格
  count number not null--订购数量
);
--商品表添加外键
alter table es_product
add constraint FK_PRODUCT_SORT_ID foreign key(sort_id)
references es_sort(id);
--订单表添加外键
alter table es_order
add constraint FK_ORDER_USER_ID foreign key(user_id)
references es_user(id);
--订单明细表添加外键
alter table es_orderdetail
ADD CONSTRAINT FK_ORDERDETAIL_ORDER_ID foreign key(order_id)
references es_order(id);
alter table es_orderdetail 
ADD CONSTRAINT  FK_ORDERDETAIL_PRODUCT_ID foreign key(prod_id)
references es_product(id);

总结：

PL/SQL的特点
1、模块化程序开发
2、采用过程性语言控制程序的结构
3、PL/SQL可以地程序中的错误进行异常处理
4、PL/SQL程序减少了网络的交互，提高程序性能
5、PL/SQL程序块具有更好的可移植性

菖草

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
PL/SQL编程2

需求：根据订单进行发货第一步：检索商品表，根据订单中的商品id检索该商品的库存第二步：判断库存量是否大于订单需求第三步：更新商品库存第四步：更新订单状态分号的注意事项：DECLARE,BEGIN和EXCEPTION后没有分号。而END以及所有SQL语句，和PL/SQL语句必须以分号结束变量的定义语法：DECLAREvariable_name type [NOT
复制链接

扫一扫