--------------------------
oracle
--------------------------
---建表----
create table HIE.DOCUMENT  (
   UNIQUEID             VARCHAR2(256)                   not null,
   HASH                 VARCHAR2(256)                   not null,
   SIZE_                NUMBER(10)                      not null,
   MIMETYPE             VARCHAR2(256)                   not null,
   REPOSITORYID         VARCHAR2(256)                   not null,
   BYTES                BLOB                            not null,
   DOCUMENTID           VARCHAR2(256)                   not null,
   CREATETIME           DATE
)
tablespace EHRDOCTBS;
------建立主键-----
alter table HIE.DOCUMENT add (constraint DOC_PKEY primary key(UNIQUEID) using index tablespace EHRINDTBS);
-----插入数据过程------
CREATE OR REPLACE PROCEDURE HIE.createDocument (startNum      NUMBER,
                                            totalCount    NUMBER,
                                            numPerTime    NUMBER)
IS
   i       NUMBER;
   guid    VARCHAR2 (64);
   guid_   VARCHAR2 (64);
BEGIN
   i := startNum;

   WHILE i < totalCount
   LOOP
      guid_ := SYS_GUID ();
      guid :=
            'urn:uuid:'
         || SUBSTR (guid_, 1, 8)
         || '-'
         || SUBSTR (guid_, 9, 4)
         || '-'
         || SUBSTR (guid_, 13, 4)
         || '-'
         || SUBSTR (guid_, 17, 4)
         || '-'
         || SUBSTR (guid_, 21, 12);

      INSERT INTO HIE.document (UNIQUEID,
                                HASH,
                                SIZE_,
                                MIMETYPE,
                                REPOSITORYID,
                                BYTES,
                                DOCUMENTID,
                                CREATETIME)
         SELECT   '1.3.6.1.4.1.21367.' || HIE.documentSEQ.NEXTVAL,
                  HASH,
                  SIZE_,
                  MIMETYPE,
                  REPOSITORYID,
                  BYTES,
                  guid,
                  SYSDATE
           FROM   HIE.DOCUMENT
          WHERE   ROWNUM <= 100;


      IF MOD (i, numPerTime) = 0
      THEN
         COMMIT;
      END IF;

      i := i + 1;
   END LOOP;

   COMMIT;
END createDocument;
/

建立序列
create sequence HIE.documentSEQ
increment by 1
start with 1
nomaxvalue
nocycle
cache 10;


注意一点点：
只要定义了HIE.documentSEQ，你才可以用CURRVAL，NEXTVAL

CURRVAL=返回 sequenCe的当前value

NEXTVAL=增加sequenCe的value，然后返回 sequenCe value

例如：

HIE.documentSEQ.CURRVAL 
HIE.documentSEQ.NEXTVAL
 
可以使用sequenCe的地方：

· 不包含子查询、snapshot、VIEW的 SELECT 语句

·INSERT语句的子查询中

·NSERT语句的VALUES中

·UPDATE 的 SET中

需要注意的是：

第一次NEXTVAL返回的是初始value；随后的NEXTVAL会自动增加你定义的INCREMENT BYvalue，然后返回增加后的value。CURRVAL 总是返回当前SEQUENCE的value，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次 SEQUENCE的value，所以如果你在同一个语句里面使用多个NEXTVAL，其value就会是不一样的。

- 假如指定CACHEvalue，OraCle就可以预先在内存里面放置一些sequenCe，这样存取的快些。CaChe里面的取完后，OraCle自动 再取一组到CaChe。 使用CaChe或许会跳号， 比如Datcbase突然不正常down掉（shutdown abort),CaChe中的sequenCe就会丢失. 所以可以在Create sequenCe的时候用noCaChe防止这种情况。

--------------------------
mysql
--------------------------
比较关键的一点是，mysql没有oracle中的.NEXTVAL函数，使用自建函数取代

---function---

DROP FUNCTION if exists `hie`.`NEXTVA`;

DELIMITER |

CREATE FUNCTION `NEXTVA`(seq int) RETURNS int(11)
begin
declare iseq INT;

set @iseq = @iseq + 1;
return @iseq;
end |
------------------------------------
每次使用前，要先将@iseq置0，或者您想要的数值
set @iseq=0
select nextva(1)

---建表----
create table HIE.DOCUMENT  (
   UNIQUEID             VARCHAR(255)                   not null,
   HASH                 VARCHAR(256)                   not null,
   SIZE_                int                            not null,
   MIMETYPE             VARCHAR(256)                   not null,
   REPOSITORYID         VARCHAR(256)                   not null,
   BYTES                BLOB                           not null,
   DOCUMENTID           VARCHAR(256)                   not null,
   CREATETIME           DATE
);



------建立主键-----

alter table HIE.DOCUMENT add primary key(uniqueid);
create unique index  docnormal on document(uniqueid);


-----插入数据过程------
DROP Procedure if exists `hie`.`createDocument` ;

DELIMITER |

CREATE PROCEDURE `createDocument`(startNum INT,
 totalCount INT,
 numPerTime INT)
BEGIN
  DECLARE i INT;
  DECLARE guid  VARCHAR (64);
  DECLARE guid_ VARCHAR (64);


   SET @i = startNum;

   WHILE (@i < totalCount)
   DO

      SET guid_ = uuid();
      SET guid =concat(
            'urn:uuid:'
         , SUBSTR(guid_, 1, 8)
         , '-'
         , SUBSTR(guid_, 9, 4)
         , '-'
         , SUBSTR(guid_, 13, 4)
         , '-'
         , SUBSTR(guid_, 17, 4)
         , '-'
         , SUBSTR(guid_, 21, 12));
         
         

      INSERT INTO HIE.document (UNIQUEID,
                                HASH,
                                SIZE_,
                                MIMETYPE,
                                REPOSITORYID,
                                BYTES,
                                DOCUMENTID,
                                CREATETIME)
         SELECT   concat('1.3.6.1.4.1.21367.' , nextva(1)),
                  HASH,
                  SIZE_,
                  MIMETYPE,
                  REPOSITORYID,
                  BYTES,
                  guid,
                  curdate()
           FROM   document
             limit  100;

      IF MOD (@i, numPerTime) = 0
      THEN
         COMMIT;
      END IF;

      set @i = @i + 1;
   END WHILE;
   commit;
END |

call createDocument(1,10,10)