简单的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;