我有一个rowtype类型的变量,变量名:one_row -- (select * into one_row from tpl_attribute_dynamic where tag_name = 'aaa')
须要从一个中提取一个列值,列名保存在另一个变量里:c_name_var := 'tag_name'
将one_row和c_name_var传入一个函数中,在函数中我试图用某种方法达到“从行变量中提取列值”置的目的。想请教各位该如何实现?
以下是我做的一些实验,仅供参考:
--使用静态SQL可以取到,但是不满足“tag_name存储在变量中”前提条件
declare
ss VARCHAR2(1024);
one_row tpl_attribute_dynamic%rowtype;
begin
select * into one_row from tpl_attribute_dynamic where tag_name = 'aaa';
select one_row.tag_name into ss from dual;
dbms_output.put_line(ss);
end;
--使用execute immediate执行动态SQL,执行报错“one_row.tag_name:invalid identifier”
declare
ss VARCHAR2(1024);
one_row tpl_attribute_dynamic%rowtype;
tmp_str VARCHAR2(1024);
begin
select * into one_row from tpl_attribute_dynamic where tag_name = 'aaa';
tmp_str := 'select one_row.tag_name from dual';
execute immediate tmp_str into ss;
dbms_output.put_line(ss);
end;
--使用DBMS_SQL包执行动态SQL,执行还是报错“one_row.tag_name:invalid identifier”
declare
one_row tpl_attribute_dynamic%rowtype;
tmp_str VARCHAR2(1024);
mycursor INT;
ignore INT;
ss VARCHAR2(1024);
begin
select * into one_row from tpl_attribute_dynamic where tag_name = 'aaa';
tmp_str := 'select one_row.tag_name from dual';
mycursor := dbms_sql.open_cursor;
dbms_sql.parse(mycursor,tmp_str,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(mycursor,1,ss,1000);
ignore := dbms_sql.execute(mycursor);
WHILE DBMS_SQL.FETCH_ROWS(mycursor)<> 0 LOOP
DBMS_SQL.COLUMN_VALUE(mycursor,1,ss);
END loop;
dbms_sql.close_cursor(mycursor);
dbms_output.put_line(ss);
end;
须要从一个中提取一个列值,列名保存在另一个变量里:c_name_var := 'tag_name'
将one_row和c_name_var传入一个函数中,在函数中我试图用某种方法达到“从行变量中提取列值”置的目的。想请教各位该如何实现?
以下是我做的一些实验,仅供参考:
--使用静态SQL可以取到,但是不满足“tag_name存储在变量中”前提条件
declare
ss VARCHAR2(1024);
one_row tpl_attribute_dynamic%rowtype;
begin
select * into one_row from tpl_attribute_dynamic where tag_name = 'aaa';
select one_row.tag_name into ss from dual;
dbms_output.put_line(ss);
end;
--使用execute immediate执行动态SQL,执行报错“one_row.tag_name:invalid identifier”
declare
ss VARCHAR2(1024);
one_row tpl_attribute_dynamic%rowtype;
tmp_str VARCHAR2(1024);
begin
select * into one_row from tpl_attribute_dynamic where tag_name = 'aaa';
tmp_str := 'select one_row.tag_name from dual';
execute immediate tmp_str into ss;
dbms_output.put_line(ss);
end;
--使用DBMS_SQL包执行动态SQL,执行还是报错“one_row.tag_name:invalid identifier”
declare
one_row tpl_attribute_dynamic%rowtype;
tmp_str VARCHAR2(1024);
mycursor INT;
ignore INT;
ss VARCHAR2(1024);
begin
select * into one_row from tpl_attribute_dynamic where tag_name = 'aaa';
tmp_str := 'select one_row.tag_name from dual';
mycursor := dbms_sql.open_cursor;
dbms_sql.parse(mycursor,tmp_str,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(mycursor,1,ss,1000);
ignore := dbms_sql.execute(mycursor);
WHILE DBMS_SQL.FETCH_ROWS(mycursor)<> 0 LOOP
DBMS_SQL.COLUMN_VALUE(mycursor,1,ss);
END loop;
dbms_sql.close_cursor(mycursor);
dbms_output.put_line(ss);
end;
解决方案 »
- OUI-25031的问题请各位大哥指教
- 这么写为什么不行,谁能给我解释一下这段sql
- 查找每个货品的最后二次进价记录
- 我司用的是艾旺的GEO软件,使用oracle数据库,涉及java语言,我是初学者,你说我应该从何学起,要实用的.
- 关于oracle中集合操作minus
- 难得一见的怪问题,请各位高手进来切磋下....
- 创建sequence的问题?
- 启动Oracle ManagementServer报997错误
- 请问那里有oracle下载阿
- ORA-04031: unable to allocate 2560 bytes of shared memory ("large pool","unknown object","session heap","First curdef allocation
- 请教一个关于同一张表相同字段的数据比较问题!
- 调用一个非常简单的存储过程出错
declare
ss VARCHAR2(1024);
one_row tpl_attribute_dynamic%rowtype;
begin
--如果你的查询结果是一行的没有问题
select * into one_row from tpl_attribute_dynamic where tag_name = 'aaa';
select one_row.tag_name into ss from dual;
dbms_output.put_line(ss.col1); --写出你要打印的列名
end;
scott@YPCOST> declare
2 emp_row emp%rowtype;
3 begin
4 select * into emp_row from emp where empno=7788;
5 dbms_output.put_line('员工编号为:'||emp_row.empno||'员工姓名为:'||emp_row.ename);
6 end;
7 /
员工编号为:7788员工姓名为:SCOTTPL/SQL procedure successfully completed.
--------------------------------
ename 1 declare
2 ss varchar2(100);
3 one_row emp%rowtype;
4 tmp_str varchar2(1000);
5 begin
6 tmp_str:='select * from emp where '||:c_name_var||'=''SCOTT''';
7 execute immediate tmp_str into one_row;
8 dbms_output.put_line(one_row.ename);
9 tmp_str:='select one_row.'||:c_name_var||' from dual';
10 dbms_output.put_line(tmp_str);
11 --execute immediate tmp_str into ss;
12 --dbms_output.put_line(ss);
13* end;
SQL> /
SCOTT
select one_row.ename from dualPL/SQL procedure successfully completed.
若那两条注释的去掉的话,就出错了。是因为不能写成select one_row.ename from dual.这 句话就是错的.怎么能把one_row.ename取出来 我也不知道 .. 等待答案.
就不能写成‘execute immediate tmp_str into ss;’这样子了!
--其实提示很明显了 你的拼接有问题 下面是修正 的--- select * into one_row from tpl_attribute_dynamic where tag_name = 'aaa';
只有一条记录的话就没问题
declare
ss VARCHAR2(1024);
one_row tpl_attribute_dynamic%rowtype;
begin
select * into one_row from tpl_attribute_dynamic where tag_name = 'aaa';
execute immediate 'select '''||one_row.tag_name||''' from dual' into ss;
dbms_output.put_line(ss);
end;