Oracle常用命令8（过程）

过程 

CREATE [OR REPLACE] PROCEDURE

   <procedure name> [(<parameter list>)]

IS|AS

   <local variable declaration>

BEGIN

   <executable statements>

[EXCEPTION

   <exception handlers>]

END;

 

调用过程

1、Execute 过程名(参数列表);

2、

begin

       过程名(参数列表);

End;

 

例1：用户注册（用户名不能重复）

Create or replace procedure proc_UserAdd(p_name varchar2,p_realyname varchar2, p_password varchar2,p_tel varchar2, p_email varchar2,p_address varchar2, isSuccess out number)

       is    

              count_add number(4);

       begin

              //判断用户是否存在

              select count(*) into count_add from userTab where u_name=p_name;

              if count_add<1 then

                     insert into userTab values(seq_user.nextval,p_name,p_realyname,p_password,p_tel, p_email,p_address);

                     isSuccess := 1;

                     dbms_output.put_line('注册成功');

              else

                     isSuccess := 0;

                     dbms_output.put_line('用户名已存在，请重新注册！');

              end if;

       exception

              when others then

                     dbms_output.put_line('错误!');

       end;

 

测试用户注册：

declare

       isSuccess number(4);

begin

       proc_UserAdd('zhongzi2','周姿','zhongzi','2526294','zhong@163.com','湖南省湘潭', isSuccess) ;

       dbms_output.put_line( isSuccess );

end;