(Parte 1 de 9)

PL/SQL

PROGRAMANDO EM ORACLE 9i

Desenvolvida Por: Rondinele Santos de Morais

INTRUDUÇÃO À LINGUAGEM PL/SQL1
Interagindo com o usuário1
PL/SQL e tráfego de rede1
Estrutura do bloco PL/SQL12
Blocos aninhados15
Identificadores16
Literais16
Comentários16
Declaração de variável17
Tipos PL/SQL17
Character Datatype17
Numeric Datatype17
Date Datatype18
LOB Datatypes18
Outros Datatypes19
Tipo booleano19
Tipos compostos19
Tipos de referencia19
Tipos de objeto19
Utilizando %TYPE19
Subtipos definidos pelo usuário20
Convertendo entre tipos de dados20
Escopo de variável e visibilidade21
Operadores21
Expressões booleanas2
Estruturas de controle PL/SQL2
IF-THEN- ELSE2
CASE23
Loops while24
Loops FOR numéricos25
GOTOs e rótulos26
Rotulando LOOPs26
NULO como uma instrução27
Registros PL/SQL27
Utilizando %ROWTYPE29
SQL DENTRO DA LINGUAGEM PL/SQL30
Select30
Insert30
Update31
Delete31
A cláusula RETURNING32
Referências de tabelas3
Database Links3
Sinônimos3
Controle de transações3
Transações autônomas34
Privilégios: GRANT e REVOKE34
Roles36
TRATAMENTO DE ERROS37
O que é uma exceção37
Tipos de erros PL/SQL37
Declarando Exceções39
Exceções definidas pelo usuário39
Exceções predefinidas40
Exceções predefinidas pelo Oracle40
Levantando exceções43
Tratando exceções4
O handler de exceção OTHERS46
SQLCODE e SQLERRM47
O pragma EXCEPTION_INIT49
Utilizando RAISE_APPLICATION_ERROR50
Exceções levantadas na seção de exceção52
FUNÇÕES SQL PREDEFINIDAS53
Funções de caractere que retornam valores de caracteres53
CHR (x[using nchar_cs])53
CONCAT (string1, string2)53
INITCAP (string)53
LOWER (string)53
LPAD (String1, x[string2])53
LTRIM (String1,String2)54
REPLACE (string, string_a_pesquisar [string_substituta])54
RPAD (string1, x, [string2])54
TRANSLATE (string, str_de, str_para)54
UPPER (string)54
SUBSTR54
SOUNDEX5
Funções de caractere que retornam valores numéricos56
ASCII (string)56
INSTR (string1,string2 [,a] [,b])56
LENGTH (string)56
INSTR56
LENGTH56
Funções de NLS57
CONVERT (string, conjunto_de_caracteres_dest[,conjunto_de_caracteres_orig])57
NCHR(x)57
NLS_CHARSET_DECL_LEN57
NLS_CHARSET_ID57
NLS_CHARSET_NAME57
NLS_INITCAP57
NLS_LOWER57
NLSSORT58
TRANSLATE58
UNISTR(s)58
Funções Numéricas58
ABS(x)58
ACOS(x)58
ASIN(x)58
ATAN(x)58
ATAN2 (x,y)58
BITAND(x,y)58
CEIL(x)58
COS(x)58
COSH(x)58
EXP(x)59
FLOOR(x)59
LN(x)59
LOG (x,y)59
MOD(x,y)59
POWER(x,y)59
ROUND(x[,y])59
SIGN(x)59
SIN(x)59
SINH(x)59
SQRT(x)59
TAN(x)59
TANH(x)59
TRUNC (x,[,y])59
WIDTH_BUCKET60
Funções de data e hora60
ADD_MONTHS(d,x)60
CURRENT_DATE60
CURRENT_TIMESTAMP61
DBTIMEZONE61
LAST_DAY61
LOCALTIMESTAMP61
MONTHS_BETWEEN61
NEW_TIME (d, zona1,zona2)61
NEXTDAY61
ROUND (d[,formato])61
SESSIONTIMEZONE61
SYS_EXTRACT_UTC61
SYSDATE61
SYSTIMETAMP61
TRUNC (d,[,formato])61
TZ_OFFSET(fuso horário)62
Funções de conversão62
BIN_TO_NUM (num[,num]...)62
CHARTOROWID62
COMPOSE62
DECOMPOSE62
FROM_TZ (timestamp,fuso horário)62
HEXTORAW62
NUMTODSINTERVAL62
NUMTOYMINTERVAL62
REFTOHEX62
RAWTOHEX62
RAWTONHEX62
ROWIDTOCHAR63
ROWIDTONCHAR63
TO_CHAR (datas e horas)63
TO_CHAR (Números)63
TO_DATE64
TO_NUMBER64
TO_TIMESTAMP e TO_TIMESTAMP_TZ64
Funções de grupo65
AVG ([DISTINCT|ALL] col)65
COUNT (*|[DISTINCT|ALL]col)65
GROUP_ID()65
GROUPING65
GROUPING_ID65
MAX([DISTINCT|ALL]col)65
MIN([DISTINCT|ALL]col)65
STDDEV ([DISTINCT|ALL]col)65
SUM([DISTINCT|ALL]col)6
Outras funções6
BFILENAME (diretório nome_de_arquivo)6
COALESCE6
EMPTY_BLOB/EMPTY_CLOB6
GREATEST (expr1 [,expr2])6
LEAST (expr1 [,expr2])…6
NULLIF (a,b)6
NVL (expr1,expr2)6
NVL2 (expr1,expr2,expr3)6
SYS_CONNECT_BY_PATH6
SYS_CONTEXT6
SYS_GUID67
SYS_TYPEID (tipo_de_objecto)67
TREAT (expr AS [REF] [esquema.]tipo )67
UID67
USER67
DUMP67
Cursores explícitos69
Parâmetros de cursor70
Atributos de cursor71
Cursores implícitos71
Loops de Busca de Cursores72
Loop Simples73
Loops WHILE73
Loops FOR de cursor74
Loops FOR implícitos75
NO_DATA_FOUND versus %NOTFOUND75
Cursores SELECT FOR UPDATE76
FOR UPDATE76
WHERE CURRENT OF76
COMMIT dentro de um Loop de cursor FOR UPDATE7
Variáveis de cursor78
COLEÇÕES81
Tabelas Index-by81
Elementos inexistentes81
Tabelas index-by de tipos compostos81
Tabelas index-by de registros82
Tabelas index-by de tipos de objeto82
Tabelas aninhadas (Nested tables)82
Inicialização de uma tabela aninhada83
Tabelas vazias84
Adicionando elementos a uma tabela existente85
VARRAYS85
Inicialização de varray86
Manipulando os elementos de um varray86
Coleções de múltiplos níveis87
Coleções no banco de dados8
A estrutura de varrays armazenados8
Estrutura das tabelas aninhadas armazenadas89
Manipulando coleções inteiras89
INSERT89
UPDATE90
DELETE91
SELECT91
Operadores de tabela SQL92
Métodos de coleção92
EXISTS93
COUNT93
LIMIT94
FIRST e LAST95
NEXT e PRIOR95
EXTEND96
TRIM97
CRIANDO PROCEDURES, FUNÇÕES E PACOTES101
Procedures e funções101
Criação de subprograma102
Criando uma procedure102
Corpo da procedure102
Criando uma função103
A instrução RETURN104
Eliminando procedures e funções105
Parâmetros de subprograma106
Modo de parâmetro106
Passando valores entre parâmetros formais e reais108
Literais ou constantes como parametros reais109
Lendo de parâmetros OUT110
Restrições quanto aos parâmetros formais110
Parâmetros de %TYPE e de procedure1
Exceções levantadas dentro de subprogramas1
Passando parâmetro por referência e por valor1
Utilizando o NOCOPY1
Semântica de exceção com NOCOPY112
Restrições de NOCOPY112
Os benefícios de NOCOPY112
Subprogramas sem parâmetros115
Notação posicional e identificada115
Valores padrão do parâmetro116
A instrução CALL117
Procedures versus funções118
Pacotes119
Especificação de pacote119
Corpo de pacote120
Pacotes e escopo122
Escopo de objetos no corpo do pacote122
Sobrecarregando subprogramas empacotados125
Inicialização do pacote127
UTILIZANDO PROCEDURES, FUNÇÕES E PACOTES130
Localizações do subprograma130
Subprogramas armazenados e o dicionário de dados130
Compilação nativa130
Subprogramas locais130
Subprogramas locais como parte de subprogramas armazenados131
Localização de subprogramas locais131
Declarações prévias132
Sobrecarregando subprogramas locais132
Subprogramas locais versus armazenados133
Considerações sobre subprogramas e pacotes armazenados133
Recompilação automática133
Pacotes e dependências133
Estado em tempo de execução de pacote135
Privilégios e subprogramas armazenados135
Privilégio EXECUTE135
Direito do chamador versus direito do definidor135
Triggers, visualizações e direitos do chamador136
Utilizando funções armazenadas em instruções SQL136
Níveis de pureza para as funções137
Chamando funções armazenadas a partir da SQL no Oracle8i138
Chamando funções a partir de instruções de DML139
Fixando no pool compartilhado140
KEEP140
UNKEEP141
SIZES141
ABORTED_REQUEST_THRESHOLD141
TRIGGERS DE BANCO DE DADOS142
Sintaxe para criação de triggers142
Criando triggers de DML142
Identificadores de correlação em triggers de nível de linha146
Cláusula REFERENCING147
A cláusula WHEN147
Predicados de trigger: INSERTING, UPDATING e DELETING147
Criandos triggers Insead-of148
Criando triggers de sistema150
Triggers de banco de dados versus esquema151
Funções do atributo de evento151
Corpos de triggers153
Privilégios de trigger153
Views do dicionário de dados154
Descartando e desativando triggers154
Tabelas que sofrem mutação154
RECURSOS AVANÇADOS158
SQL Dinâmica nativa158
Consultas com EXECUTE IMMEDIATE160
Executando consultas com cursores utilizando OPEN FOR160
Vinculação em volume163
Questões transacionais164
A cláusula BULK COLLECT166
Tipos de objeto168
Armazenando objetos no banco de dados170
Referências de objeto172
Funções de tabela em pipeline173
Pacotes avançados174
DBMS_SQL174
DBMS_PIPE175
DBMS_ALERT176
UTL_FILE177
UTL_SMTP179
UTL_HTTP179
UTL_INADDR180
DBMS_JOB180
algumas ferramentas Oracle do lado do cliente

