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;