Resumão de PL/SQL, parte 2: criar e rodar código PL/SQL

Como a PL/SQL é uma embedded language, ela não roda por si só, precisa ser executada dentro de um ambiente Oracle (Oracle Database, SQL*Plus, Oracle Forms, etc.). Em geral o código PL/SQL precisa estar carregado no banco de dados Oracle para ser compilado e executado.

Para escrever código PL/SQL você pode usar uma IDE gráfica ou rodar scripts na linha de comando. Existem várias escolhas para a IDE e poucas para a linha de comando:

Eu uso o Oracle SQL Developer que me permite, além da IDE, utilizar comandos do SQL*Plus “simulando” o ambiente de linha de comandos.

 Algumas dicas de SQL*Plus:

Toda vez que você conecta no SQL*Plus, ele roda um arquivo de startup onde você configurar várias preferências. Na verdade, ele checa a existência de 3 arquivos:

  1. $ORACLE_HOME/sqlplus/admin/glogin.sql: utilizado para executar comandos comuns para todos os usuários;
  2. <diretório de trabalho>/login.sql: utilizado para executar comandos para seu usário;
  3. login.sql encontrado via SQLPATH: utilizado para executar comandos para seu usuário.

Crie um arquivo de startup para o SQL*Plus, com as seguintes configurações:

SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
SET ECHO ON
SET LINESIZE 150
SET AUTOCOMMIT OFF
SET EXITCOMMIT OFF
SET PAGESIZE 999
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "
DEFINE _EDITOR = /bin/vi

Atenção: sempre que você se disconecta normalmente do SQL*Plus, se houver transações pendentes, ele dá um COMMIT. Cuidado com isso! Não se esqueça de dar commit ou rollback por conta própria!

Para avisar ao SQL*Plus que você já acabou de digitar seu PL/SQL, deve incluir uma barra na próxima linha, sem mais nenhum outro caractere). Essa barra diz ao SQL*Plus para executar o statement mais recente digitado.

Para rodar um script que está em seu diretório de trabalho (ou que possa ser encontrado pela variávevl SQLPATH) use o comando @ ou o START. Se esse script chamar outros scripts, a chamada deve ser feita com o comando @@ para que o SQL*Plus entenda que a chamada está sendo feita a partir do diretório onde o script pai foi rodado.

Existem 2 tipos de variáveis no SQL*Plus: DEFINE e BIND.

Uma variável define é nada mais que a definição de um valor através do comando DEFINE, por exemplo:

DEFINE x = 'teste';
DEFINE y = 50;

Para usar uma variável define, utilize o operador &:

SELECT '&x' FROM DUAL;
SELECT &y FROM DUAL;

Para usar uma variável bind, ela tem que ser declarada primeiro, e depois um valor é atribuído à ela, por exemplo:

VARIABLE x VARCHAR2(15);
VARIABLE Y NUMBER;

Note que agora há 2 variáveis “x” e 2 variáveis “y”, uma define e uma bind. Execute a seqüência abaixo para entender a diferença:

define x = 'teste define';
define y = 10;

SELECT '&x' FROM dual;
SELECT &y FROM dual;

variable x VARCHAR2(15);
variable y NUMBER;

print :x;
print :y;

BEGIN
 :x := 'teste bind';
 :y := 2;
END;

print :x;
print :y;

SELECT '&x' "x define",
       :x   "x bind",
       &y   "y define",
       :y   "y bind"
FROM dual;

Para salvar o output do SQL*Plus em um arquivo, use o comando SPOOL:

SET SPOOL /path/arquivo.txt
<comandos ...>
SPOOL OFF

Se estamos executando um script e queremos que o SQL*Plus pare se houver algum erro, devemos usar o comando (o rollback é opcional):

WHENEVER SQLERROR EXIT SQL.SQLCODE [ROLLBACK]

Se estamos criando algum PL/SQL e ocorreram erros de compilação, podemos usar o comando abaixo para ver os erros (as categorias opcionais são: dimension, function, java source, java class, package, package body, procedure, trigger, type, type body e view):

