第一个oracle存储过程,我的第一个Oracle存储过程

这个存储过程的用意是从数据库表中检索，将检索结果按照指定格式写成XML文件，上传到服务器。

库表名称：WL_AL_OPERATOR_NUMSECTION

XML文件名称：ECID_OrdSub_YYYYMMDDHHMISS_BBOSS.NNN

*

其中NNN是文件序列号，从000至999。每天EC发送的文件以000开始，如果当天EC发送给BBOSS的文件数大于999，则从000重新开始

* 文件大小不能超过5M

XML 格式定义：

序号

父元素名称

元素名称

约束

类型

长度

描述

取值说明

1

InterBOSS

UserInfo

1

－

－

用户信息

1.1

UserInfo

BizCode

1

String

V10

业务代码

1.2

UserInfo

ProdName

?

String

V256

业务名称

1.3

UserInfo

ServCode

1

String

V21

服务代码

1.4

UserInfo

UserData

+

－

－

签约关系、黑白名单信息

1.4.1

UserData

MobNum

1

String

F11

用户手机号码

1.4.2

UserData

OprCode

1

String

F2

操作代码

01－加入

02－退出

1.4.3

UserData

EfftT

?

String

F8

用户希望的生效时间

YYYYMMDD

我的代码：

-- 存储过程名为 TEST_ZYP

create or replace procedure TEST_ZYP(in_mdns in varchar,out_retval

out number) is

-- PL_SQL程序块由3部分组成 声明部分 执行部分异常处理

--声明部分由关键字DECLARE开始(这里为什么没有DECLARE?)

--如果在这里加上

DECLARE ，其实是错的，因为上面的

is 就相当于declare

--定义参数：参数名称

参数类型

l_errcode

number; --oracle中对int double float进行整合成number类型

l_errmessage

varchar2(1000); --varchar2 长度可变最大4000个字节

--l_id

number;

--变量意义：ID号

l_mobile

varchar(100); --varchar

长度不可变最大2000个字节

--变量意义：l_mobile 手机号

--l_name

varchar(100);

l_oprcode

varchar2(10); --变量意义：操作代码

f_obj

utl_file.file_type; --utl_file：

oracle内置包提供一系列的文件操作

--utl_file.file_type 文件类型

--l_line

varchar2(1000);

l_filename

varchar2(200); --变量意义：组装的文件名称

l_conn

UTL_TCP.connection; --变量意义：FTP上传的链接

--UTL_TCP：

Oracle 内置包 使用UTL_TCP包通过TCP/IP协议来访问Internet资源，

--包括大家最熟悉的HTTP协议，传输文件(FTP)，甚至发送邮件(SMTP)，

l_ecid

varchar(10);--ECID

本企业标识

l_maxnum

number;--每个文件记录的手机号最大个数(因为要求每个文件不能超过 5M)

l_recycle

number;--循环记录编号

l_filenum

number;--文件编号(因为有最大文件容量,所以要求每天的文件后缀按000-999顺序循环生成)

l_curdate

varchar(10);--处理时的日期YYYYMMDD

l_curdatetime

varchar(20);--处理时的日期时间

YYYYMMDDhhmmss

l_bizcode

varchar2(50);--业务代码

l_prodname

varchar2(50);--业务名称

l_servcode

varchar2(50);--服务代码

--CURSOR

声明游标(后续动作为打开提取数据关闭)

CURSOR c_sms_main IS select a.id,a.mobile,a.name

from wl_al_linkren a inner join WL_AL_OPERATOR_NUMSECTION b on

substr(a.mobile,0,3)=b.mdnsect

where b.operatorid=0 and a.ftpflag=0;

c_sms_row c_sms_main % ROWTYPE;

-- % ROWTYPE：复合类型。声明变量c_sms_row可以存放游标c_sms_main一整行

--执行部分由BEGIN开始

BEGIN

--变量赋值

l_ecid := '400097';

l_maxnum := 10000;

l_bizcode := 'MBJ0010801';

l_prodname := '国家农业部信息中心';

l_servcode :='1065795157';

l_recycle:=0; --循环记录编号，用来判断是否该结束当前文件(超过指定的l_maxnum)，组装新文件了

l_filenum :=-1; --文件编号000-999

--判断in_mdns，如有数据，写成一个文件，并上传到服务器。

IF in_mdns IS NOT NULL THEN --如果in_mdns不为空

BEGIN

--处理

l_ecid := '400097';

END;

END IF;

--开始处理数据库中的数据

OPEN c_sms_main; --打开游标

LOOP --直到型循环(LOOP---- EXIT [WHEN 条件] ----END LOOP)

FETCH c_sms_main INTO c_sms_row;--循环开始读取记录

FETCH INTO

将游标内容读入c_sms_main%ROWTYPE类型变量中

EXIT WHEN c_sms_main%NOTFOUND;--循环到达最后退出

%NOTFOUND 为游标4属性之一

---*************开始正儿八经的存储过程的逻辑编码**********-----

--根据ID更新数据库ftpflag状态

update wl_al_linkren set ftpflag=1 where

id=c_sms_row.id;

l_recycle := l_recycle+1;--计数器加1

IF 1=MOD(l_recycle,l_maxnum) THEN --计数器对l_maxnum取余为1