PL/SQL é uma linguagem de programação sofisticada utilizada para acessar um banco de dados Oracle a partir de vários ambientes. ElAé integrada com o serviror do banco de dados de modo que o código PL/SQL possa ser processado de maneira rápida e eficiente. Essa linguagem também está disponível em

Em linhas gerais, a PL/SQL (Procedural Language/SQL) combina o poder e flexibilidade da SQL com as construções procedurais de uma linguagem de 3ª geração.

Interagindo com o usuário

A PL/SQL não tem nenhuma funcionalidade de entrada ou de saída construída diretamente na linguagem. Para retificar isso, o SQL*Plus, em combinação com o pacote DBMS_OUTPUT, fornece a capacidade de dar saída para mensagens em tela. Isso é feito em dois passos:

1. Permitir a saída no SQL*Plus com o comando set serveroutput on : SET SERVEROUTPUT {ON | OFF} [SIZE n]

Onde o n é o tamanho do buffer de saída. Seu valor padrão é 2.0 bytes

2. Dentro do seu programa PL/SQL, utilize a procedure DBMS_OUTPUT.PUT_LINE(msg).

Exemplo:

2DBMS_OUTPUT.PUT_LINE('Teste de pl/sql!!!');

SQL> SET SERVEROUTPUT ON SQL> begin 3 end; 4 / Teste de pl/sql!!!

