PL/SQL编程2

需求:根据订单进行发货

第一步:检索商品表,根据订单中的商品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
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值