PL/SQL

-- MySQL programación
-- http://dev.mysql.com/doc/refman/5.0/es/create-procedure.html

CREATE PROCEDURE sp_name ([parameter[,...]])
    [characteristic ...] routine_body

CREATE FUNCTION sp_name ([parameter[,...]])
    RETURNS TYPE
    [characteristic ...] routine_body

parameter:
    [ IN | OUT | INOUT ] param_name TYPE

TYPE:
    ANY valid MySQL data TYPE

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    procedimientos almacenados o comandos SQL válidos




-- DROP PROCEDURE y DROP FUNCTION

DELIMITER $$
DROP {PROCEDURE | FUNCTION} [IF EXISTS] holamundo$$
CREATE PROCEDURE holamundo()
BEGIN
SELECT 'hola mundo';
END $$


-- EJEMPLO

DELIMITER $$

CREATE DEFINER=`dam`@`%` FUNCTION `MACarmonaimpar`(numero INT) RETURNS INT
BEGIN
    DECLARE impar INT;
IF MOD(numero,2)=0 THEN
        SET impar=0;
ELSE
        SET impar=1;
END IF;
RETURN (impar);
    END$$

DELIMITER ;



---

DELIMITER $$
CREATE FUNCTION estado (in-estado CHAR(1))
RETURN VARCHAR(25)
BEGIN
    DECLARE estado VARCHAR(25);
    IF in_estado='p' THEN
        SET estado='pagado';
    ELSEIF in_estado='c' THEN
        SET estado='cobrado';
    ELSEIF in_estado='e' THEN
        SET estado='enviado'
    END IF
    RETURN (estado);
END;
$$


--


DELIMITER $$
DROP PROCEDURE IF EXISTS m_estado $$
CREATE PROCEDURE m_estado (IN numero INT)
BEGIN
    IF (esimpar(numero)) THEN
        SELECT CONCAT(numero, " es impar");
    ELSE
        SELECT CONCAT(numero, " es par");
    ENDIF;
    END;
    $$


CONDICIONALES
--------------
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF




-- CASE
---------
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
O:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE


-- LOOPS
---------


-- Simple

[begin_label:] LOOP
    statement_list
 LEAVE label1; -- < --  Para salir
END LOOP [end_label]

-- Ejemplo

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
      ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END;


-- Repeat

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]


-- Ejemplo

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT
    ->     SET @x = @x + 1;
    ->   UNTIL @x > p1 END REPEAT;
    -> END
    -> //

-- While

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]


-- Ejemplo

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END;

--Case/When
-------------

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE


-- O

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE



-- Cursor
-----------
OPEN
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
CLOSE



DECLARE cursor1 CURSOR FOR
    SELECT titulo, contenido, fecha
    FROM noticia;
----
DELIMITER $$

CREATE PROCEDURE build_email_list (INOUT email_list VARCHAR(4000))
BEGIN

    DECLARE v_finished INTEGER DEFAULT 0;
        DECLARE v_email VARCHAR(100) DEFAULT "";

    -- declare cursor for employee email
    DECLARE email_cursor CURSOR FOR
        SELECT email FROM employees;

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET v_finished = 1;

    OPEN email_cursor;

    get_email: LOOP

        FETCH email_cursor INTO v_email;

        IF v_finished = 1 THEN
            LEAVE get_email;
        END IF;

        -- build email list
        SET email_list = CONCAT(v_email,";",email_list);

    END LOOP get_email;

    CLOSE email_cursor;

END$$

DELIMITER ;




SET @email_list = "";
CALL build_email_list(@email_list);
SELECT @email_list;



-- Handler
------------

DECLARE CONTINUE HANDLER FOR
    NOT FOUND SET L_last_row_fetched=1;


DECLARE $$
CP c3()
BEGIN
    DEC temp VAR(30);
    DEC lrf BOOLEAN;
    DEC nn INT;
    DEC C2 CURSOR FOR SELECT titulo FROM algo;
    DEC CONTINUE HANDLER FOR NOT FOUND
    SET lrf=1; SET nn=0;
    OPEN C2;
    l_cursor:LOOP
        FETCH C2 INTO tmp;
        SET nn=nn+1:
        IF lrf=1 THEN
            LEAVE l_cursor;
        END IF;
    END LOOP l_cursor;
    CLOSE C2;
    SELECT nn;
    END;$$

