List and describe various data types using the %TYPE attribute

O %TYPE atributo permite que use o tipo de dados de um campo, registro, tabela aninhada, coluna banco de dados, ou variável em sua próprias declarações, ao invés de codificar os nomes de tipo. Você pode usar o%TYPE atributo como um especificador de tipo de dados ao declarar constantes, variáveis, campos e parâmetros. Se os tipos que você faz referência a mudança, suas declarações são atualizados automaticamente.Esta técnica evita que você fazer alterações no código, quando, por exemplo, o comprimento de um VARCHAR2 coluna é aumentada. Note-se que restrições de coluna, como o NOT NULL restrição e cheque, ou os valores padrão não são herdadas por itens declarados utilizando %TYPE .

DECLARE
   SUBTYPE v_empid_subtype IS employees_temp.empid%TYPE; 

   SUBTYPE v_deptid_subtype IS  employees_temp.deptid%TYPE;
   SUBTYPE v_deptname_subtype IS employees_temp.deptname%TYPE;
   SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;   
   v_empid    v_empid_subtype;
   v_deptid   v_deptid_subtype;
   v_deptname v_deptname_subtype;
   v_emprec   v_emprec_subtype;
BEGIN
   v_empid := NULL;  -- this works, null constraint is not inherited
-- v_empid := 10000002; -- invalid, number precision too large 
   v_deptid := 50; -- this works, check constraint is not inherited
-- the default value is not inherited in the following
   DBMS_OUTPUT.PUT_LINE('v_deptname: ' || v_deptname);
   v_emprec.empid := NULL;  -- this works, null constraint is not inherited
-- v_emprec.empid := 10000002; -- invalid, number precision too large
   v_emprec.deptid := 50; -- this works, check constraint is not inherited
-- the default value is not inherited in the following
   DBMS_OUTPUT.PUT_LINE('v_emprec.deptname: ' || v_emprec.deptname); 
END;


employees_temp.empid é do tipo number(6). Sendo assim, quando dizemos  SUBTYPE v_empid_subtype IS employees_temp.empid%TYPE;, estamos criando um subtipo chamado v_empid_subtype, que é do mesmo tipo da coluna employees_temp.empid, ou seja, number(6); portanto, ao declararmos a variável v_empid como sendo do subtipo v_empid_subtype, sabemos que ela será do tipo number(6); 

Tópico: List the uses of variables, declare and initialize variables, use bind variables

Seria algo como 'Listar os usos de variáveis, declarar e inicializar variáveis​​, usar ' bind variables''... acho que já abordamos boa parte destes assuntos, contudo, vamos revisá-los mais um pouco!


1) Listar os usos de variáveis:

- Armazenamento temporário de dados;
- manipulação de valores armazenados ;
- reusabilidade;
- facilidade de manutenção;

2) Declare and initialize variables:

Para declararmos variáveis, primeiramente informamos o nome do identificador (seguindo as regras do post passado), seguindo do tipo e, se preciso for, precisão do mesmo;

VARIAVEL_1 VARCHAR2(50);

VARIAVEL_2 NUMBER(10);

Para iniciarmos estas variáveis, devemos atribuir um valor correspondente ao seu tipo no momento da declaração;

VARIAVEL_1 VARCHAR2(50) := 'Olá! Variável iniciada!';

VARIAVEL_2 NUMBER(10) := 1234;

 3) Bind variables(Variáveis de Ligação) ;

Uma variável de ligação é uma variável que você declara em um ambiente de host e usa para passar
valores de tempo de execução, número ou caractere, para ou de um ou mais programas PL/SQL, os
quais podem usá-la como usariam qualquer outra variável. Você poderá referenciar variáveis
declaradas em ambientes de host ou chamada em instruções PL/SQL, a não ser que a instrução esteja
em um procedimento, função ou pacote. Isso inclui as variáveis de linguagem declaradas em
programas do pré-compilador, campos de tela em aplicações de Form do Oracle Developer e as
variáveis de ligação SQL*Plus.

Criando Variáveis de Ligação

