Duration: 2 hours (Lecture: 2 hours, Lab: 2 hours)

Syllabus Topics:

• Lecture:
  • Introduction to MySQL Programming
  • Use of MySQL Programs
  • Introduction to Stored Procedures
  • Benefits of Stored Procedures
  • Procedure Parameters (IN, OUT, INOUT)
• Lab:
  • Creating Procedure without Parameters
  • Creating Procedure with (IN/OUT/INOUT) Parameters

Lecture Notes (In-Depth)

1. Introduction to MySQL Programming

• Definition: MySQL programming involves writing server-side code to perform complex operations within the database, beyond simple SQL queries. This includes stored procedures, functions, triggers, and event schedulers.
• Purpose:
  • Automate repetitive tasks (e.g., generating reports, updating records).
  • Encapsulate business logic in the database for consistency.
  • Improve performance by reducing client-server communication.
  • Enhance security by controlling access to logic and data.
• Components of MySQL Programming:
  • Stored Procedures: Precompiled SQL code stored in the database, executed via a call.
  • Functions: Similar to stored procedures but return a single value.
  • Triggers: Automatically executed in response to specific table events (e.g., INSERT, UPDATE).
  • Events: Scheduled tasks that run at specific times or intervals.
• Key Notes:
  • MySQL uses a procedural extension of SQL (based on SQL/PSM standard).
  • Code is written using a block structure (BEGIN ... END) with support for variables, loops, and conditionals.

2. Use of MySQL Programs

• Applications:
  • Data Manipulation: Automate complex updates or calculations (e.g., updating employee bonuses based on performance).
  • Batch Processing: Process large datasets (e.g., monthly payroll calculations).
  • Data Validation: Enforce business rules (e.g., check inventory levels before order processing).
  • Reporting: Generate summaries or aggregated data (e.g., sales reports by department).
• Execution:
  • Programs are stored in the database and executed via CALL (for procedures) or function invocation.
  • Can be called from MySQL clients, applications, or other database programs.
• Tools:
  • MySQL Workbench: GUI for creating and debugging programs.
  • MySQL Shell: Supports scripting in SQL, JavaScript, or Python.
  • Command-line clients (e.g., mysql).

3. Introduction to Stored Procedures

• Definition: A stored procedure is a precompiled set of SQL statements stored in the database under a name, which can be invoked using the CALL statement.

• Syntax:

  CREATE PROCEDURE procedure_name ([parameters])
  BEGIN
      -- SQL statements
  END;
  
  

• Example:

  CREATE PROCEDURE GetEmployeeCount()
  BEGIN
      SELECT COUNT(*) AS emp_count FROM employees;
  END;
  CALL GetEmployeeCount();
  
  

• Key Features:

  • Stored in the database, accessible across sessions.
  • Can include multiple SQL statements, including DML, DDL, and control structures.
  • Supports parameters for dynamic behavior.