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;