Create Function Union_TIMECARDDATA(@ID nvarchar(40),@Date Varchar(10))
Returns Varchar(1000)
As
Begin
Declare @S Varchar(1000)
Set @S=''
Select @S=@S+TimeCardTime+',' From ATDTIMECARDDATA Where PERSONID=@ID And TimeCardDate=@Date Order By PERSONID,TimeCardDate,TimeCardTime
Return(Left(@S,Len(@S)-1))
End
GO
Returns Varchar(1000)
As
Begin
Declare @S Varchar(1000)
Set @S=''
Select @S=@S+TimeCardTime+',' From ATDTIMECARDDATA Where PERSONID=@ID And TimeCardDate=@Date Order By PERSONID,TimeCardDate,TimeCardTime
Return(Left(@S,Len(@S)-1))
End
GO
解决方案 »
- 谁知道个人地址类型和单位地址类型是指什么?
- 多实例Oracle的远程链接
- 急问,Oracle8服务器的最大游标数(Cursors)如何设置??????
- VC整合 PRO*C后 调试的问题? 学生叩拜。。
- SQLServer放文章的字段,转到Oracle下不够长怎么办?
- Oracle US7ASCII字符集在C#中对应的Encoding编码名称是什么?
- oracle 新手上路,建数据库出现问题
- HELP!下面这条SQL语句什么意思?
- 偶是Oracle的小菜,问一些Oracle和MSSQL相比较的一些事情.进来就有分.
- 哪为大虾解释一下内联接,外联接,左连接,右连接
- sql 顺序问题 排序问题
- 海量数据的分页效率问题
Return Varchar2(1000)
is
P_S Varchar(1000);
Begin
P_S='' ;
Select P_S=P_S||TimeCardTime||',' From ATDTIMECARDDATA Where PERSONID=P_ID And TimeCardDate=P_Date Order By PERSONID,TimeCardDate,TimeCardTime;
Return(P_S);
End;
Return Varchar2(1000)
is
P_S Varchar(1000);
Begin
P_S='' ;
Select P_S=P_S||TimeCardTime||',' From ATDTIMECARDDATA Where PERSONID=P_ID And TimeCardDate=P_Date Order By PERSONID,TimeCardDate,TimeCardTime;
Return(P_S);
End;
Return Varchar2(1000)
is
P_S Varchar(1000);
R_S Varchar(1000);
Begin
R_S:='' ;
Select TimeCardTime into P_S From ATDTIMECARDDATA Where PERSONID=P_ID And TimeCardDate=P_Date Order By PERSONID,TimeCardDate,TimeCardTime;
R_S:=R_S||','||P_S
Return(R_S);
End; 其中TimeCardTime 是表ATDTIMECARDDATA 的一个字段.哪位高手帮忙看看哪里写错了,谢谢!
create or replace function Union_TIMECARDDATA(v_ID nvarchar2,v_Date Varchar2)
return varchar2
as
v_s varchar2(1000);
begin
v_s := '';
for rec in(Select TimeCardTime From ATDTIMECARDDATA
Where PERSONID=v_ID And TimeCardDate=v_Date
Order By PERSONID,TimeCardDate,TimeCardTime
)
loop
v_s := v_s||rec.TimeCardTime||',';
end loop;
v_s := substr(v_s,1,length(v_s)-1);
return v_s;
end;
/
CREATE OR REPLACE FUNCTION UNION_TIMECARDDATA(ID_IN VARCHAR2,DATE_IN VARCHAR2)RETURN VARCHAR2 IS
CUR_IN SYS_REFCURSOR;
P_S VARCHAR2(1000);
V_SQL VARCHAR2(1000);
V_DATE DATE;
BEGIN
V_SQL := 'SELECT TIMECARDTIME
FROM ATDTIMECARDDATA
WHERE PERSONID = :IDS
AND TIMECARDDATE = :DATES
ORDER BY PERSONID,TIMECARDDATE,TIMECARDTIME';
OPEN CUR_IN FOR V_SQL USING ID_IN,DATE_IN;
LOOP
EXIT WHEN CUR_IN%NOTFOUND;
FETCH CUR_IN INTO V_DATE;
P_S := V_DATE || ',';
END LOOP;
IF CUR_IN%ISOPEN THEN
CLOSE CUR_IN;
END IF;
RETURN(RTRIM(P_S,','));
END;