PL/SQL procedure successfully completed.

PL/SQL e tráfego de rede

No modelo cliente/servidor, o próprio programa reside na máquina cliente e envia solicitações de informações para um servidor de banco de dados. As solicitações são feitas utilizando SQL. Em geral, isso resulta em várias viagens pele rede, uma para cada instrução SQL, diferente do uso da PL/SQL que pode estar armazenada no banco de dados ou mesmo permitir que vários comandos SQL sejam empacotados em bloco PL/SQL e enviados ao servidor como uma única unidade.

Estrutura do bloco PL/SQL

A unidade básica em um programa PL/SQL é um bloco. Todos os programas da

PL/SQL são compostos por blocos, que podem ser aninhados dentro do outro. Em geral , cada bloco realiza uma unidade lógica de trabalho no programa, assim separando um do outro diferente tarefas. Um bloco tem a seguinte estrutura:

DECLARE /* Seção declarativa – variáveis, tipos, cursores e subprogramas locais */

/* Seção executável - instruções SQL e procedurais entram aqui. Essa é a principal sessão do bloco PL/SQL, e é a única obrigatória. */

/* Seção de tratamento de exceções – instruções de tratamento de erros entram aqui. */

END; Tipos de blocos PL/SQL:

Blocos anônimos: construídos dinámicamente e ejecutados apenas umas vez. Podem ser rotulados ou não. Blocos rotulados são geralmente utilizados da mesma maneira que os blocos anônimos, mas os rotulados permitem referenciar variáveis que de outro modo não seriam visíveis.

