Apostila Oracle PLSQL9i, Notas de estudo de Sistemas de Informação

Baixe Apostila Oracle PLSQL9i e outras Notas de estudo em PDF para Sistemas de Informação, somente na Docsity!

PROGRAMANDO EM ORACLE 9i

PL/SQL

INTRUDUÇÃO À LINGUAGEM PL/SQL

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 algumas ferramentas Oracle do lado do cliente. 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.000 bytes

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

Exemplo:

SQL> SET SERVEROUTPUT ON SQL> begin 2 DBMS_OUTPUT.PUT_LINE('Teste de pl/sql!!!'); 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.

A

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 */

BEGIN /* 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. */

EXCEPTION /* 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:

REM BLOCO ANONIMO NÃO ROTULADO DECLARE /* Declare variables to be used in this block. / v_Num1 NUMBER := 1; v_Num2 NUMBER := 2; v_String1 VARCHAR2(50) := 'Hello World!'; v_String2 VARCHAR2(50) := '-- This message brought to you by PL/SQL!'; v_OutputStr VARCHAR2(50); BEGIN / First, insert two rows into temp_table, using the values of the variables. */ INSERT INTO temp_table (num_col, char_col)

SELECT char_col INTO v_OutputStr 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 l_InsertIntoTemp; /

REM PROCEDIMENTO CREATE OR REPLACE PROCEDURE InsertIntoTemp AS /* Declare variables to be used in this block. / v_Num1 NUMBER := 1; v_Num2 NUMBER := 2; v_String1 VARCHAR2(50) := 'Hello World!'; v_String2 VARCHAR2(50) := '-- This message brought to you by PL/SQL!'; v_OutputStr VARCHAR2(50); BEGIN / First, insert two rows into temp_table, using the values of the variables. */ INSERT INTO temp_table (num_col, char_col) VALUES (v_Num1, v_String1); INSERT INTO temp_table (num_col, char_col) VALUES (v_Num2, v_String2);

/* Now query temp_table for the two rows we just inserted, and output them to the screen using the DBMS_OUTPUT package. */ SELECT char_col INTO v_OutputStr 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:

DECLARE /* Start of declarative section / v_StudentID NUMBER(5) := 10000; -- Numeric variable initialized -- to 10, v_FirstName VARCHAR2(20); -- Variable length character string -- with maximum length of 20 BEGIN / Start of executable section */ -- Retrieve first name of student with ID 10, SELECT first_name INTO v_FirstName FROM students WHERE id = v_StudentID;

-- Start of a nested block, which contains only an executable -- section BEGIN INSERT INTO log_table (info) VALUES ('Hello from a nested block!'); END; EXCEPTION /* Start of exception section */ WHEN NO_DATA_FOUND THEN

Declaração de variável

Nome_da_variável [CONSTANT] datatype [NOT NULL] [{:= | DEFAULT} valor];

Onde nome_da_variável é o identificador, datatype é tipo e valor é o conteúdo inicial da variável. EX:

DECLARE v_dataInicial DATE; v_contador BINARY_INTEGER NOT NULL := 0; v_nome VARCHAR2(20); c_PI CONSTANT NUMBER DEFAULT 3.14;

Tipos PL/SQL

O Oracle PL/SQL possui diferentes tipos de dados (datatypes) para atender suas necessidades, que são divididos nas seguintes categorias: CHARACTER, NUMBER, DATE, LOB, BOOLEANOS, TIPOS COMPOSTOS, TIPOS DE OBJETO e TIPOS DE REFERÊNCIA.

Character Datatype

Usados para armazenar dados alfanuméricos.

 CHAR() armazena string de tamanho fixo. Tamanho default 1, máximo 32.767. Subtipo: CHARACTER  VARCHAR2() armazena string de tamanho variável. É possível armazenar string de até 32.767 bytes. Subtipo: STRING  VARCHAR() sinônimo para o tipo VARCHAR2.  NCHAR() e NVARCHAR2() possuem as mesmas características dos tipos CHAR e VARCHAR2 e são usados para armazenar dados NLS (National Language Support). A arquitetura Oracle NLS permite armazenar, processar e recuperar informações em linguagens nativas.  LONG é um tipo de dados que se tornou “obsoleto” com a chegada dos tipos LOB (Large Object). O tipo LONG armazena strings de tamanho variável de no máximo 32.760 bytes.

Numeric Datatype

Usado para armazenar dados numéricos com precisão de até 38 digitos.

 NUMBER(, ) onde corresponde ao número de dígitos e o número de casas decimais. Valores inseridos em colunas numéricas com número de casas decimais menor que o dado inserido serão arredondados. Subtipos: DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, INT, NUMERIC, REAL, SMALLINT.

 BINARY_INTEGER utilizado para armazenar inteiros com sinal, que variam de – 2147483647 a 2147483647. Requerem menos memória que tipos NUMBER. Subtipos: NATURAL (n>=0), NATURALN (n>=0 not null), POSITIVE (n>0), POSITIVEN (n>0 not null), SIGNTYPE (-1, 0, 1).  PLS_INTEGER Possui as mesmas características do tipo BINARY_INTEGER, entretanto possui melhor performance para cálculos.

