我在function里建立了:
create or replace function GetUserName(Myuser_account varchar2) return varchar2 is
Result varchar2(50);
begin
declare cursor MyCursor is select distinct user_name from tbs_user where user_account =Myuser_account ;
begin
open MyCursor;
fetch MyCursor into Result;
close MyCursor;
end;
return(Result);
end GetUserName;
然后再试图里面调用
create or replace view view_tbp_failureupgrade as
select
t1.SQDWBM,t1.SQRQ,t1.XTBM,t1.LLDBH,t1.GDBH,t1.KF_GZXX,t1.KF_DJRBM,t1.WH1_CLNR,t1.WH1_RYBM,t1.WH1_RYBZBM,t1.WH2_CLNR,t1.WH2_RYBM,t1.WH2_RYBZBM,t1.WH3_CLNR,t1.WH3_RYBM,····
GetUserName(KF_DJRbm) as KF_DJRMC,
GetUserName(WH1_RYbm) as WH1_RYMC,
GetUserName(WH2_RYbm) as WH2_RYMC,
GetUserName(WH3_RYbm) as WH3_RYMC,
GetUserName(KF_HFRbm) as KF_HFRMC,
GetUserName(WH1_JG_DJRYbm) as WH1_JG_DJRYMC,
From TBP_FAILUREUPGRADE t1
他返回放的结果又很多行啊 ,怎么让他就返回table里面对应的一行?
为什么他会有那么多重复的行???
create or replace function GetUserName(Myuser_account varchar2) return varchar2 is
Result varchar2(50);
begin
declare cursor MyCursor is select distinct user_name from tbs_user where user_account =Myuser_account ;
begin
open MyCursor;
fetch MyCursor into Result;
close MyCursor;
end;
return(Result);
end GetUserName;
然后再试图里面调用
create or replace view view_tbp_failureupgrade as
select
t1.SQDWBM,t1.SQRQ,t1.XTBM,t1.LLDBH,t1.GDBH,t1.KF_GZXX,t1.KF_DJRBM,t1.WH1_CLNR,t1.WH1_RYBM,t1.WH1_RYBZBM,t1.WH2_CLNR,t1.WH2_RYBM,t1.WH2_RYBZBM,t1.WH3_CLNR,t1.WH3_RYBM,····
GetUserName(KF_DJRbm) as KF_DJRMC,
GetUserName(WH1_RYbm) as WH1_RYMC,
GetUserName(WH2_RYbm) as WH2_RYMC,
GetUserName(WH3_RYbm) as WH3_RYMC,
GetUserName(KF_HFRbm) as KF_HFRMC,
GetUserName(WH1_JG_DJRYbm) as WH1_JG_DJRYMC,
From TBP_FAILUREUPGRADE t1
他返回放的结果又很多行啊 ,怎么让他就返回table里面对应的一行?
为什么他会有那么多重复的行???
解决方案 »
- 朋友来,一个游标问题,望能解决
- 无法解除被锁的表
- 脏数据变脏是在事物未提交时?还是在缓存中修改却未写入文件时?
- 抓狂了,请问各位,为什么说存储过程可移植性差呢??有没有强人能给我举个简单点的例子说明关于Tsql和plsql存储不一样的地方?
- 请教高手
- oracle中的视图查询能够for update吗
- 问一个关于analyze的问题
- 我在Oracle里面建了一个触发器,为什么老是提示有错?谁帮我看一下。谢谢
- Oracle 如何计算年龄,年龄的格式是:X岁X月X天 如2013年1月1日出生,计算的年龄是:1岁6月19天
- 如何根据oracle数据库的数据内容 查找到所属的表格
- Oracle 8i 下载地址?
- 大家进来帮忙,求一条语句,是求周期的
我是初学者还请多多指教啊!
创建视图的语句可以使用rownum或distinct来限制返回行数
create or replace function GetUserName(Myuser_account varchar2) return varchar2 is
Result varchar2(50);
begin
declare cursor MyCursor is select distinct user_name from tbs_user where user_account =Myuser_account and rownum =1;
begin
open MyCursor;
fetch MyCursor into Result;
close MyCursor;
end;
return(Result);
end GetUserName;
t1: mc1 bm1 mc2 bm2
1 2 3 4
t2: xx aa
1 a
我建的试图让t1.bm1=t2.xx并且 t1.bm2=t2.xx
哪位高手知道啊 ,请指点啊!!!!
如果t2中的XX都是唯一的就没有问题如果不唯一,返回的行数大于1,就报错了