存储过程 oracle 示例,简单的oracle存储过程示例-Oracle

简单的oracle存储过程示例

— 创建无参procedure:

drop procedure proc_test

create or replace procedure proc_test

is

testvalue varchar2(40);

begin

select user_name into testvalue from user_info where user_id = 1001;

dbms_output.put_line(testvalue);

end proc_test;

/

begin

proc_test;

end;

— 创建输入参procedure:

drop procedure insert_student

create or replace procedure insert_student

(

user_id Number,user_name varchar2,user_pass varchar2

)

as

begin

insert into student values(user_id,user_name,user_pass);

end insert_student;

begin

insert_student(1,’aaa’,’bbb’);

commit;

end;

drop procedure proc_test1

create or replace procedure proc_test1

(

invalue in Integer

)

as

testvalue varchar2(40);

begin

select user_name into testvalue from user_info where user_id = invalue;

dbms_output.put_line(testvalue);

end proc_test1;

/

declare

parameter Integer := 1001;

begin

proc_test1(parameter);

end;

— 创建输出参procedure:

drop procedure proc_test2

create or replace procedure proc_test2

(

invalue out varchar2

)

as

begin

select user_name into invalue from user_info where user_id = 1001;

end proc_test2;

/

declare

parameter varchar2(20);

begin

proc_test2(parameter);

dbms_output.put_line(parameter);

end;

–既有输入，又有输出

create or replace procedure proc_test3

(

invalue in Integer,outvalue out varchar2

)

as

begin

select user_name into outvalue from user_info where user_id = invalue;

end proc_test3;

/

declare

parameter Integer := 1001;

parameter1 varchar2(20);

begin

proc_test3(parameter,parameter1);

dbms_output.put_line(parameter1);

end;