Oracle自定义函数

示例代码：

CREATE OR REPLACE  FUNCTION "MY_DATABASE"."F_GET_USER_COUNT_BY_DEPART"
    (
    DEPART_ID_VAL in long
)
return varchar2
is
USER_STATE varchar(16);
USER_COUNT number;
begin
     select count(*) into USER_COUNT from TB_USER_INFO where DEPART_ID=DEPART_ID_VAL;

     if USER_COUNT > 0 then
        USER_STATE:='该部门下有用户';
     else
        USER_STATE:='该部门下无用户';
     end if;

     return (USER_STATE);
end;

 

 

解释：

CREATE OR REPLACE  FUNCTION "数据库名称"."函数名"
    (
    参数一 [in,out] 类型,
    参数二 [in,out] 类型,
    ...
)
return 返回值类型
is
    变量一 类型;
    变量二 类型;
    ... ;
begin
     /*给变量赋值的过程*/

     return (变量一或变量二或...);
end;

 

1. 参数有两种类型，in或者out；可以创建不带参数的函数；
2. 多个参数之间用“,”隔开；多个变量之间用“;”隔开；
3. CREATE OR REPLACE FUNCTION INFODBA.H3C_GetECADocNumListByECROBID (ECRPUID IN VARCHAR2)
4.    RETURN VARCHAR2
5. /*
6.   用途：依据传入的【ECRPUID】获取该ECR下的所有ECA的单号列表，多条以逗号分隔       
7. */
8. IS
9.    ExecSQL      VARCHAR2 (1000);
10.    RESULT       VARCHAR2 (1000);
11.    TMPESULT     VARCHAR2 (1000);
12. BEGIN
13.  
14.    --1.如果传入的ECR的OBID是空，则直接返回一个空字符串
15.    IF (LENGTH (ECRPUID)<1) THEN
16.      RETURN ('Err:函数参数输入不正确，请联系系统管理员调整SQL！');
17.    END IF;
18.  
19.    --2. 循环查询结果，逐一拼接SQL，执行获取ECA申请单号，并拼接成以逗号为分隔符的字符串
20.    FOR ECAList IN ( SELECT ITEM.PITEM_ID 
21.                       FROM infodba.pimanrelation s2ECRECA,
22.                            infodba.pitem item,
23.                            infodba.pitemrevision itemrevision
24.                      WHERE 1 = 1
25.                            AND itemrevision.ritems_tagu = item.PUID
26.                            AND itemrevision.PUID = s2ECRECA.RSECONDARY_OBJECTU
27.                            AND s2ECRECA.RPRIMARY_OBJECTU = ECRPUID
28.                     )
29.    LOOP        
30.  
31.          --如果有查询结果,则拼接'
32.          IF (NVL(ECAList.PITEM_ID,'NoRecords') != 'NoRecords') THEN
33.                 RESULT := RESULT ||ECAList.PITEM_ID || ',';
34.          END IF;
35.  
36.    END LOOP;
37.  
38.    --截取掉最后一个多余的逗号
39.    RESULT := SUBSTR (RESULT, 0, LENGTH (RESULT) - 1);
40.  
41.    RETURN result;
42.  
43. EXCEPTION
44.    WHEN OTHERS
45.    THEN
46.       RETURN  ('Err:可能的出错原因是从PDM读取的字符串超长，请联系PDM系统管理员处理。');
47. END;
48. /

 

 

DROP TRIGGER INFODBA.TRIGGER_PROTECT_PDATA_UPDATE;

 

CREATE OR REPLACE TRIGGER INFODBA.TRIGGER_PROTECT_PDATA_UPDATE

BEFORE UPDATE ON INFODBA.PDATA referencing old as OLDROW new as NEWROW

for each row

DECLARE   

BEGIN

  

  IF (ABS(LENGTH(:OLDROW.PVAL)-LENGTH(:NEWROW.PVAL))>100) THEN          

    RAISE_APPLICATION_ERROR(-20000, 'Illegal behavior,if there is a need, please contact the administrator to stop the trigger TRIGGER_PROTECT_PDATA_UPDATE.');

  END IF;

END;

 

/