PL / SQL - Guia Rápido

A linguagem de programação PL / SQL foi desenvolvida pela Oracle Corporation no final dos anos 1980 como linguagem de extensão procedural para SQL e banco de dados relacional Oracle. A seguir estão alguns fatos notáveis ​​sobre PL / SQL -

• PL / SQL é uma linguagem de processamento de transações de alto desempenho totalmente portátil.

• O PL / SQL fornece um ambiente de programação integrado, interpretado e independente do sistema operacional.

• PL / SQL também pode ser chamado diretamente da linha de comando SQL*Plus interface.

• A chamada direta também pode ser feita a partir de chamadas de linguagem de programação externa para o banco de dados.

• A sintaxe geral da PL / SQL é baseada na linguagem de programação ADA e Pascal.

• Além de Oracle, PL / SQL está disponível em TimesTen in-memory database e IBM DB2.

Recursos do PL / SQL

PL / SQL tem os seguintes recursos -

• PL / SQL é totalmente integrado ao SQL.
• Oferece ampla verificação de erros.
• Ele oferece vários tipos de dados.
• Ele oferece uma variedade de estruturas de programação.
• Ele oferece suporte à programação estruturada por meio de funções e procedimentos.
• Suporta programação orientada a objetos.
• Suporta o desenvolvimento de aplicações web e páginas de servidor.

Vantagens do PL / SQL

PL / SQL tem as seguintes vantagens -

• SQL é a linguagem de banco de dados padrão e PL / SQL é fortemente integrado ao SQL. PL / SQL oferece suporte a SQL estático e dinâmico. SQL estático oferece suporte a operações DML e controle de transações do bloco PL / SQL. No SQL dinâmico, o SQL permite a incorporação de instruções DDL em blocos PL / SQL.

• A PL / SQL permite enviar um bloco inteiro de instruções ao banco de dados de uma vez. Isso reduz o tráfego de rede e fornece alto desempenho para os aplicativos.

• PL / SQL oferece alta produtividade aos programadores, pois pode consultar, transformar e atualizar dados em um banco de dados.

• A PL / SQL economiza tempo no design e na depuração por meio de recursos fortes, como tratamento de exceções, encapsulamento, ocultação de dados e tipos de dados orientados a objetos.

• Os aplicativos escritos em PL / SQL são totalmente portáteis.

• PL / SQL oferece alto nível de segurança.

• O PL / SQL fornece acesso a pacotes SQL predefinidos.

• A PL / SQL fornece suporte para Programação Orientada a Objetos.

• PL / SQL fornece suporte para o desenvolvimento de aplicativos da Web e páginas de servidor.

Neste capítulo, discutiremos a configuração do ambiente de PL / SQL. PL / SQL não é uma linguagem de programação independente; é uma ferramenta dentro do ambiente de programação Oracle.SQL* Plusé uma ferramenta interativa que permite digitar instruções SQL e PL / SQL no prompt de comando. Esses comandos são então enviados ao banco de dados para processamento. Depois de processadas as declarações, os resultados são devolvidos e exibidos na tela.

Para executar programas PL / SQL, você deve ter o Oracle RDBMS Server instalado em sua máquina. Isso cuidará da execução dos comandos SQL. A versão mais recente do Oracle RDBMS é 11g. Você pode baixar uma versão de teste do Oracle 11g no seguinte link -

Baixe o Oracle 11g Express Edition

Você terá que baixar a versão de 32 ou 64 bits da instalação de acordo com o seu sistema operacional. Normalmente, existem dois arquivos. Baixamos a versão de 64 bits. Você também usará etapas semelhantes em seu sistema operacional, não importa se é Linux ou Solaris.

• win64_11gR2_database_1of2.zip

• win64_11gR2_database_2of2.zip

Depois de baixar os dois arquivos acima, você precisará descompactá-los em um único diretório database e abaixo disso você encontrará os seguintes subdiretórios -

Passo 1

Vamos agora iniciar o Oracle Database Installer usando o arquivo de configuração. A seguir está a primeira tela. Você pode fornecer seu ID de e-mail e marcar a caixa de seleção conforme mostrado na imagem a seguir. Clique noNext botão.

Passo 2

Você será direcionado para a seguinte tela; desmarque a caixa de seleção e clique noContinue botão para continuar.

etapa 3

Basta selecionar a primeira opção Create and Configure Database usando o botão de rádio e clique no Next botão para continuar.

Passo 4

Presumimos que você esteja instalando o Oracle com o propósito básico de aprendizado e que o esteja instalando em seu PC ou laptop. Portanto, selecione oDesktop Class opção e clique no Next botão para continuar.

Etapa 5

Forneça um local onde você instalará o Oracle Server. Basta modificar oOracle Basee os outros locais serão definidos automaticamente. Você também terá que fornecer uma senha; isso será usado pelo DBA do sistema. Depois de fornecer as informações necessárias, clique noNext botão para continuar.

Etapa 6

Novamente, clique no Next botão para continuar.

Etapa 7

Clique no Finishbotão para prosseguir; isso iniciará a instalação real do servidor.

Etapa 8

Isso levará alguns momentos, até que o Oracle comece a realizar a configuração necessária.

Etapa 9

Aqui, a instalação do Oracle copiará os arquivos de configuração necessários. Isso deve demorar um pouco -

Etapa 10

Assim que os arquivos do banco de dados forem copiados, você terá a seguinte caixa de diálogo. Basta clicar noOK botão e sai.

Etapa 11

Após a instalação, você terá a seguinte janela final.

Passo final

Agora é hora de verificar sua instalação. No prompt de comando, use o seguinte comando se estiver usando o Windows -

sqlplus "/ as sysdba"

Você deve ter o prompt SQL onde escreverá seus comandos e scripts PL / SQL -

Editor de texto

Executar programas grandes a partir do prompt de comando pode fazer com que você perca inadvertidamente parte do trabalho. É sempre recomendável usar os arquivos de comando. Para usar os arquivos de comando -

• Digite seu código em um editor de texto, como Notepad, Notepad+, ou EditPlusetc.

• Salve o arquivo com o .sql extensão no diretório inicial.

• Lançar o SQL*Plus command prompt do diretório onde você criou seu arquivo PL / SQL.

• Tipo @file_name no prompt de comando SQL * Plus para executar seu programa.

Se você não estiver usando um arquivo para executar os scripts PL / SQL, simplesmente copie seu código PL / SQL e clique com o botão direito do mouse na janela preta que exibe o prompt SQL; use opasteopção de colar o código completo no prompt de comando. Finalmente, basta pressionarEnter para executar o código, se ainda não tiver sido executado.

Neste capítulo, discutiremos a sintaxe básica da PL / SQL, que é um block-structuredlíngua; isso significa que os programas PL / SQL são divididos e escritos em blocos lógicos de código. Cada bloco consiste em três sub-partes -

S.No	Seções e descrição
1	Declarations Esta seção começa com a palavra-chave DECLARE. É uma seção opcional e define todas as variáveis, cursores, subprogramas e outros elementos a serem usados ​​no programa.
2	Executable Commands Esta seção está entre as palavras-chave BEGIN e ENDe é uma seção obrigatória. Consiste nas instruções PL / SQL executáveis ​​do programa. Deve ter pelo menos uma linha de código executável, que pode ser apenas umNULL command para indicar que nada deve ser executado.
3	Exception Handling Esta seção começa com a palavra-chave EXCEPTION. Esta seção opcional contémexception(s) que tratam de erros no programa.

Cada instrução PL / SQL termina com um ponto e vírgula (;). Os blocos PL / SQL podem ser aninhados em outros blocos PL / SQL usandoBEGIN e END. A seguir está a estrutura básica de um bloco PL / SQL -

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

O exemplo 'Hello World'

DECLARE 
   message  varchar2(20):= 'Hello, World!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

o end;linha sinaliza o fim do bloco PL / SQL. Para executar o código a partir da linha de comando SQL, pode ser necessário digitar / no início da primeira linha em branco após a última linha do código. Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Hello World  

PL/SQL procedure successfully completed.

Os identificadores PL / SQL

Os identificadores PL / SQL são constantes, variáveis, exceções, procedimentos, cursores e palavras reservadas. Os identificadores consistem em uma letra seguida opcionalmente por mais letras, números, cifrões, sublinhados e sinais numéricos e não devem exceder 30 caracteres.

Por padrão, identifiers are not case-sensitive. Então você pode usarinteger ou INTEGERpara representar um valor numérico. Você não pode usar uma palavra-chave reservada como identificador.

Os delimitadores PL / SQL

Um delimitador é um símbolo com um significado especial. A seguir está a lista de delimitadores em PL / SQL -

Delimitador	Descrição
+, -, *, /	Adição, subtração / negação, multiplicação, divisão
%	Indicador de atributo
'	Delimitador de string de caracteres
.	Seletor de componente
(,)	Expressão ou delimitador de lista
:	Indicador de variável de host
,	Separador de item
"	Delimitador de identificador citado
=	Operador relacional
@	Indicador de acesso remoto
;	Terminador de declaração
:=	Operador de atribuição
=>	Operador de associação
||	Operador de concatenação
**	Operador de exponenciação
<<, >>	Delimitador de rótulo (início e fim)
/*, */	Delimitador de comentário de várias linhas (início e fim)
--	Indicador de comentário de linha única
..	Operador de alcance
<, >, <=, >=	Operadores relacionais
<>, '=, ~=, ^=	Diferentes versões de NOT EQUAL

Os Comentários PL / SQL

Os comentários do programa são declarações explicativas que podem ser incluídas no código PL / SQL que você escreve e ajudam qualquer pessoa a ler seu código-fonte. Todas as linguagens de programação permitem alguma forma de comentários.

O PL / SQL oferece suporte a comentários de uma e de várias linhas. Todos os caracteres disponíveis em qualquer comentário são ignorados pelo compilador PL / SQL. Os comentários de uma linha PL / SQL começam com o delimitador - (hífen duplo) e os comentários de várias linhas são delimitados por / * e * /.

DECLARE 
   -- variable declaration 
   message  varchar2(20):= 'Hello, World!'; 
BEGIN 
   /* 
   *  PL/SQL executable statement(s) 
   */ 
   dbms_output.put_line(message); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Hello World

PL/SQL procedure successfully completed.

Unidades de programa PL / SQL

Uma unidade PL / SQL é qualquer uma das seguintes -

• Bloco PL / SQL
• Function
• Package
• Corpo da embalagem
• Procedure
• Trigger
• Type
• Corpo do tipo

Cada uma dessas unidades será discutida nos próximos capítulos.

Neste capítulo, discutiremos os tipos de dados em PL / SQL. As variáveis, constantes e parâmetros PL / SQL devem ter um tipo de dados válido, que especifica um formato de armazenamento, restrições e um intervalo válido de valores. Vamos nos concentrar noSCALAR e a LOBtipos de dados neste capítulo. Os outros dois tipos de dados serão abordados em outros capítulos.

S.No	Categoria e descrição
1	Scalar Valores únicos sem componentes internos, como um NUMBER, DATE, ou BOOLEAN.
2	Large Object (LOB) Indicadores para objetos grandes armazenados separadamente de outros itens de dados, como texto, imagens gráficas, videoclipes e formas de onda de som.
3	Composite Itens de dados que possuem componentes internos que podem ser acessados ​​individualmente. Por exemplo, coleções e registros.
4	Reference Ponteiros para outros itens de dados.

Tipos e subtipos de dados escalares PL / SQL

Tipos e subtipos de dados escalares PL / SQL vêm nas seguintes categorias -

S.No	Tipo de data e descrição
1	Numeric Valores numéricos nos quais as operações aritméticas são realizadas.
2	Character Valores alfanuméricos que representam caracteres únicos ou sequências de caracteres.
3	Boolean Valores lógicos nos quais as operações lógicas são executadas.
4	Datetime Datas e horários.

PL / SQL fornece subtipos de tipos de dados. Por exemplo, o tipo de dados NUMBER tem um subtipo chamado INTEGER. Você pode usar os subtipos em seu programa PL / SQL para tornar os tipos de dados compatíveis com os tipos de dados em outros programas enquanto incorpora o código PL / SQL em outro programa, como um programa Java.

Tipos e subtipos de dados numéricos PL / SQL

A tabela a seguir lista os tipos de dados numéricos predefinidos PL / SQL e seus subtipos -

S.No	Tipo de dados e descrição
1	PLS_INTEGER Inteiro assinado no intervalo -2.147.483.648 a 2.147.483.647, representado em 32 bits
2	BINARY_INTEGER Inteiro assinado no intervalo -2.147.483.648 a 2.147.483.647, representado em 32 bits
3	BINARY_FLOAT Número de ponto flutuante de formato IEEE 754 de precisão única
4	BINARY_DOUBLE Número de ponto flutuante de formato IEEE 754 de precisão dupla
5	NUMBER(prec, scale) Número de ponto fixo ou ponto flutuante com valor absoluto no intervalo 1E-130 a (mas não incluindo) 1.0E126. Uma variável NUMBER também pode representar 0
6	DEC(prec, scale) Tipo de ponto fixo específico de ANSI com precisão máxima de 38 dígitos decimais
7	DECIMAL(prec, scale) Tipo de ponto fixo específico da IBM com precisão máxima de 38 dígitos decimais
8	NUMERIC(pre, secale) Tipo flutuante com precisão máxima de 38 dígitos decimais
9	DOUBLE PRECISION Tipo de ponto flutuante específico de ANSI com precisão máxima de 126 dígitos binários (aproximadamente 38 dígitos decimais)
10	FLOAT ANSI e tipo de ponto flutuante específico da IBM com precisão máxima de 126 dígitos binários (aproximadamente 38 dígitos decimais)
11	INT Tipo inteiro específico de ANSI com precisão máxima de 38 dígitos decimais
12	INTEGER ANSI e tipo inteiro específico da IBM com precisão máxima de 38 dígitos decimais
13	SMALLINT ANSI e tipo inteiro específico da IBM com precisão máxima de 38 dígitos decimais
14	REAL Tipo de ponto flutuante com precisão máxima de 63 dígitos binários (aproximadamente 18 dígitos decimais)

A seguir está uma declaração válida -

DECLARE 
   num1 INTEGER; 
   num2 REAL; 
   num3 DOUBLE PRECISION; 
BEGIN 
   null; 
END; 
/

Quando o código acima é compilado e executado, ele produz o seguinte resultado -

PL/SQL procedure successfully completed

Tipos e subtipos de dados de caracteres PL / SQL

A seguir estão os detalhes dos tipos de dados de caracteres predefinidos PL / SQL e seus subtipos -

