oracle 存储过程小记

内容都是从网上找的，具体引用了哪里的，不知道了，谢谢www.

 

 

1 oracle如何执行存储过程，

  在外面直接执行存储过程，java直接执行存储过程

 

  ：可以直接用Java执行的

 

 

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

2 简单的存储过程例子

执行下面的建立表的语句，将存储过程的语句copy到plus/sql,

执行时，注意要用/结束，执行。

--建立测试表

create table procedure_tab 

(

usersNAME varchar2(8) not null,

EMAIL varchar2(30),

FLAG number

);  

--插入语句

insert into test(usersNAME,EMAIL,FLAG) values('kiccleaf','kiccleaf@openzj.com',2);

 

--建立简单的存储过程

CREATE OR REPLACE  PROCEDURE pro_simple (

Xuan_usersname in varchar2 

)

as

begin

update procedure_tab  set usersNAME =Xuan_usersname  where FLAG=2;

commit;

end pro_simple;

/

 

--测试:

execute pro_simple('leaf');

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

3 例子2   有游标的

--create table

create table users

(id number(3) primary key,

 usersname varchar2(30)

 );

insert into users values(1,'a');

insert into users values(2,'b');

insert into users values(3,'c');

insert into users values(4,'d');

insert into users values(5,'e');

insert into users values(6,'f');

insert into users values(7,'g');

insert into users values(8,'h');

insert into users values(9,'i');

commit;

 

---create procedure

drop procedure if exists pro_users;

create or replace procedure pro_users(

  p_id in number,  

  out_info out varchar2

)

is 

v_row  users%rowtype;    ---和users是一个类型的

cursor v_cur is select * from users;

begin

if p_id > 0  then

select * into v_row from users where id=p_id;

out_info := v_row.id || '=' || v_row.usersname;

else

open v_cur;

loop

fetch v_cur into v_row;

exit when v_cur%notfound;

if out_info is null then 

out_info := v_row.id||'='||v_row.usersname;

else

out_info :=out_info||','||v_row.id||'='||v_row.usersname;

end if;

end loop;

close v_cur;

end if;

exception

when too_many_rows then  

DBMS_OUTPUT.PUT_LINE('返回值多于1行');  

when others then  

DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！'); 

end;  

 /

 

 

---test

set serveroutput on;  --打开输出开关，这样才能dbms_output.put_line管用

declare 

v_id number := -1;

v_info varchar2(400);

begin

pro_users(v_id,v_info);

DBMS_OUTPUT.PUT_LINE(v_info);  

end;

 

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

3 例子2

游标分为 

游标：用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。 

1．静态游标：包括显式游标和隐式游标。 

2．REF游标：是一种引用类型，类似于指针。 

 

--包代码

create or replace package pack_test is  

       type cur_test is ref cursor;  

end pack_test; 

 

--存储过程

create or replace procedure proc_cur(  

       p_id in number,  

       p_cur out pack_test.cur_test)   

is   

       v_sql varchar2(400);   

begin  

       if p_id = 0 then   

          open p_cur for select * from users;   

       else   

          v_sql := 'select * from users where id =: p_id';   

          open p_cur for v_sql using p_id;   

       end if;   

end proc_cur; 

 

--脚本测试代码 

-- Test statements here  

declare   

  v_id number := 1;  

  v_row users%rowtype;  

  p_cur pack_test.cur_test;  

begin   

  proc_cur(v_id, p_cur);  

  loop  

    fetch p_cur into v_row;  

    exit when p_cur%notfound;  

    DBMS_OUTPUT.PUT_LINE(v_row.id||'='||v_row.usersname);  

  end loop;  

  close p_cur;  

end;  

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

4 例子4

 

CREATE SEQUENCE SEQ_B_USER_ID INCREMENT BY 1 START WITH 1; 

 

create table buser(

id  number(3),

username varchar2(30),

nickname varchar2(40),

status  number(2),

createdtime date

);

 

create or replace  

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;   

/

 

execute p_insert_t (20);

 

 

*********其它的一些代码*************

 随机数：

sql 代码

select dbms_random.value(1,20) from dual;  

 游标的使用例子：

sql 代码

SET SERVERIUTPUT ON     

DECLARE     

CURSOR c_emp IS select title from bookmarkcategory where username='130';   

r_emp c_emp%ROWTYPE;     

BEGIN     

 OPEN c_emp;     

 -- LOOP     

 FETCH c_emp INTO r_emp;     

    --EXIT WHEN c_emp%NOTFOUND;     

    --DBMS_OUT.PUT.PUT_LINE('Salary of Employee is'|| r_emp.title);     

  --END LOOP;     

 update bookmarkcategory set title = r_emp.title where id=121;   

 CLOSE c_emp;     

END;    

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

存储过程返回指定行数

 

---该sql可以返回指定行数的记录 

SELECT results.* FROM　 

( 

SELECT t2.*, rownum rownumber FROM　 

   ( SELECT t.* FROM buser t ) t2

) results　 

WHERE results.rownumber BETWEEN 3 and 5 ;

 

--proc

create or replace package pack_getCondRows is  

       type cur_test is ref cursor;  

end pack_getCondRows; 

 

--返回指定的行间的记录，好像表不能动态的搞呢

create or replace procedure proc_cur2(  

        begno in number,  

        endno in number,

       -- tablename in varchar2,

        p_cur out pack_getCondRows.cur_test)   

is   

       v_sql varchar2(400);   

begin  

 

          v_sql := 'SELECT results.* FROM　 

( 

SELECT t2.*, rownum rownumber FROM　 

   ( SELECT t.* FROM buser t ) t2

) results　 

WHERE results.rownumber BETWEEN :begno and :endno ';  

          open p_cur for v_sql using  begno,endno;   

end proc_cur2; 

 

 

 

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

尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。