根据用户输入的表名及字段名等参数动态查询 ,用户可选多个字段 这样的PL/SQL函数怎么写
下面是我写的 但是不起作用 V_allColumnName 是个字符串, 就是用户选择的列名然后用逗号隔开 V_tableName是表名 表只可选一个function TabelData_Query(V_tableName in varchar2,V_allColumnName in varchar2)return report_row_type is result report_row_type;
begin
open result for
select V_allColumnName
from (select TABLE_NAME
from USER_TAB_COLUMNS
where TABLE_NAME = V_tableName);
return result;
end;
请高手指点
下面是我写的 但是不起作用 V_allColumnName 是个字符串, 就是用户选择的列名然后用逗号隔开 V_tableName是表名 表只可选一个function TabelData_Query(V_tableName in varchar2,V_allColumnName in varchar2)return report_row_type is result report_row_type;
begin
open result for
select V_allColumnName
from (select TABLE_NAME
from USER_TAB_COLUMNS
where TABLE_NAME = V_tableName);
return result;
end;
请高手指点
解决方案 »
- 行转列的时候怎么过滤掉重复字段值?row_number() over partition by
- ORA-01861: literal does not match format string
- oracle:delete触发器怎么更新当前表
- SQL可以单独执行,但在包体里执行就报错
- sql 百万级数据库 查询很慢 大神优化下 万分感谢
- 一个字符串替换
- 關於單行游標的數據返回問題!!
- 请问@ 和%%的用法
- 在REDHAT9.0下安装ORACLE8.1.7时运行./runInstaller时出错,请各位高手指点一下!
- ALTER DATABASE db1 CHARACTER SET WE8ISO8859P1
- VC无法访问Oracle数据库
- 菜鸟求救:提高这个SQL的性能……
from (select TABLE_NAME
from USER_TAB_COLUMNS
where TABLE_NAME = V_tableName);
换成,sql需要动态
'select '||V_allColumnName||' from '|| V_tableName;
Compilation errors for PACKAGE BODY TOOLING.MRP1_TOOL_REPORTGENERATOR010Error: PLS-00103: 发现了符号 "|" 当你等待下列事项之一发生时:
. ( * @ % & = -
+ ; < / > at in is mod not rem <an exponent (**)>
<> or != or ~= >= <= <> and or like between using ||
Line: 25
Text: 'select ' | ¦V_allColumnName ||' from ' || V_tableName;
我以前写的,不过只能有一个字段create or replace function LinkAll(sCol varchar2, sTable varchar2,sWhere varchar2,sType varchar2)
return varchar2
is
str varchar2(500);
-- sCol varchar2(100); --连接的字段
-- sTable varchar2(50); --查询的表
-- sWhere varchar2(200);--查询的条件
-- sType varchar2(10); --连接时的分割符
sReturn varchar2(50);
UnionReturn varchar2(2000);
type cursor_type is ref cursor;
c1 cursor_type;
begin
str:='select '||sCol||' from '||sTable||' where 1=1 '||sWhere;
open c1 for str;
loop
fetch c1 into sReturn;
exit when c1%notfound;
UnionReturn:=UnionReturn||sType||sReturn;
end loop;
UnionReturn:=ltrim(UnionReturn,sType);
return UnionReturn;
end ;
但是可以用produce来写,测试过了CREATE OR REPLACE PACKAGE PK_REPORT IS
TYPE refcursor IS REF CURSOR;
end PK_REPORT;
CREATE OR REPLACE PROCEDURE TabelData_Query
(
V_tableName in varchar2,
V_allColumnName in varchar2,
V_REPORT OUT PK_REPORT.REFCURSOR
)
IS
BEGIN
OPEN V_REPORT FOR 'select '||V_allColumnName ||' from ' || V_tableName;
END TabelData_Query;
function TabelData_Query(V_tableName in varchar2,V_allColumnName in varchar2)return report_row_type is result report_row_type;
begin
open result for
select V_allColumnName
from (select TABLE_NAME
from USER_TAB_COLUMNS
where TABLE_NAME = V_tableName);
return result;
end;
report_row_type 是我在包头中定义的游标
result 就是个游标
然后我在java 中用这个游标就可以了
例如
BaseServletAgent bsa1 = new BaseServletAgent(codeBase);
Vector arg = new Vector();
BaseVO bvo = new BaseVO(USERNAME,this.getClass().toString(),CommandName.CallPLSQLCmd,arg);
bvo.setPackageName("mrp1_Tool_ReportGenerator010");
bvo.setFunctionName("TabelData_Query");
data.clear();
try {
arg.clear();
arg.add(tablename);
arg.add(columnsname);
ResultVO rvo = bsa1.doFunc(bvo);
Vector result = rvo.getData();
if (result.size() > 1) {
for(int i=1;i<result.size();i++){
Vector row = (Vector)result.get(i);
Vector vec = new Vector();
for(int j=0;j<row.size();j++){
vec.add(row.get(j));
}
data.add(vec);
}
}
} catch (Exception e) {
e.printStackTrace();
}
function TabelData_Query(V_tableName in varchar2,V_allColumnName in varchar2)return report_row_type is result report_row_type;
begin
open result for
'select ' || V_allColumnName ||' from ' || V_tableName;
return result;
end;