Aerolíneas Corregidas - AGMadera/BasedeDatos2 GitHub Wiki

CREATE TABLE AEROLINEA(
ID_AEROLINEA INTEGER,
NOMBRE VARCHAR2 (120),
CONSTRAINT PK_ID_AEROLINEA primary key (ID_AEROLINEA));

CREATE TABLE AVION(
ID_AVION INTEGER,
ID_AEROLINEA INTEGER,
ASIENTOS INTEGER,
CONSTRAINT PK_ID_AVION primary key (ID_AVION),
CONSTRAINT FK_ID_AEROLINEA  foreign key (ID_AEROLINEA) REFERENCES AEROLINEA(ID_AEROLINEA));

CREATE SEQUENCE SEC_AEROLINEA
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

CREATE SEQUENCE SEC_AVION
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

CREATE OR REPLACE PROCEDURE GUARDAR_AEROLINEA(
my_ID_AEROLINEA OUT INTEGER, my_Nombre IN VARCHAR2)
AS
BEGIN
SELECT SEC_AEROLINEA.NEXTVAL INTO my_ID_AEROLINEA FROM DUAL;
INSERT INTO AEROLINEA VALUES (my_ID_AEROLINEA, my_Nombre);
END;
/

CREATE OR REPLACE PROCEDURE GUARDAR_AVION(
my_ID_AVION OUT INTEGER, my_ID_AEROLINEA OUT INTEGER, my_ASIENTOS IN INTEGER)
AS
BEGIN
SELECT SEC_AVION.NEXTVAL INTO my_ID_AVION FROM DUAL;
SELECT SEC_AEROLINEA.CURRVAL INTO my_ID_AEROLINEA FROM DUAL;
INSERT INTO AVION VALUES (my_ID_AVION, my_ID_AEROLINEA, my_ASIENTOS);
END;
/
--Se guarda automáticamente el valor(ID) anteriormente en memoria de AEROLINEA
---Lo Correcto seria:
 CREATE OR REPLACE PROCEDURE GUARDAR_AVION(
 my_ID_AVION OUT INTEGER, my_ID_AEROLINEA IN INTEGER, my_ASIENTOS IN INTEGER)
 AS
 BEGIN
 SELECT SEC_AVION.NEXTVAL INTO my_ID_AVION FROM DUAL;
 INSERT INTO AVION VALUES (my_ID_AVION, my_ID_AEROLINEA, my_ASIENTOS);
 END;
 /
--- my_ID_AEROLINEA se inserta de manera manual

DECLARE
ALGO INTEGER;
BEGIN 
GUARDAR_AEROLINEA (ALGO, 'MEXICANA');
END;
/


DECLARE
ALGO INTEGER;
ALGO2 INTEGER;
BEGIN 
GUARDAR_AVION (ALGO, ALGO2, 200);
END;
/

SELECT * FROM AVION

SELECT * FROM AEROLINEA