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
      Cursor

      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.

      Cursors

      The MySQL Cursors

      A MySQL cursor is a pointer that is used to iterate through a table's records. They are used within stored programs such as procedures and functions and have the following features −
      • READ ONLY − Cursors only allow you to read data; you can't make changes to it.
      • Non-Scrollable − Cursors move through records in one direction, from the top to the bottom.
      • Asensitive − Cursors are sensitive to the changes made in the table. Any modification done in the table will be reflected in the cursor.
      The following four operations are used to manage cursors in MySQL:
      • Declare Cursor
      • Open Cursor
      • Fetch Cursor
      • Close Cursor
      Let us now see each of these operations in detail.
      

      Declare Cursor

      The DECLARE statement is used to declare a cursor in a MySQL. Once declared, it is then associated with a SELECT statement to retrieve the records from a table.
      Following is the syntax to declare a cursor 
      DECLARE cursor_name CURSOR FOR select_statement;

      Open Cursor

      The OPEN statement is used to initialize the cursor to retrieve the data after it has been declared.
      Following is the syntax to open a cursor 
      OPEN cursor_name;

      Fetch Cursor

      The FETCH statement is then used to retrieve the record pointed by the cursor. Once retrieved, the cursor moves to the next record.
      Following is the syntax to fetch a cursor 
      FETCH cursor_name INTO variable_list;

      Close Cursor

      The CLOSE statement is used to release the memory associated with the cursor after all the records have been retrieved.
      
      Following is the syntax to close a cursor 
      CLOSE cursor_name;

      Example

      In this example, we see how to manage a cursor in a stored procedure.
      Assume two tables, CUSTOMERS and BACKUP, are created using the CREATE TABLE statement. The CUSTOMERS table holds information like ID, name, age, address, and salary as shown below 
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR (20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),
         PRIMARY KEY (ID)
      );
      Now, we are creating the BACKUP table, having the same structure as the CUSTOMERS table to store a copy of the records from the CUSTOMERS table 
      CREATE TABLE BACKUP (
         ID INT NOT NULL,
         NAME VARCHAR (20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2)
      );
      Now let us insert some records into the CUSTOMERS table using the INSERT statement as follows 
      INSERT INTO CUSTOMERS VALUES 
      (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
      (2, 'Khilan', 25, 'Delhi', 1500.00 ),
      (3, 'Kaushik', 23, 'Kota', 2000.00 ),
      (4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
      (5, 'Hardik', 27, 'Bhopal', 8500.00 ),
      (6, 'Komal', 22, 'Hyderabad', 4500.00 ),
      (7, 'Muffy', 24, 'Indore', 10000.00 );
      The following SQL query creates a cursor on the CUSTOMERS table 
      DECLARE MY_CURSOR CURSOR FOR SELECT * FROM CUSTOMERS;
      Now, we are creating a stored procedure named ExampleProc to manage the cursor from declaration to closure 
      DELIMITER //
      CREATE PROCEDURE ExampleProc()
         BEGIN
            -- Variable declarations
            DECLARE done INT DEFAULT 0;
            DECLARE cust_id, cust_age INTEGER;
            DECLARE cust_name VARCHAR(20);
            DECLARE cust_address CHAR(25);
      	  DECLARE cust_salary DECIMAL(18,2);
      	  
      	  -- Cursor declaration
            DECLARE cur CURSOR FOR SELECT * FROM CUSTOMERS;
      	  
      	  -- Handler for no more records
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
      	  
      	  -- Open the cursor
            OPEN cur;
      	  
            -- Loop to fetch and insert records
            label: LOOP
            FETCH cur INTO cust_id, cust_name, cust_age, cust_address, cust_salary;
            INSERT INTO backup VALUES(cust_id, cust_name, cust_age, cust_address, cust_salary);
            IF done = 1 THEN LEAVE label;
            END IF;
            END LOOP;
      	  
      	  -- Close the cursor
            CLOSE cur;
         END//
      DELIMITER ;

      Output

      Finally, if we call the procedure using CALL ExampleProc(); and check the contents of the BACKUP table, it will contain the same records as CUSTOMERS 
      SELECT * FROM BACKUP;
      The BACKUP table obtained is as follows
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00
      7
      Muffy
      24
      Indore
      10000.00