-- Validar o número de identificação fiscal em PL-SQL
-- António Oliveira Santos

CREATE OR REPLACE FUNCTION is_valid_nif (
	v_nif VARCHAR2
) RETURN NUMERIC

IS
	v_c CHAR(1);
	v_i NUMERIC;
	v_cd NUMERIC;
	v_result NUMERIC;

BEGIN
	v_result := 0;

	BEGIN  
		IF LENGTH (v_nif) = 9 AND TO_NUMBER (v_nif) = TO_NUMBER (v_nif) THEN
			v_c := SUBSTR (v_nif, 1, 1);

			IF v_c IN ('1','2','5','6','8','9') THEN
				v_cd := 0;
				v_i := 1;
	
				WHILE (v_i <= 8) LOOP
					v_cd := v_cd + TO_NUMBER (SUBSTR (v_nif, v_i, 1)) * (10 - v_i);
					v_i := v_i + 1;
				END LOOP;
		
				v_cd := 11 - MOD (v_cd, 11);
		
				IF v_cd >= 10 THEN
					v_cd := 0;
				END IF;
		
				IF v_cd = TO_NUMBER (SUBSTR (v_nif, 9, 1)) THEN
					v_result := 1;
				END IF;

			END IF;

		END IF;

		EXCEPTION WHEN OTHERS THEN
			v_result := 0;

	END;

	RETURN v_result;

END;