---------------------------------------ELIMINACIÓN Y CONSULTA----------------------------------------------------

DROP {PROCEDURE | FUNCTION } IF EXISTS rutina
SHOW  {PROCEDURE | FUNCTION } STATUS [LIKE 'patron']
SHOW CREATE rutina
---------------------------------------EJEMPLO CURSOR LOOP----------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `noticias_autor`()
    READS SQL DATA
BEGIN
        DECLARE vautor , na_cont INT;
        DECLARE fin BOOL;
        DECLARE autor_cursor CURSOR FOR
                SELECT id_autor FROM autor;
        DECLARE noticia_cursor CURSOR FOR
                SELECT autor FROM noticias
                WHERE autor=vautor;
        DECLARE CONTINUE HANDLER FOR
                NOT FOUND SET fin=1;
        SET na_cont=0;
        OPEN autor_cursor;
        autor_loop: LOOP
                FETCH autor_cursor INTO vautor;
                IF fin=1 THEN
                        LEAVE autor_loop;
                END IF;
        OPEN noticia_cursor;
        SET na_cont=0;
        noticias_loop : LOOP
                FETCH noticia_cursor INTO vautor;
                IF fin=1 THEN
                        LEAVE noticias_loop;
                END IF;
        SET na_cont=na_cont+1;
        END LOOP noticias_loop;
        CLOSE noticia_cursor;
        SET fin=0;
        SELECT CONCAT ('El autor ', vautor, ' tiene ', na_cont , ' noticias.');
        END LOOP autor_loop;
        CLOSE autor_cursor;
END





-- Handler
DECLARE (CONTINUE | EXIT) HANDLER FOR
    (SQL STATE sqlatate_code  | MySQL error_code | condicion_nombre)
    handler_actions

EXIT: Bloque termina. Si bloque principal -> proceimiento termina-> devuelve control a procedimiento/prog externo
CONTINUE: Continua en la declaracion siguiente donde ocasionó el error.

Ej:
DECLARE CONTINUE HANDLER FOR  1062 SET DUPLICATE_KEY =1 ;
-   -   -    FOR SQLSTATE '2300' SET -  -




Ejemplo:

DELIMETER $$
CREATE PROCEDURE inserta_autor (pautor int, plogin VARCHAR(45))
MODIFIES SQLDATE
BEGIN
DECLARE duplicate_key INT DEFAULT 0;
    BEGIN
    DECLARE EXIT HANDLER FOR 1062
    SET duplicate_key=1;
    INSERT INTO autors (id_autorm login) VALUES
        (pautor, plogin);
    END;
IF duplicate_key=1 THEN
    SELECT CONCAT('ERROR, CLAVE DUPLICADA') AS 'Resultado'
ELSE
    SELECT CONCAT('Autor ', plogin, ' creado') AS 'Resultado';
ENDIF;
END;$$




-- TRIGGERS

se activa (INSERT, UPDATE, DELETE)

CREATE TRIGGER nombre
    BEFORE INSERT SET @SUM=@SUM+NEW.cantidad

CREATE TRIGER nombre (BEFORE | AFTER) (INSERT | UPDATE | DELETE)
    ON tabla FOR EACH ROW sentencias


-- EJEMPLO

DELIMETER $$
CREATE TRIGGER comp_saldo
    BEFORE UPDATE ON MOVIMIENTO
    FOR EACH ROW
    BEGIN
        IF NEW.cantidad<0 THEN
            SET NEW.cantidad=0;
        ELSEIF NEW.cantidad>100 THEN
            SET NEW.cantidad=100;
        END IF;
    END;$$




DELIMETER $$
CREATE TRIGGER actuadas
    BEFORE INSERT ON MOVIMIENTO
    FOR EACH ROW
    BEGIN
        UPDATE cuenta SET saldo=saldo+NEW.cantidad WHERE cod_cuenta=OLD.cod_cuenta;
    END;$$



DELIMETER $$
CREATE TRIGGER ASDD
    AFTER INSERT ON movimiento
    FOR EACH ROW
    BEGIN
        IF existe(MOTH(NEW.fecha),idia)) =0 THEN
            INSERT INTO idia (cantidad, fecha)
            VALUES (NEW.cantidad, NEW.fecha);
        ELSE UPDATE idia SET cantidad = NEW.cantidad+cantidad WHERE mes=MONTH(NEW.fecha);
        ENDIF;
    END;$