BEGIN

IF l_recycle>1 THEN --计数器大于1并且对l_maxnum取余为1,代表着一个文件已经满了

BEGIN

--把刚写满的这个文件结尾

utl_file.put(f_obj,'');

utl_file.put(f_obj,'');

--关闭文件

utl_file.fclose(f_obj);

--把这个文件上传

l_conn := ftp.login('192.168.88.54', '21', 'administrator',

'830528');

ftp.binary(p_conn => l_conn);

ftp.put(p_conn

=> l_conn,

p_from_dir

=> 'FTPDIR',

p_from_file => l_filename,

p_to_file

=> l_filename);

ftp.logout(l_conn);

END;

END IF;

--需要弄一个新文件

--新文件名组装

l_curdatetime := TO_CHAR(SYSDATE(),'YYYYMMDDHHMMSS');

l_filename :=l_ecid||'_OrdSub_';

l_filename :=l_filename||l_curdatetime||'_BBOSS.';

l_filenum := l_filenum+1;

IF

l_filenum=1000 THEN

BEGIN

l_filenum:=0;

END;

END IF;

l_filename :=l_filename||l_filenum;

--打开这个新文件

f_obj:=utl_file.fopen('FTPDIR',l_filename,'w');

--给新文件写head

utl_file.put(f_obj,'');

utl_file.put(f_obj,'');

utl_file.put(f_obj,''||l_bizcode||'');

utl_file.put(f_obj,''||l_prodname||'');

utl_file.put(f_obj,''||l_servcode||'');

END;

END IF;

--若不用创建新文件，就直接往f_obj中写数据

--utl_file.put_line(f_obj,l_line);

put_line：输出一行和一个行终止符

utl_file.put(f_obj,'');--put

：在当前行输出不包含终止符

utl_file.new_line(f_obj);--new_line:不指定行数时默认为1，既换行

--MobNum 用户手机号

l_mobile:=c_sms_row.mobile;

utl_file.put(f_obj,''||l_mobile||'');

utl_file.new_line(f_obj);

--OprCode

操作代码

l_oprcode := '01';

utl_file.put(f_obj,''||l_oprcode||'');

utl_file.new_line(f_obj);

--EfftT

用户希望的生效时间

l_curdate := TO_CHAR(SYSDATE(),'YYYYMMDD');

utl_file.put(f_obj,''||l_curdate||'');

utl_file.new_line(f_obj);

utl_file.put(f_obj,'');

END LOOP;

--如有记录，关闭文件对象并上传该文件

IF (l_recycle > 0) AND

(MOD(l_recycle,l_maxnum)>0) THEN

BEGIN

--做结尾

utl_file.put(f_obj,'');

utl_file.put(f_obj,'');

--关闭文件

utl_file.fclose(f_obj);

--上传文件

--连接FTP服务器上传文件到指定目录。

l_conn := ftp.login('192.168.88.54', '21', 'administrator',

'830528');

ftp.binary(p_conn

=> l_conn);

ftp.put(p_conn

=> l_conn,

p_from_dir

=> 'FTPDIR',

p_from_file => l_filename,

p_to_file

=> l_filename);

ftp.logout(l_conn);

END;

END IF;

utl_tcp.close_all_connections;

--结束，关闭游标

CLOSE c_sms_main;

------**********结束该存储过程的逻辑编码*************----

commit;--提交提交数据(这里是释放锁不是锁表)，在未提交前你前面的操作更新的都是内存，没有更新到物理文件中。

out_retval := 0; --给输出赋值

--异常处理部分由EXCEPTION开始

exception

when others then

rollback;

l_errcode

:= sqlcode;

l_errmessage := sqlerrm;

insert into wl_al_exec_proc_log(id,

procname,

instime,

err_number,

err_message)

values

(seq_wl_al_exec_proc_log.nextval,

'proc_cms_ftp_lxr',

sysdate,

l_errcode,

l_errmessage);

commit;

out_retval := -1;

-- 由end结束存储过程

END TEST_ZYP;

编译过程中遇到的问题：

Q：以下代码报错，

IF in_mdns IS NOT NULL THEN --如果in_mdns不为空

BEGIN

END;

END IF;

A：这其实是想写这么个判断的流程，有这么个意思，并没有加具体做法。但是正是因为没有加具体做法，所以这个语法就错了。在C中可以这样写如下这一个空的if语句：

if(NULL!=in_mdns){} --正确，表示一个空的if语句

但是在Oracle中是不行的,因此，随便加个什么语句就好啦~

IF in_mdns IS NOT NULL THEN

BEGIN

--l_temp number; --错误，参数声明必须在declear中

l_ecid := '400097'; --正确

END;

END IF;

Q：以下代码报错：

utl_file.put(f_obj,"");

A: 在C中，字符用单引号；字符串用双引号。

在Oracle中，都是用单引号。

因此，改一下就好啦~

正确：

utl_file.put(f_obj,'');

Q：以下代码报错：

IF in_mdns IS NOT NULL THEN

BEGIN

l_ecid := '400097';

END

END IF

A：错心大意的我忘记在END后面加分号作为if/begin的结束了....

IF in_mdns IS NOT NULL THEN

BEGIN

l_ecid := '400097';

END;

END IF;