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,DateLength)
returning 1 into ReturnValue;   --返回值
commit;
exception
when others then
rollback;
end;

CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
BEGIN
SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
END TESTB;

用包实现存储过程返回游标：
create  or  replace  package  test_p
as 
type  outList  is  ref  cursor;
PROCEDURE  getinfor(taxpayerList  out  outList); 
end  test_p;
/

create  or  replace  package  body  test_p  as  PROCEDURE  getinfor(taxpayerList out  outList) 
    is  begin
              OPEN  taxpayerList    FOR  select  *  from td where tag='0';
    end  getinfor;
end  test_p;
/

运行：
set  serverout  on;    --将输出工具打开
variable  x  refcursor;
execute test_p.getinfor(:x);
exec  test_p.getinfor(:x);
print  x;
drop package test_p;

 

==========================

在SQL*PLUS中建一个程序包

CREATE OR REPLACE PACKAGE TESTPACKAGE AS

TYPE Test_CURSOR IS REF CURSOR;

procedure TESTC(cur_ref out Test_CURSOR);

end TESTPACKAGE;

建立存储过程，存储过程为：

create or replace package body TESTPACKAGE as

procedure TESTC(cur_ref out Test_CURSOR) is

begin

OPEN cur_ref FOR SELECT * FROM T_TEST;

end TESTC;

END TESTPACKAGE;

 

 

参考网址：
http://blog.youkuaiyun.com/awolf168/archive/2006/06/22/820902.aspx
http://www.mbsky.com/infoview/Article_8218.html
http://edu.codepub.com/2009/0819/13913.php