Gabriel eslava

-- practica funciones ///////////////////////////////

-- funcin con un valor//////////////////////////////////////////

CREATE FUNCTION hello (

s CHAR(20))

RETURNS CHAR(50)

RETURN CONCAT('Hello, ',s,'!');

SELECT hello('elefante');

Salida Hello, elefante!

-- funcin con paso de 2 valores //////////////////////////////

drop FUNCTION hello2

CREATE FUNCTION hello2 (

a CHAR(20),

b CHAR(20)

)

RETURNS CHAR(50) -- valor del retorno

RETURN CONCAT('Hello, ',a,'->',b );

SELECT hello2('gab','esl');

salida

Hello, 4

-- funcin con que suma 2 enteros /////////////////////////////// drop FUNCTION hello3

CREATE FUNCTION hello3 (

a int,

b int

)

RETURNS char(50) -- valor del retorno

RETURN CONCAT('Hello, ',a+b );

SELECT hello3(2,2);

Hello, 4

La lista de parmetros entre parntesis debe estar siempre presente. Si no hay parmetros, se debe usar una lista de parmetros vaca () . Cada parmetro es un

parmetro IN por defecto. Para especificar otro tipo de parmetro, use la palabra clave

OUT o INOUT antes del nombre del parmetro. Especificando IN, OUT, o INOUT slo es

valido para un PROCEDURE

-- practica procedimientos ///////////////////////////////

-- SIMPLE PROCEDIMIENTO ////////////////////////////////////////// drop PROCEDURE simpleproc

-- para el simbolo, habilitar el limete

delimiter //

CREATE PROCEDURE simpleproc (OUT a INT)

BEGIN

SELECT COUNT(*) INTO a FROM t1;

END

//

delimiter ;

CALL simpleproc(@a);

SELECT @a;

Salida 7

-- ////////////creando el ejemplo //////////////////////////

CREATE TABLE actor (

id_cod varchar(15) NOT NULL,

nombre VARCHAR(64) NOT NULL,

apellidos VARCHAR(64) NOT NULL,

imdb VARCHAR(32) NOT NULL ,

constraint pk_id_cod primary key(id_cod)

)

INSERT INTO actor VALUES('1','Harrison', 'Ford', 'nm0000148');

INSERT INTO actor VALUES('2','Russell', 'Crowe', 'nm0000128');

select * from actor

-- En su versin ms simple CREATE PROCEDURE pa_actores_lista()

SELECT

*

FROM

actor;

-- Para ejecutar el procedimiento almacenado: CALL pa_actores_lista();

CREATE PROCEDURE pa_actores_cantidad()

SELECT

COUNT(*)

FROM

actor;

-- Nmero de registros en la tabla de actores con

delimitadores drop PROCEDURE pa_actores_cantidad2

DELIMITER //

CREATE PROCEDURE pa_actores_cantidad2()

BEGIN

DECLARE b INT;

SELECT COUNT(*)fROM actor

INTO b;-- mete el reusultado

SELECT b;-- selecciona el resultado

END

//

CALL pa_actores_cantidad2();

--- procedimento con paso de parametro CREATE PROCEDURE pa_actores_buscar(letra CHAR(2))

SELECT * FROM actor

WHERE nombre LIKE letra;

CALL pa_actores_buscar('h%');

--- procedimento con paso de parmetro ocupando loop

drop PROCEDURE doiterate

delimiter //

CREATE PROCEDURE doiterate(p1 INT)

BEGIN

g: LOOP -- declaracion del loop

SET p1 = p1 + 1;

IF p1 < 10 THEN

ITERATE g;

END IF;

LEAVE g;-- abandona cualquier control

END LOOP g;

SET @x = p1;

END

//

delimiter ;

CALL doiterate(1);

SELECT @x;

--- disparadores ----------------------------------------

CREATE TABLE `employee` (

`id` int(11) NULL,

`first_name` varchar(15) NULL,

`last_name` varchar(15) NULL,

`start_date` date NULL,

`end_date` date NULL,

`salary` float(8,2) NULL,

`city` varchar(10) NULL,

`description` varchar(15) NULL

);

insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City,

Description)

values (1,'Jason', 'Martin', '19960725', '20060725', 1234.56, 'Toronto',

'Programmer');

insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City,

Description)

values(2,'Alison', 'Mathews', '19760321', '19860221', 6661.78, 'Vancouver','Tester');

insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City,

Description)

values(3,'James', 'Smith', '19781212', '19900315', 6544.78, 'Vancouver','Tester');

insert into Employee(id,first_name, last_name, start_date, end_Date, salary,

City, Description)

values(4,'Celia', 'Rice', '19821024', '19990421', 2344.78, 'Vancouver','Manager');

insert into Employee(id,first_name, last_name, start_date, end_Date, salary,

City, Description)

values(5,'Robert', 'Black', '19840115', '19980808', 2334.78, 'Vancouver','Tester');

insert into Employee(id,first_name, last_name, start_date, end_Date, salary,

City, Description)

values(6,'Linda', 'Green', '19870730', '19960104', 4322.78,'New York', 'Tester');

insert into Employee(id,first_name, last_name, start_date, end_Date, salary,

City, Description)

values(7,'David', 'Larry', '19901231', '19980212', 7897.78,'New York', 'Manager');

insert into Employee(id,first_name, last_name, start_date, end_Date, salary,

City, Description)

values(8,'James', 'Cat', '19960917', '20020415', 1232.78,'Vancouver', 'Tester');

select * from Employee;

CREATE TABLE transaction_log(

user_id VARCHAR(15),

description VARCHAR(50)

);

delimiter $$

CREATE TRIGGER myTrigger

AFTER UPDATE ON employee FOR EACH ROW

BEGIN

INSERT into transaction_log

(user_id, description)

VALUES (user(), CONCAT('Adjusted account ',NEW.id,' from

',OLD.salary, ' to ', NEW.salary));

END$$

delimiter ;

update Employee set salary = salary + 1000;

select * from transaction_log;