Løsningsforslag til kapittel 13

Oppgave 1

Lagret funksjon som returnerer navnet på en vare.

DELIMITER $$

DROP FUNCTION IF EXISTS hent_varenavn $$

CREATE FUNCTION hent_varenavn
(
	p_vnr CHAR(5)
)
  RETURNS VARCHAR(40)
  READS SQL DATA
BEGIN
  DECLARE v_betegnelse VARCHAR(40) DEFAULT NULL;
  
  SELECT Betegnelse INTO v_betegnelse
  FROM   Vare
  WHERE  VNr = p_vnr;
  
  RETURN v_betegnelse;
END $$

DELIMITER ;

-- Test med eksisterende varenummer
SELECT hent_varenavn('10830') FROM Dual;

-- Test med ukjent varenummer
SELECT hent_varenavn('9999') FROM Dual;

-- Test med spørring mot Ordrelinje
SELECT *, hent_varenavn(VNr) AS Betegnelse
FROM Ordrelinje;

Oppgave 2

Alle data om en gitt vare i form av ut-parametre.

DELIMITER $$

DROP PROCEDURE IF EXISTS hent_vare $$

CREATE PROCEDURE hent_varedata
(
  IN  p_vnr CHAR(5),
  OUT p_betegnelse VARCHAR(40),
  OUT p_pris DECIMAL(8, 2),
  OUT p_antall INT(11)
)
BEGIN
  SELECT Betegnelse, Pris, Antall
  INTO p_betegnelse, p_pris, p_antall
  FROM Vare
  WHERE VNr = p_vnr;
END $$

DELIMITER ;

-- Tester prosedyren

CALL hent_varedata('10820', @betegnelse, @pris, @antall);

SELECT @betegnelse AS Betegnelse, @pris AS Pris, @antall AS Antall
FROM Dual;

Oppgave 3

Setter inn en ny vare - uten feilsjekking.

DELIMITER $$

DROP PROCEDURE IF EXISTS ny_vare $$

CREATE PROCEDURE ny_vare
(
  IN  p_vnr CHAR(5),
  IN  p_betegnelse VARCHAR(30),
  IN  p_pris DECIMAL(8,2),
  IN  p_antall INT(11)
)
BEGIN
  INSERT INTO Vare(VNr, Betegnelse, pris, antall)
  VALUES (p_vnr, p_betegnelse, p_pris, p_antall);
END $$

DELIMITER ;

-- Tester prosedyren (med lovlige inndata, se oppgave 6)
CALL ny_vare('99999', 'Strøsand', 20.50, 3);

Oppgave 4

Slett vare.

DELIMITER $$

DROP PROCEDURE IF EXISTS slett_vare $$

CREATE PROCEDURE slett_vare
(
  IN  p_vnr CHAR(5),
  OUT ut_status VARCHAR(30)
)
BEGIN
  DECLARE v_ant INT;
  
  SELECT COUNT(*) INTO v_ant
  FROM Vare
  WHERE VNr = p_vnr;
  
  IF v_ant = 1 THEN
    DELETE FROM Vare
    WHERE VNr = p_vnr;
    
    COMMIT;
    SET ut_status = 'Varen er slettet.';
  ELSE
    SET ut_status = 'Ukjent vare!';
  END IF;
END $$

DELIMITER ;

-- Tester prosedyren (legg inn 99999 først)
CALL slett_vare('99999', @kvittering);
SELECT @kvittering;

Oppgave 5

Endre prisen på en vare.

DELIMITER $$

DROP PROCEDURE IF EXISTS endre_varepris $$

CREATE PROCEDURE endre_varepris
(
  IN  p_vnr CHAR(5),
  IN  p_ny_pris  DECIMAL(8, 2),
  OUT ut_status  VARCHAR(30)
)
BEGIN
  DECLARE v_ant INT;
  
  SELECT COUNT(*) INTO v_ant
  FROM Vare
  WHERE VNr = p_vnr;
  
  IF v_ant = 1 THEN
    UPDATE Vare
    SET pris = p_ny_pris
    WHERE VNr = p_vnr;
    
    COMMIT;
    SET ut_status = 'Prisen er endret.';
  ELSE
    SET ut_status = 'Ukjent vare!';
  END IF;
END $$

DELIMITER ;

-- Tester prosedyren (sett inn 99999 først)
CALL endre_varepris('99999', 50, @kvittering);
SELECT @kvittering;

Oppgave 6

Sett inn ny vare - med feilsjekking.

DELIMITER $$

DROP PROCEDURE IF EXISTS ny_vare $$

