Artigo SQL Magazine 18 - PL/SQL Developer

Guia do artigo:

• O que é O PL/SQL Developer?
• Administrando o BD Oracle
• Manipulação de objetos não PL/SQL
• Desenvolvendo programas com o PL/SQL Developer
• Criando unidades de programa (program units)
• Testando e depurando o programa
• Executando expressões SQL
• Utilizando variáveis de substituição
• A janela de comandos
• Relatórios
• Projetos
• Conclusões

O que é O PL/SQL Developer?

O PL/SQL Developer é uma IDE (Integrated Development Environment) para desenvolvimento de programas (functions, procedures, triggers, packages) armazenados em um BD Oracle. Através dele podemos construir todo o módulo executado no servidor de uma aplicação cliente-servidor. Enfim, é uma ferramenta gráfica que auxilia muito nas tarefas de desenvolvimento e também de administração de um BD Oracle.

O objetivo deste artigo não é o de se tornar um tutorial sobre a ferramenta e sim oferecer uma visão geral do que ela proporciona.

O PL/SQL Developer oferece uma interface amigável e extremamente produtiva para as tarefas de edição, compilação, correção, testes, limpeza (debugging), otimização e consulta de programas para sua aplicação cliente-servidor em Oracle, além de outras funcionalidades como execução de scripts SQL, criação e modificação de definições de tabelas (através de linguagem DDL) e relatórios.

A primeira coisa que será solicitada quando iniciado o PL/SQL Developer é a conexão ao BD (Figura 1).

Deveremos informar o usuário, senha, qual a instância a se conectar e o modo de conexão, que poderá ser “Normal”, “SYSDBA” ou “SYSOPER”.

Administrando o BD Oracle

Criando usuários

Uma das primeiras tarefas de um DBA é a criação de usuários e esquemas. Através da interface do PL/SQL Developer, a tarefa é bastante simplificada; não sendo necessário memorizar todas as definições do comando SQL para efetuar a atividade (Figura 2).

Uma das grandes vantagens do uso da ferramenta é que podemos, por exemplo, criar o usuário atribuindo-lhe os devidos privilégios e também definir cotas nas tablespaces que ele poderá acessar e criar objetos.

Na guia “General”, deve-se definir o nome e senha do usuário. É possível também definir quais são as tablespaces padrão e temporária (caso não seja informado, será utilizada a tablespace SYSTEM).

A guia “Object Privileges” permite a visualização, concessão ou modificação de privilégios a objetos (select, insert, update, delete, execute, alter, etc) ao usuário. As opções são: deixar em branco, para que o usuário não tenha tal privilégio, “Yes”, para que haja o privilégio ou “Grantable”, para que o usuário, além de possuir tal privilégio, possa concedê-lo a outros usuários.

Pode-se ainda conceder privilégios a determinados papéis (Roles) como connect, dba, resource, exp_full_database, etc. definindo também como “Grantable”, se necessário.

Em “System Privileges” é possível conceder privilégios de sistema como create any table, create session, etc. A opção “Grantable” também está presente.

Finalmente, podemos atribuir cotas (Quotas) (em bytes, Kb ou Mb) para o usuário em cada tablespace (isso significa dizer qual o máximo que o usuário poderá alocar em uma tablespace) ou definir esta cota como ilimitada.

Há ainda a opção de visualizar os comandos SQL gerados, clicando no botão View SQL (Figura 3), e gravá-los em um arquivo para posterior utilização.

Otimização

Para otimizar expressões SQL, podemos fazer uso do utilitário Explain Plan para visualizar o trajeto de execução da expressão. Podemos ainda visualizar as estatísticas sobre a execução de uma expressão SQL, configurando inclusive quais os dados estatísticos queremos visualizar, incluindo tempo de CPU, leituras físicas e lógicas, escritas físicas entre outros.

Para usar este utilitário, basta pressionar o botão New na barra de ferramentas e selecionar a opção Explain Plan Window. Será aberta uma janela vazia onde apenas é necessário digitar a expressão SQL a ser analisada na metade superior da janela e então pressionar o botão Execute (tecla de atalho F8) para visualizar a estratégia de acesso na metade inferior da janela (Figura 4).

Há também a opção de alterar o objetivo do otimizador, selecionando em Optimizer goal entre as opções:

• Choose: o otimizador fará a escolha entre o acesso baseado em custo ou baseado em regra, dependendo da existência ou não de estatísticas disponíveis. É a opção default;
• Rule: o otimizador utilizará o acesso baseado em regra, independentemente da existência ou não de estatísticas disponíveis;
• First rows: é utilizada uma mistura de acesso baseado em custo e heurística (conjunto de métodos que auxiliam na resolução de problemas) para encontrar o melhor plano de execução para trazer um número pequeno de linhas;
• All rows: será utilizado o acesso baseado em custo, independentemente da existência ou não de estatísticas disponíveis e otimiza com o objetivo de melhorar a taxa de transferência (throughput), ou seja, a menor utilização de recursos para executar uma instrução SQL completa.

A janela Explain Plan oferece ainda a possibilidade de navegação através da consulta pela ordem de operação, ou seja, é possível analisar cada um dos comandos utilizados pelo otimizador do Oracle para alcançar a informação desejada. Na Figura 4, está em destaque o comando “INDEX UNIQUE SCAN” e na barra de status um comentário da operação que está sendo executada. No caso está sendo selecionado o valor único através de um índice único (PK_CLIENTE, a chave primária), demonstrando uma boa estratégia de acesso adotada pelo otimizador.

Para questões de otimização, temos também o utilitário tkprof que é utilizado para obter informações de uso de recursos sobre todas as execuções de expressões SQL através da habilitação do SQL Trace. Em uma SQL Window ou Test Window em uso, é possível habilitar o SQL Trace através do menu Session -> SQL Trace. Após isso, todo processo executado no servidor vindo do SQL Window ou Test Window serão armazenados em um arquivo de trace no servidor de BD.

A visualização do trace é feita através da utilização do utilitário tkprof no servidor de BD. O utilitário irá gerar um relatório (Listagem 1) contendo informações úteis para análise de performance de expressões SQL ou blocos PL/SQL.

TKPROF: Release 7.2.2.3.1 - Production on Fri Sep 26 14:59:08 1997

 

Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.

 

Trace file: ora07087.trc

Sort options: default

 

********************************************************************* 

count    = number of times OCI procedure was executed

cpu      = cpu time in seconds executing

elapsed  = elapsed time in seconds executing

disk     = number of physical reads of buffers from disk

query    = number of buffers gotten for consistent read

 

current  = number of buffers gotten in current mode (usually for update)

rows     = number of rows processed by the fetch or execute call

********************************************************************* 

begin

  :deptname := employee.deptname(:empno);

end;

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

 

Execute      1      0.01       0.01          0          0          0           1

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.01       0.01          0          0          0           1

 

Misses in library cache during parse: 0

Optimizer hint: CHOOSE

Parsing user id: 16 

********************************************************************* 

SELECT DEPT.DNAME  

FROM

 DEPT,EMP  WHERE EMP.EMPNO = :b1  AND DEPT.DEPTNO = EMP.DEPTNO

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           5

Fetch        1      0.06       0.06          4          4          0           1

 

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.06       0.06          4          4          0           6

 

Misses in library cache during parse: 0

Optimizer hint: CHOOSE

Parsing user id: 16     (recursive depth: 1)

 

*********************************************************************

O relatório gerado pelo tkprof informa os valores encontrados na análise de uma instrução que foi passada para o banco de dados executar. A coleta de informações é feita para três momentos diferentes da execução da instrução:

• Parse: é a “compilação” da instrução caso a mesma não esteja ainda em memória devido a uma execução anterior. Caso a instrução já tenha sido utilizada e permaneça em memória, o Oracle reutiliza o comando não necessitando fazer o parse da instrução;
• Execute: é a execução da instrução propriamente dita;
• Fetch: é o momento em que a informação é retornada.

