PL/SQL 访问网页(get or post方式)
在我们开发plsql程序的过程中,有时候难免要访问一些外部网站的数据。这个时候我们就要用到utl_http包。
使用utl_http包前需要注意的是,当前的用户下是否有访问外部网络的权限。
get方式:
function http_get(p_url in varchar2) return clob
is
http_req utl_http.req;
http_resp utl_http.resp;
l_raw raw(1024);
l_r clob;
begin
begin
http_req:=utl_http.begin_request(p_url,'GET');
http_resp := utl_http.get_response(http_req, TRUE);
loop
utl_http.read_raw(http_resp, l_raw,1024);
l_r:=l_r||utl_raw.cast_to_varchar2(l_raw);
end loop;
utl_http.end_response(http_resp);
exception
when utl_http.end_of_body then
utl_http.end_response(http_resp);
end;
return l_r;
end;
post方式:
function http_post(
p_url in varchar2,
p_data in varchar2 --a=1&b=2...
) return clob
is
http_req utl_http.req;
http_resp utl_http.resp;
l_raw raw(1024);
l_r clob;
begin
begin
http_req:=utl_http.begin_request(p_url,'POST');
utl_http.set_header(http_req,'Content-Type','application/x-www-form-urlencoded;charset=utf-8');
utl_http.set_header(http_req,'Content-Length',length(p_data));
utl_http.write_text(http_req,p_data);
http_resp := utl_http.get_response(http_req, TRUE);
loop
utl_http.read_raw(http_resp, l_raw,1024);
l_r:=l_r||utl_raw.cast_to_varchar2(l_raw);
end loop;
utl_http.end_response(http_resp);
exception
when utl_http.end_of_body then
utl_http.end_response(http_resp);
end;
return l_r;
end;
灵活一点的
create or replace function fnHTTP_REQUEST(param varchar2,content varchar2) return varchar2 as
/**
*@paramp_dataType返回值类型:json或者xml
*@returnjson或者xml
*/
l_request utl_http.req;
l_response utl_http.resp;
l_result varchar2(32767);
l_url varchar2(4000);
l_param varchar2(4000);
begin
begin
utl_http.set_response_error_check(false);
utl_http.set_body_charset('UTF-8');
--设置请求的地址
l_url:='http://127.0.0.1:8080/api/api_test.jsp';
--设置请求的路径
l_param:='param='||param||'&content='||UTL_URL.escape(content);
--设置用POST方式请求
l_request:=utl_http.begin_request(l_url,'POST');
--设置请求头部
utl_http.set_header(l_request,'Content-Type','application/x-www-form-urlencoded');
UTL_HTTP.SET_HEADER(l_request,'Content-Length',LENGTHB(l_param));
--raw方式写入参数,可以避免中文变乱码
UTL_HTTP.WRITE_RAW (l_request,UTL_RAW.CAST_TO_RAW(l_param));
--发送请求并获取返回结果
l_response := utl_http.get_response(l_request);
if l_response.status_code = 200 then
utl_http.read_text(l_response, l_result, length(l_result));
utl_http.end_response(l_response);
else--错误处理
l_result := '网络访问错误!';
utl_http.end_response(l_response);
end if;
exception--异常处理
when others then
dbms_output.put_line(sqlerrm);
dbms_output.put_line(dbms_utility.format_error_backtrace);
l_result := sqlerrm;
if l_response.status_code is not null then
utl_http.end_response(l_response);
end if;
end;
return l_result;
end;