Cursores de Banco de Dados

09/07/2017 TRABALHO CURSOR - Documentos Google Banco de Dados II - segunda prof. Cadoná Exercício de Cursores Com base no script que segue realize as rotinas abaixo relacionadas: 1. Criar um procedimento que realize a listagem contendo o nome de todas as disciplinas e seu respectivo curso 2. Criar um procedimento que informado o código de um curso liste o nome das disciplinas 3. Criar um procedimento que informado o código do aluno liste o código da turma que este está matriculado, juntamente com a nota de G1, G2, média parcial, substituição, média final e conceito final 4. Criar uma trigger que execute as seguintes funções ao cadastrar uma nova matricula: - Calcule o valor total da matricula (valortotal=numcreditos * valorcredito ) - Calcule o valor de cada parcela (valorparcela= valortotal/ numparcelas) -Crie a quantidade necessária de DOC’s para cada matricula, de acordo com a quantidade de parcelas (numparcelas). 5. Criar uma trigger na tabela de tbmatricula que ao incluir ou modificar os conceitos cadastrados o sistema deve realizar o cálculo da média parcial (mediap – conforme média da ULBRA), a média final (mediafinal – conforme a média da ULBRA) e armazene no campo conceito “Aprovado” se a média final for maior ou igual a 6 ou reprovado. Caso a quantidade de faltas ultrapassar a 25% da quantidade total de presenças mais faltas, deve ser armazenado no campo conceito “Rep. Faltas”. 6. Crie um procedimento que informado um professor, liste o nome de todas as turmas que este ministra aula juntamente com os alunos matriculados e suas notas de G1, G2, Subst, média final e conceito final. Script de criação das tabelas e inserção de alguns registros no banco -- -- create table professor -- CREATE TABLE tbprofessor( 09/07/2017 TRABALHO CURSOR - Documentos Google pkcodprof INTEGER NOT NULL, nomeprof VARCHAR2(20) ); -- -- create table relacao disciplina professor -- CREATE TABLE tbreldiscprof( fkcoddisc INTEGER NOT NULL, fkcodprof INTEGER NOT NULL ); -- -- create table disciplina CREATE TABLE tbdisciplina( pkcoddisc INTEGER NOT NULL, nomedisc VARCHAR2(25), fkcodcurso INTEGER ); -- -- create table curso -- CREATE TABLE tbcurso( pkcodcurso INTEGER NOT NULL, nomecurso VARCHAR2(20) ); -- -- 09/07/2017 TRABALHO CURSOR - Documentos Google -- create table aluno -- CREATE TABLE tbaluno( pkcodalu INTEGER NOT NULL, nomealu VARCHAR2(25), datanascalu DATE ); -- -- create table turma -- CREATE TABLE tbturma( pkcodturma INTEGER NOT NULL, turmo VARCHAR2(20), fkcodprof INTEGER, fkcoddisc INTEGER ); -- -- create table aluno matricula -- CREATE TABLE tbmatricula( pkcodmatricula INTEGER NOT NULL, fkcodalu INTEGER, numcreditos NUMERIC(10,2), valorcredito NUMERIC(10,2), valortotal NUMERIC(10,2), numparcelas INTEGER, valorparcela NUMERIC(10,2) ); CREATE TABLE tbdoc( pkdoc INTEGER NOT NULL, fkcodmatricula INTEGER, parcela varchar(10), valorparcela NUMERIC(10,2) ); CREATE TABLE tbmatriturma( pkcodmatriturma INTEGER NOT NULL, fkcodturma INTEGER, fkcodmatricula INTEGER, g1 NUMERIC(10,2), g2 NUMERIC(10,2), numpresencas INTEGER, numfaltas INTEGER, mediap NUMERIC(10,2) DEFAULT 0, subst NUMERIC(10,2) DEFAULT 0, mediafinal NUMERIC(10,2) DEFAULT 0, conceito varchar(20) ); -- -- CREATE CONSTRAINTS PRIMARY KEY -- ALTER TABLE tbprofessor ADD CONSTRAINTS tbprofessor_pkcodprof PRIMARY KEY (pkcodprof); ALTER TABLE tbreldiscprof ADD CONSTRAINTS tbrel_pkcoddisc_pkcodprof PRIMARY KEY (fkcoddisc,fkcodprof); ALTER TABLE tbdisciplina ADD CONSTRAINTS tbdisciplina_pkcoddisc PRIMARY KEY (pkcoddisc); INSERT INTO tbprofessor (pkcodprof,nomeprof) VALUES (90,'Eliana'); INSERT INTO tbprofessor (pkcodprof,nomeprof) VALUES (100,'Gugu'); INSERT INTO tbprofessor (pkcodprof,nomeprof) VALUES (101,'Faustao'); INSERT INTO tbcurso (pkcodcurso,nomecurso) VALUES (5,'Informatica'); INSERT INTO tbcurso (pkcodcurso,nomecurso) VALUES (6,'Danca'); INSERT INTO tbcurso (pkcodcurso,nomecurso) VALUES (7,'Geografia'); INSERT INTO tbaluno (pkcodaluno,nomealuno,datanascaluno) VALUES (45,'Ana','10/06/1995'); INSERT INTO tbaluno (pkcodaluno,nomealuno,datanascaluno) VALUES (46,'Mario','21/07/1988'); INSERT INTO tbaluno (pkcodaluno,nomealuno,datanascaluno) VALUES (47,'Carl','12/05/1999'); INSERT INTO tbaluno (pkcodaluno,nomealuno,datanascaluno) VALUES (48,'Joana','13/04/1982'); INSERT INTO tbaluno (pkcodaluno,nomealuno,datanascaluno) VALUES (49,'Carle','06/03/1977'); INSERT INTO tbaluno (pkcodaluno,nomealuno,datanascaluno) VALUES (50,'Rosi','12/01/2000'); INSERT INTO tbaluno (pkcodaluno,nomealuno,datanascaluno) VALUES (51,'Fernanda','15/06/1999'); INSERT INTO tbaluno (pkcodaluno,nomealuno,datanascaluno) VALUES (52,'Amanda','13/09/1997'); INSERT INTO tbdisciplina (pkcoddisc,nomedisc,fkcodcurso) VALUES (69,'Pianol1',7); INSERT INTO tbdisciplina (pkcoddisc,nomedisc,fkcodcurso) VALUES (70,'Musica1',6); INSERT INTO tbdisciplina (pkcoddisc,nomedisc,fkcodcurso) VALUES (71,'Banco',5); INSERT INTO tbdisciplina (pkcoddisc,nomedisc,fkcodcurso) VALUES (72,'Planejc',7); INSERT INTO tbdisciplina (pkcoddisc,nomedisc,fkcodcurso) VALUES (73,'Logica',5); INSERT INTO tbdisciplina (pkcoddisc,nomedisc,fkcodcurso) VALUES (74,'Engenha',5); INSERT INTO tbturna (pkcodturma,turno,fkcodprof,fkcoddisc) VALUES (207,'Tarde',80,74); INSERT INTO tbturna (pkcodturma,turno,fkcodprof,fkcoddisc) VALUES (208,'Manha',80,72); INSERT INTO tbturna (pkcodturma,turno,fkcodprof,fkcoddisc) VALUES (209,'Manha',100,70); INSERT INTO tbturna (pkcodturma,turno,fkcodprof,fkcoddisc) VALUES (210,'Noite',90,73); INSERT INTO tbturna (pkcodturma,turno,fkcodprof,fkcoddisc) VALUES (211,'Tarde',101,70); INSERT INTO tbturna (pkcodturma,turno,fkcodprof,fkcoddisc) VALUES (212,'Manha',80,69); INSERT INTO tbturna (pkcodturma,turno,fkcodprof,fkcoddisc) VALUES (213,'Rarde',90,73); INSERT INTO tbreldiscprof (fkcoddisc,fkcodprof) VALUES (69,70); ALTER TABLE tbcurso ADD CONSTRAINTS tbcurso_pkcodcurso PRIMARY KEY (pkcodcurso); ALTER TABLE tbaluno ADD CONSTRAINTS tbaluno_pkcodaluno PRIMARY KEY (pkcodaluno); ALTER TABLE tbturna ADD CONSTRAINTS tbturna_pkcodturma PRIMARY KEY (pkcodturma); ALTER TABLE tbmatriturma ADD CONSTRAINTS tbmatriturma_pkcodmatriturma PRIMARY KEY (pkcodmatriturma); ALTER TABLE tbmatricula ADD CONSTRAINTS tbmatricula_pkcodmatricula PRIMARY KEY (pkcodmatricula); ALTER TABLE tbdoc ADD CONSTRAINTS tbdoc_pkdoc PRIMARY KEY (pkdoc); -- -- CREATE CONSTRAINTS FOREIGN KEY -- ALTER TABLE tbreldiscprof ADD CONSTRAINTS tbreldiscprof_fkcodprof FOREIGN KEY (fkcodprof) REFERENCES tbprofessor(pkcodprof); ALTER TABLE tbreldiscprof ADD CONSTRAINTS tbreldiscprof_fkcoddisc FOREIGN KEY (fkcoddisc) REFERENCES tbdisciplina(pkcoddisc); ALTER TABLE tbdisciplina ADD CONSTRAINTS tbdisciplina_fkcodcurso FOREIGN KEY (fkcodcurso) REFERENCES tbcurso(pkcodcurso); ALTER TABLE tbturna ADD CONSTRAINTS tbturna_fkcodprof FOREIGN KEY (fkcodprof) REFERENCES tbprofessor(pkcodprof); ALTER TABLE tbturna ADD CONSTRAINTS tbturna_fkcoddisc FOREIGN KEY (fkcoddisc) REFERENCES tbdisciplina(pkcoddisc); ALTER TABLE tbmatriturma ADD CONSTRAINTS tbmatriturma_fkcodturma FOREIGN KEY (fkcodturma) REFERENCES tbturna(pkcodturma); ALTER TABLE tbmatriturma ADD CONSTRAINTS tbmatriturma_fkcodmatricula FOREIGN KEY (fkcodmatricula) REFERENCES tbmatricula(pkcodmatricula); ALTER TABLE tbdoc ADD CONSTRAINTS tbdoc_fkcodmatricula FOREIGN KEY (fkcodmatricula) REFERENCES tbmatricula(pkcodmatricula); -- -- INSERTs -- INSERT INTO tbprofessor (pkcodprof,nomeprof) VALUES (70,'Bozo'); INSERT INTO tbprofessor (pkcodprof,nomeprof) VALUES (80,'Fofao'); 09/07/2017 TRABALHO CURSOR - Documentos Google INSERT INTO tbreldiscprof (fkcoddisc,fkcodprof) VALUES (69,80); INSERT INTO tbreldiscprof (fkcoddisc,fkcodprof) VALUES (70,100); INSERT INTO tbreldiscprof (fkcoddisc,fkcodprof) VALUES (71,70); INSERT INTO tbreldiscprof (fkcoddisc,fkcodprof) VALUES (72,80); INSERT INTO tbreldiscprof (fkcoddisc,fkcodprof) VALUES (70,101); INSERT INTO tbreldiscprof (fkcoddisc,fkcodprof) VALUES (73,90); INSERT INTO tbreldiscprof (fkcoddisc,fkcodprof) VALUES (74,80); RESPOSTA EX1 create or replace procedure exe1 As wnomecurso tbcurso.nomecurso%type; wnomedisc tbdisciplina.nomedsic%type; CURSOR C2 IS SELECT nomecurso, nomedisc from tbdisciplina d inner join tbcurso c on d.fkcodcurso = c.pkcodcurso; begin OPEN C2; loop dbms_output.put_line('***************************************************************'); FETCH C2 INTO wnomecurso, wnomedisc; if (C2%notfound) then exit; end if; dbms_output.put_line('Estou "varrendo" a linha ' || C2%rowcount ); dbms_output.put_line('Nome Curso: ' || wnomecurso); dbms_output.put_line('Nome Disciplina: ' || wnomedisc); end loop; CLOSE C2; end exe1; EXE2 create or replace procedure exe2( cod in tbcurso.pkcodcurso%type) https://docs.google.com/document/d/1QTX7AgMx7PLuNtRjxeoELlpYrLHVYd1Xrd0Ia6lfJ/edit 7/9 09/07/2017 TRABALHO CURSOR - Documentos Google As wdisc tbdisciplina.nomedsic%type; CURSOR C2 IS SELECT nomedisc from tbdisciplina d inner join tbcurso c on d.fkcodcurso = c.pkcodcurso where c.pkcodcurso=cod; begin OPEN C2; loop dbms_output.put_line('***************************************************************'); FETCH C2 INTO wdisc; if (C2%notfound) then exit; end if; dbms_output.put_line('Estou "varrendo" a linha ' || C2%rowcount ); dbms_output.put_line('Nome Disciplina: ' || wdisc); end loop; CLOSE C2; end exe2; EX3 create or replace https://docs.google.com/document/d/1QTX7AgMx7PLuNtRjxeoELlpYrLHVYd1Xrd0Ia6lfJ/edit 8/9 09/07/2017 TRABALHO CURSOR - Documentos Google procedure exe3 ( cod in tbaluno.pkcodalux%type) As wfkcodturma tbmatriturma.fkcodturma%type; wg1 tbmatriturma.g1%type; wg2 tbmatriturma.g2%type; wmediap tbmatriturma.mediap%type; wsubst tbmatriturma.subst%type; wmediafinal tbmatriturma.mediafinal%type; wconceito tbmatriturma.conceito%type; CURSOR C3 IS SELECT fkcodturma, g1, g2, mediap, subst, mediafinal , conceito from tbmatricula m inner join tbmatriturma mt on m.pkcodmatricula = mt.fkcodmatricula where cod = m.fkcodalu; begin OPEN C3; loop dbms_output.put_line('***************************************************************'); FETCH C3 INTO wfkcodturma, wg1, wg2, wmediap, wsubst, wmediafinal , wconceito; if (C3%notfound) then exit; end if; dbms_output.put_line('Estou "varrendo" a linha ' || C3%rowcount ); dbms_output.put_line('Cod Turma: '|| wfkcodturma); dbms_output.put_line('G1: ' || wg1); dbms_output.put_line('G2: ' || wg2); dbms_output.put_line('Subst: ' || wsubst); dbms_output.put_line('Media Parcial: ' || wmediap); dbms_output.put_line('Media Final: ' || wmediafinal); dbms_output.put_line('Conceito: ' || wconceito); end loop; CLOSE C3; end exe3; https://docs.google.com/document/d/1QTX7AgMx7PLuNtRjxeoELlpYrLHVYd1Xrd0Ia6lfJ/edit 9/9 137 Jingle Bell Arches Dingle Bell Junction, NB T7V 3L6.?