动态多表查询的存储过程报错：ORA-00918

ORA-00918: column ambiguously defined （字段模糊的定义）
发生这种错误常常是多表join后不同表有相同名字的字段，在使用该字段时未加表名前缀。
而动态的查询中的判断条件where是拼接而成的，拼的时候往往容易忘加表名前缀而导致报错ORA-00918:
测试：

/*
    基本员工信息表 tbl_hos_employee
 */
drop table tbl_hos_employee;
create table tbl_hos_employee(
    emp_no int primary key ,
    emp_name varchar2(20) not null
);
drop sequence seq_hos_emp_no;
create sequence seq_hos_emp_no
    increment by 1
    start with 1
    nocycle;
drop trigger hos_emp_trigger;
CREATE TRIGGER hos_emp_trigger BEFORE
insert ON tbl_hos_employee FOR EACH ROW
begin
    select seq_hos_emp_no.nextval into:New.emp_no from dual;
end;
/
insert into tbl_hos_employee(0,'张三');
insert into tbl_hos_employee(0,'李四');
insert into tbl_hos_employee(0,'王五');
commit;

/*
    病人表 tbl_hos_sick
 */
drop table tbl_hos_sick;
create table tbl_hos_sick(
    sick_no int primary key,
    sick_name varchar2(20) not null
);
drop sequence seq_hos_sick_no;
create sequence seq_hos_sick_no
    increment by 1
    start with 1
    nocycle;
drop trigger hos_sick_trigger;
create trigger hos_sick_trigger BEFORE
insert  on tbl_hos_sick for each row 
begin
    select seq_hos_sick_no.nextval into:New.sick_no from dual;
end;
/
insert into tbl_hos_sick(0,'赵钱');
insert into tbl_hos_sick(0,'孙李');
commit;

/*
    问诊表tbl_hos_inquiry
 */
drop table tbl_hos_inquiry;
create table tbl_hos_inquiry(
    emp_no int ,
    sick_no int not null,
    inquiry_time date default sysdate,
    all_cost number(16,2) default 0,
    inquiry_id int ,
    inquiry_status int default 0,
    constraint UN_inquiry_id  unique (inquiry_id),
    constraint FK_inquiry_emp foreign key (emp_no) references tbl_hos_employee(emp_no),
    constraint FK_inquiry_sick foreign key (sick_no) references tbl_hos_sick(sick_no),
    constraint PK_sick_emp primary key (emp_no,sick_no)
);
drop sequence seq_hos_inquiry_id;
create sequence seq_hos_inquiry_id
    increment by 1
    start with 1
    nocycle;
drop trigger hos_inquiry_trigger;
create trigger hos_inquiry_trigger BEFORE
insert on tbl_hos_inquiry for each row
begin 
    select seq_hos_inquiry_id.nextval into:New.inquiry_id from dual;
end;
/
insert into tbl_hos_inquiry(emp_no,sick_no,all_cost) values(1,2,50);
insert into tbl_hos_inquiry(emp_no,sick_no,all_cost) values(2,1,500);
insert into tbl_hos_inquiry(emp_no,sick_no,all_cost) values(1,1,50);
insert into tbl_hos_inquiry(emp_no,sick_no,all_cost) values(2,2,500);
commit;

动态查询tbl_hos_inquiry表：

create or replace procedure sp_hos_query_inquiry(
          o_result out sys_refcursor,
          o_page_count out int,          --out 数据总条数    null
          i_page_size int,               --in 分页大小       10
          i_page_index int,              --in 当前页数       1
          i_order int,              --in 根据xx值排序   null
          i_order_sort int,
          i_emp_name varchar2,
          i_sick_name varchar2,
          i_time_start varchar2,
          i_time_end varchar2,
          i_cost_start number,
          i_cost_end number,
          i_inquiry_id int,
          i_status int
) is
type aray_type is array(20) of varchar2(20);
orders aray_type :=aray_type();
strWhere varchar2(2000);
strOrder varchar2(2000);
strQuery varchar2(2000);
strCount varchar2(2000);
page_start int;
page_end int;
begin
    --动态排序
  orders.extend(7);
  orders(1):=null;
  orders(2):='inquiry_id';
  orders(3):='emp_no';
  orders(4):='sick_no';
  orders(5):='inquiry_time';
  orders(6):='all_cost';
  orders(7):='inquiry_status';

  strOrder := ' ';
  strWhere:=' where 1=1 and inquiry_id>1  ';

  --当前页始末数据
  page_start :=(i_page_index-1) * i_page_size +1;
  page_end := page_start + i_page_size -1;

  --拼接动态排序语句
  if i_order is null then
     begin
             strOrder := strOrder || ' order by '|| orders(i_order);
             if i_order_sort =2 then
               strOrder := strOrder || ' desc ';
             else
               strOrder := strOrder || ' asc ';  
             end if;
     end;
    end if; 

    --拼接动态查询语句 
  if i_emp_name is not null then
             strWhere := strWhere || ' and emp_no in (select emp_no from tbl_hos_employee where emp_name like ''%'|| i_emp_name|| '%'')';
    end if;
  if i_sick_name is not null then
             strWhere := strWhere || ' and sick_no in (select sick_no from tbl_hos_sick where sick_name like ''%'|| i_sick_name|| '%'')';
    end if;
   if i_time_start is not null  then
             strWhere := strWhere || ' and inquiry_time>= to_date('''|| i_time_start ||''',''YYYY-MM-DD'')';
    end if;
   if i_time_end is not null then
             strWhere := strWhere || ' and inquiry_time<=to_date('''|| i_time_end ||''',''YYYY-MM-DD'')';
    end if;
   if i_cost_start is not null then
             strWhere := strWhere || ' and all_cost>=' || i_cost_start;
     end if;
   if i_cost_end is not null then
             strWhere := strWhere || ' and all_cost<=' || i_cost_end;
     end if;
   if i_inquiry_id is not null and i_inquiry_id!=0 then
             strWhere := strWhere || ' and inquiry_id = '|| i_inquiry_id;
     end if;
   if i_status is not null then
             strWhere := strWhere || ' and inquiry_status = ' || i_status;
     end if;

     --获取查询数据总数
    strCount:='select count(*) from tbl_hos_inquiry ' || strWhere;
    dbms_output.put_line(strCount);
    execute immediate strCount into o_page_count;

    --查询语句拼接
    strQuery:= 'select '
               || ' inquiry_id as id,'
               || ' inquiry_time as time,'
               || ' all_cost as cost,'
               || ' inquiry_status as status,'
               || ' e.emp_no as emp_id , e.emp_name, '
               || ' s.sick_no as sick_id , s.sick_name '
               || 'from( select rownum as rn , t.* from tbl_hos_inquiry t '
               || strWhere || ' ) i '
               || ' left join tbl_hos_employee e on i.emp_no = e.emp_no '
               || ' left join tbl_hos_sick s on i.sick_no = s.sick_no '
               || strWhere  --报错ORA-00918  ，将此处的strWhee 改为 'where 1=1 '
               || ' and rn>='|| page_start
               || ' and rn<='|| page_end 
               || strOrder;
   dbms_output.put_line(strQuery);
   open o_result for strQuery;
end sp_hos_query_inquiry;

在上面例子中，拼接strWhere时emp_no与sick_no字段皆加未表名前缀，所以将strWhere作为三表连接之后的判断条件，就会出现emp_no与sick_no字段模糊的报错。故先用strWhere查询tbl_hos_inquiry表，再将查询的结果与另外两表连接，避免了错误。