SQL> create or replace procedure huitailang(i_date in date, 2 o_cur out sys_refcursor) as 3 v_sql varchar2(4000); 4 begin 5 v_sql := 'select trunc(calldate) as 日期, count(*) as 总转接量 from ((select a, b, dateTime from aa where calldate >= trunc(' || 6 i_date || ') union (select a, b, dateTime from bb 7 where calldate >= trunc(' || i_date || 8 '))) group by trunc(calldate) order by trunc(calldate)'; 9 open o_cur for v_sql; 10 end; 11 /
huitailang。 我看你就是一个大灰狼
Procedure,Functions,Packages,and Types must be created/altered in the Procedure Editor or by executing as a script这个错误,怎么回事呢?
create or replace procedure huitailang(i_date in date, c_cur out sys_refcursor) as sqlstr varchar2(4000); begin sqlstr:='select to_char(calldate,''yyyymmdd'') as 日期,count(*) as 总转接量 from ((select a,b,dateTime from aa where calldate>=to_date('''||i_date||''',''yyyymmdd'')) --i_date是变量 union (select a,b,dateTime from bb where calldate >=to_date('''||i_date||''',''yyyymmdd''))) group by to_char(calldate,''yyyymmdd'') order by to_char(calldate,''yyyymmdd'')'; open c_cur for sqlstr; end;
没错我腌SQL> SQL> create or replace procedure huitailang(i_date in date, 2 o_cur out sys_refcursor) as 3 v_sql varchar2(4000); 4 begin 5 v_sql := 'select trunc(calldate) as 日期, count(*) as 总转接量 from ((select a, b, dateTime from aa where calldate >= trunc(' || 6 i_date || ') union (select a, b, dateTime from bb 7 where calldate >= trunc(' || i_date || 8 '))) group by trunc(calldate) order by trunc(calldate)'; 9 open o_cur for v_sql; 10 end; 11 /Procedure createdSQL>
create or replace procedure huitailang(i_date in varchar2, --符合时间类型的字符 c_cur out sys_refcursor) as sqlstr varchar2(4000); begin sqlstr:='select to_char(calldate,''yyyymmdd'') as 日期,count(*) as 总转接量 from ((select a,b,dateTime from aa where calldate>=to_date('''||i_date||''',''yyyymmdd'')) --i_date是变量 union (select a,b,dateTime from bb where calldate >=to_date('''||i_date||''',''yyyymmdd''))) group by to_char(calldate,''yyyymmdd'') order by to_char(calldate,''yyyymmdd'')'; open c_cur for sqlstr; end;
create or replace p_cur(i_date varchar2,cur out sys_refcursor) as str varchar2(1000); begin str:='select to_char(calldate,''yyyymmdd'') as 日期,count(*) as 总转接量 from ((select a,b,dateTime from aa where calldate>=to_date('''||i_date||''',''yyyymmdd'')) --i_date是变量 union (select a,b,dateTime from bb where calldate >=to_date('''||i_date||''',''yyyymmdd''))) group by to_char(calldate,''yyyymmdd'') order by to_char(calldate,''yyyymmdd'')'; open cur for str; end;
第一步建包:CREATE OR REPLACE PACKAGE PKG_agentAllot AS type T_CURSOR is ref cursor; --游标 PROCEDURE Getusername ( IDATE in Date, --时间类型--传入参数 cur_name OUT T_CURSOR ); END PKG_agentAllot;第二步创建包体:CREATE OR REPLACE PACKAGE BODY PKG_agentAllot AS PROCEDURE Getusername ( ISCallBack in Date, --参数 cur_name OUT T_CURSOR ) IS TYPE t_cursor IS REF CURSOR; begin open cur_name for select to_char(calldate,'yyyymmdd') as 日期,count(*) as 总转接量 from ((select a,b,dateTime from aa where calldate>=to_date(IDATE,'yyyymmdd')) --IDATE是传入变量 union (select a,b,dateTime from bb where calldate >=to_date('i_date','yyyymmdd'))) group by to_char(calldate,'yyyymmdd') order by to_char(calldate,'yyyymmdd') end;END PKG_agentAllot;
SQL> create or replace procedure huitailang(i_date in date,
2 o_cur out sys_refcursor) as
3 v_sql varchar2(4000);
4 begin
5 v_sql := 'select trunc(calldate) as 日期, count(*) as 总转接量 from ((select a, b, dateTime from aa where calldate >= trunc(' ||
6 i_date || ') union (select a, b, dateTime from bb
7 where calldate >= trunc(' || i_date ||
8 '))) group by trunc(calldate) order by trunc(calldate)';
9 open o_cur for v_sql;
10 end;
11 /
我看你就是一个大灰狼
create or replace procedure huitailang(i_date in date,
c_cur out sys_refcursor) as
sqlstr varchar2(4000);
begin
sqlstr:='select to_char(calldate,''yyyymmdd'') as 日期,count(*) as 总转接量 from
((select a,b,dateTime from aa
where calldate>=to_date('''||i_date||''',''yyyymmdd'')) --i_date是变量
union
(select a,b,dateTime from bb
where calldate >=to_date('''||i_date||''',''yyyymmdd'')))
group by to_char(calldate,''yyyymmdd'')
order by to_char(calldate,''yyyymmdd'')';
open c_cur for sqlstr;
end;
SQL> create or replace procedure huitailang(i_date in date,
2 o_cur out sys_refcursor) as
3 v_sql varchar2(4000);
4 begin
5 v_sql := 'select trunc(calldate) as 日期, count(*) as 总转接量 from ((select a, b, dateTime from aa where calldate >= trunc(' ||
6 i_date || ') union (select a, b, dateTime from bb
7 where calldate >= trunc(' || i_date ||
8 '))) group by trunc(calldate) order by trunc(calldate)';
9 open o_cur for v_sql;
10 end;
11 /Procedure createdSQL>
create or replace procedure huitailang(i_date in varchar2, --符合时间类型的字符
c_cur out sys_refcursor) as
sqlstr varchar2(4000);
begin
sqlstr:='select to_char(calldate,''yyyymmdd'') as 日期,count(*) as 总转接量 from
((select a,b,dateTime from aa
where calldate>=to_date('''||i_date||''',''yyyymmdd'')) --i_date是变量
union
(select a,b,dateTime from bb
where calldate >=to_date('''||i_date||''',''yyyymmdd'')))
group by to_char(calldate,''yyyymmdd'')
order by to_char(calldate,''yyyymmdd'')';
open c_cur for sqlstr;
end;
create or replace p_cur(i_date varchar2,cur out sys_refcursor)
as
str varchar2(1000);
begin
str:='select to_char(calldate,''yyyymmdd'') as 日期,count(*) as 总转接量 from
((select a,b,dateTime from aa
where calldate>=to_date('''||i_date||''',''yyyymmdd'')) --i_date是变量
union
(select a,b,dateTime from bb
where calldate >=to_date('''||i_date||''',''yyyymmdd'')))
group by to_char(calldate,''yyyymmdd'') order by to_char(calldate,''yyyymmdd'')';
open cur for str;
end;
PKG_agentAllot AS
type T_CURSOR is ref cursor; --游标
PROCEDURE Getusername
(
IDATE in Date, --时间类型--传入参数
cur_name OUT T_CURSOR
);
END PKG_agentAllot;第二步创建包体:CREATE OR REPLACE PACKAGE BODY PKG_agentAllot AS
PROCEDURE Getusername
(
ISCallBack in Date, --参数
cur_name OUT T_CURSOR
) IS
TYPE t_cursor IS REF CURSOR;
begin
open cur_name for select to_char(calldate,'yyyymmdd') as 日期,count(*) as 总转接量 from
((select a,b,dateTime from aa
where calldate>=to_date(IDATE,'yyyymmdd')) --IDATE是传入变量
union
(select a,b,dateTime from bb
where calldate >=to_date('i_date','yyyymmdd')))
group by to_char(calldate,'yyyymmdd')
order by to_char(calldate,'yyyymmdd')
end;END PKG_agentAllot;