No sum for interval day to second?
I just wonder why ORACLE didn’t implement sum of interval day to second datatype.
CREATE OR REPLACE TYPE sum_interval AS OBJECT
(
/* type implements oracles data cartridge interface to compute sum of day to second interval values */
duration INTERVAL DAY TO SECOND, --duration accumulator
CONSTRUCTOR FUNCTION sum_interval RETURN SELF AS RESULT
PARALLEL_ENABLE,
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT sum_interval) RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate
(
SELF IN OUT sum_interval,
VALUE IN INTERVAL DAY TO SECOND
) RETURN NUMBER,
MEMBER FUNCTION odciaggregatedelete
(
SELF IN OUT sum_interval,
VALUE IN INTERVAL DAY TO SECOND
) RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate
(
SELF IN sum_interval,
returnvalue OUT INTERVAL DAY TO SECOND,
flags IN NUMBER
) RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge
(
SELF IN OUT sum_interval,
ctx IN sum_interval
) RETURN NUMBER
)
;
/
CREATE OR REPLACE TYPE BODY sum_interval IS
-------------------------------------------------------------------------------------------------
CONSTRUCTOR FUNCTION sum_interval RETURN SELF AS RESULT
PARALLEL_ENABLE IS
BEGIN
RETURN;
END sum_interval;
-------------------------------------------------------------------------------------------------
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT sum_interval) RETURN NUMBER IS
BEGIN
ctx := sum_interval();
RETURN odciconst.success;
END odciaggregateinitialize;
-------------------------------------------------------------------------------------------------
MEMBER FUNCTION odciaggregateiterate
(
SELF IN OUT sum_interval,
VALUE IN INTERVAL DAY TO SECOND
) RETURN NUMBER IS
BEGIN
IF SELF.duration IS NULL THEN
SELF.duration := VALUE;
ELSE
SELF.duration := SELF.duration + VALUE;
END IF;
RETURN odciconst.success;
END odciaggregateiterate;
-------------------------------------------------------------------------------------------------
MEMBER FUNCTION odciaggregateterminate
(
SELF IN sum_interval,
returnvalue OUT INTERVAL DAY TO SECOND,
flags IN NUMBER
) RETURN NUMBER IS
BEGIN
returnvalue := SELF.duration;
RETURN odciconst.success;
END odciaggregateterminate;
-------------------------------------------------------------------------------------------------
MEMBER FUNCTION odciaggregatedelete
(
SELF IN OUT sum_interval,
VALUE IN INTERVAL DAY TO SECOND
) RETURN NUMBER IS
BEGIN
SELF.duration := SELF.duration - VALUE;
RETURN odciconst.success;
END odciaggregatedelete;
-------------------------------------------------------------------------------------------------
MEMBER FUNCTION odciaggregatemerge
(
SELF IN OUT sum_interval,
ctx IN sum_interval
) RETURN NUMBER IS
BEGIN
IF ctx.duration IS NULL THEN
NULL;
ELSE
SELF.duration := SELF.duration + ctx.duration;
END IF;
RETURN odciconst.success;
END odciaggregatemerge;
END;
/
create or replace function fce_sum_interval(duration interval day to second) return interval day to second
parallel_enable
aggregate using sum_interval;
/
select fce_sum_interval(numtodsinterval(level, 'minute')) from dual connect by level <= 10;
Reklamy
PL/SQL try-catch-finally concept
Taky maly pokus o koncept implementacie try-catch-finally v PL/SQL.
- 1. priblizenie
- + zachovava “strukturu”
- – rozbija error_stack a chybovu hlasku
<<try_catch_finally_block>>
declare
--some handled exception
e exception;
pragma exception_init(e, -20666);
--some unhandled user defined exception
e_some_user_defined exception;
pragma exception_init(e_some_user_defined, -20000);
--some ora exception
e_some_ora exception;
pragma exception_init(e_some_ora, -1);
l_sqlcode pls_integer;
l_sqlerrm varchar2(500);
begin
<<try_block>>
begin
--try
dbms_output.put_line('try');
--dbms_output.put_line('handled exception raised');
--raise e;
--dbms_output.put_line('some unhandled user defined exception raised');
--raise e_some_user_defined;
--dbms_output.put_line('some unhandled ora exception raised');
--raise e_some_ora;
exception
when e then
--catch
dbms_output.put_line('catch');
when others then
--store unhandled exception
l_sqlcode := sqlcode;
l_sqlerrm := sqlerrm;
dbms_output.put_line('store unhandled exception');
end try_block;
--finally
dbms_output.put_line('finally');
--reraise unhandled exception
if (l_sqlcode is not null) then
dbms_output.put_line('reraise unhandled exception');
--if user defined exception
if l_sqlcode between -20999 and -20000 then
--reraise user defined error
raise_application_error(l_sqlcode, l_sqlerrm);
else
--reraise ora error
<<reraise_ora_error>>
declare
begin
execute immediate '
declare
e exception;
pragma exception_init(e, '||l_sqlcode||');
begin
raise e;
end;
';
end reraise_ora_error;
end if;
end if;
end try_catch_finally_block;
/
- 2. priblizenie
- – nezachovava “strukturu”
- + zachovava error_stack a chybovu hlasku
- + cistejsi & prehladnejsi kod
<<try_catch_finally_block>>
declare
--some handled exception
e exception;
pragma exception_init(e, -20666);
--some unhandled user defined exception
e_some_user_defined exception;
pragma exception_init(e_some_user_defined, -20000);
--some ora exception
e_some_ora exception;
pragma exception_init(e_some_ora, -1);
--local procedure where finalization is implemented
procedure finally is
begin
--finally
dbms_output.put_line('finally');
end finally;
begin
<<try_block>>
begin
--try
dbms_output.put_line('try');
--dbms_output.put_line('handled exception raised');
--raise e;
--dbms_output.put_line('some unhandled user defined exception raised');
--raise e_some_user_defined;
--dbms_output.put_line('some unhandled ora exception raised');
--raise e_some_ora;
exception
when e then
--catch
dbms_output.put_line('catch');
when others then
--finally
finally;
--raise unhandled
raise;
end try_block;
--finally
finally;
end try_catch_finally_block;
/
Read Full Post | Make a Comment ( 1 so far )
