判断语句：if 条件 then

   if  条件  then ************;

   elsif  条件  then  ************;

   elsif 条件  then ************;

   end if;

  end if;

主要注意elsif 写法，少一个e

时间查询：to_char(t.uptime,'yyyy-mm-dd')='2015-10-27'，用to_char函数转成字符型

 

和分页查询有关的存储过程：

create or replace procedure PROC_GET_CREATETASK

(

  var_regionCode in char := '',

  var_rwlx in nvarchar2 := '',

  var_uptime in nvarchar2 := '',

  var_orderfield in nvarchar2 := '',      

  var_ordertype in nvarchar2 := '',        

  var_pageindex in out number,          

  var_pagesize in out number,            

  var_totalRecords out number,          

  var_ret out SYS_REFCURSOR

)

is

  v_sql VARCHAR2(4000) := '';             

  v_sqlCount VARCHAR2(4000) := '';        

  v_where VARCHAR2(4000) := 'WHERE 1=1 and sftask=''鍚?'';

  v_totalPages number := 0;

  v_startRecord Number(10);               

  v_endRecord Number(10);                 

begin

  if var_regionCode is not null then

   if  var_regionCode='000000'  then v_where := v_where;

   elsif  substr(var_regionCode,3,4)='0000' then

    v_where := v_where || ' AND substr(t.xzqdm,0,2)=''' || substr(var_regionCode,1,2) || '''';

   elsif substr(var_regionCode,5,2)='00' then

    v_where := v_where || ' AND substr(t.xzqdm,0,4)=''' || substr(var_regionCode,1,4) || '''';

   end if;

  end if;

  if var_rwlx is not null then

    v_where := v_where || ' AND t.RWLX = '''|| var_rwlx || '''';

  end if;

         

  if var_uptime is not null then

    v_where := v_where || ' AND to_char(t.uptime,''yyyy-mm-dd'') = ''' || var_uptime || '''';

  end if;

  v_sql := v_sql || 'select rownum r,t.*  from VIEW_TASKMANAGE t ' || v_where;

  v_sqlCount := v_sqlCount || 'select count(*) from (' || v_sql || ')';

  execute immediate v_sqlCount into var_totalRecords;

        

  if var_orderfield <> '' then

    v_sql := v_sql || 'order by var_orderfield ';

  end if;

  if var_ordertype <> '' then

    v_sql := v_sql || var_ordertype;

  end if;

                  

  if var_totalRecords > 0 then

  begin

    v_totalPages := ceil(var_totalRecords / var_pagesize);

    if var_pageIndex < 1 then

      var_pageIndex := 1;

    end if;

    

    if var_pageIndex > v_totalPages then

      var_pageIndex := v_totalPages;

    end if;

    v_startRecord := (var_pageIndex - 1) * var_pagesize + 1;

    v_endRecord := var_pageIndex * var_pagesize;

             

    v_sql := 'SELECT * FROM (' || v_sql || ') a where a.r between ' || v_startRecord || ' and ' || v_endRecord;

  end;

         

  end if;

         

  open var_ret for v_sql;

end PROC_GET_CREATETASK;