Practica Guiada:

PROCEDIMIENTOS ALMACENADOS

Referencias de Base de Datos: AdventureWorks

Guiones de creación
En lugar de restaurar una base de datos, alternativamente, puede usar scripts
para crear las bases de datos AdventureWorks independientemente de la
versión.

Los siguientes scripts se pueden utilizar para crear la base de datos de

AdventureWorks completa:

• Zip de scripts de AdventureWorks OLTP

• Zip de scripts AdventureWorks DW

Se puede encontrar información adicional sobre el uso de los scripts en GitHub .

https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-
server-ver15&tabs=ssms

Veamos el diseño de algunos ejemplos:

Ejemplo 1: Instrucción SQL

USE AdventureWorks
Select name, Color, ListPrice, SellStartDate
FROM Production.Product
WHERE SellStartDate > '1/1/2003'
ORDER BY SellStartDate, Name

Procedimiento con instrucción anterior

CREATE PROCEDURE PROCE1_CARNET

BEGIN
Select name, Color, ListPrice, SellStartDate
FROM Production.Product
WHERE SellStartDate > '1/1/2003'
 ORDER BY SellStartDate, Name
END;

Para probar el nuevo procedimiento en SQL y ejecute el código siguiente.

exec PROCE1_CARNET;

Nota: los procedimientos almacenados los puede encontrar en la base de datos donde los
trabaja, en la opción programación.

Ejemplo2:

Utilizando parámetros de entrada:

Ya conocemos la sintaxis básica para la creación de procedimientos que es:

CREATE PROCEDURE NOMBRE

BEGIN
Sentencias

Ahora si queremos utilizar parámetros de entrada antes de escribir el comando BEGIN,

debemos digitar los datos que recibirá el procedimiento y el tipo de dato de la siguiente
manera:

CREATE PROCEDURE NOMBRE

