CREATE OR REPLACE PROCEDURE proc_split_exp(
out_exp_result OUT Sys_Refcursor,
out_exp_count OUT NUMBER,
in_exp_user_id NUMBER,
in_index_page NUMBER,
in_size_page NUMBER,
in_exp_status NUMBER,
in_exp_start_date DATE,
in_exp_end_date DATE
)
AS
v_sql VARCHAR2(500);
v_where_sql VARCHAR2(500);
v_page_sql VARCHAR2(500);
v_role NUMBER;
v_pre NUMBER;
v_size NUMBER;
BEGIN
SELECT u.users_role_id INTO v_role FROM USERS u WHERE u.users_id = in_exp_user_id;
v_sql :='select * from view_exp where 1 = 1 ';
IF v_role=2 THEN
v_where_sql := ' and exp_status = 2';
ELSIF v_role = 3 THEN
v_where_sql := ' and exp_status = 3';
ELSIF v_role = 4 THEN
v_where_sql := ' and exp_status = 4';
ELSE
v_where_sql := ' and exp_userid = '|| in_exp_user_id ;
END IF;
IF in_exp_status > 0 THEN
v_where_sql :=''||v_where_sql||' and exp_status ='||in_exp_status;
END IF;
IF (in_exp_start_date IS NOT NULL AND in_exp_end_date IS NOT NULL) THEN
v_where_sql :=''||v_where_sql||' and to_char(exp_date,"yyyymmdd") between to_char('||in_exp_start_date||',"yyyymmdd")and to_char('||in_exp_end_date||',"yyyymmdd")';
END IF;
EXECUTE IMMEDIATE 'select count(*) from view_exp where 1 = 1'||v_where_sql INTO out_exp_count;
v_pre :=(in_index_page - 1)*in_size_page;
v_size := in_index_page*in_size_page;
v_page_sql := '
select * from ( select v.* ,rownum as r from (
'||v_sql||v_where_sql||')
v where rownum <='||v_size||')
where r>'|| v_pre;
OPEN out_exp_result FOR v_page_sql;
END proc_split_exp;
测试的时候 是说这一行少了右括号 但是 我用语句测试是没有问题的
EXECUTE IMMEDIATE 'select count(*) from view_exp where 1 = 1'||v_where_sql INTO out_exp_count;
------解决方案--------------------
在EXECUTE前，你可以将要执行的语句先打印出来看语句对不对