以前一直使用mysql,第一次用oracle,有很多比较坑的细节问题,sql语句还是有差别的。
本文的使用场景是订单管理,使用了oracle存储过程、游标、视图、序列。(100多行的存储过程写完,感觉自己要无敌了,从没写过这么长的存储过程)。
由于oracle安装比较麻烦,而且卸载不干净,所以我就在我的win10笔记本上安装docker,然后在docker上运行oracle(docker运行oracle教程)使用navicat12进行连接,在navicat12中进行操作。navicat12可以直接连接oracle,navicat11需要下载连接实例。
navicat12 软件包,及注册机。
https://download.youkuaiyun.com/download/qq_34586870/11274760
1 准备工作
建表
CREATE TABLE customer(
--客户ID--
ID NUMBER(12) PRIMARY KEY,
--客户姓名
NAME NVARCHAR2(50) Unique,
--手机
PHONE VARCHAR2(50) unique ,
--qq
QQ VARCHAR2(50),
--email
EMAIL VARCHAR2(100),
--状态
STATUS NUMBER(1), --0|注销;1|在用
--密码
PASSWORD VARCHAR2(100),
--备注
MEMO VARCHAR(250)
);
CREATE TABLE product(
--产品ID
ID NUMBER(12) PRIMARY key,
--名称
NAME Varchar2(100),
--价格
PRICE NUMBER(8,2),
--库存
QUANTITY NUMBER(6),
--上架日期
CRAATE_DATE DATE DEFAULT SYSDATE,
--最后修改日期
LAST_MODIFY_DATE DATE,
--状态
STATUS NUMBER(1)
--0|注销;1|在用
)
CREATE TABLE t_order(
--订单号
ID NUMBER(12) PRIMARY key,
--订单流水号
ORDERNO VARCHAR2(16),
--客户ID
CUSTOMERID NUMBER(12),
--下单时间
ORDER_DATE DATE default sysdate,
--状态
STATUS NUMBER(1), --0|取消;1|待处理;2|已完成
--备注
MEMO VARCHAR2(500))
CREATE TABLE order_item(
--订单明细ID
ID NUMBER(5) PRIMARY key,
--订单号
ORDERID NUMBER(12),
--产品ID
PRODUCTID NUMBER(12),
--购买数量
QUANTITY NUMBER(8),
--单价
UNIT_PRICE NUMBER(8,2))
建序列
create sequence seq_customer
start with 1
increment by 1
nomaxvalue
cache 10;
create sequence seq_product
start with 1
increment by 1
nomaxvalue
cache 10;
create sequence seq_t_order
start with 1
increment by 1
nomaxvalue
cache 10;
create sequence seq_order_item
start with 1
increment by 1
nomaxvalue
cache 10;
2 功能
下单
一个订单可购买多个商品
(购买产品,可以购买多个产品,注意判断产品库存是否足够,购买完成后要更新库存)
CREATE OR REPLACE PROCEDURE PRO_KHGL_XDD ( V_CUS_ID IN NUMBER, PRO_ID_ARR IN VARCHAR2, PRO_COUNT_ARR IN VARCHAR, MEMO IN VARCHAR, V_RESULT OUT VARCHAR2 ) IS V_PRO_ID NUMBER ( 12 );
V_PRO_COUNT NUMBER ( 6 );
V_INDEX_ID NUMBER;
V_INDEX_COUNT NUMBER;
V_ORDER_ID NUMBER;
TEMP NUMBER;
V_PRICE NUMBER;
V_PRO_ID_ARR VARCHAR2 ( 4000 ) := PRO_ID_ARR;
V_PRO_COUNT_ARR VARCHAR2 ( 4000 ) := PRO_COUNT_ARR;
F_LEN_ID NUMBER ( 2 );
F_LEN_COUNT NUMBER ( 2 );
BEGIN
IF
V_PRO_ID_ARR IS NOT NULL
AND V_PRO_COUNT_ARR IS NOT NULL THEN
SELECT
TO_NUMBER( INSTR( V_PRO_ID_ARR, ',' ) ) INTO V_INDEX_ID
FROM
DUAL;
SELECT
TO_NUMBER( INSTR( PRO_COUNT_ARR, ',' ) ) INTO V_INDEX_COUNT
FROM
DUAL;
IF
V_INDEX_ID = 0 THEN--只购买一个产品
SELECT
SEQ_T_ORDER.NEXTVAL INTO V_ORDER_ID
FROM
DUAL;--订单ID
V_PRO_ID := TO_NUMBER( V_PRO_ID_ARR );--产品ID
V_PRO_COUNT := TO_NUMBER( V_PRO_COUNT_ARR );--产品数量
SELECT
COUNT( * ) INTO TEMP
FROM
PRODUCT P
WHERE
P.QUANTITY >= V_PRO_COUNT
AND P.ID = V_PRO_ID;--判断数量余量是否足够
IF
TEMP > 0 THEN
INSERT INTO T_ORDER
VALUES
( V_ORDER_ID, 'NO.' || TO_CHAR( V_ORDER_ID, '000000' ), V_CUS_ID, SYSDATE, 1, MEMO );--插入T_ORDER
SELECT
P.PRICE INTO V_PRICE
FROM
PRODUCT P
WHERE
P.ID = V_PRO_ID;--查询商品价格
INSERT INTO ORDER_ITEM
VALUES
( SEQ_ORDER_ITEM.NEXTVAL, V_ORDER_ID, V_PRO_ID, V_PRO_COUNT, V_PRICE );--插入商品详细信息
UPDATE PRODUCT P
SET P.QUANTITY = P.QUANTITY - V_PRO_COUNT,
P.LAST_MODIFY_DATE = SYSDATE
WHERE
P.ID = V_PRO_ID;--更新产品数量
COMMIT;
V_RESULT := '订单成功!1';
END IF;
ELSE --购买多个产品
SELECT
length( V_PRO_ID_ARR ) - length( REPLACE ( V_PRO_ID_ARR, ',', '' ) ) INTO F_LEN_ID
FROM
dual;
SELECT
length( V_PRO_COUNT_ARR ) - length( REPLACE ( V_PRO_COUNT_ARR, ',', '' ) ) INTO F_LEN_COUNT
FROM
dual;
IF
F_LEN_ID != F_LEN_COUNT THEN
V_RESULT := '商品和价格数量不匹配!';
return;
END IF;
SELECT
SEQ_T_ORDER.NEXTVAL INTO V_ORDER_ID
FROM
DUAL;--订单ID
INSERT INTO T_ORDER
VALUES
( V_ORDER_ID, 'NO.' || TO_CHAR( V_ORDER_ID, '000000' ), V_CUS_ID, SYSDATE, 1, MEMO );--插入T_ORDER表
LOOP-- 循环插入订单详情商品
dbms_output.put_line ( 'V_INDEX_ID1=' || V_INDEX_ID );
SELECT-- 返回第一个","在V_PRO_ID_ARR中的下标,从1开始,注意:当最后一次时,V_PRO_ID_ARR为 1 ,返回的index为0,按照之前的取值失败,所以在下面取值的时候有差异。
TO_NUMBER( INSTR( V_PRO_ID_ARR, ',' ) ) INTO V_INDEX_ID
FROM
DUAL;
SELECT-- 返回第一个","在V_PRO_ID_ARR中的下标,从1开始。同上
TO_NUMBER( INSTR( V_PRO_COUNT_ARR, ',' ) ) INTO V_INDEX_COUNT
FROM
DUAL;
dbms_output.put_line ( 'V_INDEX_ID3=' || V_INDEX_ID );
IF
V_INDEX_ID = 0 THEN -- 当最后一次时,V_PRO_ID_ARR直接复制给V_PRO_ID
V_PRO_ID := TO_NUMBER( V_PRO_ID_ARR );
V_PRO_COUNT := TO_NUMBER( V_PRO_COUNT_ARR );
ELSE V_PRO_ID := SUBSTR( V_PRO_ID_ARR, 0, V_INDEX_ID - 1 ); -- 不是最后一次时,截取第一个字符至第一个“,”,并赋值给V_PRO_ID
V_PRO_COUNT := SUBSTR( V_PRO_COUNT_ARR, 0, V_INDEX_COUNT - 1 );
END IF;
SELECT
count( * ) INTO TEMP
FROM
PRODUCT P
WHERE
P.QUANTITY >= V_PRO_COUNT
AND P.ID = V_PRO_ID;--判断数量余量是否足够
dbms_output.put_line ( 'V_PRO_ID=' || V_PRO_ID || ', V_PRO_COUNT=' || V_PRO_COUNT || ', TEMP=' || TEMP );
IF
TEMP > 0 THEN
SELECT
P.PRICE INTO V_PRICE
FROM
PRODUCT P
WHERE
P.ID = V_PRO_ID;
INSERT INTO ORDER_ITEM --插入ORDER_ITEM表
VALUES
( SEQ_ORDER_ITEM.NEXTVAL, V_ORDER_ID, V_PRO_ID, V_PRO_COUNT, V_PRICE );
UPDATE PRODUCT P --更新商品数量
SET P.QUANTITY = P.QUANTITY - V_PRO_COUNT,
P.LAST_MODIFY_DATE = SYSDATE
WHERE
P.ID = V_PRO_ID;
ELSE V_RESULT := '商品' || V_PRO_ID || ',库存不足'; -- 如果库存不足,返回提示,并回滚
dbms_output.put_line ( 'V_RESULT=' || V_RESULT );
rollback;
RETURN;
END IF;
dbms_output.put_line ( 'V_PRO_ID_ARR this=' || V_PRO_ID_ARR );
V_PRO_ID_ARR := SUBSTR( V_PRO_ID_ARR, V_INDEX_ID + 1 );
V_PRO_COUNT_ARR := SUBSTR( V_PRO_COUNT_ARR, V_INDEX_COUNT + 1 );
dbms_output.put_line ( 'V_PRO_ID_ARR next=' || V_PRO_ID_ARR );
EXIT
WHEN V_INDEX_ID = 0;
END LOOP;
V_RESULT := '订单成功!2';
END IF;
END IF;
END PRO_KHGL_XDD;
取消订单
CREATE OR REPLACE PROCEDURE pro_hk_ddqx ( order_id IN NUMBER, resoult OUT VARCHAR2 ) IS BEGIN
DECLARE
cursor oorder IS SELECT
quantity,
productid
FROM
order_item
WHERE
ORDERID = order_id;
oquantity order_item.quantity % TYPE;
oproductid order_item.productid % TYPE;
ostatus t_order.status % TYPE;
num NUMBER ( 2 );
BEGIN-- loop 不同位置结束条件测试
-- open oorder;
-- LOOP
-- FETCH oorder INTO oquantity,-- 先fetch再exit when,正常执行
-- oproductid;
-- exit
-- WHEN oorder % notfound;
-- dbms_output.put_line ( 'oproductid=' || oproductid || ', oquantity=' || oquantity );
--
-- END loop;
-- close oorder;
-- dbms_output.put_line ( '|' );
-- dbms_output.put_line ( '|' );
-- open oorder;
-- LOOP
-- exit -- 先exit when再fetch,最后一条数据会执行两次
--
-- WHEN oorder % notfound;
-- FETCH oorder INTO oquantity,
-- oproductid;
-- dbms_output.put_line ( 'oproductid=' || oproductid || ', oquantity=' || oquantity );
--
-- END loop;
-- close oorder;
-- return;
-- 测试结束
SELECT
status INTO ostatus
FROM
t_order
WHERE
id = order_id;
CASE
ostatus
WHEN 0 THEN
resoult := '订单已为取消状态!';
return;
WHEN 1 THEN
resoult := '正在取消。。。';
WHEN 2 THEN
resoult := '订单已完成,无法取消!';
return;
END CASE;
open oorder;
--0|取消;1|待处理;2|已完成
LOOP
FETCH oorder INTO oquantity,
oproductid;
exit
WHEN oorder % notfound;
dbms_output.put_line ( 'oproductid=' || oproductid || ', oquantity=' || oquantity );
--还原商品数量
UPDATE product p
SET p.quantity = p.quantity + oquantity,
p.last_modify_date = SYSDATE
WHERE
p.id = oproductid;
END loop;
close oorder;
-- 更新订单状态
UPDATE t_order
SET status = 0
WHERE
id = order_id;
resoult := '取消成功';
EXCEPTION
WHEN no_data_found THEN
resoult := '订单不存在!';
WHEN others THEN
resoult := '取消失败';
END;
END;
查询销量top5的商品
-- 新建视图
CREATE OR REPLACE VIEW "REXIAOVIEW" AS SELECT
ORDER_ITEM.ID,
ORDER_ITEM.PRODUCTID,
ORDER_ITEM.QUANTITY,
PRODUCT.NAME
FROM
ORDER_ITEM
INNER JOIN PRODUCT ON ORDER_ITEM.PRODUCTID = PRODUCT.ID
-- 存储过程
CREATE
OR REPLACE PROCEDURE "rexiao" ( resoult OUT SYS_REFCURSOR ) AS BEGIN
open resoult FOR SELECT
*
FROM
( SELECT name, sum( QUANTITY ) FROM rexiaoview GROUP BY productid, name ORDER BY sum( QUANTITY ) DESC )
WHERE
ROWNUM <= 5;
END;