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 )