EJERCICIOS DE PROCEDIMIENTOS

Javier Garca Cambronel PRIMERO DE ASIR

[EJERCICIOS DE PROCEDIMIENTOS]

COMO CREAR, MODIFICAR, O ELIMINAR UN PROCEDIMIENTO

EJERCICIOS DE PROCEDIMIENTOS1. Escribir un procedimiento parametrizado que reciba una cadena de caracteres y la visualice al revs.

2. dem pero que muestre el carcter que ocupa la posicin central de la cadena.

3. dem pero que transforme la cadena de forma que el 1er carcter y el ltimo se muestren en maysculas

4. Escribir un procedimiento que reciba una cantidad en euros y la desglose en monedas y billetes.

5. Escribir un procedimiento parametrizado que reciba el nombre de un cliente y devuelva el importe de su pedido de mayor importe, la descripcin del producto que compr, la fecha, el nombre del empleado que lo atendi y la ciudad en la que est su oficina.

6. Escribir un procedimiento que reciba la descripcin de un producto y devuelva el nombre del cliente que ha comprado ms unidades de dicho producto.

7. Escribir un procedimiento que reciba el nombre de un empleado y devuelva el n de clientes que ha atendido.

8. Inserta dos nuevos campos en la tabla Pedidos: "pagado" (booleano) y "fecha pago". Actualiza el campo pagado a falso (de todos los pedidos). Crea un procedimiento que reciba el n de pedido y actualice el campo pagado (a true) e inserte en el campo "fecha pago" el dato de la fecha en la que se ejecuta el procedimiento.

PRIMERO DE ASIR

Pgina 1

[EJERCICIOS DE PROCEDIMIENTOS]

COMO CREAR, MODIFICAR, O ELIMINAR UN PROCEDIMIENTOPara crear un procedimiento, lo hacemos normalmente desde la lnea de comandos, cuando lo creamos pero tambin podemos utilizar al igual que para las otras opciones como modificar o eliminar un procedimiento SQL Server Management Studio para hacer esto nos situaramos sobre la base de datos donde deseamos crear y/o modificar un procedimiento creado abrirla e ir a la seccin de Programacin y se encontrara en Procedimientos almacenados all podemos realizar cualquiera de las acciones mencionadas.

PRIMERO DE ASIR

Pgina 2

[EJERCICIOS DE PROCEDIMIENTOS]

EJERCICIOS DE PROCEDIMIENTOS1. Escribir un procedimiento parametrizado que reciba una cadena de caracteres y la visualice al revs. Creamos el procedimiento con el nombre que queramos en nuestro caso ejer1, una vez hecho esto creamos la variable @caracteres con el tipo de dato nvarchar y que admita una cantidad mxima de 50 caracteres entonces lo que hacemos despus es hacer un select de la variable que acabamos de asignar que va a ser lo que escribiremos y nombramos a esa columna con el nombre de Tu_palabra entonces, para hacer lo que nos pide el ejercicio realizamos la funcin reverse para que lo que introducimos sea dado la vuelta como corresponde a esta columna la renombramos con AS como palabra al reves. CREATE PROCEDURE ejer1 @caracteres nvarchar(50) AS SELECT @caracteres AS Tu_palabra, REVERSE(@caracteres) AS palabra_al_revs

COMPROBACIN DE FUNCIONAMIENTOVemos que al ejecutar el procedimiento con un nombre, nos deveuelve el nombre y el mismo nombre visualizado al reves.

En este otro ejemplo vemos como al insertar una frase, esta tambin es dada la vuelta.

PRIMERO DE ASIR

Pgina 3

[EJERCICIOS DE PROCEDIMIENTOS]2. dem pero que muestre el carcter que ocupa la posicin central de la cadena. Creamos el procedimiento con el nombre de ejer2 una vez hecho esto declaramos la variable que llamaremos @caracteres esta variable admitir datos de tipo NVARCHAR y como mxima longitud 50 caracteres, haremos una seleccin de la variable, que ser la cadena que introducimos y la renombramos con el nombre de Tu_palabra hacemos el substring de dicha cadena donde le indicaremos la operacin a realizar y donde indicaremos tambin 1, que ser el nmero de caracteres a mostrar y a todo esto, lo llamamos con el AS correspondiente Letra_posicion_central. CREATE PROCEDURE ejer2 @caracteres NVARCHAR(50) AS SELECT @caracteres AS Tu_palabra, SUBSTRING(@caracteres, (SELECT LEN(@caracteres)/2+1),1) AS 'Letra_posicion_central'

COMPROBACIN DE FUNCIONAMIENTOVemos al ejecutar el procedimiento con la palabra regates, se nos indica que la letra que ocupa la posicion central es la a es decir, nos da un resultado correcto.

