(department_name in varchar2,email_subject in varchar2) AS
file_location varchar2(20):='MYDIR';
file_name1 varchar2(200);
fhnd utl_file.file_type;
line_buff nvarchar2(30000);
SendorAddress varchar2(30) := 'XXXX@XXXXXX';
RecieverAddress varchar2(2000) :='XXXX@XXXXXX';
EmailServer varchar2(30) :='XXXXXX';
Port number := 25;
conn utl_smtp.connection;
data RAW(20000);
error_string nvarchar2(500);
cursor saledayorder IS
SELECT (a.XF_SALESMAN||'-'||c.xf_Name) as 销售员,
a.XF_STORECODE||'-'||b.xf_name as 店铺,
to_char(a.XF_TXDATE,'yyyy-mm-dd')as 销售日,
SUM(a.XF_NETAMOUNT) as 销售额
FROM espos.XF_TRANSSALESTOTAL a, espos.xf_store b, espos.XF_STAFF c
WHERE to_char(XF_TXDATE,'YYYY-MM-DD') = to_char(trunc(sysdate,'day'),'YYYY-MM-DD')
and a.XF_STORECODE=b.XF_STORECODE and a.XF_SALESMAN=c.xf_staffcode
group by (a.XF_SALESMAN||'-'||c.xf_Name), to_char(a.XF_TXDATE,'yyyy-mm-dd'),a.XF_STORECODE||'-'||b.xf_name
order by sum(a.XF_NETAMOUNT) desc ;
begin
begin
file_name1:='销售人员排行日.csv';
begin
fhnd:=utl_file.fopen(file_location,file_name1,'W');
line_buff:='销售员,店铺,销售日,销售额';
utl_file.put_line(fhnd,line_buff);
for eachitem in saledayorder loop
line_buff:=saledayorder.salesman||','||saledayorder.storename||','||saledayorder.saleday||','||saledayorder.salemoney;
utl_file.put_line(fhnd,line_buff);
end loop;
utl_file.fclose(fhnd);
end;
--send email
begin
PowerMail.smtp_host:= EmailServer;
PowerMail.smtp_port:= Port;
conn:= PowerMail.begin_mail(SendorAddress,RecieverAddress,email_subject,PowerMail.MULTIPART_MIME_TYPE);
PowerMail.attach_text(conn,'Items list of stationary for '||department_name||'.<br>'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'<br>HR System of Some Company','text/html');
PowerMail.begin_attachment(conn,'application/vnd.ms-excel',TRUE,'销售人员排行日.csv','base64');
begin
fhnd:=utl_file.fopen(file_location,file_name1,'r');
loop
utl_file.get_raw(fhnd, data, PowerMail.MAX_BASE64_LINE_WIDTH);
PowerMail.write_raw(conn,utl_encode.base64_encode(data));
end loop;
utl_file.fclose(fhnd);
exception when others then
utl_file.fclose(fhnd);
end;
PowerMail.end_attachment(conn);
utl_file.fremove(file_location,file_name1);
PowerMail.end_mail(conn);
end;
exception when others then
error_string:=SQLERRM;
end;
end;编译出错:行号= 14 列号= 1 错误文本= PL/SQL: SQL Statement ignored
行号= 18 列号= 58 错误文本= PL/SQL: ORA-00942: 表或视图不存在
行号= 31 列号= 34 错误文本= PLS-00225: 子程序或游标 'SALEDAYORDER' 引用超出范围
行号= 31 列号= 10 错误文本= PL/SQL: Statement ignored个人是菜鸟,是在例子上改的, 例子是正常的
file_location varchar2(20):='MYDIR';
file_name1 varchar2(200);
fhnd utl_file.file_type;
line_buff nvarchar2(30000);
SendorAddress varchar2(30) := 'XXXX@XXXXXX';
RecieverAddress varchar2(2000) :='XXXX@XXXXXX';
EmailServer varchar2(30) :='XXXXXX';
Port number := 25;
conn utl_smtp.connection;
data RAW(20000);
error_string nvarchar2(500);
cursor saledayorder IS
SELECT (a.XF_SALESMAN||'-'||c.xf_Name) as 销售员,
a.XF_STORECODE||'-'||b.xf_name as 店铺,
to_char(a.XF_TXDATE,'yyyy-mm-dd')as 销售日,
SUM(a.XF_NETAMOUNT) as 销售额
FROM espos.XF_TRANSSALESTOTAL a, espos.xf_store b, espos.XF_STAFF c
WHERE to_char(XF_TXDATE,'YYYY-MM-DD') = to_char(trunc(sysdate,'day'),'YYYY-MM-DD')
and a.XF_STORECODE=b.XF_STORECODE and a.XF_SALESMAN=c.xf_staffcode
group by (a.XF_SALESMAN||'-'||c.xf_Name), to_char(a.XF_TXDATE,'yyyy-mm-dd'),a.XF_STORECODE||'-'||b.xf_name
order by sum(a.XF_NETAMOUNT) desc ;
begin
begin
file_name1:='销售人员排行日.csv';
begin
fhnd:=utl_file.fopen(file_location,file_name1,'W');
line_buff:='销售员,店铺,销售日,销售额';
utl_file.put_line(fhnd,line_buff);
for eachitem in saledayorder loop
line_buff:=saledayorder.salesman||','||saledayorder.storename||','||saledayorder.saleday||','||saledayorder.salemoney;
utl_file.put_line(fhnd,line_buff);
end loop;
utl_file.fclose(fhnd);
end;
--send email
begin
PowerMail.smtp_host:= EmailServer;
PowerMail.smtp_port:= Port;
conn:= PowerMail.begin_mail(SendorAddress,RecieverAddress,email_subject,PowerMail.MULTIPART_MIME_TYPE);
PowerMail.attach_text(conn,'Items list of stationary for '||department_name||'.<br>'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'<br>HR System of Some Company','text/html');
PowerMail.begin_attachment(conn,'application/vnd.ms-excel',TRUE,'销售人员排行日.csv','base64');
begin
fhnd:=utl_file.fopen(file_location,file_name1,'r');
loop
utl_file.get_raw(fhnd, data, PowerMail.MAX_BASE64_LINE_WIDTH);
PowerMail.write_raw(conn,utl_encode.base64_encode(data));
end loop;
utl_file.fclose(fhnd);
exception when others then
utl_file.fclose(fhnd);
end;
PowerMail.end_attachment(conn);
utl_file.fremove(file_location,file_name1);
PowerMail.end_mail(conn);
end;
exception when others then
error_string:=SQLERRM;
end;
end;编译出错:行号= 14 列号= 1 错误文本= PL/SQL: SQL Statement ignored
行号= 18 列号= 58 错误文本= PL/SQL: ORA-00942: 表或视图不存在
行号= 31 列号= 34 错误文本= PLS-00225: 子程序或游标 'SALEDAYORDER' 引用超出范围
行号= 31 列号= 10 错误文本= PL/SQL: Statement ignored个人是菜鸟,是在例子上改的, 例子是正常的
解决方案 »
- 求sql这样如何加过滤条件
- Oracle 查询一个表中的最后一列数据
- 用Net8 Configuration Assistant配置监听程序时的提示。
- 这个SQL语句怎么写呀?
- oracle里如何区分date型的字段究竟是精确到了年月日,还是精确到了小时分?
- 急,请帮忙!怎么把sql数据倒入oracle中?
- 大量数据插入数据库中的问题??
- 关于其他数据库里的备注型字段,在oracle里应该用哪个?
- 数据库连接访问远程数据库怎样才能选中特定的字段??oracle里日期怎么用SQL语句进行筛选啊
- 求ORACLE中用PLSQL写的大小写金额转换的后台函数!!!
- Oracle中如何查询存储过程的调用记录?
- 初学oracle,请教大家一些入门级的问题。
有这些表?
这段是如下,开始贴错了
line_buff:=saledayorder.销售员||','||saledayorder.店铺||','||saledayorder.销售日||','||saledayorder.销售额;
"XF_TILLID" VARCHAR2(3 byte) NOT NULL,
"XF_TXDATE" DATE NOT NULL,
"XF_TXTIME" VARCHAR2(6 byte) NOT NULL,
"XF_TXSERIAL" NUMBER NOT NULL,
"XF_TXBATCH" NUMBER NOT NULL,
"XF_DOCNO" VARCHAR2(10 byte) NOT NULL,
"XF_VOIDDOCNO" VARCHAR2(10 byte),
"XF_TXTYPE" NUMBER NOT NULL,
"XF_TXHOUR" NUMBER NOT NULL,
"XF_CASHIER" VARCHAR2(10 byte),
"XF_SALESMAN" VARCHAR2(10 byte),
"XF_CLIENTCODE" VARCHAR2(12 byte),
"XF_PURCHASESTAFFCODE" VARCHAR2(10 byte),
"XF_PURCHASEDEPENDENT" NUMBER,
"XF_DEMOGRAPHICCODE" VARCHAR2(4 byte),
"XF_DEMOGRAPHICDATA" VARCHAR2(4 byte),
"XF_NETQTY" NUMBER(16, 4) NOT NULL,
"XF_ORIGINALAMOUNT" NUMBER(16, 4),
"XF_SELLINGAMOUNT" NUMBER(16, 4),
"XF_DISCOUNTAPPROVE" VARCHAR2(10 byte),
"XF_DISCOUNTAMOUNT" NUMBER(16, 4),
"XF_TTLTAXAMOUNT1" NUMBER(16, 4),
"XF_TTLTAXAMOUNT2" NUMBER(16, 4),
"XF_NETAMOUNT" NUMBER(16, 4) NOT NULL,
"XF_PAIDAMOUNT" NUMBER(16, 4),
"XF_CHANGEAMOUNT" NUMBER(16, 4),
"XF_DEFAULTTENDER" VARCHAR2(2 byte),
"XF_NUMOFITEM" NUMBER NOT NULL,
"XF_NUMOFTENDER" NUMBER NOT NULL,
"XF_PRICEINCLUDETAX" VARCHAR2(2 byte),
"XF_SHOPTAXGROUP" VARCHAR2(40 byte),
"XF_EXTENDPARAM" VARCHAR2(128 byte),
"XF_DESTLOCATIONLIST" VARCHAR2(250 byte),
"XF_POSTDATE" DATE NOT NULL,
"XF_CREATETIME" DATE NOT NULL)
TABLESPACE "DATA01" PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
--------------------------------------------------------------------------
CREATE TABLE "ESPOS"."" ("XF_STAFFCODE" VARCHAR2(10 byte) NOT NULL,
"XF_NAME" VARCHAR2(35 byte),
"XF_PASSWORD" VARCHAR2(10 byte),
"XF_PRIVID" VARCHAR2(2 byte),
"XF_USERGROUP" VARCHAR2(2 byte),
"XF_ADDRESS1" VARCHAR2(50 byte),
"XF_ADDRESS2" VARCHAR2(50 byte),
"XF_ADDRESS3" VARCHAR2(50 byte), "XF_ADDRESS4" VARCHAR2(50 byte), "XF_TELEPHONE" VARCHAR2(20 byte),
"XF_TELEPHONE2" VARCHAR2(20 byte), "XF_SEX" VARCHAR2(1 byte),
"XF_MARSTATUS" VARCHAR2(1 byte),
"XF_IDNO" VARCHAR2(20 byte), "XF_EMPDATE" DATE, "XF_CESSDATE"
DATE, "XF_STAFFTYPE" VARCHAR2(1 byte), "XF_STAFFEMAIL"
VARCHAR2(60 byte), "XF_REMARKS" VARCHAR2(100 byte),
"XF_STAFFGRADE" VARCHAR2(2 byte), "XF_DISCPER" NUMBER,
"XF_DLFLAG" VARCHAR2(1 byte) DEFAULT '1', "XF_LASTMODBY"
VARCHAR2(10 byte), "XF_LASTMODTIME" DATE, "XF_LASTUPLOADTIME"
DATE, "XF_ISSUESTORE" VARCHAR2(6 byte), "XF_ISSUEBY"
VARCHAR2(10 byte), "XF_ISSUEDATE" DATE, "XF_ISSUETIME"
VARCHAR2(6 byte), "XF_GROUPID0" VARCHAR2(25 byte),
"XF_GROUPID1" VARCHAR2(25 byte), "XF_GROUPID2" VARCHAR2(25
byte), "XF_GROUPID3" VARCHAR2(25 byte), "XF_GROUPID4"
VARCHAR2(25 byte), "XF_GROUPID5" VARCHAR2(25 byte),
"XF_GROUPID6" VARCHAR2(25 byte), "XF_GROUPID7" VARCHAR2(25
byte), "XF_GROUPID8" VARCHAR2(25 byte), "XF_DBUSER"
VARCHAR2(25 byte), "XF_CUSTCODE" VARCHAR2(10 byte),
CONSTRAINT "SYS_C0016677_1" PRIMARY KEY("XF_STAFFCODE")
USING INDEX
TABLESPACE "DATA01"
STORAGE ( INITIAL 512K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255)
TABLESPACE "DATA01" PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 512K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
------------------------CREATE TABLE "ESPOS"."" ("XF_STORECODE" VARCHAR2(6 byte) NOT NULL,
"XF_NAME" VARCHAR2(30 byte) NOT NULL, "XF_PROFILEID"
VARCHAR2(3 byte) NOT NULL, "XF_COUNTRY" VARCHAR2(1 byte) NOT
NULL, "XF_ADDRESS1" VARCHAR2(50 byte) NOT NULL, "XF_ADDRESS2"
VARCHAR2(50 byte), "XF_ADDRESS3" VARCHAR2(50 byte),
"XF_ADDRESS4" VARCHAR2(50 byte), "XF_TELEPHONE" VARCHAR2(20
byte), "XF_FAX" VARCHAR2(20 byte), "XF_EMAILSEND" VARCHAR2(60
byte), "XF_EMAILRECE" VARCHAR2(60 byte), "XF_TYPE" VARCHAR2(1
byte) NOT NULL, "XF_NUMTILL" NUMBER NOT NULL, "XF_WAREHOUSE"
VARCHAR2(6 byte), "XF_ACTIVE" VARCHAR2(1 byte) NOT NULL,
"XF_RENTAL" NUMBER(16, 4), "XF_SHOPAREA" NUMBER,
"XF_COMMDATE" DATE, "XF_DEMOGPROMPT" VARCHAR2(4 byte),
"XF_MINDEMOG" VARCHAR2(4 byte), "XF_MAXDEMOG" VARCHAR2(4
byte), "XF_DEFCASHF" NUMBER(16, 4), "XF_SPECREQUEST"
VARCHAR2(1 byte) NOT NULL, "XF_REPLSEQ" NUMBER NOT NULL,
"XF_REPLBAT" VARCHAR2(4 byte) NOT NULL, "XF_ALLOTYPE"
VARCHAR2(1 byte), "XF_RETAILCHAIN" VARCHAR2(1 byte) NOT NULL,
"XF_DISTRICTCODE" VARCHAR2(6 byte), "XF_SHOPGROUP" VARCHAR2(6
byte), "XF_ADCODE" VARCHAR2(10 byte), "XF_ZONEID" VARCHAR2(2
byte), "XF_NUMOFCONNECTION" NUMBER DEFAULT 0, "XF_ATTRIBUTE1"
VARCHAR2(80 byte), "XF_ATTRIBUTE2" VARCHAR2(80 byte),
"XF_ATTRIBUTE3" VARCHAR2(80 byte), "XF_ATTRIBUTE4"
VARCHAR2(80 byte), "XF_LASTMODBY" VARCHAR2(10 byte) NOT NULL,
"XF_LASTMODDATE" DATE NOT NULL, "XF_STORESERIALID" VARCHAR2(4
byte) NOT NULL, "XF_COSTCENTER" VARCHAR2(6 byte),
"XF_LOCATIONSEGMENT" VARCHAR2(50 byte), "XF_PRICECENTER"
VARCHAR2(6 byte), "XF_SHOPTAXGROUPCODE" VARCHAR2(6 byte),
"XF_ITEMBRAND" VARCHAR2(25 byte), "XF_ITEMSERIES" VARCHAR2(25
byte), "XF_DEPTCODE" VARCHAR2(10 byte),
CONSTRAINT "SYS_C0016857_1" PRIMARY KEY("XF_STORECODE")
USING INDEX
TABLESPACE "DATA01"
STORAGE ( INITIAL 512K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255)
TABLESPACE "DATA01" PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 512K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
XF_TRANSSALESTOTAL
XF_STAFF
xf_store
line_buff:=eachitem.销售员||','||eachitem.店铺||','||eachitem.销售日||','||eachitem.销售额;
应该用eachitem代替saledayorder
行号= 14 列号= 1 错误文本= PL/SQL: SQL Statement ignored
行号= 18 列号= 58 错误文本= PL/SQL: ORA-00942: 表或视图不存在
行号= 32 列号= 21 错误文本= PLS-00364: 循环索引变量 'EACHITEM' 的使用无效
行号= 32 列号= 10 错误文本= PL/SQL: Statement ignored