Subprogramas: consistem em procedures e funções. Podem ser armazenados no banco de dados como objetos independentes, como parte de um pacote ou como métodos de um tipo de objeto.

Triggers: consistem em um bloco PL/SQL que está associado a um evento que ocorre no banco de dados.

Exemplos:

v_Num1NUMBER := 1;
v_Num2NUMBER := 2;
'-- This message brought to you by PL/SQL!';
of the variables. */

REM BLOCO ANONIMO NÃO ROTULADO DECLARE /* Declare variables to be used in this block. */ v_String1 VARCHAR2(50) := 'Hello World!'; v_String2 VARCHAR2(50) := v_OutputStr VARCHAR2(50); BEGIN /* First, insert two rows into temp_table, using the values INSERT INTO temp_table (num_col, char_col)

VALUES (v_Num1, v_String1);
VALUES (v_Num2, v_String2);
output them to the screen using the DBMS_OUTPUT package.
INTO v_OutputStr

/* Now query temp_table for the two rows we just inserted, and */ SELECT char_col

FROM temp_table WHERE num_col = v_Num1; DBMS_OUTPUT.PUT_LINE(v_OutputStr);

INTO v_OutputStr

SELECT char_col

FROM temp_table WHERE num_col = v_Num2; DBMS_OUTPUT.PUT_LINE(v_OutputStr);

/* Rollback our changes */ ROLLBACK; END; /

v_Num1NUMBER := 1;
v_Num2NUMBER := 2;
'-- This message brought to you by PL/SQL!';
of the variables. */
VALUES (v_Num1, v_String1);
VALUES (v_Num2, v_String2);

REM BLOCO ANONIMO ROTULADO <<l_InsertIntoTemp>> DECLARE /* Declare variables to be used in this block. */ v_String1 VARCHAR2(50) := 'Hello World!'; v_String2 VARCHAR2(50) := v_OutputStr VARCHAR2(50); BEGIN /* First, insert two rows into temp_table, using the values INSERT INTO temp_table (num_col, char_col) INSERT INTO temp_table (num_col, char_col)

output them to the screen using the DBMS_OUTPUT package.

/* Now query temp_table for the two rows we just inserted, and */

INTO v_OutputStr

SELECT char_col

FROM temp_table WHERE num_col = v_Num1; DBMS_OUTPUT.PUT_LINE(v_OutputStr);

INTO v_OutputStr

SELECT char_col

FROM temp_table WHERE num_col = v_Num2; DBMS_OUTPUT.PUT_LINE(v_OutputStr);

/* Rollback our changes */ ROLLBACK;

END l_InsertIntoTemp; /

v_Num1NUMBER := 1;
v_Num2NUMBER := 2;
'-- This message brought to you by PL/SQL!';
of the variables. */
VALUES (v_Num1, v_String1);
VALUES (v_Num2, v_String2);

REM PROCEDIMENTO CREATE OR REPLACE PROCEDURE InsertIntoTemp AS /* Declare variables to be used in this block. */ v_String1 VARCHAR2(50) := 'Hello World!'; v_String2 VARCHAR2(50) := v_OutputStr VARCHAR2(50); BEGIN /* First, insert two rows into temp_table, using the values INSERT INTO temp_table (num_col, char_col) INSERT INTO temp_table (num_col, char_col)

output them to the screen using the DBMS_OUTPUT package.
INTO v_OutputStr

/* Now query temp_table for the two rows we just inserted, and */ SELECT char_col

FROM temp_table WHERE num_col = v_Num1; DBMS_OUTPUT.PUT_LINE(v_OutputStr);

SELECT char_col INTO v_OutputStr

FROM temp_table WHERE num_col = v_Num2; DBMS_OUTPUT.PUT_LINE(v_OutputStr);

/* Rollback our changes */ ROLLBACK;

END InsertIntoTemp; /

REM BLOCO ANONIMO PARA CHAMAR A PROCEDURE BEGIN InsertIntoTemp; END; /

Blocos aninhados

Um bloco pode ser aninhado dentro da seção executável ou de exceção de um bloco externo, como no exemplo abaixo:

v_FirstName VARCHAR2(20);-- Variable length

DECLARE /* Start of declarative section */ v_StudentID NUMBER(5) := 10000; -- Numeric variable initialized -- to 10,0 character string -- with maximum length of 20 BEGIN /* Start of executable section */

(Parte 1 de 9)

Comentários