Para declarar uma variável de ligação no ambiente SQL*Plus, você deve usar o comando
VARIABLE. Por exemplo, você poderá declarar uma variável de tipo NUMBER e VARCHAR2
como se segue:
Tanto o código SQL quanto o SQL*Plus poderão referenciar a variável de ligação, e o código
SQL*Plus poderá exibir seus valores.

VARIABLE return_code NUMBER;
VARIABLE return_msg VARCHAR2(30);

Para referenciar uma variável de ligação no
PL/SQL, você deverá criar um prefixo antes
do nome usando dois-pontos (:).
Exemplo:

 VARIABLE g_salary NUMBER
DECLARE
  v_sal emp.sal%TYPE;
BEGIN
  SELECT sal
      INTO v_sal
  FROM emp
     WHERE empno = 7369;
   :g_salary := v_sal;
END;
/

 fonte: Introdução ao Oracle: SQL e PL/SQL - Guia do Estudante • Volume 2

Tópico: Recognize valid and invalid identifiers

Olá!
Como 'Reconhecer identificadores válidos e inválidos'? Existem algumas regrinhas básicas que você deve aprender para não cair em uma pegadinha na prova. 

1) Devem ter menos que 30 caracteres;

DECLARE

    MINHA_VARIAVEL_GIGANTE_E_INVALIDA_DE_EXEMPLO VARCHAR2(20);

BEGIN
 NULL;
END;
/

Aparentemente não existe erro... mas se obsevarmos a regra que diz "Devem ter menos que 30 caracteres", veremos que a nossa variável é inválida, gerando o seguinte erro:

PLS-00114: identifier 'MINHA_VARIAVEL_GIGANTE_E_INVAL' too long

Como resolver? Simples, basta diminuir o tamanho do nome:

DECLARE

    MINHA_VARIAVEL_VALIDA VARCHAR2(20);

BEGIN
 NULL;
END;/  
 Compilado sem problemas!


2) Começar com uma letra;

Tente compilar o seguinte bloco:

DECLARE

    1VARIAVEL VARCHAR2(20);

BEGIN
 NULL;
END; 
 /

PLS-00103: Encountered the symbol "1" when expecting one of the following:
 
 Todo e qualquer identificador criado pelo usuário deve começar com uma letra, minúscula ou maiúscula, mas sempre uma LETRA!

3) Podem incluir $, # ou _;
 Todos os identificadores declarados abaixo são válidos:


  MINHA_VARIAVEL_VALIDA VARCHAR2(20);

  MINHA#VARIAVEL#VALIDA VARCHAR2(20);

  MINHA$VARIAVEL$VALIDA VARCHAR2(20);

4) Não pode conter pontuação, espaços ou hífen.
 Todos os identificadores declarados abaixo não são válidos:

 MINHA-VARIAVEL-INVALIDA VARCHAR2(20);

 MINHA,VARIAVEL,INVALIDA VARCHAR2(20);

 MINHA VARIAVEL INVALIDA VARCHAR2(20);

MINHA;VARIAVEL;INVALIDA VARCHAR2(20);


etc...


Até a próxima!

Declaring PL/SQL Variables

Antes de tudo, desculpas! Fiquei bastante tempo ausente, mas estou de volta e com fôlego renovado! Então, vamos lá!

O conteúdo deste assunto já foi abordado em um tópico anterior, chamado 'Os tipos de variáveis em PL/SQL', que pode ser acessado aqui. Sendo assim, vamos ao próximo assunto!

Tópico: Output messages in PL/SQL

Ok, para este tópico, não usarei o Oracle SQL Developer, mas o Oracle SQL*Plus;

Estou usando a versão 8, mas não se preocupem, isto em nada influenciará os nossos estudos. Para começarmos a lançar mensagens, teremos que habilitar o servidor de saída. Usamos a instrução ‘SET SERVEROUTPUT ON’. Você só precisa fazer isto uma vez para cada sessão, ou seja, o servidor estará habilitado até o momento em que você fechar o SQL*Plus.

Ok, agora vamos construir um Bloco Anônimo PL/SQL (já sabemos do que se trata) que vai nos retornar uma mensagem através do servidor que acabamos de ativar.

Começamos com o velho e bom ‘BEGIN’ e agora vem a novidade.

