Oracle存储过程判断语句正确写法和时间查询⽅法判断语句：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) := '';select语句查询日期
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 qdm,0,2)=''' || substr(var_regionCode,1,2) || '''';
elsif substr(var_regionCode,5,2)='00' then
v_where := v_where || ' AND qdm,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;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。