• Author / Uploaded
• silvanapaulasantos

• Categories
• SQL
• Pl / Sql
• Bases de datos
• Datos
• Datos de computadora

Citation preview

Como Instalar Oracle 11g Express Edition 1º Aula A versão do Oracle 11g Express Edition é destinada para fins acadêmicos e não deve ser usada para criação de softwares com fins comerciais, por isso ela tem uma limitação de armazenamento de informações de apenas 4 GigaBytes, parece muito mas para um sistema comercial isso é muito pouco, pois com o passar do tempo as informações vão se acumulando e dados históricos precisam ser guardados, mas como o nosso intuito é exclusivamente acadêmico esta versão Oracle é mais do que suficiente para o nosso propósito. Modelagem de Dados – Curso Oracle PL/SQL 2º Aula A Modelagem de Dados é a criação de um modelo físico que explique a lógica por traz do sistema, com ele você é capaz de explicar as características de funcionamento e comportamento de um software. A modelagem de dados é a base de criação do Banco de dados e parte essencial para a qualidade do software. Este é um assunto muito extenso e em grandes empresas existem pessoas que fazem apenas isso o dia inteiro, nas menores esta acaba sendo uma atribuição do DBA e dos desenvolvedores de softwares, mais conhecidos como nós :), então por esse motivo vou dar um overview sobre modelagem de dados. Agora você falar algumas siglas que são muito importantes vocês saberem e que vocês vão ouvir no dia-a-dia o tempo inteiro, a primeira delas é o SGDB (Sistema Gerenciador de Bando de Dados), que é basicamente o programa que usamos para criarmos o nosso banco de dados, no nosso caso estamos utilizando o Oracle, mas existem outras ferramentas que também podem ser utilizadas pelas empresas. A outra sigla importante é o DER (Diagrama Entidade-Relacionamento) ele é o desenho que muitos de vocês já podem ter visto, onde se faz toda a modelagem de dados. Se vocês viam essas imagens, como na figura abaixo, e não entendiam nada, a partir de agora vocês vão entender.

Este quadradinho que vocês estão vendo representam as tabelas do banco de dados e as linhas que conectam elas são os relacionamentos. Fazer e compreender um modelo desse é o nosso objetivo final destas duas aulas sobre modelagem. Um pouco de conceitos importantes. Entidades, são representações de algo no mundo físico para um sistema, por exemplo no nosso caso temos as entidades produtos, comanda e cliente. Que são representados pelo símbolo abaixo.

Além das Entidades, também temos os relacionamentos entre as entidades, que nada mais é do que a ligação entre duas entidades, ou algo que faça com que essas entidades tenham algo em comum. Vou dar um exemplo de relacionamento entre duas entidades.

Agora eu te convido a assistir o vídeo abaixo, ele conterá uma história real de resolução de um problema através modelagem de dados, após o vídeo vou explicar para vocês imagem por imagem como a modelagem será aplicada e os tipos relacionamentos de entidades que existem. Video 2 E aí pessoal, gostaram do vídeo? Espero que sim porque deu um trabalhão, bom vocês viram a história do José e viram com a analista de sistema Maria conseguiu resolver o problema agora vocês conseguiram perceber as conexões entre os elementos no novo processo de compra dentro da confeitaria do José? Se vocês não conseguiram agora eu vou explicar para vocês. A primeira pergunta de José é como funcionarão as comandas e Maria respondeu da seguinte forma:

Nesse momento existe um relacionamento entre os produtos consumidos e a comanda do cliente, sendo que com uma comanda eu posso consumir vários produtos. Este é um relacionamento é um relacionamento de Muitos-para-muitos (n:n) , que na linguagem mais acadêmica é explicado da seguinte forma: Uma entidade em “A” está associada a qualquer número de entidades em “B” e vice-versa. Alguns autores preferem chamar esta cardinalidade de m:n, por considerar que podem representar valores diferentes. Ainda nesta cena temos um outro relacionamento no momento em que o cliente está com uma comanda, está comanda é ativada e até ser feito o pagamento esta comanda esta atrelada apenas a um cliente e um cliente pode ter apenas uma comanda. Neste momento temos a relação de Um-para-um (1:1), que na linguagem mais acadêmica é explicado da seguinte forma: Uma entidade em “A” está associada com no máximo uma entidade em “B”, e uma entidade em “B” está associada com no máximo uma entidade em “A”. Vamos um pouco mais além do processo, os produtos cadastrados no sistema possuem características diferentes que devem ser levadas em conta, por exemplo os bolos vendidos pelo Sr. José tem validade de 1 dia e precisam ser refrigerados para que a sua consumação possa ser feita sem problemas durante o dia, mas a

água vendida também na confeitaria possui uma validade maior e não precisa ser refrigerada para se manter no padrão de consumo. Então vejam existem tipos diferentes de produtos, mas vejam este é um relacionamento diferente dos outros, porque um tipo de produto pode estar atrelado a vários produtos, porém um produto pode ter apenas um tipo. Este relacionamento é o Um-para-muitos (1:n), que na linguagem mais acadêmica é explicado da seguinte forma: Uma entidade em “A” está associado a qualquer número de entidades em “B”, e uma entidade em “B”, todavia, pode estar associado a no máximo uma entidade em “A”. Após você saber os três tipos de relacionamento, segue o modelo final desenhado.

Imagino que você deve estar se perguntando como eu faço para saber quando criar uma tabela ou não. Antes do processo de Modelagem de Dados existe um outro processo que se chama levantamento de requisitos, bom neste processo você tem que entrar em contato com a pessoa que solicitou o sistema, ou a alteração do mesmo, e verificar tudo que o mesmo deseja. E você deve mapear o seguinte: Coisas Tangíveis: elementos que têm existência concreta, que ocupam lugar no espaço. Ex: Meio de Transporte (avião, carro, etc); Funções: percepção dos objetos através da função por eles exercida (papel, atribuição, classificação, capacitação, etc). Ex: Organização (órgãos funcionais – venda, suporte, despacho de mercadorias, etc), especialistas (médicos, engenheiros, etc), cliente (pessoa atendida), atendente (pessoa que atende), etc; Eventos ou Ocorrências: alguns objetos só conseguem ser individualizados ou percebidos enquanto uma certa ação se desenrola (identifica-se características que tornam determinado fato materializável).Ex: vôo comercial, acidente de trânsito, jogo de futebol, etc. Interações: resultantes das associações entre objetos em função de um processo executado – cada objeto participante da interação preserva suas características não sendo impactados pela materialização da interação. Ex: compra de um imóvel, adoção de uma criança, venda de um produto; Especificações: são elementos que definem características de outros objetos. Ex: modelos de carro (cor, dimensões, etc), espécies animais (mamíferos, carnívoros, etc.). Este mapeamento geralmente não é feito pelo desenvolvedor de softwares, em 90% dos casos ele é feito por um Analista Funcional que passa as especificações do sistema para que os desenvolvedores devam executá-las. Vou fazer uma análise

funcional para que vocês possam treinar o conceito que eu estou passando neste artigo. Modelagem de Dados – Parte 02 – Curso Oracle A normalização de dados é o tema da terceira aula do Curso Oracle PL/SQL Grátis. Este é um tema super importante para a criação de um banco de dados, nós não vamos aprofundar muito, pois o nosso objetivo final é aprender a linguagem PL/SQL, mas como eu falei para vocês antes de colocarmos a mão-namassa (Acreditem eu estou tão ansioso quanto vocês) temos que saber os fundamentos da linguagem e os conceitos básicos de como funciona um banco de dados. O Oracle é um SGBD relacional e isso quer dizer que ele aplica as regras definidas por Edgar Frank Codd , ele foi quem desenvolveu o modelo de banco de dados relacional. Ao todo são 12 regras, porém vou passar à vocês apenas as 3 primeiras que são as essenciais para o seu dia-a-dia. Para começarmos a normalização é necessário recapitularmos o modelo da primeira parte da aula de modelagem, abaixo vocês vão ver o modelo relacional de entidades que fizemos na semana passada no nosso Curso Oracle PL/SQL, vamos seguir a ideia de sempre darmos exemplos em cima da teoria, então vou passando as regras para vocês com exemplos.

Com base no modelo que fizemos na ultima aula, vamos transformar isso tudo emtabelas (que seria o local onde guardamos as informações no Banco de Dados), e para isso existem algumas regras a serem seguidas. Toda entidade vira uma tabela; Produtos TB_PRODUTO Comanda TB_COMANDA Estoque TB_ESTOQUE

Cliente TB_CLIENTE Caixa TB_CAIXA E Se você clicar no nome da entidade aparecerá o nome da tabela que vai ser criada.

