数据表结构如下:
SQL> desc record;
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------
RECORD_GUID VARCHAR2(50) Y
DNIS VARCHAR2(15) Y
ANI VARCHAR2(15) Y
STARTTIME DATE Y
ENDTIME DATE Y
STAFFID VARCHAR2(10) Y
AGENTID VARCHAR2(10) Y
EXTENSION VARCHAR2(20) Y
尝试显示指定电话和某年月份的电话记录,采用存储过程实现
create or replace procedure proc_getrecord(i_dnis in varchar2,
i_time in varchar2)
/*
功能:查找某月电话记录
参数说明:i_dnis为主叫号码,i_time为某年月份(例如201401)
编写日期:2014.06.26
*/
as
sqlstr varchar2(5000);
begin
declare
type rc is ref cursor;
c_rc rc;
r_record record%rowtype;
begin
sqlstr:='select * from king.record where dnis =:1’||
' and replace(substr(starttime, 1, 7), ''-'', '''')=:2';
open c_rc for sqlstr using i_dnis,i_time;
fetch c_rc into r_record;
while c_rc%found loop
dbms_output.put_line(
'开始时间:'||r_record.starttime||' '||
'结束时间:'||r_record.endtime||' '||
'主叫号码:'||r_record.dnis||' '||
'被叫号码:'||r_record.ani);
fetch c_rc into r_record;
end loop;
close c_rc;
end;
end;
转载于:https://blog.51cto.com/yanlfcto/1433122