oracle 存储过程和函数学习笔记

1、创建过程的语法：

Code

简单的例子：

Code

删除过程语法：

drop procedure procedure_name;

2、创建函数的语法：

Code

简单函数的例子：

create function ClassInfo(
      p_Department classes.department%type,
      p_Course     classes.course%type)
      return varchar is
      v_CurrentStudents number;
      v_MaxStudents     number;
      v_PercentFull     number;
      begin
          select current_students,max_students
          into v_CurrentStudents,v_MaxStudents
          from classes
          where department=P_Department and course=P_Course;

          --计算百分比
          v_PercentFull:=v_CurrentStudents/V_MaxStudent*100;

          if v_PercentFull=100 then
          return 'Full';
          elseif v_PercentFull>80 then
          return 'Some Room';
          elseif v_PercentFull>60 then
          return 'More Room';
          elseif v_PercentFull>0 then
          return 'Lots of Room';
          else
          return 'Empty';
          end if

      end ClassInfo;

删除函数语法：

drop function function_name;