Stored Procedures

What are Stored Procedures ?

MySQL 5.0 finally introduces functionality for Stored Procedures. So what exactly are stored procedures? That is the kind of question that gets database professionals who use other DBMS's raising their eyebrows. Stored procedures have been integral to Oracle, PostgreSQL, DB-2, MS-SQL server and others for years, and it has long been a sore point that MySQL has not had them. But there is no snobbery here - if you are a MySQL newbie, or have been using MySQL for years and want to find out what all the fuss is about, read on. If it is your eyebrows that are raised, and you just want to know how MySQL implements them, you will be relieved to know MySQL stored procedures are very similar to the DB2 implementation, as both are based on the SQL:2003 standard.

A stored procedure is simply a procedure that is stored on the database server. MySQL developers have to date unthinkingly written and stored their procedures on the application (or web) server, mainly because there hasn't been an option. That has been limiting. Some have claimed that there are two schools of thought - one claiming that logic should be in the application, the other saying it should reside in the database. However, most professionals would not bind themselves to one or other viewpoint at all times. As always, there are times when doing either makes sense. Unfortunately, some of the staunchest adherents of the in the application school are only there because until now they have had no choice, and it is what they are used to doing. So why would we want to place logic on the database server?

Why use Stored Procedures ?

• They will run in all environments, and there is no need to recreate the logic. Since they are on the database server, it makes no difference what application environment is used - the stored procedure remains consistent. If your setup involves different clients, different programming languages - the logic remains in one place. Web developers typically make less use of this feature, since the web server and database server are usually closely linked. However, in complex client-server setups, this is a big advantage. The clients are automatically always in sync with the procedure logic as soon as its been updated.
• They can reduce network traffic. Complex, repetitive tasks may require getting results, applying some logic to them, and using this to get more results. If this only has to be done on the database server, there is no need to send result sets and new queries back and forth from application server to database server. Network traffic is a common bottleneck causing performance issues, and stored procedures can help reduce this. More often though, it is the database server itself that is the bottleneck, so this may not be much of an advantage.
  

A Simple Example

stored procedure is simply some SQL statements.This one will simply say 'Hello'.

mysql> CREATE PROCEDURE Hello() SELECT 'Hello';
Query OK, 0 rows affected (0.00 sec)

It is as simple as that. And to call it:

mysql> CALL Hello()\G
*************************** 1. row ***************************
Hello: Hello
1 row in set (0.00 sec)

Hardly useful, but the basics are there. CREATE PROCEDURE sp_name() will define the procedure, and CALL sp_name() will call the procedure.

Parameters

The real benefit of a stored procedure is of course when you can pass values to it, as well as receive values back. The concept of parameters should be familiar to anyone who has had experience with any procedural programming experience.

There are three types of parameter:

• IN: The default. This parameter is passed to the procedure, and can change inside the procedure, but remains unchanged outside.
• OUT: No value is supplied to the procedure (it is assumed to be NULL), but it can be modified inside the procedure, and is available outside the procedure.
• INOUT: The characteristics of both IN and OUT parameters. A value can be passed to the procedure, modified there as well as passed back again.

Mastery of stored procedures does require knowledge of session variables. Most of you probably know how to use session variables already, but if not, the concept is simple. You can assign a value to a variable, and retrieve it later.

Calling Stored Procedures from PHP
Consider the Procedure ViewComment and it has a IN parameter ID. The Procedure created with the following code
DELIMITER $$

DROP PROCEDURE IF EXISTS `Murali_InnoDB`.`ViewComment` $$
CREATE DEFINER=`root`@`%` PROCEDURE `ViewComment`(ID integer)
BEGIN
select * from tblPerson LEFT JOIN tblComment ON (tblComment.fk_PersonID = tblPerson.ID) where tblPerson.ID = ID;
END $$

DELIMITER ;

Then call Procedure ViewComment from a PHP file, it will be described below.

SQL Table Joining

Inner join

All of the joins that you have seen so far have used the natural join syntax—for example, to produce a list of customers and dates on which they placed orders. Remember that if this syntax is available, it will automatically pick the join attributes as those with the same name in both tables (intersection of the schemes). It will also produce only one copy of those attributes in the result table.

        SELECT cFirstName, cLastName, orderDate
       FROM customers NATURAL JOIN orders;

• The join does not consider the pk and fk attributes you have specified. If there are any non-pk/fk attributes that have the same names in the tables to be joined, they will also be included in the intersection of the schemes, and used as join attributes in the natural join. The results will certainly not be correct! This problem might be especially difficult to detect in cases where many natural joins are performed in the same query. Fortunately, you can always specify the join attributes yourself, as we describe next.

• Another keyword that produces the same results (without the potential attribute name problem) is the inner join. With this syntax, you may specify the join attributes in a USING clause. (Multiple join attributes in the USING clause are separated by commas.) This also produces only one copy of the join attributes in the result table. Like the NATURAL JOIN syntax, the USING clause is not supported by all systems.

        SELECT cFirstName, cLastName, orderDate
       FROM customers INNER JOIN orders
         USING (custID);

• The most widely-used (and most portable) syntax for the inner join substitutes an ON clause for the USING clause. This requires you to explicitly specify not only the join attribute names, but the join condition (normally equality). It also requires you to preface (qualify) the join attribute names with their table name, since both columns will be included in the result table. This is the only syntax that will let you use join attributes that have different names in the tables to be joined. Unfortunately, it also allows you to join tables on attributes other than the pk/fk pairs, which was a pre-1992 way to answer queries that can be written in better ways today.

        SELECT cFirstName, cLastName, orderDate
       FROM customers INNER JOIN orders
         ON customers.custID = orders.custID;

