PL/SQL Developer Tutorial

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).

Figura 1. Logon na inicializaç�o do PL/SQL Developer.

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�.

O PL/SQL Developer � um aplicativo 32 bit para SO Windows (no m�nimo Windows 95 ou NT4) e necessita da vers�o 32 do SQL*Net, Net 8 ou Net 9. A vers�o de avaliaç�o (sem nenhuma restriç�o de funcionalidade mas funcional durante 30 dias) do PL/SQL Developer pode ser baixada gratuitamente no site do fabricante: http://www.allroundautomations.nl.

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).

Figura 2. A janela Create User. Facilidade na criaç�o de usu�rios.

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.

Figura 3. Visualizaç�o das instruç�es em SQL.

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).

Figura 4. A janela Explain Plan.

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)

 

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

Listagem 1. Relat�rio gerado pelo utilit�rio tkprof.

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.

Figura 5. O editor de definiç�es de tabelas.

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.

Figura 6. O editor de tarefas.

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.

Figura 7. Acesso ao Program Window.

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.

Figura 8. Exemplo de janela Program Window na manipulaç�o de uma procedure.

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.

Tipo de unidade de programa	extens�o
Function	.fnc
Procedure	.prc
Especificaç�o e corpo de Package	.pck
Especificaç�o de Package	.spr
Corpo de Package	.bdy
Especificaç�o e corpo de tipos (type)	.typ
Especificaç�o de tipos (type)	.tps
Corpo de tipos (type)	.tpb
Trigger	.trg
C�digo Java	.jsp

Tabela 1. Extens�es padronizadas.

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).

Figura 9. O Test Window em ambiente de depuraç�o.

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).

Figura 10. Identificaç�o da linha de c�digo que produziu um erro.

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).

Figura 11. SQL Window com o retorno das dez primeiras linhas de um SELECT.

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).

Figura 12. Express�o SQL com recurso de vari�vel de substituiç�o.

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).

Figura 13. O Command Window, interface similar ao SQL*Plus.

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).

Figura 14. Criaç�o de um novo projeto.

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).

Figura 15. Itens adicionados ao projeto.

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.

Artigo SQL Magazine 18 - 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.