S.No	Tipo de dados e descrição
1	CHAR Sequência de caracteres de comprimento fixo com tamanho máximo de 32.767 bytes
2	VARCHAR2 Sequência de caracteres de comprimento variável com tamanho máximo de 32.767 bytes
3	RAW Binário de comprimento variável ou string de bytes com tamanho máximo de 32.767 bytes, não interpretado por PL / SQL
4	NCHAR Sequência de caracteres nacionais de comprimento fixo com tamanho máximo de 32.767 bytes
5	NVARCHAR2 Sequência de caracteres nacionais de comprimento variável com tamanho máximo de 32.767 bytes
6	LONG Sequência de caracteres de comprimento variável com tamanho máximo de 32.760 bytes
7	LONG RAW Binário de comprimento variável ou string de bytes com tamanho máximo de 32.760 bytes, não interpretado por PL / SQL
8	ROWID Identificador físico de linha, o endereço de uma linha em uma tabela comum
9	UROWID Identificador de linha universal (identificador de linha físico, lógico ou externo)

Tipos de dados booleanos PL / SQL

o BOOLEANtipo de dados armazena valores lógicos que são usados ​​em operações lógicas. Os valores lógicos são os valores booleanosTRUE e FALSE e o valor NULL.

No entanto, SQL não possui tipo de dados equivalente a BOOLEAN. Portanto, os valores booleanos não podem ser usados ​​em -

• Instruções SQL
• Funções SQL integradas (como TO_CHAR)
• Funções PL / SQL chamadas de instruções SQL

PL / SQL Datetime e tipos de intervalo

o DATEdatatype é usado para armazenar datetimes de comprimento fixo, que incluem a hora do dia em segundos desde a meia-noite. As datas válidas variam de 1º de janeiro de 4712 aC a 31 de dezembro de 9999 dC.

O formato de data padrão é definido pelo parâmetro de inicialização do Oracle NLS_DATE_FORMAT. Por exemplo, o padrão pode ser 'DD-MON-YY', que inclui um número de dois dígitos para o dia do mês, uma abreviação do nome do mês e os dois últimos dígitos do ano. Por exemplo, 01-OCT-12.

Cada DATE inclui século, ano, mês, dia, hora, minuto e segundo. A tabela a seguir mostra os valores válidos para cada campo -

Nome do Campo	Valores válidos de data e hora	Valores de intervalo válidos
ANO	-4712 a 9999 (excluindo ano 0)	Qualquer número inteiro diferente de zero
MÊS	01 a 12	0 a 11
DIA	01 a 31 (limitado pelos valores de MÊS e ANO, conforme regras do calendário do local)	Qualquer número inteiro diferente de zero
HORA	00 a 23	0 a 23
MINUTO	00 a 59	0 a 59
SEGUNDO	00 a 59,9 (n), onde 9 (n) é a precisão dos segundos fracionários de tempo	0 a 59,9 (n), onde 9 (n) é a precisão do intervalo de segundos fracionários
TIMEZONE_HOUR	-12 a 14 (a faixa acomoda as mudanças do horário de verão)	Não aplicável
TIMEZONE_MINUTE	00 a 59	Não aplicável
TIMEZONE_REGION	Encontrado na visualização de desempenho dinâmico V $ TIMEZONE_NAMES	Não aplicável
TIMEZONE_ABBR	Encontrado na visualização de desempenho dinâmico V $ TIMEZONE_NAMES	Não aplicável

Tipos de dados PL / SQL Large Object (LOB)

Os tipos de dados de objeto grande (LOB) referem-se a grandes itens de dados, como texto, imagens gráficas, videoclipes e formas de onda de som. Os tipos de dados LOB permitem acesso eficiente, aleatório e por partes a esses dados. A seguir estão os tipos de dados LOB PL / SQL predefinidos -

Tipo de dados	Descrição	Tamanho
BFILE	Usado para armazenar grandes objetos binários em arquivos do sistema operacional fora do banco de dados.	Depende do sistema. Não pode exceder 4 gigabytes (GB).
BLOB	Usado para armazenar grandes objetos binários no banco de dados.	8 a 128 terabytes (TB)
CLOB	Usado para armazenar grandes blocos de dados de caracteres no banco de dados.	8 a 128 TB
NCLOB	Usado para armazenar grandes blocos de dados NCHAR no banco de dados.	8 a 128 TB

Subtipos definidos pelo usuário PL / SQL

Um subtipo é um subconjunto de outro tipo de dados, que é chamado de tipo base. Um subtipo tem as mesmas operações válidas que seu tipo base, mas apenas um subconjunto de seus valores válidos.

PL / SQL predefine vários subtipos no pacote STANDARD. Por exemplo, PL / SQL predefine os subtiposCHARACTER e INTEGER como segue -

SUBTYPE CHARACTER IS CHAR; 
SUBTYPE INTEGER IS NUMBER(38,0);

Você pode definir e usar seus próprios subtipos. O programa a seguir ilustra a definição e o uso de um subtipo definido pelo usuário -

DECLARE 
   SUBTYPE name IS char(20); 
   SUBTYPE message IS varchar2(100); 
   salutation name; 
   greetings message; 
BEGIN 
   salutation := 'Reader '; 
   greetings := 'Welcome to the World of PL/SQL'; 
   dbms_output.put_line('Hello ' || salutation || greetings); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Hello Reader Welcome to the World of PL/SQL 
 
PL/SQL procedure successfully completed.

NULLs em PL / SQL

Os valores PL / SQL NULL representam missing ou unknown datae eles não são um número inteiro, um caractere ou qualquer outro tipo de dados específico. Observe queNULL não é o mesmo que uma string de dados vazia ou o valor de caractere nulo '\0'. Um nulo pode ser atribuído, mas não pode ser igualado a nada, incluindo ele mesmo.

Neste capítulo, discutiremos Variáveis ​​em Pl / SQL. Uma variável nada mais é que um nome dado a uma área de armazenamento que nossos programas podem manipular. Cada variável no PL / SQL possui um tipo de dado específico, que determina o tamanho e o layout da memória da variável; a faixa de valores que podem ser armazenados nessa memória e o conjunto de operações que podem ser aplicadas à variável.

O nome de uma variável PL / SQL consiste em uma letra seguida opcionalmente por mais letras, números, cifrões, sublinhados e sinais numéricos e não deve exceder 30 caracteres. Por padrão, os nomes das variáveis ​​não diferenciam maiúsculas de minúsculas. Você não pode usar uma palavra-chave PL / SQL reservada como nome de variável.

A linguagem de programação PL / SQL permite definir vários tipos de variáveis, como tipos de dados de data e hora, registros, coleções, etc. que abordaremos nos próximos capítulos. Para este capítulo, vamos estudar apenas os tipos básicos de variáveis.

Declaração de variável em PL / SQL

As variáveis ​​PL / SQL devem ser declaradas na seção de declaração ou em um pacote como uma variável global. Quando você declara uma variável, o PL / SQL aloca memória para o valor da variável e o local de armazenamento é identificado pelo nome da variável.

A sintaxe para declarar uma variável é -

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Onde nome_variável é um identificador válido em PL / SQL, tipo de dados deve ser um tipo de dados PL / SQL válido ou qualquer tipo de dados definido pelo usuário que já discutimos no último capítulo. Algumas declarações de variáveis ​​válidas junto com sua definição são mostradas abaixo -

sales number(10, 2); 
pi CONSTANT double precision := 3.1415; 
name varchar2(25); 
address varchar2(100);

Quando você fornece um limite de tamanho, escala ou precisão com o tipo de dados, ele é chamado de constrained declaration. As declarações restritas requerem menos memória do que as declarações irrestritas. Por exemplo -

sales number(10, 2); 
name varchar2(25); 
address varchar2(100);

Inicializando Variáveis ​​em PL / SQL

Sempre que você declara uma variável, o PL / SQL atribui a ela um valor padrão NULL. Se você deseja inicializar uma variável com um valor diferente do valor NULL, você pode fazer isso durante a declaração, usando um dos seguintes -

• o DEFAULT palavra chave

• o assignment operador

Por exemplo -

counter binary_integer := 0; 
greetings varchar2(20) DEFAULT 'Have a Good Day';

Você também pode especificar que uma variável não deve ter um NULL valor usando o NOT NULLrestrição. Se você usar a restrição NOT NULL, deverá atribuir explicitamente um valor inicial para essa variável.

É uma boa prática de programação inicializar as variáveis ​​corretamente, caso contrário, às vezes os programas produziriam resultados inesperados. Experimente o seguinte exemplo que usa vários tipos de variáveis ​​-

DECLARE 
   a integer := 10; 
   b integer := 20; 
   c integer; 
   f real; 
BEGIN 
   c := a + b; 
   dbms_output.put_line('Value of c: ' || c); 
   f := 70.0/3.0; 
   dbms_output.put_line('Value of f: ' || f); 
END; 
/

Quando o código acima é executado, ele produz o seguinte resultado -

Value of c: 30 
Value of f: 23.333333333333333333  

PL/SQL procedure successfully completed.

Escopo variável em PL / SQL

PL / SQL permite o aninhamento de blocos, ou seja, cada bloco de programa pode conter outro bloco interno. Se uma variável for declarada dentro de um bloco interno, ela não estará acessível ao bloco externo. No entanto, se uma variável for declarada e acessível a um bloco externo, ela também será acessível a todos os blocos internos aninhados. Existem dois tipos de escopo de variável -

• Local variables - Variáveis ​​declaradas em um bloco interno e não acessíveis aos blocos externos.

• Global variables - Variáveis ​​declaradas no bloco mais externo ou um pacote.

O exemplo a seguir mostra o uso de Local e Global variáveis ​​em sua forma simples -

DECLARE 
   -- Global variables  
   num1 number := 95;  
   num2 number := 85;  
BEGIN  
   dbms_output.put_line('Outer Variable num1: ' || num1); 
   dbms_output.put_line('Outer Variable num2: ' || num2); 
   DECLARE  
      -- Local variables 
      num1 number := 195;  
      num2 number := 185;  
   BEGIN  
      dbms_output.put_line('Inner Variable num1: ' || num1); 
      dbms_output.put_line('Inner Variable num2: ' || num2); 
   END;  
END; 
/

Quando o código acima é executado, ele produz o seguinte resultado -

Outer Variable num1: 95 
Outer Variable num2: 85 
Inner Variable num1: 195 
Inner Variable num2: 185  

PL/SQL procedure successfully completed.

Atribuição de resultados de consulta SQL a variáveis ​​PL / SQL

Você pode usar o SELECT INTOinstrução de SQL para atribuir valores a variáveis ​​PL / SQL. Para cada item noSELECT list, deve haver uma variável compatível com o tipo correspondente no INTO list. O exemplo a seguir ilustra o conceito. Vamos criar uma tabela chamada CUSTOMERS -

(For SQL statements, please refer to the SQL tutorial)

CREATE TABLE CUSTOMERS( 
   ID   INT NOT NULL, 
   NAME VARCHAR (20) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR (25), 
   SALARY   DECIMAL (18, 2),        
   PRIMARY KEY (ID) 
);  

Table Created

Vamos agora inserir alguns valores na tabela -

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
  
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 
 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

O programa a seguir atribui valores da tabela acima para variáveis ​​PL / SQL usando o SELECT INTO clause de SQL -

DECLARE 
   c_id customers.id%type := 1; 
   c_name  customers.name%type; 
   c_addr customers.address%type; 
   c_sal  customers.salary%type; 
