生成单号 by Oracle proc

最新推荐文章于 2024-03-28 10:52:47 发布

jger_520

最新推荐文章于 2024-03-28 10:52:47 发布

阅读量464

点赞数

文章标签： oracle insert null table

本文链接：https://blog.csdn.net/jger_520/article/details/7734617

版权

1.创建表

create table T_SN

(
code   CHAR(1),
type   NUMBER(4),
years NUMBER(4),
org    NUMBER(8),
val1   NUMBER(4),
val2   NUMBER(3),
status NUMBER(1)

)

2.创建proc

CREATE OR REPLACE PROCEDURE find_no
(
i_code IN char,
i_type IN number,
i_years IN number,
i_org IN number,
o_no OUT varchar2
)
AS
current_val1 number;
current_val2 number;
is_exists number;
BEGIN

      IF (i_code != 'P2') THEN

        SELECT COUNT(1) INTO is_exists FROM T_SN WHERE code=i_code AND type=i_type AND years=i_years AND org=i_org;
        IF (is_exists != 0) THEN
          UPDATE T_SN SET VAL1 = VAL1 + 1 WHERE code=i_code AND type=i_type AND years=i_years AND org=i_org;
          SELECT VAL1 INTO current_val1 FROM T_SN WHERE code=i_code AND type=i_type AND years=i_years AND org=i_org;
          o_no := i_code || lpad(i_type,4,'0') || lpad(i_years,4,'0') || lpad(i_org,8,'0') || lpad(current_val1,4,'0');
          dbms_output.put_line('no = ' || o_no);
          COMMIT;

        ELSIF (is_exists = 0) THEN
          INSERT INTO T_SN VALUES(i_code,i_type,i_years,i_org,0001,null,null);
          o_no := i_code || lpad(i_type,4,'0') || lpad(i_years,4,'0') || lpad(i_org,8,'0') || '0001';
          dbms_output.put_line('no = ' || o_no);
          COMMIT;

        END IF;

      ELSE
        SELECT COUNT(1) INTO is_exists FROM T_SN WHERE code='P' AND type=i_type AND years=i_years AND org=i_org AND STATUS=1;
        IF (is_exists != 0) THEN
          UPDATE T_SN SET VAL2 = VAL2 + 1 WHERE code='P' AND type=i_type AND years=i_years AND org=i_org AND STATUS=1;
          SELECT VAL1,VAL2 INTO current_val1,current_val2 FROM T_SN WHERE code='P' AND type=i_type AND years=i_years AND org=i_org AND STATUS=1;
          o_no := i_code || lpad(i_type,4,'0') || lpad(i_years,4,'0') || lpad(i_org,8,'0') || lpad(current_val1,4,'0') || lpad(current_val2,3,'0');
          dbms_output.put_line('no = ' || o_no);
          COMMIT;

        ELSIF (is_exists = 0) THEN
          INSERT INTO T_SN VALUES('P',i_type,i_years,i_org,0001,001,1);
          o_no := i_code || lpad(i_type,4,'0') || lpad(i_years,4,'0') || lpad(i_org,8,'0') || '0001' || '001';
          dbms_output.put_line('no = ' || o_no);
          COMMIT;

        END IF;

      END IF;

END find_no;