Vemos que ahora con la palabra Javier nos indica que la letra central es la i vemos que al dividir 5/2 da un resultado decimal, por eso como indicamos redondea al siguiente carcter, en este caso la i.

PRIMERO DE ASIR

Pgina 4

[EJERCICIOS DE PROCEDIMIENTOS]3. dem pero que transforme la cadena de forma que el 1er carcter y el ltimo se muestren en maysculas. Creamos el procedimiento con el nombre de ejer3, la variable correspondiente como hemos hecho anteriormente y un select a dicha variable, lo que hacemos aqu es indicar con UPPER el nmero de letras que sern transformadas a maysculas como nos pide el ejercicio y para ello utilizaremos tambin la funcin substring, pues devuelve parte de una expresin de caracteres. CREATE PROCEDURE ejer3 @caracteres NVARCHAR(50) AS SELECT @caracteres AS Tu_palabra, UPPER(LEFT(@caracteres,1)) + SUBSTRING(@caracteres, 2,(SELECT LEN(@caracteres)-2)) + UPPER(RIGHT(@caracteres,1))AS 'Maysculas Primera y ltima letra'

COMPROBACIN DE FUNCIONAMIENTOComprobamos que es as con la palabra regates y vemos que nos trasporfa tanto la primera letra como la ltima de la palabra en maysculas.

Y hacemos los mismo con la frase Amad a la dama en este caso, el resultado solo se vera alterado en la ltima letra, pues la primera ya esta en mayscula, vemos que el cambio se realiza correctamente en la siguiente imagen.

PRIMERO DE ASIR

Pgina 5

[EJERCICIOS DE PROCEDIMIENTOS]4. Escribir un procedimiento que reciba una cantidad en euros y la desglose en monedas y billetes. Creamos el siguiente procedimiento, del cual por cierto hay muchos ejemplos en Internet (este es uno de ellos, modificado, para que muestre el total), indicamos el nombre del procedimiento y le decimos que la variable que declaramos va a ser de tipo entero, declaramos las dems variables e indicamos su tipo, tambin entero, una vez hemos hecho esto asignamos los valores correspondientes a las variables una a una y su relacion con la variable que pediremos como parmetro @dinero para ello utilizamos set, una vez hecho esto imprimimos el total con convert donde todas las variables hasta @cinco son billetes y @dos y @uno sern monedas para hacer el total de monedas y billetes que se necesitan para el desglose haremos por una parte una suma de todas la variables pertenecientes a billetes concatenandolas con la cadena de caracteres billetes y y lo mismo hacemos con las monedas, por ltimo haremos esta conversin que hemos hecho, pero una a una, indicando de esta forma el nmero de billetes y monedas de cada tipo, por separado.CREATE PROC ejer4 @dinero INT AS DECLARE @q INT, @d INT, @cien INT, @c INT, @v INT, @diez INT, @cinco INT, @dos INT, @uno INT SET @q=@dinero/500 SET @dinero=@dinero%500 SET @d=@dinero/200 SET @dinero=@dinero%200 SET @cien=@dinero/100 SET @dinero=@dinero%100 SET @c=@dinero/50 SET @dinero=@dinero%50 SET @v=@dinero/20 SET @dinero=@dinero%20 SET @diez=@dinero/10 SET @dinero=@dinero%10 SET @cinco=@dinero/5 SET @dinero=@dinero%5 SET @dos=@dinero/2 SET @dinero=@dinero%2 SET @uno=@dinero PRINT 'EN TOTAL:'+Convert(char(2),@q+@d+@cien+@c+@v+@diez+@cinco)+' billetes y '+Convert(char(2),@dos+@uno)+'monedas' PRINT 'Resultado por partes:' PRINT Convert(char(2),@q)+' billete/s de 500 euros;' PRINT Convert(char(2),@d)+' billete/s de 200 euros;' PRINT Convert(char(2),@cien)+' billete/s de 100 euros;' PRINT Convert(char(2),@c)+' billete/s de 50 euros;' PRINT Convert(char(2),@v)+' billete/s de 20 euros;' PRINT Convert(char(2),@diez)+' billete/s de 10 euros;' PRINT Convert(char(2),@cinco)+' billete/s de 5 euros;' PRINT Convert(char(2),@dos)+' moneda/s de 2 euros;' PRINT Convert(char(2),@uno)+' moneda/s de 1 euro.'

PRIMERO DE ASIR

Pgina 6

[EJERCICIOS DE PROCEDIMIENTOS]

COMPROBACIN DE FUNCIONAMIENTOComo podemos ver cuando ejecutamos el procedimiento y le indicamos una cantidad de dinero, en este caso 5678 euros primero, obtendremos la cantidad de billetes y monedas que se utilizarn para el desglose de dicho dinero y despus indicaremos esto mismo, pero de forma separada, indicando cuantos billetes y monedas de cada tipo son necesarios.

