none
Crear procedimientos y funciones para manejo de prestamos de libros. RRS feed

  • Pregunta

  • Buen dia,
    Como puedo realizar un procedimiento para realizar prestamos de libros con las siguientes instrucciones: solo puede prestar a usuarios que no tienen préstamos activos, solo puede prestar tres libros al mismo tiempo y solo libros que tienen copias disponibles.

    Luego en las devoluciones debe pedir el código de usuario y mostrar el préstamo activo con los libros, seleccionar los libros a devolver, si no entrega todos los libros, el préstamo sigue activo, hasta que devuelve todos los libros, debe mostrar un mensaje con la multa asignada en caso que tanga multa.

    Tambien tengo que mostrar reportes, debo mostrar los préstamos activos, al seleccionar un préstamo, se debe mostrar el detalle de los libros prestados.

    Esta es la funcion del ingreso de los prestamos, logro ingresar un maximo de 3 libros por usuario, pero los voy ingresando uno por uno, no se si se pueden ingresar los 3 al mismo tiempo.

    Soy muy nuevo en SQL -__- 
    Espero me puedan ayudar, saludos comunidad.

    ALTER PROCEDURE Nuevo_Prestamo
    	@COD_PRESTAMO varchar(10),
    	@DESCRIPCION text,
    	@VALOR_TOTAL_PRESTAMO money,
    	@ESTADO int,
    	@COD_USUARIO varchar(20),
    	@rst int output
    AS
    BEGIN
    	Declare @cta int
    	Declare @ctaestado int;
    
    	Select @cta = count (*)
    	from PRESTAMO
    	where COD_USUARIO = @COD_USUARIO
    
    	if @cta > 2
    	Begin
    		set @rst = -1 --SOLO 3 LIBROS POR URUARIO
    		return
    	End
    	
    	Insert Into PRESTAMO
    	values (@COD_PRESTAMO, @DESCRIPCION, @VALOR_TOTAL_PRESTAMO, @ESTADO, @COD_USUARIO)
    	
    
    	set @rst = 0
    END
    Tabla Usuarios:
    CREATE TABLE [dbo].[USUARIOS](
    	[COD_USUARIO] [varchar](20) NOT NULL,
    	[CLAVE] [varbinary](8000) NOT NULL,
    	[TIPO_USUARIO] [int] NOT NULL,
    	[ESTADO] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[COD_USUARIO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    Tabla Libros:

    CREATE TABLE [dbo].[LIBROS](
    	[COD_LIBRO] [varchar](10) NOT NULL,
    	[NOMBRE] [varchar](100) NOT NULL,
    	[VALOR_LIBRO] [money] NOT NULL,
    	[VALOR_PRESTAMO] [money] NOT NULL,
    	[CANTIDAD] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[COD_LIBRO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    Tabla Prestamos:

    CREATE TABLE [dbo].[PRESTAMO](
    	[COD_PRESTAMO] [varchar](10) NOT NULL,
    	[DESCRIPCION] [text] NOT NULL,
    	[VALOR_TOTAL_PRESTAMO] [money] NOT NULL,
    	[ESTADO] [int] NOT NULL,
    	[COD_USUARIO] [varchar](20) NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[COD_PRESTAMO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    Tabla Detalles Prestamos:

    CREATE TABLE [dbo].[DETALLES_PRESTAMOS](
    	[COD_PRESTAMO] [varchar](10) NOT NULL,
    	[COD_LIBRO] [varchar](10) NOT NULL,
    	[DIAS_PRESTAMO] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[COD_PRESTAMO] ASC,
    	[COD_LIBRO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    martes, 17 de septiembre de 2019 22:32

Respuestas

  • Hola TheRealJoker7:

    Voy a intentar darte una aproximación a lo que tienes.

    CREATE TABLE [dbo].[USUARIOS](
    	[COD_USUARIO] [varchar](20) NOT NULL,
    	[CLAVE] [varbinary](8000) NOT NULL,
    	[TIPO_USUARIO] [int] NOT NULL,
    	[ESTADO] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[COD_USUARIO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    CREATE TABLE [dbo].[LIBROS](
    	[COD_LIBRO] [varchar](10) NOT NULL,
    	[NOMBRE] [varchar](100) NOT NULL,
    	[VALOR_LIBRO] [money] NOT NULL,
    	[VALOR_PRESTAMO] [money] NOT NULL, /* ESTO NO PARECE UNA PROPIEDAD DEL LIBRO. DEBERÍAS DE ELIMINARLA.  */
    	[CANTIDAD] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[COD_LIBRO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[PRESTAMO](
    	[COD_PRESTAMO] INT IDENTITY (1,1), /* PORQUE VARCHAR, COMO SE GENERA?????, te lo cambio por una clave identity. */
    	[DESCRIPCION] [text] NOT NULL, /* para que una columna text. Esta en desuso. Cambiar por NVARCHAR(MAX) o VARCHAR(MAX) */
    	[VALOR_TOTAL_PRESTAMO] [money] NOT NULL,
    	[ESTADO] [int] NOT NULL,
    	[COD_USUARIO] [varchar](20) NOT NULL,
    	[FECHA_PRESTAMO] DateTime,
    PRIMARY KEY CLUSTERED 
    (
    	[COD_PRESTAMO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    /* para que utilizar un detalle si solo puedes prestar al usuario 1 prestamo de cada vez, y del mismo libro. */
    
    
    INSERT INTO [dbo].[USUARIOS]
    ([COD_USUARIO],[CLAVE],[TIPO_USUARIO],[ESTADO])
    VALUES
    ('A',CONVERT(VARBINARY(8000),''),0,0),
    ('B',CONVERT(VARBINARY(8000),''),0,0);
    /* INSERTADOS 2 USUARIOS */
    GO
    INSERT INTO dbo.LIBROS 
    (COD_LIBRO, NOMBRE, VALOR_LIBRO, VALOR_PRESTAMO, CANTIDAD)
    VALUES
    ('LIB1','SQL SERVER 2012',45.3,0,5),
    ('LIB2','SQL ADMINISTRACION',35.2,0,2),
    ('LIB3','SQL SERVER 2016',35.2,0,1),
    ('LIB4','SQL SERVER 2019',35.2,0,4);
    /* NO VOY A UTILIZAR VALOR PRESTAMO */
    GO

    Como observarás, he cambiado varias cosas en tu diseño de tablas, pues no es del todo correcto.

    Un detalle sobre el prestamo. Te he cambiado días, por una fecha. Normalmente se utiliza una fecha de alta. y una fecha de baja y los dias se calculan. De manera que al insertar el registro, grabas la fecha de alta. Cuando el usuario cambia el estado del préstamo por devolución, entonces, además grabas la fecha de baja. Y los días de prestamo son la diferencia. Y si no lo ha devuelto, la diferencia de la fecha de hoy desde el alta.

    Ahora una vuelta a tu procedure.

    CREATE OR ALTER PROCEDURE NUEVO_PRESTAMO
    (
        @COD_USUARIO VARCHAR(20),
        @COD_LIBRO VARCHAR(10),
        @DETALLE_PRESTAMO VARCHAR(MAX), /*MENSAJE A ENGANCHAR AL PRESTAMO */
        @NUM_LIBROS INT=1
        /* POR DEFECTO, SE PRESTA UN SOLO LIBRO (PARAMETROS POR DEFECTO = ) */
    )
    AS
    BEGIN
        DECLARE @MENSAJE NVARCHAR(4000);
        DECLARE @VALOR MONEY;
        IF (@COD_USUARIO = '' OR @COD_USUARIO IS NULL OR @COD_LIBRO IS NULL OR @COD_LIBRO = '' OR @NUM_LIBROS < 1 OR @NUM_LIBROS > 3)
        BEGIN
    	   SET @MENSAJE = N'Parámetros incorrectos'; 
    	   RAISERROR (50000,-1,-1,@MENSAJE);
    	   RETURN;
        END
    
        DECLARE @CANTIDAD INT;
    
        SELECT @CANTIDAD = CANTIDAD, @VALOR = VALOR_LIBRO
    	   FROM LIBROS WHERE COD_LIBRO = @COD_LIBRO;
        /* EVALUAMOS LIBRO */
        IF @CANTIDAD IS NULL OR @CANTIDAD < @NUM_LIBROS
    	BEGIN
    	   SET @MENSAJE = N'Mas libros solicitados para: '+cast(@cod_libro as nvarchar(max)) + ' que en existencias.'
    	   RAISERROR (@MENSAJE,1,1);
            RETURN;
    	  /* SI CONTINUA, HAY LIBROS DISPONIBLES, PARA LO SOLICITADO */
    	END
        
    
        /* solo puede prestar a usuarios que no tienen préstamos activos */
        IF (EXISTS (SELECT * FROM PRESTAMO WHERE COD_USUARIO = @COD_USUARIO and ESTADO = 0))
        /* ENTIENDO EL ESTADO 0 COMO ACTIVO */
        BEGIN
    	   RAISERROR('Dispones de un prestamo activo. No es posible en estas condiciones.',1,1);
    	   RETURN;
        END
    
        /* ya sabemos que no tiene prestamos, y tambien hay libros bastantes. entonces insertamos el prestamo. */
    
        BEGIN TRAN
        BEGIN TRY
    	   declare @idInsertado int;
    	   INSERT INTO PRESTAMO ( DESCRIPCION,	[VALOR_TOTAL_PRESTAMO],	[ESTADO] ,	[COD_USUARIO] , FECHA_PRESTAMO)
    	   VALUES (@DETALLE_PRESTAMO,(@VALOR * @NUM_LIBROS),0,@COD_USUARIO, GETDATE());
    	   COMMIT TRAN
        END TRY
        BEGIN CATCH
    	   
    	   SET @MENSAJE = ERROR_MESSAGE();
    	   DECLARE @ERRORNUMBER INT = ERROR_STATE();
    	   DECLARE @ERRORSEVERITY INT = ERROR_SEVERITY();
    	   RAISERROR (@MENSAJE, @ERRORSEVERITY,@ERRORNUMBER);
    
    	   ROLLBACK TRAN;
    	   RETURN;
        END CATCH
    
    RETURN 	   
    END
    GO
    
    Espero te ayude.

    • Marcado como respuesta TheRealJoker7 viernes, 20 de septiembre de 2019 2:25
    jueves, 19 de septiembre de 2019 20:40

Todas las respuestas

  • Hola TheRealJoker7:

    Existen algunas pequeñas lagunas en la propuesta del procedure.

    	Select @cta = count (*)
    	from PRESTAMO
    	where COD_USUARIO = @COD_USUARIO
    		AND ESTADO = 0;
    /* SUPONGO QUE SOLO SERÁN LOS PRESTAMOS ACTIVOS, PORQUE NO BORRARÁS PRESTAMOS, SOLO CAMBIARÁN DE ESTADO */
    

    Es raro que ya tengas el código del préstamo, cuando todavía no se ha formado. Es bastante más lógico que tengas el código de usuario y el código del libro que quieres, y que por estos dos datos, se obtenga el préstamo.

    Luego en las devoluciones debe pedir el código de usuario y mostrar el préstamo activo con los libros, seleccionar los libros a devolver, si no entrega todos los libros, el préstamo sigue activo, hasta que devuelve todos los libros, debe mostrar un mensaje con la multa asignada en caso que tanga multa.

    Eso parece más bien otro procedure.

    Tambien tengo que mostrar reportes, debo mostrar los préstamos activos, al seleccionar un préstamo, se debe mostrar el detalle de los libros prestados.

    Eso parecen dos procedures diferentes, 1 del maestro y otro del detalle. No parecen tener mucha dificultad porque será una select sobre prestamos para el usuario. Y otro sobre el detalle.

    Esta es la funcion del ingreso de los prestamos, logro ingresar un maximo de 3 libros por usuario, pero los voy ingresando uno por uno, no se si se pueden ingresar los 3 al mismo tiempo.

    Es un poco raro, porque tienes un procedimiento de inserción del prestamo, pero no ingresas el detalle.

    Espero tus comentarios o aclaraciones, y le damos una vuelta

    miércoles, 18 de septiembre de 2019 13:24
  • Hola, gracias por tu tiempo. 

    Pues veras, el código de préstamo no lo tengo, lo único que tengo son los códigos de usuarios y códigos de libros en sus respectivas tablas. 

     Y con ese procediendo estuve probando y no obtuve nada, para crearlo tome de base unos que ya había creado pero no logré hacerlo funcionar como debe de hacerlo según la inducción.  Yo soy bastante nuevo en SQL y no tengo idea como hacer eso que es para presentar un trabajo en la Universidad.  

    jueves, 19 de septiembre de 2019 7:07
  • Hola TheRealJoker7:

    Voy a intentar darte una aproximación a lo que tienes.

    CREATE TABLE [dbo].[USUARIOS](
    	[COD_USUARIO] [varchar](20) NOT NULL,
    	[CLAVE] [varbinary](8000) NOT NULL,
    	[TIPO_USUARIO] [int] NOT NULL,
    	[ESTADO] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[COD_USUARIO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    CREATE TABLE [dbo].[LIBROS](
    	[COD_LIBRO] [varchar](10) NOT NULL,
    	[NOMBRE] [varchar](100) NOT NULL,
    	[VALOR_LIBRO] [money] NOT NULL,
    	[VALOR_PRESTAMO] [money] NOT NULL, /* ESTO NO PARECE UNA PROPIEDAD DEL LIBRO. DEBERÍAS DE ELIMINARLA.  */
    	[CANTIDAD] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[COD_LIBRO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[PRESTAMO](
    	[COD_PRESTAMO] INT IDENTITY (1,1), /* PORQUE VARCHAR, COMO SE GENERA?????, te lo cambio por una clave identity. */
    	[DESCRIPCION] [text] NOT NULL, /* para que una columna text. Esta en desuso. Cambiar por NVARCHAR(MAX) o VARCHAR(MAX) */
    	[VALOR_TOTAL_PRESTAMO] [money] NOT NULL,
    	[ESTADO] [int] NOT NULL,
    	[COD_USUARIO] [varchar](20) NOT NULL,
    	[FECHA_PRESTAMO] DateTime,
    PRIMARY KEY CLUSTERED 
    (
    	[COD_PRESTAMO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    /* para que utilizar un detalle si solo puedes prestar al usuario 1 prestamo de cada vez, y del mismo libro. */
    
    
    INSERT INTO [dbo].[USUARIOS]
    ([COD_USUARIO],[CLAVE],[TIPO_USUARIO],[ESTADO])
    VALUES
    ('A',CONVERT(VARBINARY(8000),''),0,0),
    ('B',CONVERT(VARBINARY(8000),''),0,0);
    /* INSERTADOS 2 USUARIOS */
    GO
    INSERT INTO dbo.LIBROS 
    (COD_LIBRO, NOMBRE, VALOR_LIBRO, VALOR_PRESTAMO, CANTIDAD)
    VALUES
    ('LIB1','SQL SERVER 2012',45.3,0,5),
    ('LIB2','SQL ADMINISTRACION',35.2,0,2),
    ('LIB3','SQL SERVER 2016',35.2,0,1),
    ('LIB4','SQL SERVER 2019',35.2,0,4);
    /* NO VOY A UTILIZAR VALOR PRESTAMO */
    GO

    Como observarás, he cambiado varias cosas en tu diseño de tablas, pues no es del todo correcto.

    Un detalle sobre el prestamo. Te he cambiado días, por una fecha. Normalmente se utiliza una fecha de alta. y una fecha de baja y los dias se calculan. De manera que al insertar el registro, grabas la fecha de alta. Cuando el usuario cambia el estado del préstamo por devolución, entonces, además grabas la fecha de baja. Y los días de prestamo son la diferencia. Y si no lo ha devuelto, la diferencia de la fecha de hoy desde el alta.

    Ahora una vuelta a tu procedure.

    CREATE OR ALTER PROCEDURE NUEVO_PRESTAMO
    (
        @COD_USUARIO VARCHAR(20),
        @COD_LIBRO VARCHAR(10),
        @DETALLE_PRESTAMO VARCHAR(MAX), /*MENSAJE A ENGANCHAR AL PRESTAMO */
        @NUM_LIBROS INT=1
        /* POR DEFECTO, SE PRESTA UN SOLO LIBRO (PARAMETROS POR DEFECTO = ) */
    )
    AS
    BEGIN
        DECLARE @MENSAJE NVARCHAR(4000);
        DECLARE @VALOR MONEY;
        IF (@COD_USUARIO = '' OR @COD_USUARIO IS NULL OR @COD_LIBRO IS NULL OR @COD_LIBRO = '' OR @NUM_LIBROS < 1 OR @NUM_LIBROS > 3)
        BEGIN
    	   SET @MENSAJE = N'Parámetros incorrectos'; 
    	   RAISERROR (50000,-1,-1,@MENSAJE);
    	   RETURN;
        END
    
        DECLARE @CANTIDAD INT;
    
        SELECT @CANTIDAD = CANTIDAD, @VALOR = VALOR_LIBRO
    	   FROM LIBROS WHERE COD_LIBRO = @COD_LIBRO;
        /* EVALUAMOS LIBRO */
        IF @CANTIDAD IS NULL OR @CANTIDAD < @NUM_LIBROS
    	BEGIN
    	   SET @MENSAJE = N'Mas libros solicitados para: '+cast(@cod_libro as nvarchar(max)) + ' que en existencias.'
    	   RAISERROR (@MENSAJE,1,1);
            RETURN;
    	  /* SI CONTINUA, HAY LIBROS DISPONIBLES, PARA LO SOLICITADO */
    	END
        
    
        /* solo puede prestar a usuarios que no tienen préstamos activos */
        IF (EXISTS (SELECT * FROM PRESTAMO WHERE COD_USUARIO = @COD_USUARIO and ESTADO = 0))
        /* ENTIENDO EL ESTADO 0 COMO ACTIVO */
        BEGIN
    	   RAISERROR('Dispones de un prestamo activo. No es posible en estas condiciones.',1,1);
    	   RETURN;
        END
    
        /* ya sabemos que no tiene prestamos, y tambien hay libros bastantes. entonces insertamos el prestamo. */
    
        BEGIN TRAN
        BEGIN TRY
    	   declare @idInsertado int;
    	   INSERT INTO PRESTAMO ( DESCRIPCION,	[VALOR_TOTAL_PRESTAMO],	[ESTADO] ,	[COD_USUARIO] , FECHA_PRESTAMO)
    	   VALUES (@DETALLE_PRESTAMO,(@VALOR * @NUM_LIBROS),0,@COD_USUARIO, GETDATE());
    	   COMMIT TRAN
        END TRY
        BEGIN CATCH
    	   
    	   SET @MENSAJE = ERROR_MESSAGE();
    	   DECLARE @ERRORNUMBER INT = ERROR_STATE();
    	   DECLARE @ERRORSEVERITY INT = ERROR_SEVERITY();
    	   RAISERROR (@MENSAJE, @ERRORSEVERITY,@ERRORNUMBER);
    
    	   ROLLBACK TRAN;
    	   RETURN;
        END CATCH
    
    RETURN 	   
    END
    GO
    
    Espero te ayude.

    • Marcado como respuesta TheRealJoker7 viernes, 20 de septiembre de 2019 2:25
    jueves, 19 de septiembre de 2019 20:40
  • Hola, muchas gracias,

    Lo probé y todo esta muy bien :) 

    ¿Como hago para que en vez de retornar los mensajes me retorne solo números? 
    Los números harían referencia a cada acción, 

    Disculpas por las molestias,

    Saludos.

    viernes, 20 de septiembre de 2019 2:25
  • Hola TheRealJoker7:

    CREATE OR ALTER PROCEDURE NUEVO_PRESTAMO
    (
        @COD_USUARIO VARCHAR(20),
        @COD_LIBRO VARCHAR(10),
        @DETALLE_PRESTAMO VARCHAR(MAX), /*MENSAJE A ENGANCHAR AL PRESTAMO */
        @NUM_LIBROS INT=1,
        @RESULTADO INT OUTPUT
    )
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @VALOR MONEY;
        IF (@COD_USUARIO = '' OR @COD_USUARIO IS NULL OR @COD_LIBRO IS NULL OR @COD_LIBRO = '' OR @NUM_LIBROS < 1 OR @NUM_LIBROS > 3)
        BEGIN
    	   SET @RESULTADO = -1;
    	   RETURN;
        END
    
        -- …..
    
    RETURN 	   
    END
    GO

    Utilizando parámetros tipo OUT

    DECLARE @RESULTADO INT
    EXEC NUEVO_PRESTAMO '','LIB1','MI SOLICITUD',1, @RESULTADO OUT;
    PRINT @RESULTADO;

    Salida


    viernes, 20 de septiembre de 2019 3:30
  • Gracias Javi...
    Saludos.
    sábado, 21 de septiembre de 2019 3:10
  • De nada
    sábado, 21 de septiembre de 2019 5:06