Oracle PL/SQL: dominando packages

Preparando o ambiente - Apresentação

Olá, tudo bem? Meu nome é Victorino Vila e vou ser o instrutor de mais um treinamento na formação Consultas com Oracle Database. Este curso vai tratar sobre pacotes ou, em inglês, packages.

Victorino Vila é um homem de pele clara, olhos castanhos e cabelo e barba grisalhos. Está com uma camisa azul-escura. Ao fundo, uma parede azul-clara lisa sem decorações.

Conteúdo

Afinal, o que é um pacote? O pacote é um repositório onde podemos ter diversos objetos Oracle que podem ser acessados de forma externa por outras pessoas usuárias.

É como se fosse uma biblioteca, um local onde temos rotinas que são padronizadas e as pessoas usuárias podem acessar esses pacotes em suas rotinas locais no ambiente Oracle.

Nesse curso, vamos inicialmente entender o que é um pacote na parte teórica, criar o ambiente e começar a construção do entendimento do pacote falando sobre sinônimos e dependências.

Sinônimos são apelidos que damos a objetos Oracle, principalmente quando somos pessoas usuárias e acessamos um objeto que pertence a outra pessoa usuária. O sinônimo facilita esse acesso.

Além disso, vamos entender que todos os objetos Oracle possuem algo chamado dependência. Quando dizemos objeto Oracle, significa tabela, índice, procedure, função e assim por diante. A dependência é uma ligação entre eles.

Por exemplo, se temos uma procedure que faz um INSERT em uma tabela, essa tabela e a procedure são dependentes. Se mudar algo na tabela, isso pode afetar o funcionamento da procedure. Por isso, vamos aprender como identificar as dependências de um objeto.

Finalmente, vamos falar de pacotes. As dependências são muito importantes porque costumamos colocar dentro do pacote todas as rotinas que são dependentes de um determinado objeto. Assim, uma pessoa usuária externa ao local onde está a tabela pode manipulá-la sem precisar verificar a estrutura da tabela e fazer outras ações que a pessoa proprietária desse objeto não queira que outras pessoas façam.

Depois, vamos abordar sobrecargas e rotinas. Quando criamos as rotinas para serem executadas no pacote, elas podem ter os mesmos nomes, mas passando parâmetros diferentes. Isso é o que chamamos de sobrecarga.

E, muitas vezes, as rotinas que colocamos nos pacotes vieram de rotinas que chamamos de externas - que são as procedures e funções que criamos. Inclusive, trabalhamos muito com esse assunto nos cursos anteriores a esse. Com isso, podemos eliminar essas rotinas que são obsoletas após colocar esse código-fonte dentro dos pacotes.

Finalmente, vamos aprender que além de procedures e funções, podemos colocar em um pacote declarações de exceções e outra entidade chamada constantes que são valores fixos que depois podemos usar dentro do código-fonte das procedures ou funções.

Esse vai ser o conteúdo desse treinamento. Esperamos que vocês gostem. Vamos seguir em frente. Um abraço e até os próximos vídeos.

Preparando o ambiente - Instalando o Oracle

Se você está começando a formação Consultas com Oracle Database por este curso ou está seguindo a formação, mas está em uma máquina limpa para este treinamento, gostaríamos de lembrá-lo que é preciso instalar o Oracle Database e o software Oracle SQL Developer para poder seguir este curso normalmente.

Se você quer instalar esses softwares, acesse o curso "Introdução ao SQL com Oracle: manipule e consulte dados".

Dirija-se diretamente à segunda aula que se chama "Instalando Oracle e conhecendo as entidades de um banco de dados". Entre nessa aula e assistir três vídeos em sequência:

1. "Instalando o Oracle Express Edition";
2. "Instalando o Oracle Developer";
3. "Criando a conexão".

Após seguir esses três vídeos, você pode voltar e seguir em frente com os próximos vídeos desse curso sobre "Oracle PL/SQL: dominando packages".

Se você vem seguindo o curso normalmente e está usando a mesma máquina dos outros cursos, você não precisa fazer nada. É só seguir em frente.

Um abraço e até o próximo vídeo.

Preparando o ambiente - Preparando o ambiente

Vamos começar? Independente se você teve que instalar o Oracle Database e o Oracle SQL Developer em uma máquina limpa ou se você vem usando a mesma máquina e seguindo todos os cursos da formação Oracle, o primeiro passo é criar um novo ambiente para que todas as pessoas possam começar esse curso niveladas e analisando os mesmos dados.

