存储过程基础 oracle,oracle 存储过程基础

-- 1、创建简单存储过程，将学生年龄都改为10

create or replace procedure update_students

as

begin update students set student_age = 10;

commit;

end update_students;

-- 2、查看存储过程在数据字典中的信息

select object_name,object_type,status from user_objects where object_name='UPDATE_STUDENTS'

-- 3、查看创建存储过程sql

select * from user_source where name='UPDATE_STUDENTS';

-- 4、调用

call update_students();

/** 存储过程的参数分为 in传入参数，out 传出参数， in out 传入传出参数**/

-- A:创建 in参数类型 的存储过程

--eg1 要求：创建按传入的值修改学生年龄的存储过程

create or replace procedure update_students_in(in_age in number)

as

begin

update students set student_age = in_age;

commit;

end update_students_in;

-- 执行，将学生年龄改为12

call update_students_in(12);

-- 查询STUDENTS，看是否修改成功

select * from STUDENTS t;

-- (说明in参数不可修改)举例当尝试修改in参数的值时会报错！

create or replace procedure update_students_in(in_age in number)

as

begin

update students set student_age = in_age;

in_age :=in_age+10;

commit;

end update_students_in;

-- B：创建 out参数类型 的存储过程

--要求：返回学生id=1的修改后的年龄

create or replace procedure update_students_out(in_age in number,out_age out number) as

begin

update students set student_age = in_age;

select student_age into out_age from students where student_id = 1;

commit;

end update_students_out;

-- 测试

declare

out_updatedAge number;

begin

update_students_out(20,out_updatedAge);

dbms_output.put_line(out_updatedAge);

end;

-- C:in out 参数：交换两个变量的值

create or replace procedure swap(in_out_param1 in out number, in_out_param2 in out number)

as

begin

declare v_param number;

begin

v_param := in_out_param1;

in_out_param1:=in_out_param2;

in_out_param2 := v_param;

end;

end;

-- 测试

declare

param1 number:=25;

param2 number:=55;

begin

swap(param1,param2);

dbms_output.put_line('param1=' || param1);

dbms_output.put_line('param2=' || param2);

end;