BEGIN 
   SELECT name, address, salary INTO c_name, c_addr, c_sal 
   FROM customers 
   WHERE id = c_id;  
   dbms_output.put_line 
   ('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal); 
END; 
/

Quando o código acima é executado, ele produz o seguinte resultado -

Customer Ramesh from Ahmedabad earns 2000  

PL/SQL procedure completed successfully

Neste capítulo, discutiremos constants e literalsem PL / SQL. Uma constante contém um valor que, uma vez declarado, não muda no programa. Uma declaração constante especifica seu nome, tipo de dados e valor, e aloca armazenamento para eles. A declaração também pode impor oNOT NULL constraint.

Declarando uma constante

Uma constante é declarada usando o CONSTANTpalavra-chave. Requer um valor inicial e não permite que esse valor seja alterado. Por exemplo -

PI CONSTANT NUMBER := 3.141592654; 
DECLARE 
   -- constant declaration 
   pi constant number := 3.141592654; 
   -- other declarations 
   radius number(5,2);  
   dia number(5,2);  
   circumference number(7, 2); 
   area number (10, 2); 
BEGIN  
   -- processing 
   radius := 9.5;  
   dia := radius * 2;  
   circumference := 2.0 * pi * radius; 
   area := pi * radius * radius; 
   -- output 
   dbms_output.put_line('Radius: ' || radius); 
   dbms_output.put_line('Diameter: ' || dia); 
   dbms_output.put_line('Circumference: ' || circumference); 
   dbms_output.put_line('Area: ' || area); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Radius: 9.5 
Diameter: 19 
Circumference: 59.69 
Area: 283.53  

Pl/SQL procedure successfully completed.

Os literais PL / SQL

Um literal é um valor numérico, caractere, string ou booleano explícito não representado por um identificador. Por exemplo, TRUE, 786, NULL, 'tutorialspoint' são todos literais do tipo booleano, número ou string. PL / SQL, literais diferenciam maiúsculas de minúsculas. PL / SQL suporta os seguintes tipos de literais -

• Literais Numéricos
• Literais de caracteres
• Literais de string
• Literais BOOLEAN
• Literais de data e hora

A tabela a seguir fornece exemplos de todas essas categorias de valores literais.

S.No	Tipo Literal e Exemplo
1	Numeric Literals 050 78 -14 0 +32767 6,6667 0,0 -12,0 3,14159 +7800,00 6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3
2	Character Literals 'A' '%' '9' '' 'z' '('
3	String Literals 'Olá Mundo!' 'Tutoriais Point' '19 -NOV-12 '
4	BOOLEAN Literals TRUE, FALSE e NULL.
5	Date and Time Literals DATE '1978-12-25'; TIMESTAMP '2012-10-29 12:01:01';

Para incorporar aspas simples em um literal de string, coloque duas aspas simples uma ao lado da outra, conforme mostrado no programa a seguir -

DECLARE 
   message  varchar2(30):= 'That''s tutorialspoint.com!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

That's tutorialspoint.com!  

PL/SQL procedure successfully completed.

Neste capítulo, discutiremos os operadores em PL / SQL. Um operador é um símbolo que informa ao compilador para realizar uma manipulação matemática ou lógica específica. A linguagem PL / SQL é rica em operadores integrados e fornece os seguintes tipos de operadores -

• Operadores aritméticos
• Operadores relacionais
• Operadores de comparação
• Operadores lógicos
• Operadores de string

Aqui, entenderemos os operadores aritméticos, relacionais, de comparação e lógicos um por um. Os operadores String serão discutidos em um capítulo posterior -PL/SQL - Strings.

Operadores aritméticos

A tabela a seguir mostra todos os operadores aritméticos suportados pelo PL / SQL. Vamos assumirvariable A detém 10 e variable B segura 5, então -

Mostrar exemplos

Operador	Descrição	Exemplo
+	Adiciona dois operandos	A + B dará 15
-	Subtrai o segundo operando do primeiro	A - B dará 5
*	Multiplica ambos os operandos	A * B dará 50
/	Divide numerador por de-numerador	A / B dará 2
**	Operador de exponenciação, eleva um operando à potência de outro	A ** B dará 100.000

Operadores Relacionais

Os operadores relacionais comparam duas expressões ou valores e retornam um resultado booleano. A tabela a seguir mostra todos os operadores relacionais suportados pelo PL / SQL. Vamos assumirvariable A detém 10 e variable B contém 20, então -

Mostrar exemplos

Operador	Descrição	Exemplo
=	Verifica se os valores dos dois operandos são iguais ou não, se sim a condição torna-se verdadeira.	(A = B) não é verdade.
! = <> ~ =	Verifica se os valores de dois operandos são iguais ou não, se os valores não são iguais, a condição se torna verdadeira.	(A! = B) é verdade.
>	Verifica se o valor do operando esquerdo é maior que o valor do operando direito, se sim então a condição torna-se verdadeira.	(A> B) não é verdade.
<	Verifica se o valor do operando esquerdo é menor que o valor do operando direito; se sim, a condição torna-se verdadeira.	(A <B) é verdade.
> =	Verifica se o valor do operando esquerdo é maior ou igual ao valor do operando direito, se sim a condição torna-se verdadeira.	(A> = B) não é verdade.
<=	Verifica se o valor do operando esquerdo é menor ou igual ao valor do operando direito; em caso afirmativo, a condição torna-se verdadeira.	(A <= B) é verdadeiro

Operadores de comparação

Operadores de comparação são usados ​​para comparar uma expressão a outra. O resultado é sempre ouTRUE, FALSE ou NULL.

Mostrar exemplos

Operador	Descrição	Exemplo
GOSTAR	O operador LIKE compara um caractere, string ou valor CLOB com um padrão e retorna TRUE se o valor corresponder ao padrão e FALSE se não corresponder.	Se 'Zara Ali' como 'Z% A_i' retorna um booleano verdadeiro, enquanto, 'Nuha Ali' como 'Z% A_i' retorna um booleano falso.
ENTRE	O operador BETWEEN testa se um valor está em um intervalo especificado. x BETWEEN a AND b significa que x> = a e x <= b.	Se x = 10, então, x entre 5 e 20 retorna verdadeiro, x entre 5 e 10 retorna verdadeiro, mas x entre 11 e 20 retorna falso.
DENTRO	O operador IN testa a associação do conjunto. x IN (conjunto) significa que x é igual a qualquer membro do conjunto.	Se x = 'm', então, x in ('a', 'b', 'c') retorna booleano falso, mas x em ('m', 'n', 'o') retorna booleano verdadeiro.
É NULO	O operador IS NULL retorna o valor BOOLEANO TRUE se seu operando for NULL ou FALSE se não for NULL. Comparações envolvendo valores NULL sempre geram NULL.	Se x = 'm', então 'x é nulo' retorna falso booleano.

Operadores lógicos

A tabela a seguir mostra os operadores lógicos suportados pelo PL / SQL. Todos esses operadores trabalham em operandos booleanos e produzem resultados booleanos. Vamos assumirvariable A é verdadeiro e variable B é falso, então -

Mostrar exemplos

Operador	Descrição	Exemplos
e	Chamado de operador lógico AND. Se ambos os operandos forem verdadeiros, a condição se torna verdadeira.	(A e B) é falso.
ou	Chamado o operador OR lógico. Se qualquer um dos dois operandos for verdadeiro, a condição se torna verdadeira.	(A ou B) é verdade.
não	Chamado de operador NOT lógico. Usado para reverter o estado lógico de seu operando. Se uma condição for verdadeira, o operador lógico NOT a tornará falsa.	não (A e B) é verdade.

Precedência do operador PL / SQL

A precedência do operador determina o agrupamento de termos em uma expressão. Isso afeta como uma expressão é avaliada. Certos operadores têm precedência mais alta do que outros; por exemplo, o operador de multiplicação tem precedência mais alta do que o operador de adição.

Por exemplo, x = 7 + 3 * 2; aqui,x é atribuído 13, não 20 porque o operador * tem precedência maior que +, então ele primeiro é multiplicado por 3*2 e então adiciona em 7.

Aqui, os operadores com a precedência mais alta aparecem na parte superior da tabela, aqueles com a mais baixa aparecem na parte inferior. Em uma expressão, os operadores de precedência superior serão avaliados primeiro.

A precedência dos operadores é a seguinte: =, <,>, <=,> =, <>,! =, ~ =, ^ =, IS NULL, LIKE, BETWEEN, IN.

Mostrar exemplos

Operador	Operação
**	exponenciação
+, -	identidade, negação
*, /	multiplicação, divisão
+, -, ||	adição, subtração, concatenação
comparação
NÃO	negação lógica
E	conjunção
OU	inclusão

Neste capítulo, discutiremos as condições em PL / SQL. Estruturas de tomada de decisão requerem que o programador especifique uma ou mais condições a serem avaliadas ou testadas pelo programa, junto com uma instrução ou instruções a serem executadas se a condição for determinada como verdadeira e, opcionalmente, outras instruções a serem executadas se o condição é determinada como falsa.

A seguir está a forma geral de uma estrutura condicional típica (ou seja, tomada de decisão) encontrada na maioria das linguagens de programação -

A linguagem de programação PL / SQL fornece os seguintes tipos de instruções de tomada de decisão. Clique nos links a seguir para verificar seus detalhes.

S.No	Declaração e descrição
1	IF - declaração THEN o IF statement associa uma condição a uma sequência de declarações delimitadas pelas palavras-chave THEN e END IF. Se a condição for verdadeira, as instruções serão executadas e se a condição for falsa ou NULL, a instrução IF não fará nada.
2	Instrução IF-THEN-ELSE IF statement adiciona a palavra-chave ELSEseguido por uma sequência alternativa de declaração. Se a condição for falsa ou NULL, apenas a sequência alternativa de instruções será executada. Ele garante que qualquer uma das sequências de instruções seja executada.
3	Instrução IF-THEN-ELSIF Ele permite que você escolha entre várias alternativas.
4	Declaração de caso Como a declaração IF, o CASE statement seleciona uma sequência de instruções para executar. No entanto, para selecionar a sequência, a instrução CASE usa um seletor em vez de várias expressões booleanas. Um seletor é uma expressão cujo valor é usado para selecionar uma das várias alternativas.
5	Declaração CASE pesquisada A declaração CASE pesquisada has no selector, e suas cláusulas WHEN contêm condições de pesquisa que geram valores booleanos.
6	aninhado IF-THEN-ELSE Você pode usar um IF-THEN ou IF-THEN-ELSIF declaração dentro de outra IF-THEN ou IF-THEN-ELSIF afirmações).

Neste capítulo, discutiremos Loops em PL / SQL. Pode haver uma situação em que você precise executar um bloco de código várias vezes. Em geral, as instruções são executadas sequencialmente: a primeira instrução em uma função é executada primeiro, seguida pela segunda e assim por diante.

As linguagens de programação fornecem várias estruturas de controle que permitem caminhos de execução mais complicados.

Uma instrução de loop nos permite executar uma instrução ou grupo de instruções várias vezes e a seguir está a forma geral de uma instrução de loop na maioria das linguagens de programação -

A PL / SQL fornece os seguintes tipos de loop para lidar com os requisitos de loop. Clique nos links a seguir para verificar seus detalhes.

S.No	Tipo de Loop e Descrição
1	PL / SQL Basic LOOP Nessa estrutura de loop, a sequência de instruções é incluída entre as instruções LOOP e END LOOP. A cada iteração, a sequência de instruções é executada e o controle é retomado no topo do loop.
2	PL / SQL WHILE LOOP Repete uma declaração ou grupo de declarações enquanto uma determinada condição for verdadeira. Ele testa a condição antes de executar o corpo do loop.
3	PL / SQL PARA LOOP Execute uma sequência de instruções várias vezes e abrevia o código que gerencia a variável de loop.
4	Loops aninhados em PL / SQL Você pode usar um ou mais loop dentro de qualquer outro loop básico, while ou for.

Rotulando um PL / SQL Loop

Os loops PL / SQL podem ser rotulados. O rótulo deve ser colocado entre colchetes angulares duplos (<< e >>) e aparecer no início da instrução LOOP. O nome do rótulo também pode aparecer no final da instrução LOOP. Você pode usar o rótulo na instrução EXIT para sair do loop.

O programa a seguir ilustra o conceito -

DECLARE 
   i number(1); 
   j number(1); 
BEGIN 
   << outer_loop >> 
   FOR i IN 1..3 LOOP 
      << inner_loop >> 
      FOR j IN 1..3 LOOP 
         dbms_output.put_line('i is: '|| i || ' and j is: ' || j); 
      END loop inner_loop; 
   END loop outer_loop; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

i is: 1 and j is: 1 
i is: 1 and j is: 2 
i is: 1 and j is: 3 
i is: 2 and j is: 1 
i is: 2 and j is: 2 
i is: 2 and j is: 3 
i is: 3 and j is: 1 
i is: 3 and j is: 2 
i is: 3 and j is: 3  

PL/SQL procedure successfully completed.

As declarações de controle de loop

As instruções de controle de loop alteram a execução de sua sequência normal. Quando a execução deixa um escopo, todos os objetos automáticos que foram criados nesse escopo são destruídos.

PL / SQL oferece suporte às seguintes instruções de controle. Os loops de rotulagem também ajudam a tirar o controle de um loop. Clique nos links a seguir para verificar seus detalhes.

S.No	Declaração de controle e descrição
1	Declaração EXIT A instrução Exit completa o loop e o controle passa para a instrução imediatamente após END LOOP.
2	CONTINUE declaração Faz com que o loop pule o restante de seu corpo e teste novamente sua condição imediatamente antes de reiterar.
3	Declaração GOTO Transfere o controle para a instrução rotulada. Embora não seja aconselhável usar a instrução GOTO em seu programa.

A string em PL / SQL é, na verdade, uma sequência de caracteres com uma especificação de tamanho opcional. Os caracteres podem ser numéricos, letras, espaços em branco, caracteres especiais ou uma combinação de todos. PL / SQL oferece três tipos de strings -

• Fixed-length strings- Em tais strings, os programadores especificam o comprimento ao declarar a string. A string é preenchida à direita com espaços de acordo com o comprimento especificado.

• Variable-length strings - Em tais strings, um comprimento máximo de até 32.767, para a string é especificado e nenhum preenchimento ocorre.

• Character large objects (CLOBs) - São strings de comprimento variável que podem ter até 128 terabytes.

As strings PL / SQL podem ser variáveis ​​ou literais. Um literal de string é colocado entre aspas. Por exemplo,

'This is a string literal.' Or 'hello world'

Para incluir uma aspa simples dentro de um literal de string, você precisa digitar duas aspas simples uma ao lado da outra. Por exemplo,

'this isn''t what it looks like'

Declarando Variáveis ​​de String

O banco de dados Oracle fornece vários tipos de dados de string, como CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB e NCLOB. Os tipos de dados prefixados com um'N' está 'national character set' tipos de dados, que armazenam dados de caracteres Unicode.

Se você precisar declarar uma string de comprimento variável, deverá fornecer o comprimento máximo dessa string. Por exemplo, o tipo de dados VARCHAR2. O exemplo a seguir ilustra a declaração e o uso de algumas variáveis ​​de string -

DECLARE 
   name varchar2(20); 
   company varchar2(30); 
   introduction clob; 
   choice char(1); 
BEGIN 
   name := 'John Smith'; 
   company := 'Infotech'; 
   introduction := ' Hello! I''m John Smith from Infotech.'; 
   choice := 'y'; 
   IF choice = 'y' THEN 
      dbms_output.put_line(name); 
      dbms_output.put_line(company); 
      dbms_output.put_line(introduction); 
   END IF; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

John Smith 
Infotech
Hello! I'm John Smith from Infotech.  

PL/SQL procedure successfully completed

Para declarar uma string de comprimento fixo, use o tipo de dados CHAR. Aqui você não precisa especificar um comprimento máximo para uma variável de comprimento fixo. Se você ignorar a restrição de comprimento, o Oracle Database usará automaticamente um comprimento máximo necessário. As duas declarações a seguir são idênticas -

red_flag CHAR(1) := 'Y'; 
 red_flag CHAR   := 'Y';

Funções e operadores de string PL / SQL

PL / SQL oferece o operador de concatenação (||)para juntar duas cordas. A tabela a seguir fornece as funções de string fornecidas pela PL / SQL -

S.No	Função e objetivo
1	ASCII(x); Retorna o valor ASCII do caractere x.
2	CHR(x); Retorna o caractere com o valor ASCII de x.
3	CONCAT(x, y); Concatena as strings xey e retorna a string anexada.
4	INITCAP(x); Converte a letra inicial de cada palavra em x em maiúsculas e retorna essa string.
5	INSTR(x, find_string [, start] [, occurrence]); Pesquisas por find_string em xe retorna a posição em que ocorre.
6	INSTRB(x); Retorna a localização de uma string dentro de outra string, mas retorna o valor em bytes.
7	LENGTH(x); Retorna o número de caracteres em x.
8	LENGTHB(x); Retorna o comprimento de uma string de caracteres em bytes para o conjunto de caracteres de byte único.
9	LOWER(x); Converte as letras de x em minúsculas e retorna essa string.
10	LPAD(x, width [, pad_string]) ; Almofadas x com espaços à esquerda, para trazer o comprimento total da string para caracteres de largura.
11	LTRIM(x [, trim_string]); Corta caracteres à esquerda de x.
12	NANVL(x, value); Retorna o valor se x corresponder ao valor especial NaN (não um número), caso contrário x é devolvido.
13	NLS_INITCAP(x); Igual à função INITCAP, exceto que pode usar um método de classificação diferente, conforme especificado por NLSSORT.
14	NLS_LOWER(x) ; Igual à função LOWER, exceto que ela pode usar um método de classificação diferente, conforme especificado por NLSSORT.
15	NLS_UPPER(x); Igual à função UPPER, exceto que pode usar um método de classificação diferente, conforme especificado por NLSSORT.
16	NLSSORT(x); Altera o método de classificação dos personagens. Deve ser especificado antes de qualquer função NLS; caso contrário, a classificação padrão será usada.
17	NVL(x, value); Retorna valor se xé nulo; caso contrário, x é retornado.
18	NVL2(x, value1, value2); Retorna valor1 se x não for nulo; se x for nulo, valor2 será retornado.
19	REPLACE(x, search_string, replace_string); Pesquisas x para search_string e substitui-o por replace_string.
20	RPAD(x, width [, pad_string]); Almofadas x para a direita.
21	RTRIM(x [, trim_string]); Trims x da direita.
22	SOUNDEX(x) ; Retorna uma string contendo a representação fonética de x.
23	SUBSTR(x, start [, length]); Retorna uma substring de xque começa na posição especificada por start. Um comprimento opcional para a substring pode ser fornecido.
24	SUBSTRB(x); O mesmo que SUBSTR, exceto que os parâmetros são expressos em bytes em vez de caracteres para os sistemas de caracteres de byte único.
25	TRIM([trim_char FROM) x); Corta caracteres à esquerda e à direita de x.
26	UPPER(x); Converte as letras de x em maiúsculas e retorna essa string.

