update a table using a stored procedure in oracle -

-

i have table t_time have below attributes

time_key, calendar_dt, cal_year, cal_quarter, cal_month, cal_week, week_in_month, cal_st_dt_of_wk, cal_end_dt_of_wk, rfrsh_dt, cal_yyyymm  select * t_time time_key = (select max(time_key) t_time);  74937   31-12-2015  2015    4   12  5   5   27-12-2015  02-01-2016  17-07-2009  201512

i want write stored proc such when specify year,t_time should inserted keys , other attributes..

like

for 2016

time_key  calendar_dt cal_year  cal_quarter cal_month cal_week  week_in_month cal_st_dt_of_wk cal_end_dt_of_wk  rfrsh_dt  cal_yyyymm 74938       01-01-2016    2016        1           1         1             1         01-01-2016      02-01-2016    22-04-2015      201601 74939       02-01-2016    2016        1           1         1             1         01-01-2016      02-01-2016    22-04-2015      201601 74940       03-01-2016    2016        1           1         2             2         03-01-2016      09-01-2016    22-04-2015      201601 74941       04-01-2016    2016        1           1         2             2         03-01-2016      09-01-2016    22-04-2015      201601

cal_end_dt_of_wk saturday of week cal_st_dt_of_wk sunday of week

can give me idea start with..

time_key -  time_key + 1 calendar_dt - select sysdate dual; cal_year    - select extract(year sysdate) dual; cal_quarter - select case when extract(month sysdate) in (1,2,3) 1                      case when extract(month sysdate) in (4,5,6) 2                      case when extract(month sysdate) in (7,8,9) 3                      case when extract(month sysdate) in (10,11,12) 4 else 0 end cal_quarter dual; cal_month   - select extract(month sysdate) dual;  cal_week    - select to_char(to_date(sysdate),'ww') dual;   week_in_month - select to_char(to_date(sysdate),'w') dual; cal_st_dt_of_wk -  select trunc(sysdate,'iw')-1 dual; cal_end_dt_of_wk - select trunc(sysdate,'iw')+5 dual; rfrsh_dt - select sysdate dual; cal_yyyymm - select to_char(sysdate,'yyyymm') dual;

ok, it's lot of stuff ;) think should work now:

-- provide year yyyy create or replace procedure fill_table (year in varchar2)   date_holder date := to_date ('01.01.' || year,'dd.mm.yyyy'); begin   while (to_char (date_holder,'yyyy') = year) loop   insert t_time       values (1,               date_holder,              extract(year date_holder),               case when extract(month date_holder) in (1,2,3) 1                   when extract(month date_holder) in (4,5,6) 2                   when extract(month date_holder) in (7,8,9) 3                   when extract(month date_holder) in (10,11,12) 4 else 0 end,               extract(month date_holder),               to_char(to_date(date_holder),'ww'),              to_char(to_date(date_holder),'w'),               trunc(date_holder,'iw')-1,              trunc(date_holder,'iw')+5,              sysdate ,              to_char(date_holder,'yyyymm'));       date_holder := date_holder +1;   end loop; end; /

so basic idea is:

  • start 1.1.<year> date
  • add 1 day other , insert values described

there seems issue on calendar week, week in month, start , end of week though .... anyway - approach should fine. omitted correct key calculation - best option sequence.

p.s.: check this demo.


Comments

Post a Comment

Popular posts from this blog

c++ - No viable overloaded operator for references a map -

-
i trying use map can make tag names reference number. when try , use it, in code error (6 in total every time reference map): src/main.cpp:25:45: error: no viable overloaded operator[] type 'std::map<std::string, std::string>' const char* idcs = node.child_value(tagmap[3]); this code: #include "pugi/pugixml.hpp" #include <iostream> #include <string> #include <map> int main() { pugi::xml_document doca, docb; std::map<std::string, pugi::xml_node> mapa, mapb; std::map<std::string, std::string> tagmap {{"1", "data"}, {"2", "entry"}, {"3", "id"}, {"4", "content"}}; if (!doca.load_file("a.xml") || !docb.load_file("b.xml")) { std::cout << "can't find input files"; return 1; } (auto& node: doca.child(tagmap[1]).children(tagmap[2])) { co
Read more

java - Custom OutputStreamAppender not run: LOGBACK: No context given for <MYAPPENDER> -

-
i wish write custom appender on basis of outputstreamappender . wrote following class package tests; import java.io.ioexception; import java.io.outputstream; import ch.qos.logback.core.outputstreamappender; public class myappender<e> extends outputstreamappender<e> { public myappender() { system.out.println("myappender created"); setoutputstream(new outputstream() { @override public void write(int b) throws ioexception { throw new unsupportedoperationexception(); } }); } } then wrote following runner: package tests; import org.slf4j.loggerfactory; import ch.qos.logback.classic.logger; public class runner { private static final logger log = (logger) loggerfactory.getlogger(runner.class); public static void main(string[] args) { //log.addappender(new myappender<iloggingevent>()); log.info("hello world"); } } finall
Read more

java - Cannot secure connection using TLS -

-
i'm trying establish secure socket connection between java client applet (built jdk 1.7.0_75-b13) , vc++ server application. as test vehicle, used vc++ client/server sample found in msdn forums, modified use schannel , able establish socket using cipher suite tls_rsa_with_aes_128_cbc_sha. works of tls 1.0/1.1/1.2. when try opening socket java applet same server application, connection rejected server reporting following: tls 1.0 acceptsecuritycontext failed: 0x80090327 tls 1.1 acceptsecuritycontext failed: 0x80090331 tls 1.2 acceptsecuritycontext failed: 0x80090331 this java code used create socket: debugprint("setting secure connection"); sslsocketfactory sslsocketfactory = (sslsocketfactory) sslsocketfactory.getdefault(); sslsocket sslsocket = (sslsocket) sslsocketfactory.createsocket("127.0.0.1", socketnumber); debugprint("starting handshake"); sslsocket.settcpnodelay(true); sslsocket.setsolinger(false, 0); sslsocket.s
Read more