Para isso, devemos fazer o download do arquivo Rotinas.zip também disponibilizado na atividade "Faça como eu fiz".

Após fazer o download, salvar na sua máquina e descompactar o arquivo, encontramos 4 scripts:

• 01_Criar_Usuario.sql
• 02_Criar_Tabelas.sql
• 03_Incluir_Dados_Tabelas.sql
• 04_Criar_Ambiente_Curso.sql

Vamos executar esses scripts em uma determinada ordem fazendo diferentes conexões.

Criação de usuários

Depois que baixamos e descompactamos o arquivo, vamos entrar no Oracle SQL Developer e esperar a aplicação carregar.

O primeiro passo que vamos fazer nesse ambiente é criar uma área de script usando aquela conexão que acessa o usuário system, um usuário master que criamos após a instalação do Oracle. Na máquina do instrutor, essa conexão se chama "CONEXÃO MÁQUINA LOCAL". Mas, você pode ter colocado outro nome nessa conexão.

Para criar o novo script, clicamos na seta para baixo do botão "Planilha SQL" da barra de ferramentas (ou "Alt + F10") para listar todas as conexões válidas. Vamos escolher a conexão chamada "CONEXÃO MÁQUINA LOCAL".

Vamos ao diretório onde descompactamos o arquivo Rotinas.zip e abrimos o script 01_Criar_Usuarios.sql. Copiamos todos os comandos que estão dentro desse arquivo e os colamos no script vazio:

-- Criação do ambiente do curso

alter session set "_oracle_script"=true;

CREATE USER user_dev IDENTIFIED BY user_dev
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

GRANT connect, resource TO user_dev;
GRANT create public synonym TO user_dev;
GRANT create view TO user_dev;
GRANT EXECUTE ANY PROCEDURE TO user_dev;
GRANT CREATE ANY DIRECTORY TO user_dev;

CREATE USER user_app IDENTIFIED BY user_app
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

GRANT connect, resource TO user_app;

ALTER USER user_dev QUOTA UNLIMITED ON USERS;


ALTER USER user_app QUOTA UNLIMITED ON USERS;


-- Criar as conexões no Oracle SQL Developer dos usuarios: user_dev e user_app

São comandos de criação de dois usuários com os quais vamos trabalhar durante esse curso: user_app e user_dev que possuem privilégios diferentes dentro do ambiente.

O user_dev vai ser o "dono" da aplicação. É quem vai desenvolver as aplicações Oracle e disponibilizá-las para serem acessadas pelo user_app.

Agora, vamos selecionar todos os comandos e executá-los. Para isso, selecionamos todas as linhas e apertamos o botão "Executar Instrução" que possui ícone de play no canto superior esquerdo do script (ou "Ctrl + Enter").

Saída do Script:

• User USER_DEV criado.
• Grant bem-sucedido.
• Grant bem-sucedido.
• Grant bem-sucedido.
• Grant bem-sucedido.
• Grant bem-sucedido.
• User USER_APP criado.
• Grant bem-sucedido.
• User USER_DEV alterado.
• User USER_APP alterado.

Pronto, criamos ambos usuários.

Criação de conexões

Agora o que vamos fazer é criar duas conexões para acessar o ambiente Oracle, usando esses dois usuários.

Para isso, no painel "Conexões" à esquerda, vamos clicar nesse botão "Conexões" que possui um símbolo de "+" para criar uma conexão nova.

A primeira conexão que vou criar é a “user_dev” que é o usuário todo da aplicação.

Na janela "Novo/Selecionar Conexão do Banco de Dados" que se abre, vamos criar a conexão para o usuário dono da aplicação:

• Nome da nova conexão: user_dev
• Nome do usuário: user_dev
• Senha: user_dev

O nome da conexão, do usuário e a senha serão user_dev em minúsculas. Clicamos no botão "Testar" do canto inferior direito e, consequentemente, temos a seguinte mensagem:

Status: Com Sucesso

Em seguida, apertamos o botão "Conectar". Com isso, temos agora mais uma conexão no ambiente que é a do usuário user_dev.

Vamos repetir o mesmo processo para o user_app, clicando no botão "Conexões" com ícone de “+” no painel "Conexões".

Na janela que se abre, colocamos o mesmo nome para a conexão, usuário e senha:

