ORACLE存儲過程--實例篇

1、ORACLE存儲過程-實例篇 上儲過程功能是統(tǒng)計HR系統(tǒng)中當(dāng)前年月員工轉(zhuǎn)正率，不多說了，代碼如下      首先是PACKAGE部分      create or replace package PKG_HR_WCC isPROCEDURE PROC_HR_FORMAL(I_PK_CORP     IN VARCHAR2,           &#

2、160;               I_FORMAL_YEAR IN INTEGER,                           O_RETCODE   &#

3、160; OUT VARCHAR2, -成功標(biāo)志                           O_RETMSG      OUT VARCHAR2);PROCEDURE PROC_HR_FORMAL_RATE(I_PK_DEPT    

4、60;         IN VARCHAR2, -部門主鍵                                I_FORMALACT_DATE    

5、60;  IN VARCHAR2, -應(yīng)轉(zhuǎn)正年月                                I_PROBATION_START_DATE IN VARCHAR2, -入職年月       &

6、#160;                                          O_RETCODE       &#

7、160;      OUT VARCHAR2, -成功標(biāo)志                                O_RETMSG        

8、60;      OUT VARCHAR2,                                O_FORMAL_REAL         

9、; OUT VARCHAR2, -當(dāng)月實際轉(zhuǎn)正人數(shù)                                O_FORMAL_SHOULD        OUT VARCHAR2, -當(dāng)月應(yīng)該轉(zhuǎn)正人數(shù) &

10、#160;                              O_FORMAL_RATE          OUT VARCHAR2); -當(dāng)月轉(zhuǎn)正率end PKG_HR_WCC;接下來是PACKAGE BODY

11、部分create or replace package body PKG_HR_WCC is/*-存儲過程名稱： PROC_HR_FORMAL-存儲過程描述： 統(tǒng)計某年某月員工的轉(zhuǎn)正率-功能：          -功能模塊：      -作者：          王成誠-時間：       

12、0;  2008-4-17-參數(shù)說明：*/PROCEDURE PROC_HR_FORMAL(I_PK_CORP     IN VARCHAR2,                           I_FORMAL_YEAR IN INTEGER,   

13、60;                       O_RETCODE     OUT VARCHAR2, -成功標(biāo)志                  

14、60;        O_RETMSG      OUT VARCHAR2)   AS    v_jan_formal             VARCHAR2(10);    v_feb_formal      

15、60;      VARCHAR2(10);    v_mar_formal             VARCHAR2(10);    v_apr_formal             VARCHAR2(10);   

16、v_may_formal             VARCHAR2(10);    v_jun_formal             VARCHAR2(10);    v_jul_formal         

17、    VARCHAR2(10);    v_aug_formal             VARCHAR2(10);    v_sep_formal             VARCHAR2(10);    v_oct_formal

18、60;            VARCHAR2(10);    v_nov_formal             VARCHAR2(10);    v_dec_formal           

19、60; VARCHAR2(10);    v_probation_start_date   VARCHAR2(10); -入職年月    v_formalact_date         VARCHAR2(10); -應(yīng)轉(zhuǎn)正年月    v_formal_real            VARCHAR

20、2(10); -當(dāng)月實際轉(zhuǎn)正人數(shù)    v_formal_should          VARCHAR2(10); -當(dāng)月應(yīng)該轉(zhuǎn)正人數(shù)    v_formal_real_year       VARCHAR2(10); -當(dāng)年實際轉(zhuǎn)正人數(shù)    v_formal_should_year     VARCHAR2(10

21、); -當(dāng)年應(yīng)該轉(zhuǎn)正人數(shù)    v_formal_real_all        VARCHAR2(10); -當(dāng)月整個公司實際轉(zhuǎn)正人數(shù)    v_formal_should_all      VARCHAR2(10); -當(dāng)月整個公司應(yīng)該轉(zhuǎn)正人數(shù)    v_formal_real_all_year   VARCHAR2(10); -當(dāng)年整個公司實際轉(zhuǎn)正人數(shù) 

22、;   v_formal_should_all_year VARCHAR2(10); -當(dāng)年整個公司應(yīng)該轉(zhuǎn)正人數(shù)    v_formal_rate            VARCHAR2(10); -當(dāng)月轉(zhuǎn)正率    v_formal_year            VARCHAR2(10); -當(dāng)

23、年轉(zhuǎn)正率    v_month                  VARCHAR2(10);    v_unitname BD_CORP.UNITSHORTNAME%TYPE;    v_pk_dept   BD_DEPTDOC.PK_DEPT%TYPE;    v_pkcorp 

24、;   BD_DEPTDOC.PK_CORP%TYPE;    v_deptName BD_DEPTDOC.DEPT_NAME%TYPE;    v_dept_seq BD_DEPTDOC.Dept_Seq%TYPE;    v_dept_code BD_DEPTDOC.DEPT_CODE%TYPE;    v_org_level VARCHAR2(10);    CURSOR CUR_BASEINFO IS -聲明游標(biāo)

25、60;     SELECT ORG.PK_DEPT,             ORG.PK_CORP,             DECODE(ORG.ORGLEVEL,            

26、60;       1,                    ORG.DEPT_NAME,                    2,  

27、                  '' | ORG.DEPT_NAME,                    3,         

28、;           '' | ORG.DEPT_NAME,                    4,               

29、0;    '' | ORG.DEPT_NAME,                    '' | ORG.DEPT_NAME) DEPT_NAME,             ORG.DEPT_CODE,  

30、           ORG.DEPT_SEQ,             ORG.ORGLEVEL        FROM (SELECT LENGTHB(DEPT_SEQ) - LENGTHB(REPLACE(DEPT_SEQ, '.', '') ORGLEVEL, 

31、;                    DEPT.PK_DEPT,                     DEPT.PK_CORP,      &

32、#160;              DEPT.DEPT_NAME,                     DEPT.DEPT_CODE,           

33、;          DEPT_SEQ                FROM BD_DEPTDOC DEPT               WHERE DEPT.PK_CORP = 1001) ORG 

34、0;     ORDER BY DEPT_CODE;BEGIN    DELETE FROM HR_TEMP_FORMAL; -清空表信息    OPEN CUR_BASEINFO; -打開游標(biāo)    -    LOOP      FETCH CUR_BASEINFO        INTO v_pk_dept, v_

35、pkcorp, v_deptName, v_dept_code, v_dept_seq, v_org_level;          EXIT WHEN CUR_BASEINFO%NOTFOUND;          v_month              := 1;   &#

36、160;  v_formal_real_year   := 0;      v_formal_should_year := 0;      LOOP        EXIT WHEN v_month = 13;              IF v_month < 10 TH

37、EN          v_formalact_date := I_FORMAL_YEAR | '0' | v_month; -計算應(yīng)轉(zhuǎn)正年月        ELSE          v_formalact_date := I_FORMAL_YEAR | v_month;     

38、   END IF;              v_probation_start_date := to_char(add_months(to_date(v_formalact_date,                      

39、0;                                      'yyyymm'),         

40、60;                                           -3),      

41、0;                                   'yyyymm'); -計算入職年月            

42、  PROC_HR_FORMAL_RATE(v_pk_dept, -部門主鍵                            v_formalact_date, -應(yīng)轉(zhuǎn)正年月           

43、0;                v_probation_start_date, -入職年月                              

44、60;               O_RETCODE, -成功標(biāo)志                            O_RETMSG,    

45、                        v_formal_real,                        

46、60;   v_formal_should,                            v_formal_rate);        IF O_RETCODE <> 0 THEN   &

47、#160;      RETURN;        END IF;              -將12個月份的轉(zhuǎn)正率插入局部變量中        CASE v_month          WHEN

48、1 THEN            v_jan_formal := v_formal_rate;          WHEN 2 THEN            v_feb_formal := v_formal_rate;     

49、0;    WHEN 3 THEN            v_mar_formal := v_formal_rate;          WHEN 4 THEN            v_apr_formal := v_formal_rate; &#

50、160;        WHEN 5 THEN            v_may_formal := v_formal_rate;          WHEN 6 THEN            v_jun_formal

51、 := v_formal_rate;          WHEN 7 THEN            v_jul_formal := v_formal_rate;          WHEN 8 THEN         

52、;   v_aug_formal := v_formal_rate;          WHEN 9 THEN            v_sep_formal := v_formal_rate;          WHEN 10 THEN    &#

53、160;       v_oct_formal := v_formal_rate;          WHEN 11 THEN            v_nov_formal := v_formal_rate;          WHEN 12 TH

54、EN            v_dec_formal := v_formal_rate;        END CASE;              v_month           &#

55、160;  := v_month + 1;        v_formal_real_year   := v_formal_real_year + v_formal_real;        v_formal_should_year := v_formal_should_year + v_formal_should;      END LOOP;  

56、0;       -計算一年的轉(zhuǎn)正率      IF v_formal_real_year = 0 THEN        IF v_formal_should_year = 0 THEN          v_formal_year := ''      

57、60; ELSE          v_formal_year := '-'        END IF;      ELSE        v_formal_year := round(v_formal_real_year / v_formal_should_year) * 100) | '%

58、'      END IF;          select UNITSHORTNAME        into v_unitname        from BD_CORP       where pk_corp = I_PK_CORP;  

59、;        -插入數(shù)據(jù)      insert into HR_TEMP_FORMAL        (PK_FORMAL,         PK_CORP,         UNITNAME,     

60、    PK_DEPT,         DEPT_NAME,         DEPT_SEQ,         DEPT_CODE,         ORG_LEVEL,      &#

61、160;  JAN_FORMAL,         FEB_FORMAL,         MAR_FORMAL,         APR_FORMAL,         MAY_FORMAL,      &#

62、160;  JUN_FORMAL,         JUL_FORMAL,         AUG_FORMAL,         SEP_FORMAL,         OCT_FORMAL,      &#

63、160;  NOV_FORMAL,         DEC_FORMAL,         YEAR_FORMAL)      values        (formal_sequence.Nextval,         v_p

64、kcorp,         v_unitname,         v_pk_dept,         v_deptName,         v_dept_seq,         v_dept

65、_code,         v_org_level,         v_jan_formal,         v_feb_formal,         v_mar_formal,        

66、; v_apr_formal,         v_may_formal,         v_jun_formal,         v_jul_formal,         v_aug_formal,      &

67、#160;  v_sep_formal,         v_oct_formal,         v_nov_formal,         v_dec_formal,         v_formal_year);     

68、   END LOOP;    -統(tǒng)計整個公司的轉(zhuǎn)正率    v_month                  := 1;    v_formal_real_all_year   := 0;    v_formal_should_all_year := 0; 

69、   LOOP      EXIT WHEN v_month = 13;          IF v_month < 10 THEN        v_formalact_date := I_FORMAL_YEAR | '0' | v_month; -計算應(yīng)轉(zhuǎn)正年月      ELSE  

70、;      v_formalact_date := I_FORMAL_YEAR | v_month;      END IF;          v_probation_start_date := to_char(add_months(to_date(v_formalact_date,           

71、                                                'yyyymm')

72、,                                                  

73、; -3),                                        'yyyymm'); -計算入職年月      &#

74、160;   -統(tǒng)計整個公司當(dāng)月實際轉(zhuǎn)正人數(shù)          select count(*)        into v_formal_real_all        from (select bation_start_date, contract.formalact_date    

75、0;           from EMP_WORK_CONTRACT contract, EMP_BASEINFO baseinfo               where contract.pk_emp = baseinfo.pk_emp          

76、;       and baseinfo.pk_corp = I_PK_CORP                 and contract.numcur = '1'                 and

77、 to_char(bation_start_date, 'yyyymm') =                     v_probation_start_date                 a

78、nd to_char(contract.formalact_date, 'yyyymm') between                     v_probation_start_date and v_formalact_date            

79、60;  order by bation_start_date);          -統(tǒng)計整個公司當(dāng)月應(yīng)轉(zhuǎn)正人數(shù)          select count(*)        into v_formal_should_all        from (selec

80、t bation_start_date, contract.formalact_date                from EMP_WORK_CONTRACT contract, EMP_BASEINFO baseinfo               where contract

81、.pk_emp = baseinfo.pk_emp                 and baseinfo.pk_corp = I_PK_CORP                 and contract.numcur = '1'  

82、0;              and to_char(bation_start_date, 'yyyymm') =                     v_probation_start_date  &#

83、160;            order by bation_start_date);          -統(tǒng)計整個公司當(dāng)月轉(zhuǎn)正率          IF v_formal_real_all = 0 THEN        IF v_

84、formal_should_all = 0 THEN          v_formal_rate := ''        ELSE          v_formal_rate := '-'        END IF;  

85、    ELSE        v_formal_rate := round(v_formal_real / v_formal_should) * 100) | '%'      END IF;          -將12個月份的轉(zhuǎn)正率插入局部變量中      CASE v_month 

86、60;      WHEN 1 THEN          v_jan_formal := v_formal_rate;        WHEN 2 THEN          v_feb_formal := v_formal_rate;     &

87、#160;  WHEN 3 THEN          v_mar_formal := v_formal_rate;        WHEN 4 THEN          v_apr_formal := v_formal_rate;        WHEN 5 THE

88、N          v_may_formal := v_formal_rate;        WHEN 6 THEN          v_jun_formal := v_formal_rate;        WHEN 7 THEN   

89、60;      v_jul_formal := v_formal_rate;        WHEN 8 THEN          v_aug_formal := v_formal_rate;        WHEN 9 THEN       &

90、#160;  v_sep_formal := v_formal_rate;        WHEN 10 THEN          v_oct_formal := v_formal_rate;        WHEN 11 THEN          v_nov_fo

91、rmal := v_formal_rate;        WHEN 12 THEN          v_dec_formal := v_formal_rate;      END CASE;          v_month       

92、;           := v_month + 1;      v_formal_real_all_year   := v_formal_real_all_year +                     

93、0;            v_formal_real_all;      v_formal_should_all_year := v_formal_should_all_year +                     &#

94、160;            v_formal_should_all;    END LOOP;    -統(tǒng)計整個公司一年的轉(zhuǎn)正率    IF v_formal_real_all_year = 0 THEN      IF v_formal_should_all_year = 0 THEN     

95、;   v_formal_year := ''      ELSE        v_formal_year := '-'      END IF;    ELSE      v_formal_year := round(v_formal_real_all_year /  &#

96、160;                          v_formal_should_all_year) * 100) | '%'    END IF;    -插入數(shù)據(jù)    insert into HR_TEMP_FORMAL

