Oracle P/L SQL实现发送Email、浏览网页等网络操作功能
Oracle P/L SQL实现发送Email、浏览网页等网络操作功能，以下是此过程包的头部，包体经常打包处理plb，感兴趣用户可以下载下来。
--.使用聚合函数实现 多行合并
Drop Type Strcat_type;
Drop Function f_StrCat;
Drop Package UTL_INet;
Variable ls_ObjectName VarChar2(128);
Begin
Select Sequence_Name Into :ls_ObjectName From User_Sequences Where Sequence_Name = 'SYS_RAND_ID';
DBMS_Output.Put_Line( :ls_ObjectName );
Exception When No_Data_Found Then
Execute Immediate 'Create Sequence SYS_RAND_ID minvalue 1 maxValue 99999999999999999 Start With 1 increment by 1 cache 5 cycle order';
End;
/
--1、创建类型
Create Or Replace Type Strcat_type As Object (
cat_string varchar2(4000),
Static Function ODCIAggregateInitialize(cs_ctx In Out strcat_type) Return Number,
Member Function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) Return Number,
Member Function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) Return Number,
Member Function ODCIAggregateTerminate(self In Out strcat_type,ReturnValue Out varchar2,flags in Number) Return Number
);
/
--2. 创建类型体
Create Or Replace Type Body Strcat_type Is
Static Function ODCIAggregateInitialize( cs_ctx In Out strcat_type
)Return Number
is
Begin
cs_ctx := strcat_type( Null );
Return ODCIConst.Success;
End;
Member Function ODCIAggregateIterate( self In Out strcat_type,
value In varchar2 )
Return Number
is
Begin
if self.cat_string is Null or Instr( self.cat_string, value ) = 0 Then
self.cat_string := self.cat_string || ','|| value;
End if;
Return ODCIConst.Success;
End;
Member Function ODCIAggregateTerminate( self In Out strcat_type,
ReturnValue Out varchar2,
flags In Number)
Return Number
is
Begin
ReturnValue := ltrim(rtrim( self.cat_string,','),',' );
Return ODCIConst.Success;
End;
Member Function ODCIAggregateMerge( self In Out strcat_type,
ctx2 In Out strcat_type)
Return Number
is
Begin
if self.cat_string is Null or Instr( self.cat_string, ctx2.cat_string ) = 0 Then
self.cat_string := self.cat_string || ',' || ctx2.cat_string;
End if;
Return ODCIConst.Success;
End;
End;
/
--3.创建函数: 使用聚合函数实现 多行合并
Create or Replace Function f_StrCat( as_input Varchar2 ) Return Varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;
/
Grant Execute on f_StrCat To Public;
--End of 使用聚合函数实现 多行合并
Create Or Replace Package UTL_INet AS
Type VarChar_Type is Table of VarChar2(400) Index By Binary_Integer;
Type Number_Type is Table of Number(12,4) Index By Binary_Integer;
Type DynamicCursor is ref Cursor; --动态游标
--Purpose : 获得汉字拼音编码
Type ut_PYIndex_191_List is Varray( 191 ) OF Number;
Type ut_PYIndex_List is Varray( 10 ) OF ut_PYIndex_191_List;
is_OracleDirectory Constant VarChar2(20) := 'ATTACH_DIR'; --内部附件生成目录（Oracle的目录）
--Clob叠加比较慢，先用VarChar2叠加到4000个字符后才叠加到Clob字段
--UTL_INet.p_ClobCAT(
Procedure p_ClobCAT(
ac_HTMLText in Out Clob,
as_CatText in Out VarChar2,
as_Str in VarChar2 Default Null
);
--字符串根据特定分隔符分来
--Select UTL_INet.f_SplitString( 'A,B,C', xx, ',' ) From dual;
Function f_SplitString(
as_SourStr in out Clob, --输入字符串A,B,C
as_Separator in VarChar2 Default '/' --分拆依据的分隔符,
)Return VarChar2; --分拆结果A
--将Clob内容写入物理文件
--Exec UTL_INet.p_PutClob2File( as_FileName => 'aa.sql', ac_Text => 'test sql' );
Procedure p_PutClob2File(
as_SubDir in VarChar2, --目录名
as_FileName in VarChar2, --文件名
ac_Text in Clob, --文件内容
as_Overwrite in VarChar default 'Y', --标志位：Y:覆盖文件内容，N:追加
as_OraVersion in VarChar default 'N' --标志位：Y:写入Oracle版本信息
);
--序号自动递增计算
--e.g.: 输入：HLXU99349021，返回：HLXU99349022
--范例： Select UTL_INet.f_AutoNum( 'HLXU99349021' ), UTL_INet.f_AutoNum( 'ABA', -1 ), UTL_INet.f_AutoNum( 'ABZ' ) from Dual;
Function f_AutoNum(
as_OldNum in VarChar2, --原字符串
ai_Step in Number Default 1, --步长，默认是递增加1，
ai_DigitXXX in Number Default Null --累计序号位数 XXX
)Return VarChar2;
--字符串加解密，返回一串32位长的字符串
--Select UTL_INet.f_MD5( 'TestPassword' ) From Dual;
Function f_MD5(
as_SourceStr in Varchar2 --需要加密的字符串
) Return Varchar2;
--将Email地址去头去尾，剩下最简单的Email地址，如"TSI Customer Service" <cs@csdn.com>变成cs@csdn.com
Function f_GetNakedEmailAddr(
as_DisplayEmail In VarChar2,
as_Including in Char Default 'N' --Y: 返回<cs@csdn.com>
)Return VarChar2;
--测试发送Email的邮箱是否正确
Function f_TestEmailAccount(
ac_Connection out Nocopy UTL_SMTP.Connection,
as_SMTPHost in VarChar2, --邮件服务器 mail.csdn.com
ai_SMTPPort in PLS_Integer Default 25, --邮件服务器端口
as_SMTPAuth in VarChar2 Default 'Y', --发送密码验证
as_Username in VarChar2 Default Null, --邮件用户
as_Password in VarChar2 Default Null, --邮件口令
as_WalletPath in VarChar2 Default Null,
as_WalletPwd in VarChar2 Default Null
)Return Boolean;
------------------------------------------------ 写邮件头和邮件内容-------------------------
Procedure p_WriteRawData(
ac_Conn in Out Nocopy UTL_SMTP.Connection,
as_Partname in VarChar2,
as_Value in VarChar2,
as_Splite in VarChar2 Default ':',
as_CRLF in VarChar2 Default UTL_TCP.CRLF
);
----------------------------------------------发送附件-------------------------------------
Procedure p_MailAttachment(
ac_Conn in Out Nocopy UTL_SMTP.Connection,
as_Filename in VarChar2,
as_Boundary in VarChar2,
as_Encode in VarChar2 Default 'base64',
as_MimeType in VarChar2 Default 'text/plain',
as_ContentID in VarChar2 Default Null, --
ab_Inline in Boolean Default False --True将文本内容直接在邮件内容显示出来，并出现在附件中，False不显示只出现在附件中
);
-----------------自动签名的生成，签名生成显示后还出现此签名文件为附件，尚未解决-------------------------------
Procedure p_GetMailSignature(
ac_Conn in Out Nocopy UTL_SMTP.Connection,
as_Boundary in VarChar2,
as_Encode in VarChar2 Default 'base64',
as_SignatureLogo in VarChar2 Default Null,
as_SignatureText in VarChar2 Default Null
);
--发送Email前必须将ewallet.p12拷贝到C:\OracleAttachDir目录
--发送电子邮件
--Exec UTL_INet.p_SendEmail( 'csdn@gmail.com', 'Test 主题Subject', 'Mail body(邮件内容)' );
Procedure p_SendEmail(
as_Sender in VarChar2,
as_Recipient in VarChar2,
as_CC in VarChar2 Default Null,
as_BCC in VarChar2 Default Null,
as_Subject in VarChar2,
ac_Message in Clob,
as_AttachLists in VarChar2 Default Null, --多个用逗号，分开
as_SMTPHost in VarChar2, --邮件服务器
ai_SMTPPort in PLS_Integer Default 25, --邮件服务器端口
as_SMTPAuth in VarChar2 Default 'Y', --发送密码验证
as_WalletPath in VarChar2 Default Null,
as_WalletPwd in VarChar2 Default Null,
as_Username in VarChar2 Default Null,
as_Password in VarChar2 Default Null,
as_RunResult out VarChar2, --返回信息，OK成功，其他返回错误
as_SignatureLogo in VarChar2 Default Null,
as_SignatureText in VarChar2 Default Null,
as_Encode in VarChar2 Default 'base64',
ai_Priority In Pls_Integer Default Null
);
Function f_LoadHTMLFromURL(
as_URL in VarChar2,
as_CharSet in VarChar2 Default 'UTF-8'
)Return Clob;
--生成HTML报表表头
--Exec f_HTMLTableHead( 'Tab1', '20:Table Name;20:Records' );
Function f_HTMLTableHead(
as_TableID in VarChar2, --表ID
as_WidthColumns in VarChar2, --表头内容，用分号;隔开，宽度与标题用冒号:隔开
as_BgColor in VarChar2 Default 'CCCCCC' --标题背景色
)Return VarChar2;
--生成HTML报表主体内容
--Exec UTL_INet.f_HTMLTableBody( '' );
Function f_HTMLTableBody(
as_BodyText in VarChar2, --主体内容，多个用分号;隔开
as_Align in VarChar2 Default Null --格式（居中，靠左，靠右）
)Return VarChar2;
--获取汉字拼音字母表
--Select UTL_INet.f_getChineseSpell( '获取汉字拼音' ) from dual;
Function f_getChineseSpell(
as_CNStr in VarChar2, --中文
as_First in VarChar2 Default Null --空返回完整拼音，其他返回拼音首字母
)Return VarChar2;
--二进制转换成十进制函数
--Select UTL_INet.f_Bin2Dec( '10111011' ) From dual;
Function f_Bin2Dec(
as_Bin in VarChar2
)Return Number;
--十进制转换成二进制函数
--Select UTL_INet.f_Dec2Bin( 187 ) From dual;
Function f_Dec2Bin(
an_Dec in Number
)Return VarChar2;
--十进制转换成三十二进制函数
--Select UTL_INet.f_Dec2Hex( 187 ) From dual;
Function f_Dec2Hex(
an_Dec in Number
)Return VarChar2;
--三十二进制转换成十进制函数
--Select UTL_INet.f_Hex2Dec( '5R' ) From dual;
Function f_Hex2Dec(
as_Hex in VarChar2
)Return Number;
--生成12位随机数
--Select UTL_INet.f_Rand() From dual;
Function f_Rand(
as_PreFix in VarChar2 Default '00', --未满个数字符补充串
an_Nums in Number Default 12
)Return VarChar2;
--繁体字转化成简体字(传入的汉字，若有繁体自动转化为简体)
Function f_ft2jt(
as_Text in VarChar2 --传入的汉字
)Return VarChar2;
--简体字转化成繁体字(传入的汉字，若有简体自动转化为繁体)
Function f_jt2ft(
as_Text in VarChar2 --传入的汉字
)Return VarChar2;
-- --------------------------------------------------------------------------
-- Description : SOAP related Functions for consuming web services.
Type t_Request is Record (
Method VarChar2(256),
Namespace VarChar2(256),
Body VarChar2(32767),
EnvelopeTag VarChar2(30)
);
Type t_Response is Record
(
Doc XMLType,
EnvelopeTag VarChar2(30)
);
Function f_NewRequest(
as_Method in VarChar2,
as_Namespace in VarChar2,
as_EnvelopeTag in VarChar2 Default 'SOAP-ENV'
)Return t_Request;
Procedure p_AddParameter(
as_Request in out Nocopy t_Request,
as_Name in VarChar2,
as_Type in VarChar2,
as_Value in VarChar2
);
Function f_Invoke(
as_Request in out Nocopy t_Request,
as_URL in VarChar2,
as_Action in VarChar2
)Return t_Response;
Function f_GetReturnValue(
as_Response in out Nocopy t_Response,
as_Name in VarChar2,
as_Namespace in VarChar2
)Return VarChar2;
--列出当前目录下所有文件清单，可以指定扩展名，是否包含子目录，返回的文件名用|分割开来
Function f_ListDirectory(
as_SubDir in VarChar2,
as_Ext in VarChar2 Default Null,
as_IncludingSubDir in Char Default 'N'
)Return Clob;
--将数值翻译成中文大写、英文大写
--Select f_Digit2Char( 24822.80, 'EN_Amount' ) From dual;
Function f_Digit2Char(
an_Amount in Number, --要被翻译的数值
as_Option in VarChar2 --翻译选择项
)Return VarChar2;
--发送短信
Function f_SendSMS(
as_MobilePhone in VarChar2, --手机号码
as_SMSText in VarChar2, --短信内容
as_SMSURL in VarChar2,
as_SMSUserID in VarChar2,
as_SMSPasswd in VarChar2
)Return VarChar2;
--删除某个指定文件
Procedure p_RemoveFile(
as_SubDir in VarChar2, --路径
as_FileName in VarChar2 --多个用逗号，分开
);
--从文件中读内容
Function f_GetTextFromFile(
as_SubDir in VarChar2, --目录名
as_FileName in VarChar2, --文件名
as_NewLine in VarChar2 Default UTL_TCP.CRLF --换行符
)Return Clob;
END UTL_INet;
/
扫一扫
156
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