• Nome da nova conexão: user_app
• Nome do usuário: user_app
• Senha: user_app

Testamos ao clicar no botão "Teste".

Status: Com Sucesso

E conectamos no botão "Conectar". Pronto, agora temos as duas conexões.

Criação base de dados e rotinas

Agora, vamos criar a base de dados e as rotinas dentro do ambiente do user_dev já que é o dono da aplicação.

Então, clicamos na seta para baixo do botão "Planilha SQL" da barra de ferramentas e especificamos a conexão "user_dev".

Vamos voltar aos arquivos descompactados e abrir o bloco de notas com o script 02_Criar_Tabelas.sql. Copiamos todos os comandos e colamos no script vazio no Orace SQl Developer.

--Conectar-se ao usuario: user_dev e criar as tabelas

CREATE TABLE segmercado (id NUMBER(5),                
          descricao VARCHAR2(100));

ALTER TABLE Segmercado ADD CONSTRAINT         
    segmercado_id_pk PRIMARY KEY(ID);

CREATE TABLE cliente
    ( ID NUMBER(5),
      razao_social VARCHAR2(100),
      CNPJ VARCHAR2(20),
      segmercado_id NUMBER(5),
      data_inclusao DATE,
      faturamento_previsto NUMBER(10,2),
      categoria VARCHAR2(20));

ALTER TABLE cliente ADD CONSTRAINT cliente_id_pk 
    PRIMARY KEY(ID);

ALTER TABLE cliente 
    ADD CONSTRAINT cliente_segmercado_fk 
    FOREIGN KEY(segmercado_id) 
    REFERENCES segmercado(id);

Vamos selecionar todas as linhas e executar.

Saída do Script:

• Table SEGMERCADO criado.
• Table SEGMERCADO alterado.
• Table CLIENTE criado.
• Table CLIENTE alterado.
• Table CLIENTE criado.

Pois bem, nesse momento criamos duas tabelas. A tabela chamada "SEGMERCADO" tem a lista de segmentos de mercados, enquanto a tabela "CLIENTE" tem a lista de clientes associados a segmentos de mercados.

Vamos continuar. Criamos um novo script, novamente usando a conexão "user_dev".

Voltamos para o diretório onde temos os scripts descompactados e abrimos com o bloco de notas o arquivo 03_Incluir_Dados_Tabelas.sql. Vamos selecionar todas as linhas, voltar para o ambiente de trabalho e colar os comandos.

BEGIN

   -- Incluir segmentos de mercado
   insert into segmercado values (1,'VAREJISTA');
   insert into segmercado values (2,'ATACADISTA');
   insert into segmercado values (3,'FARMACEUTICO');
   insert into segmercado values (4,'INDUSTRIAL');
   insert into segmercado values (5,'AGROPECUARIA');


   -- incluir clientes
   insert into cliente values (1 ,'SUPERMERCADO XYZ','12/345',5,sysdate,150000, 'GRANDE' );
   insert into cliente values (2 ,'SUPERMERCADO IJK','67/890',1,sysdate,90000, 'MEDIO GRANDE' );
   insert into cliente values (3 ,'SUPERMERCADO IJK','89/012',3,sysdate,80000, 'MEDIO GRANDE' );
   insert into cliente values (4 ,'FARMACIA AXZ','12/378',3, sysdate,80000,  'MEDIO GRANDE' );

   COMMIT;

END;
/

O que vamos fazer é criar conteúdos dentro das tabelas "SEGMERCADO" e "CLIENTE". Por issom vamos selecionar todas as linhas e executar o script.

Saída do Script:

Procedimento PL/SQL concluído com sucesso.

Para poder garantir que o procedimento realmente deu certo, vamos consultar os registros da tabela "SEGMERCADO" para conferir se os segmentos de mercado foram criados.

SELECT * FROM SEGMERCADO;

Resultado da Consulta:

Os 5 segmentos de mercado estão registrados.

Também vamos consultar os registros de "CLIENTE" para saber se os dados de cliente foram criados.

SELECT * FROM CLIENTE;

Resultado da Consulta:

Perfeito.

Vamos criar mais um script também acessando a conexão do usuário "user_dev". Em seguida, vamos voltar para o diretório descompactado e abrir o script 04_Criar_Ambiente_Curso.sql. Vamos selecionar todas as linhas e colamos no script vazio.

