关于Oracle过程，函数的经典例子及解析

一，Oracle中的过程，函数

对于oracle中的过程和函数，个人觉得可以化为一类，因为它们在写法上并没有什么的不同。公式无非就是

create or replace Package_name(paramater1 in type,paramater2 in type,out_message out type)

　　is

　　locate_paramater1 type;

　　locate_paramater2 type;

　　begin

　　　　begin

　　　　　　　　select sth into locate_paramater1  from table_name where 。。。。

　　　　exception

　　　　　　　　when no_data_found then

　　　　　　　　 locate_paramater1:=' ';

　　　　　　　　out_message:='Some Information to user to look';  　　

　　　　end

　　　　begin

　　　　　　　　select sth into locate_paramater2  from table_name where 。。。。

　　　　exception

　　　　　　　　when no_data_found then

　　　　　　　　 locate_paramater2:=' ';

　　　　　　　　out_message:='Some Information to user to look';  　　

　　　　end

 

　　end

总而言之，言而总之：包声明，私有数据神马的在紧跟is之后。接着就是一串的begin......exception....end;

 

　　函数

create or replace function_name(paramater1 in type,paramater2 in type)

　　return return_type;

　　is

　　locate_paramater1 type;

　　locate_paramater2 type;

　　out_message return_type;

　　begin

　　　　begin

　　　　　　　　select sth into locate_paramater1  from table_name where 。。。。

　　　　exception

　　　　　　　　when no_data_found then

　　　　　　　　　　 locate_paramater1:=' ';

　　　　　　　　　　out_message:='Some Information to user to look'; 

　　　　　　　　　　return 　out_message;

　　　　　　　　when others then

　　　　　　　　　　out_message:='Error'; 

　　　　　　　　　　return 　out_message;

　　　　end

　　　　begin

　　　　　　　　select sth into locate_paramater2  from table_name where 。。。。

　　　　exception

　　　　　　　　when no_data_found then

　　　　　　　　 locate_paramater2:=' ';

　　　　　　　　out_message:='Some Information to user to look'; 

　　　　　　　　return 　out_message;　

　　　　end

 

　　end

实战演练

create or replace   procedure inOrUp_Sc_Number(ip_sno in number,ip_courseCount in number)    
                 is
                 tempt_count number(4);
                 e_sc_number number(4);
                 begin
                   
                   begin
                     --tab 是一个系统表，存储当前用户下所拥有的表信息
                     select 1 into e_sc_number from tab where tname='SC_Number';
                   exception 
                     when no_data_found then
                       --注意对私有数据的赋值方式!!!!
                       e_sc_number:=0;
                     when others then
                       dbms_output.put_line(sqlerrm);
                       return;
                    end;
                    --endOfBegin
                   if e_sc_number=0 then
                     --这个是动态sql,因为在执行这句话之前不知道是否要执行，所以要用动态的
                     --动态sql的语法：execute immediate 'sql statement'
                     execute immediate
                             'create table SC_Number(sno  number(5) primary key,cnum number(3) )';
                     execute immediate
                             'insert into SC_Number select sno,count(*) from sc group by sno';
                             commit;
                   end if;
                   
                   begin
                     --这个也是动态sql
                     --需要注意的是给动态sql，动态的赋值，=：number  useing paramater
                     execute immediate
                             'select 1 from SC_Number where sno=:1' into tempt_count using trim(ip_sno);
                   exception
                      when no_data_found then
                              tempt_count:=0;
                      when others then
                           dbms_output.put_line(sqlerrm);
                           return;
                    end;
                    --endOfBegin
                   if  tempt_count=1 then
                     begin
                     execute immediate
                             --这个还是动态sql
                             --需要注意的是给动态sql，动态的赋值，=：number  useing paramater
                             'update SC_Number set cnum=:1 where sno=:2'using ip_courseCount,trim(ip_sno);
                             commit;
                     exception 
                       when others then
                          dbms_output.put_line(sqlerrm);
                          return;
                     end;
                   end if; 
                     --endOfIf     
                   if  tempt_count=0 then
                     begin
                       execute immediate
                       --为神马这么多动态sql
                       'insert into SC_Number values(:1,:2)'using ip_sno,ip_courseCount;
                       commit;
                     exception 
                       when others then
                         dbms_output.put_line(sqlerrm);
                         return;
                     end;
                   end if; 
                   --endOfIf
                   end;

create or replace function func_InsetOrUpdateSc(ip_sname in varchar2,ip_cname in varchar2,ip_grade in varchar2)
   --这个是函数的返回类型               
 return varchar2
is
    student_num student.sno%type;
    course_num course.cno%type;
    out_messsage varchar2(30);--注意私有数据声明时要标注大小
    tempt_count number(4);
begin
  
  begin
   select sno into student_num from student where student.snmae=ip_sname;
  exception 
    when no_data_found then
      student_num:='';
      out_messsage:='No such a Student';
      return out_messsage;
    when others then
      out_messsage:='Error';
      return out_messsage;
  end;
  --endOfBegin
  begin
    select cno into course_num from course where course.cname=ip_cname;
  exception
    when no_data_found then
      course_num:='';--私有数据的赋值方法和过程类似
      out_messsage:='No such a Course';
      return out_messsage;
     when others then
       out_messsage:='Error';
      return out_messsage;
   end; 
   --endOfBegin
   begin
      select 1 into tempt_count from sc where sc.sno=student_num and sc.cno=course_num;
   exception
     when no_data_found then
     tempt_count:=0;
     when others then
      out_messsage:='Error';
      return out_messsage;
    end;
    --endOfBegin
    if  tempt_count=1 then
      begin
        update sc set sc.grade=ip_grade where sc.sno=student_num and sc.cno=course_num;
        --注意下面的commit语句，在update，insert时，不要忘记了哦
        commit;
        out_messsage:='Update Success';
        return out_messsage;
      exception
        when others then
          out_messsage:='Error';
          return out_messsage;
       end;
    end if; 
    --endOfIf 
    if  tempt_count=0 then
      begin
        insert into sc values(student_num,course_num,ip_grade);
        commit;
        out_messsage:='Insert Success!';
        return out_messsage;
      exception
        when others then
          out_messsage:='Error';
          return out_messsage;
       end;
    end if;  
    --endOfIf
  end;