97、0;     (PK_FORMAL,       PK_CORP,       UNITNAME,       DEPT_NAME,       JAN_FORMAL,       FEB_FORMAL,    

98、0;  MAR_FORMAL,       APR_FORMAL,       MAY_FORMAL,       JUN_FORMAL,       JUL_FORMAL,       AUG_FORMAL,       SEP

99、_FORMAL,       OCT_FORMAL,       NOV_FORMAL,       DEC_FORMAL,       YEAR_FORMAL)    values      (formal_sequence.Nextval,   

100、    v_pkcorp,       v_unitname,       '公司轉(zhuǎn)正率',       v_jan_formal,       v_feb_formal,       v_mar_formal,   

101、60;   v_apr_formal,       v_may_formal,       v_jun_formal,       v_jul_formal,       v_aug_formal,       v_sep_formal,   

102、60;   v_oct_formal,       v_nov_formal,       v_dec_formal,       v_formal_year);    CLOSE CUR_BASEINFO;    COMMIT;    O_RETCODE := '0'  

103、  O_RETMSG := '成功！'    RETURN;EXCEPTION    WHEN NO_DATA_FOUND THEN      O_RETCODE := '-1'      O_RETMSG := '查找無數(shù)據(jù)！' | SQLERRM;    WHEN OTHERS THEN     

