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

以下皆为自己的学习感受，存档加分享。存档在先，分享在后，若有不妥之处，并无误人子弟之意，请各位AC之间的大牛不吝指教，小生在此鞠躬谢过。

一，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

下面奉上

/*  练习一：函数：作一函数，完成下面的功能：输入姓名，课程名，成绩该过程完成对SC表的插入或修改操作，若插入成功，返回成功信息，若该选课信息已经存在，则修改其成绩为输入的成绩，若遇系统错误，返回错误信息*//*过程：建立过程，当传入学号和选课门数，首先判断SC_Number表是否存在，若不存在则创建该表格（包括学号和选修门数两列），  将传入值插入或修改到SC_Number表中（该生不存在则插入，若存在则修改其选课门数）*/

create or replace procedure inOrUp_Sc_Number(ip_sno in number,ip_courseCount in number)    istempt_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;   --endOfIfend;

create or replace function func_InsetOrUpdateSc(ip_sname in varchar2,ip_cname in varchar2,ip_grade in varchar2)   --这个是函数的返回类型                return varchar2is    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;

转自：http://www.cnblogs.com/sc-xx/archive/2011/12/22/2298381.html