create or replace function timecoverbegin(outbegintime IN DATE,
outendtime IN DATE,
inbegintime IN DATE,
inendtime IN DATE)
RETURN date
as
tempTbl varchar2(300);
result date;
begin
tempTbl:=' create global temporary table tempTbl
(
tempdate date
) on commit delete rows ';
EXECUTE IMMEDIATE tempTbl;
insert into tempTbl(tempdate) values(outbegintime);
insert into tempTbl(tempdate) values(outendtime);
insert into tempTbl(tempdate) values(inbegintime);
insert into tempTbl(tempdate) values(inendtime);
select tempdate into result from tempTbl order by tempdate;
return result;
end timecoverbegin
;
/
outendtime IN DATE,
inbegintime IN DATE,
inendtime IN DATE)
RETURN date
as
tempTbl varchar2(300);
result date;
begin
tempTbl:=' create global temporary table tempTbl
(
tempdate date
) on commit delete rows ';
EXECUTE IMMEDIATE tempTbl;
insert into tempTbl(tempdate) values(outbegintime);
insert into tempTbl(tempdate) values(outendtime);
insert into tempTbl(tempdate) values(inbegintime);
insert into tempTbl(tempdate) values(inendtime);
select tempdate into result from tempTbl order by tempdate;
return result;
end timecoverbegin
;
/
create or replace function timecoverbegin(outbegintime IN DATE,
outendtime IN DATE,
inbegintime IN DATE,
inendtime IN DATE)
RETURN date
as
tempTbl varchar2(300);
result date;
begin
tempTbl:=' create global temporary table tempTbl
(
tempdate date
) on commit delete rows ';
EXECUTE IMMEDIATE tempTbl;
insert into tempTbl(tempdate) values(outbegintime);
insert into tempTbl(tempdate) values(outendtime);
insert into tempTbl(tempdate) values(inbegintime);
insert into tempTbl(tempdate) values(inendtime);
select tempdate into result from tempTbl where rownum=2 order by tempdate ;
return result;
end timecoverbegin
;
/
select tempdate into result from tempTbl order by tempdate;
SELECT INTO只能允许有一条记录,你这里会选出四条记录的。
但是function也不能用如下語句創建臨時表呀
create or replace function timecoverbegin(outbegintime IN DATE,
outendtime IN DATE,
inbegintime IN DATE,
inendtime IN DATE)
RETURN date
as
result date;
begin
create global temporary table tempTbl
(
tempdate date
) on commit delete rows;
EXECUTE IMMEDIATE tempTbl;
insert into tempTbl(tempdate) values(outbegintime);
insert into tempTbl(tempdate) values(outendtime);
insert into tempTbl(tempdate) values(inbegintime);
insert into tempTbl(tempdate) values(inendtime);
select tempdate into result from tempTbl where rownum=2 order by tempdate ;
return result;
end timecoverbegin
;
/
錯誤是“PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma”
我知道,create or replace function timecoverbegin(outbegintime IN DATE,
outendtime IN DATE,
inbegintime IN DATE,
inendtime IN DATE)
RETURN date
as
tempTbl varchar2(300);
result date;
begin
tempTbl:=
' create global temporary table tempTbl
(
tempdate date
) on commit delete rows;
insert into tempTbl(tempdate) values(outbegintime);
insert into tempTbl(tempdate) values(outendtime);
insert into tempTbl(tempdate) values(inbegintime);
insert into tempTbl(tempdate) values(inendtime);
';
EXECUTE IMMEDIATE tempTbl; select tempdate into result from tempTbl where rownum=2 order by tempdate ;
return result;
end timecoverbegin
;
/可我的select到哪裡去寫呢?
1、oracle中的临时表,指数据的临时,并不是表的临时,一次建完了,以后都能用,没必要每次都在函数中建。
你这么写函数,第二次调用肯定会出错,告诉你表已经存在的错误。2、select * .... where rownum=2; 这个是查不出任何东西的,即使你的表中有数据。
--按照你的思路给你改了一下,你自己参考吧。
CREATE OR REPLACE FUNCTION TIMECOVERBEGIN(OUTBEGINTIME IN DATE,
OUTENDTIME IN DATE,
INBEGINTIME IN DATE,
INENDTIME IN DATE) RETURN DATE AS
TEMPTBL VARCHAR2(300);
RESULT DATE;
BEGIN
TEMPTBL := ' create global temporary table tempTbl
(
tempdate date
) on commit delete rows
';
EXECUTE IMMEDIATE TEMPTBL; EXECUTE IMMEDIATE 'INSERT INTO TEMPTBL (TEMPDATE) VALUES (:OUTBEGINTIME)'
USING OUTBEGINTIME;
EXECUTE IMMEDIATE 'INSERT INTO TEMPTBL (TEMPDATE) VALUES (:OUTENDTIME)'
USING OUTENDTIME;
EXECUTE IMMEDIATE 'INSERT INTO TEMPTBL (TEMPDATE) VALUES (:INBEGINTIME)'
USING INBEGINTIME;
EXECUTE IMMEDIATE 'INSERT INTO TEMPTBL (TEMPDATE) VALUES (:INENDTIME)'
USING INENDTIME; SELECT TEMPDATE
FROM (SELECT TEMPDATE, ROW_NUMBER() OVER(ORDER BY TEMPDATE) ROW_
FROM TEMPTBL)
INTO RESULT;
WHERE ROW_ = 2; RETURN RESULT;
EXCEPTION
WHEN OTHERS THEN
RETURN 1;
END TIMECOVERBEGIN;
/
第二點
我知道的,該過來了 select tempdate into result from
(
select rownum n,tempdate from tempTbl order by tempdate
) where n=2;這樣對不?
INTO RESULT
FROM (SELECT ROWNUM N, TEMPDATE
FROM (SELECT TEMPDATE FROM TEMPTBL ORDER BY TEMPDATE))
WHERE N = 2;原因:rownum 的规则是先给记录编号,再排序。而你的意图是先排序再按照大小顺序取第二条,因此嵌套一层显然不符合逻辑。 因此就需要先排序,再编号,就出现了嵌套三层,当然你也可以用我10楼的写法,只嵌套一层。
我說下我寫這個函數的意圖和思路吧!
我需要比較4個時間的大小(先後順序)
然後返回4個時間,大家都知道,oracle里function只能返回一個值,所以想通過臨表里插入數據進行排序
獲取查詢到的第一條就是最大,第二條其次,……(ps:雖然要執行4次,但是可行的)
select OUTBEGINTIME as tempdate from dual union all
select OUTENDTIME from dual union all
select INBEGINTIME from dual union all
select INENDTIME from dual
)select tempdate from t1 order by tempdate;