Os dados coletados em cada passo da execução (parse, execute e fetch) são referentes a:

• Count: número de vezes que o procedimento foi executado;
• CPU: tempo de utilização de processamento (cpu) em segundos;
• Elapsed: tempo total de execução em segundos;
• Disk: número de leituras físicas executadas em disco;
• Query: número de informações em memória para leitura consistente;
• Corrent: número de informações em memória em modo corrente (normalmente em operações de update);
• Rows: número de linhas processadas em fetch ou execute.

Manipulação de objetos não PL/SQL

O PL/SQL Developer permite manipular objetos não PL/SQL com extrema facilidade. Dentre estes objetos, podemos destacar a criação/manipulação de:

• Usuários (visto anteriormente – Figura 2);
• Tabelas (Figura 5).

Composto por seis páginas, o editor de tabelas permite definir/alterar as informações sobre armazenamento dos dados, cluster, duração e organização (através da guia General). Na guia Columns é possível visualizar, adicionar, excluir, mover ou modificar colunas da tabela. Para manipular as chaves primária, única ou estrangeira da tabela, acesse a guia Keys.

A página Checks permite manipular as restrições de verificação (check constraints), que são as restrições criadas para garantir regras de negócios. Podemos ainda manipular os índices existentes na tabela. Para isso, acesse a página Indexes. Finalmente, através da página Privileges, é possível manipular a concessão ou negação de privilégios sobre o objeto a outros usuários:

• Seqüências (sequences): através do editor de seqüências, pode-se efetuar a definição de informações como valor mínimo e máximo que a seqüência irá adotar, qual o valor inicial de trabalho, incremento, etc.;
• Podemos ainda manipular sinônimos (synonyms), bibliotecas, diretórios, roles, profiles, database links;
• Tarefas (Jobs) (Figura 6). Podemos agendar atividades que serão executadas automaticamente. Para isso devemos criar jobs. A criação dos jobs através do PL/SQL Developer é feita na janela Jobs, que internamente está invocando o pacote (package) DBMS_JOB, que acompanha o Oracle 9i.

Desenvolvendo programas com o PL/SQL Developer

Em um BD Oracle, podemos criar cinco diferentes tipos de unidades de programa armazenadas no próprio BD (stored programs): functions (funções), procedures (procedimentos), packages (pacotes), types (tipos) e triggers (gatilhos).

Criando unidades de programa (program units)

Para criar um novo programa, basta selecionar o tipo de unidade que se deseja criar através do menu File -> New -> Program Window (Figura 7) e selecionar o tipo de unidade de programa que se deseja criar.

Nos será apresentado um assistente onde definiremos as opções solicitadas para criação da unidade de programa, tais como: nome da unidade, parâmetros (in, out ou in/out), valores de retorno (no caso das functions), etc. Após definidas as opções iniciais, será exibida a janela Program Window (Figura 8), em que várias partes do código, como o famoso “CREATE OR REPLACE PROCEDURE” já estarão inseridos pelo assistente do PL/SQL Developer.

Na coluna da direita você encontrará o conteúdo do código. Já na coluna da esquerda da janela são mostrados as variáveis, constantes, tipos, exceções (exceptions), funções locais e procedures utilizadas na unidade de programa que está sendo desenvolvida. Esta funcionalidade se torna muito útil quando se programa um código longo, como um package body, por exemplo.

Existe ainda a opção de salvar o arquivo do código do programa, utilizando extensões padronizadas de acordo com o tipo de unidade de programa. A Tabela 1 mostra as extensões padronizadas para as unidades.

As extensões padronizadas para os arquivos das unidades de programa são apenas uma sugestão fornecida pelo PL/SQL Developer e podem ser alteradas pelo desenvolvedor sem nenhum impacto na programação. Todos os arquivos de unidades de programa salvos através do PL/SQL Developer possuem um formato totalmente compatível com o Oracle SQL*Plus.

Testando e depurando o programa

