14动态游标

/*动态游标是用于执行动态SQL语句。*/declare  --oracle9i之前的定义  Type REFCURSOR is REF CURSOR;  cs_user REFCURSOR;  --oracle9i之后的定义  --cs_user sys_refcursor;  v_sql      varchar2(1000);  v_username varchar2(100);  v_password varchar2(100);  v_row  T_Userinfo%RowType;  v_dept T_Sys_Dept%RowType;begin  --SQL语句的拼凑  v_username := 'a';  v_password := null;  v_sql := 'Select * From T_Userinfo where 1 = 1';  if v_username is not null and v_username != ' ' then    v_sql := v_sql || '     and username like ''%' || v_username ||             '%''       ';  end if;  if v_password is not null and v_password != ' ' then    v_sql := v_sql || ' and password = ''';    v_sql := v_sql || v_password;    v_sql := v_sql || '''';    end if;  v_sql := v_sql || ' order by userid asc';  dbms_output.put_line(v_sql);  --使用动态游标来打开动态SQL.  open cs_user for v_sql;  loop    fetch cs_user      into v_row;      if cs_user%notfound then      exit;    end if;    dbms_output.put_line(v_row.userid || '   ' || v_row.username);    end loop;  close cs_user;  dbms_output.put_line('读取另外一张表的数据');  v_sql := 'Select * From T_Sys_Dept order by dept_id asc';  open cs_user for v_sql;  loop    fetch cs_user      into v_dept;      if cs_user%notfound then      exit;    end if;    dbms_output.put_line(v_dept.dept_id || '   ' || v_dept.dept_name);  end loop;  close cs_user;end;--Select * From T_Userinfo where 1 = 1 order by userid asc