p_cedula VARCHAR(20)

tinyint(4)

BEGIN
  DECLARE i INT DEFAULT 1; DECLARE suma INT DEFAULT 0; DECLARE prod INT DEFAULT 0; DECLARE coef INT DEFAULT 0;
  DECLARE prov INT DEFAULT 0; DECLARE tercer INT DEFAULT 0; DECLARE verif INT DEFAULT 0;
  IF p_cedula IS NULL OR p_cedula NOT REGEXP '^[0-9]{10}$' THEN RETURN 0; END IF;
  SET prov=CAST(SUBSTRING(p_cedula,1,2) AS UNSIGNED); SET tercer=CAST(SUBSTRING(p_cedula,3,1) AS UNSIGNED);
  IF NOT ((prov BETWEEN 1 AND 24) OR prov=30) THEN RETURN 0; END IF;
  IF tercer>=6 THEN RETURN 0; END IF;
  WHILE i<=9 DO
    SET coef = IF(MOD(i,2)=1,2,1);
    SET prod = CAST(SUBSTRING(p_cedula,i,1) AS UNSIGNED)*coef;
    IF prod>9 THEN SET prod=prod-9; END IF;
    SET suma=suma+prod; SET i=i+1;
  END WHILE;
  SET verif=MOD(10-MOD(suma,10),10);
  IF verif=CAST(SUBSTRING(p_cedula,10,1) AS UNSIGNED) THEN RETURN 1; END IF;
  RETURN 0;
END

p_ruc VARCHAR(20)

tinyint(4)

BEGIN
  DECLARE prov INT DEFAULT 0; DECLARE tercer INT DEFAULT 0; DECLARE estab VARCHAR(3) DEFAULT '';
  DECLARE i INT DEFAULT 1; DECLARE suma INT DEFAULT 0; DECLARE coef INT DEFAULT 0; DECLARE residuo INT DEFAULT 0; DECLARE verif INT DEFAULT 0;
  IF p_ruc IS NULL OR p_ruc NOT REGEXP '^[0-9]{13}$' THEN RETURN 0; END IF;
  SET prov=CAST(SUBSTRING(p_ruc,1,2) AS UNSIGNED); SET tercer=CAST(SUBSTRING(p_ruc,3,1) AS UNSIGNED); SET estab=SUBSTRING(p_ruc,11,3);
  IF NOT ((prov BETWEEN 1 AND 24) OR prov=30) THEN RETURN 0; END IF;
  IF estab='000' THEN RETURN 0; END IF;
  IF tercer<6 THEN RETURN fn_ec_validar_cedula(SUBSTRING(p_ruc,1,10)); END IF;
  IF tercer=6 THEN
    SET i=1; SET suma=0;
    WHILE i<=8 DO
      SET coef=CASE i WHEN 1 THEN 3 WHEN 2 THEN 2 WHEN 3 THEN 7 WHEN 4 THEN 6 WHEN 5 THEN 5 WHEN 6 THEN 4 WHEN 7 THEN 3 WHEN 8 THEN 2 END;
      SET suma=suma+(CAST(SUBSTRING(p_ruc,i,1) AS UNSIGNED)*coef); SET i=i+1;
    END WHILE;
    SET residuo=MOD(suma,11); SET verif=IF(residuo=0,0,11-residuo); IF verif=11 THEN SET verif=0; END IF;
    IF verif=CAST(SUBSTRING(p_ruc,9,1) AS UNSIGNED) THEN RETURN 1; END IF;
    RETURN 0;
  END IF;
  IF tercer=9 THEN
    SET i=1; SET suma=0;
    WHILE i<=9 DO
      SET coef=CASE i WHEN 1 THEN 4 WHEN 2 THEN 3 WHEN 3 THEN 2 WHEN 4 THEN 7 WHEN 5 THEN 6 WHEN 6 THEN 5 WHEN 7 THEN 4 WHEN 8 THEN 3 WHEN 9 THEN 2 END;
      SET suma=suma+(CAST(SUBSTRING(p_ruc,i,1) AS UNSIGNED)*coef); SET i=i+1;
    END WHILE;
    SET residuo=MOD(suma,11); SET verif=IF(residuo=0,0,11-residuo); IF verif=11 THEN SET verif=0; END IF;
    IF verif=CAST(SUBSTRING(p_ruc,10,1) AS UNSIGNED) THEN RETURN 1; END IF;
    RETURN 0;
  END IF;
  RETURN 0;
END

p_id VARCHAR(30)

tinyint(4)

BEGIN
  IF p_id IS NULL OR TRIM(p_id)='' THEN RETURN 0; END IF;
  SET p_id=TRIM(p_id);
  IF p_id NOT REGEXP '^[0-9]+$' THEN RETURN 0; END IF;
  IF p_id='9999999999999' THEN RETURN 1; END IF;
  IF CHAR_LENGTH(p_id)=10 THEN RETURN fn_ec_validar_cedula(p_id); END IF;
  IF CHAR_LENGTH(p_id)=13 THEN RETURN fn_ec_validar_ruc(p_id); END IF;
  RETURN 0;
END