CREATE PROCEDURE ny_vare
(
  IN  p_vnr CHAR(5),
  IN  p_betegnelse VARCHAR(30),
  IN  p_pris DECIMAL(8,2),
  IN  p_antall INT(11)
)
BEGIN
  DECLARE v_antall INT;
  DECLARE v_utdata TEXT;
  
  SET v_utdata = '';
  
  -- Sjekker om varen finnes fra før
  SELECT COUNT(*) INTO v_antall
  FROM Vare
  WHERE VNr = p_vnr;
  
  IF v_antall > 0 THEN
    SET v_utdata = 'Varen finnes allerede!';
  ELSEIF p_antall < 0 THEN
    SET v_utdata = 'Antall på lager må være større enn 0!';
  ELSEIF p_pris <= 0 OR p_pris > 100000 THEN
    SET v_utdata = 'Pris må være positiv og mindre enn kr 100 000!';
  ELSE
    SET v_utdata = 'Varen er registrert.';
    
    INSERT INTO Vare(VNr, Betegnelse, pris, antall)
    VALUES (p_vnr, p_betegnelse, p_pris, p_antall);
    
    COMMIT;
  END IF;
  
  -- Kvittering til bruker
  SELECT v_utdata;
END $$

DELIMITER ;

-- Tester prosedyren med lovlige inndata
CALL ny_vare('99999', 'Strøsand', 20.50, 3);

-- Tester prosedyren med eksisterende varenummer
CALL ny_vare('10830', 'Strøsand', 20.50, 3);

-- Tester prosedyren med negativt antall
CALL ny_vare('99999', 'Strøsand', 20.50, -3);

-- Tester prosedyren med for høy pris
CALL ny_vare('99999', 'Strøsand', 200000, 3);

-- Tester prosedyren med negativ pris
CALL ny_vare('99999', 'Strøsand', -1, 3);

Oppgave 7

Viser kun kall på ny_vare (oppgave 3). Antar vi får inn data via URL-parametre (GET).

$vnr = $_GET['vnr'];
$betegnelse = $_GET['betegnelse'];
$pris = $_GET['pris'];
$antall = $_GET['antall'];

// Oppkoblingsparametre må tilpasses
$tjener = 'localhost';
$bruker = 'root';
$pass = '';
$db = 'hobbyhuset';

// Koble opp databasen
$link = mysqli_connect($tjener, $bruker, $pass, $db);
mysqli_set_charset($link, 'utf8');

// Utfør prosedyrekall
$sql = "CALL ny_vare('$vnr', '$betegnelse', $pris, $antall);";
$resultat = mysqli_query($link, $sql);

