As mentioned in my earlier post Emulating Oracle Output Functionality, I’ll be speaking at the MySQL Users Conference on the topic of MySQL for Oracle Developers. Here is the second in a series of points regarding current MySQL Stored Procedures and Functions functionality.

• 3. Named Parameters
• 4. Procedure Overloading

3. Named Parameters

Parameters passed to Procedures under normal operations can be considered positional parameters. For each parameter, a calling statement is required to pass the same number of parameters and in the same order. Named parameters allows the user to pass parmeters in the order of their choosing, and also not pass all parameters.

For Example, consider the following Oracle Stored Procedure definition.

  EXAMPLE_PROCEDURE(num IN NUMBER DEFAULT 0,
                    dte IN DATE DEFAULT SYSDATE,
                    str IN VARCHAR2 DEFAULT NULL,
                    str2 IN VARCHAR2 DEFAULT NULL);

This can be called in several ways, here are few examples.

  EXAMPLE_PROCEDURE();
  EXAMPLE_PROCEDURE(9,  '01-MAR-2006', 'Hello World', NULL);
  EXAMPLE_PROCEDURE(dte => '01-MAR-2006');
  EXAMPLE_PROCEDURE(dte => '01-MAR-2006', str2 => 'Hello World');

The key to being able to use named parameters, is the addition of the DEFAULT syntax in the variable definition.

Giuseppe Maxia has many MySQL stored procedures, and I applaud his initiate with The MySQL General Purpose Stored Routines Library. Included here is an implementation of handling named parameters. I can’t say I’ve used it personally, however I did browse the code (another Open Source benefit).

4. Procedure Overloading

Oracle provides the capability like Java, which allows for multiple procedures to have the same name, but have a different set of parameter arguments.

Oracle provides the ability to overload a Procedure call with the same number of arguments and varying datatypes, for example, going with the earlier DBMS_OUTPUT.PUT_LINE analogy, Oracle has the following specification.

  DBMS_OUTPUT.PUT_LINE (item IN NUMBER);
  DBMS_OUTPUT.PUT_LINE (item IN VARCHAR2);
  DBMS_OUTPUT.PUT_LINE (item IN DATE);

Likewise, it possible to have procedures with different numbers and types of arguments.

  EXAMPLE_PROCEDURE(num IN NUMBER);
  EXAMPLE_PROCEDURE(num IN NUMBER, str IN VARCHAR2);
  EXAMPLE_PROCEDURE(num IN NUMBER, dte IN DATE);
  EXAMPLE_PROCEDURE(num IN NUMBER, dte IN DATE, str IN VARCHAR2, str2 IN VARCHAR2);
etc.

Notes

As MySQL growth continues and developers take up placing greater business logic in the database with Stored Procedures, there may be merit to consider these points. On the flip side, 10 years ago, business logic was placed in the database for many reasons including the extent of capable client programming languages, a Client/Server architecture and of course tight coupling with Oracles GUI products. One could argue a justification as to how much business logic is stored in the database, and how much is managed within an application. Given the advent of the Web, and multiple applications and clients assessing coporate data, it’s logical to place essential logic as close to the source of the data as possible, and the introduction of Stored Procedures in MySQL 5 released last year provided the capacity to consider this.

At this time, I would err on the conservative side with the use of Stored Procedures. The type of application, requirements and signficantly the age of the product (being a new product or existing legacy product) all affect the outcome. As mentioned, Giuseppe feels strongly regarding named parameters, and has provided a workaround. The benefit with Open Source is this FREEDOM clearly exists when the community contributes. This can only benefit MySQL in areas such as Stored Procedures.

Myself, only a few days ago over the weekend, I took an Java Open Source product, that lacked the capacity to support the calling of Stored Procedures via JDBC, developed it myself and then submitted my work back to the Apache community. I was even more happy when it was accepted unaltered, committed and in the nightly build for the next day. Read More. I’m now working on the next contribution to the same project, providing JDBC Transaction Support. While this may seem a sidetrack, I’m actually specifically using this product in testing and usability of MySQL, so ultimately everybody wins.

I stand corrected on my earlier post Emulating Oracle Output Functionality (which I’ve updated) when I made a reference to MySQL catching up. That was not what I was implying, that MySQL had to catchup to Oracle. I was indeed making reference that MySQL is a 10 year old product, Oracle almost 30, and with a far greater historical R&D budget, MySQL as an organisation has for lack of a better term, entered the race at a later time. One could also argue it’s not a race, I’m just using this analogy.

For those that might have thought this in reading my earlier article, I’d encourage you to re-read my updated introduction for my clarification and correction.

The comment was made to me, “Oracle is very good at being Oracle” which is totally correct, and the “MySQL’s market now overlaps that of Oracle, and so there is competition.” Indeed Oracle, as a leader in the RDBMS industry has directed development in functionality and features.

