动态多表查询的存储过程报错：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 BEFOREinsert ON tbl_hos_employee FOR EACH ROWbegin    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 BEFOREinsert  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 BEFOREinsert on tbl_hos_inquiry for each rowbegin     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) istype 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表，再将查询的结果与另外两表连接，避免了错误。