Relacionamentos que possuem atributos viram tabelas (existe a possibilidade em relacionamentos 1:n dos atributos irem para uma das tabelas, ao invés de se criar uma nova. Entretanto, relacionamentos com atributos são mais comuns em relacionamentos n:n, gerando assim uma nova tabela); Agora vamos criar as tabelas desses relacionamentos. stoque Caixa Compra TB_PRODUTO_COMANDA Quantidade TB_PRODUTO_ESTOQUE Baixa TB_CAIXA_ESTOQUE Pagamento TB_CLIENTE_PAGAMENTO Toda tabela possui um ou mais campos que são os campos únicos, onde cada entidade se diferencia, por exemplo, um cliente possui um CPF único que pode ser o que diferencia todos os clientes, estes campo únicos são chamados de chaves primárias. Abaixo seguem as chaves primárias de todas as tabelas criadas. TB_PRODUTO ID_PRODUTO TB_COMANDA ID_COMANDA, DT_INICIO, DT_FIM TB_ESTOQUE ID_ESTOQUE TB_CLIENTE ID_CLIENTE (Que nesse caso vai ser o CPF) TB_CAIXA ID_PAGAMENTO

TB_PRODUTO_COMANDA ID_PRODUTO, ID_COMANDA, DT_INICIO, DT_FIM TB_PRODUTO_ESTOQUE ID_PRODUTO, ID_ESTOQUE TB_CAIXA_ESTOQUE ID_PAGAMENTO, ID_ESTOQUE TB_CLIENTE_PAGAMENTO ID_CLIENTE, ID_PAGAMENTO Relacionamentos são representados por chaves estrangeiras (ou Foreign Key – atributo correspondente à chave primária de outra relação, base para a integridade referencial); Temos as tabelas que possuem chaves estrangeiras que compõem os relacionamentos das tabelas do nosso banco de dados. TB_PRODUTO_COMANDA ID_PRODUTO, ID_COMANDA TB_PRODUTO_ESTOQUE ID_PRODUTO, ID_ESTOQUE TB_CAIXA_ESTOQUE ID_PAGAMENTO, ID_ESTOQUE TB_CLIENTE_PAGAMENTO ID_CLIENTE, ID_PAGAMENTO Perceberam algo bem interessante, as chaves estrangeiras são os mesmos campos que formam as chaves primárias compostas dos relacionamentos, então tentem sempre achar esta conexão entre os relacionamentos, chaves estrangeiras e chaves primárias. Relacionamentos 1:1 podem ser mapeados numa única tabela (quando possuem a mesma chave primária), em duas tabelas (quando as chaves primárias são diferentes e um dos lados do relacionamento é obrigatório) ou em três tabelas (quando o relacionamento é opcional em ambos os lados); No nosso caso existe apenas um relacionamento 1:1 na nossa modelagem que é o relacionamento da entidade Comanda x Cliente, porque um cliente pode apenas ter uma comanda para efetuar compras e uma comanda pode pertencer apenas a um cliente enquanto ela estiver ativada. Por esse motivo temos esse relacionamento 1:1. Mas elas possuem chaves primárias diferentes então por esse motivo tempos duas tabelas, porém com a obrigatoriedade do ID_CLIENTE (Chave Primária da TB_CLIENTE, na TB_COMANDA). Relacionamentos 1:n são mapeados de forma que a chave primária do lado “1” seja representada do lado “n” como chave estrangeira; Relacionamentos n:n devem ser transformados em dois relacionamentos 1:n, resultando numa nova tabela;

Estes dois últimos passos ficarão mais legais nos desenhos que vou mostrar para vocês logo abaixo. Após passar essas regras começamos a desenhar o nosso banco de dados, bom como nesse caso ficaria muito trabalhoso usar o Paint para desenhar tudo na mão, então eu vou utilizar o MySql Workbench que é uma ferramenta utilizada para modelar oMySQL, que é um banco de dados muito utilizado para aplicações web e que foi comprado pela Oracle e melhor de tudo, esta ferramenta é totalmente grátis. Vou fazer um post no futuro para ensinar vocês a usarem esta ferramenta fantástica, mas agora nosso foco é aprende PL/SQL. Um pouco mais de teoria com as 3 formas normais de Codd, que vou apresentar a vocês. 1ª Forma Normal (1FN): toda relação deve ter uma chave primária e deve-se garantir que todo atributo seja atômico. Atributos compostos devem ser separados. Por exemplo, um atributo Endereço deve ser subdividido em seus componentes: Logradouro, Número, Complemento, Bairro, Cidade, Estado e CEP. Além disso, atributos multivalorados devem ser discriminados separadamente ou separados em uma outra relação. Por exemplo, um atributo multivalorado Telefones poderia ser separado em Telefone Residencial, Telefone Comercial e Telefone Celular ou, ainda, ser convertido em outra relação que pudesse representar um número indeterminado de telefones. Já passei para vocês como transformar um Modelo de Relacionamentos de Entidades em um modelo de relacionamento de tabelas. E se alguém ficar com alguma dúvida por favor comentem para que eu posso ajudá-los a resolve-las e claro fazer com que o meu curso evolua sanando as principais dúvidas de todos. Para exemplificar vou pegar um dos casos de relacionamento, quando criamos a tabela TB_PRODUTO_COMANDA, que contém o Identificador da Comanda e o Identificador do Produto estamos transformando um relacionamento de n:n entre o produto e a comanda em um relacionamento 1:n, pois podem existir vários produtos, mas na tabela TB_PRODUTO_COMANDA vai existir apenas um ID_PRODUTO e campo quantidade para fazer os cálculos na hora da compra e o mesmo serve para a Comanda, onde podem existir várias comandas, mas apenas um identificador de comanda poderá estar atrelado a tabela TB_PRODUTO_COMANDA. Segue a primeira parte do nosso modelo aplicando a primeira regra de normalização de Codd.

2ª Forma Normal (2FN): toda relação deve estar na 1FN e devem-se eliminar dependências funcionais parciais, ou seja, todo atributo não chave deve ser totalmente dependente da chave primária. Como exemplo, uma relação que contenha os atributos Código da Obra, Código do Fornecedor, Nome do Fornecedor e Preço de Venda, Considerando que a chave primária é composta pelos atributos Código da Obra e Código do Fornecedor, não está na Segunda Forma Normal, uma vez que o Nome do Fornecedor depende apenas do Código do Fornecedor, e não do Código da Obra. Uma nova relação (Fornecedor) deve ser criada contendo os campos Código do Fornecedor (como chave) e Nome do Fornecedor. Na relação original, ficariam os atributos Código da Obra e o Código do Fornecedor, ambos formando a chave primária composta, e o atributo Preço de Venda. Além disso, o atributo Código do Fornecedor também seria uma chave estrangeira para a nova relação criada. Esta forma normal ajuda a diminuir redundâncias de informações criadas indevidamente. Essa regra é bem interessante, porque eliminamos informações duplicados e conseguimos conservar a integridade das informações, por exemplo, na tabela TB_PRODUTO colocamos o nome do tipo do produto, bom vamos dizer que o produto seja uma bebida, e que um funcionário cadastrou o banco de dados Bebida, mas veio outro funcionário e ao cadastrar o produto cadastrou Bebidas, isso faria com que tivéssemos dois tipos de produto cadastrados no banco, que na verdade seriam os mesmos, por esse motivo é necessário criar essas tabelas. Aplicando a 2ª Regra temos o seguinte modelo:

3ª Forma Normal (3FN): toda relação deve estar na 2FN e devem-se eliminar dependências funcionais transitivas, ou seja, todo atributo não chave deve ser mutuamente independente. Como exemplo, uma relação que contenha os atributos Matrícula do Funcionário (atributo chave), Nome do Funcionário, Código do Departamento e Nome do Departamento não está na Terceira Forma Normal. O Nome do Departamento é dependente do Código do Departamento, e não da Matrícula do Funcionário. Uma mudança no nome do departamento, por exemplo, levaria a modificações em todos os funcionários daquele departamento. Para eliminar este problema, cria-se uma nova relação (Departamento) contendo Código do Departamento e Nome do Departamento. Na relação original, retira-se o Nome de Departamento, mantendo-se o Código do Departamento, agora como chave estrangeira. Esta forma normal também ajuda a diminuir redundâncias e aumentar a independência das relações.

