BD Essencial - Aula 6

<img src="https://i.imgur.com/D01Fpky.png" alt="" title="Entre no nosso site e veja nossos cursos"> <h1> <img class="emoji" alt="" src="https://image.flaticon.com/icons/png/128/2906/2906274.png"> BD Essencial - Aula 6 </h1> O objetivo desse curso é capacitar o aluno a entrar no mercado de trabalho executando as atividades primordiais que são solicitadas no desenvolvimento, manutenção, ou atualização de bancos de dados dos mais diferentes tipos de sistemas. Focamos na execução, e não em criação de diagramas - atividade essa que é desenvolvida por profissionais mais experientes. Nas nossas prieiras aulas, utilizamos o Gerenciador de Bancos de Dados Relacionais MySQL nesse nosso curso de Banco de Dados Essencial, porque essa ferramenta é gratuita e de fácil utilização - entretanto, todo o nosso conteúdo pode ser aplicado em qualquer Gerenciador de Bancos de Dados Relacionais, apenas com algumas mínimas diferenças. Na segunda parte do curso, utilizamos o Gerenciador de Bancos de Dados Relacionais PostgreSQL. <blockquote style="border-left-color: red;"> Prof Fernando Gomes - fernandojnr@gmail.com</blockquote> <h2 id="-Tema-da-aula" data-id="-Tema-da-aula"><a class="anchor hidden-xs" href="#-Tema-da-aula" title="-Tema-da-aula"></a><img class="emoji" alt=":book:" src="https://cdn.jsdelivr.net/npm/@hackmd/emojify.js@2.1.0/dist/images/basic/book.png"> Tema da aula:</h2> Nessa sexta aula, vamos falar sobre <ul> <li>Números com casas decimais;</li> <li>Função com passagem de valor por parâmetro;</li> <li>DROP FUNCTION IF EXISTS;</li> <li>Conversão de número em string, usando to_char;</li> <li>plpgsql;</li> <li>Operador ||;</li> <li>Exceção;</li> <li>Trigger;</li> <li>before insert;</li> <li>Cópia de dados de tabela para TXT e CSV [com ou sem cabeçalhos] com \copy;</li> <li>create table like;</li> <li>Importação de dados de arquivo TXT para tabela;</li> <li>Comando alter table para criação de chave primária;</li> <li>Exportando dados de tabela para arquivo JSON com row_to_json;</li> </ul>   ```postgre= \c banco20; -- salario: numero de dez digitos -- sendo 2 desses para as casas decimais create table funcionario( idFuncionario serial primary key, nome varchar (50) not null, sexo varchar (1) check (sexo in('m','f')) not null, salario dec(10,2) not null); insert into funcionario (nome, sexo, salario) values ('david','m',8000), ('matheus','m',5000), ('marcelo','m',7600), ('sergio', 'm',5000), ('luciana','f',1200); -- insert into funcionario (nome, sexo, salario) values -- ('belem','m',12345678.91); select * from funcionario; -- funcao com passagem de valor por parametro create or replace function fnObterNome(id INT) returns varchar (50) as $$ select nome from funcionario where idFuncionario = id; $$ language sql; select fnObterNome(1); select fnObterNome(2); select fnObterNome(3); -- DROP FUNCTION IF EXISTS fnObterNome(id INT); select to_char(1000000, '"R$"999G999G999D99'); banco20=# select to_char(1000000, '"R$"999G999G999D99'); to_char ------------------- R$ 1.000.000,00 (1 row) -- funcao com passagem de valor por parametro -- sem ter um nome declarado para esse parametro -- retorna um numero com casas decimais -- dez digitos no total, dos quais, dois sao para as casas decimais create or replace function fnCalculoDescontoNum(dec(10,2)) returns dec(10,2) as $$ select $1 * 0.95; $$ language sql; -- resultado nao sai com formatacao perfeita select nome, sexo, fnCalculoDescontoNum(salario) as desconto from funcionario; -- retornando o valor formatado como uma string -- dessa forma, controlamos a formatacao do resultado create or replace function fnCalculoDesconto(Dec(10,2)) returns varchar (15) as $$ select to_char($1 * 0.95, '"R$"999G999G999D99'); $$ language sql; select nome, sexo, fnCalculoDesconto(salario) as desconto from funcionario; -- funcao em plpgsql -- recebe os dados do funcionario; -- calcula o sal. liquido; -- insere na tabela depois de feito o calculo; -- retorna uma mensagem se o cadastro foi feito com sucesso; create or replace function cadFunc(varchar(30), char, dec(10,2)) returns varchar(50) as $$ declare -- variaveis internas da function msg varchar(50); liquido dec(10,2); begin if $3 > 4000 then liquido = $3 * 0.8; else liquido = $3 * 0.9; end if; insert into funcionario(nome, sexo, salario) values ($1, $2,liquido); msg = $1 || ' Cadastrado com Sucesso !!!!'; -- Operador || permite a cooncatenacao de varias strings return msg; end; $$ language plpgsql; select cadFunc('Du dudu Edu','m',1000); select cadFunc('Lucina Medeiro','m',15000); select * from funcionario; -- funcao que eh chamada por um trigger: "retorna" um trigger; -- lanca uma excecao, caso a regra de negocio nao seja atendida -- so insere na tabela se o salario for maior q 900 create or replace function fnVerificaSalarioMin() returns trigger as $$ begin if new.salario < 900 then raise Exception 'Salario Inferior que o Salario Minimo !!!'; end if; end; $$ language PLPGSQL; create trigger triggerVerificaSalarioMinimo before insert on funcionario for each row execute procedure fnVerificaSalarioMin(); insert into funcionario (nome,sexo,salario) values ('ze estagiario','m','800'); banco20=# insert into funcionario (nome,sexo,salario) values banco20-# ('ze estagiario','m','800'); ERROR: Salario Inferior que o Salario Minimo !!! create sequence seq_veiculo start with 102 increment by 1; create table veiculo( idVeiculo int primary key, modelo varchar (50) not null, ano int not null, valor dec(10,2) not null ); insert into veiculo values (nextval('seq_veiculo'), 'Eco Sport',2012, 25000); insert into veiculo values (nextval('seq_veiculo'), 'palio',2015, 33000); insert into veiculo values (nextval('seq_veiculo'), 'hb20',2015, 45000); -- copiando o conteudo de uma tabela para um arquixo TXT \copy veiculo to 'C:\BD\veiculo.txt' 102 Eco Sport 2012 25000.00 103 palio 2015 33000.00 104 hb20 2015 45000.00 -- copiando o conteudo de uma tabela para um arquixo CSV \copy veiculo to 'C:\BD\veiculo.csv' delimiter ',' -- abrimos com o excel e visualizamos uma planilha normal \copy veiculo to 'C:\BD\veiculo2.csv' delimiter ',' header csv -- abrimos com o excel e visualizamos uma planilha normal -- dessa vez, com cabecalhos idveiculo modelo ano valor 102 Eco Sport 2012 25000 103 palio 2015 33000 104 hb20 2015 45000 --criando tabela igual a tabela veiculo create table importVeiculo(like veiculo); banco20=# \d importVeiculo Table "public.importveiculo" Column | Type | Modifiers -----------+-----------------------+----------- idveiculo | integer | not null modelo | character varying(50) | not null ano | integer | not null valor | numeric(10,2) | not null banco20=# \d veiculo Table "public.veiculo" Column | Type | Modifiers -----------+-----------------------+----------- idveiculo | integer | not null modelo | character varying(50) | not null ano | integer | not null valor | numeric(10,2) | not null Indexes: "veiculo_pkey" PRIMARY KEY, btree (idveiculo) --importando os dados do arquivo TXT \copy importVeiculo from 'C:\BD\veiculo.txt' banco20=# select * from importVeiculo; idveiculo | modelo | ano | valor -----------+-----------+------+---------- 102 | Eco Sport | 2012 | 25000.00 103 | palio | 2015 | 33000.00 104 | hb20 | 2015 | 45000.00 (3 rows) -- alterando tabela para idVeiculo se tornar chave primaria ALTER TABLE importVeiculo ADD PRIMARY KEY (idveiculo); banco20=# \d importveiculo Table "public.importveiculo" Column | Type | Modifiers -----------+-----------------------+----------- idveiculo | integer | not null modelo | character varying(50) | not null ano | integer | not null valor | numeric(10,2) | not null Indexes: "importveiculo_pkey" PRIMARY KEY, btree (idveiculo) select row_to_json(veiculo) from veiculo; banco20=# select row_to_json(veiculo) from veiculo; row_to_json -------------------------------------------------------------------- {"idveiculo":102,"modelo":"Eco Sport","ano":2012,"valor":25000.00} {"idveiculo":103,"modelo":"palio","ano":2015,"valor":33000.00} {"idveiculo":104,"modelo":"hb20","ano":2015,"valor":45000.00} (3 rows) \copy (select row_to_json(veiculo) from veiculo) to 'C:\BD\veiculo.json' ```  <blockquote style="border-left-color: red;"> Prof Fernando Gomes - fernandojnr@gmail.com</blockquote> <div class="alert alert-info"> <blockquote style="border-left-color: blue;"> E.B. Cursos https://www.cursoseb.com.br/ E.B. Cursos EAD https://edsonbelemtreinamento.com.br/ead/ </blockquote> </div>