Vamos agora trabalhar com alguns exemplos para entender o conceito -

Exemplo 1

DECLARE 
   greetings varchar2(11) := 'hello world'; 
BEGIN 
   dbms_output.put_line(UPPER(greetings)); 
    
   dbms_output.put_line(LOWER(greetings)); 
    
   dbms_output.put_line(INITCAP(greetings)); 
    
   /* retrieve the first character in the string */ 
   dbms_output.put_line ( SUBSTR (greetings, 1, 1)); 
    
   /* retrieve the last character in the string */ 
   dbms_output.put_line ( SUBSTR (greetings, -1, 1)); 
    
   /* retrieve five characters,  
      starting from the seventh position. */ 
   dbms_output.put_line ( SUBSTR (greetings, 7, 5)); 
    
   /* retrieve the remainder of the string, 
      starting from the second position. */ 
   dbms_output.put_line ( SUBSTR (greetings, 2)); 
     
   /* find the location of the first "e" */ 
   dbms_output.put_line ( INSTR (greetings, 'e')); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

HELLO WORLD 
hello world 
Hello World 
h 
d 
World 
ello World 
2  

PL/SQL procedure successfully completed.

Exemplo 2

DECLARE 
   greetings varchar2(30) := '......Hello World.....'; 
BEGIN 
   dbms_output.put_line(RTRIM(greetings,'.')); 
   dbms_output.put_line(LTRIM(greetings, '.')); 
   dbms_output.put_line(TRIM( '.' from greetings)); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

......Hello World  
Hello World..... 
Hello World  

PL/SQL procedure successfully completed.

Neste capítulo, discutiremos arrays em PL / SQL. A linguagem de programação PL / SQL fornece uma estrutura de dados chamada deVARRAY, que pode armazenar uma coleção sequencial de tamanho fixo de elementos do mesmo tipo. Um varray é usado para armazenar uma coleção ordenada de dados; entretanto, geralmente é melhor pensar em uma matriz como uma coleção de variáveis ​​do mesmo tipo.

Todos os varrays consistem em localizações de memória contíguas. O endereço mais baixo corresponde ao primeiro elemento e o endereço mais alto ao último elemento.

Um array é uma parte dos dados do tipo de coleção e significa arrays de tamanho variável. Estudaremos outros tipos de coleção em um capítulo posterior'PL/SQL Collections'.

Cada elemento em um varraytem um índice associado a ele. Ele também tem um tamanho máximo que pode ser alterado dinamicamente.

Criação de um tipo Varray

Um tipo varray é criado com o CREATE TYPEdeclaração. Você deve especificar o tamanho máximo e o tipo de elementos armazenados no varray.

A sintaxe básica para criar um tipo VARRAY no nível do esquema é -

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

Onde,

• varray_type_name é um nome de atributo válido,
• n é o número de elementos (máximo) no varray,
• element_type é o tipo de dados dos elementos da matriz.

O tamanho máximo de um varray pode ser alterado usando o ALTER TYPE declaração.

Por exemplo,

CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10); 
/ 

Type created.

A sintaxe básica para criar um tipo VARRAY em um bloco PL / SQL é -

TYPE varray_type_name IS VARRAY(n) of <element_type>

Por exemplo -

TYPE namearray IS VARRAY(5) OF VARCHAR2(10); 
Type grades IS VARRAY(5) OF INTEGER;

Vamos agora trabalhar com alguns exemplos para entender o conceito -

Exemplo 1

O programa a seguir ilustra o uso de varrays -

DECLARE 
   type namesarray IS VARRAY(5) OF VARCHAR2(10); 
   type grades IS VARRAY(5) OF INTEGER; 
   names namesarray; 
   marks grades; 
   total integer; 
BEGIN 
   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i in 1 .. total LOOP 
      dbms_output.put_line('Student: ' || names(i) || ' 
      Marks: ' || marks(i)); 
   END LOOP; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Total 5 Students 
Student: Kavita  Marks: 98 
Student: Pritam  Marks: 97 
Student: Ayan  Marks: 78 
Student: Rishav  Marks: 87 
Student: Aziz  Marks: 92 

PL/SQL procedure successfully completed.

Please note -

• No ambiente Oracle, o índice inicial para varrays é sempre 1.

• Você pode inicializar os elementos varray usando o método construtor do tipo varray, que tem o mesmo nome do varray.

• Varrays são arranjos unidimensionais.

• Um varray é automaticamente NULL quando é declarado e deve ser inicializado antes que seus elementos possam ser referenciados.

Exemplo 2

Os elementos de um varray também podem ser um% ROWTYPE de qualquer tabela do banco de dados ou% TYPE de qualquer campo da tabela do banco de dados. O exemplo a seguir ilustra o conceito.

Usaremos a tabela CUSTOMERS armazenada em nosso banco de dados como -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

O exemplo a seguir faz uso de cursor, que você estudará em detalhes em um capítulo separado.

DECLARE 
   CURSOR c_customers is 
   SELECT  name FROM customers; 
   type c_list is varray (6) of customers.name%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter + 1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Customer('||counter ||'):'||name_list(counter)); 
   END LOOP; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed.

Neste capítulo, discutiremos procedimentos em PL / SQL. UMAsubprogramé uma unidade / módulo de programa que executa uma tarefa específica. Esses subprogramas são combinados para formar programas maiores. Isso é basicamente chamado de 'design modular'. Um subprograma pode ser invocado por outro subprograma ou programa que é chamado decalling program.

Um subprograma pode ser criado -

• No nível do esquema
• Dentro de um pacote
• Dentro de um bloco PL / SQL

No nível do esquema, o subprograma é um standalone subprogram. Ele é criado com a instrução CREATE PROCEDURE ou CREATE FUNCTION. Ele é armazenado no banco de dados e pode ser excluído com a instrução DROP PROCEDURE ou DROP FUNCTION.

Um subprograma criado dentro de um pacote é um packaged subprogram. Ele é armazenado no banco de dados e pode ser excluído apenas quando o pacote é excluído com a instrução DROP PACKAGE. Discutiremos os pacotes no capítulo'PL/SQL - Packages'.

Os subprogramas PL / SQL são chamados de blocos PL / SQL que podem ser chamados com um conjunto de parâmetros. PL / SQL fornece dois tipos de subprogramas -

• Functions- Esses subprogramas retornam um único valor; usado principalmente para calcular e retornar um valor.

• Procedures- Esses subprogramas não retornam um valor diretamente; usado principalmente para executar uma ação.

Este capítulo irá cobrir aspectos importantes de um PL/SQL procedure. Vamos discutirPL/SQL function no próximo capítulo.

Partes de um subprograma PL / SQL

Cada subprograma PL / SQL possui um nome e também pode ter uma lista de parâmetros. Como blocos PL / SQL anônimos, os blocos nomeados também terão as seguintes três partes -

S.No	Peças e Descrição
1	Declarative Part É uma parte opcional. No entanto, a parte declarativa de um subprograma não começa com a palavra-chave DECLARE. Ele contém declarações de tipos, cursores, constantes, variáveis, exceções e subprogramas aninhados. Esses itens são locais para o subprograma e deixam de existir quando o subprograma conclui a execução.
2	Executable Part Esta é uma parte obrigatória e contém instruções que executam a ação designada.
3	Exception-handling Novamente, esta é uma parte opcional. Ele contém o código que trata os erros de tempo de execução.

Criando um Procedimento

Um procedimento é criado com o CREATE OR REPLACE PROCEDUREdeclaração. A sintaxe simplificada para a instrução CREATE OR REPLACE PROCEDURE é a seguinte -

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name;

Onde,

• nome-do-procedimento especifica o nome do procedimento.

• A opção [OU REPLACE] permite a modificação de um procedimento existente.

• A lista de parâmetros opcional contém nome, modo e tipos de parâmetros. IN representa o valor que será passado de fora e OUT representa o parâmetro que será usado para retornar um valor fora do procedimento.

• o corpo do procedimento contém a parte executável.

• A palavra-chave AS é usada em vez da palavra-chave IS para criar um procedimento autônomo.

Exemplo

O exemplo a seguir cria um procedimento simples que exibe a string 'Hello World!' na tela quando executado.

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   dbms_output.put_line('Hello World!'); 
END; 
/

Quando o código acima é executado usando o prompt SQL, ele produzirá o seguinte resultado -

Procedure created.

Executando um Procedimento Independente

Um procedimento autônomo pode ser chamado de duas maneiras -

• Usando o EXECUTE palavra chave

• Chamando o nome do procedimento de um bloco PL / SQL

O procedimento acima denominado 'greetings' pode ser chamado com a palavra-chave EXECUTE como -

EXECUTE greetings;

A chamada acima exibirá -

Hello World

PL/SQL procedure successfully completed.

O procedimento também pode ser chamado de outro bloco PL / SQL -

BEGIN 
   greetings; 
END; 
/

A chamada acima exibirá -

Hello World  

PL/SQL procedure successfully completed.

Excluindo um Procedimento Independente

Um procedimento autônomo é excluído com o DROP PROCEDUREdeclaração. A sintaxe para excluir um procedimento é -

DROP PROCEDURE procedure-name;

Você pode cancelar o procedimento de saudação usando a seguinte instrução -

DROP PROCEDURE greetings;

Modos de parâmetro em subprogramas PL / SQL

A tabela a seguir lista os modos de parâmetro em subprogramas PL / SQL -

S.No	Modo e descrição do parâmetro
1	IN Um parâmetro IN permite que você passe um valor para o subprograma. It is a read-only parameter. Dentro do subprograma, um parâmetro IN atua como uma constante. Não pode ser atribuído um valor. Você pode passar uma constante, literal, variável inicializada ou expressão como um parâmetro IN. Você também pode inicializá-lo com um valor padrão; entretanto, nesse caso, ele é omitido da chamada do subprograma.It is the default mode of parameter passing. Parameters are passed by reference.
2	OUT Um parâmetro OUT retorna um valor para o programa de chamada. Dentro do subprograma, um parâmetro OUT atua como uma variável. Você pode alterar seu valor e fazer referência ao valor após atribuí-lo.The actual parameter must be variable and it is passed by value.
3	IN OUT A IN OUTparâmetro passa um valor inicial para um subprograma e retorna um valor atualizado para o chamador. Pode ser atribuído um valor e o valor pode ser lido. O parâmetro real correspondente a um parâmetro formal IN OUT deve ser uma variável, não uma constante ou uma expressão. Parâmetro formal deve receber um valor.Actual parameter is passed by value.

Modo IN e OUT Exemplo 1

Este programa encontra o mínimo de dois valores. Aqui, o procedimento pega dois números usando o modo IN e retorna o mínimo usando os parâmetros OUT.

DECLARE 
   a number; 
   b number; 
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 
BEGIN 
   IF x < y THEN 
      z:= x; 
   ELSE 
      z:= y; 
   END IF; 
END;   
BEGIN 
   a:= 23; 
   b:= 45; 
   findMin(a, b, c); 
   dbms_output.put_line(' Minimum of (23, 45) : ' || c); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Minimum of (23, 45) : 23  

PL/SQL procedure successfully completed.

Modo IN e OUT Exemplo 2

Este procedimento calcula o quadrado do valor de um valor passado. Este exemplo mostra como podemos usar o mesmo parâmetro para aceitar um valor e retornar outro resultado.

DECLARE 
   a number; 
PROCEDURE squareNum(x IN OUT number) IS 
BEGIN 
  x := x * x; 
END;  
BEGIN 
   a:= 23; 
   squareNum(a); 
   dbms_output.put_line(' Square of (23): ' || a); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Square of (23): 529 

PL/SQL procedure successfully completed.

Métodos para passar parâmetros

Os parâmetros reais podem ser passados ​​de três maneiras -

• Notação posicional
• Notação nomeada
• Notação mista

Notação Posicional

Em notação posicional, você pode chamar o procedimento como -

findMin(a, b, c, d);

Na notação posicional, o primeiro parâmetro real é substituído pelo primeiro parâmetro formal; o segundo parâmetro real é substituído pelo segundo parâmetro formal e assim por diante. Então,a é substituído por x, b é substituído por y, c é substituído por z e d é substituído por m.

Notação Nomeada

Na notação nomeada, o parâmetro real é associado ao parâmetro formal usando o arrow symbol ( => ). A chamada de procedimento será como a seguinte -

findMin(x => a, y => b, z => c, m => d);

Notação mista

Em notação mista, você pode misturar ambas as notações na chamada de procedimento; entretanto, a notação posicional deve preceder a notação nomeada.

A seguinte chamada é legal -

findMin(a, b, c, m => d);

No entanto, isso não é legal:

findMin(x => a, b, c, d);

Neste capítulo, discutiremos as funções em PL / SQL. Uma função é igual a um procedimento, exceto que retorna um valor. Portanto, todas as discussões do capítulo anterior também são verdadeiras para as funções.

Criação de uma função

Uma função autônoma é criada usando o CREATE FUNCTIONdeclaração. A sintaxe simplificada para oCREATE OR REPLACE PROCEDURE declaração é a seguinte -

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
BEGIN 
   < function_body > 
END [function_name];

Onde,

• nome-da-função especifica o nome da função.

• A opção [OU REPLACE] permite a modificação de uma função existente.

• A lista de parâmetros opcional contém nome, modo e tipos de parâmetros. IN representa o valor que será passado de fora e OUT representa o parâmetro que será usado para retornar um valor fora do procedimento.

• A função deve conter um return declaração.

• A cláusula RETURN especifica o tipo de dados que você retornará da função.

• function-body contém a parte executável.

• A palavra-chave AS é usada em vez da palavra-chave IS para criar uma função autônoma.

Exemplo

O exemplo a seguir ilustra como criar e chamar uma função autônoma. Esta função retorna o número total de CLIENTES na tabela de clientes.

Usaremos a tabela CUSTOMERS, que criamos no capítulo Variáveis ​​PL / SQL -

Select * from customers; 
 
+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

CREATE OR REPLACE FUNCTION totalCustomers 
RETURN number IS 
   total number(2) := 0; 
BEGIN 
   SELECT count(*) into total 
   FROM customers; 
    
   RETURN total; 
END; 
/

Quando o código acima é executado usando o prompt SQL, ele produzirá o seguinte resultado -

Function created.

Chamando uma função

Ao criar uma função, você dá uma definição do que a função deve fazer. Para usar uma função, você terá que chamar essa função para executar a tarefa definida. Quando um programa chama uma função, o controle do programa é transferido para a função chamada.

Uma função chamada executa a tarefa definida e quando sua instrução de retorno é executada ou quando o last end statement é alcançado, ele retorna o controle do programa de volta ao programa principal.

Para chamar uma função, você simplesmente precisa passar os parâmetros necessários junto com o nome da função e, se a função retornar um valor, você pode armazenar o valor retornado. O programa a seguir chama a funçãototalCustomers de um bloco anônimo -

DECLARE 
   c number(2); 
BEGIN 
   c := totalCustomers(); 
   dbms_output.put_line('Total no. of Customers: ' || c); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Total no. of Customers: 6  

PL/SQL procedure successfully completed.

Exemplo

O exemplo a seguir demonstra como declarar, definir e chamar uma função PL / SQL simples que calcula e retorna o máximo de dois valores.

DECLARE 
   a number; 
   b number; 
   c number; 
FUNCTION findMax(x IN number, y IN number)  
RETURN number 
IS 
    z number; 
BEGIN 
   IF x > y THEN 
      z:= x; 
   ELSE 
      Z:= y; 
   END IF;  
   RETURN z; 
END; 
BEGIN 
   a:= 23; 
   b:= 45;  
   c := findMax(a, b); 
   dbms_output.put_line(' Maximum of (23,45): ' || c); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Maximum of (23,45): 45   

PL/SQL procedure successfully completed.

Funções recursivas PL / SQL

Vimos que um programa ou subprograma pode chamar outro subprograma. Quando um subprograma chama a si mesmo, é referido como uma chamada recursiva e o processo é conhecido comorecursion.

Para ilustrar o conceito, vamos calcular o fatorial de um número. Fatorial de um número n é definido como -

n! = n*(n-1)! 
   = n*(n-1)*(n-2)! 
      ... 
   = n*(n-1)*(n-2)*(n-3)... 1

O programa a seguir calcula o fatorial de um determinado número chamando a si mesmo recursivamente -

DECLARE 
   num number; 
   factorial number;  
   
FUNCTION fact(x number) 
RETURN number  
IS 
   f number; 
BEGIN 
   IF x=0 THEN 
      f := 1; 
   ELSE 
      f := x * fact(x-1); 
   END IF; 
RETURN f; 
END;  

BEGIN 
   num:= 6; 
   factorial := fact(num); 
   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Factorial 6 is 720 
  
PL/SQL procedure successfully completed.

Neste capítulo, discutiremos os cursores em PL / SQL. O Oracle cria uma área de memória, conhecida como área de contexto, para processar uma instrução SQL, que contém todas as informações necessárias para o processamento da instrução; por exemplo, o número de linhas processadas, etc.

UMA cursoré um ponteiro para esta área de contexto. PL / SQL controla a área de contexto por meio de um cursor. Um cursor contém as linhas (uma ou mais) retornadas por uma instrução SQL. O conjunto de linhas que o cursor mantém é referido como oactive set.

Você pode nomear um cursor para que ele possa ser referido em um programa para buscar e processar as linhas retornadas pela instrução SQL, uma de cada vez. Existem dois tipos de cursores -

• Cursores implícitos
• Cursores explícitos

Cursores implícitos

Cursores implícitos são criados automaticamente pelo Oracle sempre que uma instrução SQL é executada, quando não há cursor explícito para a instrução. Os programadores não podem controlar os cursores implícitos e as informações nele contidas.

Sempre que uma instrução DML (INSERT, UPDATE e DELETE) é emitida, um cursor implícito é associado a essa instrução. Para operações INSERT, o cursor contém os dados que precisam ser inseridos. Para as operações UPDATE e DELETE, o cursor identifica as linhas que seriam afetadas.

Em PL / SQL, você pode se referir ao cursor implícito mais recente como o SQL cursor, que sempre tem atributos como %FOUND, %ISOPEN, %NOTFOUND, e %ROWCOUNT. O cursor SQL tem atributos adicionais,%BULK_ROWCOUNT e %BULK_EXCEPTIONS, projetado para uso com o FORALLdeclaração. A tabela a seguir fornece a descrição dos atributos mais usados ​​-

S.No	Atributo e descrição
1	%FOUND Retorna TRUE se uma instrução INSERT, UPDATE ou DELETE afetou uma ou mais linhas ou uma instrução SELECT INTO retornou uma ou mais linhas. Caso contrário, retorna FALSE.
2	%NOTFOUND O oposto lógico de% FOUND. Ele retorna TRUE se uma instrução INSERT, UPDATE ou DELETE não afetou nenhuma linha ou uma instrução SELECT INTO não retornou nenhuma linha. Caso contrário, retorna FALSE.
3	%ISOPEN Sempre retorna FALSE para cursores implícitos, porque o Oracle fecha o cursor SQL automaticamente após executar sua instrução SQL associada.
4	%ROWCOUNT Retorna o número de linhas afetadas por uma instrução INSERT, UPDATE ou DELETE, ou retornadas por uma instrução SELECT INTO.

Qualquer atributo do cursor SQL será acessado como sql%attribute_name conforme mostrado abaixo no exemplo.

Exemplo

Estaremos usando a tabela CLIENTES que criamos e usamos nos capítulos anteriores.

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

O programa a seguir atualizará a tabela e aumentará o salário de cada cliente em 500 e usará o SQL%ROWCOUNT atributo para determinar o número de linhas afetadas -

DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE customers 
   SET salary = salary + 500; 
   IF sql%notfound THEN 
      dbms_output.put_line('no customers selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected '); 
   END IF;  
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

6 customers selected  

PL/SQL procedure successfully completed.

Se você verificar os registros na tabela de clientes, verá que as linhas foram atualizadas -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 | 
|  2 | Khilan   |  25 | Delhi     |  2000.00 | 
|  3 | kaushik  |  23 | Kota      |  2500.00 | 
|  4 | Chaitali |  25 | Mumbai    |  7000.00 | 
|  5 | Hardik   |  27 | Bhopal    |  9000.00 | 
|  6 | Komal    |  22 | MP        |  5000.00 | 
+----+----------+-----+-----------+----------+

Cursores explícitos

Cursores explícitos são cursores definidos pelo programador para obter mais controle sobre o context area. Um cursor explícito deve ser definido na seção de declaração do Bloco PL / SQL. Ele é criado em uma instrução SELECT que retorna mais de uma linha.

A sintaxe para criar um cursor explícito é -

CURSOR cursor_name IS select_statement;

Trabalhar com um cursor explícito inclui as seguintes etapas -

• Declarando o cursor para inicializar a memória
• Abrindo o cursor para alocar a memória
• Buscando o cursor para recuperar os dados
• Fechando o cursor para liberar a memória alocada

Declarando o Cursor

Declarar o cursor define o cursor com um nome e a instrução SELECT associada. Por exemplo -

CURSOR c_customers IS 
   SELECT id, name, address FROM customers;

Abrindo o Cursor

Abrir o cursor aloca a memória para o cursor e o torna pronto para buscar as linhas retornadas pela instrução SQL nele. Por exemplo, vamos abrir o cursor definido acima da seguinte maneira -

OPEN c_customers;

Buscando o Cursor

Buscar o cursor envolve acessar uma linha por vez. Por exemplo, buscaremos linhas do cursor aberto acima da seguinte maneira -

FETCH c_customers INTO c_id, c_name, c_addr;

Fechando o Cursor

Fechar o cursor significa liberar a memória alocada. Por exemplo, fecharemos o cursor aberto acima da seguinte maneira -

CLOSE c_customers;

Exemplo

A seguir está um exemplo completo para ilustrar os conceitos de cursores explícitos do & minua;

DECLARE 
   c_id customers.id%type; 
   c_name customer.name%type; 
   c_addr customers.address%type; 
   CURSOR c_customers is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN c_customers; 
   LOOP 
   FETCH c_customers into c_id, c_name, c_addr; 
      EXIT WHEN c_customers%notfound; 
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
   END LOOP; 
   CLOSE c_customers; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

1 Ramesh Ahmedabad  
2 Khilan Delhi  
3 kaushik Kota     
4 Chaitali Mumbai  
5 Hardik Bhopal   
6 Komal MP  
  
PL/SQL procedure successfully completed.

Neste capítulo, discutiremos Registros em PL / SQL. UMArecordé uma estrutura de dados que pode conter itens de dados de diferentes tipos. Os registros consistem em campos diferentes, semelhantes a uma linha de uma tabela de banco de dados.

Por exemplo, você deseja manter o controle de seus livros em uma biblioteca. Você pode querer rastrear os seguintes atributos sobre cada livro, como Título, Autor, Assunto, ID do Livro. Um registro contendo um campo para cada um desses itens permite tratar um LIVRO como uma unidade lógica e permite organizar e representar melhor suas informações.

PL / SQL pode lidar com os seguintes tipos de registros -

• Table-based
• Registros baseados em cursor
• Registros definidos pelo usuário

Registros baseados em tabela

O atributo% ROWTYPE permite que um programador crie table-based e cursorbased registros.

O exemplo a seguir ilustra o conceito de table-basedregistros. Estaremos usando a tabela CLIENTES que criamos e usamos nos capítulos anteriores -

DECLARE 
   customer_rec customers%rowtype; 
BEGIN 
   SELECT * into customer_rec 
   FROM customers 
   WHERE id = 5;  
   dbms_output.put_line('Customer ID: ' || customer_rec.id); 
   dbms_output.put_line('Customer Name: ' || customer_rec.name); 
   dbms_output.put_line('Customer Address: ' || customer_rec.address); 
   dbms_output.put_line('Customer Salary: ' || customer_rec.salary); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Customer ID: 5 
Customer Name: Hardik 
Customer Address: Bhopal 
Customer Salary: 9000 
 
PL/SQL procedure successfully completed.

Registros baseados em cursor

O exemplo a seguir ilustra o conceito de cursor-basedregistros. Estaremos usando a tabela CLIENTES que criamos e usamos nos capítulos anteriores -

DECLARE 
   CURSOR customer_cur is 
      SELECT id, name, address  
      FROM customers; 
   customer_rec customer_cur%rowtype; 
BEGIN 
   OPEN customer_cur; 
   LOOP 
      FETCH customer_cur into customer_rec; 
      EXIT WHEN customer_cur%notfound; 
      DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name); 
   END LOOP; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