DBMS_OUTPUT.PUT_LINE(‘OLÁ! MEU NOME É RUDAK!’);

Para não me esquecer de algumas instruções, gosto de fazer certas analogias. Como não me esquecer de uma instrução deste tamanho?

DBMS -> Isto me parece ser um sigla de DataBase MeSsage (mensagem da base de dados).

DBMS_OUTPUT -> Eu traduziria isto como ‘Mensagens de saída da base de dados’

PUT_LINE -> Coloque uma linha com o conteúdo entre parênteses e avança para a próxima linha.

Obs.: para confirmar uma linha no SQL*PLUS (pular linha) use a tecla ENTER. XD;

Nosso bloco ficará assim:

BEGIN

  DBMS_OUTPUT.PUT_LINE (‘OLÁ! MEU NOME É RUDAK!’);

END;

/

 A barra serve para dizer ao compilador “Ok compilador! Compile o bloco PL/SQL acima!”. Já vimos que no Oracle SQL Developer não é preciso usá-la.

Resultado: ele ‘printou’ na tela a mensagem que especificamos e depois confirmou o sucesso da operação.

Pense no DBMS_OUTPUT como uma classe. Sendo uma classe, ele pode conter vários métodos. E têm. Vejamos alguns deles:

1)PUT_LINE: Já vimos como funciona, retorna uma mensagem e pula para a próxima linha. Mais tarde veremos que esta mensagem pode vir concatenada à uma variável.

Exemplo:

BEGIN

  DBMS_OUTPUT.PUT_LINE(‘OLÁ! MEU NOME É RUDAK!’);

END;

/

2)PUT: Retorna uma mensagem e mas não pula para a próxima linha. Vejamos o exemplo a seguir:

Exemplo:

BEGIN

   DBMS_OUTPUT.PUT(‘OLÁ! MEU NOME É’);    

   DBMS_OUTPUT.PUT(‘ RUDAK!’);                       

END;

/

O resultado da execução deste bloco será “OLÁ! MEU NOME É RUDAK!” Notaram que ele não pulou uma linha?

3)NEW_LINE: Este método simplesmente cria uma linha em branco, ou se você preferir, pula uma linha.

Eu diria que o PUT_LINE é um cruzamento de um PUT com um NEW_LINE. XD

PUT_LINE = PUT + NEW_LINE;

Exemplo:

BEGIN

   DBMS_OUTPUT.PUT (‘OLÁ! MEU NOME É’);

   DBMS_OUTPUT.NEW_LINE;

   DBMS_OUTPUT.PUT_LINE(‘ RUDAK!’);

END;

/

Agora sim pulou para a linha debaixo!

Valeu pessoal, até a próxima!

Tópico: Identify the different types of PL/SQL blocks

Boa tarde pessoal! Acredito que já abordamos este assunto, contudo, vamos nos aprofundar um pouco mais agora. O que podemos entender por “Identificar os diferentes tipos de blocos PL/SQL”? Se você não entende nada, vamos por partes: o que podemos entender por um “bloco PL/SQL”?

Bloco PL/SQL

Ao meu entender, podemos considerar um bloco PL/SQL como sendo aquele bloco que possui as estruturas mínimas para se executar um comando PL/SQL sem gerar erros (diferente de exceções); Se um bloco gera erro, ele não executa, logo, não é um bloco PL/SQL válido.  Segue a estrutura padrão de um bloco PL/SQL:

[DECLARE]  --Isto não é obrigatório!
 --declaração de variáveis, cursores, etc.
BEGIN
--seu código aqui!
[EXCEPTION] --Isto também não é obrigatório
 --tratamento de exceções (falhas)
END;

Entendendo:

Seção Declarativa/DECLARE: Contém todas as variáveis, constantes, cursores e exceções definidas pelo usuário que são referenciadas nas seções executável e declarativa (Opcional)

Seção Executável/BEGIN: Contém instruções SQL para manipular dados no banco de dados e instruções PL/SQL para manipular dados no bloco (Obrigatória)

Seção Tratamento de exceção/EXCEPTION: Especifica as ações a desempenhar quando erros e condições anormais surgem na seção executável (Opcional).

 Como já foi citado antes, segue o bloco PL/SQL mínimo aceitável:

BEGIN

  NULL;

END;

Este bloco é um bloco PL/SQL aceitável, pois ao executá-lo, nenhum erro ocorre apesar deste comando ser inútil...

Este tipo de bloco é conhecido com Bloco Anônimo. Anônimo – Sem identificação, sem nome. E o que tornaria um bloco em “não anônimo”?

- Bloco de Procedimento/Procedural: nada mais é do um bloco PL/SQL nomeado. A grande vantagem sobre um bloco PL/SQL anônimo é que pode ser compilado e armazenado no banco de dados como um objeto de schema. Graças a essa característica as procedures são de fácil manutenção, o código é reutilizável e permitem que trabalhemos com módulos de programa.

Ex.:

CREATE PROCEDURE INUTIL AS

  BEGIN

    NULL;

  END;

Olhem só que legal! Criamos uma procedure (bloco PL/SQL nomeado e armazenado) que não serve pra p!@#$ nenhuma! Mas criamos! Se você estiver usando o Oracle SQL Developer, pode visualizá-la aqui:
 

Como visualizar uma procedure criada no Oracle SQL Developer

It’s alright! It’s ok! Vamos tentar executar novamente o mesmo bloco? Só para ver o que acontece:

Erro gerado ao tentar se executar novamente o mesmo bloco PL/SQL pela segunda vez

Porque deu erro se a procedure não está errada? Pelo simples motivo de ela já existir! Podemos proceder de duas formas: ou atualizamos a nossa procedure, ou mudamos o nome e criamos outra. Para atualizar, basta adicionarmos o comando OR REPLACE logo após o comando CREATE, deixando o nosso bloco assim:

CREATE OR REPLACE PROCEDURE INUTIL AS

  BEGIN

    NULL;

  END;

Entendendo o bloco: através no nome da procedure, o bloco verifica se já existe outra procedure com o mesmo nome; se não existir, ele cria (CREATE). Se existir, ele substitui (OR REPLACE). Simples assim.

Certo, mas e agora, o que faço com uma procedure após cria-la? Podemos executá-la em outro bloco PL/SQL! Qual o nome da nossa procedure? INUTIL! Então basta chama-la da seguinte forma:

BEGIN

  INUTIL;

END;

Como a procedure INUTIL nada faz, este bloco também nada fará. Mas que tal darmos uma razão de ser à nossa procedure? Para isto, seguiremos para o próximo tópico - Output messages in PL/SQL.

Tópico - Explain the advantages of PL/SQL

PL / SQL é uma linguagem de processamento de transações, que oferece as seguintes vantagens:

- Suporte para SQL - SQL é flexível, poderoso e fácil de aprender.
- Suporte para programação orientada a objeto
- Melhor desempenho - com PL / SQL, um bloco inteiro de declarações podem ser enviadas para o Oracle de uma vez.
- Maior produtividade - PL / SQL aumenta a produtividade, permitindo o uso de ferramentas de melhor.
- A portabilidade plena - PL / SQL são portáveis ​​para qualquer sistema operacional e plataforma na qual o Oracle é executado.
- Integração com o Oracle
- Segurança PL / SQL pode conseguir abstração de dados, controle de acesso.

E mais:

Integração

- A linguagem PL/SQL desempenha um papel central tanto para o Oracle Server através de procedimentos armazenados, funções armazenadas, gatilhos de banco de dados e pacotes.
- Além de os tipos de dados SQL também serem usados no código PL/SQL.
-Combinados com o acesso direto que a linguagem SQL fornece, esses tipos de dados compartilhados integram a linguagem PL/SQL com o dicionário de dados do Oracle Server.

Melhora o Desempenho

- Reduz o Tráfego da Rede, visto que ele agrupa as instruções SQL em um único bloco e envia esse bloco inteiro para o servidor em uma única chamada.
- A linguagem PL/SQL também pode cooperar com as ferramentas de desenvolvimento de aplicação do Oracle Server como, por exemplo, Oracle Developer Forms e Reports. Ao adicionar recursos de processamento procedural a essas ferramentas, a linguagem PL/SQL aumenta o desempenho.