mysql – 如何防止存储过程的用户传递null？

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 如何防止存储过程的用户传递null？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3067字，纯文字阅读大概需要5分钟。

内容图文

我正在编写一个简单的MySQL存储过程：

DELIMITER $
DROP PROCEDURE IF EXISTS GetUserByCaseId $
CREATE DEFINER = 'DEV_Organization'@'localhost' 
PROCEDURE GetUserByCaseId (IN searchedForId VARCHAR(8)) 
  LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER
BEGIN
  SELECT 
    CaseIdAuthenticator.sid AS sid, 
    CaseIdAuthenticator.caseId AS caseId, 
    User.firstName AS firstName, 
    User.lastName AS lastName, 
    User.position AS position, 
    User.email AS email 
  FROM CaseIdAuthenticator
  INNER JOIN User ON User.sid = CaseIdAuthenticator.sid
  WHERE CaseIdAuthenticator.caseId = searchedForId
  LIMIT 1;
END
$

这有效：

mysql> CALL DEV_Organization.GetUserByCaseId("bro4");
+------+--------+-----------+----------+----------+----------------------+
| sid  | caseId | firstName | lastName | position | email                |
+------+--------+-----------+----------+----------+----------------------+
| 3773 | bro4   | Billy     | O'Neal   |          | billy.oneal@case.edu |
+------+--------+-----------+----------+----------+----------------------+
1 row in set (0.00 sec)

但不幸的是,允许客户端传递NULL：

mysql> CALL DEV_Organization.GetUserByCaseId(NULL);
Empty set (0.00 sec)

我宁愿让这个抛出错误.我怎样才能做到这一点？ (简单地设置VARCHAR(8)NOT NULL类型会导致MySQL在创建过程时抛出错误…)

编辑：评论者要求我的理由.我正在PHP域中使用数据库API,如下所示：

/**
 * Inside function which is used to implement other procedure functions.
 *
 * @param string $suffix The database schema suffix.
 * @param string $procedure The name of the procedure that should be executed.
 * @param array $arguments A set of arguments which should be passed to the stored procedure.
 * @return PDOStatement The PDOStatement generated by sending the query to the MySQL Server.
 */
private function ProcedureInner($suffix, $procedure, $arguments = array())
{
  $suffix = (string)$suffix;
  $procedure = (string)$procedure;
  $questionMarks = '';
  $args = count($arguments);
  if ($args > 0)
  {
    $questionMarks = '?';
    for ($idx = 1; $idx < $args; ++$idx)
    {
      $questionMarks .= ', ?';
    }
  }
  $stmt = $this->pdo->prepare("CALL `{$this->mode}_{$suffix}`.`{$procedure}` ({$questionMarks})");
  $stmt->execute($arguments);
  return $stmt;
}

/**
 * Executes a stored procedure which returns a single value in a single row.
 *
 * @param string $schema The suffix of the schema where the procedure is located.
 * @param string $procedure The name of the procedure to call.
 * @param array $arguments Arguments to supply to the procedure, if any.
 * @return mixed The content of the value.
 */
public function ProcedureScalar($schema, $procedure, $arguments = array())
{
  $result = $this->ProcedureInner($schema, $procedure, $arguments);
  $answer = $result->fetchColumn(0);
  $result->closeCursor();
  return $answer;
}

请注意客户端如何容易忘记在arguments数组中传递参数.但是,这是数据库抽象的低级部分,我不想在这里放置寻找特定参数的约束.

解决方法:

MySQL 5.5及更高版本仅为此目的提供SIGNAL(在用户例程中引发任意异常)：

....
BEGIN
  IF searchedForId IS NULL THEN
    SIGNAL SQLSTATE '45000' -- unhandled user-defined exception
      SET MESSAGE_TEXT = 'Parameter may not be NULL';
  END IF;
  SELECT 
    CaseIdAuthenticator.sid AS sid, 
....

内容总结

以上是互联网集市为您收集整理的mysql – 如何防止存储过程的用户传递null？全部内容，希望文章能够帮你解决mysql – 如何防止存储过程的用户传递null？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端