1 Ramesh 
2 Khilan 
3 kaushik 
4 Chaitali 
5 Hardik 
6 Komal  

PL/SQL procedure successfully completed.

Registros definidos pelo usuário

A PL / SQL fornece um tipo de registro definido pelo usuário que permite definir as diferentes estruturas de registro. Esses registros consistem em campos diferentes. Suponha que você queira manter o controle de seus livros em uma biblioteca. Você pode querer rastrear os seguintes atributos sobre cada livro -

• Title
• Author
• Subject
• ID do livro

Definindo um Registro

O tipo de registro é definido como -

TYPE 
type_name IS RECORD 
  ( field_name1  datatype1  [NOT NULL]  [:= DEFAULT EXPRESSION], 
   field_name2   datatype2   [NOT NULL]  [:= DEFAULT EXPRESSION], 
   ... 
   field_nameN  datatypeN  [NOT NULL]  [:= DEFAULT EXPRESSION); 
record-name  type_name;

O registro do livro é declarado da seguinte maneira -

DECLARE 
TYPE books IS RECORD 
(title  varchar(50), 
   author  varchar(50), 
   subject varchar(100), 
   book_id   number); 
book1 books; 
book2 books;

Acessando Campos

Para acessar qualquer campo de um registro, usamos o ponto (.)operador. O operador de acesso de membro é codificado como um período entre o nome da variável de registro e o campo que desejamos acessar. A seguir está um exemplo para explicar o uso do registro -

DECLARE 
   type books is record 
      (title varchar(50), 
      author varchar(50), 
      subject varchar(100), 
      book_id number); 
   book1 books; 
   book2 books; 
BEGIN 
   -- Book 1 specification 
   book1.title  := 'C Programming'; 
   book1.author := 'Nuha Ali ';  
   book1.subject := 'C Programming Tutorial'; 
   book1.book_id := 6495407;  
   -- Book 2 specification 
   book2.title := 'Telecom Billing'; 
   book2.author := 'Zara Ali'; 
   book2.subject := 'Telecom Billing Tutorial'; 
   book2.book_id := 6495700;  
  
  -- Print book 1 record 
   dbms_output.put_line('Book 1 title : '|| book1.title); 
   dbms_output.put_line('Book 1 author : '|| book1.author); 
   dbms_output.put_line('Book 1 subject : '|| book1.subject); 
   dbms_output.put_line('Book 1 book_id : ' || book1.book_id); 
   
   -- Print book 2 record 
   dbms_output.put_line('Book 2 title : '|| book2.title); 
   dbms_output.put_line('Book 2 author : '|| book2.author); 
   dbms_output.put_line('Book 2 subject : '|| book2.subject); 
   dbms_output.put_line('Book 2 book_id : '|| book2.book_id); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Book 1 title : C Programming 
Book 1 author : Nuha Ali 
Book 1 subject : C Programming Tutorial 
Book 1 book_id : 6495407 
Book 2 title : Telecom Billing 
Book 2 author : Zara Ali 
Book 2 subject : Telecom Billing Tutorial 
Book 2 book_id : 6495700  

PL/SQL procedure successfully completed.

Registros como parâmetros de subprograma

Você pode passar um registro como um parâmetro de subprograma da mesma forma que passa qualquer outra variável. Você também pode acessar os campos de registro da mesma forma que acessou no exemplo acima -

DECLARE 
   type books is record 
      (title  varchar(50), 
      author  varchar(50), 
      subject varchar(100), 
      book_id   number); 
   book1 books; 
   book2 books;  
PROCEDURE printbook (book books) IS 
BEGIN 
   dbms_output.put_line ('Book  title :  ' || book.title); 
   dbms_output.put_line('Book  author : ' || book.author); 
   dbms_output.put_line( 'Book  subject : ' || book.subject); 
   dbms_output.put_line( 'Book book_id : ' || book.book_id); 
END; 
   
BEGIN 
   -- Book 1 specification 
   book1.title  := 'C Programming'; 
   book1.author := 'Nuha Ali ';  
   book1.subject := 'C Programming Tutorial'; 
   book1.book_id := 6495407;
   
   -- Book 2 specification 
   book2.title := 'Telecom Billing'; 
   book2.author := 'Zara Ali'; 
   book2.subject := 'Telecom Billing Tutorial'; 
   book2.book_id := 6495700;  
   
   -- Use procedure to print book info 
   printbook(book1); 
   printbook(book2); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Book  title : C Programming 
Book  author : Nuha Ali 
Book subject : C Programming Tutorial 
Book  book_id : 6495407 
Book title : Telecom Billing 
Book author : Zara Ali 
Book subject : Telecom Billing Tutorial 
Book book_id : 6495700  

PL/SQL procedure successfully completed.

Neste capítulo, discutiremos Exceções em PL / SQL. Uma exceção é uma condição de erro durante a execução de um programa. PL / SQL oferece suporte a programadores para capturar tais condições usandoEXCEPTIONbloco no programa e uma ação apropriada é executada contra a condição de erro. Existem dois tipos de exceções -

• Exceções definidas pelo sistema
• Exceções definidas pelo usuário

Sintaxe para tratamento de exceções

A sintaxe geral para tratamento de exceções é a seguinte. Aqui você pode listar quantas exceções puder controlar. A exceção padrão será tratada usandoWHEN others THEN -

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)> 
EXCEPTION 
   <exception handling goes here > 
   WHEN exception1 THEN  
      exception1-handling-statements  
   WHEN exception2  THEN  
      exception2-handling-statements  
   WHEN exception3 THEN  
      exception3-handling-statements 
   ........ 
   WHEN others THEN 
      exception3-handling-statements 
