ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Sequence in Merge statement

Sequence in Merge statement

原创 Linux操作系统 作者:dragondb 时间:2019-04-04 10:33:05 0 删除 编辑

SQL> CREATE TABLE test(c1 int,c2 varchar2(35));

Table created

SQL>
SQL> CREATE SEQUENCE test_s
  2           INCREMENT BY 1
  3           START WITH 1
  4           MAXVALUE 10000
  5           NOCYCLE
  6           CACHE 20;

Sequence created

SQL>
SQL> INSERT INTO test VALUES(test_s.nextval,'AAA');

1 row inserted

SQL> INSERT INTO test VALUES(test_s.nextval,'BBB');

1 row inserted

SQL> INSERT INTO test VALUES(test_s.nextval,'CCC');

1 row inserted

SQL> select * from test;

                                     C1 C2
--------------------------------------- -----------------------------------
                                      1 AAA
                                      2 BBB
                                      3 CCC

SQL> SET SERVEROUT ON;
SQL>
SQL> DECLARE
  2  BEGIN
  3    FOR i in 1..5 LOOP
  4      MERGE INTO   test
  5            USING (SELECT i c1,'xxx' c2 FROM DUAL) test2
  6               ON (test.c1 = test2.c1)
  7            WHEN MATCHED THEN       UPDATE SET test.c2=test.c2||test2.c2
  8             WHEN NOT MATCHED THEN  INSERT (test.c1,test.c2)
  9                                    VALUES (test_s.nextval,test2.c2);
 10      DBMS_OUTPUT.PUT_LINE(test_s.currval);
 11    END LOOP;
 12  END;
 13  /

4
5
6
7
8

PL/SQL procedure successfully completed

SQL> select * from test;

                                     C1 C2
--------------------------------------- -----------------------------------
                                      1 AAAxxx
                                      2 BBBxxx
                                      3 CCCxxx
                                      7 xxx
                                      8 xxx

SQL>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/45259/viewspace-630878/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2003-05-05

  • 博文量
    65
  • 访问量
    48799