[游戏开发] 获取广告分页列表

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 游戏开发 -> 获取广告分页列表 -> 正文阅读

[游戏开发]获取广告分页列表

一般sql语句比较复杂的话直接写存储过程比较实用

-- PROCEDURE: public.pr_adverinfo_get_list_by_page(integer, integer, character varying, character varying, refcursor, refcursor)

-- DROP PROCEDURE public.pr_adverinfo_get_list_by_page(integer, integer, character varying, character varying, refcursor, refcursor);

CREATE OR REPLACE PROCEDURE public.pr_adverinfo_get_list_by_page(
	startindex integer,
	pagesize integer,
	strwhere character varying,
	strorder character varying,
	INOUT adverinfocursor refcursor,
	INOUT adverinfocountcursor refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
declare exec_sql character varying(3000);
declare exec_str_order character varying(3000);
declare exec_recordcount_sql character varying(3000);
begin
    exec_str_order := ' t_advert_info.advert_id desc ';
    if length(strorder)>0 then
        exec_str_order := strorder;
    end if;
    exec_sql := '     select  t_advert_info.advert_id,t_advert_info.advert_title,t_advert_info.img_url,t_advert_info.advert_content,t_advert_info.advert_type,t_advert_info.key_id,t_advert_info.publish_time,t_advert_info.module_id,t_advert_info.is_show,t_advert_info.link_url,t_advert_info.order_weight
    from t_advert_info'; 
    if length(strwhere)>0 then 
        exec_sql := format('%s where %s',exec_sql,strwhere);
    end if;
    exec_sql := format('%s order by %s limit %s offset %s ',exec_sql,exec_str_order,pagesize,startindex);
    open adverinfocursor for execute exec_sql;
    exec_recordcount_sql := ' select count(t_advert_info.advert_id) as record_count from t_advert_info ';
    if length(strwhere)>0 then 
        exec_recordcount_sql := format(' %s where %s ',exec_recordcount_sql,strwhere);
    end if;
    open adverinfocountcursor for execute exec_recordcount_sql;
end;
$BODY$;

COMMENT ON PROCEDURE public.pr_adverinfo_get_list_by_page(integer, integer, character varying, character varying, refcursor, refcursor)
    IS '备注说明【获取广告分页列表】';