在编译的时候提示为:
(1):PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
(1):
(1): ; <an identifier> <a double-quoted delimited-identifier>
(1): delete exists prior <a single-quoted SQL string>
(1):The symbol ";" was substituted for "end-of-file" to continue.
请大家帮看看,谢谢```急``马上接帖``
create or replace procedure trm_sale_info(
start_date in varchar2, --开始时间
end_date in varchar2 --结束时间
)is
cursor cur_c_num is
SELECT emp_no,count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>=start_date and send_date<=end_date
GROUP BY emp_no;
cursor cur_c_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>='20050701' and send_date<=end_date
GROUP BY emp_no;
cursor cur_g_num is
SELECT emp_no,count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>=start_date and send_date<=end_date
GROUP BY emp_no;
cursor cur_g_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>='20050701' and send_date<=end_date
GROUP BY emp_no;
cursor cur_c_sale is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>=start_date and send_date<=end_date
and sale_type in (select sale_type from tcomm_mid_type)
GROUP BY emp_no;
cursor cur_sale_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>='20050701' and send_date<=end_date
and sale_type in (select sale_type from tcomm_mid_type)
GROUP BY emp_no;
cursor cur_c_dinner is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>=start_date and send_date<=end_date
and dinner in (select dinner from tcomm_mid_type)
GROUP BY emp_no;
cursor cur_dinner_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>='20050701' and send_date<=end_date
and dinner in (select dinner from tcomm_mid_type)
GROUP BY emp_no;
BEGIN
DELETE FROM trm_sale_dtl;
INSERT INTO tcr_simcard_info
(
emp_no, --varchar2(7),
emp_name, --varchar2(20),
emp_type, --varchar2(12),
office_name, --varchar2(20),
area_name, --varchar2(10),
Region_name, --varchar2(10),
SEND_DATE, --VARCHAR2(8),
PHONE_ID, --VARCHAR2(12),
SALE_TYPE, --VARCHAR2(4),
DINNER_NO, --VARCHAR2(16),
CALL_DATE, --VARCHAR2(14),
FIRST_CALL_DATE, --VARCHAR2(8),
start_date, --VARCHAR2(8),
end_date --VARCHAR2(8)
)
SELECT
a.emp_no,
b.emp_name,
decode(b.emp_type,'1','员工','客户经理'),
e.office_name,
f.area_name,
g.region_name,
a.send_date,
a.phone_id,
c.sale_type,
c.dinner_no,
c.call_date,
c.first_call_date,
start_date,
end_date
FROM tcr_emp_develop a,tei_employee_info b,tcc_user_info c,tcomm_office_info e,tcomm_area_info f,tcomm_region_info g
WHERE a.emp_no=b.emp_no and a.user_id=c.user_id and a.oper_type=c.oper_type
and b.office_no=e.office_no(+) and b.area_no=f.area_no(+)
and b.region_no=g.region_no(+);
COMMIT;
DELETE FROM trm_sale_count;
INSERT INTO trm_sale_count
(
emp_no, --varchar2(7),
emp_name, --varchar2(20),
emp_type, --varchar2(12),
office_name, --varchar2(20),
area_name, --varchar2(10),
Region_name, --varchar2(10),
start_date, --VARCHAR2(8),
end_date --VARCHAR2(8)
)
SELECT
distinct emp_no,
emp_name,
emp_type,
office_name,
area_name,
Region_name,
start_date,
end_date
FROM trm_sale_dtl;
COMMIT;
UPDATE trm_sale_count set
c_num=cur_c_num.numm,
c_all=cur_c_all.numm,
g_num=cur_g_num.numm,
g_all=cur_g_all.numm,
c_sale_num=cur_c_sale.numm,
c_sale_all=cur_sale_all.numm,
c_dinner_num=cur_c_dinner.numm,
c_dinner_all=cur_dinner_all.numm
WHERE emp_no=cur_c_num.emp_no and emp_no=cur_c_all.emp_no
and emp_no=cur_g_num.emp_no and amp_no=cur_g_all.emp_no
and emp_no=cur_c_sale.emp_no and emp_no=cur_sale_all.emp_no
and emp_no=cur_c_dinner.emp_no and emp_no=cur_dinner_all.emp_no;
COMMIT;
END
(1):PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
(1):
(1): ; <an identifier> <a double-quoted delimited-identifier>
(1): delete exists prior <a single-quoted SQL string>
(1):The symbol ";" was substituted for "end-of-file" to continue.
请大家帮看看,谢谢```急``马上接帖``
create or replace procedure trm_sale_info(
start_date in varchar2, --开始时间
end_date in varchar2 --结束时间
)is
cursor cur_c_num is
SELECT emp_no,count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>=start_date and send_date<=end_date
GROUP BY emp_no;
cursor cur_c_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>='20050701' and send_date<=end_date
GROUP BY emp_no;
cursor cur_g_num is
SELECT emp_no,count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>=start_date and send_date<=end_date
GROUP BY emp_no;
cursor cur_g_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>='20050701' and send_date<=end_date
GROUP BY emp_no;
cursor cur_c_sale is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>=start_date and send_date<=end_date
and sale_type in (select sale_type from tcomm_mid_type)
GROUP BY emp_no;
cursor cur_sale_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>='20050701' and send_date<=end_date
and sale_type in (select sale_type from tcomm_mid_type)
GROUP BY emp_no;
cursor cur_c_dinner is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>=start_date and send_date<=end_date
and dinner in (select dinner from tcomm_mid_type)
GROUP BY emp_no;
cursor cur_dinner_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>='20050701' and send_date<=end_date
and dinner in (select dinner from tcomm_mid_type)
GROUP BY emp_no;
BEGIN
DELETE FROM trm_sale_dtl;
INSERT INTO tcr_simcard_info
(
emp_no, --varchar2(7),
emp_name, --varchar2(20),
emp_type, --varchar2(12),
office_name, --varchar2(20),
area_name, --varchar2(10),
Region_name, --varchar2(10),
SEND_DATE, --VARCHAR2(8),
PHONE_ID, --VARCHAR2(12),
SALE_TYPE, --VARCHAR2(4),
DINNER_NO, --VARCHAR2(16),
CALL_DATE, --VARCHAR2(14),
FIRST_CALL_DATE, --VARCHAR2(8),
start_date, --VARCHAR2(8),
end_date --VARCHAR2(8)
)
SELECT
a.emp_no,
b.emp_name,
decode(b.emp_type,'1','员工','客户经理'),
e.office_name,
f.area_name,
g.region_name,
a.send_date,
a.phone_id,
c.sale_type,
c.dinner_no,
c.call_date,
c.first_call_date,
start_date,
end_date
FROM tcr_emp_develop a,tei_employee_info b,tcc_user_info c,tcomm_office_info e,tcomm_area_info f,tcomm_region_info g
WHERE a.emp_no=b.emp_no and a.user_id=c.user_id and a.oper_type=c.oper_type
and b.office_no=e.office_no(+) and b.area_no=f.area_no(+)
and b.region_no=g.region_no(+);
COMMIT;
DELETE FROM trm_sale_count;
INSERT INTO trm_sale_count
(
emp_no, --varchar2(7),
emp_name, --varchar2(20),
emp_type, --varchar2(12),
office_name, --varchar2(20),
area_name, --varchar2(10),
Region_name, --varchar2(10),
start_date, --VARCHAR2(8),
end_date --VARCHAR2(8)
)
SELECT
distinct emp_no,
emp_name,
emp_type,
office_name,
area_name,
Region_name,
start_date,
end_date
FROM trm_sale_dtl;
COMMIT;
UPDATE trm_sale_count set
c_num=cur_c_num.numm,
c_all=cur_c_all.numm,
g_num=cur_g_num.numm,
g_all=cur_g_all.numm,
c_sale_num=cur_c_sale.numm,
c_sale_all=cur_sale_all.numm,
c_dinner_num=cur_c_dinner.numm,
c_dinner_all=cur_dinner_all.numm
WHERE emp_no=cur_c_num.emp_no and emp_no=cur_c_all.emp_no
and emp_no=cur_g_num.emp_no and amp_no=cur_g_all.emp_no
and emp_no=cur_c_sale.emp_no and emp_no=cur_sale_all.emp_no
and emp_no=cur_c_dinner.emp_no and emp_no=cur_dinner_all.emp_no;
COMMIT;
END
create or replace procedure trm_sale_info(
start_date in varchar2, --开始时间
end_date in varchar2 --结束时间
)is
cursor cur_c_num is
SELECT emp_no,count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>=start_date and send_date<=end_date
GROUP BY emp_no;
cursor cur_c_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>='20050701' and send_date<=end_date
GROUP BY emp_no;
cursor cur_g_num is
SELECT emp_no,count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>=start_date and send_date<=end_date
GROUP BY emp_no;
cursor cur_g_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>='20050701' and send_date<=end_date
GROUP BY emp_no;
cursor cur_c_sale is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>=start_date and send_date<=end_date
and sale_type in (select sale_type from tcomm_mid_type)
GROUP BY emp_no;
cursor cur_sale_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>='20050701' and send_date<=end_date
and sale_type in (select sale_type from tcomm_mid_type)
GROUP BY emp_no;
cursor cur_c_dinner is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>=start_date and send_date<=end_date
and dinner in (select dinner from tcomm_mid_type)
GROUP BY emp_no;
cursor cur_dinner_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>='20050701' and send_date<=end_date
and dinner in (select dinner from tcomm_mid_type)
GROUP BY emp_no;
BEGIN
DELETE FROM trm_sale_dtl;
INSERT INTO tcr_simcard_info
(
emp_no, --varchar2(7),
emp_name, --varchar2(20),
emp_type, --varchar2(12),
office_name, --varchar2(20),
area_name, --varchar2(10),
Region_name, --varchar2(10),
SEND_DATE, --VARCHAR2(8),
PHONE_ID, --VARCHAR2(12),
SALE_TYPE, --VARCHAR2(4),
DINNER_NO, --VARCHAR2(16),
CALL_DATE, --VARCHAR2(14),
FIRST_CALL_DATE, --VARCHAR2(8),
start_date, --VARCHAR2(8),
end_date --VARCHAR2(8)
);
SELECT
a.emp_no,
b.emp_name,
decode(b.emp_type,'1','员工','客户经理'),
e.office_name,
f.area_name,
g.region_name,
a.send_date,
a.phone_id,
c.sale_type,
c.dinner_no,
c.call_date,
c.first_call_date,
start_date,
end_date
FROM tcr_emp_develop a,tei_employee_info b,tcc_user_info c,tcomm_office_info e,tcomm_area_info f,tcomm_region_info g
WHERE a.emp_no=b.emp_no and a.user_id=c.user_id and a.oper_type=c.oper_type
and b.office_no=e.office_no(+) and b.area_no=f.area_no(+)
and b.region_no=g.region_no(+);
COMMIT;
DELETE FROM trm_sale_count;
INSERT INTO trm_sale_count
(
emp_no, --varchar2(7),
emp_name, --varchar2(20),
emp_type, --varchar2(12),
office_name, --varchar2(20),
area_name, --varchar2(10),
Region_name, --varchar2(10),
start_date, --VARCHAR2(8),
end_date --VARCHAR2(8)
);
SELECT
distinct emp_no,
emp_name,
emp_type,
office_name,
area_name,
Region_name,
start_date,
end_date
FROM trm_sale_dtl;
COMMIT;
UPDATE trm_sale_count set
c_num=cur_c_num.numm,
c_all=cur_c_all.numm,
g_num=cur_g_num.numm,
g_all=cur_g_all.numm,
c_sale_num=cur_c_sale.numm,
c_sale_all=cur_sale_all.numm,
c_dinner_num=cur_c_dinner.numm,
c_dinner_all=cur_dinner_all.numm
WHERE emp_no=cur_c_num.emp_no and emp_no=cur_c_all.emp_no
and emp_no=cur_g_num.emp_no and amp_no=cur_g_all.emp_no
and emp_no=cur_c_sale.emp_no and emp_no=cur_sale_all.emp_no
and emp_no=cur_c_dinner.emp_no and emp_no=cur_dinner_all.emp_no;
COMMIT;
END trm_sale_info;
(1):PLS-00201: identifier 'DINNER' must be declared
(2):PL/SQL: SQL Statement ignored
(3):PLS-00201: identifier 'DINNER' must be declared
(4):PL/SQL: SQL Statement ignored
(5):PLS-00390: undefined column 'EMP_NAME' in INSERT statement
(6):PL/SQL: SQL Statement ignored
(7):PLS-00225: subprogram or cursor 'CUR_C_NUM' reference is out of scope
(8):PL/SQL: SQL Statement ignored
请再帮我看看。分不够,可以加的。
start_date in varchar2, --开始时间
end_date in varchar2 --结束时间
)is
cursor cur_c_num is
SELECT emp_no,count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>=start_date and send_date<=end_date
GROUP BY emp_no;
cursor cur_c_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>='20050701' and send_date<=end_date
GROUP BY emp_no;
cursor cur_g_num is
SELECT emp_no,count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>=start_date and send_date<=end_date
GROUP BY emp_no;
cursor cur_g_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>='20050701' and send_date<=end_date
GROUP BY emp_no;
cursor cur_c_sale is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>=start_date and send_date<=end_date
and sale_type in (select sale_type from tcomm_mid_type)
GROUP BY emp_no;
cursor cur_sale_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='C' and send_date>='20050701' and send_date<=end_date
and sale_type in (select sale_type from tcomm_mid_type)
GROUP BY emp_no;
cursor cur_c_dinner is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>=start_date and send_date<=end_date
and dinner in (select dinner from tcomm_mid_type)
GROUP BY emp_no;
cursor cur_dinner_all is
SELECT emp_no, count(*) numm
FROM trm_sale_dtl
WHERE oper_type='G' and send_date>='20050701' and send_date<=end_date
and dinner in (select dinner from tcomm_mid_type)
GROUP BY emp_no;
BEGIN
DELETE FROM trm_sale_dtl;
INSERT INTO tcr_simcard_info
(
emp_no, --varchar2(7), 员工号码
emp_name, --varchar2(20), 员工名称
emp_type, --varchar2(12), 员工类别(1:员工 2:客户经理)
office_name, --varchar2(20), 部门
area_name, --varchar2(10), 片区
Region_name, --varchar2(10), 分公司
SEND_DATE, --VARCHAR2(8), 在直销系统领卡时间
PHONE_ID, --VARCHAR2(12), 手机号码
SALE_TYPE, --VARCHAR2(4), 销售模式
DINNER_NO, --VARCHAR2(16), 自费套餐
CALL_DATE, --VARCHAR2(14), 返档时间
FIRST_CALL_DATE, --VARCHAR2(8), 首次通话时间
start_date, --VARCHAR2(8), 起始时间
end_date --VARCHAR2(8) 结束时间
)
VALUES(
a.emp_no,
b.emp_name,
decode(b.emp_type,'1','员工','客户经理'),
e.office_name,
f.area_name,
g.region_name,
a.send_date,
a.phone_id,
c.sale_type,
c.dinner_no,
c.call_date,
c.first_call_date,
start_date,
end_date
)
FROM tcr_emp_develop a,tei_employee_info b,tcc_user_info c,tcomm_office_info e,tcomm_area_info f,tcomm_region_info g
WHERE a.emp_no=b.emp_no and a.user_id=c.user_id and a.oper_type=c.oper_type
and b.office_no=e.office_no(+) and b.area_no=f.area_no(+)
and b.region_no=g.region_no(+);
COMMIT;
DELETE FROM trm_sale_count;
INSERT INTO trm_sale_count
(
emp_no, --varchar2(7), --员工号码
emp_name, --varchar2(20), --员工名称
emp_type, --varchar2(12), --员工类别(1:员工 2:客户经理)
office_name, --varchar2(20), --部门
area_name, --varchar2(10), --片区
Region_name, --varchar2(10), --分公司
start_date, --VARCHAR2(8), --起始时间
end_date --VARCHAR2(8) --结束时间
)
VALUES(
distinct emp_no,
emp_name,
emp_type,
office_name,
area_name,
Region_name,
start_date,
end_date
)
FROM trm_sale_dtl;
COMMIT;
UPDATE trm_sale_count set
c_num=cur_c_num.numm,
c_all=cur_c_all.numm,
g_num=cur_g_num.numm,
g_all=cur_g_all.numm,
c_sale_num=cur_c_sale.numm,
c_sale_all=cur_sale_all.numm,
c_dinner_num=cur_c_dinner.numm,
c_dinner_all=cur_dinner_all.numm
WHERE emp_no=cur_c_num.emp_no and emp_no=cur_c_all.emp_no
and emp_no=cur_g_num.emp_no and amp_no=cur_g_all.emp_no
and emp_no=cur_c_sale.emp_no and emp_no=cur_sale_all.emp_no
and emp_no=cur_c_dinner.emp_no and emp_no=cur_dinner_all.emp_no;
COMMIT;
END;
现在前面从游标里查出来放到变量里,然后再写insert,这样清晰些。