RT 知道表名tablename 关键字KEYID为keyid_no 要求返回 字段NUM的值
SQL语句为 SELECT num FROM tablename WHERE KEYID=keyid_no
但是希望做成一个function 带入参数为字段名,关键字,和表名 让此函数返回值
我写了函数
create or replace
function fun_forlog(name varchar2, keyid_no int,tablenamec varchar2) return varchar2 as
test varchar2(40);
begin
select name
into test
from count2
where keyid=2;
return test;
end fun_forlog;
但是返还回来的不是我所要求的值
SQL语句为 SELECT num FROM tablename WHERE KEYID=keyid_no
但是希望做成一个function 带入参数为字段名,关键字,和表名 让此函数返回值
我写了函数
create or replace
function fun_forlog(name varchar2, keyid_no int,tablenamec varchar2) return varchar2 as
test varchar2(40);
begin
select name
into test
from count2
where keyid=2;
return test;
end fun_forlog;
但是返还回来的不是我所要求的值
解决方案 »
- PL/SQL 自动生成创建表的语句
- 求 oracle 10g R1 em 重建
- pl/sql函数是不是只能返回一个值?
- 创建函数报错?急
- 目标主机或对象不存在
- 怎样用exec命令执行带返回值的存储参数阿?
- 急!如何实现将oracle数据库中某张表的数据定时导出到SQL server中去
- 约束名称是存储在哪里的?
- 应用程序双击启动一会后就消失,无法打开.log_file ,trc_file,log_status文件显示如下错误:
- 如何登陆数据库?我安装时没有叫我输入用户名和密码啊!!我刚接触Oracle~~!
- to_date() 函数的问题,急!!!!!!!!!!!!!
- 急,在线等!oracle写文件的时候生成文本文件的格式,需要指定是UTF-8格式
execute immediate 'select ' || name ||'';
function fun_forlog(name varchar2, keyid_no int,tablenamec varchar2) return varchar2 as
test varchar2(40);
begin
execute immediate 'select '|| name
|| ' from ' || tablenamec || ' where keyid= ' || Keyid_no
returning into test;
return test;
end fun_forlog;
The name of the column whose value must match attrval in order for the record to be counted
attrval:
The value that attr is compared against
tbl:
The name of the table. set feedback off
set linesize 120
set pagesize 0create or replace function count_in_table
(attr in varchar2, attrval in varchar2, tbl in varchar2)
return number
is
cnt number;
begin
execute immediate 'select count(1) from ' || tbl || ' where ' || attr || ' = :a' into cnt using attrval;
return cnt;
end;
/
The name of the column whose value must match attrval in order for the record to be counted
attrval:
The value that attr is compared against
tbl:
The name of the table. set feedback off
set linesize 120
set pagesize 0create or replace function count_in_table
(attr in varchar2, attrval in varchar2, tbl in varchar2)
return number
is
cnt number;
begin
execute immediate 'select count(1) from ' || tbl || ' where ' || attr || ' = :a' into cnt using attrval;
return cnt;
end;
/
SQL>exec dbms_output.put_line(fun_forlog('num',3,'count2'))begin dbms_output.put_line(fun_forlog('num',3,'count2')); end;ORA-06547: INSERT?UPDATE?? DELETE ?????? RETURNING ??
ORA-06512: ?"TEST.FUN_FORLOG", line 4
ORA-06512: ?line 1
function fun_forlog(name varchar2, keyid_no int,tablenamec varchar2) return varchar2 as
test varchar2(40);
begin
execute immediate 'select ' ¦ ¦ name
¦ ¦ ' from ' ¦ ¦ tablenamec ¦ ¦ ' where keyid= ' ¦ ¦ Keyid_no
returning into test;
return test;
end fun_forlog;
其中粗体的returning应该去掉。select的结果直接into test就可以,而其他如update,insert,delete需要返回值即returning into test.