Introdução a plsql

PL/SQL
PL/SQL (acrônimo para a expressão inglesa Procedural Language/Structured Query Language) é uma
extensão da linguagem padrão SQL primeiramente criada para o SGBD Oracle da Oracle Corporation.
É uma Linguagem Procedural da Oracle, estendida da SQL.
Permite que a manipulação de dados seja incluída em unidades de programas. Blocos de PL/SQL são
passados e processados por uma PL/SQL Engine que pode estar dentro de uma ferramenta de
desenvolvimento de aplicações ou no SGBD. A PL/SQL Engine filtra os comandos SQL e manda
individualmente o comando SQL para o SQL Statement Executor no SGBD, que processa o PL/SQL
com os dados retornados do SGBD.
É a linguagem básica para criar programas complexos e poderosos, não só no banco de dados, mas
também em diversas ferramentas Ligadas ao SGBD.
Antes de 1991 a única forma de usar construções procedurais com o SQL era usar PRO*C. Foi onde as
instruções SQL do Oracle foram embutidas em código na linguagem “C”. O código “C” era précompilado para converter as instruções SQL em chamadas de bibliotecas.
Em 1991 o PL/SQL 1.0 foi lançado com o Oracle Versão 6.0. Ele era muito limitado nas suas
capacidades.
A unidade básica em PL/SQL é um bloco. Todos os programas em PL/SQL são compostos por blocos,
que podem estar localizados uns dentro dos outros. Geralmente, cada bloco efetua uma ação lógica no
programa. Um bloco tem basicamente a seguinte estrutura para SGBDs Oracle:
DECLARE
Seção para declaração de variáveis,tipos e subprogramas locais.
BEGIN
Seção Executável, nesta seção ficam as instruções procedurais e SQL. Esta é a única seção do bloco que é indispensável e
obrigatória.
EXCEPTION
Seção/Setor onde ficam as instruções de tratamento de erro.
END
Procedures
Uma procedure nada mais é que um bloco PL/SQL nomeado. A grande vantagem sobre um bloco
PL/SQL anônimo é que pode ser compilado e armazenado no banco de dados como um objeto de
schema. Graças a essa característica as procedures são de fácil manutenção, o código é reutilizável e
permitem que trabalhemos com módulos de programa.
Uma procedure é, então, um bloco PL/SQL nomeado que pode aceitar argumentos (também chamado
de parâmetros) e pode ser chamada por um programa, uma sessão SQL ou uma trigger.
Durante a instalação do banco de dados Oracle um script é executado automaticamente e cria toda a
estrutura necessária para que as procedures sejam executadas. Eventualmente esse procedimento automático pode
falhar devido a alguma falha física no disco rígido, nesse caso o usuário SYS pode recriar a estrutura através do
script SQL DBMSSTDX.SQL.
Para criar uma procedure o usuário precisa ter o privilégio de sistema CREATE PROCEDURE, para
criar a procedure em outros schemas (Banco de dados) o usuário deve ter o privilégio de CREATE ANY
PROCEDURE. Este é um ponto muito interessante sobre as procedures, os privilégios para criação de
procedures têm que concedidos explicitamente, ou seja, não pode ser adquirido através de roles
(usuários comuns).
Para executar uma procedure externa é necessário ter o privilégio de EXECUTE. Caso queira alterar a
procedure de outro schema deve ter o privilégio de sistema ALTER ANY PROCEDURE.
A sintaxe básica de uma procedure no MYSQL é:
CREATE PROCEDURE proc_name([parameters, ...])
[characteristics]
[BEGIN]
corpo_da_rotina;
[END]
Explicando...
proc_name: seu procedimento armazenado deve ter um nome, para quando for chamado, podermos
então usá-lo;
tipo_param: existem 3 tipos de parâmetros em uma Stored Procedure no MySQL:



