Процедура Oracle SQL для поиска ставки кредита по сроку, платежу и сумме кредита

11 декабря 2015, SQL, Konstantin

На просторах Интернета не сложно найти SQL функцию, которая считала бы аннуитетный платёж по данным о величине кредита, процентной ставке и сроку кредита. Но вот готовой функции для другой задачи, а именно поиска процентной ставки по данным об аннуитете, сроке и величине кредита, мне найти не удалось. Решение данной задачи не тривиальное и требует применения численных методов. В Excel есть очень удобная для этого функция СТАВКА(), на аналог которой на языке PHP я наткнулся на StackOverflow. Мне оставалось только переписать код для SQL. Результат можно увидеть ниже.

nper — количество платёжных периодов; pmt — аннуитетный платёж; pv — величина кредита. Для поиска решения используется метод хорд.

CREATE OR REPLACE FUNCTION RATE (nper NUMBER, pmt NUMBER, pv NUMBER) RETURN NUMBER
  AS v_result NUMBER;
  fv NUMBER:= 0.0;
  s_type INT:= 0;
  guess NUMBER := 0.1;
  FINANCIAL_MAX_ITERATIONS INT := 5000; --количество итераций
  FINANCIAL_PRECISION NUMBER:= 1.0e-08; --точность
  y NUMBER;
  y0 NUMBER;
  y1 NUMBER;
  f NUMBER;
  i NUMBER;
  x0 NUMBER;
  x1 NUMBER;
BEGIN
v_result := guess;
 
IF (abs(v_result) < FINANCIAL_PRECISION) THEN
y := pv * (1 + nper * v_result) + pmt * (1 + v_result * s_type) * nper + fv;
ELSE
f := EXP(nper * ln(1 + v_result));
y := pv * f + pmt * (1 / v_result + s_type) * (f - 1) + fv;
END IF;
y0 := pv + pmt * nper + fv;
y1 := pv * f + pmt * (1 / v_result + s_type) * (f - 1) + fv;
 
-- поиск корня методом хорд
i := 0;
x0 := 0;
x1 := v_result;
while ((abs(y0 - y1) > FINANCIAL_PRECISION) AND (i < FINANCIAL_MAX_ITERATIONS)) loop
     v_result := (y1 * x0 - y0 * x1) / (y1 - y0);
     x0 := x1;
     x1 := v_result;
     IF ((nper * abs(pmt)) > (pv - fv)) THEN
         x1 := abs(x1);
     END IF;
     IF (abs(v_result) < FINANCIAL_PRECISION) THEN
         y := pv * (1 + nper * v_result) + pmt * (1 + v_result * s_type) * nper + fv;
     ELSE
         f := EXP(nper * ln(1 + v_result));
         y := pv * f + pmt * (1 / v_result + s_type) * (f - 1) + fv;
     END IF;
     y0 := y1;
     y1 := y;
     i:=i+1;
END loop;
    RETURN v_result;
END;
Теги:
 
1 Комментарии
Комментировать

Функция проверки корректности e-mail на SQL (Oracle)

17 февраля 2015, SQL, Konstantin

Если на этапе заполнения базы данных разработчики не предусмотрели механизма предварительной проверки правильности e-mail, то полезной будет следующая функция. Она возвращает 1, если адрес электронной почты корректен, и код ошибки в противном случае:

CREATE OR REPLACE FUNCTION EMAIL_CHECK (email varchar2)
--проверяет адрес эл. почты 1 - else - not ok
/********************************************/
--КОДЫ ОШИБОК
--0   Пустой email
--99  Нет символа @ или не заполнено имя пользователя или пароль
--100 Введённый email содержит пробелы в имени пользователя.
--101 Точка (.), в имени пользователя не может быть первым символом.
--102 Минус (-), в имени пользователя не может быть первым символом.
--103 Подчёркивание (_), в имени пользователя не может быть первым символом.
--104 Введённый email содержит недопустимые символы в имени пользователя
--105 Введённый email содержит пробелы в домене.
--106 Домен должен содержать хотя бы одну точку (.).
--107 Точка (.), в домене не может быть первым или последним символом.
--108 Введённый email содержит недопустимые символы в домене
--109 Введённый email содержит недопустимые символы в суффиксе домена (то, что написано после последней точки)
/********************************************/
RETURN INT
IS
 R INT:= 1;
 username VARCHAR(200);
 DOMAIN VARCHAR(200);
 msg VARCHAR(255);
 temp_str VARCHAR(200);
 len INT;
 
BEGIN
IF ltrim(rtrim(email)) IS NOT NULL THEN
 username:=LOWER(substr(email,1,instr(email,'@')-1));
 DOMAIN:=LOWER(substr(email,instr(email,'@')+1,LENGTH(email)-instr(email,'@')+1));
 
-- проверка @ и длины имени пользователя и домена
IF LENGTH(username)=0 OR LENGTH(DOMAIN)=0 THEN
    R := 99;
END IF;
 
-- проверка правильности имени пользователя
IF instr(username,' ')!=0 THEN
    R := 100;
END IF;
 
IF substr(username,1,1)='.' THEN
    R := 101;
END IF;
 
IF substr(username,1,1)='-' THEN
    R := 102;
END IF;
 
IF substr(username,1,1)='_' THEN
    R := 103;
END IF;
 
temp_str:=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(username,'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'q',''),'r',''),'s',''),'t',''),'u',''),'v',''),'w',''),'x',''),'y',''),'z',''),'.',''),'_',''),'-',''),'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','');
 
IF LENGTH(temp_str)<>0 THEN
    R := 104;
END IF;
 
 
-- проверка правильности домена
IF instr(DOMAIN,' ')!=0 THEN
    R := 105;
END IF;
 
IF instr(DOMAIN,'.')=0 THEN
    R := 106;
END IF;
 
IF substr(DOMAIN,1,1)='.' OR substr(DOMAIN,LENGTH(DOMAIN),1)='.' THEN
    R := 107;
END IF;
      temp_str:=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DOMAIN,'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'q',''),'r',''),'s',''),'t',''),'u',''),'v',''),'w',''),'x',''),'y',''),'z',''),'.',''),'-',''),'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','');
 
IF LENGTH(temp_str)<>0 THEN
    R := 108;
END IF;
 
len:=LENGTH(DOMAIN);
while len>0 loop
   IF substr(DOMAIN,len,1)='.' THEN
     exit;
   END IF;
   len := len - 1;
END loop;
 
temp_str:=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(substr(DOMAIN,len+1,LENGTH(DOMAIN)-len+1),'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'q',''),'r',''),'s',''),'t',''),'u',''),'v',''),'w',''),'x',''),'y',''),'z','');
 
IF LENGTH(temp_str)<>0 THEN
    R := 109;
END IF;
 
ELSE
  R := 0;
END IF;
  RETURN(R);
END;

Продолжить чтение »

Теги: • •
 
1 Комментарии
Комментировать

На Верх