ORACLE之建表、建序列、建触发器SQL语句

最新推荐文章于 2024-09-25 17:49:34 发布

自然鸟神

最新推荐文章于 2024-09-25 17:49:34 发布

阅读量2.4k

点赞数

分类专栏： ORACLE数据库

本文链接：https://blog.csdn.net/chenpeng19910926/article/details/56673354

版权

本文分享了在生产环境中创建Oracle数据库表、序列及触发器的SQL语句实例，为数据库运维提供参考。

摘要由CSDN通过智能技术生成

今天在生产上准备把新表加入到库中，运维在建表等反面给了一些案例和建议和大家共勉

1 标准TIGGER写法参考如下


  CREATE OR REPLACE TRIGGER "QRPAY"."APP_INFO_ID_TRG" BEFORE INSERT OR UPDATE ON  app_info
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.id IS NULL THEN
    SELECT   app_info_id_seq.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN
      --get the max indentity value from the table
      SELECT NVL(max(id),0) INTO v_newVal FROM  app_info;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT  app_info_id_seq.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
    --used to emulate LAST_INSERT_ID()
    --mysql_utilities.identity := v_newVal;
   -- assign the valu