用Mysql存储过程查询你的三险一金及收入

2009年10月09日　作者： 大头刚　

三险即：养老保险、失业保险、医疗保险。
一金即：住房公积金（购房按揭时所用）。

每个地区的缴费比例可能不太一样，有些公司可能是五险一金。下面以北京三险一金为例，用mysql存储过程来查询你的三险一金以及你的收入，呵呵，也许你会发现你到手的收入只有你实际工资的一半哦。（参考了网上的PL/SQL过程）。

例如你的工资为5000元，那么你的实际收入情况如下：

mysql> call calc_salary(5000);
+-----------------------------------------------+
| income_info                                   |
+-----------------------------------------------+
| pension_insurance_1(20.00%)      :    1000.00 |   
| pension_insurance_2(8.00%)       :    400.00  | 
| medical_insurance_1(10.00%)      :    500.00  | 
| medical_insurance_2(2.00%)       :    103.00  | 
| unemployment_insurance_1(1.50%)  :    75.00   | 
| unemployment_insurance_2(0.50%)  :    25.00   | 
| workinjury_insurance_1(0.00%)    :    0.00    | 
| maternity_insurance_1(0.00%)     :    0.00    | 
| house_fund_1(8.00%)              :    400.00  | 
| house_fund_2(8.00%)              :    400.00  | 
| should_tax                       :    2072.00 | 
| tax_ratio                        :    0.15    | 
| susuankouchushu                  :    125     | 
| tax                              :    185.80  | 
| basic_wages                      :    5000    | 
| company_insurance_fund           :    1975.00 | 
| company_cost                     :    6975.00 | /*  公司总成本                     */
| personal_insurance               :    528.00  | 
| total_house_fund                 :    800.00  | 
| real_income                      :    3886.20 | /*  实际收入                       */
| real_income(have house)          :    4686.20 | /*  虚拟收入(包括实收和基金)      */
+-----------------------------------------------+
21 rows in set (0.00 sec)

下面是源代码：

delimiter //
DROP PROCEDURE calc_salary//
CREATE PROCEDURE calc_salary (
     IN p_basic_wages                int                        /*基本工资                            */
)
BEGIN
DECLARE p_pension_insurance_1 DECIMAL(4,2) DEFAULT 0.20;        /* 养老保险公司缴存比例                */
DECLARE p_pension_insurance_2 DECIMAL(4,2) DEFAULT 0.08;        /*   养老保险个人缴存比例              */
DECLARE p_medical_insurance_1 DECIMAL(4,2) DEFAULT 0.10;        /*   医疗保险公司缴存比例              */
DECLARE p_medical_insurance_2 DECIMAL(4,2) DEFAULT 0.02;        /*   医疗保险个人缴存比例+3元          */
DECLARE p_unemployment_insurance_1 DECIMAL(6,4) DEFAULT 0.015; /*   失业保险公司缴存比例              */
DECLARE p_unemployment_insurance_2 DECIMAL(6,4) DEFAULT 0.005; /*   失业保险个人缴存比例              */
DECLARE p_workinjury_insurance_1 DECIMAL(4,2) DEFAULT 0.00;     /*   工伤保险公司缴存比例，个人不交    */
DECLARE p_maternity_insurance_1 DECIMAL(4,2) DEFAULT 0.00;      /*   生育保险公司缴存比例，个人不交    */ 
DECLARE p_house_fund_1 DECIMAL(4,2) DEFAULT 0.08;               /*   住房公积金公司缴存比例            */
DECLARE p_house_fund_2 DECIMAL(4,2) DEFAULT 0.08;               /*   住房公积金个人缴存比例            */
DECLARE p_medical_insurance_fee int DEFAULT 3;                  /*   医疗保险个人工本费3元             */
DECLARE p_tax_start int DEFAULT 2000;                           /*   纳税起征额度                      */
DECLARE v_should_tax DECIMAL(10, 2);      /*应纳税所得额，是基本工资减去起征额度和扣除各项个人缴存的保险基金后的额度*/
DECLARE    v_susuankouchushu         int(10);                  /*  速算扣除数                     */
DECLARE    v_tax                     DECIMAL(10, 2);           /*  税额                           */
DECLARE    v_total_house_fund        DECIMAL(10, 2);           /*  住房公积金总额                 */
DECLARE    v_real_income             DECIMAL(10, 2);           /*  实际收入                       */
DECLARE    v_real_income_have_house  DECIMAL(10, 2);           /*  虚拟收入(包括实收和基金)       */
DECLARE    v_personal_insurance      DECIMAL(10, 2);           /*  个人缴存的保险和基金           */
DECLARE    v_company_insurance_fund  DECIMAL(10, 2);           /*  公司缴存的保险和基金           */
DECLARE    v_company_cost            DECIMAL(10, 2);           /*  公司总成本                     */
DECLARE v_pension_insurance_1 DECIMAL(10, 2);                  /*养老保险公司缴存数额*/ 
DECLARE v_pension_insurance_2 DECIMAL(10, 2);                  /*养老保险个人缴存数额*/ 
DECLARE v_medical_insurance_1 DECIMAL(10, 2);                  /*医疗保险公司缴存数额*/
DECLARE v_medical_insurance_2 DECIMAL(10, 2);                  /*医疗保险个人缴存数额+3元 */
DECLARE v_unemployment_insurance_1 DECIMAL(10, 2);             /*失业保险公司缴存数额   */
DECLARE v_unemployment_insurance_2 DECIMAL(10, 2);             /*失业保险个人缴存数额   */
DECLARE v_workinjury_insurance_1 DECIMAL(10, 2);               /*工伤保险公司缴存数额，个人不交*/
DECLARE v_maternity_insurance_1 DECIMAL(10, 2);                /*生育保险公司缴存数额，个人不交*/
DECLARE v_house_fund_1 DECIMAL(10, 2);                         /*住房公积金公司缴存数额   */
DECLARE v_house_fund_2 DECIMAL(10, 2);                         /*住房公积金个人缴存数额   */
DECLARE    v_tax_ratio               DECIMAL(10, 2);           /*  税率                           */
 