Após aplicar as 3 formais normais temos o nosso relacionamento do Banco de Dados completo, mas antes de terminar gostaria de deixar para vocês minha fonte para escrever este artigo foi este ótimo trabalho acadêmico que eu encontrei no google, então caso alguém queira se aprofundar mais em modelagem vale muito a pena lê-lo. Outro recado é que a partir da próxima aula vamos precisar ter o Oracle instalado para prosseguirmos com o curso, então quem não instalou o Oracle ainda, instale porque você não vai conseguir acompanhar o restante do pessoal se ele não for instalado. Se tiver alguma dúvida sobre a instalação de uma olhada naprimeira aula do Curso Oracle PL/SQL e aprenda a instala-lo. E para quem estiver na minha lista vocês vão receber nos próximos dias um email com uma dica de ferramenta TOP de mercado que vamos usar para facilitar a nossa vida com a conexão de dados. SQL Parte 01 – Curso Oracle PL/SQL A SQL é a linguagem de programação não procedural planejada para funcionar com conjuntos de dados em sistemas de gerenciamento de bases de dados relacionais. Ela é parte fundamental no Curso Oracle PL/SQL, sem o SQL o PL/SQL não existiria, na verdade o PL/SQL eu gosto de tratar como uma evolução do SQL, já que esta linguagem foi criada para resolver problemas lógicos que somente o SQL puro não resolveria. Ela é importantíssima, pois um bom SQL deixa o código mais robusto e performático, evitando problemas. A SQL permite que você defina, modifique e remova objetos da base de dados, faça transações de dados, controle o fluxo das transações e solicite os dados. Os comandos da linguagem SQL estão frequentemente agrupados por função em três grupos que são chamados da linguagens: DDL (CREATE, ALTER, DROP e GRANT com o DDL você define, modifica e remove objetos “Objetos entenda como Triggers, Procedures, Packages, Functions e Types”), DML (SELECT, INSERT, DELETE e UPDATE, ela é a linguagem responsável por solicitar dados e manipular dados) e o DCL (SAVEPOINT, COMMIT e ROLLBACK, serve para você controlar as transações). Bom não entendeu algumas dessas siglas ou não tem noção do que elas são, fique tranquilo quando você finalizar este Curso Oracle PL/SQL você vai dominar tudo isso. Agora vou passar para vocês os tipos de dados mais comuns que existem no Oracle, vou passar os mais comuns porque os Tipos de dados não é o foco deste curso, no futuro estou planejando uma surpresa para vocês, totalmente focado no SQL, mas como é uma surpresa, aguardem, Existem ao todo 23 tipos de dados, mas para esta aula vou passar apenas 5 tipos de dados, porque existem tipos que serão utilizados apenas em situações especiais e que no momento não interessam, no futuro vou escrever uma matéria apenas dos tipos de dados e assim eles ficam disponíveis para futuras consultas. CHAR: Ela tem um tamanho fixo de bites ou caracteres, este tipo de dado é muito utilizado para armazenar informações do tipo string que nunca mudam o tamanho, por exemplo siglas de sexo que apenas um carácter, CEP (Você deve estar se perguntando mas CEP é número, explico melhor isso no tipo Number), Telefone,

CPF/CNPJ, basicamente informações não variáveis, na sua declaração você deve usar a seguinte forma CHAR(“Tamanho do Campo”). Ex: CD_SEXO CHAR(1); VARCHAR2: Este tipo de dado é utilizado para armazenar strings de tamanhos variados, com um tamanho de até 4000 caractéres. Este tipo de dados é utilizado para strings variáveis, por exemplo Nome da Pessoa, Rua, Cidade, basicamente campos que não tem como você prever o tamanho deles no início. Para a declarar o VARCHAR2, utilizamos a seguinte sintaxe CHAR, que seria por exemploNM_CLIENTE VARCHAR2(70): CLOB: Este tipo é utilizado para guardar caracteres de um tamanho até 4GB. Ele é muito utilizado para guardar arquivos transacionais, como por exemplo XMLs(Antigamente não existiam as XMLs Types no Oracle e como a maioria dos sistemas de hoje foram feitos antigamente, vocês vão encontrar muitos XMLs armazenados desta forma). Esses tipos não são definidos sem nenhum parâmetro para o tamanho. Ex: TRANSACAO_XML CLOB; NUMBER: É uma coluna numérica de tipo de dados, com um tamanho de até 38 posições. Ele é utilizado para gravar todos os tipos valores numéricos no banco, esse valores podem ser definidos da seguinte forma, NUMBER(8,2) , onde o 8 é o tamanho total do campo e 2 a precisão, ou seja nesse campo você só poderia chegar a casa dos milhões, uma vez que o dois também faz parte do 8, então o número máximo que poderia ser colocado neste campo seria 999999,99. Além disso o NUMBER nunca começa com zero, por esse motivo precisamos usar oCHAR para salvarmos informações como o CEP, Telefone, etc. DATE: o Date possui 7 bytes e armazena de janeiro de 4712 A.C. até 31 de dezembro de 9999, usando a resentação do calendário Grego, que é o que utilizamos. A máscara de formato padrão é DD-MON-RR (DD -> Dia, MON -> ‘Jan’, ‘Feb’, RR -> ’13′ “Os dois ultimos dígitos do ano”). Para alterar o padrão é necessário alterar o parâmetro NLS_DATE_FORMAT, aí se o administrador do Banco de Dados, ou DBA, quiser ele pode colocar no padrão brasileiro que é “DD/MM/RRRR“. Ele é utilizado para gravar campos com datas. Todos os tipos que forma passados acima, possuem outros subtipos, porém se eu fosse passar todos eles aqui levaria a matéria inteira e sinceramente vocês vão usálos muito pouco, como eu já falei acima, duas aulas apenas do assunto SQL é muito pouco, por esse motivo vou preparar algo especial para vocês logo depois de terminar o Curso Oracle PL/SQL. Já passei a vocês os tipos de códigos, agora vamos criar as tabelas que desenhamos na ultima aula se utilizando dessas informações, para quem não se lembra segue a imagem do relacionamento feito.

Então vamos usar a tabela TB_PRODUTO como exemplo, na tabela nela temos os campos ID_PRODUTO, NM_PRODUTO, ID_TIPO_PRODUTO, VL_COMPRA_PRODUTO, VL_VENDA_PRODUTO, DT_VALIDADE_PRODUTO e NR_LOTE_PRODUTO. Vamos criar o nosso primeiro código DDL agora para criarmos uma tabela. E ele vai logo abaixo! CREATE TABLE TB_PRODUTO ( ID_PRODUTO NUMBER(10), NM_PRODUTO VARCHAR2(50), ID_TIPO_PRODUTO NUMBER(10), VL_COMPRA_PRODUTO NUMBER(8,2), VL_VENDA_PRODUTO NUMBER(8,2), DT_VALIDADE_PRODUTO DATE, NR_LOTE_PRODUTO VARCHAR2(30)); Caramba, acabamos de criar o nosso primeiro código DDL no curso, isso é um marco :). Mas você deve ter muitas dúvidas agora, e a maior delas deve ser, criei o meu primeiroDDL, mas onde eu vou coloca-lo? Lembram da primeira aula, para quem não lembrar é só clicar aqui , lá no final da instalação definimos um usuário e uma senha, para acessarmos o banco de dados, não é o system da instalação, é o ultimo usuário que você criou. Agora você vai entrar neste endereço http://127.0.0.1:8080/apex e vai acessar com aquele ultimo usuário criado. Vai abrir a janela abaixo e você vai clicar em SQL Commands, conforme destacado na imagem.

ocê vai entrar nesta tela, aqui dentro nós vamos colocar o código de criação de tabela e clicar no botão “Run” e como resultado teremos a mensagem destacada na imagem.

Agora sim podemos dizer que temos o nosso primeiro objeto criado. Porém você temos um identificador único na tabela TB_PRODUTO, o ID_PRODUTO, mas como é feito essa diferenciação de campo, isso é fácil nós vamos criar uma Primary Key que é um identificador única para esta tabela. ALTER TABLE TB_PRODUTO ADD CONSTRAINT PK_PRODUTO PRIMARY KEY (ID_PRODUTO); Legal né, mais um código feito e uma chave primária criada, um pouco de explicação, o código “ALTER TABLE” é o código DDL que indica que nós vamos alterar a tabela, depois desse código vem o nome da tabela, TB_PRODUTO, que vai

ser alterada, após isso nós vamos falar para o banco o que nós vamos fazer nesta tabela e por isso colocamos o código “ADD CONTRAINT” e após isso vamos colocar o nome dessa constraint, que vai ser PK_PRODUTO, após colocarmos o nome vamos por o tipo da constraint que vamos colocar nesta tabela e o campo que ela vai representar “PRIMARY KEY (ID_PRODUTO)“. Agora vou usar outro código DDL para vermos como ficou a nossa tabelaTB_PRODUTO, que é o DESC, ele tem a função de descrever tudo que existe na tabela. E eu vou destacar a coluna ID_PRODUTO nesta imagem na coluna Primary Key, vocês viram que ela está como 1 , isso indica que esta coluna é única e que sempre teremos um código diferenciado para esta coluna.

Vocês viram que além da coluna Primary Key eu também destaquei a coluna Nullable, que significa que aquele campo pode ser inserido como nulo, mas existem campos nesta tabela que não podem ser nulos, mas eles podem ser repetidos, como por exemplo o campo NM_PRODUTO, e como vamos fazer para alterar esta informação, é simples, alterar esta coluna e colocarmos ela como NOT NULL. ALTER TABLE TB_PRODUTO MODIFY NM_PRODUTO NOT NULL; Temos um novo ALTER TABLE, onde como no outro caso vem o nome da tabela que vamos alterar, no caso TB_PRODUTO, após vem o código que vamos fazer nessa tabela, que vai ser o MODIFY, e o nome da coluna que nós vamos modificar NM_PRODUTO e o que vamos fazer com esta coluna, NOT NULL. Bastante coisa né, agora só falta mais um assunto na aula de hoje para fecharmos, que é as ligações entre as tabelas, como vocês viram o Oracle é um SGBD que é relacional, ou seja existe uma relação entre as informações, e todas as tabelas estão relacionadas, sendo assim as tabelas dentro do Oracle se relacionam e para se relacionarem existe uma ligação entre elas que é feita pela Foreing Key ou Chave Estrangeira, agora vamos aprender a fazer uma FK.

