PL/SQL Package gestionInstituto for Institute Data
Classified in Visual arts
Written at on 
English with a size of 3.08 KB.
Package Specification: gestionInstituto
CREATE OR REPLACE PACKAGE gestionInstituto
AS
Cursors
CURSOR curAlumnos IS SELECT * FROM alumnos;
CURSOR curNotas IS SELECT * FROM notas;
CURSOR curAsignaturas IS SELECT * FROM asignaturas;Functions and Procedures
FUNCTION media (codigo notas.cod%TYPE) RETURN NUMBER;PROCEDURE mediaMayor (codigo notas.cod%TYPE);PROCEDURE verTablas;PROCEDURE insertarAlumno (
NIE alumnos.dni%TYPE,
nombre alumnos.apenom%TYPE,
direccion alumnos.direc%TYPE,
telefono alumnos.telef%TYPE
);END gestionInstituto;Package Body: gestionInstituto
CREATE OR REPLACE PACKAGE BODY gestionInstituto
AS
Function: media
FUNCTION media (codigo notas.cod%TYPE) RETURN NUMBER
IS
mediaNotas NUMBER;
BEGIN
SELECT AVG(nota)
INTO mediaNotas
FROM notas
WHERE cod = codigo;RETURN mediaNotas;
END media;Procedure: mediaMayor
PROCEDURE mediaMayor (codigo notas.cod%TYPE)
IS
mediaNotas NUMBER;
BEGIN
mediaNotas := media(codigo); -- Llamada a la function media-- Display Subject Name
FOR i IN curAsignaturas LOOP
IF i.cod = codigo THEN
DBMS_OUTPUT.PUT_LINE(i.nombre);
DBMS_OUTPUT.PUT_LINE('---------------------');
END IF;
END LOOP;-- Display Students with Grades >= Average
FOR i IN curNotas LOOP
FOR x IN curAlumnos LOOP
IF i.DNI = x.DNI AND i.cod = codigo THEN -- Check subject code too
IF i.nota >= mediaNotas THEN
DBMS_OUTPUT.PUT_LINE(x.apenom);
END IF;
END IF;
END LOOP;
END LOOP;END mediaMayor;Procedure: verTablas
PROCEDURE verTablas
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('ASIGNATURAS');
DBMS_OUTPUT.PUT_LINE('---------------------');
FOR i IN curAsignaturas LOOP
DBMS_OUTPUT.PUT_LINE(i.cod || ' ' || i.nombre);
END LOOP;DBMS_OUTPUT.PUT_LINE(CHR(10)); -- Add a blank line
DBMS_OUTPUT.PUT_LINE('NOTAS');
DBMS_OUTPUT.PUT_LINE('---------------------');
FOR i IN curNotas LOOP
DBMS_OUTPUT.PUT_LINE(i.dni || ' ' || i.cod || ' ' || i.nota);
END LOOP;DBMS_OUTPUT.PUT_LINE(CHR(10)); -- Add a blank line
DBMS_OUTPUT.PUT_LINE('ALUMNOS');
DBMS_OUTPUT.PUT_LINE('---------------------');
FOR i IN curAlumnos LOOP
DBMS_OUTPUT.PUT_LINE(i.dni || ' ' || i.apenom || ' ' || i.direc || ' ' || i.pobla || ' ' || i.telef);
END LOOP;END verTablas;Procedure: insertarAlumno
PROCEDURE insertarAlumno (
NIE alumnos.dni%TYPE,
nombre alumnos.apenom%TYPE,
direccion alumnos.direc%TYPE,
telefono alumnos.telef%TYPE
)
IS
localidad alumnos.pobla%TYPE;
BEGIN
-- Assumption: Get locality from a specific existing student
-- This might need adjustment based on actual requirements
SELECT pobla
INTO localidad
FROM alumnos
WHERE apenom = 'Diaz Fernandez, Maria'; -- Example: Consider parameterizing thisINSERT INTO alumnos (dni, apenom, direc, pobla, telef)
VALUES (NIE, nombre, direccion, localidad, telefono);END insertarAlumno;END gestionInstituto;