$rad = mysqli_fetch_array($resultat);
if ($rad) {
  $melding = $rad[0];
  print("
$melding
");
}
else {
  print('
Prosedyrekallet feilet!
');
}

mysqli_close($link);

Oppgave 8

Lagret prosedyre som viser alle varer - foreløpig uten PHP-skript.

DELIMITER $$

DROP PROCEDURE IF EXISTS vis_varer $$

CREATE PROCEDURE vis_varer()
BEGIN
  SELECT VNr, Betegnelse, Pris
  FROM Vare
  WHERE Pris < 100;
END $$

DELIMITER ;

-- Test
CALL vis_varer();

Oppgave 9

Ny ordre - bør utvides med diverse sjekker, f.eks. at varen eksisterer.

DELIMITER $$

DROP PROCEDURE IF EXISTS ny_ordre $$

CREATE PROCEDURE ny_ordre
(
  IN p_ordrenr INT(11),
  IN p_knr INT(11),
  IN p_ansnr INT(5),
  IN p_vnr CHAR(6),
  IN p_antall INT(4)
)
BEGIN
  DECLARE v_pris DECIMAL(8, 2);
  
  INSERT INTO Ordre(OrdreNr, OrdreDato, KNr, AnsNr)
  VALUES (p_ordrenr, CURDATE(), p_knr, p_ansnr);
  
  SELECT Pris INTO v_pris
  FROM Vare
  WHERE VNr = p_vnr;
  
  INSERT INTO Ordrelinje(OrdreNr, VNr, PrisPrEnhet, Antall)
  VALUES (p_ordrenr, p_vnr, v_pris, p_antall);
  
  COMMIT;
  
END $$

DELIMITER ;

-- Test
CALL ny_ordre(99999, 5002, 1, '10830', 33);

Oppgave 10

Trigger som lagrer navn med stor forbokstav.

DELIMITER $$

DROP TRIGGER IF EXISTS ansatt_b_ins_trg $$
DROP TRIGGER IF EXISTS ansatt_b_upd_trg $$
DROP FUNCTION IF EXISTS INITCAP $$

-- Denne er litt for enkel: 'anders' blir korrekt til 'Anders',
-- men 'karl martin' blir til 'Karl martin'.
CREATE FUNCTION INITCAP(str VARCHAR(255))
  RETURNS VARCHAR(255)
  DETERMINISTIC
BEGIN
  DECLARE ut VARCHAR(255);
  SET ut = CONCAT(UPPER(SUBSTR(str, 1,1)), LOWER(SUBSTR(str, 2)));
  RETURN ut;
END $$

CREATE TRIGGER ansatt_b_ins_trg
  BEFORE INSERT ON Ansatt
  FOR EACH ROW
BEGIN
  SET NEW.Fornavn = INITCAP(NEW.Fornavn);
  SET NEW.Etternavn = INITCAP(NEW.Etternavn);
END $$

CREATE TRIGGER ansatt_b_upd_trg
  BEFORE UPDATE ON Ansatt
  FOR EACH ROW
BEGIN
  SET NEW.Fornavn = INITCAP(NEW.Fornavn);
  SET NEW.Etternavn = INITCAP(NEW.Etternavn);
END $$

DELIMITER ;

-- Tester hjelpefunksjonen
SELECT INITCAP('oLe') FROM DUAL;

-- Tester innsetting
INSERT INTO Ansatt(AnsNr, Fornavn, Etternavn, PostNr)
  VALUES (999, 'PeR', 'hansen', 3800);

-- Tester oppdatering
UPDATE Ansatt
  SET Fornavn='ola', Etternavn='li'
  WHERE AnsNr = 999;

Oppgave 11

Trigger for loggføring.

-- Lager logg-tabellen først
DROP TABLE IF EXISTS logg;

CREATE TABLE logg
(
  id INT(11) AUTO_INCREMENT,
  bruker VARCHAR(40),
  vnr CHAR(5),
  antall_gml INT(5),
  antall_ny INT(5),
  tidspunkt DATE,
  PRIMARY KEY (id)
);


-- Lager triggeren:
DELIMITER $$

DROP TRIGGER IF EXISTS vare_a_upd_trg $$

CREATE TRIGGER vare_a_upd_trg
  AFTER UPDATE ON Vare
  FOR EACH ROW
BEGIN
  INSERT INTO logg(bruker, vnr, antall_gml, antall_ny, tidspunkt)
  VALUES(USER(), NEW.VNr, OLD.Antall, NEW.Antall, NOW());
END $$

DELIMITER ;
  

-- Tester
UPDATE Vare 
SET Antall = Antall+1;

COMMIT;

Oppgave 12

Oppdater SendtDato.

DELIMITER $$

DROP TRIGGER IF EXISTS ordre_b_ins_trg $$

CREATE TRIGGER ordre_b_ins_trg
  BEFORE INSERT ON Ordre
  FOR EACH ROW
BEGIN
  SET NEW.SendtDato = NEW.OrdreDato;
END $$

DELIMITER ;

-- Tester
INSERT INTO Ordre(OrdreNr, OrdreDato, KNr, AnsNr)
VALUES(99999, CURDATE(), 5002, 1);

Oppgave 13

Sjekk at varepriser er mellom 0 og 10 000.

Dette lar seg generelt formulere som en CHECK contraint, og MySQL godtar syntaksen:

ALTER TABLE Vare
ADD CHECK (Pris < 10000);

Men det ser ikke ut til at MySQL kontrollerer regelen, så da må vi bruke triggere i stedet. Viser bare UPDATE-triggeren.

DELIMITER $$

DROP TRIGGER IF EXISTS vare_b_upd_trg $$

CREATE TRIGGER vare_b_upd_trg
  BEFORE UPDATE ON Vare
  FOR EACH ROW
BEGIN
  IF NOT (NEW.Pris BETWEEN 0 AND 10000) THEN
    SIGNAL SQLSTATE 'U0001' -- Egendefinert feilkode
    SET MESSAGE_TEXT = 'Pris må være mellom 0 og 10 000!';
  END IF;
END $$

DELIMITER ;

-- Tester
UPDATE Vare
SET Pris = -1
WHERE VNr = '10820';

UPDATE Vare
SET Pris = 20000
WHERE VNr = '10820';

Oppgave 14

Forhindre endring av PrisPrEnhet.

DELIMITER $$

DROP TRIGGER IF EXISTS ordrelinje_b_upd_trg $$

CREATE TRIGGER ordrelinje_b_upd_trg
  BEFORE UPDATE ON Ordrelinje
  FOR EACH ROW
BEGIN
  IF NEW.PrisPrEnhet <> OLD.PrisPrEnhet THEN
    SIGNAL SQLSTATE 'U0002' -- Brukerdefinert feilkode
    SET MESSAGE_TEXT = 'Ikke lov å endre pris på eksisterende ordrelinje!';
  END IF;
END $$

DELIMITER ;

-- Tester
UPDATE Ordrelinje
SET PrisPrEnhet = PrisPrEnhet + 1; 

Tester triggeren fra PHP:

// Oppkoblingsparametre må tilpasses
$tjener = 'localhost';
$bruker = 'root';
$pass = '';
$db = 'hobbyhuset';

// Koble opp databasen
$link = mysqli_connect($tjener, $bruker, $pass, $db);
mysqli_set_charset($link, 'utf8');

$sql = "UPDATE Ordrelinje " .
       "SET PrisPrEnhet = PrisPrEnhet + 1";
$resultat = mysqli_query($link, $sql);

if (mysqli_errno($link) <> 0) {
  printf("Feil %d %s: %s",
    mysqli_errno($link), mysqli_sqlstate($link), mysqli_error($link));
}
else {
  print("Prisen er oppdatert!");
}

mysqli_close($link);

---