Vocês viram TB_PRODUTO tem ligação com tabela TB_ITEM_PRODUTO, onde temos um relacionamento de (1:N), e para ligar as tabelas primeiro temos que criar a tabela TB_ITEM_PRODUTO, então segue o script dela. CREATE TABLE TB_TIPO_PRODUTO (ID_TIPO_PRODUTO NUMBER(10) NOT NULL, NM_TIPO_PRODUTO VARCHAR2(10) NOT NULL); ALTER TABLE TB_TIPO_PRODUTO ADD CONSTRAINT PK_TIPO_PRODUTOPRIMARY KEY(ID_TIPO_PRODUTO); Neste momento vamos ligar a tabela TB_TIPO_PRODUTO com a tabela TB_PRODUTO, com o código ALTER TABLE TB_PRODUTO ADD CONSTRAINT FK01_PRODUTO FOREIGN KEY (ID_TIPO_PRODUTO) REFERENCES TB_TIPO_PRODUTO(ID_TIPO_PRODUTO); Mais um ALTER TABLE, e após ele vem o nome da tabela, TB_PRODUTO, onde o campo está sendo referenciado ou o que pode ter muitos dados da outra tabela que pode ter apenas um, após vem o ADD CONSTRAINT e o nome da constraint,FK01_produto, aí vem o tipo da constraint FOREIGN KEY e o campo desta constraint na tabela ID_TIPO_PRODUTO. Agora vem a referencia, de onde este campo é único, por isso colocamos o código REFERENCES e o nome da tabela referenciada,TB_TIPO_PRODUTO e em seguida deve-se colocar o campo da tabela referenciada que vai ser alterado ID_TIPO_PRODUTO. Agora já podemos finalizar a primeira aula de SQL, hoje vocês aprenderam os principais tipos de campos do Oracle, como criar uma tabela, como criar uma chave primária, como alterar uma coluna de uma tabela, como criar uma chave estrangeira e como ver os dados da tabela. Bastante coisa né? Agora vocês viram que falta um monte de tabelas do nosso projeto para se relacionarem, e para fazermos toda essa relação convido vocês à terminarem todo o relacionamento do nosso projeto da segunda aula e a postarem o script de tudo pronto no nosso grupo de alunos no facebook. O melhor projeto entregue até o dia 25/10/2013, vai ganhar um prêmio especial, eu vou bater um papo via skype com essa pessoa e vamos conversar um pouco sobre profissão, tecnologia e qualquer dúvida que você quiser perguntar para mim, ao vivo. SQL Parte 02 – Curso Oracle PL/SQL Chegamos na segunda aula de SQL do Curso Oracle PL/SQL, nesta aula você vai aprender os comandos INSERT, UPDATE, DELETE, TRUNCATE e o DROP. Estes comandos são a base do SQL e existem várias variações deles, não vou conseguir passar todas as variações nem todas as funções possíveis, devido ao nosso tempo e também ao que o Curso Oracle PL/SQL propõe, pois este é um curso básico. Os campos INSERT, UPDATE e DELETE fazem parte do grupo de comandos DML, como vimos na última aula. Já os comando DROP e TRUNCATE são comandos DDL, se você não se lembra dos comandos de uma olhada na última aula clicando aqui! Agora vou passar a vocês uma explicação breve sobre cada comando:

INSERT: Este comando serve para cadastrar dados dentro de uma tabela, ele trabalha a nível de linhas, ou seja quando ele é executado insere uma ou mais linhas dentro da tabela. Sua sintaxe principal é “INSERT INTO [NOME_TABELA] ( [NOME_CAMPO1, NOME_CAMPO2, NOME_CAMPO_3 ...] ) VALUES([VALOR_CAMPO1, VALOR_CAMPO2, VALOR_CAMPO3 ...]); “ Além dessa forma também podemos fazer um INSERT sem colocar os campos da tabela após o nome dela, mas desse jeito os valores tem que ficar na ordem em que os campos foram declarados na tabela, se vocês colocarem na ordem errada os campos serão inseridos no lugar errado, nesta outra versão o comando ficaria da seguinte forma: “INSERT INTO [NOME_TABELA] VALUES ([VALOR_CAMPO1, VALOR_CAMPO2, VALOR_CAMPO3 ...]);” Para mostrar melhor como seria vou usar o exemplo do INSERT vou usar as tabelas TB_PRODUTO e TB_TIPO_PRODUTO, que foi criada na última aula, utilizando os dois exemplos :

Fiz um INSERT com os valores, mas vocês viram que o comando não funcionou, e vocês sabem o porque não funcionou? É porque estamos trabalho com o Oracle que é umSGDB Relacional e quando tentamos inserir um registro em uma tabela que possui referencia de outra tabela e este registro não existe nesta outra tabela, temos este erro. Basicamente estamos tentando inserir um Produto que não possuí um tipo cadastrado e para resolver isso vamos cadastrar um Tipo.

Vocês viram que para inserir a linha da TB_TIPO_PRODUTO que não especifiquei as colunas da tabela que seriam inseridas e o comando funcionou, agora vamos inserir o registro na TB_PRODUTO, especificando as colunas.

Vocês perceberam algumas coisas na hora de eu colocar os valores nesse últimoINSERT? Agora vou passar para vocês algumas regras que servem para todos os comandos DML desta aula: Todo os valores cujo o tipo é VARCHAR2 ou CHAR são colocados entre parênteses, se eles não forem colocados desta forma o Oracle não entende que eles são deste tipo e vai dar erro na hora de executar o comando.

×Valores para campos numéricos com precisão, como é o caso dos preços, o número inteiro vem separado da sua casa decimal por um ponto “52.75″ ×O valor SYSDATE, significa a data que está no servidor atual, com hora, minuto e segundo o formato desta data é o padrão do servidor, quando colocamos +5 significa a data do servidor mais 5 dias, mais para frente vamos aprender como somar minutos, segundos, horas ou qualquer outro parâmetro de tempo nos campos to tipo data. Além dosysdate, também poderíamos usar a função TO_DATE, que será passada no próximo post sobre funções SQL. Bastante coisa no INSERT e ainda nem começamos direito, agora chegou a hora doUPDATE, vamos a uma explicação breve deste comando. UPDATE: Esta instrução permite que você atualize um ou mais valores de coluna em uma linha ou um conjunto de linhas em uma tabela. Para distinguir as linhas que serão atualizadas é utilizado a clausula WHERE, que serve para limitar a quantidade de registros a serem atualizados de acordo com as condições que são solicitadas. Se nenhuma clausula WHERE for colocado no comando todas as linhas da tabela serão atualizadas. A clausula WHERE é também utilizada nos comandos DELETEe SELECT. O esqueleto do comando seria UPDATE [NOME_TABELA] SET[NOME_COLUNA1] = [VALOR_COLUNA1], [NOME_COLUNA2] = [VALOR_COLUNA2] … WHERE [NOME_COLUNA_ALTERADA] = [VALOR_COLUNA_ALTERADA] Um exemplo do comando UPDATE. Vamos dizer que na o preço do Bolo de Chocolate aumentou de R$75.50 para R$79.00, então como vamos fazer isso? simples com umUPDATE. Segue o comando abaixo.

Supondo que nós não temos o ID_PRODUTO, apenas o nome e o tipo dele na hora de fazer a atualização e nesta tabela tenhamos além do Bolo de Chocolate inteiro, também tem o registro do Bolo de Chocolate em pedaços. Esse bolo em pedaço custa R$ 5,00 e queremos atualizar este valor para R$5,50 , mas sem atualizar os dados do bolo de chocolate inteiro, afinal não faria sentido o bolo de chocolate inteiro custar R$5,50 . Então temos o seguinte UPDATE.

Neste passo vimos que se inserirmos uma cláusula WHERE com um AND, conseguimos limitar mais a atualização conforme a nossa necessidade. Nesses casos se tivéssemos colocado apenas WHERE NM_PRODUTO = ‘BOLO DE CHOCOLATE’ iriamos atualizar duas linhas, uma com o pedaço e a outra com o bolo inteiro, então por isso é muito importante o WHERE, pois com ele conseguimos fazer as alterações conforme as nossas necessidades.

E chegou a hora do último comando DML da aula SQL Parte 02, que o DELETE, abaixo uma breve descrição sobre este comando. DELETE: Assim como o INSERT Este comando trabalha a nível de linha. Você deleta uma ou mais linhas com a instrução DELETE. Assim como com a instruçãoUPDATE, você geralmente terá uma cláusula WHERE, do contrário você deleta todas as linhas da tabela. A estrutura do DELETE é a seguinte: DELETE FROM [NOME_TABELA] WHERE[CLAUSULA_ALTERAR] Agora vamos ao exemplo prático, vamos deletar o registro referente ao pedaço do Bolo de Chocolate da tabela TB_PRODUTO.

