为什么不直接调用
select '华北区',code,name from emp;如果确需如此,需要用动态SQL
declare
l_sql varchar(1000);
area_name varchar2(30);
begin
area_name := '华北区';
l_sql :='select '''||area_name||''''||' ,code, name from emp ';
execute immediate l_sql;
end;
/
select '华北区',code,name from emp;如果确需如此,需要用动态SQL
declare
l_sql varchar(1000);
area_name varchar2(30);
begin
area_name := '华北区';
l_sql :='select '''||area_name||''''||' ,code, name from emp ';
execute immediate l_sql;
end;
/
不对啊,提示:无效的列名。
不好意思,是我敲错了。
多谢了。
在SQL*PLUS中
set serveroutput on
然后
declare
l_sql varchar(1000);
area_name varchar2(30);
begin
area_name := '华北区';
l_sql :='select '''||area_name||''''||' ,code, name from emp ';
DBMS_OUTPUT.PUT_LINE(l_sql);
execute immediate l_sql;
end;
/
2 area_name varchar2(30);
3 type t_sor is ref cursor;
4 v_sor t_sor;
5 v_employeeno info.employeeno%type;
6
7 begin
8 area_name := '华北区';
9 open v_sor for 'select '''||area_name||''',employeeno from info';
10 fetch v_sor into area_name,v_employeeno;
11 loop
12 exit when v_sor%notfound;
13 dbms_output.put_line(area_name||' '||v_employeeno);
14 fetch v_sor into area_name,v_employeeno;
15 end loop;
16 end;
17 /
华北区 0212
华北区 0213
华北区 0221
华北区 0214
华北区 0211 PL/SQL procedure successfully completed修改如下:
declare
area_name varchar2(30);
type t_sor is ref cursor;
v_sor t_sor;
v_code emp.code%type;
v_name emp.name%type;
begin
area_name := '华北区';
open v_sor for 'select '''||area_name||''',code,name from emp';
fetch v_sor into area_name,v_code,v_name;
loop
exit when v_sor%notfound;
dbms_output.put_line(area_name||' '||v_code||' '||v_name);
fetch v_sor into area_name,v_code,v_name;
end loop;
end;
/