create or replace procedure find_role(
p_role_name IN VARCHAR,
p_start IN NUMBER,
p_end IN NUMBER,
r_count OUT NUMBER,
ref_cur OUT SYS_REFCURSOR) IS
BEGIN
select count(*) INTO r_count from t_role where role_name like '%' || 'p_role_name' || '%';
open ref_cur for
select role_id, role_name, note from
(select role_id, role_name, note,ROWNUM RN from t_role where role_name like '%' || p_role_name || '%' AND ROWNUM <= p_end)
where RN > p_start;
end find_role;
在模糊查找条件里,'p_role_name' ,变量p_role_name两边不需要有引号,加了引号变成'%p_role_name%'字符串了,表里当然没有包含这个字符串的数据了。把引号去掉即可