Com esta explicação fechamos os quatro comandos DML de hoje, INSERT, UPDATE e o DELETE, além desses temos o SELECT, mas eu acho que este comando é muito importante e muito extenso, por isso decidi fugir do planejamento inicial do curso e fazer mais 2 aulas sobre SQL, a Parte 03 do SQL terá apenas as funções de SQL mais utilizadas e como elas ponde ser usadas, na parte 04 teremos uma aula somente sobre SQL. O TRUNCATE é um comando DDL que tem uma função muito parecida com o DELETE, mas com algumas diferenças, ao executar um TRUNCATE você vai remover todos os registros da tabela, não existindo a possibilidade de recuperá-los com um ROLLBACK(Que vocês aprenderam mais para frente no curso). Além disso não não conseguimos usar a clausula WHERE, porque ele deleta todos os registros. Sua Sintaxe é a seguinte TRUNCATE TABLE [NM_TABELA]. Ele é usado quando você quer excluir todos os registros da tabela e como ele não guarda as

informações em memória em caso de ROLLBACK ele se torna mais rápido que oDELETE. Chegamos ao ultimo comando de hoje, este é um comando também é do tipo DDL e é responsável por excluir objetos do banco de dados, este comando é o DROP. Quando ele é rodado ele exclui todos os registros (No caso das tabelas) e o objeto são excluídos, e não temos mais como recuperarmos o objetos/registros (Esta possibilidade só existe acionando o DBA e solicitando que o mesmo faça um RESTORE da base, mas este é um procedimento demorado e que tem um impacto muito grande dependendo do ambiente que você está trabalhando, por esse motivo ele deve ser evitado a todo curso), assim como o TRUNCATE. Abaixo segue um exemplo de um DROP TABLE.

Como vocês podem ver, mais um exemplo de como um SGDB Relacional trabalha. Tentamos dropar a tabela TB_TIPO_PRODUTO, porém ela possui registros que estão ligados na TB_PRODUTO e por esse motivo tivemos este erro. Para que este comando funcione precisamos primeiro dropar a tabela TB_PRODUTO. Que é o que eu fiz na imagem abaixo.

Além de tabelas o DROP também é utilizado para deletar qualquer tipo de objeto da base de dados, como por exemplo VIEWs, PACKAGEs, PROCEDUREs e etc, a sua sintaxe é a seguinte DROP [TIPO_OBJETO][NOME_OBJETO]; Fechamos assim a segunda parte da aula de SQL, espero que vocês tenham gostado. Caso vocês tenham alguma dúvida entrem na nossa comunidade do facebook e postem lá. Na comunidade estamos interagindo bastante e o pessoal esta dando bastante ideias de como eu posso melhorar o curso, caso você queira acrescentar algo ou dar a sua opinião sobre o curso, entre lá a sua interação é muito importante para mim. Funções SQL Nativas do Oracle – Curso Oracle PL/SQL Vamos a mais uma aula, agora vamos falar sobre Funções de uma linha do SQL que são incorporadas no Oracle. Esse tipo de função são utilizadas dentro do PL/SQL e do SQL é claro, geralmente em instruções DML. Quando entrarmos na parte de desenvolvimento PL/SQL vamos aprender como fazer essas funções, porém para o momento vamos apenas focar nas principais funções que já são nativas do Oracle. Existem quatro tipos de funções SQL nativas do Oracle que são Funções Numéricas, Funções de Carácter, Funções de Data e Funções de Conversão. Segue abaixo uma descrição de cada tipo das funções Oracle. ×Funções SQL Numéricas:Essas funções basicamente recebem um número e retornam um número, por exemplo para fazer uma média, ou uma soma existem funções que serão descritas logo abaixo.

×Funções SQL de Carácter: Este tipo de função aceitam somente carácteres como parâmetros de entrada, mas elas retornam tanto carácteres como números, um exemplo é se você d eseja contar a quantidade de carácteres de uma determinada string ou a posição de um carácter dentro de uma string. ×Funções SQL de Data: Essa variação de função recebe parâmetros do tipo DATE e retornam parâmetros dos tipos DATE e NUMBER, este tipo retorna por exemplo o tipo de data atual do sistema ou a quantidade de meses entre duas datas ×Funções SQL de Conversão: Este é o quarto e ultimo tipo de função SQL que temos nativas do Oracle, neste tipo conseguimos converter um valor de entrada em outro valor na sua saída após executar a função, temos um número e queremos converte-lo em uma string, para isso usamos uma função de conversão. Estes são os quatro tipos de funções SQL nativas do Oracle que temos, mas antes vou explicar uma tabela virtual nativa do Oracle, que é a DUAL, basicamente ela é uma tabela que usamos para testes, sejam eles para cálculos, retorno de funções entre outros, que provavelmente faremos ao longo do curso. Abaixo seguem alguns exemplos da utilização da tabela DUAL, com alguns SELECTs.

Numéricas

1º Funções

