Treinamento e Certificação OCI gratuitos

    De acordo com postagem da Oracle através de comunicado a imprensa, a Oracle anunciou neste mês que cursos oficiais e certificações estarão disponíveis gratuitamente até 31/12/2021. Os cursos e provas contemplados são referentes a plataforma Oracle Cloud Infrastructure (OCI).

    Os treinamentos podem ser efetuados através da página oficial da Oracle através deste link.

Entre os cursos contemplados temos:

• Oracle Cloud Infrastructure Foundation
• Oracle Cloud Infrastructure Architect Associate
• Oracle Cloud Infrastructure Architect Professional
• Oracle Cloud Infrastructure Operations
• Autonomous Database Specialist

Objetos do Banco de Dados

Basicamente o banco de dados possui 2 tipos de objetos: Objetos de schemas e objetos que não são de schemas.

Schema Objects

    Os objetos de schemas são objetos atrelados ao schema do usuário, lembrando que um schema é de propriedade de um usuário que tem o mesmo nome que o schema, e cada usuário é dono de apenas um schema. Os objetos de schema ficam armazenados dentro do schema.

Nota: Embora muitas vezes se confunda usuário com schema, pois tem o mesmo nome, não são a mesma coisa.

Quais são os objetos de schema?

• Analytic views
• Attribute dimensions
• Clusters
• Constraints
• Database links
• Database triggers
• Dimensions
• External procedure libraries
• Hierarchies
• Index-organized tables
• Indexes
• Indextypes
• Java classes
• Java resources
• Java sources
• Join groups
• Materialized views
• Materialized view logs
• Mining models
• Object tables
• Object types
• Object views
• Operators
• Packages
• Sequences
• Stored functions
• Stored procedures
• Synonyms
• Tables
• Views
• Zone maps

Nonschema Objects

  Os nonschema objects ou objetos fora de schema, são objetos que são armazenados diretamente em banco de dados, o que não impede que sejam criados e manipulados por SQL. Abaixo lista de objetos sem schema.

• Contexts
• Directories
• Editions
• Flashback archives
• Lockdown profiles
• Profiles
• Restore points
• Roles
• Rollback segments
• Tablespaces
• Tablespace sets
• Unified audit policies
• Users

