数据库之建立存储过程/存储函数的过程

Create or replace function Almostfull(
   p_cno classes.cno%type)
Return boolean is 
   V_currentstudents number;
   v_maxstudents number;
   v_returnvalue boolean;
   v_fullpercent constant  number:=90;
  Begin
    select current_students,max_students
     into  v_currentstudents,v_maxstudents
     from classes
     where cno=p_cno;
   If (v_currentstudents/v_maxstudents*100)>v_fullpercent then
      v_returnvalue:=true;
   else
     v_returnvalue:=false;
   end if;
   return v_returnvalue;
 End Almostfull;

#此处是创建一个函数
 Declare
    cursor c_classes is
      select cno from classes;
 Begin
    for v_classesrecord in c_classes loop
      if Almostfull(v_classesrecord.cno) then
        dbms_output.PUT_LINE(v_classesrecord.cno  || ' is almost full');
      end if;
    end loop;
 End;

#此处是写一个匿名块，调用刚才函数

create or replace procedure ListAlmostFullClass
as
    cursor c_classes is
      select cno from classes;
 Begin
    for v_classesrecord in c_classes loop
      if Almostfull(v_classesrecord.cno) then
        insert into temp_table  (char_col) values
         (v_classesrecord.cno  || 'is almost full');
      end if;
    end loop;
    commit;
 End ListAlmostFullClass
#此处是create or replace procedure存储过程使用