ABS(X): Retorna o valor absoluto de um número, ou seja se você entrar com um valor negativo ele vai retornar um valor positivo e se o valor de entrada for positivo ele vai retornar um valor positivo. Por exemplo se colocar select ABS(30) from dual; ou select ABS(30 from dual; o resultado será o mesmo “30″

CEIL(x): Vai retornar o número arredondado para cima, por exemplo: select ceil (3.45) from dual o seu retorno vai ser 4. Preste bastante atenção, pois se o valor for negativo o maior valor seria o menor, por causa disso neste exemplo select ceil(-3.45) from dual; retornará o valor -3. FLOOR(x): Essa função é o contrário da última, ela vai retornar sempre o número arredondado para baixo, então usando os mesmos exemplos da última funçãoselect floor(3.45) from dual; o retorno vai ser 3 e quando executarmos select floor (-3.45) from dual; o retorno vai ser 4 TRUNC(x.y): O Trunc retira os valores que vem após a virgula até a quantidade de casas decimais definida em y, se y não for definido então ele vai retirar todos os números após a virgula, isto sem fazer nenhum arredondamento. Exemplo: select TRUNC(3.45, 1) from dual; irá retornar 3.4, agora se fizermos assim select TRUNC(3.45) from dual; irá retornar apenas 3 ROUND(x, y): A função Round faz algo semelhante ao TRUNC, porém com uma pequena variação, ele ao invés de apenas retirar os valores como o TRUNC, ele arredonda os valores seguindo as regras de maior ou igual a 5 ele arredonda para cima e menor que 5 ele arredonda para baixo. Exemplos: select round(3.45, 1) from dual; teremos como retorno 3.4 , já se fizermos select round(3.45) from dual teremos como retorno 3. 2º Funções de Carácter LOWER(valor_string): Esta função colocará todos os carácteres de uma string em caixa baixa (letras minúsculas). Exemplo: select LOWER(‘Fazendo Teste’) from dual; teremos retorno de “fazendo teste”. UPPER(valor_string): Ela faz exatamente o oposto da LOWER, ou seja todos os carácteres da string ficarão em caixa alta. Exemplo: select UPPER(‘Fazendo Teste’) from dual; eremos retorno de “FAZENDO TESTE”. INITCAP(valor_string): O INITCAP faz algo semelhante ao UPPER e ao LOWER, porém com uma diferença, ele vai colocar o primeiro carácter de qualquer palavra em caixa alta e o restante dos caracteres da palavra em caixa baixa. Exemplo:select INITCAP (‘fazeNdo Teste’) from dual; terá o seguinte retorno “Fazendo Teste”. LTRIM(valor_string, valor_string_2): Esta função vai retirar da primeira string o valor definido em valor_string_2, isso da esquerda para a direita. Exemplo select LTRIM(‘fazendo Teste fazendo’, ‘fazendo’) from dual; ao executar este comando teremos o seguinte retorno “Teste fazendo” RTRIM(valor_string, valor_string_2): O RTRIM faz exatamente o mesmo processo do LTRIM, porém faz da direita para esquerda, então se executarmos a query select LTRIM(‘fazendo Teste fazendo’, ‘fazendo’) from dual; teremos o seguinte resultado “fazendo Teste”. TRIM(valor_string_retirado FROM valor_string): Esta função irá retirar o primeiro carácter definido no valor_string_retirado do valor definido em valor_string. Exemplo: select TRIM(‘a’ from ‘a fazendo teste’) from dual; terá o seguinte retorno “fazendo teste”

SUBSTR(valor_string, x, y): Irá retornar a quantidade y de caracteres definidos a partir da posição x. Exemplo: select SUBSTR(‘fazendo teste’, 9, 5) from dual; ele irá retornar 5 caracteres a partir da posição nove, sendo assim teremos o retorno de “teste” LENGTH (valor_string): O LENGTH vai retornar a quantidade de caracteres que temos na string. Exemplo select LENGTH(‘fazendo teste’) from dual; teremos o retorno de 13. LPAD(valor_string, x, valor_pad): Esta função irá definir um tamanho que a string será retornada, a partir deste valor, caso o valor_string for menor que o valor definido em x ele completará o restante deste valor com o carácter definido em valor_pad, isso sendo feito da esquerda para a direita. Se este valor for maior ele cortará os caracteres a mais da direita para esquerda. Exemplos: select LPAD(‘fazendo teste’, 10, ‘*’) from dual; temos o seguinte retorno: “fazendo te”, agora se fizermos a seguinte query select LPAD(‘fazendo teste’, 15, ‘*’) from dual;o Oracle irá retornar “**fazendo teste” RPAD(valor_string, x, valor_pad): O RPAD vai fazer o mesmo que o LPAD a única diferença é que nos casos onde a quantidade de caracteres da string alor_string for menor que o definido em x o valor definido em valor_pad será preenchido na direita. Exemplos: select RPAD(‘fazendo teste’, 10, ‘*’) from dual; temos o seguinte retorno: “fazendo te”, agora se fizermos a seguinte query select RPAD(‘fazendo teste’, 15, ‘*’) from dual; o Oracle irá retornar “fazendo teste**” 3º Funções SQL de Data ADD_MONTHS(valor_data, x): Irá adicionar a quantidade de meses definido em x à data definida em valor_data. Exemplo select ADD_MONTHS (to_date(’16/08/2013′,’DD/MM/RRRR’), 3) from dual; teremos o seguinte retorno ’16/11/2013′. p.s. O TO_DATE vocês vão aprender logo mais, só usei porque não sei que padrão de data foi instalado no Oracle de vocês. MONTHS_BETWEEN (valor_data_1, valor_data_2): Retorna a quantidade de meses entre a valor_data_1 e a valor_data_2. Exemplo select MONTHS_BETWEEN (to_date(’16/11/2013′,’DD/MM/RRRR’), to_date(’16/08/2013′,’DD/MM/RRRR’)) from dual; teremos o retorno de 3. SYSDATE: O SYSDATE retorna a data atual do servido, sendo que se adicionar algum número inteiro ou subtrairmos ele vai adicionar / subtrair a quantidade de dias definidos neste número. Exemplo: select sysdate from dual; como hoje é dia 07/11/2013 ele irá retorna examente esta data, agora se eu adicionar select sysdate + 1 from dual; ele irá retornar 08/11/2013 NEXT_DAY( valor_data, dia_semana): Esta função retornará o próximo dia da semana descrito em dia_semana “Este campo deve ser inserido em inglês” em relação ao valor definido em valor_data. Exemplo: Vamos descobrir quando é o próximo domingo em relação a hoje. select NEXT_DAY(sysdate, ‘Sunday’) from dual; como hoje é dia 07/11/2013 teremos como retorno o dia 10/11/2013 LAST_DAY(valor_data ):Retorna o ultimo dia do mês da data definida em valor_data. Exemplo: select last_day (sysdate) from dual; como novembro tem apenas 30 dias teremos o retorno de 30/11/2013.

p.s. As funções de carácter ROUND e TRUNC também funcionam para parâmetros de data, com as diretrizes idênticas às definidas acima. Exemplos: select trunc(to_date(’07/11/2013 23:57′, ‘dd/mm/rrrr hh24:mi’)) from dual; temos o retorno de “07/11/2013″ já neste caso select round(to_date(’07/11/2013 23:57′, ‘dd/mm/rrrr hh24:mi’)) from dual;, temos o retorno de “08/11/2013″. 4º Funções SQL de Conversão. TO_CHAR(x[,y]): Converte o valor definido em x para carácter, podemos inserir um valor numérico ou de data em x e y é opcional, sendo a mascara de retorno deste valor. Exemplos: select to_char(’4000′, ‘$9999′) from dual; temos o retorno de $4000, select to_char(sysdate, ‘DD/MM/YYYY’) from dual; temos o retorno da data atual, no meu caso 07/11/2013. TO_DATE(x, formato_data): Esta função converte o valor definido em x para data na máscara definida em formato_data. Exemplo: select to_date(’03/12/2013′, ‘DD/MM/RRRR’) + 3 from dual; teremos o retorno de “06/11/2013″, porque estamos adicionando 3 dias à data definida na função NVL(x,y): O NVL vai retornar o valor de y caso x seja um valor nulo, caso contrário irá retornar x. Exemplo: select NVL(NULL, 10) from dual; temos o retorno de 10, agora neste caso select NVL(2, 10) from dual; temos o retorno de 2 DECODE (a, b, c, d, e, valor_padrão): Estão função é o IF dentro do select, ela pega como padrão o primeiro valor definido em a e partir daí vê. Se “a” for igual a “b” então ele retornará “c”, no caso de “a” for igual “d” então retorna “e”, se “a” não for igual a nenhum valor definido ele vai retornar o valor_padrão. Exemplo: select DECODE(2, 2, 10, 3, 20, 100) from dual; vai retornar 10, select DECODE(3, 2, 10, 3, 20, 100) from dual; irá retornar 20, select DECODE(4, 2, 10, 3, 20, 100) from dual;irá retornar 100. Agora fechamos a nossa aula com algumas funções SQL nativas do Oracle, lembrando que esta são as principais funções, com as suas principais variações. 9 lições sobre SELECT que vão te ajudar e muito! Vídeo 3: No dia 23/11/2013 aconteceu o nosso primeiro Hangout sobre SELECT e foi muito bom … Eu particularmente gostei muito da experiência, foi o primeiro e ainda tem muito a melhorar, a partir de agora eu pretendo dar todas as aulas nesse formato, acho que é o melhor, o pessoal consegue interagir e eu consigo tirar as dúvidas de todos ao vivo. Nesta aula tratamos dos seguintes assuntos: 1º O que é um SELECT; 2º A Sintax de um SELECT; 3º Clausula WHERE 4º Como usar um ORDER BY; 5º Um pouco sobre JOINs;

6º GROUP BY o que é e como utiliza-lo; 7º Funções do GROUP BY; 8º Aprenda a utilizar o HAVING; 9º SUBQUERIES e algumas formas de usá-las. A próxima estou marcando para o dia 30/11/2013 às 15 horas novamente. Espero que vocês gostem e ela também estará disponível na nossa sala de aula, se você ficou com alguma dúvida, deixe o seu comentário lá, o próximo Hangout eu vou começar respondendo todas as dúvidas desta aula. Se você quiser entrar um pouco mais cedo entre, vai valer a experiência, posso tentar tirar qualquer dúvida que você tiver, tanto sobre carreira, profissão vale a pena o bate-papo. A uma última coisa revelei uma surpresa para vocês no final do hangout, que tem haver com o assunto certificação! Então vale muito a pena vocês assistirem tudo, tentei fazer o mais detalhista o possível. Aula 08 – Visão Geral PL/SQL Curso Oracle PL/SQL Mais um Hangout e a aula 08 finalizada. Nesta aula vamos abordar uma visão geral doPL/SQL, como eu falei no começo do vídeo é bem teórica, porém muito importante, porque ela é a base para você aprender a linguagem PL/SQL. Os tópicos abordados nesta aula são: 1. 2. 3. 4.

A História do PL/SQL Arquitetura Estrutura Básica de Blocos O que são Transações e Como controlá-las.

Aula 10 – Aprenda Como Funciona o Gerenciamento de Erros no Oracle 5. Além dos tópicos acima temos uma surpresa no final, que eu vou revelar aqui apenas para vocês ficarem mais motivados. Eu vou dar uma bolsa de Estudos no Curso que será lançado em Janeiro, isso mesmo além deste curso de PL/SQL Totalmente Grátis que você já tem, eu vou preparar o melhor aluno para uma certificação Oracle, isso de forma totalmente gratuita, então assista o vídeo até o final, veja todas as regras para participar desta corrida pela Bolsa de Estudos. Oi Pessoal, tudo bem? Com um pouco de atraso, mas a tempo, segue a aula 10 do Curso Oracle PL/SQL. Nesta aula você vai aprender como funciona o Gerenciamento de Erros no Oracle.

Está é a última aula teórica antes de vocês começarem a criar os Programas armazenados dentro do Banco de Dados. Segue abaixo os scripts criados durante o vídeo e assistam até o final, porque sempre tem uma surpresa no meio :). create table tb_produto(id_produto number(10) Primary Key, nm_produto varchar2(30)); INSERT INTO TB_PRODUTO (ID_PRODUTO, NM_PRODUTO) VALUES (1, ‘COCA-COLA’); INSERT INTO TB_PRODUTO (ID_PRODUTO, NM_PRODUTO) VALUES (1, ‘Coca-cola’); BEGIN INSERT INTO TB_PRODUTO (ID_PRODUTO, NM_PRODUTO) VALUES (1, ‘Coca-cola’); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE TB_PRODUTO SET NM_PRODUTO = ‘Coca-Cola’ WHERE ID_PRODUTO = 1; DBMS_OUTPUT.PUT_LINE(‘ENTROU NA EXCEPTION’); END; / DECLARE V_NM_PRODUTO TB_PRODUTO.NM_PRODUTO%TYPE; BEGIN SELECT NM_PRODUTO INTO V_NM_PRODUTO FROM TB_PRODUTO WHERE ID_PRODUTO = 2; DBMS_OUTPUT.PUT_LINE(‘O PRODUTO É :’ ||V_NM_PRODUTO ); EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO TB_PRODUTO (ID_PRODUTO, NM_PRODUTO) VALUES (2, ‘Fanta’); END; / SELECT * FROM TB_PRODUTO; INSERT INTO TB_PRODUTO (ID_PRODUTO, NM_PRODUTO) VALUES (3, ‘Coca-Cola’); DECLARE V_NM_PRODUTO TB_PRODUTO.NM_PRODUTO%TYPE; BEGIN SELECT NM_PRODUTO INTO V_NM_PRODUTO FROM TB_PRODUTO WHERE NM_PRODUTO = ‘Coca-Cola’; DBMS_OUTPUT.PUT_LINE(‘O PRODUTO É :’ ||V_NM_PRODUTO ); EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO TB_PRODUTO (ID_PRODUTO, NM_PRODUTO) VALUES (2, ‘Fanta’);

WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (‘TEM MAIS DE UMA COCA-COLA NA TABELA’); END; / DECLARE V_ZERO_DIVIDE NUMBER(10); BEGIN V_ZERO_DIVIDE := 10/0; DBMS_OUTPUT.PUT_LINE (V_ZERO_DIVIDE); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE (‘NÃO PODE DIVIDIR POR ZERO’); END; DECLARE V_ZERO_DIVIDE NUMBER(10); BEGIN V_ZERO_DIVIDE := 10/0; DBMS_OUTPUT.PUT_LINE (V_ZERO_DIVIDE); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE (‘NÃO PODE DIVIDIR POR ZERO’); END; / DECLARE V_ZERO_DIVIDE NUMBER(10); BEGIN V_ZERO_DIVIDE := 10/0; DBMS_OUTPUT.PUT_LINE (V_ZERO_DIVIDE); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (‘NÃO PODE DIVIDIR POR ZERO ‘ || sqlerrm); END; / DECLARE e_Erro exception; BEGIN IF 1 = 1 THEN RAISE e_Erro; END IF; DBMS_OUTPUT.PUT_LINE (‘Esta com um problema não entrou na Exception’); EXCEPTION WHEN e_Erro THEN DBMS_OUTPUT.PUT_LINE (‘Esta tudo certo’); END; /

Cursores, Procedures e Functions aprenda a realmente usá-los Assistir ao Vídeo...

Chegamos à penúltima aula do Curso Oracle PL/SQL. E ela é para mim considerada a aula mais importante deste curso, então façam-na com bastante atenção. No final dela eu falo um pouco sobre os planos já para o começo de 2014 e lanço um desafio para quem está #embuscadabolsa, a disputa está acirrada então acesse e assista a aula até o final. Os assuntos abordados nesta aula são: O que é um Cursor; Como utilizar um Cursor; Function ou Procedure … Qual você deve usar? Como criar uma Procedure; Como criar uma Function; Exercício, que vai valer alguns PONTOS EXTRAS para quem terminar primeiro! Seguem os scripts feitos durante a aula e depois dos scripts você vai ver as especificações para o exercício. create table tb_alunos (ID_ALUNO NUMBER(10), nm_aluno varchar2(100), DT_NASCIMENTO DATE); INSERT INTO TB_ALUNOS (ID_ALUNO, NM_ALUNO, DT_NASCIMENTO) VALUES (1, ‘WILLIAM’, TO_DATE(’20/01/1989′,’DD/MM/RRRR’)); INSERT INTO TB_ALUNOS (ID_ALUNO, NM_ALUNO, DT_NASCIMENTO) VALUES (2, ‘WILLIAM2′, TO_DATE(’20/02/1989′,’DD/MM/RRRR’)); INSERT INTO TB_ALUNOS (ID_ALUNO, NM_ALUNO, DT_NASCIMENTO) VALUES (3, ‘WILLIAM3′, TO_DATE(’20/03/1989′,’DD/MM/RRRR’)); INSERT INTO TB_ALUNOS (ID_ALUNO, NM_ALUNO, DT_NASCIMENTO) VALUES (4, ‘WILLIAM4′, TO_DATE(’20/04/1989′,’DD/MM/RRRR’)); DECLARE CURSOR cur_alunos IS SELECT ID_ALUNO, NM_ALUNO, DT_NASCIMENTO FROM TB_ALUNOS; BEGIN FOR C_ALUNOS IN cur_alunos LOOP DBMS_OUTPUT.PUT_LINE (‘O CÓDIGO DO ALUNO É: ‘ || C_ALUNOS.ID_ALUNO); DBMS_OUTPUT.PUT_LINE (‘O NOME DO ALUNO É: ‘ || C_ALUNOS.NM_ALUNO); DBMS_OUTPUT.PUT_LINE (‘A DATA DE NASCIMENTO DO ALUNO É: ‘ || C_ALUNOS.DT_NASCIMENTO); DBMS_OUTPUT.PUT_LINE (‘———————————————————— —————-’); DBMS_OUTPUT.PUT_LINE (”); END LOOP; END; DECLARE

CURSOR cur_alunos(C_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE) IS SELECT ID_ALUNO, NM_ALUNO, DT_NASCIMENTO FROM TB_ALUNOS WHERE ID_ALUNO = C_ID_ALUNO; V_NM_ALUNO TB_ALUNOS.NM_ALUNO%TYPE; V_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE; V_DT_NASCIMENTO TB_ALUNOS.DT_NASCIMENTO%TYPE; BEGIN OPEN cur_alunos(1); FETCH cur_alunos INTO V_ID_ALUNO, V_NM_ALUNO, V_DT_NASCIMENTO; CLOSE cur_alunos; DBMS_OUTPUT.PUT_LINE (‘O CÓDIGO DO ALUNO É: ‘ || V_ID_ALUNO); DBMS_OUTPUT.PUT_LINE (‘O NOME DO ALUNO É: ‘ || V_NM_ALUNO); DBMS_OUTPUT.PUT_LINE (‘A DATA DE NASCIMENTO DO ALUNO É: ‘ || V_DT_NASCIMENTO); DBMS_OUTPUT.PUT_LINE (‘———————————————————— —————-’); DBMS_OUTPUT.PUT_LINE (”); END; CREATE OR REPLACE PROCEDURE SP_REL_ALUNOS (P_ID_ALUNO IN TB_ALUNOS.ID_ALUNO%TYPE, P_NM_ALUNO OUT TB_ALUNOS.NM_ALUNO%TYPE, P_DT_NASCIMENTO OUT TB_ALUNOS.DT_NASCIMENTO%TYPE) IS CURSOR cur_alunos(C_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE) IS SELECT ID_ALUNO, NM_ALUNO, DT_NASCIMENTO FROM TB_ALUNOS WHERE ID_ALUNO = C_ID_ALUNO; V_NM_ALUNO TB_ALUNOS.NM_ALUNO%TYPE; V_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE; V_DT_NASCIMENTO TB_ALUNOS.DT_NASCIMENTO%TYPE; BEGIN OPEN cur_alunos(P_ID_ALUNO); FETCH cur_alunos INTO V_ID_ALUNO, V_NM_ALUNO, V_DT_NASCIMENTO; CLOSE cur_alunos; P_NM_ALUNO := V_NM_ALUNO; P_DT_NASCIMENTO := V_DT_NASCIMENTO; END SP_REL_ALUNOS; DECLARE V_NM_ALUNO TB_ALUNOS.NM_ALUNO%TYPE; V_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE;

