MySQL 5.0�ő�̖ڋʂ̓X�g�A�h�v���V�[�W���ł���B�C���X�g�[������5.0���g���āA�X�g�A�h�v���V�[�W���̓���������Ă݂悤�B�i�ҏW�ǁj
�@MySQL 5.0�ő�̖ڋʂ́A���Ƃ����Ă��X�g�A�h�v���V�[�W���ł��B���̓�������ۂɊm���߂Ă݂܂��傤�B
�@��Ƃ��āA�ȉ��̃T���v���e�[�u����p�ӂ��܂��B
# mysql -u root mysql> use test Database changed mysql> desc REGION; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | NO | int(11) | | PRI | NULL | auto_increment | | AREA | varchar(50) | | MUL | | | | PREF | varchar(50) | | MUL | | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.03 sec) mysql> select * from REGION; +----+----------+----------+ | NO | AREA | PREF | +----+----------+----------+ | 1 | �k�C�� | �k�C�� | | 2 | ���k�n�� | ������ | �i�ȗ��j | 47 | ���ꏔ�� | ���ꌧ | +----+----------+----------+ 47 rows in set (0.00 sec)
�@��1�t�B�[���h��ID�A��2�t�B�[���h�ɒn�於�A��3�t�B�[���h�Ɍ������i�[���܂��B�F��������ۂɎ�����悤�ɁA�e�[�u�����쐬����region.sql�t�@�C����p�ӂ��܂����B�_�E�����[�h���Ă����p���������B
�@�f�[�^�x�[�X�́A�C���X�g�[�����ɍ쐬�����test�𗘗p���܂��B
# mysql -u root test < region.sql
�@�ŏ��ɁA�ł��ȒP�ȗ�����Ă����܂��傤�B
mysql> delimiter //�@�i1�j mysql> CREATE PROCEDURE simpleproc1()�@�i2�j -> BEGIN -> SELECT AREA,PREF FROM REGION; -> END -> // Query OK, 0 rows affected (0.00 sec)
�@SQL�N�G���[�̍Ō�́u;�v��t���܂����A�X�g�A�h�v���V�[�W�����L�q����ꍇ�A�X�N���v�g���̃Z���e���X�̏I���ɂ��u;�v���g�p���邽�߁A��ʂ̂��߂Ƀf���~�^�iSQL�N�G���[�̋�蕶���j���i1�j�̂悤�ɂ��ĕύX���܂��B��ł́u//�v�Ƃ��܂������A�u|�v�ȂǁA�C�ӂ̕����ō\���܂���B
�@�i2�j�ŃX�g�A�h�v���V�[�W�����L�q���܂��B��ł́A�X�g�A�h�v���V�[�W���usimpleproc1�v���쐬���Ă��܂��B�X�g�A�h�v���V�[�W���̋L�q�ɂ��Ă͓��Ɍ��y���܂���B�X�g�A�h�v���V�[�W���ɂ��ẮA�ȉ����Q�Ƃ��Ă��������B
�Q�l�F
��MySQL�ЁuStored Procedures and Functions�v
http://www.mysql.com/doc/en/Stored_Procedures.html
���A�ځFSQL���H�u���i19�j�`�i23�j
http://www.atmarkit.co.jp/fnetwork/rensai/sql19/sql1.html
�@simpleproc1�́A�������߂�l���Ȃ��A�ł��V���v���ȗ�ł��B�i3�j�̂悤�ɌĂяo�����ƂŁASELECT AREA,PREF FROM REGION�����s����܂��B���̍ہASQL�N�G���[�̏I�[���u//�v�ɕύX����Ă��邱�Ƃ����Y��Ȃ��B�N�G���[�́A�i4�j�̂悤�ɂ��Ď��s���܂��B
mysql> CALL simpleproc1()�@�i3�j -> //�@�i4�j +----------+----------+ | AREA | PREF | +----------+----------+ | �k�C�� | �k�C�� | | ���k�n�� | ������ | | ���k�n�� | �X�� | | ���k�n�� | �H�c�� | | ���k�n�� | �R�`�� | | ���k�n�� | �{�錧 | | ���k�n�� | ��茧 | �i�ȗ��j
�@���ɖ߂�l��������Ă݂܂��傤�B��قǂ̗�ł́ACALL���ꂽ���_��SELECT AREA,PREF FROM REGION�����s����܂������A���x��CALL�Ō��ʂ�ϐ��ɓn���ASELECT�ŕϐ��̒l�������o���܂��B
mysql> CREATE PROCEDURE simpleproc2(OUT param1 INT) -> BEGIN -> SELECT COUNT(*) INTO param1 FROM REGION;�@�i1�j -> END -> // Query OK, 0 rows affected (0.00 sec)
�@�i1�j�ŁASELECT * INTO�N�G���[���g�p���Ă��܂��BMySQL��SELECT �c�c INTO TABLE�N�G���[�ɑΉ����Ă��炸�AINSERT INTO �c�c TABLE�N�G���[�����Ɏg�p���܂����A�i�[�悪�ϐ��̏ꍇ��SELECT �c�c INTO �ϐ��N�G���[���g�p�\�ł��B�����ł́AREGION�e�[�u���̑����R�[�h����ϐ�param1�Ɉ����n���Ă��܂��B
mysql> CALL simpleproc2(@a)�@�i2�j -> // Query OK, 0 rows affected (0.00 sec) mysql> SELECT @a�@�i3�j -> // +------+ | @a | +------+ | 47 | +------+ 1 row in set (0.00 sec)
�@�i2�j��CALLL�����s����ۂ́A�߂�l����郍�[�J���ϐ����w�肵�܂��B�������邱�ƂŁA�X�g�A�h�v���V�[�W���̌��ʂ��ϐ�@a�Ɋi�[����܂��B@a�̒l�������o���ɂ́A�i3�j�̂悤��SELECT�N�G���[��p���܂��B
Copyright © ITmedia, Inc. All Rights Reserved.