SET v_pension_insurance_1:= p_basic_wages * p_pension_insurance_1;    
SET v_pension_insurance_2:= p_basic_wages * p_pension_insurance_2;      
SET v_medical_insurance_1:= p_basic_wages * p_medical_insurance_1;   
SET v_medical_insurance_2 := p_basic_wages * p_medical_insurance_2 + p_medical_insurance_fee; 
SET v_unemployment_insurance_1:= p_basic_wages * p_unemployment_insurance_1;                  
SET v_unemployment_insurance_2:= p_basic_wages * p_unemployment_insurance_2;                 
SET v_workinjury_insurance_1:= p_basic_wages * p_workinjury_insurance_1; 
SET v_maternity_insurance_1:= p_basic_wages * p_maternity_insurance_1;   
SET v_house_fund_1:= p_basic_wages * p_house_fund_1;    
SET v_house_fund_2:= p_basic_wages * p_house_fund_2; 
SET v_should_tax:=
          p_basic_wages
        - p_tax_start
        - v_pension_insurance_2
        - v_medical_insurance_2
        - v_unemployment_insurance_2
        - v_house_fund_2;
SET v_total_house_fund:= v_house_fund_1 + v_house_fund_2;
SET v_personal_insurance:= v_pension_insurance_2 + v_medical_insurance_2 + v_unemployment_insurance_2;
SET v_company_insurance_fund:=
          v_pension_insurance_1
        + v_medical_insurance_1
        + v_unemployment_insurance_1
        + v_workinjury_insurance_1
        + v_maternity_insurance_1
        + v_house_fund_1;
    /*
    全月应纳税所得额 税率(％) 速算扣除数(元)
    1 不超过500元的 5 0
    2 超过500元至2000元的部分 10 25
    3 超过2000元至5000元的部分 15 125
    4 超过5000元至20000元的部分 20 375
    5 超过20000元至40000元的部分 25 1375
    6 超过40000元至60000元的部分 30 3375
    7 超过60000元至80000元的部分 35 6375
    8 超过80000元至100000元的部分 40 10375
    9 超过100000元的部分 45 15375
        */
    CASE
        WHEN v_should_tax < 500
        THEN
           SET v_tax_ratio := 0.05;
           SET v_susuankouchushu := 0;
        WHEN v_should_tax < 2000
        THEN
            SET v_tax_ratio := 0.10;
            SET v_susuankouchushu := 25;
        WHEN v_should_tax < 5000
        THEN
            SET v_tax_ratio := 0.15;
            SET v_susuankouchushu := 125;
        WHEN v_should_tax < 20000
        THEN
            SET v_tax_ratio := 0.20;
            SET v_susuankouchushu := 375;
        WHEN v_should_tax < 40000
        THEN
            SET v_tax_ratio := 0.25;
            SET v_susuankouchushu := 1375;
        WHEN v_should_tax < 60000
        THEN
            SET v_tax_ratio := 0.30;
            SET v_susuankouchushu := 3375;
        WHEN v_should_tax < 80000
        THEN
            SET v_tax_ratio := 0.35;
            SET v_susuankouchushu := 6375;
        WHEN v_should_tax < 100000
        THEN
            SET v_tax_ratio := 0.40;
            SET v_susuankouchushu := 10375;
        ELSE
            SET v_tax_ratio := 0.45;
            SET v_susuankouchushu := 15375;
    END CASE;