SHOW ERRORS [CATEGORY [SCHEMA.] OBJECT]

Tarefas PL/SQL essenciais: criar/compilar, executar e exibir o código

Para criar código em PL/SQL, temos que usar algum CREATE statement, por exemplo, create function, create procedure, etc., quando queremos que o código fique armazenado no banco de dados.

Ao criar algum código PL/SQL, ele é “compilado” pelo banco de dados, ou seja, é checado, validado e armazenado no banco. Se ocorreram erros de compilação, podemos usar o comando abaixo para ver os erros (as categorias opcionais são: dimension, function, java source, java class, package, package body, procedure, trigger, type, type body e view):

SHOW ERRORS [CATEGORY [SCHEMA.] OBJECT]

É muito importante colocar o SHOW ERRORS após cada comando create de um PL/SQL!

Para executar um código PL/SQL, é a coisa mais fácil do mundo: chame o nome do código criado e pronto. Podemos também código que não fica armazenado no banco de dados, usanto um bloco simples (begin … end;); também podemos usar o comando EXECUTE do SQL*Plus.

Para exibir código PL/SQL, podemos consultar a view USER_OBJECTS, para ver o que temos armazenado, e depois usar o comando DESCRIBE (sim, describe funciona com tabelas, views, object types, procedures e packages). Aliás, o DESCRIBE é muito útil para uma rápida visualização dos parâmetros dos procedures.

Para exibir o código fonte, consulte a view USER_SOURCE ou view TRIGGER_SOURCE.

Gerenciar grants e sinônimos para PL/SQL armazenado

Quando você cria um PL/SQL só você e um DBA podem executá-lo. Para permitir que outros usuários possam aproveitar a funcionalidade que você desenvolveu, precisa dar um grant:

GRANT EXECUTE ON <schema>.<código> TO {user|role|public};
REVOKE EXECUTE ON <schema>.<código> FROM {user|role|public};

Para ver uma lista dos privilégios que você concedeu a outros usuários ou papéis, consulte a view USER_TAB_PRIVS_MADE.

Para faciliar o uso dos códigos, você pode criar um sinônimo (privado ou público) para o PL/SQL.

CREATE OR REPLACE [PUBLIC] SYNONYM <sinônimo> FOR <schema>.<object>
DROP SYNONYM <sinônimo>

Apagar um código armazenado

Basta usar um DROP statement.

Esconder a fonte PL/SQL

Você pode “criptografar” o código fonte PL/SQL com o utilitário wrap do Oracle, mas isso não é uma criptografia verdadeira: seu código fonte ainda pode ser recuperado!

Resumão de PL/SQL, parte 1: introdução

Esta série de artigos é um resumão dos capítulos do livro Oracle PL/SQL Programming, 6ª edição, de Steven Feuerstein e Bill Pribyl, que estou estudando. O objetivo deste resumão é manter aqui um lembrete para as coisas mais importantes que aprendi e que podem ser úteis no futuro.

PL/SQL significa “Procedural Language EXTENSIONS to Structured Query Language”, ou seja, é uma EXTENSÃO procedural à linguagem SQL do banco de dados Oracle.

Se você trabalha com, ou programa qualquer coisa para,  o Oracle Database terá que, em algum momento, escrever código em PL/SQL. Sendo uma extensão procedural à SQL, a PL/SQL agrega várias coisas ao SQL do Oracle:

  • Condicionais (if, then, else, case, etc.)
  • Loops (for, while, etc.)
  • Funçṍes, procedures
  • Tratamento de exceptions e erros
  • Variáveis, arrays, datatypes, etc.
  • Várias packages built-in com funcionalidades excepcionais

A PL/SQL não é executável, ou seja, para rodar o código deve estar compilado dentro do Oracle Database ou de alguma ferramenta cliente capaz de executar código PL/SQL, como o SQL*Plus ou o Oracle Forms.

A cada release do Oracle Database uma nova versão da PL/SQL é lançada, com várias inovações para os programadores. Hoje, falar que “isso não pode ser feito com PL/SQL” é mera desculpa.