-- No Oracle SQL Developer ir para: Menu Superior -> Ferramentas -> Preferencias
-- Banco de Dados -> Planilha 
-- Em SELECIONAR CAMINHO PADRÃO PARA PROCURA DE SCRIPTS colocar o local do script do curso. 

CREATE OR REPLACE FUNCTION OBTER_CATEGORIA_CLIENTE
    (p_faturamento_previsto IN cliente.faturamento_previsto%type)
    RETURN cliente.categoria%type
IS
BEGIN
    IF p_faturamento_previsto <= 10000 THEN 
       RETURN 'PEQUENO';
    ELSIF p_faturamento_previsto <= 50000 THEN
       RETURN 'MEDIO';
    ELSIF p_faturamento_previsto <= 100000  THEN
       RETURN 'MEDIO GRANDE';
    ELSE
       RETURN 'GRANDE';
    END IF;   
END;
/

CREATE OR REPLACE FUNCTION OBTER_DESCRICAO_SEGMENTO 
    (p_id in segmercado.id%type)
    RETURN segmercado.descricao%type
IS
    v_descricao segmercado.descricao%type;
BEGIN
    SELECT descricao INTO v_descricao
       FROM segmercado
       WHERE id = p_id;
    RETURN v_descricao;
EXCEPTION
        WHEN no_data_found then
            raise_application_error(-20002,'Segmento de Mercado    Inexistente');
END;
/

CREATE OR REPLACE FUNCTION VERIFICA_SEGMENTO_MERCADO
    (p_id in segmercado.id%type)
        RETURN boolean
IS
    v_dummy number(1);
BEGIN
    SELECT 1 into v_dummy
        FROM segmercado
        WHERE id = p_id;
    RETURN true;
EXCEPTION   
    WHEN no_data_found then
        RETURN false;
END;
/


CREATE OR REPLACE PROCEDURE FORMAT_CNPJ 
            (p_cnpj IN OUT varchar2)
IS
BEGIN
    p_cnpj := substr(p_cnpj,1,2) ||'/'|| substr(p_cnpj,3);
END;
/

CREATE OR REPLACE PROCEDURE ATUALIZAR_CLI_SEG_MERCADO
    (p_id cliente.id%type,
     p_segmercado_id cliente.segmercado_id%type)
IS
        e_fk exception;
        pragma exception_init(e_fk, -2291);
        e_no_update exception;
BEGIN
    UPDATE cliente
        SET segmercado_id = p_segmercado_id
        WHERE id = p_id;
    IF SQL%NOTFOUND then
        RAISE e_no_update;
    END IF;
    COMMIT;
EXCEPTION
    WHEN e_fk then
        RAISE_APPLICATION_ERROR (-20001,'Segmento de Mercado Inexistente');
    WHEN e_no_update then
       RAISE_APPLICATION_ERROR (-20002,'Cliente Inexistente');
END;
/

CREATE OR REPLACE PROCEDURE ATUALIZAR_FATURAMENTO_PREVISTO
    (p_id in cliente.id%type,
     p_faturamento_previsto in cliente.faturamento_previsto%type)
IS 
    v_categoria cliente.categoria%type;
    e_error_id exception;
BEGIN
    v_categoria := obter_categoria_cliente(p_faturamento_previsto);
    UPDATE cliente
        SET categoria = v_categoria,
            faturamento_previsto = p_faturamento_previsto
        WHERE id = p_id;
    IF SQL%NOTFOUND THEN
        RAISE e_error_id;
    END IF;
    COMMIT;
EXCEPTION
    WHEN e_error_id then
        raise_application_error(-20010,'Cliente inexistente');
END;
/

CREATE OR REPLACE PROCEDURE EXCLUIR_CLIENTE
    (p_id in cliente.id%type)
IS 
    e_error_id exception;
BEGIN
    DELETE FROM cliente
        WHERE id = p_id;
    IF SQL%NOTFOUND THEN
        RAISE e_error_id;
    END IF;
    COMMIT;
EXCEPTION
    WHEN e_error_id then
        raise_application_error(-20010,'Cliente inexistente');
END;
/

CREATE OR REPLACE PROCEDURE INCLUIR_CLIENTE 
   (p_id in cliente.id%type,
    p_razao_social in cliente.razao_social%type,
    p_CNPJ cliente.CNPJ%type ,
    p_segmercado_id cliente.segmercado_id%type,
    p_faturamento_previsto cliente.faturamento_previsto%type)