SET v_tax:=v_should_tax * v_tax_ratio - v_susuankouchushu;
SET v_real_income:=v_should_tax + p_tax_start - v_tax;
SET v_real_income_have_house:= v_real_income + v_total_house_fund;
SET v_company_cost:= p_basic_wages + v_company_insurance_fund;
 
SELECT CONCAT('pension_insurance_1(',ROUND(p_pension_insurance_1*100,2),'%)','      :    ',v_pension_insurance_1) AS 
income_info UNION ALL
SELECT CONCAT('pension_insurance_2(',ROUND(p_pension_insurance_2*100,2),'%)','       :    ',v_pension_insurance_2)            
 UNION ALL
SELECT CONCAT('medical_insurance_1(',ROUND(p_medical_insurance_1*100,2),'%)','      :    ',v_medical_insurance_1)             
UNION ALL
SELECT CONCAT('medical_insurance_2(',ROUND(p_medical_insurance_2*100,2),'%)','       :    ',v_medical_insurance_2)            
 UNION ALL
SELECT CONCAT('unemployment_insurance_1(',ROUND(p_unemployment_insurance_1*100,2),'%)','  :    ',v_unemployment_insurance_1)  
UNION ALL
SELECT CONCAT('unemployment_insurance_2(',ROUND(p_unemployment_insurance_2*100,2),'%)','  :    ',v_unemployment_insurance_2)  
UNION ALL
SELECT CONCAT('workinjury_insurance_1(',ROUND(p_workinjury_insurance_1*100,2),'%)','    :    ',v_workinjury_insurance_1)      
UNION ALL
SELECT CONCAT('maternity_insurance_1(',ROUND(p_maternity_insurance_1*100,2),'%)','     :    ',v_maternity_insurance_1)        
UNION ALL
SELECT CONCAT('house_fund_1(',ROUND(p_house_fund_1*100,2),'%)','              :    ',v_house_fund_1)                    
UNION ALL
SELECT CONCAT('house_fund_2(',ROUND(p_house_fund_2*100,2),'%)','              :    ',v_house_fund_2)                     
UNION ALL
SELECT CONCAT('should_tax                       :    ',v_should_tax)                       UNION ALL
SELECT CONCAT('tax_ratio                        :    ',v_tax_ratio)                        UNION ALL
SELECT CONCAT('susuankouchushu                  :    ',v_susuankouchushu)                  UNION ALL
SELECT CONCAT('tax                              :    ',v_tax)                              UNION ALL
SELECT CONCAT('basic_wages                      :    ',p_basic_wages)                      UNION ALL
SELECT CONCAT('company_insurance_fund           :    ',v_company_insurance_fund)           UNION ALL            
SELECT CONCAT('company_cost                     :    ',v_company_cost)                     UNION ALL
SELECT CONCAT('personal_insurance               :    ',v_personal_insurance)               UNION ALL
SELECT CONCAT('total_house_fund                 :    ',v_total_house_fund)                 UNION ALL
SELECT CONCAT('real_income                      :    ',v_real_income)                      UNION ALL
SELECT CONCAT('real_income(have house)          :    ',v_real_income_have_house)             
;
END;
//
标签: , , | 分类 : Mysql










  • Comments (0)
  • Trackbacks (0)
Leave a comment Trackback

No comments yet.

No trackback yet.