物料表, Truncate 表，插入数据存储过程

最新推荐文章于 2024-07-19 08:00:00 发布

原创最新推荐文章于 2024-07-19 08:00:00 发布 · 621 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #sql #table #exception #insert #null

本文介绍了一个Oracle存储过程，用于创建并填充一个名为T_ITEM的表，该表包含多个字段如物料ID、名称、型号等，并定义了每个字段的注释说明。

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

CREATE OR REPLACE PROCEDURE P_T_ITEM IS V_ROWLINE NUMBER; V_SQL LONG; V_HASTABLE NUMBER; BEGIN V_ROWLINE := 0; /*写入日志*/ P_LOG(SYSDATE, 'T_ITEM', 0, '', '开始'); EXECUTE IMMEDIATE 'SELECT * FROM DUAL@DBLK1'; SELECT COUNT(1) INTO V_HASTABLE FROM USER_TABLES WHERE TABLE_NAME = UPPER('T_ITEM'); IF V_HASTABLE > 0 THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE T_ITEM'; EXECUTE IMMEDIATE 'DROP TABLE T_ITEM'; END IF; V_SQL := 'CREATE TABLE T_ITEM ( F_INTITEMID NUMBER(10) NOT NULL, F_STRMODEL NVARCHAR2(255), F_STRNAME NVARCHAR2(80) NOT NULL, F_STRSYMBOL NVARCHAR2(50), F_STRNUMBER NVARCHAR2(80), F_INTMEASUREUNITID NUMBER(10), F_NUMSALEPRICE NUMBER(28,10), F_INTKEEPPERIOD NUMBER(28,10), F_INTATTRIBUTE NUMBER, F_STRNOTE NVARCHAR2(500) )'; EXECUTE IMMEDIATE V_SQL; V_SQL := 'COMMENT ON TABLE T_ITEM IS ''物料信息表'''; EXECUTE IMMEDIATE V_SQL; V_SQL := 'COMMENT ON COLUMN T_ITEM.F_INTITEMID IS ''主键'''; EXECUTE IMMEDIATE V_SQL; V_SQL := 'COMMENT ON COLUMN T_ITEM.F_STRMODEL IS ''型号'''; EXECUTE IMMEDIATE V_SQL; V_SQL := 'COMMENT ON COLUMN T_ITEM.F_STRNAME IS ''名称'''; EXECUTE IMMEDIATE V_SQL; V_SQL := 'COMMENT ON COLUMN T_ITEM.F_STRSYMBOL IS ''助记码'''; EXECUTE IMMEDIATE V_SQL; V_SQL := 'COMMENT ON COLUMN T_ITEM.F_STRNUMBER IS ''编号'''; EXECUTE IMMEDIATE V_SQL; V_SQL := 'COMMENT ON COLUMN T_ITEM.F_INTMEASUREUNITID IS ''计量单位编号'''; EXECUTE IMMEDIATE V_SQL; V_SQL := 'COMMENT ON COLUMN T_ITEM.F_NUMSALEPRICE IS ''单价'''; EXECUTE IMMEDIATE V_SQL; V_SQL := 'COMMENT ON COLUMN T_ITEM.F_INTKEEPPERIOD IS ''保质期'''; EXECUTE IMMEDIATE V_SQL; V_SQL := 'COMMENT ON COLUMN T_ITEM.F_INTATTRIBUTE IS ''物料属性'''; EXECUTE IMMEDIATE V_SQL; V_SQL := 'COMMENT ON COLUMN T_ITEM.F_STRNOTE IS ''备注'''; EXECUTE IMMEDIATE V_SQL; V_SQL := 'CREATE UNIQUE INDEX T_ITEM_INDEX ON T_ITEM (F_INTITEMID)'; EXECUTE IMMEDIATE V_SQL; EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO T_ITEM NOLOGGING SELECT "FItemID", "FModel", "FName", "FHelpCode", "FNumber", "FOrderUnitID", "FSalePrice", "FKFPeriod", "FErpClsID", '''' FROM T_ICITEM@DBLK1'; V_ROWLINE := V_ROWLINE + SQL%ROWCOUNT; COMMIT; --更新日志 P_LOG(SYSDATE, 'T_ITEM', V_ROWLINE, '', '成功'); EXCEPTION WHEN OTHERS THEN /*更新日志*/ P_LOG(SYSDATE, 'T_ITEM', 0, SQLERRM, '失败'); ROLLBACK; END;