104、 O_RETCODE := '-1'      O_RETMSG := '處理不成功！' | SQLERRM;      ROLLBACK;      RETURN;END PROC_HR_FORMAL;ORACLE存儲過程-實例篇 下/*-存儲過程名稱： PROC_HR_FORMAL_RATE-存儲過程描述： 統(tǒng)計部門轉(zhuǎn)正率-功能：       

105、;   -功能模塊：      -作者：          王成誠-時間：          2008-4-17-參數(shù)說明：*/PROCEDURE PROC_HR_FORMAL_RATE(I_PK_DEPT            

106、  IN VARCHAR2, -部門主鍵                                I_FORMALACT_DATE       IN VARCHAR2, -實際轉(zhuǎn)正年月  

107、60;                             I_PROBATION_START_DATE IN VARCHAR2, -入職年月              

108、60;                                   O_RETCODE              OUT VARCH

109、AR2, -成功標(biāo)志                                O_RETMSG               OUT VARCHA

110、R2,                                O_FORMAL_REAL          OUT VARCHAR2, -當(dāng)月實際轉(zhuǎn)正人數(shù)   &

111、#160;                            O_FORMAL_SHOULD        OUT VARCHAR2, -當(dāng)月應(yīng)該轉(zhuǎn)正人數(shù)        

112、60;                       O_FORMAL_RATE          OUT VARCHAR2) -當(dāng)月轉(zhuǎn)正率   AS    v_formal_real   VARCHAR2(10); -當(dāng)

