Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Resignal

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      RESIGNAL Statement

      When working with stored procedures in MySQL, it is important to manage exceptions that may arise during their execution. These exceptions could otherwise lead to an abrupt termination of the procedure.
      To address this issue, MySQL offers a way to handle exceptions through error handlers. These handlers can be declared using the DECLARE ... HANDLER statement.

      The MySQL RESIGNAL Statement

      The MySQL RESIGNAL statement is used to provide error information to handlers, applications, or clients when an exception occurs within a stored procedure.
      
      RESIGNAL is specifically used within error handlers and must always include attributes. These attributes specify the SQL state, error code, and error message to be associated with the raised error.
      

      Customizing Error Messages

      The RESIGNAL statement allows you to customize error messages using the SET MESSAGE_TEXT command, ensuring smoother procedure execution.

      Syntax

      Following is the syntax of the MySQL RESIGNAL Statement 
      RESIGNAL condition_value [SET signal_information_item]
      Where,
      • condition_value represents the error value to be returned, which can be either a "sqlstate_value" or a "condition_name".
      • signal_information_item allows you to set additional information related to the error condition. You can specify various signal information items like CLASS_ORIGIN, SUBCLASS_ORIGIN, MESSAGE_TEXT, MYSQL_ERRNO, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, or CURSOR_NAME.

      Example

      In this example, we create a procedure that accepts the short form of degrees and returns their full forms. If we provide an invalid degree i.e. value other than BBA, BCA, MD and ITI, an error message is generated using the RESIGNAL statement 
      DELIMITER //
      CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form VARCHAR(50))
      BEGIN
      DECLARE wrong_choice CONDITION FOR SQLSTATE '45000';
      DECLARE EXIT HANDLER FOR wrong_choice
      RESIGNAL SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
      IF degree='BBA' THEN SET full_form = 'Bachelor of Business Administration'; 
      ELSEIF degree='BCA' THEN SET full_form = 'Bachelor of Computer Applications'; 
      ELSEIF degree='MD' THEN SET full_form = 'Doctor of Medicine';
      ELSEIF degree='ITI' THEN SET full_form = 'Industrial Training Institute';
      ELSE 
      SIGNAL wrong_choice;
      END IF;
      END //
      DELIMITER ;
      You can call the above procedure to retrieve the result as shown below 
      CALL example('MD', @fullform);
      You can retrieve the value of the variable using the following SELECT statement 
      SELECT @fullform;
      Following is the output obtained
      @fullform
      Doctor of Medicine
      If you pass an invalid value to the procedure, it will generate an error message as follows 
      CALL example ('IIT', @fullform);
      The output obtained is as follows 
      ERROR 1001 (45000): Given degree is not valid

      Handling Warnings with RESIGNAL

      Let us see another example where we do not pass optional attributes to the RESIGNAL statement 
      DELIMITER //
      CREATE PROCEDURE testexample (num INT)
      BEGIN
      DECLARE testCondition1 CONDITION FOR SQLSTATE '01000';
      DECLARE EXIT HANDLER FOR testCondition1 RESIGNAL;
      IF num < 0 THEN
      SIGNAL testCondition1;
      END IF;
      END //
      DELIMITER ;
      You can call the above procedure by passing two values. But, any SQLSTATE value that starts with '01' refers to a warning, so the query is executed with a warning as shown below 
      CALL testexample(-15);
      The output obtained is as follows 
      Query OK, 0 rows affected, 1 warning (0.00 sec)

      Resignal Statement Using Client Program

      We can also perform resignal Using Client Program.

      Syntax

      PHPNodeJSJavaPython
      To perform the resignal statement through a PHP program, we need to execute the "Stored Procedure" using the mysqli function query() as follows 
      $sql = "CREATE PROCEDURE example_new1(IN degree VARCHAR(20), OUT full_form Varchar(50))
      BEGIN
         IF degree='B-Tech' THEN SET full_form = 'Bachelor of Technology'; ELSEIF degree='M-Tech' THEN SET full_form = 'Master of Technology'; ELSEIF degree='BSC' THEN SET full_form = 'Bachelor of Science';
         ELSEIF degree='MSC' THEN SET full_form = 'Master of Science';
         ELSE 
            RESIGNAL SQLSTATE '01000'
         SET MESSAGE_TEXT = 'Choose from the existing values', MYSQL_ERRNO = 12121;
            RESIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
         END IF;
      END";
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $db = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      //printf('Connected successfully.');
      //lets generate error messege using RESIGNAL statement
      $sql = "
      CREATE PROCEDURE example_new1(IN degree VARCHAR(20), OUT full_form Varchar(50))
         BEGIN
            IF degree='B-Tech' THEN SET full_form = 'Bachelor of Technology'; ELSEIF degree='M-Tech' THEN SET full_form = 'Master of Technology'; ELSEIF degree='BSC' THEN SET full_form = 'Bachelor of Science';
            ELSEIF degree='MSC' THEN SET full_form = 'Master of Science';
            ELSE 
               RESIGNAL SQLSTATE '01000'
            SET MESSAGE_TEXT = 'Choose from the existing values', MYSQL_ERRNO = 12121;
               RESIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
            END IF;
         END";
      if($mysqli->query($sql)){
          printf("Resignal statement created successfully....!\n");
      }
      //lets call the above procedure
      $sql = "CALL example_new('BSC', @fullform)";
      if($mysqli->query($sql)){
          printf("Procedure called successfully...!\n");
      }
      //lets retirve the value variable using SELECT statement...
      $sql = "SELECT @fullform";
      if($result = $mysqli->query($sql)){
          printf("Variable value is: \n");
          while($row = mysqli_fetch_array($result)){
              print_r($row);
          }
      }
      if($mysqli->error){
          printf("Error message: ", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as shown below 
      Resignal statement created successfully....!
      Procedure called successfully...!
      Variable value is: 
      Array
      (
         [0] => Bachelor of Science
         [@fullform] => Bachelor of Science
      )