oracle 存储过程订单下单，取消，热销查询

最新推荐文章于 2023-06-09 19:49:32 发布

loockluo

最新推荐文章于 2023-06-09 19:49:32 发布

阅读量856

点赞数 5

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： oracle 存储过程

本文链接：https://blog.youkuaiyun.com/qq_34586870/article/details/94603342

数据库专栏收录该内容

1 篇文章

订阅专栏

本文分享了在订单管理场景中使用Oracle数据库的经验，包括存储过程、游标、视图和序列的应用。通过具体案例，如创建订单、取消订单和查询热销商品，展示了Oracle的强大功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

以前一直使用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;