oracle存储过程和存储函数语法和举例

创建过程的语法：  

create [or replace] procedure procedure_name
  [(argument[{in|out|in out}] type,
    
   argument[{in|out|in out}] type)] {is|as}
  procedure_body


简单的例子：

 

create procedure RaiseError(
   /*if p_Raise is true,then an unhandled error is raised.
   if p_Raise is false,the procedure completes successfully.*/
      p_Raise in boolean:=true,
      p_ParameterA out number) as
      
  begin
      p_Parameter1:='abcdwxc';
      p_Parameter2:=143;
  end ParameterLength;


删除过程语法：

 

drop procedure procedure_name;


2、创建函数的语法：

 

create [or replace] function function_name
   [(argument[{in|out|in out}] type,
     
     argument[{in|out|in out}] type)]
  return return_type {is|as}
  function_body


简单函数的例子：

 

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;

在sqlplus中执行存储函数的方法：
方法1:
SQL>variable a number
SQL>begin
:a:=test_function1(1);
end;
/
SQL>print a
         A
----------
         6

方法2：
sql>variable a number
sql>call test_function1(1) into :a;   
Call completed.
sql>print a
       A
----------
        6

方法3：
sql>variable a number
sql>execute :a:=test_function1(1)
sql>print a
       A
----------
        6