IS
    v_categoria cliente.categoria%type;
    v_CNPJ cliente.cnpj%type := p_CNPJ;
    v_codigo_erro number(5);
    v_mensagem_erro varchar2(200);
    v_dummy number;
    v_verifica_segmento boolean;
    e_segmento exception;
BEGIN
    v_verifica_segmento :=     verifica_segmento_mercado(p_segmercado_id);
    IF v_verifica_segmento = false THEN
        RAISE e_segmento;
    END IF;
    v_categoria := obter_categoria_cliente(p_faturamento_previsto);
    format_cnpj (v_cnpj);
    INSERT INTO cliente 
          VALUES (p_id, UPPER(p_razao_social), v_CNPJ, p_segmercado_id
                  ,SYSDATE, p_faturamento_previsto, v_categoria);
   COMMIT;   
EXCEPTION
    WHEN dup_val_on_index then
        raise_application_error(-20010,'Cliente já cadastrado');
    WHEN e_segmento then
        raise_application_error (-20011,'Segmento de mercado inexistente');
    WHEN OTHERS then
        v_codigo_erro := sqlcode;
        v_mensagem_erro := sqlerrm;
        raise_application_error (-20000,to_char(v_codigo_erro)||v_mensagem_erro);
END;
/

CREATE OR REPLACE PROCEDURE INCLUIR_SEGMERCADO
    (p_id in segmercado.id%type,
     p_descricao in segmercado.descricao%type)
IS
 BEGIN
    INSERT into segmercado 
       values(p_id, UPPER(p_descricao));
    COMMIT;
EXCEPTION
    WHEN dup_val_on_index THEN
        raise_application_error(-20001,'Segmento de Mercado já Cadastrado');
END;
/



CREATE OR REPLACE FUNCTION VERIFICA_SEGMENTO_MERCADO
    (p_id in segmercado.id%type)
        RETURN boolean
IS
    v_dummy number(1);
BEGIN
    SELECT 1 into v_dummy
        FROM segmercado
        WHERE id = p_id;
    RETURN true;
EXCEPTION   
    WHEN no_data_found then
        RETURN false;
END;
/

Nesse ponto, vamos criar uma série de procedures e funções que serão usadas durante o treinamento. Para isso, selecionamos todo o script e executamos.

Saída do Script:

• Function OBTER_CATEGORIA_CLIENTE compilado
• Function OBTER_DESCRICAO_SEGMENTO compilado
• Function VERIFICA_SEGMENTO_MERCADO compilado
• Procedure FORMAT_CNPJ compilado
• Procedure ATUALIZAR_CLI_SEG_MERCADO compilado
• Procedure ATUALIZAR_FATURAMENTO_PREVISTO compilado
• Procedure EXCLUIR_CLIENTE compilado
• Procedure INCLUIR_CLIENTE compilado
• Procedure INCLUIR_SEGMERCADO compilado
• Function VERIFICA_SEGMENTO_MERCADO compilado

Para garantir, primeiro verificamos a saída para saber se não houve nenhum erro na geração do script.

No painel "Conexões", vamos expandir "user_dev" para abrir os objetos. Conferimos as procedures, funções e tabelas salvas associadas ao "user_dev".

• 2 tabelas salvas: CLIENTE e SEGMERCADO.
• 6 procedures salvas: ATUALIZAR_CLI_SEG_MERCADO, ATUALIZAR_FATURAMENTO_PREVISTO, EXCLUIR_CLIENTE, FORMAT_CNPJ, INCLUIR_CLIENTE e INCLUIR_SEGMERCADO;
• 3 funções salvas: OBTER_CATEGORIA_CLIENTE, OBTER_DESCRICAO_SEGMENTO e VERIFICA_SEGMENTO_MERCADO;

O usuário "user_app" não tem nada associado a ele. Nenhuma função, nenhuma procedure, nenhuma tabela. Inclusive, podemos até verificá-lo ao expandir "user_app".

Agora estamos preparados para fazer o curso e seguir com os exemplos práticos. Grande abraço e até o próximo vídeo.

Sobre o curso Oracle PL/SQL: dominando packages

O curso Oracle PL/SQL: dominando packages possui 193 minutos de vídeos, em um total de 67 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Preparando o ambiente
• Sinônimos e dependências
• Pacotes
• Sobrecargas e rotinas internas
• Exceções e constantes