oracle函數和預存程序樣本

標籤：

--為了使產生的uuid符合rfc 4122的標準(http://tools.ietf.org/html/rfc4122),例如:a8f662b8-6e7a-13fe-e040-970a437c6bd7
--函數
CREATE OR REPLACE
FUNCTION get_uuid
RETURN VARCHAR
IS
guid VARCHAR (50);
BEGIN
guid := lower(RAWTOHEX(sys_guid()));
RETURN
substr(guid,1,8)||‘-‘||substr(guid,9,4)||‘-‘||substr(guid,13,4)||‘-‘||substr(guid,17,4)||‘-‘||substr(guid,21,12);
END get_uuid;




--功能:結轉.比如當前日期為8月31日,8月是沒有資料的,需將七月份的資料拷貝一份作為8月份的

create or replace procedure FYS_SCH_LVYOU2_carryover (syear IN varchar2,smonth  IN varchar2)--注意:是需要結轉的年月
is
     cursor t_rows is select t.* from lvyou2_bak t where t.remarks=smonth and t.year=syear;
     tmonth varchar2(10);
     t_row t_rows%rowtype;
begin
  dbms_output.enable(100000);
  for t_row in t_rows
  loop
     tmonth:=to_char(add_months(to_date(t_row.year||‘-‘||t_row.remarks,‘yyyy-mm‘),1),‘yyyy-mm‘);--結轉到的年月
     dbms_output.put_line(tmonth);
     insert into lvyou2_bak values(   get_uuid(),        --UUID
                                      substr(tmonth,0,4),--year
                                      t_row.classification,
                                      t_row.num,
                                      substr(tmonth,6,2),--month
                                      t_row.remarks1,
                                      t_row.remarks2,
                                      t_row.remarks3,
                                      t_row.remarks4,
                                      t_row.remarks5,
                                      ‘‘,‘‘,‘‘,‘‘,‘‘
                                      );
  end loop;
  commit;
  exception
     when others then rollback;
end;

oracle函數和預存程序樣本