create or replace procedure insertTest is
begin
insert into T_DAILY_REPORT(ID,PROVINCE_ID,REGIST_NUM,ORDER_NUM,DATE_TIME)
values (SEQ_DAILY_REPORT.NEXTVAL,
select (select to_char(sysdate-1,'YYYY-MM-DD' )from dual) as time, NVL (test1.id,test2.id) as PID ,
NVL (test1.CAS_NUM,0) as num1,
NVL (test2.CAS_NUM,0) as num2
from
(SELECT T1.ID,COUNT(T1.ID) CAS_NUM FROM T_PROVINCE T1
RIGHT JOIN
T_CITY T2 ON T1.ID=T2.PROVINCE_ID
RIGHT JOIN
(SELECT REGION_ID FROM T_CAS_TRADE WHERE TRADE_TYPE = '1'
and
substr(T_CAS_TRADE.TRADE_TIME, 1, 10) =(select to_char(sysdate-1,'YYYY-MM-DD' )from dual)
) T3 ON T2.ID=T3.REGION_ID GROUP BY T1.ID) test1
LEFT outer join
(SELECT T1.ID,COUNT(T1.ID) CAS_NUM FROM T_PROVINCE T1
RIGHT JOIN
T_CITY T2 ON T1.ID=T2.PROVINCE_ID
RIGHT JOIN
(SELECT REGION_ID FROM T_CAS_USERST WHERE 1=1
and substr(t_cas_userst.create_time, 1, 10) =(select to_char(sysdate-1,'YYYY-MM-DD' )from dual)
) T3
ON T2.ID=T3.REGION_ID GROUP BY T1.ID) test2
on test1.id=test2.id
end insertTest;报错Compilation errors for PROCEDURE KAREPORT.INSERTTESTError: PL/SQL: ORA-00936: 缺失表达式
Line: 6
Text: select (select to_char(sysdate-1,'YYYY-MM-DD' )from dual) as time,Error: PL/SQL: SQL Statement ignored
Line: 3
Text: insert into T_DAILY_REPORT(ID,PROVINCE_ID,REGIST_NUM,ORDER_NUM,DATE_TIME)Error: PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
( begin case
declare end exception exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
Line: 31
Text: end insertTest;在线等啊~~~真心的跪求有人能来解决!!!!!! 急!!!
begin
insert into T_DAILY_REPORT(ID,PROVINCE_ID,REGIST_NUM,ORDER_NUM,DATE_TIME)
values (SEQ_DAILY_REPORT.NEXTVAL,
select (select to_char(sysdate-1,'YYYY-MM-DD' )from dual) as time, NVL (test1.id,test2.id) as PID ,
NVL (test1.CAS_NUM,0) as num1,
NVL (test2.CAS_NUM,0) as num2
from
(SELECT T1.ID,COUNT(T1.ID) CAS_NUM FROM T_PROVINCE T1
RIGHT JOIN
T_CITY T2 ON T1.ID=T2.PROVINCE_ID
RIGHT JOIN
(SELECT REGION_ID FROM T_CAS_TRADE WHERE TRADE_TYPE = '1'
and
substr(T_CAS_TRADE.TRADE_TIME, 1, 10) =(select to_char(sysdate-1,'YYYY-MM-DD' )from dual)
) T3 ON T2.ID=T3.REGION_ID GROUP BY T1.ID) test1
LEFT outer join
(SELECT T1.ID,COUNT(T1.ID) CAS_NUM FROM T_PROVINCE T1
RIGHT JOIN
T_CITY T2 ON T1.ID=T2.PROVINCE_ID
RIGHT JOIN
(SELECT REGION_ID FROM T_CAS_USERST WHERE 1=1
and substr(t_cas_userst.create_time, 1, 10) =(select to_char(sysdate-1,'YYYY-MM-DD' )from dual)
) T3
ON T2.ID=T3.REGION_ID GROUP BY T1.ID) test2
on test1.id=test2.id
end insertTest;报错Compilation errors for PROCEDURE KAREPORT.INSERTTESTError: PL/SQL: ORA-00936: 缺失表达式
Line: 6
Text: select (select to_char(sysdate-1,'YYYY-MM-DD' )from dual) as time,Error: PL/SQL: SQL Statement ignored
Line: 3
Text: insert into T_DAILY_REPORT(ID,PROVINCE_ID,REGIST_NUM,ORDER_NUM,DATE_TIME)Error: PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
( begin case
declare end exception exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
Line: 31
Text: end insertTest;在线等啊~~~真心的跪求有人能来解决!!!!!! 急!!!
--乱七八糟的给你改了一下。
--求日期用不着select to_char(sysdate-1,'YYYY-MM-DD' )from dual
--insert into 后面写values 就不能用select,用select 就不能用values
CREATE OR REPLACE PROCEDURE INSERTTEST IS
BEGIN INSERT INTO T_DAILY_REPORT
(ID, PROVINCE_ID, REGIST_NUM, ORDER_NUM, DATE_TIME)
SELECT SEQ_DAILY_REPORT.NEXTVAL,
TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD') AS TIME,
NVL(TEST1.ID, TEST2.ID) AS PID,
NVL(TEST1.CAS_NUM, 0) AS NUM1,
NVL(TEST2.CAS_NUM, 0) AS NUM2
FROM (SELECT T1.ID, COUNT(T1.ID) CAS_NUM
FROM T_PROVINCE T1
RIGHT JOIN T_CITY T2 ON T1.ID = T2.PROVINCE_ID
RIGHT JOIN (SELECT REGION_ID
FROM T_CAS_TRADE
WHERE TRADE_TYPE = '1'
AND SUBSTR(T_CAS_TRADE.TRADE_TIME, 1, 10) =
TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD')) T3 ON T2.ID =
T3.REGION_ID
GROUP BY T1.ID) TEST1
LEFT OUTER JOIN (SELECT T1.ID, COUNT(T1.ID) CAS_NUM
FROM T_PROVINCE T1
RIGHT JOIN T_CITY T2 ON T1.ID = T2.PROVINCE_ID
RIGHT JOIN (SELECT REGION_ID
FROM T_CAS_USERST
WHERE 1 = 1
AND SUBSTR(T_CAS_USERST.CREATE_TIME,
1,
10) =
TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD')) T3 ON T2.ID =
T3.REGION_ID
GROUP BY T1.ID) TEST2 ON TEST1.ID = TEST2.ID;END INSERTTEST;
--如果按照你自己的逻辑来改
--values (SEQ_DAILY_REPORT.NEXTVAL, 删掉
select (select to_char(sysdate-1,'YYYY-MM-DD' )from dual) as time,
--改为
select SEQ_DAILY_REPORT.NEXTVAL,
(select to_char(sysdate-1,'YYYY-MM-DD' )from dual) as time,--程序最后
on test1.id=test2.id
--改为
on test1.id=test2.id;