Após o programa ter sido compilado com sucesso, precisaremos testá-lo. Para isto, basta clicar com botão direito do mouse sobre a unidade de programa, na janela de navegação à esquerda, e selecionar a opção Test. Será apresentada a janela Test Window com os parâmetros contidos no código para efetuar o teste. (Figura 9). Basta informar valores de teste para os parâmetros e iniciar o teste (através da barra de ferramentas do próprio Test Window ou pressionando a tecla F8).

O PL/SQL Developer possui ainda um depurador integrado ao Test Window que permite rastrear erros nas unidades de programa. Há uma barra de ferramentas no alto da janela com funções relativas à depuração.

Se for encontrado algum erro durante a execução da unidade de programa, será apresentada uma mensagem contendo qual o erro ocorrido e lhe será dado a opção de visualizar o código identificando a linha exata em que o erro ocorreu (Figura 10).

Executando expressões SQL

Freqüentemente, durante o desenvolvimento de programas, é necessário executar algumas expressões SQL para visualizar ou mesmo atualizar dados em tabelas. O PL/SQL Developer oferece uma interface para este tipo de tarefa através do SQL Window. Para acessar o SQL Window, vá em File -> New -> SQL Window. Para executar a expressão SQL informada pressione o botão Execute (tecla de atalho F8) (Figura 11).

Utilizando variáveis de substituição

Pode-se utilizar variáveis de substituição na expressão SQL para permitir uma entrada de dados do usuário quando a expressão é executada. Para utilizar este recurso, basta acrescentar o nome da variável precedida do caractere “&”. No momento da execução da expressão, será apresentada uma janela de entrada de dados para que seja inserido o valor que será utilizado como substituição na expressão SQL (Figura 12).

A janela de comandos

O PL/SQL Developer oferece ainda uma interface de linha de comando extremamente parecida com o SQL*Plus, que possibilita a execução de scripts SQL além das expressões SQL em geral. Para acessar a Command Window, selecione File -> New -> Command Window (Figura 13).

Relatórios

Existe a opção de geração de relatórios sobre os dados da aplicação que estiver sendo desenvolvida ou até mesmo sobre as informações do dicionário de dados. Esta segunda, bastante útil para os DBAs. Estão disponíveis vários relatórios padronizados de acordo com a característica das informações que se pretende obter.

Para ter acesso aos relatórios, vá em Reports e escolha a seção de relatório necessária. São quatro as opções de informações para os relatórios: DBA, Objects, PL SQL e User.

• DBA: refere-se a relatórios que buscam informações do dicionário de dados. Temos as seguintes opções:
  • Initialization Parameters: indica as informações dos parâmetros de inicialização do BD;
  • NLS Database Parameters: informações sobre os parâmetros de suporte a língua nativa configurados no BD;
  • Role Privileges: relatório sobre os privilégios concedidos a um papel (role);
  • Roles: relatório de todos os papeis (roles) criadas no BD;
  • Rollback Segments: traz as informações de todos os segmentos de rollback configurados no sistema;
  • Server Components: mostra os componentes do SGBD tais como: versão do BD, do PL/SQL, etc.;
  • System Privileges: informa os privilégios de sistema concedidos a todos os usuários ou a um usuário específico;
  • Tablespaces: relatório de informações da configuração das tablespaces;
  • Users: mostra as informações dos usuários do BD;
• Objects: refere-se aos objetos criados no BD e estão subdivididos em:
  • All Objects: prove informações sobre todos os objetos do BD ou todos os objetos de um determinado usuário;
  • Indexes: informações sobre os índices de uma tabela ou de um esquema completo;
  • Sequences: exibe as configurações das seqüências ou de uma determinada seqüência no esquema;
  • Synonyms: mostra as informações dos sinônimos presentes no sistema;
  • Tables: informações sobre as tabelas do esquema;
  • Triggers: exibe as informações dos gatilhos, incluindo a descrição e o corpo do gatilho;
  • Types: informa todos os tipos criados no esquema;
  • Views: exibe as visões criadas no esquema;
• PL SQL: oferece somente a opção de Compilation errors para visualizar os erros de compilação dos blocos de programa PL/SQL;
• User: apresenta os relatórios referentes às informações do usuário através das seguintes opções:
  • Granted Roles: exibe os papeis concedidas ao usuário;
  • Granted System Privileges: mostra os privilégios de sistema concedidos ao usuário;
  • NLS Session Parameters: indica o valor dos parâmetros de suporte à língua nativa configurados para a sessão;
  • Object Privileges Made: indica os privilégios de objeto que o usuário concedeu a outros;
  • Object Privileges Received: exibe os privilégios de objeto recebidos por outros usuários.

Os relatórios ainda oferecem a opção de visualização da expressão SQL que dá origem aos resultados obtidos pelo relatório, e se o acesso à expressão foi liberada (em alguns casos, o acesso está travado), é possível ainda editá-la, alterando conforme necessidade. Por fim, temos que a exibição é feita em formato HTML e existe a possibilidade de gravação do arquivo HTML e até mesmo exportação do relatório para formatos CVS, TSV ou XML e Excel.

Projetos

Para uma melhor organização dos trabalhos, o PL/SQL Developer permite a organização dos arquivos manipulados através de projetos. Um projeto nada mais é do que uma coleção de códigos fonte, objetos de BD, notas e opções. Desta forma, pode-se trabalhar apenas com um escopo de itens específicos ao invés de trabalhar com um BD ou esquema completos.

O único cuidado que devemos ter é o de garantir que a opção Use Projects esteja selecionada nas preferências do PL/SQL Developer. Vá em Tools -> Preferences -> Options e selecione a opção Use Projects caso ainda não esteja selecionada.

Através do menu Project -> New, preencha as opções de criação do novo projeto (Figura 14).

Salve o projeto (por padrão, a extensão será .prj) e a partir deste instante, todas as vezes em que este projeto for aberto, as definições, objetos, códigos, etc. a ele associados, estarão disponíveis organizadamente.

Agora é o momento de adicionar as unidades de programa, objetos, etc. ao seu projeto. Para adicionar uma unidade de programa previamente salva, basta abri-la e selecionar Project -> Add to Project para que esta unidade de programa seja incluída na lista de itens do projeto. Uma outra maneira de adicionar unidades de programa ou objetos é através do navegador à esquerda da área de trabalho do PL/SQL Developer. Neste navegador estarão disponíveis apenas as unidades de programa ou objetos que já tenham sido criadas no BD. Abra a janela Project Items (Project -> Project Items...) e depois selecione a unidade de programa ou objeto no navegador e, pressionando o botão direito do mouse, selecione a opção Add to project (Figura 15).

Finalmente, basta compilar o projeto. Para isso, selecione a opção Build ou Make no menu Project. A função Build irá simplesmente compilar todos os componentes do projeto que estiverem habilitados para compilação. Os itens serão compilados na ordem em que aparecem na lista de itens do projeto (Figura 15). A função Make somente compilará os objetos que sofreram alguma alteração desde a última compilação.

Conclusões

Desenvolver aplicações ou executar administração em um BD Oracle não é uma tarefa simples, requer um alto grau de conhecimento do BD e também da linguagem procedural PL/SQL além, é claro, da estrutura de expressões SQL. Considerando estas tarefas, ferramentas gráficas que podem nos trazer facilidades e agilidade no processo de desenvolvimento e administração do BD são muito bem vindas.

O PL/SQL Developer é uma destas ferramentas que nos permite um alto grau de produtividade e confiabilidade nas informações e resultados.

Por fim, um conselho que me permito dar é o de não se “viciar” nas ferramentas gráficas e sim usá-las apenas como um apoio às tarefas. Não se esqueça nunca das expressões SQL para chegar nesta ou naquela informação, pois não é nem um pouco improvável que em uma consultoria você possa ter acesso apenas a um terminal de fósforo verde piscando em sua frente ou mesmo, sem chegar a extremos, um BD instalado sobre um sistema operacional Linux, por exemplo, sem interface gráfica instalada para poupar recursos de memória. Lembre-se, sempre teremos o velho e bom SQL*Plus em qualquer ambiente.