-- 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;
oracle 存储过程基础
最新推荐文章于 2022-12-23 14:54:01 发布