Regras para nomear Objetos e Qualificadores

    Alguns objetos podem ter seu nome criado de forma sistema pelo banco de dados, contudo, muitos objetos exigem que o usuário faça a nomeação, por exemplo tabelas e colunas. Para realizar essa nomeação existem algumas regras e restrições. Objetos que iniciam com SYS_ ou ORA_ se tratam de objetos do sistema de banco de dados.

    Durante a nomeação de um objeto temos 2 maneiras para fazer a nomeação que são utilizando aspas (") ou sem a utilização de aspas. O que isso muda no uso de um objeto? Quando a nomeação é feita com a utilização de aspas é necessário utilizar aspas para referenciar esse objeto que também se torna sensível aos caracteres maiúsculos ou minúsculos.

Exemplos:

Foi criada uma tabela com nome referenciando e realizado um insert na tabela referenCIANDO, após foi criado uma nova tabela utilizando aspas que se chama referenCIANDO, seguido por mais 2 comandos inserts, um com o nome do objeto entre aspas duplas e outro não.

    Após a criação das tabelas e as inserções, foram realizados consultas em ambas, pode ser notado que apenas 1 dos comandos inserts foi realmente inserido na tabela de nome "referenCIANDO", enquanto o banco de dados entende que sem aspas, REFERENCIANDO é igual referenciando ou qualquer outra combinação das letras entre maiúsculas e minúsculas.

Sabendo que existem essas diferenças para as nomenclaturas, vamos ver as demais regras.

Regra 1 - Tamanhos

Os tamanhos variam de acordo com a configuração do parâmetro COMPATIBLE, mas podem ser de 1 até 30 bytes ou de 1 até 128 bytes, tendo 2 exceções:

• nome do banco de dados é limitado em 8 bytes;
• nomes de diskgroups, pdbs, rollback segments, tablespaces e tablespace sets são limitados em 30 bytes.

Regra 2 - Palavras reservadas

Palavras reservadas não podem ser utilizadas como nome de objetos, a exceção é que podem ser utilizadas desde que declaradas fazendo o uso de aspas duplas (").

Regra 3 - Palavras especiais

    No SQL algumas palavras não são reservadas, porem são utilizadas em sintaxes e comandos, essas palavras podem ser utilizadas, embora não seja recomendado, pois podem causar confusão ao analisar um comando, por exemplo.

Algumas dessas palavras são: DIMENSION, SEGMENT, ALLOCATE, DISABLE.

A listagem completa de palavras reservadas e palavras utilizadas no SQL, podem ser encontradas através de uma consulta na view V$RESERVED_WORDS.

Regra 4

    Devem ser utilizados caracteres ASCII para nomes de banco de dados, nomes de links de banco de dados e global name do banco de dados, devido a compatibilidade entre sistemas operacionais. Da mesma maneira em CDBs devem ser utilizados apenas caracteres ASCII para nomes comuns de usuários, roles e profiles.

Regra 5

Caracteres multibytes podem ser incluídos nas senhas. Mais informações podem ser vistas neste link.

Regra 6 

    Nomes que não utilizam aspas duplas devem iniciar com caracteres alfabéticos, enquanto nomes que utilizam aspas duplas podem iniciar com qualquer caractere.

Regra 7 - Caracteres Especiais e pontuação

    Nomes que utilizem aspas duplas suportam os caracteres especiais _ $ e #, desde que não sejam o primeiro caractere.

    Para links de bancos de dados também são suportados . e @, também não é indicado o uso de $ e # nesses casos.

    Para nomes que utilizam aspas duplas nas declarações não há restrições no uso de caracteres especiais, pontuação ou espaços em branco.

    Em ambos os casos não é permito o uso do caractere null (\0).

Regra 8 - Nomes duplicados

    Não é permito que dois objetos tenham o mesmo nome dentro do mesmo namespace.

Objetos que compartilham o mesmo namespace:

• Packages
• Private synonyms
• Sequences
• Stand-alone procedures
• Stand-alone stored functions
• Tables
• User-defined operators
• User-defined types
• Views

Objetos que tem seu próprio namespace:

• Clusters
• Constraints
• Database triggers
• Dimensions
• Indexes
• Materialized views * 
• Private database links

    Com base nessas informações, podemos entender que não é possível uma sequência e uma tabela possuam o mesmo nome, embora uma tabela pode ter o mesmo nome de um índice. Além disso cada schema tem seu próprio namespace, ou seja, é possível ter 2 tabelas com o mesmo nome em schemas diferentes.

*Quando uma view materializada é criada, o banco de dados cria uma tabela interna de mesmo nome, essa tabela tem o mesmo namespace que as outras tabela no schema. Então não é possível ter uma tabela e uma view materializada com mesmos nomes.

    Há ainda objetos que não estão dentro de schemas e compartilham o mesmo namespace para todo o banco de dados, esses objetos são os seguintes:

• Editions
• Parameter files (PFILEs) and server parameter files (SPFILEs)
• Profiles
• Public database links
• Public synonyms
• Tablespaces
• User roles

Regra 9 

    Nomes que não são definidos com uso de aspas duplas, não são sensíveis a maiúsculas e minúsculas, o banco de dados Oracle interpreta todos como se fosse maiúsculos, ou seja, uma tabela que foi definida como nome_tabela será interpretada pelo banco de dados como NOME_TABELA, mesmo que seja escrita em um comando de modo minúsculo ou contendo alguns caracteres maiúsculos e outros minúsculos.

    No caso dos nomes definidos com aspas duplas eles são sensíveis aos caracteres maiúsculos e minúsculos, nesse caso deve ser utilizado exatamente como foi definido.

Regra 10

    Quando o Oracle armazena ou faz comparações do identificadores em letras maiúsculas funciona como se Oracle aplicasse a função UPPER neste identificador.

Esse comportamento de comparação em maiúsculas serve para garantir que as comparações serão corretas independente de quais configurações linguísticas a sessão possui.

Regra 11 - Colunas

    Colunas na mesma tabela não podem ter o mesmo nome, colunas podem ter o mesmo nome em tabelas diferentes. Também é possível utilizar na mesma tabela o mesmo nome de coluna desde que usando aspas duplas na definição.

Regra 12 - Procedures e functions

    Procedures ou functions que estão no mesmo pacote podem ter o mesmo nome caso não tenham a mesma quantidade de argumentos e esses não sejam do mesmo tipo.

O processo de criar vários procedimentos ou funções no mesmo pacote com o mesmo nome e diferentes argumentos é chamado de overloading.

Regra 13 - Tablespaces

    Tablespaces são sensíveis a caracteres maiúsculos e minúsculos, diferente de outros identificadores que também são limitados a 30 bytes.

Fonte:

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Database-Objects.html#GUID-1B1818AD-6A70-4C2A-8E86-98BECA723FB8

Null

    NULL significa nulo, em um banco de dados um valor nulo é uma coluna que não possui nenhum valor atribuído. O null é diferente de espaços em branco e também é diferente de 0, o banco de dados Oracle considera um valor null um valor que tem tamanho igual a 0. Não é possível atribuir valor null para uma coluna definida como NOT NULL ou como PRIMARY KEY.

    Para filtramos colunas que tenham valores null devemos utilizar o comparador IS ou NOT IS ao invés de = ou <>.

    Podemos notar que não retorna erro de sintaxe ao utilizar o comparador =, contudo, o resultado não é o esperado.

    Ao utilizar comparadores com valor null em uma consulta, podemos ter 3 resultados: VERDADEIRO (TRUE), FALSO (FALSE) ou DESCONHECIDO (UNKNOWN).

    Quando utilizamos os operadores de igual e diferente ( =, <>, !=, ^=) com NULL a sintaxe não retornará erro, mas o resultado da comparação será desconhecido e a consulta não retornará resultados, conforme vimos na imagem anterior.

    Resultados VERDADEIROS (TRUE) são quando a comparação verifica se o conteúdo da coluna é nulo e o conteúdo de fato é nulo ou quando verifica se o conteúdo não é nulo e esse conteúdo não é nulo.

    Já os resultados FALSOS (FALSE) de uma comparação com null é quando a comparação busca uma coluna com valor nulo e ela tem qualquer valor, ou ainda quando a comparação busca uma coluna que não tenha valor nulo e ela tem o valor null. Levando em conta o exemplo das imagens acima, utilizadas para exemplificar os resultados verdadeiros, seria o inverso.

    Lembrando ainda que valores nulos não retornam erro de sintaxe quando utilizados em operações aritméticas, embora toda operação envolvendo um valor nulo irá retornar um valor nulo. Esse deve ser um ponto de atenção na lógica ao desenvolver uma consulta SQL.

    Nos casos como o citado acima, podemos utilizar funções específicas para o tratamento de valores nulos como as funções NLV e NVL2.

Fonte:

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Nulls.html#GUID-C785FE74-5F9C-4F70-AC4B-CA5D3010162A

Formatos

    Vimos como utilizar literais para incluir informações nas nossa consultas, mas há ainda como incrementar o uso de literais ao utilizar FORMATOS, que também podem ser chamados de máscaras, que permitem alterar o modo de exibição de um dado em relação a maneira que este dado está persistido no banco de dados.

Podemos utilizar formatos em números e datas ou ainda para aplicar um padrão de de exibição em um texto.

Formatos de Números

    A utilização de formatos em números é feita através das funções TO_NUMBER ou TO_CHAR. No caso da função TO_CHAR os formatos alteram a exibição de um tipo de dado NUMBER, BINARY_FLOAT ou BINARY_DOUBLE  para VARCHAR2. Já na utilização da função TO_NUMBER passamos a exibir um dados CHAR ou VARCHAR2 como NUMBER. Lembrando ainda que ao utilizar modelos de formatação os valores exibidos podem ser arredondados de acordo com o formato o que pode exibir no resultado valores # no arredondamento.

No exemplo abaixo, podemos ver a utilização de alguns padrões.

    A coluna salary da tabela HR.Employees é do tipo NUMBER, então para conversão utilizamos a função TO_CHAR() seguida da máscara. 

    Na coluna Tamanho temos a seguinte máscara '$09999.99', onde a inclusão do caractere $ a frente do valor vai exibir este mesmo símbolo no retorno e o 0 indica que caso o número tenha menos dígitos será complementado com 0 a esquerda como no caso da linha 20 onde o valor exibido é $09600.00.

    Na coluna Tamanho1 temos o exemplo que caso o valor tenha mais dígitos que a máscara o valor será substituído por #, como vemos nas linhas 16, 17, 18 e 19 do retorno, pois o formato definido é $9999.99 e temos valores como 10000 que excede em uma posição o formato suportado.

    Nas colunas CasaDecimal e CasaDecimal1 vemos a utilização de casas decimais nos formatos, na primeira coluna, $999999D99, o D indica que o separador será o que está configurado na parametrização do banco de dados, enquanto na segunda coluna, $9999.99, temos o separador explicito, em ambos os casos, caso o número convertido não tenha casas decimais, será exibido como 00, caso tenha casas decimais será exibido esse valor, conforme exemplo 58.

    Na coluna NOME_MOEDA é possível incluir o nome da moeda local de acordo com parametrização do banco de dados, no caso do meu banco de dados sai o BRL, também é possível incluir o símbolo da moeda conforme utilizado na coluna NOTACAO_MOEDA, ainda sobre moedas, temos a coluna NOTACAO_ALTERNATIVA, onde é possível exibir uma outra notação da moeda. Para exibir esses dados é bastante simples, bastando incluir a respectiva letra antes do formato desejado C para o nome da moeda, L para o símbolo da moeda e U para o símbolo alternativo.

    Na coluna ApenasInteiro temos um arredondamento do valor, sendo ignorados os valores das casas decimais.

E por fim a coluna SINAL onde podemos exibir o sinal de positivo ou negativo do número, bastando incluir a letra S no formato, podendo ser exibido no início ou final.

Formatos de Datas

    Assim como com números é possível utilizar formatos para alterar a exibição de datas, para isso precisamos utilizar uma das funções de conversão TO_DATE, TO_TIMESTAMP ou TO_TIMESTAMP_TZ, também é possível utilizar a função TO_CHAR para conversão de datas em caracteres.

    Na imagem abaixo, temos exemplos do uso dos formatos de datas com as funções TO_CHAR, TO_DATE e TO_TIMESTAMP, podemos encontrar os seguintes formatos no exemplo abaixo.

DD     -> Indica o dia

MM     -> Indica o mês

YYYY   -> Formato para o ano

HH      -> Formato para horário de 12 horas

HH24  -> Formato para horário de 24 horas

MI      -> Formato para minutos

SS      -> Formato para segundos

FF9     -> Formato para fração de segundos, onde o 9 é a quantidade de casas decimais, podendo ser entre 1 e 9, caso a precisão seja ignorada retornará o padrão.

Opções para exibir o dia.

    Além da opção DD que exibe o dia, podemos utilizar o formato DAY que exibirá o nome do dia ou então DY que irá exibir as 3 primeiras letras do nome do dia, a exibição irá depender da maneira como for colocado o formato, por exemplo, DAY irá retornar DOMINGO, enquanto Day retornará Domingo e day o retorno será domingo. Da mesma maneira DY trará DOM, enquanto Dy o resultado será Dom e dy dom.

    Ainda para dias, temos as opções de exibir a data informando o dia da semana na forma de número onde 1 é domingo e 7 sábado para isso utilizamos a formato d, ou exibir o data na forma de dia do ano utilizando o formato ddd.

Opções para exibir o mês

    Assim como para os dias, para os meses também é possível exibir no formato por extenso ou abreviado, para exibir abreviado utilizamos o formato MON e por extenso o formato MONTH e também como os dias, a forma que o formato é passado na máscara será exibido no retorno, caso MONTH será MAIO, caso Month o retorno será Maio e no caso de month teremos maio. Nos casos que o formato configurado seja da maneira abreviada, teremos o retorno apenas das 3 primeiras letras, conforme o exemplo o retorno seria MAI. Existe também a possibilidade de exibir o mês em algarismo romano, para isso é necessário utilizar o formato RM.

Formatos DL e DS

    Os formatos DL e DS são formatos exibidos de acordo com as configurações NLS do banco, onde o DL é o formato longo e o DS o formato curto.

Formatos de semana

    Também temos formatos para retornar uma data com dados da semana. Onde através do formato W temos a semana do mês da data informada e usando o formato WW temos a semana do ano da data informada. Há ainda a possibilidade de utilizar o formato IW que é a semana do ano de acordo com o padrão ISO, ou seja, a primeira semana não necessariamente será semana do dia 01 de janeiro, mas sim a primeira semana que tem ao menos 4 dias no novo ano.

Sufixos para datas

    Há também a possibilidade de incluir sufixos para as formatações de datas da forma que as datas sejam exibidas de forma ordinal, escrita ou combinada. As opções para as máscaras de formatos são: SP, TH ou THSP/SPTH. Vamos ver alguns exemplos práticos de uso.

    Durante provas de certificação atentar nesse ponto, pois existem pegadinhas com a exibição de dados utilizando essas formatações. Por exemplo, embora a sintaxe TH esteja maiúscula, caso o dia(DD) esteja minúsculo será exibido minúsculo.

FX e FM

    Nas sintaxes de formatos para datas é possível incluir FX ou FM, e o que acontece ao adicionar FX ou FM no formato? Basicamente, FX se refere ao formato exato, ou seja, a máscara deve estar idêntica ao conteúdo que se deseja converter. Enquanto o FM é o modo de preenchimento, em que o conteúdo pode ser preenchido com espaços ou 0 para completar o formato, no caso do FM ainda é possível utilizar um separador diferente do formato.

Formato do Ano

    Para anos temos 2 possibilidades de formatos podemos utilizar o RRRR (RR) ou o formato YYYY (YY). O recomendado é a utilização do formato YYYY, pois esse formato elimina a ambiguidade e não prejudica a otimização das consultas. O formato RR é similar ao YY, contudo tem particularidades referentes ao século que basicamente permite armazenar anos de século 20 estando no século 21. Quando utilizamos o formato YY, os 2 primeiros dígitos serão sempre iguais do ano atual, porém quando utilizamos o formato RR os 2 primeiros dígitos vão depender dos 2 últimos dígitos da data e também dos 2 últimos dígitos do ano atual.

    Caso os 2 dígitos retornados para o ano sejam entre 00 e 49:

    - Se os 2 últimos dígitos do ano atual estiverem entre 00 e 49:

    será retornado os mesmos 2 dígitos do ano atual.

    - Se os 2 últimos dígitos do ano atual estiverem entre 50 e 00:

    será retornado 1 número maior que os 2 dígitos do ano atual.

    Caso os 2 dígitos retornados para o ano sejam entre 50 e 99:

    - Se os 2 últimos dígitos do ano atual estiverem entre 00 e 49:

            será retornado 1 número menor que os 2 dígitos do ano atual.

    - Se os 2 últimos dígitos do ano atual estiverem entre 50 e 00:

    será retornado os mesmos 2 dígitos do ano atual.

Fonte:

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Format-Models.html#GUID-DFB23985-2943-4C6A-96DF-DF0F664CED96

Literais

Literais

    Quando falamos sobre um valor literal em uma consulta de banco de dados estamos nos referindo há um valor fixo, isso significa que esse valor se repetirá em todas as linhas retornadas pela consulta. Temos 4 tipos de literais textos, numéricos, datas e intervalos, abaixo alguns exemplos do uso, note que embora o valor dentro do literal pareça igual nas colunas "Número" e "Caractere" uma o valor 101 é exibido em formato numérico enquanto na outra em formato de sequência de caracteres, essa indicação é facilmente identificada através do alinhamento dos valores dentro da coluna.

Texto Literal

    Textos podem ser utilizados como literais de maneira bem simples basta incluir os caracteres entre aspas simples, podendo receber um ALIAS, caso o alias seja omitido o título da coluna será o valor utilizado no literal excluindo os espaços.

    Quando há necessidade de incluir aspas no texto que será exibido no literal é necessário fazer a indicação de um delimitador alternativo chamado de quote_delimiter que basicamente é a substituição da delimitação através de aspas simples por outra indicada podendo ser (), {}, [] ou <>. A letra 'Q' no delimitador pode ser maiúscula ou minúscula, e as símbolos usados como delimitador podem ser incluídos dentro do literal. Há ainda a possibilidade de duplicar as aspas para indicar que a mesma está sendo utilizada dentro do literal. Abaixo exemplos de uso dos delimitadores.

    O Oracle permite até 4000 bytes em um único literal, podendo ser 32767 caso o parâmetro MAX_STRING_SIZE esteja com o valor igual a EXTENDED.

Número Literal

    Diferente dos caracteres, números não precisam de aspas simples ( ' ) para informar que são literais, basta incluí-los na consulta, mas assim como os caracteres literais, os números literais também tem suas particularidades.

    Na imagem abaixo, podemos ver formas diferentes de usar literais numéricos. Vale destacar que para números inteiros positivos não é necessário utilizar o sinal de mais ( + ) e que quando se usa casas decimais o limite é de 38 dígitos. Para que o retorno seja no formato BINARY_FLOAT devemos utilizar 'f' após o número e no caso de DOUBLE_FLOAT devemos utilizar 'd' enquanto a letra 'e' simboliza uma notação científica.

    Ao utilizar literais com números devemos ter cuidado quanto ao separador. Para verificar qual o padrão está definido podemos realizar uma consulta e verificar como está definida a parametrização para a sessão ou para o banco de dados através das consultas abaixo:

        select * from nls_session_parameters

        where parameter = 'NLS_NUMERIC_CHARACTERS';

        select * from nls_database_parameters

        where parameter = 'NLS_NUMERIC_CHARACTERS';

    No exemplo abaixo, temos 3 expressões que deveriam ter o mesmo resultado, mas por erro ao escrever a consulta não traz o resultado esperado. Quando o literal utilizado for do tipo numérico devemos utilizar o ponto ( . ) como separador das casas decimais, caso for utilizado a vírgula ( , ) como separador o literal deve ser passado como caractere, ou seja, dentro de aspas simples ( ' ). Caso o separador seja a vírgula e não esteja dentre de aspas simples o banco de dados irá identificar a vírgula como separador de colunas conforme exemplo abaixo.

Data e Horário Literal

    Para os literais de datas é possível informa-los como texto ou utilizar a função TO_DATE e realizar a conversão para formato de data, ou ainda utilizando a função SYSDATE ou SYSTIMESTAMP, sendo ainda permitido efetuar operações com as funções.

    Para identificar o formato de datas utilizado pelo banco de dados, basta realizar uma consulta em NLS_DATABASE_PARAMETERS procurando pelo parâmetro NLS_DATE_FORMAT.

   Caso o horário seja omitido em um literal de data e hora, a hora será interpretada como '00:00:00' ou '12:00:00' dependendo do padrão de horário ser 24 horas ou 12 horas.

    Da mesma maneira, que podemos utilizar as datas, podemos utilizar o formato timestamp como literal, inclusive utilizando as variantes de local time zone ou com um time zone específico.

Intervalo Literal

    Os intervalos especificam períodos de tempo podendo ser anos e meses ou dias, horas, minutos e segundos, de acordo com o tipo de dado utilizado YEAR TO MONTH ou DAY TO MINUTE, conforme visto aqui e também podemos utilizar literais com os intervalos para especificar esses períodos. Nesse tipo de literal informamos 1 ou 2 campos, onde o primeiro é a maior unidade de medida e o segundo a menor unidade de medida, por exemplo, ao passar um literal '123-2' para o tipo YEAR TO MONTH, estamos dizendo que temos 123 anos e 2 meses. 

    Abaixo exemplos do uso do intervalo com anos e meses, nos exemplos podemos notar o uso dos intervalos literais, como a precisão para anos é de 2, precisamos informar a precisão nos casos dos literais maiores que 2 casas, no caso dos mesmo o padrão é 3. no caso dos literais '123-9' estamos é necessário a utilização do year(3) to month enquanto nos outros podemos apenas informar year ou month.

    Lembrando que caso o literal informado não esteja no padrão esperado ou estoure o tamanho da precisão a consulta não irá executar retornando um erro.

    Da mesma maneira, podemos utilizar os literais para DAY TO MINUTE e suas partes. Podendo utilizar apenas uma parte ou mais de uma parte, desde que respeitado o formato necessário.

    O uso de intervalos literais costuma ser um tema recorrente nas provas de certificação 1Z0-071, por isso deve ser um tema estudado e testado bastante.

Fonte:

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Literals.html#GUID-4C258D8F-3DF2-4D45-BE3E-14864DD77100