oracle 存储过程备忘

创建sequence的方法：
CREATE SEQUENCE SEQ_B_USER_ID INCREMENT BY 1 START WITH 1; 
复制一个表的结构：
create table test as select * from bookmarkcategory where 1=2; 
随机数：
 select dbms_random.value(1,20) from dual; 
无返回值的存储过程
用来插入大量测试数据的存储过程 ：
CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
BEGIN
    INSERT INTO T_TEST (I_ID,I_NAME) VALUES (PARA1, PARA2);
END TESTA;
CREATE OR REPLACE PROCEDURE INSERTAMOUNTTEST
(
ST_NUM        IN     NUMBER,
ED_NUM        IN     NUMBER
)
IS
BEGIN
declare
       i   number;
begin
FOR i IN ST_NUM..ED_NUM LOOP
INSERT INTO tb values(i,i,'3','3','3',100,'0');
END LOOP;
end;
END;
运行：
sql>execute INSERTAMOUNTTEST(1,45000)   -- 一次插入45000条测试数据
PROCEDURE p_insert_t 
 (v_x in NUMBER) 
 IS
 temp_id number; 
 BEGIN
 FOR i IN 1..v_x LOOP 
 select SEQ_B_USER_ID.nextval into temp_id from dual; 
 insert into buser (id, username, nickname, status, createdtime) values(temp_id, to_char(temp_id), to_char(temp_id), 0, sysdate); 
 END LOOP; 
 END; 
从存储过程中返回值：
create or replace procedure spaddflowdate
(
varAppTypeId               in varchar2,
varFlowId                  in varchar2,
DateLength                 in number,
ReturnValue                out number    --返回值
)
is
begin
insert into td values(varAppTypeId,varFlowId,Date