`
aaron_ch
  • 浏览: 178088 次
  • 性别: Icon_minigender_1
  • 来自: 苏州
社区版块
存档分类
最新评论

Oracle Function

Oracle 
阅读更多

CREATE OR REPLACE FUNCTION TRAINING.c1c3flt_cat_grp2(cof IN VARCHAR2)
RETURN NUMBER
IS
CAT_GRP NUMBER;
BEGIN
       IF cof BETWEEN '604' AND '607' THEN
         CAT_GRP := 1;
       ELSIF (cof IN ('610', '083', '084', '085')) OR (cof BETWEEN '615' and '619') OR
                            (cof BETWEEN '621' and '623') OR (cof BETWEEN '625' and '629') OR
                           (cof BETWEEN '631' and '634') THEN
         CAT_GRP := 1;
       ELSIF cof = '630' THEN
         CAT_GRP := 1;
       ELSIF (cof IN ('644', '699')) OR (cof BETWEEN '640' and '642') THEN
         CAT_GRP := 1;
       ELSIF (cof IN ('081', '082')) OR (cof BETWEEN '501' and '529') OR (cof BETWEEN '561' and '579') THEN
         CAT_GRP := 1;
       ELSIF cof IN ('086', '087', '080') THEN
         CAT_GRP := 1;
       ELSIF cof IN ('599', '593') THEN
         CAT_GRP := 1;
       ELSIF cof IN ('558', '598') THEN
         CAT_GRP := 1;
       ELSIF cof = '010' THEN
         CAT_GRP := 1;
       ELSIF cof BETWEEN '049' AND '056' THEN
         CAT_GRP := 1;
       ELSIF cof = '075' OR (cof BETWEEN '536' and '539') OR (cof BETWEEN '586' and '590') THEN
         CAT_GRP := 1;
       ELSIF (cof BETWEEN '531' and '533') OR (cof BETWEEN '581' and '583') THEN
         CAT_GRP := 1;
       ELSIF cof IN ('555', '595') THEN
         CAT_GRP := 1;
       ELSIF cof IN ('592', '552') THEN
         CAT_GRP := 1;
       ELSIF cof = '596' THEN
         CAT_GRP := 1;
       ELSIF cof BETWEEN '540' AND '548' THEN
         CAT_GRP := 1;
       ELSIF cof IN ('559', '551', '591', '594', '597', '553', '554', '556', '557') THEN
         CAT_GRP := 1;
       ELSIF (cof IN ('442', '448')) OR (cof BETWEEN '435' and '437') THEN
         CAT_GRP := 2;
       ELSIF (cof IN ('076', '472')) OR (cof BETWEEN '465' and '467') OR (cof BETWEEN '474' and '479') THEN
         CAT_GRP := 2;
       ELSIF (cof IN ('397', '403')) OR (cof BETWEEN '390' and '392') THEN
         CAT_GRP := 2;
       ELSIF (cof IN ('064', '078', '079', '322', '328')) OR (cof BETWEEN '315' and '317') THEN
         CAT_GRP := 2;
       ELSIF (cof IN ('028', '029', '128', '129', '312', '313')) OR (cof BETWEEN '300' and '302') THEN
         CAT_GRP := 2;
       ELSIF cof = '001' THEN
         CAT_GRP := 2;
       ELSIF cof = '021' THEN
         CAT_GRP := 2;
       ELSIF cof = '090' THEN
         CAT_GRP := 2;
       ELSIF cof = '107' THEN
         CAT_GRP := 2;
       ELSIF cof BETWEEN '161' AND '166' THEN
         CAT_GRP := 2;
       ELSIF cof = '030' THEN
         CAT_GRP := 2;
       ELSIF cof = '006' THEN
         CAT_GRP := 2;
       ELSIF cof = '004' THEN
         CAT_GRP := 2;
       ELSIF cof = '063' THEN
         CAT_GRP := 2;
       ELSIF cof = '005' THEN
         CAT_GRP := 2;
       ELSIF cof = '066' THEN
         CAT_GRP := 2;
       ELSIF cof = '045' THEN
         CAT_GRP := 2;
       ELSIF cof = '007' THEN
         CAT_GRP := 3;
       ELSIF cof = '130' THEN
         CAT_GRP := 3;
       ELSIF cof = '073' THEN
         CAT_GRP := 3;
       ELSIF cof = '070' THEN
         CAT_GRP := 3;
       ELSIF cof IN ('061', '062') THEN
         CAT_GRP := 3;
       ELSIF cof = '060' THEN
         CAT_GRP := 3;
       ELSIF cof = '008' THEN
         CAT_GRP := 3;
       ELSIF cof = '065' THEN
         CAT_GRP := 3;
       ELSIF cof IN ('019', '096', '098') THEN
         CAT_GRP := 3;
       ELSIF cof = '097' THEN
         CAT_GRP := 3;
       ELSIF cof IN ('018', '020') THEN
         CAT_GRP := 3;
       ELSIF cof = '074' THEN
         CAT_GRP := 3;
       ELSIF cof = '088' THEN
         CAT_GRP := 3;
       ELSIF cof = '089' THEN
         CAT_GRP := 3;
       ELSIF cof = '099' THEN
         CAT_GRP := 3;
       ELSIF (cof IN ('041', '044', '092', '093')) OR (cof BETWEEN '176' and '181') THEN
         CAT_GRP := 3;
       ELSIF cof IN ('046', '047', '048') THEN
         CAT_GRP := 3;
       ELSIF cof = '174' THEN
         CAT_GRP := 3;
       ELSIF cof IN ('168', '169') THEN
         CAT_GRP := 3;
       ELSIF cof IN ('153', '170', '171', '172', '173') THEN
         CAT_GRP := 3;
       ELSIF cof = '167' THEN
         CAT_GRP := 3;
       ELSIF cof = '105' THEN
         CAT_GRP := 3;
       END IF;
       RETURN(CAT_GRP);
END;
/

 

 

CREATE OR REPLACE FUNCTION TRAINING.CHECKUSERTEAM(uid IN VARCHAR2,teamcode_mv IN VARCHAR2) RETURN number AS
    CURSOR c1 IS SELECT * FROM us_userposn WHERE userid=uid;
    c1_rec c1%ROWTYPE;
    tmpstr1 varchar2(5) :='';
    --tmpstr2 varchar2(3000) :='';
 teamcode_mv_tmp varchar2(1000):='';
 teamcode_mv_len number(2) :=0;
 ptr number(2) :=1;
 retVal number := 0;-- o= false
BEGIN
 teamcode_mv_tmp :=teamcode_mv;
 teamcode_mv_len := length(teamcode_mv);
 while ptr<=teamcode_mv_len  loop
    begin
        IF NOT c1%ISOPEN THEN
          OPEN c1;
        END IF;
  tmpstr1:='';
  while substr(teamcode_mv_tmp,1,1) != ';' loop
  begin
     tmpstr1 := tmpstr1||substr(teamcode_mv_tmp,1,1);
     teamcode_mv_tmp :=substr(teamcode_mv_tmp,2,teamcode_mv_len);
     ptr := ptr + 1;
  end;
  end loop;
/*  DBMS_OUTPUT.PUT_LINE(tmpstr1);
  DBMS_OUTPUT.PUT_LINE(uid);
  SELECT TEAMCODE INTO tmpstr2 FROM US_USERPOSN WHERE USERID=uid and teamcode=tmpstr1;
  if tmpstr2 is not null then
  begin
    retVal := true;
    exit;
  end;
  end if;*/
  --funcstr :=funcstr||tmpstr2||';;';
  --DBMS_OUTPUT.PUT_LINE(uid);
        LOOP
          FETCH c1 INTO c1_rec;
          EXIT WHEN c1%NOTFOUND;
       IF c1_rec.teamcode = tmpstr1 THEN
       retVal := 1;
          EXIT;
       END IF;
        END LOOP;
        CLOSE c1;
  if retVal=1 then exit; end if;
  while substr(teamcode_mv_tmp,1,1) = ';' loop
  begin
     teamcode_mv_tmp :=substr(teamcode_mv_tmp,2,teamcode_mv_len);
     ptr := ptr + 1;
  end;
  end loop;
 end;
 end loop;
    --DBMS_OUTPUT.PUT_LINE(uid);
    --DBMS_OUTPUT.PUT_LINE(retVal);
 RETURN retVal;
END;
/

 

 

CREATE OR REPLACE FUNCTION TRAINING.GET_LEAVEDURATIONSWITHDESC(leavedata VARCHAR2)
RETURN varchar2 IS LeaveDurations varchar2(1000);

 v_counter number;
 curr_char char(1);
--lgq
prev_char char(1);
 get_first boolean;
 get_second boolean;
 curr_time_slot varchar2(10);
 first_pos number;
 cm_separator char(3);
 v_leavetype VARCHAR2(100);

BEGIN

 get_first := true;
 get_second := false;
 curr_time_slot := '';
 cm_separator := ';; ';

curr_char := ' ';
 for v_counter in 1..length(leavedata) loop
--lgq
                prev_char := curr_char;
  curr_char := substr(leavedata, v_counter, 1);
  if (get_first = true and (curr_char = '4' OR curr_char = '5' OR curr_char = '6' OR curr_char = '7' OR curr_char = '8' OR curr_char = '9')) then
   select substr(to_char(to_date('01/01/2002 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + (1/(24*2)) * (v_counter-1) , 'hh24:mi:ss'), 1, 8)
   into curr_time_slot
   from dual;
   SELECT valuedesc INTO v_leavetype FROM FW_co_dropdown WHERE colname = 'LEAVETYPE' AND colvalue = curr_char;
   select LeaveDurations || v_leavetype || ' (' || curr_time_slot || '-' into LeaveDurations from dual;
   get_first := false;
   get_second := true;
   first_pos := v_counter;
   goto next_v_counter;
  end if;
--lgq  if (get_second = true and (curr_char = '0' OR curr_char = '1' OR curr_char = '2' OR curr_char = '3')) then
  if (get_second = true and prev_char != curr_char) then
   select substr(to_char(to_date('01/01/2002 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + (1/(24*2)) * (v_counter-1) , 'hh24:mi:ss'), 1, 8)
   into curr_time_slot
   from dual;
   if (curr_time_slot = '00:00:00') then
    curr_time_slot := '23:59:59';
   end if;
   select LeaveDurations || curr_time_slot || ')' || cm_separator into LeaveDurations from dual;
   get_first := true;
   get_second := false;
--lgq start
if (curr_char = '4' or curr_char = '5' or curr_char = '6' or curr_char = '7' or curr_char='8' or curr_char='9') then
   select substr(to_char(to_date('01/01/2002 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + (1/(24*2)) * (v_counter-1) , 'hh24:mi:ss'), 1, 8)
   into curr_time_slot
   from dual;
   SELECT valuedesc INTO v_leavetype FROM FW_co_dropdown WHERE colname = 'LEAVETYPE' AND colvalue = curr_char;
   select LeaveDurations || v_leavetype || ' (' || curr_time_slot || '-' into LeaveDurations from dual;
   get_first := false;
   get_second := true;
   first_pos := v_counter;
   goto next_v_counter;
end if;
--lgq end
  end if;

  <<next_v_counter>>
  if (get_second = true) then
--lgq   if (v_counter = length(leavedata) and (curr_char = '4' OR curr_char = '5' OR curr_char = '6' OR curr_char = '7' OR curr_char_ = '8' OR curr_char = '9')) then
   if (v_counter = length(leavedata) and (curr_char = prev_char)) then
    select substr(to_char(to_date('01/01/2002 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + (1/(24*2)) * (v_counter) , 'hh24:mi:ss'), 1, 8)
    into curr_time_slot
    from dual;
    if (curr_time_slot = '00:00:00') then
     curr_time_slot := '23:59:59';
    end if;
    --SELECT valuedesc INTO v_leavetype FROM FW_co_dropdown WHERE colname = 'LEAVETYPE' AND colvalue = curr_char;
    --select LeaveDurations || v_leavetype || ' on ' || curr_time_slot || cm_separator into LeaveDurations from dual;
    select LeaveDurations || curr_time_slot || ')' || cm_separator into LeaveDurations from dual;
   end if;
--lgq   if (v_counter-1 = first_pos and (curr_char = '0' OR curr_char = '1' OR curr_char = '2' OR curr_char = '3')) then
   if (v_counter-1 = first_pos and (curr_char != prev_char)) then
    if (v_counter = length(leavedata)) then
     select substr(to_char(to_date('01/01/2002 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + (1/(24*2)) * (v_counter-1) , 'hh24:mi:ss'), 1, 8)
     into curr_time_slot
     from dual;
    else
     select substr(to_char(to_date('01/01/2002 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + (1/(24*2)) * (v_counter) , 'hh24:mi:ss'), 1, 8)
     into curr_time_slot
     from dual;
    end if;
    if (curr_time_slot = '00:00:00') then
     curr_time_slot := '23:59:59';
    end if;
    --SELECT valuedesc INTO v_leavetype FROM FW_co_dropdown WHERE colname = 'LEAVETYPE' AND colvalue = curr_char;
    --select LeaveDurations || v_leavetype || ' on ' || curr_time_slot || cm_separator into LeaveDurations from dual;
    select LeaveDurations || curr_time_slot || ')' || cm_separator into LeaveDurations from dual;
   end if;
  end if;
 end loop;

 if (length(LeaveDurations) > 0) then
  return substr(LeaveDurations, 1, length(LeaveDurations) - length(cm_separator));
 else
  return LeaveDurations;
 end if;
END;
/

分享到：
| CakePHP Application
评论
1 楼 jiasky 2010-03-04  
好像是在练书法。
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    oracle function 返回结果集

    ### Oracle Function 返回结果集 #### 知识点概述 在Oracle数据库中，有时我们需要创建一个函数来返回一个结果集（即一系列记录），而非单一值。本文档中的代码示例展示了如何定义一个返回自定义表类型的函数`Ns_...

    oracle function.rar

    oracle function.rar

    oracle function 实现七种银行企业帐对帐

    根据提供的信息，我们可以总结出以下有关Oracle Function在实现银行企业账对账中的应用与细节。 ### Oracle Function在银行企业账对账中的应用 #### 1. **Function Get_RevokeRecImport 的实现** 此函数用于处理...

    Oracle Function Collection

    Decrible the functions using in the Oracle, you could use them to program for PL/SQL

    oracle_function用法

    Oracle 函数用法 Oracle 函数是Oracle数据库中的一种编程对象，它允许用户定义自己的函数，以便在SQL语句中调用。 Oracle 函数的用法可以分为以下几个方面： 函数调用限制 在 Oracle 中，函数可以在 SQL 语句中被...

    oracle-function-执行动态sql(包括DML、DLL、DQL、DCL)

    oracle-function-执行动态sql(包括DML、DLL、DQL、DCL)

    oracle function

    Oracle 函数是数据库管理系统Oracle中的一个重要组成部分，它们是预定义的代码块，用于执行特定的计算或操作。在Oracle SQL中，函数可以嵌入到查询、更新、插入和删除语句中，帮助处理数据并返回单个值。Oracle提供...

    Oracle的几个Function实例

    在"Oracle的几个Function实例"这个主题中，我们将探讨一些常见的Oracle函数，并通过实际示例来理解它们的用法。 1. ** NVL 函数**： NVL函数用于处理空值（NULL）。如果一个字段的值为NULL，NVL函数会将其替换为你...

    oracle-function--api.zip_oracle_oracle API_oracle sql api

    本资料“oracle-function--api.zip”包含了Oracle数据库中的一些核心API和SQL函数，特别是针对字符串、日期和数字类型的处理，对于Oracle开发人员来说极具参考价值。 一、Oracle SQL API Oracle SQL API是Oracle...

    ORACLE函数大全

    Oracle数据库是世界上最广泛使用的数据库系统之一，其强大的功能和丰富的内置函数是其核心优势之一。这份"ORACLE函数大全"文档无疑为学习和使用Oracle数据库的用户提供了宝贵的资源。下面，我们将深入探讨Oracle函数...

    oracle-function-执行动态sql

    execute immediate str_sql into tabtcn; --动态执行DDL语句

    Oracle_function

    在IT领域，尤其是在数据库管理与开发中，Oracle Function扮演着至关重要的角色。它们是数据库编程中的核心组件，允许用户创建可重用的代码块来执行特定任务并返回结果。本文将深入探讨Oracle自定义函数的概念、语法...

    Oracle函数_JDBC常用写法

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统之一，其丰富的内置函数使得数据处理变得更为便捷。JDBC（Java Database Connectivity）则是Java编程语言与各种数据库交互的桥梁，它提供了标准的API来执行SQL...

    ORACLE NO_DATA_FOUND的三种处理办法

    在Oracle数据库中，`NO_DATA_FOUND`异常是当你执行一个SQL查询时，没有找到匹配的记录时抛出的。这个异常通常与PL/SQL编程有关，因为SQL查询在PL/SQL块（如游标、存储过程或函数）中运行。在处理`NO_DATA_FOUND`异常...

    Oracle pl/SQL 优化 华为

    Oracle pl/SQL 优化 华为内部资料

    oracle SQL讲义

    10. **Oracle Function**：Oracle提供了许多内置函数，如TO_DATE, TO_CHAR, NVL等，用于数据转换和处理。 11. **LOAD, UNLOAD指令**：用于导入和导出数据，通常使用SQL*Loader（LOAD）和Export/Import utilities...

    Oracle SQL function.ppt

    Oracle SQL 函数是数据库管理中不可或缺的一部分，它们用于处理和操作数据，提供了一种强大的工具集来执行各种计算、转换和数据提取任务。在Oracle 8i版本中，SQL内置函数涉及了数字格式化和日期格式化等多个方面。 ...

    Oracle 11gR2创建PASSWORD_VERIFY_FUNCTION对应密码复杂度验证函数步骤.doc

    这可以通过创建和应用`PASSWORD_VERIFY_FUNCTION`来实现。`PASSWORD_VERIFY_FUNCTION`允许我们自定义一个PL/SQL函数，该函数将在用户尝试更改密码时进行验证，确保新密码满足预设的复杂度规则。以下是在Oracle 11g ...

Magicbox
Global site tag (gtag.js) - Google Analytics