Stored Routines: Tips, Tricks, and Solutions

Alexander Nozdrin (Oracle (MySQL division)), George Trujillo (MySQL )
Average rating: ***..
(3.00, 2 ratings)

This presentation is intended to get beyond the basic usage of stored routines. We are going to explain stored routines behavior in detail referring to the underlying MySQL server concepts and design decisions. Basically this presentation will consist of two major blocks: explaining tricky or not obvious features of stored routine behavior and providing examples of advanced use.

Topics will include:

  • Stored routines variables: fields or items?

    Here we’ll talk about two different arithmetics in MySQL: item and field arithmetic. The item-arithmetic is weakly typed and is used in SELECT statements. The field-arithmetic is strictly typed and is used for inserting data into tables. Stored routine variables work according to the field-arithmetic.

  • Ways to implement arrays in stored routines
    • Text lists;
    • Temp tables;
  • Stored routines and Dynamic SQL
    • The need for Dynamic SQL:
      • avoiding SQL injections;
      • dealing with dynamic table and column names;
      • dynamic search condition;
    • Optimization issues;
    • Permission issues;
    • When to use Dynamic SQL and when not; using views instead of Dynamic SQL.

Alexander Nozdrin

Oracle (MySQL division)

Alexander Nozdrin is a software developer at MySQL AB. He has been working for the company since 2005 as a runtime team member. Alexander works mainly on standard-compliance aspects of stored programs, character set support and the client server protocol.

George Trujillo

MySQL

Over 18 years database and developer consulting experience. Has led project efforts in launching new technologies internally to three of the top ten software companies in the world. Primary focus the last seven years has been in working with Fortune 1000 companies in migrating to leading edge database and middleware technologies. George Trujillo is recognized internationally for his expertise in database technology and has been selected as a keynote and master presenter at numerous business and technical conferences. Strong database experience in high availability, performance tuning, replication, clusters and backup and recovery. Middleware experience includes J2EE, Application Development Frameworks, Application Servers, Web Services, XML, SOA, BPEL and Business Rules. George has served on board of directors for different International technology user groups, technology councils for top software companies as well as beta leadership councils for top ten software vendors.

Sponsors
  • Kickfire
  • Zmanda, Inc.
  • Continuent
  • EDS
  • JasperSoft
  • Sun Microsystems
  • Symantec Anti-Virus Software
  • XAware
  • Data Direct Technologies
  • Dolphin Interconnect Solutions
  • Hewlett Packard
  • Infobright, Inc
  • Linagora
  • Microsoft
  • OpSource
  • Oracle
  • Pentaho
  • R1Soft
  • Red Hat
  • Ticketmaster
  • TechRepublic

Contact Us

View a complete list of MySQL contacts.