Pra quem quiser brincar.
Mas não se enganem.... é de principiante, porque ainda sou principiante nisso.
Código: Selecionar todos
<sql>
<ze_calculadigito>
CREATE FUNCTION ze_CalculaDigito(
cNumero VARCHAR(30),
nModulo INT(11)
)
RETURNS VARCHAR(1)
BEGIN
DECLARE nFator INT(11);
DECLARE nSoma INT(11);
DECLARE nCont INT(11);
DECLARE nResto INT(11);
IF LENGTH( cNumero ) < 1 THEN
RETURN '';
END IF;
SET nFator = 2;
SET nSoma = 0;
SET nCont = LENGTH( cNumero );
IF nModulo = 10 THEN
WHILE nCont > 0 DO
SET nSoma = nSoma + ( CAST( SUBSTR( cNumero, nCont, 1 ) AS SIGNED ) * nFator ) ;
SET nFator = nFator + 1;
SET nCont = nCont - 1;
END WHILE;
ELSE
WHILE nCont > 0 DO
SET nSoma = nSoma + ( CAST( SUBSTR( cNumero, nCont, 1 ) AS SIGNED ) * nFator ) ;
IF nFator = 9 THEN
SET nFator = 2;
ELSE
SET nFator = nFator + 1;
END IF;
SET nCont = nCont - 1;
END WHILE;
END IF;
SET nResto = 11 - MOD( nSoma, 11 );
IF nResto > 9 THEN
SET nResto = 0;
END IF;
RETURN CAST( nResto AS CHAR(1) );
END
</ze_calculadigito>
<ze_domingodepascoa>
CREATE FUNCTION ze_DomingoDePascoa( nAno INT )
RETURNS DATE
BEGIN
DECLARE nA, nB, nC, nD, nE, nF, nG, nH, nI, nK, nL, nM, nMes, nDia INT;
DECLARE dData DATE;
SET nA = MOD( nAno, 19 );
SET nB = FLOOR( nAno / 100 );
SET nC = MOD( nAno, 100 );
SET nD = FLOOR( nB / 4 );
SET nE = MOD( nB, 4 );
SET nF = FLOOR( ( nB + 8 ) / 25 );
SET nG = FLOOR( ( nB - nF + 1 ) / 3 );
SET nH = MOD( 19 * nA + nB - nD - nG + 15, 30 );
SET nI = FLOOR( nC / 4 );
SET nK = MOD( nC, 4 );
SET nL = MOD( 32 + 2 * nE + 2 * nI - nH - nK, 7 );
SET nM = FLOOR( ( nA + 11 * nH + 22 * nL ) / 451 );
SET nMes = FLOOR( ( nH + nL - 7 * nM + 114 ) / 31 );
SET nDia = MOD( nH + nL - 7 * nM + 114, 31 ) + 1;
SET dData = CONCAT( LPAD( nAno, 4, '0' ), '-', + LPAD( nMes, 2, '0' ), '-', LPAD( nDia, 2, '0' ) );
RETURN dData;
END
</ze_domingodepascoa>
<ze_extenso>
CREATE FUNCTION ze_Extenso( nValor DECIMAL(18,2) )
RETURNS varchar(500)
BEGIN
DECLARE cTxt VARCHAR(500) DEFAULT '';
DECLARE nInteiro DECIMAL(18,0);
DECLARE nDecimal DECIMAL(5,0);
SET nInteiro = FLOOR( nValor );
SET nDecimal = FLOOR( ( nValor - nInteiro ) * 100 );
IF nInteiro != 0 THEN
SET cTxt = CONCAT( cTxt, ze_ExtensoNumero( nInteiro ), ' ', IF( nInteiro > 1, 'REAIS', 'REAL' ) );
END IF;
IF nInteiro != 0 AND nDecimal != 0 THEN
SET cTxt = CONCAT( cTxt, ' E ' );
END IF;
IF nDecimal != 0 THEN
SET cTxt = CONCAT( cTxt, ze_ExtensoNumero( nDecimal ), ' ', IF( nDecimal > 1, 'CENTAVOS', 'CENTAVO' ) );
IF nInteiro = 0 THEN
SET cTxt = CONCAT( cTxt, ' DE REAL' );
END IF;
END IF;
SET cTxt = REPLACE( cTxt, 'ILHAO REAIS', 'ILHAO DE REAIS' );
SET cTxt = REPLACE( cTxt, 'ILHOES REAIS', 'ILHOES DE REAIS' );
RETURN cTxt;
END
</ze_extenso>
<ze_extensocentena>
CREATE FUNCTION ze_ExtensoCentena( nValor INT )
RETURNS VARCHAR(500)
BEGIN
DECLARE cTxt VARCHAR(500);
DECLARE nCentena INT;
DECLARE nDezena INT;
SET cTxt = '';
IF nValor > 0 THEN
IF nValor = 100 THEN
SET cTxt = 'CEM';
ELSE
SET nCentena = floor( nValor / 100 );
SET nDezena = nValor - ( nCentena * 100 );
SET cTxt = (
CASE
WHEN nCentena = 1 THEN 'CENTO'
WHEN nCentena = 2 THEN 'DUZENTOS'
WHEN nCentena = 3 THEN 'TREZENTOS'
WHEN nCentena = 4 THEN 'QUATROCENTOS'
WHEN nCentena = 5 THEN 'QUINHENTOS'
WHEN nCentena = 6 THEN 'SEISSENTOS'
WHEN nCentena = 7 THEN 'SETECENTOS'
WHEN nCentena = 8 THEN 'OITOCENTOS'
WHEN nCentena = 9 THEN 'NOVECENTOS'
ELSE ''
END );
IF nDezena != 0 THEN
SET cTxt = CONCAT( cTxt, IF( nCentena = 0, '', ' E ' ), ze_ExtensoDezena( nDezena ) );
END IF;
END IF;
END IF;
RETURN cTxt;
END
</ze_extensocentena>
<ze_extensodezena>
CREATE FUNCTION ze_ExtensoDezena( nValor INT )
RETURNS varchar(500)
BEGIN
DECLARE cTxt VARCHAR(500);
DECLARE nDezena INT;
DECLARE nUnidade INT;
SET cTxt = '';
IF nValor > 0 THEN
IF nValor < 20 THEN
SET cTxt = ze_ExtensoUnidade( nValor );
ELSE
SET nDezena = floor( nValor / 10 );
SET nUnidade = nValor - ( nDezena * 10 );
SET cTxt = (
CASE
WHEN nDezena = 2 THEN 'VINTE'
WHEN nDezena = 3 THEN 'TRINTA'
WHEN nDezena = 4 THEN 'QUARENTA'
WHEN nDezena = 5 THEN 'CINQUENTA'
WHEN nDezena = 6 THEN 'SESSENTA'
WHEN nDezena = 7 THEN 'SETENTA'
WHEN nDezena = 8 THEN 'OITENTA'
WHEN nDezena = 9 THEN 'NOVENTA'
ELSE ''
END );
IF nUnidade != 0 THEN
SET cTxt = CONCAT( cTxt, ' E ', ze_ExtensoUnidade( nUnidade ) );
END IF;
END IF;
END IF;
RETURN cTxt;
END
</ze_extensodezena>
<ze_extensonumero>
CREATE FUNCTION ze_ExtensoNumero( nValor DECIMAL(18,0) )
RETURNS varchar(500)
BEGIN
DECLARE cTxt VARCHAR(500) DEFAULT '';
DECLARE nGrupo INT DEFAULT 0;
DECLARE nValGrupo DECIMAL(15,0);
DECLARE nValResto DECIMAL(15,0);
DECLARE cStrValor VARCHAR(20);
DECLARE cTxtGrupo VARCHAR(20);
DECLARE cTxtThis VARCHAR(500);
IF nValor = 0 THEN
RETURN '*ZERO*';
END IF ;
SET cStrValor = LPAD( ABS( nValor ), 18, '0' );
SET nGrupo = 0;
LOOP_GRUPOS: LOOP
SET nGrupo = nGrupo + 1;
IF nGrupo > 6 THEN
LEAVE LOOP_GRUPOS;
END IF;
SET cTxtGrupo = '';
SET cTxtThis = '';
SET nValGrupo = CAST( SUBSTR( cStrValor, ( nGrupo * 3 ) - 2, 3 ) AS DECIMAL(5,0) );
IF LENGTH( cStrValor ) <= nGrupo * 3 + 4 THEN
SET nValResto = 0;
ELSE
SET nValResto = CAST( SUBSTR( cStrValor, ( nGrupo * 3 ) + 1, 3 ) AS DECIMAL(18,0) );
END IF;
IF nValGrupo > 0 THEN
SET cTxtGrupo = (
CASE
WHEN nGrupo = 6 THEN ''
WHEN nGrupo = 5 THEN 'MIL'
WHEN nGrupo = 4 THEN 'MILHAO'
WHEN nGrupo = 3 THEN 'BILHAO'
WHEN nGrupo = 2 THEN 'TRILHAO'
WHEN nGrupo = 1 THEN 'QUATRILHAO'
WHEN nGrupo = 0 THEN 'QUINTILHAO'
WHEN nGrupo = 0 THEN 'SEPTILHAO'
WHEN nGrupo = 0 THEN 'OCTILHAO'
WHEN nGrupo = 0 THEN 'NONILHAO'
WHEN nGrupo = 0 THEN 'DECILHAO'
ELSE ''
END );
IF nValGrupo > 1 THEN
SET cTxtGrupo = REPLACE( cTxtGrupo, 'AO', 'OES' );
END IF ;
IF LENGTH( cTxt ) <> 0 THEN
IF nValGrupo = FLOOR( nValGrupo / 100 ) * 100 THEN
SET cTxt = CONCAT( cTxt, ' E' );
END IF;
END IF;
IF NOT ( nGrupo = 5 AND nValGrupo = 1 ) THEN
SET cTxt = CONCAT( cTxt, ' ', ze_ExtensoCentena( nValGrupo ), ' ' );
END IF;
SET cTxt = CONCAT( cTxt, cTxtGrupo );
END IF;
END LOOP LOOP_GRUPOS;
RETURN cTxt;
END
</ze_extensonumero>
<ze_extensounidade>
CREATE FUNCTION ze_ExtensoUnidade( nValor INT )
RETURNS varchar(500)
BEGIN
DECLARE cTxt VARCHAR(500);
SET cTxt = (
CASE
WHEN nValor = 1 THEN 'UM'
WHEN nValor = 2 THEN 'DOIS'
WHEN nValor = 3 THEN 'TRES'
WHEN nValor = 4 THEN 'QUATRO'
WHEN nValor = 5 THEN 'CINCO'
WHEN nValor = 6 THEN 'SEIS'
WHEN nValor = 7 THEN 'SETE'
WHEN nValor = 8 THEN 'OITO'
WHEN nValor = 9 THEN 'NOVE'
WHEN nValor = 10 THEN 'DEZ'
WHEN nValor = 11 THEN 'ONZE'
WHEN nValor = 12 THEN 'DOZE'
WHEN nValor = 13 THEN 'TREZE'
WHEN nValor = 14 THEN 'QUATORZE'
WHEN nValor = 15 THEN 'QUINZE'
WHEN nValor = 16 THEN 'DEZESSEIS'
WHEN nValor = 17 THEN 'DEZESSETE'
WHEN nValor = 18 THEN 'DEZOITO'
WHEN nValor = 19 THEN 'DEZENOVE'
ELSE ''
END );
RETURN cTxt;
END
</ze_extensounidade>
<ze_sonumeros>
CREATE FUNCTION ze_SoNumeros(
cValue VARCHAR(20)
)
RETURNS varchar(20) CHARSET latin1
BEGIN
DECLARE cReturn VARCHAR(20) DEFAULT '';
DECLARE nCont INT(11) DEFAULT 1;
WHILE nCont <= LENGTH( cValue ) DO
IF SUBSTR( cValue, nCont, 1 ) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN
set creturn = CONCAT( creturn, SUBSTR( cvalue, ncont, 1 ) );
END if;
SET ncont = ncont + 1;
END WHILE;
RETURN creturn;
END
</ze_sonumeros>
<ze_tercadecarnaval>
CREATE FUNCTION ze_TercaDeCarnaval( nAno INT )
RETURNS DATE
BEGIN
DECLARE dData DATE;
SET dData = DATE_SUB( ze_DomingoDePascoa( nAno ), INTERVAL 47 DAY );
RETURN dData;
END
</ze_tercadecarnaval>
<ze_validcnpj>
CREATE FUNCTION ze_ValidCNPJ( cCnpj VARCHAR(20) )
RETURNS int(11)
BEGIN
DECLARE cNumero VARCHAR(20);
DECLARE lOk INT(11);
SET cNumero = ze_SoNumeros( cCnpj );
IF LENGTH( cNumero ) <> 14 THEN
RETURN IF( LENGTH( cNumero ) = 0, 1, 0 );
END IF;
SET cNumero = LPAD( cNumero, 14, '0' );
SET cNumero = SUBSTR( cNumero, 1, 12 );
SET cNumero = CONCAT( cNumero, ze_CalculaDigito( cNumero, 11 ) );
SET cNumero = CONCAT( cNumero, ze_CalculaDigito( cNumero, 11 ) );
SET lOk = ( Right( cCnpj, 2 ) = Right( cNumero, 2 ) );
IF lOk THEN
SET cCnpj = CONCAT( SUBSTR( cNumero, 1, 2 ), '.',
SUBSTR( cNumero, 3, 3 ), '.',
SUBSTR( cNumero, 6, 3 ), '/',
SUBSTR( cNumero, 9, 4 ), '-',
SUBSTR( cNumero, 13, 2 ) );
END IF;
RETURN lOk;
END
</ze_validcnpj>
<ze_validcpf>
CREATE FUNCTION ze_ValidCPF( cCPF VARCHAR(20) )
RETURNS int(11)
BEGIN
DECLARE cNumero VARCHAR(20);
DECLARE lOk INT(11);
SET cNumero = ze_SoNumeros( CCPF );
IF LENGTH( cNumero ) <> 11 THEN
RETURN IF( LENGTH( cNumero ) = 0, 1, 0 );
END IF;
SET cNumero = LPAD( cNumero, 11, '0' );
SET cNumero = SUBSTR( cNumero, 1, 9 );
SET cNumero = CONCAT( cNumero, ze_CalculaDigito( cNumero, 10 ) );
SET cNumero = CONCAT( cNumero, ze_CalculaDigito( cNumero, 10 ) );
SET lOK = ( RIGHT( cCPF, 2 ) = RIGHT( cNumero, 2 ) );
IF lOK THEN
SET CCPF = CONCAT(
SUBSTR( cNumero, 1, 3 ), '.',
SUBSTR( CNUMERO, 4, 3 ), '.',
SUBSTR( CNUMERO, 7, 3 ), '-',
SUBSTR( cNumero, 10, 2 ) );
END IF;
RETURN lOk;
END
</ze_validcpf>
<ze_validie>
CREATE FUNCTION ze_ValidIE
(
cInscricao VARCHAR(20),
cUF VARCHAR(2)
)
RETURNS INT(11)
BEGIN
DECLARE lOk INT(11);
DECLARE nLen INT(11);
IF cInscricao IN ( 'ISENTO', 'NAO CONTRIBUINTE' ) THEN
RETURN 1;
END IF;
SET nLen = LENGTH( cInscricao );
SET cInscricao = ze_SoNumeros( cInscricao );
/* AC, AL, AP, BA, DF, GO, MA, MG, PE, PR, RJ, RR, RO, SP */
IF cUF = 'AM' THEN RETURN ze_ValidIE_AM( cInscricao ); END IF;
IF cUF = 'CE' THEN RETURN ze_ValidIE_CE( cInscricao ); END IF;
IF cUF = 'ES' THEN RETURN ze_ValidIE_ES( cInscricao ); END IF;
IF cUF = 'MS' THEN RETURN ze_ValidIE_MS( cInscricao ); END IF;
IF cUF = 'MT' THEN RETURN ze_ValidIE_MT( cInscricao ); END IF;
IF cUF = 'PA' THEN RETURN ze_ValidIE_PA( cInscricao ); END IF;
IF cUF = 'PB' THEN RETURN ze_ValidIE_PB( cInscricao ); END IF;
IF cUF = 'PI' THEN RETURN ze_ValidIE_PI( cInscricao ); END IF;
IF cUF = 'RN' THEN RETURN ze_ValidIE_RN( cInscricao ); END IF;
IF cUF = 'RS' THEN RETURN ze_ValidIE_RS( cInscricao ); END IF;
IF cUF = 'SC' THEN RETURN ze_ValidIE_SC( cInscricao ); END IF;
IF cUF = 'SE' THEN RETURN ze_ValidIE_SE( cInscricao ); END IF;
IF cUF = 'TO' THEN RETURN ze_ValidIE_TO( cInscricao ); END IF;
RETURN 0;
END
</ze_validie>
<ze_validie_am>
CREATE FUNCTION ze_ValidIE_AM( cInscricao VARCHAR(20) )
RETURNS INT(11)
BEGIN
IF LENGTH( cInscricao ) <> 9 THEN
RETURN 0;
END IF;
IF ze_CalculaDigito( Substr( cInscricao, 1, 8 ), "11" ) <> Substr( cInscricao, 9, 1 ) THEN
RETURN 0;
END IF;
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 2 ), '.', SUBSTR( cInscricao, 3, 6 ), '-', SUBSTR( cInscricao, 9, 1 ) );
RETURN 1;
END
</ze_validie_am>
<ze_validie_ce>
CREATE FUNCTION ze_ValidIE_CE( cInscricao VARCHAR(20) )
RETURNS INT(11)
BEGIN
IF LENGTH( cInscricao ) <> 9 THEN
RETURN 0;
END IF;
IF ze_CalculaDigito( Substr( cInscricao, 1, 8 ), "11" ) <> Substr( cInscricao, 9, 1 ) THEN
RETURN 0;
END IF;
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 2 ), '.', SUBSTR( cInscricao, 3, 6 ), '-', SUBSTR( cInscricao, 9, 1 ) );
RETURN 1;
END
</ze_validie_ce>
<ze_validie_es>
CREATE FUNCTION ze_ValidIE_ES( cInscricao VARCHAR(20) )
RETURNS INT(11)
BEGIN
IF LENGTH( cInscricao ) <> 9 THEN
RETURN 0;
END IF;
IF ze_CalculaDigito( Substr( cInscricao, 1, 8 ), "11" ) <> Substr( cInscricao, 9, 1 ) THEN
RETURN 0;
END IF;
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 3 ), '.', SUBSTR( cInscricao, 4, 3 ), '.', SUBSTR( cInscricao, 7, 2 ), '-', SUBSTR( cInscricao, 9, 1 ) );
RETURN 1;
END
</ze_validie_es>
<ze_validie_ms>
CREATE FUNCTION ze_ValidIE_MS( cInscricao VARCHAR(20) )
RETURNS INT(11)
BEGIN
IF LENGTH( cInscricao ) <> 9 THEN
RETURN 0;
END IF;
IF SUBSTR( cInscricao, 1, 2 ) <> '28' THEN
RETURN 0;
END IF;
IF ze_CalculaDigito( Substr( cInscricao, 1, 8 ), "11" ) <> Substr( cInscricao, 9, 1 ) THEN
RETURN 0;
END IF;
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 2 ), '.', SUBSTR( cInscricao, 3, 3 ), '.', SUBSTR( cInscricao, 6, 3 ), '.', SUBSTR( cInscricao, 9, 1 ) );
RETURN 1;
END
</ze_validie_ms>
<ze_validie_mt>
CREATE FUNCTION ze_ValidIE_MT( cInscricao VARCHAR(20) )
RETURNS INT(11)
BEGIN
IF LENGTH( cInscricao ) > 11 THEN
RETURN 0;
END IF;
SET cInscricao = LPAD( cInscricao, 11, '0' );
IF ze_CalculaDigito( Substr( cInscricao, 1, 10 ), "11" ) <> Substr( cInscricao, 11, 1 ) THEN
RETURN 0;
END IF;
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 10 ), '-', SUBSTR( cInscricao, 11, 1 ) );
RETURN 1;
END
</ze_validie_mt>
<ze_validie_pa>
CREATE FUNCTION ze_ValidIE_PA( cInscricao VARCHAR(20) )
RETURNS INT(11)
BEGIN
IF LENGTH( cInscricao ) <> 9 THEN
RETURN 0;
END IF;
IF SUBSTR( cInscricao, 1, 2 ) <> '15' THEN
RETURN 0;
END IF;
IF ze_CalculaDigito( Substr( cInscricao, 1, 8 ), "11" ) <> Substr( cInscricao, 9, 1 ) THEN
RETURN 0;
END IF;
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 2 ), '-', SUBSTR( cInscricao, 3, 6 ), '-', SUBSTR( cInscricao, 9, 1 ) );
RETURN 1;
END
</ze_validie_pa>
<ze_validie_pb>
CREATE FUNCTION ze_ValidIE_PB( cInscricao VARCHAR(20) )
RETURNS INT(11)
BEGIN
IF LENGTH( cInscricao ) <> 9 THEN
RETURN 0;
END IF;
IF ze_CalculaDigito( Substr( cInscricao, 1, 8 ), "11" ) <> Substr( cInscricao, 9, 1 ) THEN
RETURN 0;
END IF;
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 8 ), '-', SUBSTR( cInscricao, 9, 1 ) );
RETURN 1;
END
</ze_validie_pb>
<ze_validie_pi>
CREATE FUNCTION ze_ValidIE_PI( cInscricao VARCHAR(20) )
RETURNS INT(11)
BEGIN
IF LENGTH( cInscricao ) <> 9 THEN
RETURN 0;
END IF;
IF ze_CalculaDigito( Substr( cInscricao, 1, 8 ), "11" ) <> Substr( cInscricao, 9, 1 ) THEN
RETURN 0;
END IF;
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 2 ), '.', SUBSTR( cInscricao, 3, 3 ), '.', SUBSTR( cInscricao, 6, 3 ), '-', SUBSTR( cInscricao, 9, 1 ) );
RETURN 1;
END
</ze_validie_pi>
<ze_validie_rn>
CREATE FUNCTION ze_ValidIE_RN( cInscricao VARCHAR(20) )
RETURNS INT(11)
BEGIN
IF LENGTH( cInscricao ) <> 9 THEN
RETURN 0;
END IF;
IF ze_CalculaDigito( Substr( cInscricao, 1, 8 ), "11" ) <> Substr( cInscricao, 9, 1 ) THEN
RETURN 0;
END IF;
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 2 ), '.', SUBSTR( cInscricao, 3, 3 ), '.', SUBSTR( cInscricao, 6, 3 ), '-', SUBSTR( cInscricao, 9, 1 ) );
RETURN 1;
END
</ze_validie_rn>
<ze_validie_rs>
CREATE FUNCTION ze_ValidIE_RS( cInscricao VARCHAR(20) )
RETURNS INT(11)
BEGIN
IF LENGTH( cInscricao ) <> 10 THEN
RETURN 0;
END IF;
IF SUBSTR( cInscricao, 1, 3 ) < '001' THEN
RETURN 0;
END IF;
IF ze_CalculaDigito( Substr( cInscricao, 1, 9 ), "11" ) <> Substr( cInscricao, 10, 1 ) THEN
RETURN 0;
END IF;
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 3 ), '/', SUBSTR( cInscricao, 4, 7 ) );
RETURN 1;
END
</ze_validie_rs>
<ze_validie_sc>
CREATE FUNCTION ZE_ValidIE_SC( cInscricao VARCHAR(20) )
RETURNS INT(11)
BEGIN
IF LENGTH( cInscricao ) <> 9 THEN
RETURN 0;
END IF;
IF ze_CalculaDigito( Substr( cInscricao, 1, 8 ), "11" ) <> Substr( cInscricao, 9, 1 ) THEN
RETURN 0;
END IF;
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 3 ), '.', SUBSTR( cInscricao, 4, 3 ), '.', SUBSTR( cInscricao, 7, 3 ) );
RETURN 1;
END
</ze_validie_sc>
<ze_validie_se>
CREATE FUNCTION ze_ValidIE_SE( cInscricao VARCHAR(20) )
RETURNS INT(11)
BEGIN
IF LENGTH( cInscricao ) <> 9 THEN
RETURN 0;
END IF;
IF ze_CalculaDigito( SUBSTR( cInscricao, 1, 8 ), "11" ) <> SUBSTR( cInscricao, 9, 1 ) THEN
RETURN 0;
END IF;
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 2 ), '.', SUBSTR( cInscricao, 3, 3 ), '.', SUBSTR( cInscricao, 6, 3 ), '-', SUBSTR( cInscricao, 9, 1 ) );
RETURN 1;
END
</ze_validie_se>
<ze_validie_to>
CREATE FUNCTION ze_ValidIE_TO( cInscricao VARCHAR(20) )
RETURNS INT(11)
BEGIN
IF NOT LENGTH( cInscricao ) IN ( 9, 11 ) THEN
RETURN 0;
END IF;
IF SUBSTR( cInscricao, 1, 2 ) <> '29' THEN
RETURN 0;
END IF;
IF LENGTH( cInscricao ) = 11 AND NOT SUBSTR( cInscricao, 3, 2 ) IN ( '01', '02', '03', '99' ) THEN
RETURN 0;
END IF;
IF ze_CalculaDigito( CONCAT( SUBSTR( cInscricao, 1, 2 ), SUBSTR( cInscricao, LENGTH( cInscricao ) - 6, 6 ) ), "11" ) != RIGHT( cInscricao, 1 ) THEN
RETURN 0;
END IF;
IF LENGTH( cInscricao ) = 11 THEN
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 2 ), '.', SUBSTR( cInscricao, 3, 2 ), '.', SUBSTR( cInscricao, 5, 6 ), '-', SUBSTR( cInscricao, 11, 1 ) );
ELSE
SET cInscricao = CONCAT( SUBSTR( cInscricao, 1, 2 ), '.', SUBSTR( cInscricao, 3, 2 ), '.', SUBSTR( cInscricao, 5, 3 ), '-', SUBSTR( cInscricao, 8, 1 ) );
END IF;
RETURN 1;
END
</ze_validie_to>
</sql>