END;

Exemplo

Vamos escrever um código para ilustrar o conceito. Estaremos usando a tabela CLIENTES que criamos e usamos nos capítulos anteriores -

DECLARE 
   c_id customers.id%type := 8; 
   c_name customerS.Name%type; 
   c_addr customers.address%type; 
BEGIN 
   SELECT  name, address INTO  c_name, c_addr 
   FROM customers 
   WHERE id = c_id;  
   DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name); 
   DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 

EXCEPTION 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such customer!'); 
   WHEN others THEN 
      dbms_output.put_line('Error!'); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

No such customer!  

PL/SQL procedure successfully completed.

O programa acima exibe o nome e endereço de um cliente cujo ID é fornecido. Como não há nenhum cliente com valor de ID 8 em nosso banco de dados, o programa levanta a exceção de tempo de execuçãoNO_DATA_FOUND, que é capturado no EXCEPTION block.

Levantando exceções

Exceções são levantadas pelo servidor de banco de dados automaticamente sempre que houver algum erro interno do banco de dados, mas as exceções podem ser levantadas explicitamente pelo programador usando o comando RAISE. A seguir está a sintaxe simples para levantar uma exceção -

DECLARE 
   exception_name EXCEPTION; 
BEGIN 
   IF condition THEN 
      RAISE exception_name; 
   END IF; 
EXCEPTION 
   WHEN exception_name THEN 
   statement; 
END;

Você pode usar a sintaxe acima para gerar a exceção padrão do Oracle ou qualquer exceção definida pelo usuário. Na próxima seção, daremos um exemplo de como gerar uma exceção definida pelo usuário. Você pode aumentar as exceções padrão do Oracle de maneira semelhante.

Exceções definidas pelo usuário

PL / SQL permite que você defina suas próprias exceções de acordo com a necessidade de seu programa. Uma exceção definida pelo usuário deve ser declarada e, em seguida, gerada explicitamente, usando uma instrução RAISE ou o procedimentoDBMS_STANDARD.RAISE_APPLICATION_ERROR.

A sintaxe para declarar uma exceção é -

DECLARE 
   my-exception EXCEPTION;

Exemplo

O exemplo a seguir ilustra o conceito. Este programa pede um ID de cliente, quando o usuário insere um ID inválido, a exceçãoinvalid_id é gerado.

DECLARE 
   c_id customers.id%type := &cc_id; 
   c_name customerS.Name%type; 
   c_addr customers.address%type;  
   -- user defined exception 
   ex_invalid_id  EXCEPTION; 
BEGIN 
   IF c_id <= 0 THEN 
      RAISE ex_invalid_id; 
   ELSE 
      SELECT  name, address INTO  c_name, c_addr 
      FROM customers 
      WHERE id = c_id;
      DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);  
      DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 
   END IF; 

EXCEPTION 
   WHEN ex_invalid_id THEN 
      dbms_output.put_line('ID must be greater than zero!'); 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such customer!'); 
   WHEN others THEN 
      dbms_output.put_line('Error!');  
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Enter value for cc_id: -6 (let's enter a value -6) 
old  2: c_id customers.id%type := &cc_id; 
new  2: c_id customers.id%type := -6; 
ID must be greater than zero! 
 
PL/SQL procedure successfully completed.

Exceções pré-definidas

A PL / SQL fornece muitas exceções predefinidas, que são executadas quando qualquer regra do banco de dados é violada por um programa. Por exemplo, a exceção predefinida NO_DATA_FOUND é gerada quando uma instrução SELECT INTO não retorna nenhuma linha. A tabela a seguir lista algumas das exceções predefinidas importantes -

Exceção	Erro Oracle	SQLCODE	Descrição
ACCESS_INTO_NULL	06530	-6530	Ele é gerado quando um objeto nulo é automaticamente atribuído a um valor.
CASE_NOT_FOUND	06592	-6592	Ele é gerado quando nenhuma das opções na cláusula WHEN de uma instrução CASE é selecionada e não há nenhuma cláusula ELSE.
COLLECTION_IS_NULL	06531	-6531	Ele é gerado quando um programa tenta aplicar métodos de coleta diferentes de EXISTS a uma tabela ou varray aninhada não inicializada, ou o programa tenta atribuir valores aos elementos de uma tabela ou varray aninhada não inicializada.
DUP_VAL_ON_INDEX	00001	-1	Ele é gerado quando valores duplicados são tentados a serem armazenados em uma coluna com índice exclusivo.
INVALID_CURSOR	01001	-1001	Ele é gerado quando são feitas tentativas de fazer uma operação de cursor que não é permitida, como fechar um cursor não aberto.
NÚMERO INVÁLIDO	01722	-1722	Ele é gerado quando a conversão de uma string de caracteres em um número falha porque a string não representa um número válido.
LOGIN_DENIED	01017	-1017	É gerado quando um programa tenta fazer logon no banco de dados com um nome de usuário ou senha inválidos.
NENHUM DADO ENCONTRADO	01403	+100	Ele é gerado quando uma instrução SELECT INTO não retorna nenhuma linha.
NOT_LOGGED_ON	01012	-1012	Ele é gerado quando uma chamada de banco de dados é emitida sem estar conectada ao banco de dados.
PROGRAM_ERROR	06501	-6501	Ele é gerado quando o PL / SQL tem um problema interno.
ROWTYPE_MISMATCH	06504	-6504	Ele é gerado quando um cursor busca o valor em uma variável com tipo de dados incompatível.
SELF_IS_NULL	30625	-30625	Ele é gerado quando um método de membro é chamado, mas a instância do tipo de objeto não foi inicializada.
STORAGE_ERROR	06500	-6500	Ele é gerado quando o PL / SQL fica sem memória ou a memória é corrompida.
TOO_MANY_ROWS	01422	-1422	É gerado quando uma instrução SELECT INTO retorna mais de uma linha.
VALUE_ERROR	06502	-6502	É gerado quando ocorre um erro aritmético, de conversão, truncamento ou restrição de tamanho.
DIVISÃO POR ZERO	01476	1476	Ele é gerado quando é feita uma tentativa de dividir um número por zero.

Neste capítulo, discutiremos Triggers em PL / SQL. Os gatilhos são programas armazenados, que são executados ou disparados automaticamente quando ocorrem alguns eventos. Os gatilhos são, na verdade, escritos para serem executados em resposta a qualquer um dos seguintes eventos -

• UMA database manipulation (DML) declaração (DELETE, INSERT ou UPDATE)

• UMA database definition (DDL) instrução (CREATE, ALTER ou DROP).

• UMA database operation (SERVERERROR, LOGON, LOGOFF, STARTUP ou SHUTDOWN).

Os gatilhos podem ser definidos na tabela, visão, esquema ou banco de dados ao qual o evento está associado.

Benefícios dos gatilhos

Os gatilhos podem ser escritos para os seguintes propósitos -

• Gerando alguns valores de coluna derivados automaticamente
• Impondo integridade referencial
• Registro de eventos e armazenamento de informações sobre o acesso à mesa
• Auditing
• Replicação síncrona de tabelas
• Impondo autorizações de segurança
• Prevenir transações inválidas

Criação de gatilhos

A sintaxe para criar um gatilho é -

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END;

Onde,

• CREATE [OR REPLACE] TRIGGER trigger_name - Cria ou substitui um trigger existente pelo trigger_name .

• {ANTES | DEPOIS | INSTEAD OF} - especifica quando o gatilho será executado. A cláusula INSTEAD OF é usada para criar gatilhos em uma visão.

• {INSERT [OU] | ATUALIZAR [OU] | DELETE} - Isso especifica a operação DML.

• [OF nome_coluna] - Especifica o nome da coluna que será atualizado.

• [ON nome_tabela] - Especifica o nome da tabela associada ao acionador.

• [REFERENCING OLD AS o NEW AS n] - Isso permite que você faça referência a valores novos e antigos para várias instruções DML, como INSERT, UPDATE e DELETE.

• [PARA CADA LINHA] - Especifica um acionador no nível da linha, ou seja, o acionador será executado para cada linha afetada. Caso contrário, o gatilho será executado apenas uma vez quando a instrução SQL for executada, o que é chamado de gatilho de nível de tabela.

• WHEN (condição) - fornece uma condição para as linhas para as quais o acionador seria disparado. Esta cláusula é válida apenas para gatilhos em nível de linha.

Exemplo

Para começar, usaremos a tabela CLIENTES que criamos e usamos nos capítulos anteriores -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

O programa a seguir cria um row-levelgatilho para a tabela de clientes que seria acionado para as operações INSERT, UPDATE ou DELETE executadas na tabela CUSTOMERS. Este gatilho exibirá a diferença salarial entre os valores antigos e os novos -

CREATE OR REPLACE TRIGGER display_salary_changes 
BEFORE DELETE OR INSERT OR UPDATE ON customers 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   sal_diff number; 
BEGIN 
   sal_diff := :NEW.salary  - :OLD.salary; 
   dbms_output.put_line('Old salary: ' || :OLD.salary); 
   dbms_output.put_line('New salary: ' || :NEW.salary); 
   dbms_output.put_line('Salary difference: ' || sal_diff); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Trigger created.

Os seguintes pontos devem ser considerados aqui -

• Referências OLD e NEW não estão disponíveis para gatilhos de nível de tabela, em vez disso, você pode usá-las para gatilhos de nível de registro.

• Se você deseja consultar a tabela no mesmo gatilho, deve usar a palavra-chave AFTER, porque os gatilhos podem consultar a tabela ou alterá-la novamente somente após as alterações iniciais serem aplicadas e a tabela voltar a um estado consistente.

• O gatilho acima foi escrito de forma que será acionado antes de qualquer operação DELETE, INSERT ou UPDATE na tabela, mas você pode escrever seu gatilho em uma ou várias operações, por exemplo BEFORE DELETE, que será acionado sempre que um registro será excluído usando a operação DELETE na tabela.

Disparando um gatilho

Vamos realizar algumas operações DML na tabela CUSTOMERS. Aqui está uma instrução INSERT, que criará um novo registro na tabela -

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

Quando um registro é criado na tabela CLIENTES, o gatilho de criação acima, display_salary_changes será disparado e exibirá o seguinte resultado -

Old salary: 
New salary: 7500 
Salary difference:

Como este é um novo registro, o salário anterior não está disponível e o resultado acima é nulo. Vamos agora realizar mais uma operação DML na tabela CUSTOMERS. A instrução UPDATE atualizará um registro existente na tabela -

UPDATE customers 
SET salary = salary + 500 
WHERE id = 2;

Quando um registro é atualizado na tabela CUSTOMERS, o gatilho de criação acima, display_salary_changes será disparado e exibirá o seguinte resultado -

Old salary: 1500 
New salary: 2000 
Salary difference: 500

Neste capítulo, discutiremos os Pacotes em PL / SQL. Pacotes são objetos de esquema que agrupam tipos, variáveis ​​e subprogramas PL / SQL relacionados logicamente.

Um pacote terá duas partes obrigatórias -

• Especificação do pacote
• Corpo do pacote ou definição

Especificação do Pacote

A especificação é a interface para o pacote. É sóDECLARESos tipos, variáveis, constantes, exceções, cursores e subprogramas que podem ser referenciados de fora do pacote. Em outras palavras, contém todas as informações sobre o conteúdo do pacote, mas exclui o código dos subprogramas.

Todos os objetos colocados na especificação são chamados publicobjetos. Qualquer subprograma que não esteja na especificação do pacote, mas codificado no corpo do pacote, é chamado deprivate objeto.

O fragmento de código a seguir mostra uma especificação de pacote com um único procedimento. Você pode ter muitas variáveis ​​globais definidas e vários procedimentos ou funções dentro de um pacote.

CREATE PACKAGE cust_sal AS 
   PROCEDURE find_sal(c_id customers.id%type); 
END cust_sal; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Package created.

Corpo da embalagem

O corpo do pacote contém os códigos para vários métodos declarados na especificação do pacote e outras declarações privadas, que estão ocultas do código fora do pacote.

o CREATE PACKAGE BODYA instrução é usada para criar o corpo do pacote. O seguinte trecho de código mostra a declaração do corpo do pacote para ocust_salpacote criado acima. Presumi que já temos a tabela CUSTOMERS criada em nosso banco de dados conforme mencionado no capítulo PL / SQL - Variáveis .

CREATE OR REPLACE PACKAGE BODY cust_sal AS  
   
   PROCEDURE find_sal(c_id customers.id%TYPE) IS 
   c_sal customers.salary%TYPE; 
   BEGIN 
      SELECT salary INTO c_sal 
      FROM customers 
      WHERE id = c_id; 
      dbms_output.put_line('Salary: '|| c_sal); 
   END find_sal; 
END cust_sal; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Package body created.

Usando os Elementos do Pacote

Os elementos do pacote (variáveis, procedimentos ou funções) são acessados ​​com a seguinte sintaxe -

package_name.element_name;

Considere, já criamos o pacote acima em nosso esquema de banco de dados, o programa a seguir usa o find_sal método do cust_sal pacote -

DECLARE 
   code customers.id%type := &cc_id; 
BEGIN 
   cust_sal.find_sal(code); 
END; 
/

Quando o código acima é executado no prompt SQL, ele pede para inserir o ID do cliente e quando você insere um ID, ele exibe o salário correspondente da seguinte forma -

Enter value for cc_id: 1 
Salary: 3000 

PL/SQL procedure successfully completed.

Exemplo

O programa a seguir fornece um pacote mais completo. Usaremos a tabela CUSTOMERS armazenada em nosso banco de dados com os seguintes registros -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  3000.00 | 
|  2 | Khilan   |  25 | Delhi     |  3000.00 | 
|  3 | kaushik  |  23 | Kota      |  3000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  7500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  9500.00 | 
|  6 | Komal    |  22 | MP        |  5500.00 | 
+----+----------+-----+-----------+----------+

A Especificação do Pacote

CREATE OR REPLACE PACKAGE c_package AS 
   -- Adds a customer 
   PROCEDURE addCustomer(c_id   customers.id%type, 
   c_name  customerS.No.ame%type, 
   c_age  customers.age%type, 
   c_addr customers.address%type,  
   c_sal  customers.salary%type); 
   
   -- Removes a customer 
   PROCEDURE delCustomer(c_id  customers.id%TYPE); 
   --Lists all customers 
   PROCEDURE listCustomer; 
  
END c_package; 
/

Quando o código acima é executado no prompt SQL, ele cria o pacote acima e exibe o seguinte resultado -

Package created.

Criando o Corpo do Pacote

CREATE OR REPLACE PACKAGE BODY c_package AS 
   PROCEDURE addCustomer(c_id  customers.id%type, 
      c_name customerS.No.ame%type, 
      c_age  customers.age%type, 
      c_addr  customers.address%type,  
      c_sal   customers.salary%type) 
   IS 
   BEGIN 
      INSERT INTO customers (id,name,age,address,salary) 
         VALUES(c_id, c_name, c_age, c_addr, c_sal); 
   END addCustomer; 
   
   PROCEDURE delCustomer(c_id   customers.id%type) IS 
   BEGIN 
      DELETE FROM customers 
      WHERE id = c_id; 
   END delCustomer;  
   
   PROCEDURE listCustomer IS 
   CURSOR c_customers is 
      SELECT  name FROM customers; 
   TYPE c_list is TABLE OF customers.Name%type;  
   name_list c_list := c_list(); 
   counter integer :=0; 
   BEGIN 
      FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter) := n.name; 
      dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter)); 
      END LOOP; 
   END listCustomer;
   
