Até aqui vimos a como criar, manipular e gerenciar um banco de dados com a SQL. A partir desse ponto começaremos a estudar assuntos que farão uso da linguagem de programação PL/SQL, que nos auxiliará a manipulação dos dados em blocos de comandos, dispostos de forma lógica.
PL significa Procedural Language (Linguagem Procedural). É uma linguagem estruturada em blocos e cada bloco pode conter indeterminado sub-blocos.
A reunião de comandos da PL/SQL é capaz de processar uma série de atividades como consultas, cálculos, inclusões, alterações, atualizações, etc., retornando ou não informações ao usuário.
• Function.
Veremos aqui como criar e trabalhar com functions (funções). Funções são rotinas que retornam algum valor, sejam eles consultados ou calculados.
Vejamos como elas funcionam!
Elaboraremos uma função para calcular os juros das vendas. A taxa será de 5%.
Digite:
create or replace function calcula_juros (cod_venda in number) return number is v_vlr_total number(5.2); v_vlr_final number(5.2); begin select valor_total into v_vlr_total from vendas
where codigo = cod_venda;
v_vlr_final := v_vlr_total + (v_vlr_total * 0.05); return (v_vlr_final);
end; /
Vamos entender a sintaxe de uma função em PL/SQL!
Na cor verde temos o cabeçalho da função. Usamos o create or replace function como comando de criação da função (se a mesma já tiver sido criada anteriormente, ao indicarmos replace ela será recarregada). Em seguida, entre parênteses, informamos o parâmetro de entrada que receberá um valor externo para que
esse valor seja utilizado. Posteriormente informamos o tipo de dado que deverá ser retornado, no nosso exemplo, number.
Na cor azul temos a declaração de variáveis. Nesse ponto não há segredos, pois para realizar tal tarefa fazemos tal como na criação de tabelas. Aqui informamos o nome das variáveis e o tipo de dados das mesmas.
Em vermelho indicamos o início e o fim do nosso bloco de dados.
Na cor preta temos a manipulação dos dados. Fizemos um comando SQL para buscar os valores do campo valor_total da tabela vendas. Note que antes do from utilizamos a cláusula into. Sempre que utilizamos um comando select dentro de um bloco de comandos PL/SQL devemos informar a cláusula into e informar a variável (que tem que ser declarada) que receberá o valor que o select retornar. Por fim, na cláusula where comparamos um determinado campo da tabela ao parâmetro de entrada que definimos no cabeçalho da função. Em seguida temos o cálculo do total da venda mais a taxa de juros. Observe a sintaxe do sinal “recebe” (:=).
Por fim, na cor rosa, temos o comando return, que indica o que deve ser retornado.
Se não houver erros de sintaxe na função, sua tela deverá estar assim:
Figura 4.1: Create Function
Até esse ponto apenas criamos a função. Vamos ver como utilizá-la. Digite:
select calcula_juros(4) from dual;
Note que utilizamos um comando select para “chamar” a função, e informamos, entre parênteses, o código da venda que queremos que seja calculado os juros.
Figura 4.2: Executando Function
Veja que o resultado calculado foi 798. A venda de código 4 tinha o valor original de 760,00. Cinco por cento desse valor é 38,00, somando isso ao valor inicial, temos 798. Isso comprova que a função que elaboramos está correta.
Funções são muito úteis quando há de se realizar cálculos, porém podem ser aplicadas em qualquer situação, pois podem também retornar dados do tipo char ou varchar2. Tudo depende da interpretação do desenvolvedor para os mais diversos casos.
Se desejarmos excluir uma função podemos digitar um comando SQL, tal qual nos indica a sintaxe:
drop function [nome da função];
• Procedure.
Nesse tópico veremos como criar e trabalhar com procedures (procedimentos).
Procedimentos são muito semelhantes à funções, pois também são capazes de armazenar vários blocos de comandos em PL/SQL. A principal diferença entre um procedimento e uma função é que a função sempre retornará um valor, enquanto um procedimento não retorna valor algum.
Vamos elaborar um procedimento que insira registros na nossa tabela vendas. Já vimos como fazer isso em SQL, agora vamos colocar esse comando SQL dentro de um bloco PL/SQL.
Então digite:
create or replace procedure insere_vendas
(cod in number, vendedor in number, dt_venda in date, total in number) is begin
insert into vendas (codigo, cod_vendedor, data, valor_total) values (cod, vendedor, dt_venda, total);
end; /
Aqui criamos um procedimento extremamente simples, com uma única finalidade, que é inserir registros na tabela vendas.
Na cor verde temos o cabeçalho do nosso procedimento. Assim como na função, informamos quais serão os parâmetros de entrada e o tipo de valor que eles receberão.
Em vermelho temos a declaração de início e fim do nosso procedimento. Note que no fim colocamos o comando end seguido do nome que demos ao procedimento.
E na cor preta temos o nosso bloco de códigos, que é composto apenas por um simples comando insert.
Se não houver erros de sintaxe no procedimento, sua tela deverá estar assim:
Figura 4.3: Create Procedure
Até esse ponto apenas criamos o procedimento, porém ainda não fizemos uso dele.
Então, para executá-lo, digite:
exec insere_vendas (6, 4, '23/04/2010', 490.00);
Para executarmos um procedimento fazemos uso do comando exec. Após o comando exec, informamos o nome do procedimento e passamos os parâmetros que o mesmo deverá receber.
Após executar um procedimento, sua tela do SQL*Plus deverá estar parecida com a seguinte:
Figura 4.4: Executando Procedure
Agora, para confirmarmos, digite um comando SQL que nos possibilite ver todos os registros da tabela vendas:
Resultado:
Figura 4.5: Verificando resultado da Procedure
Note que a venda de código 6 foi inserida, provando que nosso procedimento está funcionando.
Se desejarmos excluir um procedimento basta usarmos o comando drop, tal como a sintaxe a seguir:
• Sequence.
Neste tópico veremos como criar e trabalhar com sequences (sequências). Sequências são utilizadas para gerar valores numéricos que deverão preencher algum campo. Elas são muito úteis na geração de chaves primárias, já que uma das moires controvérsias do Oracle é não possuir campos do tipo auto-increment.
Vejamos como criar uma sequência. Digite: create sequence vendas_seq
increment by 1 start with 7 nomaxvalue nominvalue nocycle noorder nocache;
Vamos entender a estrutura da sequência!
Na cor verde temos o cabeçalho da tabela, onde informamos o nome da nossa sequência.
Na cor azul informamos o intervalo de incremento da sequência. No nosso caso informamos o valor 1, então a sequência aumentará uma unidade a cada vez que for utilizada.
Em vermelho informamos qual será o valor inicial dessa sequência, no nosso caso iniciará com o valor 7, pois o maior valor da chave primária da tabela vendas é 6.
Em rosa informamos que não haverá valor máximo para a nossa sequência. Em preto informamos que não haverá valor mínimo para a nossa sequência (caso o mesma fosse decrescente).
Em roxo informamos que o código não realizará ciclos.
Em azul claro informamos que os códigos não serão ordenados.
Em cinza informamos que os dados não deverão ser armazenados em cache. Se sua sequência estiver correta, sua tela deverá estar parecida com a seguinte:
Figura 4.6: Criando Sequence
Não executaremos nossa sequência agora pois como ela insere chaves- primárias na tabela vendas, precisamos inserir um registro na mesma para vermos o funcionamento da mesma, e esse registro só inseriremos quando estudarmos o próximo tópico desse material.
Para apagar uma sequência, basta utilizar o comando drop, como exemplificado na sintaxe a seguir:
drop sequence [nome da sequência];
• Trigger.
O conceito de triggers (gatilhos) é um dos mais importantes de nosso estudo! É muito utilizado na linguagem PL/SQL, muito útil para os mais diversos casos.
O nome “gatilho” não é por acaso, pois são blocos de códigos que são “disparados” em determinado momento escolhido pelo desenvolvedor.
Um gatilho pode ter todo tipo de manipulação de dados. Ele pode ser acionado antes (before) ou depois (after) de determinada ocorrência, seja essa ocorrência uma inclusão, exclusão ou atualização.
Imaginemos o seguinte: até o momento, quando vamos inserir um registro em qualquer tabela, temos que informar manualmente o valor da chave-primária. Em tabelas com poucos registros isso não é tarefa difícil, mas imagine uma tabela com milhares de registros. Seria necessário consultarmos o valor máximo da chave-primária dessa tabela toda vez que fossemos inserir um registro. Seria um trabalho desnecessário!
No exemplo que será mostrado aqui vamos fazer um gatilho que acione a sequência que criamos anteriormente. Ou seja, cada vez que inserirmos um registro na tabela vendas não precisaremos informar o valor da chave-primária, pois teremos uma sequência que gerará esses valores e um gatilho que acionará essa sequência no momento da inclusão.
create or replace trigger trg_vendas_cp
before insert on vendas for each row
begin
select vendas_seq.nextval into :new.codigo from dual; end;
/
Vamos entender!
Na cor verde temos o cabeçalho do nosso gatilho, que contem o nome do gatilho (trg_vendas_cp).
Em azul indicamos o momento em que o gatilho deve ser “disparado”, a tabela que será alterada por ele, e informamos for each row, que significa que os comandos afetarão qualquer campo da tabela informada. Se desejássemos especificar um campo deveríamos informar o comando table columns e o nome das colunas que seriam alteradas. No nosso caso não será necessário, pois especificamos a coluna dentro do nosso bloco de comandos.
Em vermelho temos o início e o fim do nosso bloco de comandos.
Em preto temos um código SQL que ainda não conhecemos. Portanto cabe aqui uma breve explicação sobre ele. Fizemos uso de um comando select, obviamente para selecionar um valor. Em seguida informamos que o select deverá buscar esse valor em um sequência (no nosso caso vendas_seq); logo após informamos, com a função nextval, que deverá ser retornado o próximo valor da sequência. Informamos o comando :new pois estaremos aqui inserindo um novo registro na tabela; o into indica que esse valor deverá ser inserido em algum lugar, e após o into informamos o nome da coluna que esse valor deverá ser inserido, no nosso caso a coluna codigo. Por fim, informamos a tabela padrão do Oracle: dual, pois esse próximo número da sequência não existe cadastrado no banco de dados e por isso devemos informar a tabela dual.
Figura 4.7: Criando Trigger Agora vamos testar!
Insira um novo registro na tabela vendas, mas não informe o valor do campo codigo. Segue sugestão: Tabela: vendas CAMPO VALOR codigo cod_vendedor 5 data 26/04/10 valor_total 320,00 Tabela 4.1: Venda 6
Figura 4.8: Inserindo Registro
Agora elabore um comando select que exiba todos os registros da tabela vendas.
Figura 4.9: Verificando Vendas
Para apagar um gatilho, basta utilizar o comando drop, como exemplificado na sintaxe a seguir:
drop sequence [nome do gatilho];
• View
Neste tópico, estudaremos o conceito e a aplicabilidade de views (visões) no banco de dados Oracle.
Uma visão é parecida com uma tabela, pois possui colunas onde é possível realizar consultas. É considerada uma “máscara” sobreposta a uma ou mais estruturas de dados, normalmente tabelas. São usadas para que o usuário tenha acesso a determinadas informações, e as mesmas, quando acessadas de uma visão, não podem ser alteradas, apenas consultadas.
Vamos construir uma visão!
Na nossa visão, faremos uma consulta na tabela vendedores, e retornaremos o código e o nome de cada vendedor.
Portanto, digite:
create or replace view vendedor_vw as
select cod_vendedor, nome from vendedores;
Vamos entender o que fizemos!
Como percebido, uma visão possui uma estrutura extremamente simples! Na cor verde temos o cabeçalho da visão, onde informamos o nome da visão. Na cor preta temos a consulta que essa visão retornará.
E só! Uma visão é composta apenas por essas duas partes. É claro que, observando esse exemplo, podemos imaginar que esse é um recurso desnecessário, inútil. Mas imagine uma consulta de cem, cento e cinquenta linhas! É muito melhor usarmos uma consulta desse tamanho salvo em uma visão. Se sua visão for criada corretamente, sua tela do SQL*Plus deverá estar semelhante a seguinte:
Figura 4.10: Criando View
Até esse ponto apenas criamos a visão, mas ainda não fizemos uso da mesma. Então, vejamos agora como executar uma visão.
Digite:
select * from vendedor_vw;
Veja que quando efetuamos o comando select indicando a visão que criamos é como se possuíssemos uma tabela no banco de dados com o nome vendedor_vw, onde ficariam armazenados os códigos e os nomes dos vendedores. O resultado deverá estar parecido com o seguinte:
Figura 4.11: Visualizando View
Se desejar apagar uma visão, basta utilizar o comando drop: drop view [nome da visão];
• Index
Até aqui vimos muitos assuntos de extrema importância dentro do banco de dados Oracle 10g XE. Aprenderemos agora o conceito e como trabalhar com index (índice) no banco de dados.
Índices são objetos do banco de dados e são usados para localizar de forma rápida determinados registros em uma tabela. São geralmente utilizados quando a tabela começa passa a armazenar uma quantidade muito grande de registros. Toda chave- primária pode ser considerada um índice, pois possuem valores únicos em uma tabela e por meio desses valores conseguimos chegar à um determinado registro específico. Porém, pode haver casos em que a massa de dados de uma coluna seja realmente muito grande, e nesses casos a criação de um índice é essencial para facilitar a localização dos dados necessários. Aqui não perceberemos as reais vantagens dos índices, pois nossa massa de dados não é grande o suficiente. Mas mesmo assim veremos como trabalhar com eles.
Vamos criar um índice na tabela vendedores. Esse índice será o campo nome. Mas antes de criá-lo digite um comando select que retorne todos os registros apenas do campo nome (que em breve será nosso índice) e observe o resultado:
Figura 4.12: Campo Nome da Tabela Vendedores Agora digite:
create unique index vendedor_nome on vendedores (nome);
Vamos entender as partes que compõem a criação de um índice!
Em verde temos o comando de criação do índice. A opção unique é opcional, mas se for definido que o índice será único a coluna referida não poderá conter valores repetidos. No nosso caso, como se trata apenas de um teste, poderemos preservar a opção unique, mas se fosse um caso real, jamais poderíamos deixar um índice que é composto por um campo que registra nomes de funcionários com a opção unique declarada, pois pode duas pessoas podem perfeitamente ter o mesmo nome, embora não seja comum. Se não quiséssemos que nosso índice fosse único bastaria apenas não informar a a palavra unique.
Em preto temos a definição do nome do índice.
Em vermelho temos a indicação da tabela que vai receber esse índice. E por fim, em azul, indicamos o campo que será o índice.
Se seu código não possuir erros de sintaxe, sua tela deverá estar semelhante à tela seguinte:
Figura 4.13: Criando Index
Assim como fizemos antes de criarmos o índice, elabore um comando select que retorne todos os registros apenas do campo nome.
Figura 4.14: Funcionalidade do Index
Observe que os nomes estão agora aparecendo em ordem alfabética. Com a criação do índice esses campos aparecem de forma organizada alfabeticamente, porém eles continuam guardados na ordem física em que foram gravados.
Caso queira excluir um índice, basta utilizar o comando drop, como na sintaxe: drop index [nome do índice];