IN => este é um parâmetro de entrada, ou seja, um parâmetro cujo seu valor será utilizado no
interior do procedimento para produzir algum resultado;
OUT => esté parâmetro retorna algo de dentro do procedimento para o lado externo, colocando
os valores manipulados disponíveis na memória ou no conjunto de resultados;
INOUT => faz os dois trabalhos ao mesmo tempo!
parameters: nessa parte do procedimento, informaremos os parâmetros da seguinte forma: [IN | OUT |
INOUT] nome_parametro tipo_dado.
characteristics: as características do procedimento pode apresentar. Como não utilizaremos
inicialmente tais características, vamos nos ater a sintaxe principal. Questões de segurança, se é
determinística ou não, qual a linguagem que estamos utilizando e se nosso procedimento modificará
dados na banco de dados, são algumas das características que poderemos definir neste item que
abordaremos com mais detalhe na parte dois do artigo.
corpo_da_rotina: onde são definidos os comandos SQL que farão alguma manipulação e/ou defenderão
alguma lógica, podendo retornar ou não algum resultado.
Vamos ver um exemplo de procedure para ajudar nosso entendimento :
O Query Browser já nos deu quase tudo pronto para escrevermos somente os parâmetros que teremos
no nosso procedimento e o corpo da rotina. Notem que é utilizado o operador DELIMITER para mudar o
delimitador de comandos, que por padrão é o ";". Mudamos o DELIMITADOR para podermos usar o ";"
no meio do procedimento. Caso não efetuemos essa troca, o procedimento será enviado pela metade e
um erro será enviado ao terminal, por erro na sintaxe.
DELIMITADOR no MySQL, em outras situações, por padrão também é chamado de terminador. Para
verificar qual é o delimitador da sessão corrente emita o comando \s, que é a forma curta do comando
STATUS.
DELIMITER $$
DROP PROCEDURE IF EXISTES `banco`.`nomeproc`$$
CREATE PROCEDURE `banco`.`nomepreoc`( )
BEGIN
SELECT 'Olá!';
END$$
DELIMITER ;
Bem, procedimento compilado basta executarmos o procedimento com o seguinte comando:
CALL OLA();
OBS.: Após a versão 5.1.18, não é mais necessário os parênteses caso o procedimento não receba
parâmetros.
A resposta do procedimento será "OLÁ!" como definimos.
Bom, agora que já temos uma noção básica de como é implementado um procedimento armazenado
no MySQL, já podemos partir para aplicações do mundo real, tais como, manipular inserções de dados,
exclusões de registros e atualizações de linhas de uma ou mais tabelas. Tudo isso nos levará a aportar
lá na frente no conceito de transações, onde desenvolveremos um procedimento para simulação de
transferência de valor entre contas bancárias de mesma agência.
Como seria então, um procedimento para inserir dados em uma tabela do banco de dados?
Bom, antes de prosseguirmos, criaremos uma tabela, de nome correntista, que terá os campos
correntista_id do tipo INT, correntista_nome do tipo VARCHAR(60) e correntista_cpf do tipo
VARCHAR(20).
CREATE TABLE tbl_correntista (
correntista_id int auto_increment primary key,
correntista_nome varchar(60) not null unique,
correntista_cpf varchar(20) not null,
dt_cadastro timestamp default current_timestamp,
) Engine =InnoDB;
Pronto! Após executarmos o script acima nossa tabela estará criada e já poderemos dar carga através
de um procedimento armazenado, onde também utilizaremos em meio a este, estruturas condicionais,
IF-THEN-ELSE!
Com a nossa tabela criada, criaremos nosso procedimento para efetuar o INSERT dos dados, ou seja,
um procedimento para dar carga na tabela.
DELIMITER //
CREATE PROCEDURE correntistaInsert(v_nome VARCHAR(60), v_cpf VARCHAR(20))
BEGIN
IF ((v_nome != '') && (v_cpf != '')) THEN
INSERT INTO tbl_correntista (correntista_nome, correntista_cpf)
VALUES (v_nome, v_cpf);
ELSE
SELECT 'NOME e CPF devem ser fornecidos para o cadastro!' AS Msg;
END IF;
END;
//
Após compilarmos o procedimento, já poderemos chamá-lo através da declaração CALL, como se
segue:
CALL correntistaInsert('Wagner Bianchi', '023.456.789-10');
...notem que utilizamos em meio ao nosso procedimento de inserção, a estrutura condicional para
consistir o valor das variáveis. Caso os valores de ambas sejam vazios, a mensagem será disparada.
Faremos a seguir, um procedimento, com o mesmo formato para atualizarmos o registro da nossa
tabela "tbl_correntista", que até o momento encontra-se com um registro. Serão três agora, o
identificador do registro - v_id - , o novo nome do correntista “v_nome” e o novo cpf “v_cpf”.
DELIMITER //
CREATE PROCEDURE correntistaUpdate(v_id INT, v_nome VARCHAR(60), v_cpf VARCHAR(20))
BEGIN
IF (((v_id > 0) && (v_id != '') ) && (v_nome != '') && (v_cpf != '')) THEN
UPDATE tbl_correntista SET correntista_nome =v_nome, correntista_cpf = v_cpf
WHERE correntista_id =v_id;
ELSE
SELECT 'O novos NOME e CPF devem ser informados!' AS Msg;
END IF;
END;
//
Já podemos, após compilarmos o procedimento de UPDATE, atualizarmos nosso registro na tabela de
correntistas. Depois de compilado com sucesso, já podemos chamar nosso procedimento de
atualização e passar os parâmetros para atualizar o registro que temos na tabela.
CALL correntistaUpdate(1, 'Wagner MySQL Bianchi', '123.123.111-11');
Note que este procedimento poderá facilmente ser adaptado em qualquer sistema que receba o
identificador do registro a ser atualizado em uma tabela qualquer de um banco de dados.
Para finalizarmos, faremos um procedimento para excluir registros, que é o mais trivial de todos, basta
mais uma vez enviarmos o identificador do registro como parâmetro e efetuarmos a exclusão após a
conferência como estamos fazendo nos outros procedimentos.
Note que, em um sistema, você poderá implementar um único procedimento para exclusão de registros,
que receberá alguns parâmetros como o identificador, o nome da tabela e o nome da coluna, mas,
nesse momento, nos atentaremos para o simples, nos próximos artigos sofisticaremos um pouco mais
nossos procedimentos.
DELIMITER //
CREATE PROCEDURE correntistaDelete(v_id INT)
BEGIN
IF ((v_id > 0) && (v_id != '')) THEN
DELETE FROM tbl_correntista WHERE correntista_id =v_id;
ELSE
SELECT 'O identifiador do registro não foi informado!' AS Msg;
END IF;
END;
//
Agora já podemos excluir o registro que inserimos e atualizamos! Segue a sintaxe para isso:
CALL correntistaDelete(1);
Functions
Da mesma forma com que são criados os “Stored Procedures” são criadas as “Stored Functions”.
O que se difere entre os “Stored Procedures” das “Stored Functions” é que as “Stored Functions” tem
por definição retornar valores.
Veja neste exemplo como se faz uma “Stored Function” que retorna a media aritmética de quatro
números inteiros.
DELIMITER $$
CREATE FUNCTION `func_retorna_media` (n1 INT, n2 INT, n3 INT, n4 INT) RETURNS INT
BEGIN
DECLARE media INT;
SET media = (n1+n2+n3+n4)/4;
RETURN media;
END $$
DELIMITER ;
Já a chamada à esta função também é diferente do “Stored Procedure”:
SELECT func_retorna_media(10,0,10,0)
Para que se exclua uma Function ou um procedure, basta usar o seguinte comando:
DROP FUNCTION IF EXISTS `nome_da_funcao`;
DROP PROCEDURE IF EXISTS `nome_do_procedimento`;
Views
Basicamente VIEW é uma tabela virtual gerada a partir do resultado de uma instrução SELECT.
Uma VIEW contém linhas e colunas, como se fosse uma tabela real, os campos na VIEW são campos
obtidos em uma tabela de um banco de dados. E na VIEW pode-se chamar funções e cláusulas SQL
como WHERE e JOIN.
Obs: O projeto e a estrutura do banco de dados original não será alterado pelas funções utilizadas em
sua confecção, pelo WHERE, ou JOINs implementados na VIEW.
Um dos objetivos ao se implementar VIEWs é evitar a constante manutenção de códigos SQL em
aplicações, ou seja, pode-se implementar um SELECT * padrão na aplicação (que estará extraindo
dados previamente filtrados da VIEW) e controlar o que e como será mostrado diretamente do servidor,
alterando as características da VIEW.
A instrução para a criação de uma VIEW:
CREATE VIEW <nome da view> AS SELECT colunas(s)
FROM <tabela> WHERE <condição>
Exemplo:
CREATE VIEW comedias AS
SELECT *
FROM filmes
WHERE tipo = 'Comédia';
E uma VIEW pode ser removida.
A instrução para a remoção de uma VIEW:
DROP VIEW <nome da view>
O uso de VIEWs pode facilitar muito o trabalho do desenvolvedor, principalmente na manutenção das
informações que serão expostas, pois alterando apenas a VIEW no banco todos os clientes passarão a
contemplar a alteração.
Segue mais um exemplo de criação de uma View, agora com uma consulta aninhada???
CREATE VIEW `candidatos`.`pessoas_cidades` AS
SELECT
pesCodigo, pesNome, cidNome as cidMora, nasNome as cidNasceu
FROM
pessoas join
cidade join
(select cidCodigo as nasCodigo, cidNome as nasNome from cidade) nasci
WHERE
pes_cidCodigoMora = cidCodigo and
pes_cidCodigoNascimento = nasCodigo;
No MYSQL até a versão 5.0 não é possível criar uma view utilizando consulta aninhada!