• You can save a bit of typing by specifying an alias for each table name (such as c and o in this example), then using the alias instead of the full name when you refer to the attributes. This is the only syntax that will let you join a table to itself, as we will see when we discuss recursive relationships.

        SELECT cFirstName, cLastName, orderDate
       FROM customers c INNER JOIN orders o
         ON c.custID = o.custID;

• All of the join statements above are specified as part of the 1992 SQL standard, which was not widely supported for several years after that. In earlier systems, joins were done with the 1986 standard SQL syntax. Although you shouldn’t use this unless you absolutely have to, you just might get stuck working on an older database. If so, you should recognize that the join condition is placed confusingly in the WHERE clause, along with all of the tests to pick the right rows:

        SELECT cFirstName, cLastName, orderDate
       FROM customers c, orders o
       WHERE c.custID = o.custID;

Outer join

One important effect of all natural and inner joins is that any unmatched PK value simply drops out of the result. In our example, this means that any customer who didn’t place an order isn’t shown. Suppose that we want a list of all customers, along with order date(s) for those who did place orders. To include the customers who did not place orders, we will use an outer join, which may take either the USING or the ON clause syntax.

        SELECT cFirstName, cLastName, orderDate
       FROM customers c LEFT OUTER JOIN orders o
         ON c.custID = o.custID;

All customers and order dates

cfirstname	clastname	orderdate
Tom	Jewett
Alvaro	Monge	2003-07-14
Alvaro	Monge	2003-07-18
Alvaro	Monge	2003-07-20
Wayne	Dick	2003-07-14

• Notice that for customers who placed no orders, any attributes from the Orders table are simply filled with NULL values.

• The word “left” refers to the order of the tables in the FROM clause (customers on the left, orders on the right). The left table here is the one that might have unmatched join attributes—the one from which we want all rows. We could have gotten exactly the same results if the table names and outer join direction were reversed:

        SELECT cFirstName, cLastName, orderDate
       FROM orders o RIGHT OUTER JOIN customers c
         ON o.custID = c.custID;

• An outer join makes sense only if one side of the relationship has a minimum cardinality of zero (as Orders does in this example). Otherwise, the outer join will produce exactly the same result as an inner join (for example, between Orders and OrderLines).

• The SQL standard also allows a FULL outer join, in which unmatched join attributes from either side are paired with null values on the other side. You will probably not have to use this with most well-designed databases.

Evaluation order

Multiple joins in a query are evaluated left-to-right in the order that you write them, unless you use parentheses to force a different evaluation order. (Some database systems require parentheses in any case.) The schemes of the joins are also cumulative in the order that they are evaluated; in RA, this means that

r1 r2 r3 = (r1 r2) r3.

• It is especially important to remember this rule when outer joins are mixed with other joins in a query. For example, if you write:

        SELECT cFirstName, cLastName, orderDate, UPC, quantity
       FROM customers LEFT OUTER JOIN orders
         USING (custID)
         NATURAL JOIN orderlines;

you will lose the customers who haven’t placed orders. They will be retained if you force the second join to be executed first:

        SELECT cFirstName, cLastName, orderDate, UPC, quantity
       FROM customers LEFT OUTER JOIN
         (orders NATURAL JOIN orderlines)
         USING (custID);

Other join types

For sake of completeness, you should also know that if you try to join two tables with no join condition, the result will be that every row from one side is paired with every row from the other side. Mathematically, this is a Cartesian product of the two tables, as you have seen before. It is almost never what you want. In pre-1992 syntax, it is easy to do this accidently, by forgetting to put the join condition in the WHERE clause:

        SELECT cFirstName, cLastName, orderDate
       FROM customers, orders;

• If your system is backward-compatible (most are), you might actually try this just to prove to yourself that the result is pure nonsense. However, if you ever have an occasion to really need a Cartesian product of two tables, use the new cross join syntax to prove that you really mean it. Notice that this example still produces nonsense.

        SELECT cFirstName, cLastName, orderDate
       FROM customers CROSS JOIN orders;

• It is possible, but confusing, to specify a join condition other than equality of two attributes; this is called a non-equi-join. If you see such a thing in older code, it probably represents a WHERE clause or subquery in disguise.

• You may also hear the term self join, which is nothing but an inner or outer join between two attributes in the same table. We’ll look at these when we discuss recursive relationships.

Database Replication in MySQL

MySQL does not claim to be enterprise-ready for nothing, and Yahoo and other high-volume users of MySQL certainly do not run on one database server. There are a number of techniques to handle high volumes, one of which is introduced in this article - MySQL replication.

http://databasejournal.com/features/...le.php/3355201

My Sql

Referential integrity
Referential integrity means that when a record in a table refers to a corresponding record in another table, that corresponding record will exist. To read more about this follow the following links.
1.http://www.databasejournal.com/features/mysql/article.php/10897_2248101_1
2.http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

PHP Developers

Developer Manuals:

1. Download PHP manual

2. Download MySql Manual

3. Download Javascript

Software:

To execute and test php sites you need php and php enabled web servers and db database based web pages. Some control panels includes all these three. You can download these from the following link.

1. Download XAMPP

2. Download WAMPP

3. Download Easy PHP

And Dreamweaver is the best design and development tool for php you can download this from the following link.

4. Dreamweaver 8.0

Latest News and Discussion forums:

To view latest news and discussion forms about php Click here