@Miri_Skava

Как вызвать хранимую процедуру в Yii2?

Здравствуйте
Пытаюсь вызвать процедуру в yii2:
$result = \Yii::$app->db->createCommand("CALL coverage_dis_prof(@r,:prof, :dis);SELECT @r;")
            ->bindValue(':prof' , 2)
            ->bindValue(':dis', 31)->execute();
        $r=(double)$result['r'];

В результате выводится 0, хотя должно вывестись что-то около 0.07...
В phpmyadmin запрос выводит именно 0.07...
Сама процедура выглядит вот так:
CREATE DEFINER = 'root'@'%'
PROCEDURE catalog.coverage_dis_prof(OUT r double, IN prof int, IN dis int)
BEGIN
  DECLARE allw int;
  DECLARE kd1 int;
  DECLARE kd2 int;
  DECLARE kd int;
  DECLARE k int;
  SELECT
    COUNT(action.id)
  FROM action,
       workfunction,
       general
  WHERE ACTION.id_workfunction = workfunction.id
  AND workfunction.id_general = general.id
  AND general.id_profession = prof INTO allw;
  SELECT
    COUNT(own.id)
  FROM own,
       action,
       workfunction,
       general
  WHERE own.id_discipline = dis
  AND own.id_sort = action.id
  AND ACTION.id_workfunction = workfunction.id
  AND workfunction.id_general = general.id
  AND general.id_profession = prof INTO kd1;
  SELECT
    COUNT(ac_dis.id_discipline)
  FROM ac_dis,
       action,
       workfunction,
       general
  WHERE ac_dis.id_discipline = dis
  AND ac_dis.id_action = action.id
  AND action.id_workfunction = workfunction.id
  AND workfunction.id_general = general.id
  AND general.id_profession = prof INTO kd2;
  SELECT
    kd1 + kd2 INTO kd;
  SELECT
    COUNT(ac_dis.id_discipline)
  FROM ac_dis,
       action,
       own
  WHERE ac_dis.id_action = action.id
  AND own.id_sort = action.id
  AND ac_dis.id_discipline = own.id_discipline INTO k;

  SELECT
    kd - k INTO kd;
  SELECT
    allw + COUNT(knowledge.id)
  FROM knowledge,
       workfunction,
       general
  WHERE knowledge.id_workfunction = workfunction.id
  AND workfunction.id_general = general.id
  AND general.id_profession = prof INTO allw;
  SELECT
    COUNT(know.id)
  FROM know,
       knowledge,
       workfunction,
       general
  WHERE know.id_discipline = dis
  AND know.id_sort = knowledge.id
  AND knowledge.id_workfunction = workfunction.id
  AND workfunction.id_general = general.id
  AND general.id_profession = prof INTO kd1;
  SELECT
    COUNT(kn_dis.id_discipline)
  FROM kn_dis,
       knowledge,
       workfunction,
       general
  WHERE kn_dis.id_discipline = dis
  AND kn_dis.id_knowledge = knowledge.id
  AND knowledge.id_workfunction = workfunction.id
  AND workfunction.id_general = general.id
  AND general.id_profession = prof INTO kd2;
  SELECT
    kd + kd1 + kd2 INTO kd;
  SELECT
    COUNT(kn_dis.id_discipline)
  FROM kn_dis,
       know,
       knowledge
  WHERE kn_dis.id_knowledge = knowledge.id
  AND know.id_sort = knowledge.id
  AND kn_dis.id_discipline = know.id_discipline INTO k;
  SELECT
    kd - k INTO kd;
  SELECT
    allw + COUNT(skill.id)
  FROM skill,
       workfunction,
       general
  WHERE skill.id_workfunction = workfunction.id
  AND workfunction.id_general = general.id
  AND general.id_profession = prof INTO allw;
  SELECT
    COUNT(can.id)
  FROM can,
       skill,
       workfunction,
       general
  WHERE can.id_discipline = dis
  AND id_sort = skill.id
  AND id_workfunction = workfunction.id
  AND workfunction.id_general = general.id
  AND general.id_profession = prof INTO kd1;
  SELECT
    COUNT(id_discipline)
  FROM sk_dis,
       skill,
       workfunction,
       general
  WHERE sk_dis.id_discipline = dis
  AND sk_dis.id_skill = skill.id
  AND skill.id_workfunction = workfunction.id
  AND workfunction.id_general = general.id
  AND general.id_profession = prof INTO kd2;
  SELECT
    kd + kd1 + kd2 INTO kd;
  SELECT
    COUNT(sk_dis.id_discipline)
  FROM sk_dis,
       can,
       skill
  WHERE sk_dis.id_skill = skill.id
  AND can.id_sort = skill.id
  AND sk_dis.id_discipline = can.id_discipline INTO k;
  SELECT
    (kd - k) / allw INTO r;
END

В чем может быть проблема?
  • Вопрос задан
  • 296 просмотров
Решения вопроса 1
@Miri_Skava Автор вопроса
Нашла решение.
Если кому интересно решение заключается в следующем:
$result = \Yii::$app->db->createCommand("CALL coverage_dis_prof(@r,:prof, :dis);")
            ->bindValue(':prof' , 2)
            ->bindValue(':dis', 31)->execute();
$rez=Yii::$app->db->createCommand("SELECT @r;")->queryScalar();
$r=(double)$rez;
Ответ написан
Комментировать
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы