Oracle自定义函数

示例代码：

[sql]  view plain  copy

 

1. CREATE OR REPLACE  FUNCTION "MY_DATABASE"."F_GET_USER_COUNT_BY_DEPART"   
2.     (  
3.     DEPART_ID_VAL in long   
4. )  
5. return varchar2  
6. is  
7. USER_STATE varchar(16);   
8. USER_COUNT number;  
9. begin  
10.      select count(*) into USER_COUNT from TB_USER_INFO where DEPART_ID=DEPART_ID_VAL;  
11.        
12.      if USER_COUNT > 0 then  
13.         USER_STATE:='该部门下有用户';  
14.      else  
15.         USER_STATE:='该部门下无用户';  
16.      end if;  
17.        
18.      return (USER_STATE);  
19. end;  

 

 

解释：

[sql]  view plain  copy

 

1. CREATE OR REPLACE  FUNCTION "数据库名称"."函数名"   
2.     (  
3.     参数一 [in,out] 类型,  
4.     参数二 [in,out] 类型,  
5.     ...   
6. )  
7. return 返回值类型  
8. is  
9.     变量一 类型;  
10.     变量二 类型;  
11.     ... ;  
12. begin  
13.      /*给变量赋值的过程*/  
14.        
15.      return (变量一或变量二或...);  
16. 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;

 

/