We (being the MySQL product and Open Source) are indeed in competition (with other RDBMS products, both commercial and open source) , and will always be, however those that know me would also know that differing products serve to provide differing features and functionality in enterprise solutions. Some have strengths over others, and also weaknesses.

I am a strong advocate of larger enterprises currently using Oracle to embrace both Oracle and MySQL within an organisation for the benefit of serving differing purposes. I do not believe they are mutually exclusive. I believe there needs to be a looser coupling in the emerging marketplace. Of course, if a new company was starting tomorrow, and wanted to use Oracle, I would question the requirements as MySQL will serve the needs for most practical enterprise solutions. Had that question been asked 5 years ago, it would depend signficantly on the requirements, 10 years ago it would not have been a consideration.

Updated 28-mar-2006
There really is no way to do a comparision by numbers in features and functionality when it comes to Oracle and MySQL in the area of Stored Procedures and Triggers. Oracle does provide a far greater and extensive features list, however having more only means it has more.

We all know this, MySQL is a growing evolving database product, having just celebrated it’s tenth anniversary, Oracle on the other hand, will next year be 30, and has had significant funding in R&D being up until recently the second largest software company world wide. However, being open source, MySQL has the advantages of being lean and mean, providing only functionality users actually want and use, and has the community and organisation that is focussed clearly on providing new and improved functionality that users need.

I’ll have a lot more to speak about on this topic at my MySQL User Conference presentation MySQL for Oracle Developers next month, however in the preceeding weeks I thought I’d open some discussion and debate on various features, particularly in the area of Stored Procedures.

Here are some initial points, I’d like to talk about today. The purpose of this discussion is to highlight the difference between these two products, and possibilly other RDBMS products. While a RDBMS offers a feature, I am not stating that MySQL should necessarily also have this feature, however as part of comparing products in these articles, I am comparing existing Oracle Functionality to MySQL, so it will generally involve the description of functionality that is not presently comparable.

1. Package Space
2. Provider Supplied Procedures

1. Package Space

MySQL provides for Stored Procedures and Functions only the definition of individual procedures and functions. There is no way to group them into logical groupings.

Oracle provides the functionality of Package Space. Groups of procedures and functions can be defined within a package. Consider it like a wrapper of a group of procedures and functions. The package definition consists of 2 parts, the header section, which defines the scope of the procedures and functions, and the body which defines the actual procedures and functions. An added advantage of this that within Oracle you can protect your package body code (making it unavailable for view). I’ll discuss this more at a later time.

I’d like to see MySQL consider a package syntax particularly as businesses start to work more extensively with stored procedures and functions in larger enterprise applications. UPDATE: As stated by Roland in the comments, you can register your support for this functionality at MySQL under Bug #11696.

Within MySQL you can emulate package name, at least in syntax. You can use a dot ‘.’ within a procedure name, unlike this restriction for tables. The down side is the call must be quoted. I’ll discuss this more next.

2. Provider Supplied Procedures

Oracle provides an extensive list of supplied procedures and functions, defined in many packages. One of the earliest you learn in Oracle development is DBMS_OUTPUT.PUT_LINE. As the name indicates, this allows your to output a line of information. This is a common means of debugging output. Andrew Gilfrin post on Debugging Stored Procedures in MySQL raises this point, and an interesting solution using a debugging table. While this functionality works, one of my goals is to provide compability functions so that any migration of code could be simplified. Here is a much simplier solution.

$ more dbms_output.put_line.mysql
DELIMITER //
DROP PROCEDURE `dbms_output.put_line`
//
CREATE PROCEDURE `dbms_output.put_line` (output  VARCHAR(255))
BEGIN
  SELECT output;
END;
//
DELIMITER ;

A MySQL Usage:

mysql> CALL `dbms_output.put_line` ('hello world');


A comparision Oracle Usage:

SQL> SET SERVER OUTPUT ON
SQL> BEGIN
SQL> DBMS_OUTPUT.PUT_LINE ('hello world');
SQL> END;


This is as close the syntax can be. With MySQL you require the CALL and the backqoutes (`). Both things that can be easily added in an automated way.

References:
DBMS_OUTPUT Note: this is a public web version of the Version 8i manual online. Oracle provides online documentation, but you require a login (which is free) to the Oracle Technology Network.

Oracle8i Supplied Packages Reference. Again this is dated information, being an older version, but gives you an indication. For reference Oracle 7 released in 1993 first provided Package/Procedure/Function functionality.

Other

On a side note, I’d really like to see a IF EXISTS syntax for DROP PROCEDURE. IF EXISTS is an excellent MySQL extension. Oracle for stored procedures has the CREATE OR REPLACE syntax.