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

创建过程的语法：     

Code
<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->create [or replace] procedure procedure_name
  [(argument[{in|out|in out}] type,
    
   argument[{in|out|in out}] type)] {is|as}
  procedure_body
  
  

简单的例子：

Code
<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->
  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;

 

删除过程语法：

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--> drop   procedure  procedure_name;

 

2、创建函数的语法：

Code
<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->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

 

简单函数的例子：

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->   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;
          

 

删除函数语法：

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--> drop   function  function_name;