PRIMERO DE ASIR

Pgina 7

[EJERCICIOS DE PROCEDIMIENTOS]5. Escribir un procedimiento parametrizado que reciba el nombre de un cliente y devuelva el importe de su pedido de mayor importe, la descripcin del producto que compr, la fecha, el nombre del empleado que lo atendi y la ciudad en la que est su oficina. Creamos el procedimiento indicando su nombre, creamos la variable que vamos a utilizar @cliente y el nmero de caracteres y tipo esperado por esta. Despus hacemos la seleccin necesaria para que se nos de solo el primer resultado de lo que le pedimos con la funcin TOP seguido de el nmero de resultados que queremos que se nos muestre, la seleccin tiene que ser de todo lo que se nos pide, (nombre, nombre de cliente, fecha de pedido, ciudad y descripcin), al igual que la suma del importe del pedido a la que renombraremos como importe mayor gastado con AS. Una vez hecho esto en el from tendremos que indicar las tablas que vamos a consultar y que se va comparar en la unin de tablas que indicamos, para que los datos sean verdicos y pertenezcan al cliente que nosotros indicamos y al pedido que hace dicho cliente. Una vez hemos hecho esto en la particula where indicaremos el nombre del cliente al que nos referimos, y como es el parmetro que le tenemos que pasar mediante el procedimiento, haremos la igualdad con la variable que @Cliente que es la que hemos declarado y va a recoger los datos que escribamos, por ltimo haremos el group by correspondiente de todas las columnas que hemos indicado en el select y lo ordenaremos con order by importe DESC para que los resultados pertenezcan al pedido de mayor importe, ya que con la clausula TOP1 solamente nos saldra el de menor importe, pues SQL nos da los datos de dicha select de forma predeterminada en ASC, es decir, nos daria los resultados, de el pedido de menor importe del cliente. CREATE PROCEDURE ejer5 @cliente VARCHAR (30) AS select TOP 1 nombre,nombrecliente,fechapedido,ciudad,descripcion, sum (importe) as [Importe mayor gastado] from ((((empleados left join pedidos on numemp = rep) left join oficinas on pedidos.rep = oficinas.dir) left join productos on producto=idproducto) left join clientes on numclie=clie) Where nombrecliente=@cliente group by nombre,importe,nombrecliente,fechapedido,ciudad,descripcion order by importe DESC;

MEJORA DE CDIGO EXPLICADA EN CLASENo hara falta cambiar el nombre de la columna nombre para evitar la ambigedad de nombre, tendramos que indicar en cada uno de ellos a que tabla pertenecen ya sea clientes.nombre o empleados.nombre

PRIMERO DE ASIR

Pgina 8

[EJERCICIOS DE PROCEDIMIENTOS]

COMPROBACIN DE FUNCIONAMIENTOVemos el nmero de cliente y el nombre del cliente al que hacen referecia.

Consultamos los pedidos de mayor importe que han hecho dichos clientes, los cuales tendran que ser el resultado que nos den al ejecutar el procedimiento.

Al ejecutar el procedimiento con Jaime LLorens cuyo nmero de cliente es 2103, vemos que el resultado del importe de su mayor pedido es de 450 euros, y como podemos comprobar en la imagen de arriba, con un recuadro rojo,as es.

Al ejecutar el procedimiento con Juan Surez cuyo nmero de cliente es 2106, vemos que el resultado del importe de su mayor pedido es de 21,30 euros, y como podemos comprobar en la imagen de arriba, con un recuadro rojo, as es.

PRIMERO DE ASIR

Pgina 9

[EJERCICIOS DE PROCEDIMIENTOS]6. Escribir un procedimiento que reciba la descripcin de un producto y devuelva el nombre del cliente que ha comprado ms unidades de dicho producto. Creamos el procedimiento e indicamos su nombre, hacemos lo mismo con la variable, a la que hemos llamado @des e indicamos su tipo y el nmero de caracteres mximo que puede recibir. Una vez hecho esto hacemos la seleccin con TOP 1 para que solo se nos muestre un resultado de nombrecliente. Hacemos el from y las uniones de tablas pertinentes para que los datos que mostramos sean los que pertenecen a la descripcin del producto que introducimos, e indicamos con la clausula WHERE que la descripcin sea igual a la variable que introducimos, es decir a la descripcin del producto, hacemos el group by correspondiente y por ltimo lo ordenamos con el contador todos los resultados en orden DESC que junto con la clausula TOP 1 que indicamos al principio del cuerpo del procedimiento mostrara solo el nombre del cliente que ms unidades ha comprado de dicho producto. CREATE PROCEDURE ejer6 @des varchar(30) AS SELECT TOP 1 nombrecliente FROM (clientes INNER JOIN pedidos ON clientes.numclie = pedidos.clie) INNER JOIN productos ON pedidos.producto = productos.idproducto WHERE descripcion = @des GROUP BY nombrecliente ORDER BY COUNT(*) DESC