END c_package; 
/

O exemplo acima faz uso do nested table. Discutiremos o conceito de tabela aninhada no próximo capítulo.

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Package body created.

Usando o pacote

O programa a seguir usa os métodos declarados e definidos no pacote c_package .

DECLARE 
   code customers.id%type:= 8; 
BEGIN 
   c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500); 
   c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500); 
   c_package.listcustomer; 
   c_package.delcustomer(code); 
   c_package.listcustomer; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal 
Customer(7): Rajnish 
Customer(8): Subham 
Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal
Customer(7): Rajnish 

PL/SQL procedure successfully completed

Neste capítulo, discutiremos as Coleções em PL / SQL. Uma coleção é um grupo ordenado de elementos com o mesmo tipo de dados. Cada elemento é identificado por um subscrito exclusivo que representa sua posição na coleção.

PL / SQL fornece três tipos de coleção -

• Tabelas de indexação ou matriz associativa
• Mesa aninhada
• Matriz de tamanho variável ou Varray

A documentação da Oracle fornece as seguintes características para cada tipo de coleção -

Tipo de coleção	Número de Elementos	Tipo de Subscrito	Denso ou esparso	Onde foi criado	Pode ser atributo de tipo de objeto
Matriz associativa (ou índice por tabela)	Sem limites	String ou inteiro	Ou	Apenas em bloco PL / SQL	Não
Mesa aninhada	Sem limites	Inteiro	Começa denso, pode se tornar esparso	Tanto no bloco PL / SQL quanto no nível do esquema	sim
Matriz de tamanho variável (Varray)	Delimitado	Inteiro	Sempre denso	Tanto no bloco PL / SQL quanto no nível do esquema	sim

Já discutimos varray no capítulo 'PL/SQL arrays'. Neste capítulo, discutiremos as tabelas PL / SQL.

Ambos os tipos de tabelas PL / SQL, ou seja, as tabelas index-by e as tabelas aninhadas têm a mesma estrutura e suas linhas são acessadas usando a notação de subscrito. No entanto, esses dois tipos de tabelas diferem em um aspecto; as tabelas aninhadas podem ser armazenadas em uma coluna do banco de dados e as tabelas index-by não podem.

Índice por tabela

A index-by mesa (também chamada de associative array) é um conjunto de key-valuepares. Cada chave é única e é usada para localizar o valor correspondente. A chave pode ser um número inteiro ou uma string.

Uma tabela index-by é criada usando a seguinte sintaxe. Aqui, estamos criando umindex-by mesa chamada table_name, as chaves serão do subscript_type e os valores associados serão do element_type

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; 
 
table_name type_name;

Exemplo

O exemplo a seguir mostra como criar uma tabela para armazenar valores inteiros junto com nomes e posteriormente imprimir a mesma lista de nomes.

DECLARE 
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); 
   salary_list salary; 
   name   VARCHAR2(20); 
BEGIN 
   -- adding elements to the table 
   salary_list('Rajnish') := 62000; 
   salary_list('Minakshi') := 75000; 
   salary_list('Martin') := 100000; 
   salary_list('James') := 78000;  
   
   -- printing the table 
   name := salary_list.FIRST; 
   WHILE name IS NOT null LOOP 
      dbms_output.put_line 
      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name))); 
      name := salary_list.NEXT(name); 
   END LOOP; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Salary of James is 78000 
Salary of Martin is 100000 
Salary of Minakshi is 75000 
Salary of Rajnish is 62000  

PL/SQL procedure successfully completed.

Exemplo

Os elementos de uma tabela index-by também podem ser um %ROWTYPE de qualquer tabela de banco de dados ou %TYPEde qualquer campo da tabela do banco de dados. O exemplo a seguir ilustra o conceito. Vamos usar oCUSTOMERS tabela armazenada em nosso banco de dados como -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

DECLARE 
   CURSOR c_customers is 
      select name from customers; 

   TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer; 
   name_list c_list; 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list(counter) := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_lis t(counter)); 
   END LOOP; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed

Mesas Aninhadas

UMA nested tableé como uma matriz unidimensional com um número arbitrário de elementos. No entanto, uma tabela aninhada difere de uma matriz nos seguintes aspectos -

• Uma matriz tem um número declarado de elementos, mas uma tabela aninhada não. O tamanho de uma tabela aninhada pode aumentar dinamicamente.

• Um array é sempre denso, ou seja, sempre possui subscritos consecutivos. Uma matriz aninhada é inicialmente densa, mas pode se tornar esparsa quando os elementos são excluídos dela.

Uma tabela aninhada é criada usando a seguinte sintaxe -

TYPE type_name IS TABLE OF element_type [NOT NULL]; 
 
table_name type_name;

Esta declaração é semelhante à declaração de um index-by mesa, mas não há INDEX BY cláusula.

Uma tabela aninhada pode ser armazenada em uma coluna do banco de dados. Ele também pode ser usado para simplificar as operações SQL em que você une uma tabela de coluna única a uma tabela maior. Uma matriz associativa não pode ser armazenada no banco de dados.

Exemplo

Os exemplos a seguir ilustram o uso de tabela aninhada -

DECLARE 
   TYPE names_table IS TABLE OF VARCHAR2(10); 
   TYPE grades IS TABLE OF INTEGER;  
   names names_table; 
   marks grades; 
   total integer; 
BEGIN 
   names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i IN 1 .. total LOOP 
      dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i)); 
   end loop; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Total 5 Students 
Student:Kavita, Marks:98 
Student:Pritam, Marks:97 
Student:Ayan, Marks:78 
Student:Rishav, Marks:87 
Student:Aziz, Marks:92  

PL/SQL procedure successfully completed.

Exemplo

Elementos de um nested table também pode ser um %ROWTYPEde qualquer tabela do banco de dados ou% TYPE de qualquer campo da tabela do banco de dados. O exemplo a seguir ilustra o conceito. Usaremos a tabela CUSTOMERS armazenada em nosso banco de dados como -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

DECLARE 
   CURSOR c_customers is  
      SELECT  name FROM customers;  
   TYPE c_list IS TABLE of customerS.No.ame%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); 
   END LOOP; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed.

Métodos de coleta

A PL / SQL fornece os métodos de coleta integrados que tornam as coleções mais fáceis de usar. A tabela a seguir lista os métodos e sua finalidade -

S.No	Nome e objetivo do método
1	EXISTS(n) Retorna TRUE se o enésimo elemento em uma coleção existir; caso contrário, retorna FALSE.
2	COUNT Retorna o número de elementos que uma coleção contém atualmente.
3	LIMIT Verifica o tamanho máximo de uma coleção.
4	FIRST Retorna os primeiros (menores) números de índice em uma coleção que usa os subscritos inteiros.
5	LAST Retorna os últimos (maiores) números de índice em uma coleção que usa os subscritos inteiros.
6	PRIOR(n) Retorna o número do índice que precede o índice n em uma coleção.
7	NEXT(n) Retorna o número do índice que sucede ao índice n.
8	EXTEND Acrescenta um elemento nulo a uma coleção.
9	EXTEND(n) Acrescenta n elementos nulos a uma coleção.
10	EXTEND(n,i) Anexos ncópias do i ésimo elemento para uma coleção.
11	TRIM Remove um elemento do final de uma coleção.
12	TRIM(n) Remove n elementos do final de uma coleção.
13	DELETE Remove todos os elementos de uma coleção, definindo COUNT como 0.
14	DELETE(n) Remove o nthelemento de uma matriz associativa com uma chave numérica ou uma tabela aninhada. Se a matriz associativa tiver uma chave de string, o elemento correspondente ao valor da chave será excluído. E sen é nulo, DELETE(n) faz nada.
15	DELETE(m,n) Remove todos os elementos do intervalo m..nde uma matriz associativa ou tabela aninhada. E sem é maior que n ou se m ou n é nulo, DELETE(m,n) faz nada.

Exceções de coleção

A tabela a seguir fornece as exceções de coleção e quando elas são levantadas -

Exceção de coleção	Criado em Situações
COLLECTION_IS_NULL	Você tenta operar em uma coleção atomicamente nula.
NENHUM DADO ENCONTRADO	Um subscrito designa um elemento que foi excluído ou um elemento inexistente de uma matriz associativa.
SUBSCRIPT_BEYOND_COUNT	Um subscrito excede o número de elementos em uma coleção.
SUBSCRIPT_OUTSIDE_LIMIT	Um subscrito está fora do intervalo permitido.
VALUE_ERROR	Um subscrito é nulo ou não conversível para o tipo de chave. Esta exceção pode ocorrer se a chave for definida como umPLS_INTEGER intervalo, e o subscrito está fora desse intervalo.

Neste capítulo, discutiremos as transações em PL / SQL. Um banco de dadostransactioné uma unidade atômica de trabalho que pode consistir em uma ou mais instruções SQL relacionadas. É chamado de atômico porque as modificações do banco de dados causadas pelas instruções SQL que constituem uma transação podem ser coletivamente confirmadas, ou seja, tornadas permanentes no banco de dados ou revertidas (desfeitas) do banco de dados.

Uma instrução SQL executada com sucesso e uma transação confirmada não são iguais. Mesmo se uma instrução SQL for executada com sucesso, a menos que a transação que contém a instrução seja confirmada, ela pode ser revertida e todas as alterações feitas pela (s) instrução (ões) podem ser desfeitas.

Iniciando e terminando uma transação

Uma transação tem um beginning e um end. Uma transação começa quando um dos seguintes eventos ocorre -

• A primeira instrução SQL é executada após a conexão com o banco de dados.

• A cada nova instrução SQL emitida após a conclusão de uma transação.

Uma transação termina quando um dos seguintes eventos ocorre -

• UMA COMMIT ou um ROLLBACK declaração é emitida.

• UMA DDL declaração, como CREATE TABLEdeclaração, é emitida; porque nesse caso um COMMIT é executado automaticamente.

• UMA DCL declaração, como um GRANTdeclaração, é emitida; porque nesse caso um COMMIT é executado automaticamente.

• O usuário se desconecta do banco de dados.

• O usuário sai de SQL*PLUS emitindo o EXIT comando, um COMMIT é executado automaticamente.

• SQL * Plus termina de forma anormal, um ROLLBACK é executado automaticamente.

• UMA DMLdeclaração falha; nesse caso, um ROLLBACK é executado automaticamente para desfazer a instrução DML.

Comprometendo uma transação

Uma transação torna-se permanente emitindo o comando SQL COMMIT. A sintaxe geral do comando COMMIT é -

COMMIT;

Por exemplo,

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Komal', 22, 'MP', 4500.00 ); 

COMMIT;

Revertendo transações

As alterações feitas no banco de dados sem COMMIT podem ser desfeitas com o comando ROLLBACK.

A sintaxe geral para o comando ROLLBACK é -

ROLLBACK [TO SAVEPOINT < savepoint_name>];

Quando uma transação é abortada devido a alguma situação sem precedentes, como falha do sistema, toda a transação desde a confirmação é automaticamente revertida. Se você não está usandosavepoint, então simplesmente use a seguinte instrução para reverter todas as alterações -

ROLLBACK;

Savepoints

Os pontos de salvamento são uma espécie de marcadores que ajudam a dividir uma transação longa em unidades menores, definindo alguns pontos de verificação. Ao definir pontos de salvamento em uma transação longa, você pode reverter para um ponto de verificação, se necessário. Isso é feito emitindo oSAVEPOINT comando.

A sintaxe geral para o comando SAVEPOINT é -

SAVEPOINT < savepoint_name >;

Por exemplo

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Rajnish', 27, 'HP', 9500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (8, 'Riddhi', 21, 'WB', 4500.00 ); 
SAVEPOINT sav1;
  
UPDATE CUSTOMERS 
SET SALARY = SALARY + 1000; 
ROLLBACK TO sav1;
  
UPDATE CUSTOMERS 
SET SALARY = SALARY + 1000 
WHERE ID = 7; 
UPDATE CUSTOMERS 
SET SALARY = SALARY + 1000 
WHERE ID = 8; 

COMMIT;

ROLLBACK TO sav1 - Esta instrução reverte todas as alterações até o ponto em que você marcou o savepoint sav1.

Depois disso, as novas alterações que você fizer começarão.

Controle Automático de Transação

Para executar um COMMIT automaticamente sempre que um INSERT, UPDATE ou DELETE comando é executado, você pode definir o AUTOCOMMIT variável de ambiente como -

SET AUTOCOMMIT ON;

Você pode desligar o modo de confirmação automática usando o seguinte comando -

SET AUTOCOMMIT OFF;

Neste capítulo, discutiremos a Data e a Hora em PL / SQL. Existem duas classes de tipos de dados relacionados a data e hora no PL / SQL -

• Tipos de dados de data e hora
• Tipos de dados de intervalo

Os tipos de dados Datetime são -

• DATE
• TIMESTAMP
• TIMESTAMP COM FUSO HORÁRIO
• TIMESTAMP COM FUSO HORÁRIO LOCAL

Os tipos de dados de intervalo são -

• INTERVALO ANO A MÊS
• INTERVAL DAY TO SECOND

Valores de campo para tipos de dados de data e hora e intervalo

Ambos datetime e interval tipos de dados consistem em fields. Os valores desses campos determinam o valor do tipo de dados. A tabela a seguir lista os campos e seus valores possíveis para datas e intervalos.

Nome do Campo	Valores válidos de data e hora	Valores de intervalo válidos
ANO	-4712 a 9999 (excluindo ano 0)	Qualquer número inteiro diferente de zero
MÊS	01 a 12	0 a 11
DIA	01 a 31 (limitado pelos valores de MÊS e ANO, conforme regras do calendário do local)	Qualquer número inteiro diferente de zero
HORA	00 a 23	0 a 23
MINUTO	00 a 59	0 a 59
SEGUNDO	00 a 59,9 (n), onde 9 (n) é a precisão dos segundos fracionários de tempo A parcela 9 (n) não é aplicável para DATA.	0 a 59,9 (n), onde 9 (n) é a precisão do intervalo de segundos fracionários
TIMEZONE_HOUR	-12 a 14 (a faixa acomoda as mudanças do horário de verão) Não aplicável para DATE ou TIMESTAMP.	Não aplicável
TIMEZONE_MINUTE	00 a 59 Não aplicável para DATE ou TIMESTAMP.	Não aplicável
TIMEZONE_REGION	Não aplicável para DATE ou TIMESTAMP.	Não aplicável
TIMEZONE_ABBR	Não aplicável para DATE ou TIMESTAMP.	Não aplicável

