Oracle 创建函数与存储过程语句积累

1.

创建一个返回字符串的函数

create or replace function get_hello_msg 
return varchar2 as
begin 
  return 'hello world';
end get_hello_msg;

查看函数的类型和状态
select object_name, object_type, status from user_objects where lower(object_name)='get_hello_msg';

user_source用于查询数据库中定义的函数和存储过程的代码

select name,type,line,text from user_source where lower(name)='get_hello_msg';

select get_hello_msg() from dual;

2. 创建一个获得税收的函数

create or replace
  function get_tax(p_salary number)
  return number as
  begin
    declare tax_salary number;
    begin
    tax_salary := p_salary - 2000;
    if tax_salary<=0 then
       return 0;
    end if;
    
    if tax_salary<=500 then
      return tax_salary*5/100;
    end if;
    
    if tax_salary<=2000 then
      return tax_salary*10/100 - 25;
    end if;
    
    if tax_salary<=5000 then
       return tax_salary*15/100 - 125;
    end if;
    
    if tax_salary<=20000 then
       return tax_salary*20/100 - 375;
    end if;
    
    if tax_salary<= 40000 then
       return tax_salary*25/100 - 1375;
    end if;
    
    if tax_salary<= 60000 then
       return tax_salary*30/100 - 3375;
    end if;
    
   end;
  end get_tax;

函数的调用：

 select get_tax(6000) tax from dual;

3：一个表中含有学生姓名字段，为了获得所有学生姓名，必须要对数据表中的数据循环处理，以获得每个学生的姓名，并将所有学生姓名

的字符串串联起来，可以创建一个函数来处理该过程。

create or replace 
   function get_student_string
   return varchar2
   as
   begin
      declare cursor cu_student is
       select student_name from students       
       order by student_id;
      student_nane varchar2(10);
      rowString varchar2(500);
      
      begin
       open cu_student;
       fetch cu_student into student_name;  //将游标所指向的当前记录的数据赋值给student_name
       
       while cu_student%found loop       //用于循环处理游标所指向的记录
         rowString:=rowString || student_name || ', ';
         fetch cu_student into student_name;
       end loop;
       
       return substr(rowString, 1, length(rowString) - 1);
     end;
   end get_student_string;
   

4：存储过程

create or replace procedure update_students
as 
  begin
     update students set student_age=10;
     commit;
  end update_students;

  select object_name, object_type, status from user_objects where lower(object_name)='update_students';
  
  select * from user_source where lower(name)='update_students';
  
  execute update_students;

5：存储过程-in参数

create or replace
  procedure update_students(in_age in number) as
  begin
    update student set student_age=in_age;
    commit;
  end update_students;
  

6：存储过程-out参数

create or replace 
  procedure update_students(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;
   

7：存储过程的参数---IN   OUT  参数

      利用IN OUT参数来交换两个变量的值

      

create or replace procedure swap(in_out_param1 in out number, in_out_param2 in out number)
 as 
   begin
      declare param number;
      begin
        param:=in_out_param1;
        in_out_param1 := in_out_param2;
        in_out_param2 := param;
      end;
   end;
   

8: 以视图user_objects中的数据为例子，将其作为源表，创建新表target

create table target (object_id number, object_name varchar2(30), object_type varchar2(30), previous_name varchar2(30),
   status varchar2(30));

值得注意的是，列previous_name是指，所有记录按照Object_id进行升序排列，处于当前记录之前的那条记录的object_name的列值。

利用存储过程来解决这个问题。

create or replace procedure insert_objects as
begin
  declare
      cursor cu_objects is
      select * from user_objects order by object_id;
      obj user_objects%rowtype;
      previous_name user_objects.object_name%type;
      begin
        open cu_objects;
        fetch cu_objects into obj;
        while cu_objects%found loop
          insert into target
          values
          (
            obj.object_id,
            obj.object_name,
            obj.object_type,
            previous_name,
            obj.status
          );
          previous_name := obj.object_name;
          fetch cu_objects into obj;
        end loop;
      end;
end insert_objects;