PRIMERO DE ASIR

Pgina 10

[EJERCICIOS DE PROCEDIMIENTOS]

COMPROBACIN DE FUNCIONAMIENTOHacemos una consulta a la tabla pedidos y vemos que tanto el cliente 2112 comparte el mismo producto en el pedido con el 2113 y a su vez el 2106 con el 2107, pero los que vemos en la imagen marcados en rojo son los que han comprado ms unidades del producto

Hacemos la consulta pertinente para ver los nmeros de clientes a los que pertenecen dichos nmeros de clientes.

Y en la tabla productos hacemos la consulta para ver que descripcin es la de tales productos.

Ejecutamos el procedimiento preguntando por la descripcin del producto bomba r y vemos que el resultado que nos da es Mara Silva es correcto, podemos comprobarlo en la tabla del principio de la pgina.

Hacemos lo mismo con junta y vemos que el resultado, tambien es correcto y el que mas ha comprado es Juan Suarez

PRIMERO DE ASIR

Pgina 11

[EJERCICIOS DE PROCEDIMIENTOS]7. Escribir un procedimiento que reciba el nombre de un empleado y devuelva el n de clientes que ha atendido. Creamos el procedimiento y le indicamos el nombre, hacemos lo mismo con la variable a la que llamamos @emple e indicamos el tipo de datos que va a aceptar y el nmero mximo de caracteres que va a admitir hacemos una seleccin con la funcin count, para contar el nmero de clientes que ha atendido. Todo esto lo hacemos con un INNER JOIN correspondiente entre las tablas pedidos y empleados donde comprobaremos la igualdad entre numemp y rep para que la relacin se lleve a cabo de forma correcta entre los pedidos y el empleado que indicamos. El que indicamos viene dado por la variable que habamos creado de ah que la igualdad where nombre sea sobre esta, por ltimo hacemos la agrupacin con el group by correspondiente es decir, sobre nombre. CREATE PROCEDURE ejer7 @emple VARCHAR(30) AS SELECT COUNT(*)AS 'numero de clientes atendidos' FROM empleados INNER JOIN pedidos ON empleados.numemp = pedidos.rep WHERE nombre = @emple GROUP BY nombre

COMPROBACIN DE FUNCIONAMIENTOHacemos la seleccin correspondiente a la tabla pedidos y tomamos del campo rep, los que vemos en el recuadro, pedidos que han sido llevados a cabos por esos empleados.

Vemos consultndola tabla empleados a quienes pertenecen esos nmeros.

Ejecutamos el procedimiento con el empleado Alvaro Jaumes y vemos que los clientes que ha atendido, son tres.

Hacemos los mismo con Juan Rovira dando como resultado que ha atendido a dos clientes.

PRIMERO DE ASIR

Pgina 12

[EJERCICIOS DE PROCEDIMIENTOS]8. Inserta dos nuevos campos en la tabla Pedidos: "pagado" (booleano) y "fecha pago". Actualiza el campo pagado a falso (de todos los pedidos). Crea un procedimiento que reciba el n de pedido y actualice el campo pagado (a true) e inserte en el campo "fecha pago" el dato de la fecha en la que se ejecuta el procedimiento. Creamos el procedimiento y la variable, en la cual indicamos que el tipo de dato que va a aceptar sean enteros. Actualizamos la tabla pedidos y declaramos que el valor sea 1 en el campo pagado, cuando el numero de pedido sea, el que nosotros introducimos y que esta dentro de la variable y por ltimo actualizamos de nuevo la tabla pedidos indicando que el valor de fechapago sea igual a la fecha actual del sistema en la que se esta ejecutando el procedimiento donde el numero de pedido, se recoger tambin de la variable. CREATE PROC ejer8 @pedido INT AS UPDATE pedidos SET pagado = 1 WHERE numpedido = @pedido UPDATE pedidos SET fechapago = GETDATE() WHERE numpedido = @pedido COMPROBACIN DE FUNCIONAMIENTO Creamos los campos que se nos indican antes de ejecutar el procedimiento.

Indicamos que el valor predeterminado para el campo pagado sea 0.

Ejecutamos el procedimiento con el nmero de pedido que queramos.

PRIMERO DE ASIR

Pgina 13

[EJERCICIOS DE PROCEDIMIENTOS]Por ltimo, consultamos la tabla pedidos y vemos como se ha llevado a cabo perfectamente.

PRIMERO DE ASIR

Pgina 14