create or replace procedure createLyReleaseForCc is 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;给你个例子
create or replace procedure test is sql varchar2(100); begin --方法1 insert into......; --方法2 sql:='insert int ....'; execute immediate sql; commit;exception when others then rollback; end test ;
create or replace procedure test_proc is v_date date; --date v_char varchar2(10); --char v_number number; --numberbegin v_date := sysdate; v_char := 'test'; v_number := 123; insert into table values(v_date,v_char,v_number);commit;exception when others then rollback; end;
或者是动态sql执行execute immediate sqlstr using param1, param2, ...
create or replace procedure createLyReleaseForCc is
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;给你个例子
sql varchar2(100); begin
--方法1
insert into......;
--方法2
sql:='insert int ....';
execute immediate sql;
commit;exception when others
then
rollback;
end test ;
v_date date; --date
v_char varchar2(10); --char
v_number number; --numberbegin
v_date := sysdate;
v_char := 'test';
v_number := 123;
insert into table values(v_date,v_char,v_number);commit;exception
when others then
rollback;
end;
或者是动态sql执行execute immediate sqlstr using param1, param2, ...