V_DT_NASCIMENTO TB_ALUNOS.DT_NASCIMENTO%TYPE; BEGIN V_ID_ALUNO := 3; SP_REL_ALUNOS(P_ID_ALUNO => V_ID_ALUNO, P_NM_ALUNO => V_NM_ALUNO, P_DT_NASCIMENTO => V_DT_NASCIMENTO); DBMS_OUTPUT.PUT_LINE (‘O CÓDIGO DO ALUNO É: ‘ || V_ID_ALUNO); DBMS_OUTPUT.PUT_LINE (‘O NOME DO ALUNO É: ‘ || V_NM_ALUNO); DBMS_OUTPUT.PUT_LINE (‘A DATA DE NASCIMENTO DO ALUNO É: ‘ || V_DT_NASCIMENTO); DBMS_OUTPUT.PUT_LINE (‘———————————————————— —————-’); DBMS_OUTPUT.PUT_LINE (”); END; CREATE OR REPLACE FUNCTION FN_REL_ALUNO (P_ID_ALUNO IN TB_ALUNOS.ID_ALUNO%TYPE) RETURN TB_ALUNOS.NM_ALUNO%TYPE IS CURSOR cur_alunos(C_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE) IS SELECT ID_ALUNO, NM_ALUNO, DT_NASCIMENTO FROM TB_ALUNOS WHERE ID_ALUNO = C_ID_ALUNO; V_NM_ALUNO TB_ALUNOS.NM_ALUNO%TYPE; V_ID_ALUNO TB_ALUNOS.ID_ALUNO%TYPE; V_DT_NASCIMENTO TB_ALUNOS.DT_NASCIMENTO%TYPE; BEGIN OPEN cur_alunos(P_ID_ALUNO); FETCH cur_alunos INTO V_ID_ALUNO, V_NM_ALUNO, V_DT_NASCIMENTO; CLOSE cur_alunos; return V_NM_ALUNO; END FN_REL_ALUNO; SELECT FN_REL_ALUNO(4) FROM DUAL; O Exercício deve ser feito da seguinte forma: 1º Você deve criar uma Procedure Chamada SP_CONTROLA_CAIXA, Esta procedure vai receber o P_ID_PRODUTO, da tabela TB_PRODUTO campo ID_PRODUTO como parâmetro de entrada e terá o parâmetro P_ERRO como parâmetro de saída. 2º Dentro dessa procedure você vai criar um cursor que vai verificar se existe o produto no estoque. Se não existir ele deve retornar uma exception, com o seguinte descrtivo “Este produto não está disponível no estoque!” 3º Se existir o produto no estoque o ID_PRODUTO deve ser o parâmetro de

uma function que vai retornar o valor do produto. 4º Dentro dessa function deve haver uma verificação na tabela TB_DESCONTO, que deve ser criar com os campos ID_PRODUTO e VL_DESCONTO (Valor em porcentagem, então deve ser do tipo number (5,2)), 5º Se existir desconto para este produto, lembrando que a verificação deve ser feita por um cursor, deve-se aplicar o desconto retonardo no valor de venda do produto, que deve ser retornado em outro cursor. 6º Este valor deve ser retornado para a procedure e caso não tenha desconto deve-se retornar o valor real do produto. 7º Após o desconto aplicado você vai gravar este valor, o ID_PRODUTO e a DATA_ATUAL dentro da tabela TB_VENDAS. 8º Não se esquecer dos gerenciamentos de erros, vou cadastrar produtos com o valor 0 e tentar gravar um desconto de valor 0, por exemplo então você deve gerenciá-los muito bem para eu considerar o exercício como correto. Sequence, Package e Trigger Como e Quando Utiliza-los Vídeo Tudo tem um começo, um meio e um fim … E hoje chegou ao fim o curso Oracle PL/SQL, fico muito triste com o fim, mas muito feliz por tudo que passamos juntos. Lembrando que sem ter feito a última aula, você não vai conseguir entender

… Melhor assisti-la até o final. Na aula de hoje vou abordar os seguintes assuntos: 1º Corrigir os exercícios da última aula que foram enviados; 2º Sequencies, o que são, como e porque utiliza-las; 3º O que é uma Package; 4º Porque utilizar uma Package; 5º Como utilizar uma Package; 6º O que é uma Trigger;

7º Quanto utilizar uma Trigger; 8º Como utilizar uma Trigger. Agora um pouco dos códigos que foram passados na última aula: Código para Criação de uma Sequence: CREATE SEQUENCE SQ_VENDA INCREMENT BY 1 START WITH 1 MAXVALUE 9999999999; Agora para criar uma Package Spec: CREATE OR REPLACE PACKAGE PKG_CONTROLA_CAIXA PROCEDURE SP_CONTROLA_CAIXA(P_ID_PRODUTO IN TB_PRODUTO.ID_PRODUTO%TYPE, P_ERRO OUT VARCHAR2); END PACKAGE PKG_CONTROLA_CAIXA; Package Body: CREATE OR REPLACE PACKAGE BODY PKG_CONTROLA_CAIXA FUNCTION FN_RET_VALOR(P_ID_PRODUTO in TB_PRODUTO_ESTOQUE.ID_PRODUTO%type, P_ERRO OUT varchar2) return TB_PRODUTO.VL_VENDA_PRODUTO%type is –Esse cursor verifico se existe a venda cursor CUR_VENDA (C_ID_PRODUTO TB_PRODUTO_ESTOQUE.ID_PRODUTO%type) is select VL_VENDA_PRODUTO from TB_PRODUTO where ID_PRODUTO = C_ID_PRODUTO; –Esse cursor verifico se existe o desconto cursor CUR_DESCONTO (C_ID_PRODUTO TB_PRODUTO_ESTOQUE.ID_PRODUTO%type) is select VL_DESCONTO from TB_DEsCONTO where ID_PRODUTO = C_ID_PRODUTO; –Declaração das variaveis V_VL_DESCONTO TB_DESCONTO.VL_DESCONTO%type; V_VL_VENDA_PRODUTO TB_PRODUTO.VL_VENDA_PRODUTO%type; begin –Abertura do cursor que verifica a venda open CUR_VENDA(P_ID_PRODUTO); FETCH CUR_VENDA into V_VL_VENDA_PRODUTO; close CUR_VENDA; begin –Abertura do cursor que verifica o desconto open CUR_DESCONTO(P_ID_PRODUTO);

FETCH CUR_DESCONTO into V_VL_DESCONTO; – Nesse bloco é: se existir registros no desconto, então tenho que aplicar o valor do desconto if CUR_DESCONTO%FOUND then V_VL_VENDA_PRODUTO:= V_VL_VENDA_PRODUTO – (V_VL_VENDA_PRODUTO*(V_VL_DESCONTO/100)); — Aqui é o calculo de % end if; EXCEPTION when OTHERS then P_ERRO:= ‘Não foi possível aplicar desconto no produto’||SQLERRM; end; close CUR_DESCONTO; return V_VL_VENDA_PRODUTO; end FN_RET_VALOR; PROCEDURE SP_CONTROLA_CAIXA( P_ID_PRODUTO IN TB_PRODUTO.ID_PRODUTO%TYPE, P_ERRO OUT VARCHAR2) IS – Cursor para verificar se existe o produto no estoque CURSOR CUR_PRODUTO(C_ID_PRODUTO TB_PRODUTO.ID_PRODUTO%TYPE) IS SELECT PE.ID_PRODUTO, P.NM_PRODUTO, PE.QT_PRODUTO_ESTOQUE FROM TB_PRODUTO P, TB_PRODUTO_ESTOQUE PE WHERE PE.ID_PRODUTO = P.ID_PRODUTO AND PE.ID_PRODUTO = C_ID_PRODUTO; – Declaração das variáveis V_ID_PRODUTO TB_PRODUTO_ESTOQUE.ID_PRODUTO%TYPE; V_NM_PRODUTO TB_PRODUTO.NM_PRODUTO%TYPE; V_QT_PRODUTO_ESTOQUE TB_PRODUTO_ESTOQUE.QT_PRODUTO_ESTOQUE%TYPE; V_VALOR_PRODUTO TB_PRODUTO.VL_VENDA_PRODUTO%TYPE; V_ID_VENDAS TB_VENDAS.ID_VENDA%TYPE; e_produto_nao_encontrado exception; BEGIN BEGIN OPEN CUR_PRODUTO (P_ID_PRODUTO); FETCH CUR_PRODUTO INTO V_ID_PRODUTO, V_NM_PRODUTO, V_QT_PRODUTO_ESTOQUE; – Neste bloco verifico se existe registros no cursor, se não existir mostro uma msg ao usuário IF CUR_PRODUTO%NOTFOUND THEN P_ERRO := ‘PRODUTO INEXISTENTE, VERIFIQUE O CÓDIGO INFORMADO’; raise e_produto_nao_encontrado; –Aproveitei e já verifico se existe o produto no estoque, caso não exista disparo

outra mensagem para o usuario ELSIF V_QT_PRODUTO_ESTOQUE 1000, P_ERRO => V_ERRO); END; Criando uma Trigger de Log: CREATE OR REPLACE TRIGGER TRG_VENDAS BEFORE UPDATE OR DELETE ON TB_VENDAS FOR EACH ROW BEGIN IF UPDATING THEN INSERT INTO TB_VENDAS_HST (ID_VENDA, ID_PRODUTO, DT_ATUAL, VALOR_PRODUTO, DT_ALTERACAO, NM_EVENTO) VALUES (:OLD.ID_VENDA, :OLD.ID_PRODUTO, :OLD.DT_ATUAL, :OLD.VALOR_PRODUTO, SYSDATE, ‘UPDATE’); ELSIF DELETING THEN INSERT INTO TB_VENDAS_HST (ID_VENDA, ID_PRODUTO, DT_ATUAL, VALOR_PRODUTO, DT_ALTERACAO, NM_EVENTO) VALUES (:OLD.ID_VENDA, :OLD.ID_PRODUTO, :OLD.DT_ATUAL, :OLD.VALOR_PRODUTO, SYSDATE, ‘DELETE’); END IF; END TRG_VENDAS;