算是一篇鼓励自己继续前行的回忆录吧
算是一篇鼓励自己继续前行的回忆录吧
阅读《Oracle优化日记：一个金牌DBA的故事》，顺便翻看了往日的日记本，感觉自己错误了这15年时间，
2006.3进入了一家所谓500强Emerson - 做AC/DC、DC/DC做了一颗螺丝钉，相当于转行了啊，不知道是不愿意承认还是什么，
现在心里不知啥滋味，错过了这黄金般的时间啊。
2005年做JDE的二次报表开发，没咨询顾问，短时间看完了那2本关于报表的打印书，其他还有好多没空看，
接触了一段时间的Oracle，感觉PL/SQL不怎么好用，不过时常更新，喜欢Toad for Oracle但是感觉当时还是挺着迷的(主要是看Oracle的一些帮助文件比较方便)，
翻看了一下往日的日记大约还有JDE 报表大约10几篇 | 10几篇Asktom上的文章，不过后来都早忘了，^_^，感慨万千，现在都不记得了。
jdewards.free.fr （法语站点，似乎进不去了...）
www.jdetips.com （刚输入链接，该链接还活着啊...）
www.jdefans.com （当时时不时访问这个站点，似乎早就挂掉了...）
www.erpforum.net （后来冒出来的，模仿上面的站点，抄袭吧，感觉比较讨厌它）
为了弄清楚表之间的关系，当时就哗啦啦的用个程序导出那些表结构
获取表结构的语句 ------------------------------------------------------------------------------------------------------------------------------ -- Table Columns select tdobnm "Table Name", simd, tdsqlc "SQL Column Name", frdsca "Data Dictionary Description", drdl01 "Data Type Description", frdtas "Item Size", tdobnd "Data Dictionary Alias", frowdi "Data Item", frowtp "Data Type Code", tdpseq "Seq Number" from obj7334.f9860, pd7334.f98711 left join dd7334.f9203 on trim(frdtai) = trim(tdobnd) and trim(frlngp) is null and trim(frsyr) is null and trim(frscrn) is null left join dd7334.f9210 on trim(frdtai) = trim(tdobnd) left join prodctl.f0005 on drsy = 'H98' and drrt = 'DT' and trim(drky) = trim(frowtp) where siobnm = tdobnm order by tdobnm ------------------------------------------------------------------------ --查某个表结构的SQL、并排序 select tdobnm "Table Name", simd, tdsqlc "SQL Column Name", frdsca "Data Dictionary Description", drdl01 "Data Type Description", frdtas "Item Size", tdobnd "Data Dictionary Alias", frowdi "Data Item", frowtp "Data Type Code", tdpseq "Seq Number" from obj7334.f9860, pd7334.f98711 left join dd7334.f9203 on trim(frdtai) = trim(tdobnd) and trim(frlngp) is null and trim(frsyr) is null and trim(frscrn) is null left join dd7334.f9210 on trim(frdtai) = trim(tdobnd) left join prodctl.f0005 on drsy = 'H98' and drrt = 'DT' and trim(drky) = trim(frowtp) where siobnm = tdobnm and trim(tdobnm)='F0101' order by tdpseq
--得到当前汇率的代码 create or replace function GetCurrencyRate(mycrcd in varchar, mycrdc in varchar) return number is crcd varchar(3); crdc varchar(3); mycrr number; begin crcd := mycrcd; crdc := mycrdc; select f0015.cxcrr from (select cxcrcd, cxcrdc, max(dt) dt from (select cxcrcd, cxcrdc, TO_DATE(convertodate(cxeft), 'YYYY-MM-DD') dt, cxcrr from proddta.f0015 where TO_DATE(convertodate(cxeft), 'YYYY-MM-DD') <= sysdate and trim(cxcrcd) = crcd and trim(cxcrdc) = crdc group by cxcrcd, cxcrdc, cxeft, cxcrr order by cxcrcd) rate group by cxcrcd, cxcrdc) mytab, proddta.f0015 f0015 where mytab.cxcrcd = f0015.cxcrcd and mytab.cxcrdc = f0015.cxcrdc and mytab.dt = TO_DATE(convertodate(f0015.cxeft), 'YYYY-MM-DD'); return mycrr; end --------------------------------------------------------------------- create or replace procedure mab is declare handle utl_file.file_type; v_diff number; cursor cur_test is select trim(imlitm) || ' | ' || trim(imdsc1) || ' | ' || councs / 10000 || ' | 0 | 0 | 0 | 0 | 0' from proddta.f4101, proddta.f4102 left join proddta.f4105 on ibitm = coitm and ibmcu = comcu and coledg = '07' where imitm = ibitm and trim(ibmcu) in ('FE01') order by imlitm; begin v_diff := dbms_utility.get_time; handle := utl_file.fopen('c:\test', 'ma.txt', 'w'); for v_rid in cur_test loop utl_file.put_line(handle, v_rid.f000); end loop; v_diff := dbms_utility.get_time - v_diff; dbms_output.put_line('execute time:' || to_char(v_diff)); utl_file.fclose(handle); end mab;
--儒略日期转公历函数 create or replace function converToDate(strDate in number) return varchar2 is Mcenture varchar2(1); Myear varchar2(4); Mdays varchar2(3); Mintyear integer; Mintdays integer; Mmonth varchar2(2); Mday integer; Mstrday varchar2(6); Mdate varchar(12); begin Mcenture := substr(to_char(strdate), 1, 1); Myear := substr(to_char(strdate), 2, 2); Mdays := substr(to_char(strdate), 4, 3); Mintyear := to_number(Myear); Mintdays := to_number(Mdays); if (Mcenture = '1') then Myear := '20' || Myear; else Myear := '19' || Myear; end if; Mmonth := '01'; Mday := 1; if (mod(Mintyear, 400) = 0 or (Mod(Mintyear, 4) = 0 and Mod(Mintyear, 100) <> 0)) then if (Mintdays > 0 and Mintdays <= 31) then Mmonth := '01'; Mday := Mintdays; elsif (Mintdays > 31 and Mintdays <= 60) then Mmonth := '02'; Mday := Mintdays - 31; elsif (Mintdays > 60 and Mintdays <= 91) then Mmonth := '03'; Mday := Mintdays - 60; elsif (Mintdays > 91 and Mintdays <= 121) then Mmonth := '04'; Mday := Mintdays - 91; elsif (Mintdays > 121 and Mintdays <= 152) then Mmonth := '05'; Mday := Mintdays - 121; elsif (Mintdays > 152 and Mintdays <= 182) then Mmonth := '06'; Mday := Mintdays - 152; elsif (Mintdays > 182 and Mintdays <= 213) then Mmonth := '07'; Mday := Mintdays - 182; elsif (Mintdays > 213 and Mintdays <= 244) then Mmonth := '08'; Mday := Mintdays - 213; elsif (Mintdays > 244 and Mintdays <= 274) then Mmonth := '09'; Mday := Mintdays - 244; elsif (Mintdays > 274 and Mintdays <= 305) then Mmonth := '10'; Mday := Mintdays - 274; elsif (Mintdays > 305 and Mintdays <= 335) then Mmonth := '11'; Mday := Mintdays - 305; elsif (Mintdays > 335 and Mintdays <= 366) then Mmonth := '12'; Mday := Mintdays - 335; end if; else if (Mintdays > 0 and Mintdays <= 31) then Mmonth := '01'; Mday := Mintdays; elsif (Mintdays > 31 and Mintdays <= 59) then Mmonth := '02'; Mday := Mintdays - 31; elsif (Mintdays > 59 and Mintdays <= 90) then Mmonth := '03'; Mday := Mintdays - 59; elsif (Mintdays > 90 and Mintdays <= 120) then Mmonth := '04'; Mday := Mintdays - 90; elsif (Mintdays > 120 and Mintdays <= 151) then Mmonth := '05'; Mday := Mintdays - 120; elsif (Mintdays > 151 and Mintdays <= 181) then Mmonth := '06'; Mday := Mintdays - 151; elsif (Mintdays > 181 and Mintdays <= 212) then Mmonth := '07'; Mday := Mintdays - 181; elsif (Mintdays > 212 and Mintdays <= 243) then Mmonth := '08'; Mday := Mintdays - 212; elsif (Mintdays > 243 and Mintdays <= 273) then Mmonth := '09'; Mday := Mintdays - 243; elsif (Mintdays > 273 and Mintdays <= 304) then Mmonth := '10'; Mday := Mintdays - 273; elsif (Mintdays > 304 and Mintdays <= 334) then Mmonth := '11'; Mday := Mintdays - 304; elsif (Mintdays > 334 and Mintdays <= 365) then Mmonth := '12'; Mday := Mintdays - 334; end if; end if; if (Mday < 10) then Mstrday := '0' || to_Char(Mday); else Mstrday := to_Char(Mday); end if; if to_char(strDate) = '0' then Mdate := '1900-01-01'; else Mdate := Myear || '-' || Mmonth || '-' || Mstrday; end if; return Mdate; end;
OnionYang@
