Lidt stored procedure

December 8, 2008 · Posted in Udvikling · 2 Comments 

Som inspiration til stored procedure i MySQL har jeg lavet en lille eksempel.

Stored procedure er en nem måde at “gemme” noget SQL på serveren. Lave en funktion om man vil. Mit eksempel er igen lidt tænkt, men burde illustrere hvad man kan bruge stored procedures til.

Først opretter jeg to meget simple tabeller. En til brugere og en til rettigheder.

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE priv (
  userid INT NOT NULL auto_increment,
  area CHAR(10) NOT NULL,
  PRIMARY KEY (userid,area)
) ENGINE=MyISAM;
 
CREATE TABLE users (
  userid INT NOT NULL auto_increment,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (userid)
) ENGINE=MyISAM;

Når jeg opretter en bruger vil jeg gerne automatisk gi’ den bruger adgang til områderne foo og bar. Jeg skal altså indsætte en række i users-tabellen og to rækker i priv-tabellen. Rækkerne i priv skal matche det userid man indsatte i users.

Jeg laver følgende stored procedure:

1
2
3
4
5
6
7
8

CREATE PROCEDURE add_user (IN i VARCHAR(255))
BEGIN
  DECLARE u INT;
  INSERT INTO users (name) VALUES (i);
  SELECT LAST_INSERT_ID() INTO u;
  INSERT INTO priv(userid, area) VALUES(u, 'foo');
  INSERT INTO priv(userid, area) VALUES(u, 'bar');
END;

Hvor jeg som argument ta’r en VARCHAR(255). I min stored procedure indsætter jeg brugeren i users-tabellen. Henter userid via LAST_INSERT_ID() og indsætter to rækker i priv-tabellen med områderne foo, bar sammen med userid.

Tilsvarende laver jeg en remove_user() der kan slette brugeren og hans privilegier

1
2
3
4
5
6
7

CREATE PROCEDURE remove_user(IN i VARCHAR(255))
BEGIN  
  DECLARE u INT;
  SELECT userid INTO u FROM users WHERE name = i;
  DELETE FROM priv WHERE userid = u;
  DELETE FROM users WHERE userid = u;
END;

For at indsætte nogle brugere kalder jeg følgende:

1
2
3

CALL add_user('Anna');
CALL add_user('Bent');
CALL add_user('Casper');

Og for at slette kalder jeg:

1

Call remove_user('Bent');

Bemærk at hvis man opretter stored procedures via phpMyAdmin så skal man skifte delimiter fra ; til fx //.

Tags: <, >


  • Om websmed.dk

    Jeg hedder Morten, og jeg har udviklet webapplikationer siden slutningen af 90'erne.

    Jeg vil her dele ud af min erfaring, og med jævne mellemrum poste nogle tips og tricks, samt løsningsforslag på generelle dagligdags problemstillinger.

    Jeg har en forkærlighed for PHP og MySQL, hvorfor I nok vil se flest indlæg der vedrører den gren af webudvikling.

    Jeg har en anden blog af mere personlig karakter på mbn.dk, hvor I kan finde kontaktmuligheder m.m.

    Mine indlæg vil tit være baseret på problemstillinger i mit daglige virke, eller i mit eget firma MRLYTICS, hvor jeg sælger kundeundersøgelser samt kundedatabaser.

    Denne side er hostet hos slicehost.

  • Skrevet på Twitter