Os tipos de dados e funções de data e hora

A seguir estão os tipos de dados Datetime -

ENCONTRO

Ele armazena informações de data e hora em tipos de dados de caracteres e números. É feito de informações sobre século, ano, mês, data, hora, minuto e segundo. É especificado como -

TIMESTAMP

É uma extensão do tipo de dados DATE. Ele armazena o ano, mês e dia do tipo de dados DATE, junto com os valores de hora, minuto e segundo. É útil para armazenar valores de tempo precisos.

TIMESTAMP COM FUSO HORÁRIO

É uma variante de TIMESTAMP que inclui um nome de região de fuso horário ou um deslocamento de fuso horário em seu valor. A diferença de fuso horário é a diferença (em horas e minutos) entre a hora local e UTC. Este tipo de dados é útil para coletar e avaliar informações de data em regiões geográficas.

TIMESTAMP COM FUSO HORÁRIO LOCAL

É outra variante de TIMESTAMP que inclui um deslocamento de fuso horário em seu valor.

A tabela a seguir fornece as funções Datetime (em que x tem o valor datetime) -

S.No	Nome e descrição da função
1	ADD_MONTHS(x, y); Adiciona y meses para x.
2	LAST_DAY(x); Retorna o último dia do mês.
3	MONTHS_BETWEEN(x, y); Retorna o número de meses entre x e y.
4	NEXT_DAY(x, day); Retorna a data e hora do dia seguinte apósx.
5	NEW_TIME; Retorna o valor de hora / dia de um fuso horário especificado pelo usuário.
6	ROUND(x [, unit]); Rodadas x.
7	SYSDATE(); Retorna a data e hora atual.
8	TRUNC(x [, unit]); Trunca x.

Funções de carimbo de data / hora (em que x tem um valor de carimbo de data / hora) -

S.No	Nome e descrição da função
1	CURRENT_TIMESTAMP(); Retorna um TIMESTAMP WITH TIME ZONE contendo o tempo da sessão atual junto com o fuso horário da sessão.
2	EXTRACT({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | } TIMEZONE_ABBR ) FROM x) Extrai e retorna um ano, mês, dia, hora, minuto, segundo ou fuso horário de x.
3	FROM_TZ(x, time_zone); Converte o TIMESTAMP xeo fuso horário especificado por time_zone em um TIMESTAMP WITH TIMEZONE.
4	LOCALTIMESTAMP(); Retorna um TIMESTAMP contendo a hora local no fuso horário da sessão.
5	SYSTIMESTAMP(); Retorna um TIMESTAMP WITH TIME ZONE contendo a hora atual do banco de dados junto com o fuso horário do banco de dados.
6	SYS_EXTRACT_UTC(x); Converte o TIMESTAMP WITH TIMEZONE x em um TIMESTAMP contendo a data e a hora em UTC.
7	TO_TIMESTAMP(x, [format]); Converte a string x em um TIMESTAMP.
8	TO_TIMESTAMP_TZ(x, [format]); Converte a string x em um TIMESTAMP WITH TIMEZONE.

Exemplos

Os seguintes snippets de código ilustram o uso das funções acima -

Example 1

SELECT SYSDATE FROM DUAL;

Output -

08/31/2012 5:25:34 PM

Example 2

SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS') FROM DUAL;

Output -

31-08-2012 05:26:14

Example 3

SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;

Output -

01/31/2013 5:26:31 PM

Example 4

SELECT LOCALTIMESTAMP FROM DUAL;

Output -

8/31/2012 5:26:55.347000 PM

Os tipos de dados e funções do intervalo

A seguir estão os tipos de dados de intervalo -

• IINTERVAL ANO A MÊS - Armazena um período de tempo usando os campos de data e hora ANO e MÊS.

• INTERVAL DAY TO SECOND - Armazena um período de tempo em termos de dias, horas, minutos e segundos.

Funções de intervalo

S.No	Nome e descrição da função
1	NUMTODSINTERVAL(x, interval_unit); Converte o número x em um INTERVAL DAY TO SECOND.
2	NUMTOYMINTERVAL(x, interval_unit); Converte o número x em um INTERVAL YEAR TO MONTH.
3	TO_DSINTERVAL(x); Converte a string x em um INTERVAL DAY TO SECOND.
4	TO_YMINTERVAL(x); Converte a string x em um INTERVAL YEAR TO MONTH.

Neste capítulo, discutiremos a saída do DBMS em PL / SQL. oDBMS_OUTPUTé um pacote integrado que permite exibir saída, informações de depuração e enviar mensagens de blocos PL / SQL, subprogramas, pacotes e gatilhos. Já usamos este pacote em todo o nosso tutorial.

Vejamos um pequeno trecho de código que exibirá todas as tabelas do usuário no banco de dados. Experimente em seu banco de dados para listar todos os nomes de tabelas -

BEGIN 
   dbms_output.put_line  (user || ' Tables in the database:'); 
   FOR t IN (SELECT table_name FROM user_tables) 
   LOOP 
      dbms_output.put_line(t.table_name); 
   END LOOP; 
END; 
/

Subprogramas DBMS_OUTPUT

O pacote DBMS_OUTPUT tem os seguintes subprogramas -

S.No	Subprograma e Objetivo
1	DBMS_OUTPUT.DISABLE; Desativa a saída da mensagem.
2	DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000); Ativa a saída da mensagem. Um valor NULL debuffer_size representa o tamanho do buffer ilimitado.
3	DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2, status OUT INTEGER); Recupera uma única linha de informações armazenadas em buffer.
4	DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER); Recupera uma matriz de linhas do buffer.
5	DBMS_OUTPUT.NEW_LINE; Coloca um marcador de fim de linha.
6	DBMS_OUTPUT.PUT(item IN VARCHAR2); Coloca uma linha parcial no buffer.
7	DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2); Coloca uma linha no buffer.

Exemplo

DECLARE 
   lines dbms_output.chararr; 
   num_lines number; 
BEGIN 
   -- enable the buffer with default size 20000 
   dbms_output.enable; 
   
   dbms_output.put_line('Hello Reader!'); 
   dbms_output.put_line('Hope you have enjoyed the tutorials!'); 
   dbms_output.put_line('Have a great time exploring pl/sql!'); 
  
   num_lines := 3; 
  
   dbms_output.get_lines(lines, num_lines); 
  
   FOR i IN 1..num_lines LOOP 
      dbms_output.put_line(lines(i)); 
   END LOOP; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Hello Reader! 
Hope you have enjoyed the tutorials! 
Have a great time exploring pl/sql!  

PL/SQL procedure successfully completed.

Neste capítulo, discutiremos a PL / SQL orientada a objetos. PL / SQL permite definir um tipo de objeto, o que ajuda na concepção de banco de dados orientado a objetos no Oracle. Um tipo de objeto permite criar tipos compostos. O uso de objetos permite que você implemente objetos do mundo real com estrutura específica de dados e métodos para operá-los. Os objetos têm atributos e métodos. Atributos são propriedades de um objeto e são usados ​​para armazenar o estado de um objeto; e métodos são usados ​​para modelar seu comportamento.

Os objetos são criados usando a instrução CREATE [OR REPLACE] TYPE. A seguir está um exemplo para criar um simplesaddress objeto que consiste em alguns atributos -

CREATE OR REPLACE TYPE address AS OBJECT 
(house_no varchar2(10), 
 street varchar2(30), 
 city varchar2(20), 
 state varchar2(10), 
 pincode varchar2(10) 
); 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Type created.

Vamos criar mais um objeto customer onde vamos embrulhar attributes e methods juntos para ter uma sensação de orientação a objetos -

CREATE OR REPLACE TYPE customer AS OBJECT 
(code number(5), 
 name varchar2(30), 
 contact_no varchar2(12), 
 addr address, 
 member procedure display 
); 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Type created.

Instanciando um objeto

Definir um tipo de objeto fornece um esquema para o objeto. Para usar este objeto, você precisa criar instâncias deste objeto. Você pode acessar os atributos e métodos do objeto usando o nome da instância ethe access operator (.) como segue -

DECLARE 
   residence address; 
BEGIN 
   residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301'); 
   dbms_output.put_line('House No: '|| residence.house_no); 
   dbms_output.put_line('Street: '|| residence.street); 
   dbms_output.put_line('City: '|| residence.city); 
   dbms_output.put_line('State: '|| residence.state); 
   dbms_output.put_line('Pincode: '|| residence.pincode); 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

House No: 103A 
Street: M.G.Road 
City: Jaipur 
State: Rajasthan 
Pincode: 201301  

PL/SQL procedure successfully completed.

Métodos de Membros

Member methods são usados ​​para manipular o attributesdo objeto. Você fornece a declaração de um método de membro ao declarar o tipo de objeto. O corpo do objeto define o código para os métodos de membro. O corpo do objeto é criado usando a instrução CREATE TYPE BODY.

Constructorssão funções que retornam um novo objeto como seu valor. Cada objeto tem um método construtor definido pelo sistema. O nome do construtor é igual ao tipo de objeto. Por exemplo -

residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');

o comparison methodssão usados ​​para comparar objetos. Existem duas maneiras de comparar objetos -

Método de mapa

o Map methodé uma função implementada de tal forma que seu valor depende do valor dos atributos. Por exemplo, para um objeto de cliente, se o código do cliente for o mesmo para dois clientes, ambos os clientes podem ser iguais. Portanto, a relação entre esses dois objetos dependeria do valor do código.

Método de pedido

o Order methodimplementa alguma lógica interna para comparar dois objetos. Por exemplo, para um objeto retângulo, um retângulo é maior do que outro retângulo se ambos os lados forem maiores.

Usando o método Map

Vamos tentar entender os conceitos acima usando o seguinte objeto retângulo -

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 member function enlarge( inc number) return rectangle, 
 member procedure display, 
 map member function measure return number 
); 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Type created.

Criando o corpo do tipo -

CREATE OR REPLACE TYPE BODY rectangle AS 
   MEMBER FUNCTION enlarge(inc number) return rectangle IS 
   BEGIN 
      return rectangle(self.length + inc, self.width + inc); 
   END enlarge;  
   MEMBER PROCEDURE display IS 
   BEGIN  
      dbms_output.put_line('Length: '|| length); 
      dbms_output.put_line('Width: '|| width); 
   END display;  
   MAP MEMBER FUNCTION measure return number IS 
   BEGIN 
      return (sqrt(length*length + width*width)); 
   END measure; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Type body created.

Agora, usando o objeto retângulo e suas funções-membro -

DECLARE 
   r1 rectangle; 
   r2 rectangle; 
   r3 rectangle; 
   inc_factor number := 5; 
BEGIN 
   r1 := rectangle(3, 4); 
   r2 := rectangle(5, 7); 
   r3 := r1.enlarge(inc_factor); 
   r3.display;  
   IF (r1 > r2) THEN -- calling measure function 
      r1.display; 
   ELSE 
      r2.display; 
   END IF; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Length: 8 
Width: 9 
Length: 5 
Width: 7  

PL/SQL procedure successfully completed.

Usando o método de pedido

Agora o same effect could be achieved using an order method. Vamos recriar o objeto retângulo usando um método de pedido -

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 member procedure display, 
 order member function measure(r rectangle) return number 
); 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Type created.

Criando o corpo do tipo -

CREATE OR REPLACE TYPE BODY rectangle AS 
   MEMBER PROCEDURE display IS 
   BEGIN 
      dbms_output.put_line('Length: '|| length); 
      dbms_output.put_line('Width: '|| width); 
   END display;  
   ORDER MEMBER FUNCTION measure(r rectangle) return number IS 
   BEGIN 
      IF(sqrt(self.length*self.length + self.width*self.width)> 
         sqrt(r.length*r.length + r.width*r.width)) then 
         return(1); 
      ELSE 
         return(-1); 
      END IF; 
   END measure; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Type body created.

Usando o objeto retângulo e suas funções-membro -

DECLARE 
   r1 rectangle; 
   r2 rectangle; 
BEGIN 
   r1 := rectangle(23, 44); 
   r2 := rectangle(15, 17); 
   r1.display; 
   r2.display; 
   IF (r1 > r2) THEN -- calling measure function 
      r1.display; 
   ELSE 
      r2.display; 
   END IF; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Length: 23 
Width: 44 
Length: 15 
Width: 17 
Length: 23 
Width: 44 

PL/SQL procedure successfully completed.

Herança para objetos PL / SQL

PL / SQL permite a criação de objetos a partir dos objetos base existentes. Para implementar a herança, os objetos base devem ser declarados comoNOT FINAL. O padrão éFINAL.

Os programas a seguir ilustram a herança em objetos PL / SQL. Vamos criar outro objeto chamadoTableTop, isso é herdado do objeto Rectangle. Para isso, precisamos criar o objeto retângulo base -

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 member function enlarge( inc number) return rectangle, 
 NOT FINAL member procedure display) NOT FINAL 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Type created.

Criando o corpo do tipo base -

CREATE OR REPLACE TYPE BODY rectangle AS 
   MEMBER FUNCTION enlarge(inc number) return rectangle IS 
   BEGIN 
      return rectangle(self.length + inc, self.width + inc); 
   END enlarge;  
   MEMBER PROCEDURE display IS 
   BEGIN 
      dbms_output.put_line('Length: '|| length); 
      dbms_output.put_line('Width: '|| width); 
   END display; 
END; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Type body created.

Criando o objeto filho tabletop -

CREATE OR REPLACE TYPE tabletop UNDER rectangle 
(   
   material varchar2(20), 
   OVERRIDING member procedure display 
) 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Type created.

Criando o corpo do tipo para o objeto filho tabletop

CREATE OR REPLACE TYPE BODY tabletop AS 
OVERRIDING MEMBER PROCEDURE display IS 
BEGIN 
   dbms_output.put_line('Length: '|| length); 
   dbms_output.put_line('Width: '|| width); 
   dbms_output.put_line('Material: '|| material); 
END display; 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Type body created.

Usando o objeto tabletop e suas funções-membro -

DECLARE 
   t1 tabletop; 
   t2 tabletop; 
BEGIN 
   t1:= tabletop(20, 10, 'Wood'); 
   t2 := tabletop(50, 30, 'Steel'); 
   t1.display; 
   t2.display; 
END;
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Length: 20 
Width: 10 
Material: Wood 
Length: 50 
Width: 30 
Material: Steel  

PL/SQL procedure successfully completed.

Objetos abstratos em PL / SQL

o NOT INSTANTIABLEcláusula permite que você declare um objeto abstrato. Você não pode usar um objeto abstrato como ele é; você terá que criar um subtipo ou tipo filho de tais objetos para usar suas funcionalidades.

Por exemplo,

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE display)  
 NOT INSTANTIABLE NOT FINAL 
/

Quando o código acima é executado no prompt SQL, ele produz o seguinte resultado -

Type created.