create or replace procedure createLyReleaseForCc isbegin
truncate table --这里报错
ly_release_for_cc;
insert /*+ append */
into ly_release_for_cc
(ziliao_id,
project_name,
status_date,
status,
user_name,
telephone,
mobile,
email,
address,
content,
CCCOUNT,
done_count,
var2,
agent_id,
zl_status_date,
owner,
project_code,
company_name,
company_code,
num2,
num1,
zl_status
) select ly.ziliao_id,
ly.project_name,
ly.status_date,
ly.status,
ly.user_name,
ly.telephone,
ly.mobile,
ly.email,
ly.address,
ly.content,
coalesce(cc, 0),
count(ly.ziliao_id) over(partition by ly.ziliao_id) as done_count, --在规定时间内,是否已做过 这里报错
ly.var2 as agentName,
ly.agent_id,
ly_ziliao.status_date,
ly.owner,
ly.project_code,
ly.company_name,
ly.company_code,
ly.num2,
ly.num1,
ly_ziliao.status
from ly_release ly
left outer join (select ziliao_id, count(ziliao_id) as cc
from ly_cc
where create_ly_cc_time >= trunc(sysdate) - 40
group by ziliao_id) ly_cc on ly.ziliao_id =
ly_cc.ziliao_id--查找抽查看几次
,ly_ziliao --补上资料状态及变更时间
where ly.create_date > trunc(sysdate) - 40 and ly.status >= 70
and ly.ziliao_id = ly_cc.ziliao_id
and ly.ziliao_id = ly_ziliao.ziliao_id
and ly_ziliao.ziliao_id = ly_cc.ziliao_id
end;这注释语句报错Compilation errors for PROCEDURE KAILIAO.CREATELYRELEASEFORCCError: PLS-00103: 出现符号 "TABLE"在需要下列之一时:
:= . ( @ % ;
符号 ":=在 "TABLE" 继续之前已插入。
Line: 4
Text: truncate table --这里报错Error: PLS-00103: 出现符号 "("在需要下列之一时:
, from
Line: 44
Text: count(ly.ziliao_id) over(partition by ly.ziliao_id) as done_count, --在规定时间内,是否已做过 这里报错
truncate table --这里报错
ly_release_for_cc;
insert /*+ append */
into ly_release_for_cc
(ziliao_id,
project_name,
status_date,
status,
user_name,
telephone,
mobile,
email,
address,
content,
CCCOUNT,
done_count,
var2,
agent_id,
zl_status_date,
owner,
project_code,
company_name,
company_code,
num2,
num1,
zl_status
) select ly.ziliao_id,
ly.project_name,
ly.status_date,
ly.status,
ly.user_name,
ly.telephone,
ly.mobile,
ly.email,
ly.address,
ly.content,
coalesce(cc, 0),
count(ly.ziliao_id) over(partition by ly.ziliao_id) as done_count, --在规定时间内,是否已做过 这里报错
ly.var2 as agentName,
ly.agent_id,
ly_ziliao.status_date,
ly.owner,
ly.project_code,
ly.company_name,
ly.company_code,
ly.num2,
ly.num1,
ly_ziliao.status
from ly_release ly
left outer join (select ziliao_id, count(ziliao_id) as cc
from ly_cc
where create_ly_cc_time >= trunc(sysdate) - 40
group by ziliao_id) ly_cc on ly.ziliao_id =
ly_cc.ziliao_id--查找抽查看几次
,ly_ziliao --补上资料状态及变更时间
where ly.create_date > trunc(sysdate) - 40 and ly.status >= 70
and ly.ziliao_id = ly_cc.ziliao_id
and ly.ziliao_id = ly_ziliao.ziliao_id
and ly_ziliao.ziliao_id = ly_cc.ziliao_id
end;这注释语句报错Compilation errors for PROCEDURE KAILIAO.CREATELYRELEASEFORCCError: PLS-00103: 出现符号 "TABLE"在需要下列之一时:
:= . ( @ % ;
符号 ":=在 "TABLE" 继续之前已插入。
Line: 4
Text: truncate table --这里报错Error: PLS-00103: 出现符号 "("在需要下列之一时:
, from
Line: 44
Text: count(ly.ziliao_id) over(partition by ly.ziliao_id) as done_count, --在规定时间内,是否已做过 这里报错
truncate table ly_release_for_cc;
truncate ly_release_for_cc; --是这样的.
truncate是DDL所以要用动态SQL执行;
execute immediate 'truncate table ly_release_for_cc';
再不行就用Delete了,呵呵.
sqlstr := 'truncate table ly_release_for_cc';
execute immediate sqlstr;
create or replace procedure createLyReleaseForCc isbegin
sqlstr := 'truncate table ly_release_for_cc';
execute immediate sqlstr;
insert /*+ append */
into ly_release_for_cc
(ziliao_id,
project_name,
status_date,
status,
user_name,
telephone,
mobile,
email,
address,
content,
CCCOUNT,
done_count,
var2,
agent_id,
zl_status_date,
owner,
project_code,
company_name,
company_code,
num2,
num1,
zl_status
) select ly.ziliao_id,
ly.project_name,
ly.status_date,
ly.status,
ly.user_name,
ly.telephone,
ly.mobile,
ly.email,
ly.address,
ly.content,
coalesce(cc, 0),
count(ly.ziliao_id) over(partition by ly.ziliao_id) as done_count, --在规定时间内,是否已做过 这里报错
ly.var2 as agentName,
ly.agent_id,
ly_ziliao.status_date,
ly.owner,
ly.project_code,
ly.company_name,
ly.company_code,
ly.num2,
ly.num1,
ly_ziliao.status
from ly_release ly
left outer join (select ziliao_id, count(ziliao_id) as cc
from ly_cc
where create_ly_cc_time >= trunc(sysdate) - 40
group by ziliao_id) ly_cc on ly.ziliao_id =
ly_cc.ziliao_id--查找抽查看几次
,ly_ziliao --补上资料状态及变更时间
where ly.create_date > trunc(sysdate) - 40 and ly.status >= 70
and ly.ziliao_id = ly_cc.ziliao_id
and ly.ziliao_id = ly_ziliao.ziliao_id
and ly_ziliao.ziliao_id = ly_cc.ziliao_id
end;
改成这样了,还报下面的错Compilation errors for PROCEDURE KAILIAO.CREATELYRELEASEFORCCError: PL/SQL: ORA-00933: SQL 命令未正确结束
Line: 66Error: PL/SQL: SQL Statement ignored
Line: 7
Text: insert /*+ append */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> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
Line: 68
where ly.create_date > trunc(sysdate) - 40 and ly.status >= 70
and ly.ziliao_id = ly_cc.ziliao_id
and ly.ziliao_id = ly_ziliao.ziliao_id
and ly_ziliao.ziliao_id = ly_cc.ziliao_id这句结束没加分号
sqlstr := 'truncate table ly_release_for_cc';
execute immediate sqlstr;
insert
into ly_release_for_cc
(ziliao_id,
project_name,
status_date,
status,
user_name,
telephone,
mobile,
email,
address,
content,
CCCOUNT,
done_count,
var2,
agent_id,
zl_status_date,
owner,
project_code,
company_name,
company_code,
num2,
num1,
zl_status
) select ly.ziliao_id,
ly.project_name,
ly.status_date,
ly.status,
ly.user_name,
ly.telephone,
ly.mobile,
ly.email,
ly.address,
ly.content,
coalesce(cc, 0),
count(ly.ziliao_id) over(partition by ly.ziliao_id) as done_count, --在规定时间内,是否已做过 这里报错
ly.var2 as agentName,
ly.agent_id,
ly_ziliao.status_date,
ly.owner,
ly.project_code,
ly.company_name,
ly.company_code,
ly.num2,
ly.num1,
ly_ziliao.status
from ly_release ly
left outer join (select ziliao_id, count(ziliao_id) as cc
from ly_cc
where create_ly_cc_time >= trunc(sysdate) - 40
group by ziliao_id) ly_cc on ly.ziliao_id =
ly_cc.ziliao_id--查找抽查看几次
,ly_ziliao --补上资料状态及变更时间
where ly.create_date > trunc(sysdate) - 40 and ly.status >= 70
and ly.ziliao_id = ly_cc.ziliao_id
and ly.ziliao_id = ly_ziliao.ziliao_id
and ly_ziliao.ziliao_id = ly_cc.ziliao_id;
end; 还报这个错
Compilation errors for PROCEDURE KAILIAO.CREATELYRELEASEFORCCError: PLS-00201: 必须说明标识符 'SQLSTR'
Line: 4
Text: sqlstr := 'truncate table ly_release_for_cc';Error: PL/SQL: Statement ignored
Line: 4
Text: sqlstr := 'truncate table ly_release_for_cc';Error: PLS-00201: 必须说明标识符 'SQLSTR'
Line: 5
Text: execute immediate sqlstr;Error: PL/SQL: Statement ignored
Line: 5
Text: execute immediate sqlstr;
declare sqlstr := 'truncate table ly_release_for_cc';
begin
--sqlstr := 'truncate table ly_release_for_cc';
execute immediate sqlstr;
insert
into ly_release_for_cc
(ziliao_id,
project_name,
status_date,
status,
user_name,
telephone,
mobile,
email,
address,
content,
CCCOUNT,
done_count,
var2,
agent_id,
zl_status_date,
owner,
project_code,
company_name,
company_code,
num2,
num1,
zl_status
) select ly.ziliao_id,
ly.project_name,
ly.status_date,
ly.status,
ly.user_name,
ly.telephone,
ly.mobile,
ly.email,
ly.address,
ly.content,
coalesce(cc, 0),
count(ly.ziliao_id) over(partition by ly.ziliao_id) as done_count, --在规定时间内,是否已做过 这里报错
ly.var2 as agentName,
ly.agent_id,
ly_ziliao.status_date,
ly.owner,
ly.project_code,
ly.company_name,
ly.company_code,
ly.num2,
ly.num1,
ly_ziliao.status
from ly_release ly
left outer join (select ziliao_id, count(ziliao_id) as cc
from ly_cc
where create_ly_cc_time >= trunc(sysdate) - 40
group by ziliao_id) ly_cc on ly.ziliao_id =
ly_cc.ziliao_id--查找抽查看几次
,ly_ziliao --补上资料状态及变更时间
where ly.create_date > trunc(sysdate) - 40 and ly.status >= 70
and ly.ziliao_id = ly_cc.ziliao_id
and ly.ziliao_id = ly_ziliao.ziliao_id
and ly_ziliao.ziliao_id = ly_cc.ziliao_id;
end;
sqlstr varchar2(100);
begin
sqlstr:='truncate table ly_release_for_cc';
execute immediate sqlstr;………………………………
sqlstr varchar2(100); begin
sqlstr := 'truncate table ly_release_for_cc';
execute immediate sqlstr;
insert
into ly_release_for_cc
(ziliao_id,
project_name,
status_date,
status,
user_name,
telephone,
mobile,
email,
address,
content,
CCCOUNT,
done_count,
var2,
agent_id,
zl_status_date,
owner,
project_code,
company_name,
company_code,
num2,
num1,
zl_status
) select ly.ziliao_id,
ly.project_name,
ly.status_date,
ly.status,
ly.user_name,
ly.telephone,
ly.mobile,
ly.email,
ly.address,
ly.content,
coalesce(cc, 0),
count(ly.ziliao_id) over(partition by ly.ziliao_id) as done_count, --在规定时间内,是否已做过 这里报错
ly.var2 as agentName,
ly.agent_id,
ly_ziliao.status_date,
ly.owner,
ly.project_code,
ly.company_name,
ly.company_code,
ly.num2,
ly.num1,
ly_ziliao.status
from ly_release ly
left outer join (select ziliao_id, count(ziliao_id) as cc
from ly_cc
where create_ly_cc_time >= trunc(sysdate) - 40
group by ziliao_id) ly_cc on ly.ziliao_id =
ly_cc.ziliao_id--查找抽查看几次
,ly_ziliao --补上资料状态及变更时间
where ly.create_date > trunc(sysdate) - 40 and ly.status >= 70
and ly.ziliao_id = ly_cc.ziliao_id
and ly.ziliao_id = ly_ziliao.ziliao_id
and ly_ziliao.ziliao_id = ly_cc.ziliao_id;
end;