44 lines
1.3 KiB
SQL
44 lines
1.3 KiB
SQL
-- 1. 收货单明细表新增尺寸字段
|
|
ALTER TABLE RECEIPT_MATERIAL_DETAIL ADD DIMENSIONS VARCHAR2(255);
|
|
COMMENT ON COLUMN RECEIPT_MATERIAL_DETAIL.DIMENSIONS IS '尺寸';
|
|
|
|
-- 2. 上架单明细表新增尺寸字段
|
|
ALTER TABLE SHELF_MATERIAL_DETAIL ADD DIMENSIONS VARCHAR2(255);
|
|
COMMENT ON COLUMN SHELF_MATERIAL_DETAIL.DIMENSIONS IS '尺寸';
|
|
|
|
-- 拣货单明细新增规格字段 (SIZE)
|
|
ALTER TABLE PICKING_MATERIAL_DETAIL ADD "SIZE" VARCHAR2(255);
|
|
COMMENT ON COLUMN PICKING_MATERIAL_DETAIL."SIZE" IS '尺寸';
|
|
|
|
-- 1) 创建序列（若已存在会报错，可先跳过或改名）
|
|
CREATE SEQUENCE SEQ_MATERIAL_BASE_INFO_ID
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NOCACHE
|
|
NOCYCLE;
|
|
|
|
-- 2) 将序列起始值调整到“当前最大ID+1”
|
|
DECLARE
|
|
v_next BIGINT;
|
|
BEGIN
|
|
SELECT NVL(MAX(MATERIAL_BASE_INFO_ID), 0) + 1
|
|
INTO v_next
|
|
FROM MATERIAL_BASE_INFO;
|
|
|
|
EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_MATERIAL_BASE_INFO_ID';
|
|
EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_MATERIAL_BASE_INFO_ID START WITH ' || v_next || ' INCREMENT BY 1 NOCACHE NOCYCLE';
|
|
END;
|
|
/
|
|
|
|
-- 3) 创建触发器：仅在主键为空时自动赋值
|
|
CREATE OR REPLACE TRIGGER TRG_MATERIAL_BASE_INFO_BI
|
|
BEFORE INSERT ON MATERIAL_BASE_INFO
|
|
FOR EACH ROW
|
|
BEGIN
|
|
IF :NEW.MATERIAL_BASE_INFO_ID IS NULL THEN
|
|
SELECT SEQ_MATERIAL_BASE_INFO_ID.NEXTVAL
|
|
INTO :NEW.MATERIAL_BASE_INFO_ID
|
|
FROM DUAL;
|
|
END IF;
|
|
END;
|
|
/ |