(parametro1 tipo,parametro2 tipo,…
BEGIN
Cuando se llame al procedimiento y se ingresen los parámetros, estos se almacenarán en el
orden que se declararon.

Digite el siguiente procedimiento:

USE library
CREATE PROCEDURE ingreso(
IN apellido varchar(15), IN nombre varchar(15))
BEGIN
insert into member(lastname,firstname)
values(apellido,nombre)
SELECT 'eL REGISTRO SE HA INGRESADO CORRECTAMENTE'

Como puede ver el procedimiento pide dos parámetros apellido y nombre del tipo varchar,
cuando se llame al procedimiento deberá digitarse primero el apellido y después el nombre,
porque ese es el nombre que se le ha fijado en el desarrollo del procedimiento. Para llamar
este procedimiento utilice la siguiente sentencia.
USE library
EXEC ingreso 'Moran','Gustavo'

Para comprobar que se ingresó el registro utilice la siguiente instrucción y busque el registro
que acaba de ingresar con el procedimiento.

Select * from member

Si alguien ingresa un valor nulo

EXEC ingreso '','Jose'

El procedimiento lo aceptara sin enviar ningún error, entonces podemos modificar el

procedimiento de la siguiente manera:

ALTER PROCEDURE ingreso(in apellido varchar(15),in nombre

varchar(15))
BEGIN
IF((nombre='') OR (apellido=''))
BEGIN
SELECT 'NO SE PUEDEN INGRESAR VALORES NULOS'
RETURN
END
ELSE
BEGIN
insert into member(lastname,firstname)
values(apellido,nombre)
SELECT 'eL REGISTRO SE HA INGRESADO CORRECTAMENTE'
END

Los BEGIN y END son el inicio y fin del if Y RETURN provoca la salida del procedimiento tras
enviar un mensaje a la pantalla del usuario.

Pruebe ingresar nuevamente EXEC ingreso '','Karla'

Reglas de procedimientos almacenados:

Entre las reglas para la programación de procedimientos almacenados, cabe citar las
siguientes:
• La propia definición CREATE PROCEDURE puede incluir cualquier número y tipo de
instrucciones SQL, excepto las siguientes instrucciones CREATE, que no pueden ser
utilizadas nunca dentro de un procedimiento almacenado:
 CREATE DEFAULT CREATE TRIGGER
CREATE PROCEDURE CREATE VIEW
CREATE RULE
• Se puede crear otros objetos de base de datos dentro de un procedimiento almacenado.
Puede hacer referencia a un objeto creado en el mismo procedimiento almacenado,
siempre que se cree antes de que se haga referencia al objeto.
• Puede hacer referencia a tablas temporales dentro de un procedimiento almacenado.
• Si ejecuta un procedimiento almacenado que llama a otro procedimiento almacenado,
el procedimiento al que se llama puede tener acceso a todos los objetos creados por el
primer procedimiento, incluidas las tablas temporales.
• El número máximo de parámetros en un procedimiento almacenado es de 1,024.
• El número máximo de variables locales en un procedimiento almacenado está limitado
únicamente por la memoria disponible.

Use de variables locales.

Una variable local de Transact-SQL es un objeto que contiene un valor individual de datos de
un tipo específico

Las variables locales se definen debajo de la sentencia BEGIN y llevan el prefijo DECLARE.

Create PROCEDURE ingreso2(in nombre varchar(15), in apellido

varchar(15) )
BEGIN
Declare n varchar(4)
Declare a int

Ejemplo 3:

Con el procedimiento anterior se verifico lo de ingresar valores nulos, ahora vamos a ver que el
nombre completo del usuario (nombre y apellido) no debe repetirse en la base de datos, para
ello utilizamos el siguiente código.

Modifique los siguientes procedimientos para lograr el objetivo

ALTER PROCEDURE ingreso(@apellido varchar(15), @nombre

varchar(15))
AS
DECLARE nom varchar(15)—-variable local
DECLARE ape varchar(15)—-variable local
--revisamos que las variables no sean nulas
--PRIMER IF EXTERNO si las variales son diferentes de null entra
al if
--de lo contario sale y mande que no pueden ser valores nulos
IF((nombre<>'') AND (apellido<>''))
 BEGIN
--guardamos el nombre y el apellido en las variables locales @non
y @ape
SELECT nom=firstname, ape=lastname from member where
firstname=nombre and lastname=apellido
--IF INTERNO comparamos el valor capturado en @nom y @ape y
los comparamos
--con los parametros de entrada
IF((nom=nombre) AND (ape=apellido))
BEGIN
SELECT 'EL USUARIO YA EXISTE EN LA BASE DE DATOS'
RETURN
END--END DEL IF INTERNO
ELSE--ELSE DEL IF INTERNO
BEGIN
INSERT INTO member(lastname,firstname)
values(apellido,nombre)
SELECT 'EL REGISTRO SE HA INGRESADO CORRECTAMENTE'
END--END DEL ELSE INTERNO
END--END DEL IF EXTERNO
ELSE--ELSE DEL IF EXTERNO
BEGIN
SELECT 'NO SE PUEDEN INGRESAR VALORES NULOS'
RETURN
END--END DEL ELSE EXTERNO

Intente ingresar el nombre y el mismo apellido con este procedimiento EXEC

ingreso 'Moran', 'Gustavo'

Procedimiento

CREATE DATABASE bodega GO

use bodega

CREATE TABLE PRODUCTOS

(
idprod char(7) PRIMARY KEY,
descripcion varchar(25),
existencias int
)

CREATE TABLE PEDIDOS

(
idpedido char(7), idprod Ejecute el siguiente Script
char(7), cantidad int
FOREIGN KEY(idprod) REFERENCES
PRODUCTOS(idprod)
)

La tabla productos contiene información general de los productos y la tabla pedidos contiene
la información del pedido que se realiza de un cierto producto.

Ejemplos de datos
PRODUCTOS

idprod descripcion existencias

Proc01 manzanas 10

PEDIDOS

idpedido idprod cantidad

Ped01 Proc01 2

RESOLVER

1. Crear un procedimiento almacenado que ingrese los valores en la tabla PRODUCTOS, y

deberá verificar que el código del producto no exista para poder insertarlo, en caso que
 el código del producto ya exista enviar un mensaje que diga ESTE PRODUCTO YA HA SIDO
INGRESADO.

2. Crear un procedimiento almacenado que permita realizar un pedido EN LA TABLA

PEDIDOS, este procedimiento deberá verificar si el código del producto ingresado existe
en la tabla PRODUCTO, además si la cantidad a pedir del producto es mayor a la
existencia del producto deberá enviar un mensaje que diga EXISTENCIA DEL PRODUCTO
INSUFICIENTE, en caso que la cantidad a pedir sea menor o igual deberá modificar el
valor de la existencia.

Ejemplo:

idprod descripcion existencias

Proc01 manzanas 10

Se realiza un pedido del producto Proc01 y se pide de cantidad 6, al terminar el procedimiento

el registro de ese producto deberá ser:

idprod descripcion existencias

Proc01 manzanas 4
create table ARTICULO (
ARTICULO CHAR(255) not null,
DESCRIPCION CHAR(255),
PESO FLOAT,
constraint PK_ARTICULO primary key (ARTICULO)
);
create table ARTICULO_BODEGA (
BODEGA VARCHAR2(15) not null,
ARTICULO CHAR(255) not null,
constraint PK_ARTICULO_BODEGA primary key (BODEGA, ARTICULO)
);
create index ARTICULO_BODEGA_FK on ARTICULO_BODEGA (
BODEGA ASC
);
create index ARTICULO_BODEGA_FK2 on ARTICULO_BODEGA (
ARTICULO ASC
);
create table BODEGA (
BODEGA VARCHAR2(15) not null,
LINEA CHAR(38),
NOMBRE_BODEGA VARCHAR2(255),
EXISTENCIA FLOAT,
constraint PK_BODEGA primary key (BODEGA)
);
create index BODEGA_FACTURA_DETALLE_FK on BODEGA (
LINEA ASC
);
create table COBRADOR (
COBRAR VARCHAR2(15) not null,
NOMBRE_COBRADOR VARCHAR2(255),
constraint PK_COBRADOR primary key (COBRAR)
);
create table FACTURA (
TIPO_FACTURA CHAR(1) not null,
NOFACTURA CHAR(12) not null,
VENDEDOR VARCHAR2(15),
COBRAR VARCHAR2(15),
ASIENTO_CONTABLE CHAR(10),
COMISION BINARY_DOUBLE,
TOTAL_PESO BINARY_DOUBLE,
IMPUESTO BINARY_DOUBLE,
FECHA DATE,
MONTO_DESCUENTO BINARY_DOUBLE,
PORC_DESCUENTO BINARY_DOUBLE,
TOTAL_FACTURA BINARY_DOUBLE,
DIRECCION_FACTURA CHAR(1),
SIGNO BINARY_DOUBLE,
OBSERVACIONES CHAR(1),
USUARIO_CREADOR CHAR(10),
TIPO_CAMBIO DATE,
FCONTROL_CREACION DATE,
FCONTRO_MODIFICACION DATE,
VALOR_IVA FLOAT,
constraint PK_FACTURA primary key (TIPO_FACTURA, NOFACTURA)
);

comment on column FACTURA.SIGNO is

'este campo contiene dos valores relacionados con el tipo de documento
si es devolucion tiene -1 si es factura tiene 1 y si es anulacion
tiene 0.';
create index COBRADOE_FACTURA_FK on FACTURA (
 COBRAR ASC
);
create index FACTURA_VENDEDOR_FK on FACTURA (
VENDEDOR ASC
);
create table FACTURA_DETALLE (
LINEA CHAR(38) not null,
TIPO_FACTURA CHAR(1),
NOFACTURA CHAR(12),
ARTICULO CHAR(255),
CANTIDAD BINARY_DOUBLE,
PRECIO_UNITARIO BINARY_DOUBLE,
TOTAL_IMPUESTO1_1 BINARY_DOUBLE,
DESC_TOT_GENERAL BINARY_DOUBLE,
COSTO_TOTAL BINARY_DOUBLE,
PRECIO_TOTAL BINARY_DOUBLE,
constraint PK_FACTURA_DETALLE primary key (LINEA)
);

comment on table FACTURA_DETALLE is

'Contiene el detalle de toda la facturacion registrada dentro del
sistema.';
create index FACTURA_FACTURA_LINEA_FK on FACTURA_DETALLE (
TIPO_FACTURA ASC,
NOFACTURA ASC
);
create index ARTICULO_FACTURA_DETALLE_FK on FACTURA_DETALLE (
ARTICULO ASC
);
create table VENDEDOR (
VENDEDOR VARCHAR2(15) not null,
NOMBRE VARCHAR2(255),
constraint PK_VENDEDOR primary key (VENDEDOR)
);
alter table ARTICULO_BODEGA
add constraint FK_ARTICULO_ARTICULO__ARTICULO foreign key
(ARTICULO)
references ARTICULO (ARTICULO);
alter table ARTICULO_BODEGA
add constraint FK_ARTICULO_ARTICULO__BODEGA foreign key (BODEGA)
references BODEGA (BODEGA);

alter table BODEGA

add constraint FK_BODEGA_BODEGA_FA_FACTURA_ foreign key (LINEA)
references FACTURA_DETALLE (LINEA);

alter table FACTURA

add constraint FK_FACTURA_COBRADOE__COBRADOR foreign key (COBRAR)
references COBRADOR (COBRAR);

alter table FACTURA

add constraint FK_FACTURA_FACTURA_V_VENDEDOR foreign key (VENDEDOR)
references VENDEDOR (VENDEDOR);

alter table FACTURA_DETALLE

add constraint FK_FACTURA__ARTICULO__ARTICULO foreign key
(ARTICULO)
references ARTICULO (ARTICULO);

alter table FACTURA_DETALLE

 add constraint FK_FACTURA__FACTURA_F_FACTURA foreign key
(TIPO_FACTURA, NOFACTURA)
references FACTURA (TIPO_FACTURA, NOFACTURA);
Practica: Procedimientos almacenados
Crear la siguiente base de datos “ligadefutbol”, en SQL Server y agregarle una tabla, la
siguiente:

CREATE TABLE jugador (

id int(2) NOT NULL auto_increment,
nombre varchar(60) NOT NULL,
anio_nac int(4) default NULL,
categoria varchar(20) default NULL,
PRIMARY KEY (id)
) ;

Crear un procedimiento almacenado que permita, registrar a un jugador dado su nombre y

año de nacimiento, se calculara su edad y dependiendo de esta, se registrara en la categoría
correspondiente, de 18 a 20 años en la categoría “Sub 20”, de 21 a 23 años en la categoría
“Sub 22”, si el rango de edad no entra en ninguno de los anteriores, se registrara al jugador
como “Sin categoría”.

Arreglar el siguiente procedimiento almacenado para “Registrar un Jugador”

CREATE PROCEDURE registrar_jugador(@nombre varchar(60) ,

@anio_nac INT,
@resultado TEXT)
BEGIN
DECLARE edad int ;
DECLARE categoria char(20) ;
-- obtiene la edad del jugador
SET edad = ( DATE_FORMAT(now() ,'%Y') - anio_nac );
-- dependiendo de la edad se registra al jugador en diferentes categor
ias
CASE
WHEN edad=18 OR edad=19 OR edad=20 THEN SET categoria = 'Sub 20’;
WHEN edad=21 OR edad=22 OR edad=23 THEN SET categoria = 'Sub 22’;
ELSE SET categoria = 'Sin categoria';
END CASE;
INSERT INTO jugador (nombre, anio_nac, categoria)
VALUES (nombre, anio_nac, categoria);
-- Cuando se termina de registrar al jugador, se devuelve una respuest
a en formato de texto
SET resultado = CONCAT (‘Jugador ', nombre, ' de ‘, edad, ' años reg
istrado en categoría [‘, categoria, ']’);
END

Probar el procedimiento en SQL:

exec registrar_jugador('Lionel Zaragosa',1990, @resultado);

Use JAVA Netbeans.

Crear un nuevo proyecto “ligaconsp”, añade la libreria *.jar de conexion JDBC y crea una nueva
clase “database.java”, el código para esta clase es:

package ligaconsp;
import java.sql.*;

public class database {

/* DATOS PARA LA CONEXION */
private String bd = "BASE_DE_DATOS";
private String login = "USUARIO";
private String password = "PASSWORD";
private String url = "jdbc:sql://localhost/"+bd;
private Connection conn = null;
//____________________________________________________________________
//Constructor de la clase que se conecta a la base de datos una vez que se cre
a la instancia
public database(){
try{
Class.forName("com.sql.jdbc.Driver");
conn = DriverManager.getConnection(url,login,password);
}catch(SQLException e){
System.out.println(e);
}catch(ClassNotFoundException e){
System.out.println(e);
}
}
//____________________________________________________________________
/* FUNCION PARA LLAMAR A UN PROCEDIMIENTO ALMACENADO
* INPUT: nombre = tipo string, anio_nacimiento = tipo entero
* OUTPUT:String
*/
public String procedure(String nombre, int anio_nacimiento)
{
String resultado=null;
try {
// se crea instancia a procedimiento, los parámetros de entrada y salid
a se simbolizan con el signo ?
CallableStatement proc = conn.prepareCall(" CALL registrar_jugador
(?,?,?) ");
 //se cargan los parámetros de entrada
proc.setString("nombre", nombre);//Tipo String
proc.setInt("anio_nac", anio_nacimiento);//Tipo entero
// parametros de salida
proc.registerOutParameter("resultado", Types.VARCHAR);//Tipo String
// Se ejecuta el procedimiento almacenado
proc.execute();
// devuelve el valor del parametro de salida del procedimiento
resultado = proc.getString("resultado");
}
catch (Exception e) {
System.out.println(e);
}
return resultado;
}
}

Esta clase está dividida en dos partes, el constructor de la clase, donde realizamos la conexión
a la base de datos y un método al que llamamos “procedure”, este método es el que nos
permite ejecutar el procedimiento almacenado en SQL desde Java, esta comentado para que
se pueda entender más fácilmente.

package ligaconsp;
public class Main {
public static void main(String[] args) {
database db = new database();
String r = db.procedure("Abelardo Lopez", 1999);
System.out.println(r);
r = db.procedure("Jaime Rodriguez", 1990);
System.out.println(r);
r = db.procedure("Flabio Portal", 1994);
System.out.println(r);
}
}

ACTIVIDAD FINAL
Al ejecutar el proyecto en netbeans, se debe obtener como resultado:

Jugador Abelardo Lopez de 13 años registrado en categoria [Sin categoria]

Jugador Jaime Rodriguez de 22 años registrado en categoria [Sub 22]
Jugador Flabio Portal de 18 años registrado en categoria [Sub 20]