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
      Signal

      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.

      SIGNAL Statement

      When working with MySQL stored procedures, managing exceptions is important to prevent abrupt termination of procedures and to provide meaningful error information. This is achieved using the MySQL SIGNAL statement and the DECLARE ... HANDLER statement.

      The MySQL SIGNAL Statement

      The MySQL SIGNAL statement is used to convey error information in stored procedures. It ensures that exceptions are properly handled, preventing sudden procedure termination.

      Exception Handling with DECLARE ... HANDLER

      You can use the DECLARE ... HANDLER statement to effectively manage exceptions in MySQL. It allows you to specify how different types of exceptions should be handled within a stored procedure. By using this statement in conjunction with SIGNAL, you can enable precise control over error handling.

      Customizing Error Messages

      The SIGNAL statement allows for the customization of error messages using the SET MESSAGE_TEXT command. This is helpful for modifying error messages to provide more meaningful feedback to handlers, applications, or clients.

      Syntax

      Following is the syntax of the MySQL SIGNAL Statement 
      SIGNAL 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 B-Tech, M-Tech, BSC and MSC, an error message is generated using the SIGNAL statement 
      DELIMITER //
      CREATE PROCEDURE example(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
      SIGNAL SQLSTATE '01000'
      SET MESSAGE_TEXT = 'Choose from the existing values', MYSQL_ERRNO = 12121;
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
      END IF;
      END //
      DELIMITER ;
      You can call the above procedure to retrieve the result as shown below 
      CALL example('BSC', @fullform);
      You can retrieve the value of the variable using the following SELECT statement 
      SELECT @fullform;
      Following is the output obtained
      @fullform
      Bachelor of Science
      If you pass an invalid value to the procedure, it will generate an error message as shown below 
      CALL example ('BBC', @fullform);
      The output obtained is as follows 
      ERROR 1001 (45000): Given degree is not valid

      Example

      Following is another example demonstrating exception handling with the SIGNAL statement. Here, we declare a condition and use SIGNAL to trigger exceptions based on certain conditions 
      DELIMITER //
      CREATE PROCEDURE example (num INT)
      BEGIN
      DECLARE testCondition CONDITION FOR SQLSTATE '45000';
      IF num < 0 THEN
      SIGNAL SQLSTATE '01000';
      ELSEIF num > 150 THEN
      SIGNAL SQLSTATE '45000';
      END IF;
      END //
      DELIMITER ;
      You can call the above procedure by passing two values as shown below 
      CALL example(15);
      Following is the output obtained 
      Query OK, 0 rows affected (0.00 sec)
      Calling the procedure by passing the second value 
      CALL example(160);
      The result produced is as shown below 
      ERROR 1644 (45000): Unhandled user-defined exception condition

      Example

      You can customize error messages using SET MESSAGE_TEXT with the SIGNAL statement as shown in this modified example 
      DELIMITER //
      CREATE PROCEDURE example (num INT)
      BEGIN
      DECLARE testCondition CONDITION FOR SQLSTATE '45000';
      IF num < 0 THEN
      SIGNAL SQLSTATE '01000';
      ELSEIF num > 150 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Number higher than the limit set';
      END IF;
      END //
      DELIMITER ;
      We get the following output 
      Query OK, 0 rows affected (0.01 sec)
      You can call the above procedure by passing two values as shown below 
      CALL example(20);
      Following is the output obtained 
      Query OK, 0 rows affected (0.00 sec)
      Calling the procedure by passing the second value 
      CALL example(160);
      You can observe in the output below, the error message displayed is customized according to the user 
      ERROR 1644 (45000): Number higher than the limit set