113、月實際轉(zhuǎn)正人數(shù)    v_formal_should VARCHAR2(10); -當(dāng)月應(yīng)該轉(zhuǎn)正人數(shù)BEGIN    -統(tǒng)計當(dāng)月實際轉(zhuǎn)正人數(shù)    select count(*)      into v_formal_real      from (select dept.dept_seq,          

114、;         bation_start_date,                   contract.formalact_date              from EMP_

115、WORK_CONTRACT contract,                   EMP_BASEINFO      baseinfo,                   BD_DEPT

116、DOC        dept             where contract.pk_emp = baseinfo.pk_emp               and baseinfo.dept_id = dept.pk_dept   

117、            and dept.dept_seq like '%' | I_PK_DEPT | '%'               and contract.numcur = '1'         &

118、#160;     and to_char(bation_start_date, 'yyyymm') =                   I_PROBATION_START_DATE             

119、;  and to_char(contract.formalact_date, 'yyyymm') between                   I_PROBATION_START_DATE and I_FORMALACT_DATE             order

120、 by bation_start_date);    -統(tǒng)計當(dāng)月應(yīng)轉(zhuǎn)正人數(shù)    select count(*)      into v_formal_should      from (select dept.dept_seq,               

121、60;   bation_start_date,                   contract.formalact_date              from EMP_WORK_CONTRACT contract,  

122、;                 EMP_BASEINFO      baseinfo,                   BD_DEPTDOC     &

123、#160;  dept             where contract.pk_emp = baseinfo.pk_emp               and baseinfo.dept_id = dept.pk_dept        

124、0;      and dept.dept_seq like '%' | I_PK_DEPT | '%'               and contract.numcur = '1'               and

125、to_char(bation_start_date, 'yyyymm') =                   I_PROBATION_START_DATE             order by bation_start_date

126、);    -將當(dāng)月實際轉(zhuǎn)正人數(shù)與當(dāng)月應(yīng)該轉(zhuǎn)正人數(shù)輸出    O_FORMAL_REAL   := v_formal_real;    O_FORMAL_SHOULD := v_formal_should;    -統(tǒng)計當(dāng)月轉(zhuǎn)正率    IF v_formal_real = 0 THEN      IF v_formal_should = 0 THEN  &

