MySqlStoredProcedure

UserPreferences

1 개요

5.0 버전부터 저장 프로시져 기능이 생겼다. 아직 알파 버전이라 좀 불안하지만, 차츰 나아지겠지.

2 예제

DROP TABLE IF EXISTS SpSample;

CREATE TABLE SpSample
(
    ID INT NOT NULL,
    Name VARCHAR(100) NOT NULL,

    PRIMARY KEY(ID)
);

INSERT INTO SpSample VALUES (1, "this is 1");
INSERT INTO SpSample VALUEs (2, "this is 2");

DROP PROCEDURE IF EXISTS test;

delimiter //
CREATE PROCEDURE test (IN IDVALUE INT)
BEGIN
    SELECT * FROM SpSample WHERE ID = IDVALUE;
END
//
delimiter ;

CALL test(1);
CALL test(2);

함수 내부에서 ';' 기호를 사용하기 때문에, delimiter 명령어를 이용해서, 기호를 바꿔줘야한다. 아니면 ';' 기호가 나타나는 곳 까지를 하나의 명령어로 인식하기 때문에 에러가 발생한다.


create table catagory
(
    catagory_id int unsigned not null auto_increment,
    name varchar(50) not null,
    description text,
    primary key (catagory_id)
) type=innodb;

create table catagory_set
(
    master_id int unsigned not null,
    slave_id int unsigned not null,
    index(master_id),
    index(slave_id),
    primary key (master_id,slave_id),
    foreign key (master_id) references catagory (catagory_id) on delete cascade,
    foreign key (slave_id) references catagory (catagory_id) on delete cascade
) type=innodb;

drop procedure add_catagory;

delimiter ?

create procedure add_catagory (
    IN param1 int, IN param2 char(50),IN param3 text, OUT cid int, OUT error_msg char(80))
begin
    declare master_id, master_exist, name_exist int;
    set cid = -1;
    set name_exist = 0, master_exist = 0;

    # Insert a subcatagory #
    if param1 > 0 then

        # Check if the master catagory ID is valid # 
        select count(catagory_id), catagory_id into master_exist, master_id
            from catagory where catagory_id=param1 group by catagory_id;
    
        # Check if the same catagory name exist and the master catagory # 
        select count(catagory_id) into name_exist from catagory, catagory_set
            where catagory.name=param2 and catagory.catagory_id=catagory_set.slave_id
                and catagory_set.master_id=master_id;
    
        if master_exist > 0 and name_exist = 0 then
            lock tables catagory write, catagory_set write;
            flush table catagory, catagory_set;
            insert into catagory values (null, param2, param3);
            select last_insert_id() into cid;
            insert into catagory_set values (param1, cid);
            unlock tables;
    
        elseif master_exist = 0 then
            set error_msg = 'The master catagory ID provided does not exist';
    
        elseif name_exist > 0 then
            set error_msg = 'The catagory name already exist, please choose another name'; 
    
        end if;

    # Insert a primary catagory #
    else
        # Search and compare the name of all primary catagory #
        select count(catagory_id) into name_exist from catagory
            where name = param2 and not exists(
                select * from catagory_set
                    where catagory_set.slave_id = catagory.catagory_id
            );
        if name_exist > 0 then
            set error_msg = 'The catagory name already exist, please choose another name'; 
        else
            insert into catagory values (null, param2, param3);
            select last_insert_id() into cid;
        end if;

    end if;
end ?

delimiter ;

call add_catagory(1,'Planet','Earth',@cid,@error);
select @cid, @error;




delimiter //

CREATE PROCEDURE GEOCODE03()
BEGIN
    DECLARE X_digit_one CHAR(1);
    DECLARE Y_digit_one CHAR(1);
    DECLARE INTOSSHEET INT;
    DECLARE INTNGFIELD INT;
    DECLARE strOSSHEET CHAR(50);
    DECLARE strNGFIELD CHAR(50);
    DECLARE lngGeoLoop INT;
    DECLARE lngXINT INT;
    DECLARE lngYINT INT;
    DECLARE INTCOUNT INT;
    DECLARE INTNUMROWS INT;
    DECLARE intMIKEY INT;
    DECLARE STRXINT CHAR(50);
    DECLARE STRYINT CHAR(50);
    DECLARE FIRSTDIG CHAR(2);
    DECLARE GeoCodeCUR CURSOR FOR SELECT lpaSheetReference,lpaFieldNumber,UID FROM MIXCheckDB.IACS2003GIS;
    OPEN GeoCodeCUR;
    SELECT COUNT(*) INTO INTNUMROWS FROM MIXCheckDB.IACS2003GIS;
    SET INTCOUNT = INTNUMROWS;
    WHILE INTCOUNT > 0 DO
        FETCH GeoCodeCUR INTO strOSSHEET,strNGFIELD,intMIKEY;
        SET FIRSTDIG = LEFT(strOSSHEET,2);
        CASE FIRSTDIG
            WHEN "SR" THEN
                SET X_digit_one = "1";
                SET Y_digit_one = "1";
            WHEN "SM" THEN
                SET X_digit_one = "1";
                SET Y_digit_one = "2";
            WHEN "SS" THEN
                SET X_digit_one = "2";
                SET Y_digit_one = "1";
            WHEN "SN" THEN
                SET X_digit_one = "2";
                SET Y_digit_one = "2";
            ....(Lots more of the case statement here)...
            ELSE
                SET X_digit_one = "0";
                SET Y_digit_one = "0";
        END CASE;
        SET STRXINT = CONCAT(X_digit_one, MID(strOSSHEET,3,2), LEFT(strNGFIELD,2), '0');
        SET STRYINT = CONCAT(Y_digit_one, RIGHT(strOSSHEET,2), RIGHT(strNGFIELD,2),'0');
        SET lngXINT = STRXINT;
        SET lngYINT = STRYINT;
        UPDATE MIXCheckDB.IACS2003GIS SET xcoord = lngXINT, ycoord = lngYINT WHERE MIXCheckDB.IACS2003GIS.UID = intMIKEY;
        SET INTCOUNT = INTCOUNT - 1;
    END WHILE;
CLOSE GeoCodeCUR;
END

3 링크

• MySQL Manual > Stored Procedures and Functions
  
공식 매뉴얼. 내용 한참 보강해야 할 듯 하다.

• see also MySql
  

• CategoryDatabase