Date Datatype

O tipo DATE permite valores de data e hora. O formato padrão é definido pelo parâmetro NLS_DATE_FORMAT. O Oracle armazena internamente a data em formato de número juliano com a parte fracionária usada para controlar a hora. Uma data Juliana corresponde ao número de dias desde 1 de Janeiro de 4712 A.C. Para operações aritméticas com datas no Oracle, basta adicionar ou subtrair números inteiros ou fracionários. Por exemplo, SYSDATE + 1 para somar uma dia, 1/ para acrescentar uma hora, 1/(24x60) ou 1/1440 para acrescentar 1 minuto e 1/(24x60x60) ou 1/86400 para um segundo. No Oracle 9i há também 5 novos tipos de dados para armazenar tipos de data:

 TIMESTAMP semelhante ao tipo DATE, com a diferença de armazenar fração de segundos com precisão de até 9 digitos.  TIMESTAMP WITH TIME ZONE armazena data/hora com informações de fuso horário.  TIMESTAMP WITH LOCAL TIME ZONE armazena data/hora no fuso horário do servidor. Quando o usuário seleciona os dados, o valor é ajustado para as configurações da sua sessão.  INTERVAL YEAR TO MONTH usado para armazenar espaço de tempo em anos e meses.  INTERVAL DAY TO SECOND permite especificar intervalos em dias, horas, minutos e segundos.

LOB Datatypes

Large Object (LOB) datatypes são usado para armazenar dados não estruturados como imagens, arquivos binários. Os tipos LOBs podem armazenar até 4GB de informação. A manipulação dos tipos LOB é feita através da package DBMS_LOB.

Datatype Descrição BLOB Binary Large Object Armazena até 4GB de dados binários no banco CLOB Character Large Object Armazena até 4GB de dados caráter BFILE Binary File Armazena até 4GB de dados em arquivos binários externos. Uma coluna BFILE armazena um ponteiro para o arquivo armazenado no sistema operacional.

DECLARE

v_Nome STUDENTS.FIRST_NAME%TYPE; v_Idade PLS_INTEGER NOT NULL :=0; v_IdadeTemp v_Idade%TYPE; --não herda restrição nem valor default

Subtipos definidos pelo usuário

Utilizado para fornecer um tipo alternativo para um tipo, descrevendo sua utilização pretendida. Sintaxe:

SUBTYPE novo_tipo IS tipo_original ;

Exemplo:

DECLARE SUBTYPE T_Contador IS NUMBER(3); v_Contador T_Contador; SUBTYPE T_Nome IS STUDENTS.FIRST_NAME%TYPE;

Convertendo entre tipos de dados

Quando possível, a PL/SQL converterá automaticamente (conversão implícita) tipos dentro da mesma família e entre as famílias de tipos de dados:  Caracteres e números  Caracteres e datas

Funções para conversão explícita de tipos de dados:

Função Descrição TO_CHAR Converte seu argumento em um tipo VARCHAR TO_DATE Converte seu argumento em um tipo DATE TO_TIMESTAMP Converte seu argumento em um tipo TIMESTAMP TO_TIMESTAMP_TZ Converte seu argumento em um tipo TIMESTAMP WITH TIMEZONE TO_DSINTERVAL Converte seu argumento em um tipo INTERVAL DAY TO SECOND TO_YMINTERVAL Converte seu argumento em um tipo INTERVAL YEAR TO MONTH TO_NUMBER Converte seu argumento em um tipo NUMBER HEXTORAW Converte uma representação hexadecimal na quantidade binária equivalente

Função Descrição RAWTOHEX Converte um valor RAW em uma representação hexadecimal da quantidade binária CHARTOROWID Converte uma representação de caractere de um ROWID em formato binário interno ROWIDTOCHAR Converte uma variável binária interna de ROWID em formato externo de 18 caracteres

Escopo de variável e visibilidade

O escopo de uma variável é a parte do programa onde a variável pode ser acessada antes de ser liberada da memória. A visibilidade de uma variável é a parte do programa onde a variável pode ser acessada sem ter de qualificar a referência.

<> DECLARE v_flag BOOLEAN; v_SSN NUMBER(9); BEGIN /* Aquí v_flag e v_SSN estão visíveis */

DECLARE v_Data DATE; v_SSN CHAR(11); BEGIN /* Aquí v_flag, v_Data e v_SSN CHAR(11) estão visíveis. Para acessar v_SSN NUMBER(9) é necessário informar o bloco a que pertence: bloco1.v_SSN */ END;

/* Aquí v_flag e v_SSN estão visíveis */

END;

Operadores

O operador mais básico na PL/SQL é o de atribuição. A sintaxe é:

Variável := valor;

Veja a tabela abaixo com os operadores PL/SQL: