用动态sql拼吧:CREATE OR REPLACE PROCEDURE prc_getcommonuser_list(p_i_qrybeginpos IN VARCHAR2, p_i_qrynum IN VARCHAR2, p_i_id IN VARCHAR2, p_i_user_level IN VARCHAR2, p_o_code OUT VARCHAR2, p_o_total_num OUT VARCHAR2 ) IS v_sql VARCHAR2(2000); v_where VARCHAR2(1000); BEGIN p_o_code := '0'; v_where := ' where 1 = 1'; IF p_i_id IS NOT NULL THEN v_where := v_where || ' and ID = ''' || p_i_id || ''''; END IF; IF p_i_user_level IS NOT NULL THEN v_where := v_where || ' and USER_LEVEL = ''' || p_i_user_level || ''''; END IF; v_sql := 'select count(1) from tab_user' || v_where; EXECUTE IMMEDIATE v_sql INTO p_o_total_num; EXCEPTION WHEN OTHERS THEN ROLLBACK; p_o_code := '1'; p_o_msg := '获取列表失败!'; END;
p_i_qrynum IN VARCHAR2,
p_i_id IN VARCHAR2,
p_i_user_level IN VARCHAR2,
p_o_code OUT VARCHAR2,
p_o_total_num OUT VARCHAR2
) IS
v_sql VARCHAR2(2000);
v_where VARCHAR2(1000);
BEGIN
p_o_code := '0';
v_where := ' where 1 = 1';
IF p_i_id IS NOT NULL THEN
v_where := v_where || ' and ID = ''' || p_i_id || '''';
END IF;
IF p_i_user_level IS NOT NULL THEN
v_where := v_where || ' and USER_LEVEL = ''' || p_i_user_level || '''';
END IF;
v_sql := 'select count(1) from tab_user' || v_where;
EXECUTE IMMEDIATE v_sql
INTO p_o_total_num;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
p_o_code := '1';
p_o_msg := '获取列表失败!';
END;