127、#160;     O_FORMAL_RATE := ''      ELSE        O_FORMAL_RATE := '-'      END IF;    ELSE      O_FORMAL_RATE := round(v_formal_real / v_form

128、al_should) * 100) | '%'    END IF;    O_RETCODE := '0'    O_RETMSG := '成功！'    RETURN;EXCEPTION    WHEN NO_DATA_FOUND THEN      O_RETCODE := '-1'    

129、;  O_RETMSG := '查找無數(shù)據(jù)！' | SQLERRM;    WHEN OTHERS THEN      O_RETCODE := '-1'      O_RETMSG := '處理不成功！' | SQLERRM;      ROLLBACK;      RETURN;END PROC_HR_FO

130、RMAL_RATE;END PKG_HR_WCC;Oracle存儲過程實例1. create or replace procedure GetRecords(name_out out varchar2,age_in in varchar2) as    2. begin    3.    select NAME into name_out from test where AGE = age_in;    4. end;    5.    6. create or replac

131、e procedure insertRecord(UserID in varchar2, UserName in varchar2,UserAge in varchar2) is    7. begin    8.    insert into test values (UserID, UserName, UserAge);    9. end;    首先，在Oracle中創(chuàng)建了一個名為TEST_SEQ的Sequence對象，SQL語句如下： 1. create sequence TEST_SEQ    2. minvalue 100    3. maxvalue 999    4. start with 102    5. increment by 1  &#