Esse capitulo mostra algumas funções do Oracle.
Parte 2

Curiosidades:

  • São funções simples, que retornam um resultado, por exemplo: UPPER / lower;
  • Podem ser usados no SELECT/DELETE na clausula WHERE, no UPDATE na clausula SET e/ou WHERE e INSERT na lsita de valores;
Funções String:

Upper(String1);
Lower(String1);
Initcap(String1);
|| ou Concat(String1,String2);
Lpad ou Rpad(String1, Número, String2)
Sem String2 usa espaço em branco como default;
Ltrim ou Rtrim(String1, String2)
Sem String2 usa espaço em branco como default;

Trim(trim_info trim_string FROM trim_source)
trim_info:
leading – remove trim_string do inicio da trim_source;
trailing – remove trim_string do final da trim_source;
both – remove trim_string de ambos os lados da trim_source – Defualt;
trim_string: omitido usa espaço em branco como default;
Length(String1);
Instr(String1, String2, Posicao_Inicial, Nr_Ocorrencia)
Posicao_Inicial e Nr_Ocorrencias não são obrigatórios e o default é 1;
Resultado é numérico que diz em que posição aconteceu a Nr_Ocorrencia procurada;
Substr(String1, Posicao_Inicial, Tamanho)
Tamanho é opcional e ao não passar vai até o final da String1;
Soundex(String1);

Exemplos:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IFNlbGVjdCBVcHBlcignbWFpw7pzY3VsbycpIEZyb20gRHVhbDsKClNlbGVjdCBMb3dlcignTUlOw5pTQ1VMTycpCkZyb20gRHVhbDsKCiBTZWxlY3QgSW5pdGNhcCgncFJJTUVJUkEgbUFpc2N1bGEgU0VNUFJFJykgRnJvbSBEdWFsOwoKIFNlbGVjdCBDb25jYXQoJ0NvbmNhdGVuYXI6IFN0cmluZzEgJywgJ1N0cmluZzInKSBGcm9tIER1YWw7CgogU2VsZWN0ICdDb25jYXRlbmFyIHx8OiAnIHx8ICdTdHJpbmcxICcgfHwgJyBTdHJpbmcyJyBGcm9tIER1YWw7CgpTZWxlY3QgTHBhZCgnUHJlbmNoZSBjb20gYSBTVFJJTkcyIGEgRElSRUlUQSBuYSBxdWFudGlkYWRlIGRvIE5VTUVSTyAocG9zacOnw7VlcyknLCAxMDAsICcuJykgCkZyb20gRHVhbCBVbmlvbiBTZWxlY3QgTHBhZCgnc2VndW5kbyBleGVtcGxvIHBhcmEgdmVyIGNvbW8gZmljb3UnLCAxMDAsICcuJykgRnJvbSBEdWFsOwoKCiBTZWxlY3QgUnBhZCgnUHJlbmNoZSBjb20gYSBTVFJJTkcyIGEgRElSRUlUQSBuYSBxdWFudGlkYWRlIGRvIE5VTUVSTyAocG9zacOnw7VlcyknLCAxMDAsICcuJykgCkZyb20gRHVhbDsKCiBTZWxlY3QgTHRyaW0oJyBUaXJhIEVzcGHDp28gRXNxdWVyZGEnKSBGcm9tIER1YWwgClVuaW9uIFNlbGVjdCBMdHJpbSgnMTExMTExVGlyYSAnJ1N0cmluZzInJyBFc3F1ZXJkYScsICcxJykgIEZyb20gRHVhbCAKVW5pb24gU2VsZWN0IEx0cmltKCcxMTExMTFUaXJhICcnU3RyaW5nMicnIEVzcXVlcmRhJywgJzInKSBGcm9tIER1YWw7CgogU2VsZWN0IFJ0cmltKCcgVGlyYSBFc3Bhw6dvIERpcmVpdGEgJykgRnJvbSBEdWFsIApVbmlvbiBTZWxlY3QgUnRyaW0oJ1RpcmEgJydTdHJpbmcyJycgRGlyZXRpYSAxMTExMTExMTExMScsICcxJykgRnJvbSBEdWFsIApVbmlvbiBTZWxlY3QgUnRyaW0oJ1RpcmEgJydTdHJpbmcyJycgRXNxdWVyZGEgMzMzMzMzMzMzJywgJzInKSBGcm9tIER1YWw7CgogU2VsZWN0IFRyaW0oJyBEZWZhdWx0ICcpIEZyb20gRHVhbCAKVW5pb24gU2VsZWN0IFRyaW0oJyAnIEZyb20gJyBTZW0gYm90aCAnKSBGcm9tIER1YWwgClVuaW9uIFNlbGVjdCBUcmltKEJvdGggJyAnIEZyb20gJyBDb20gYm90aCAnKSBGcm9tIER1YWw7CiBTZWxlY3QgVHJpbShMZWFkaW5nICcxJyBGcm9tICcxMTExMTFUaXJhICcndHJpbV9zdHJpbmcnJyBFc3F1ZXJkYScpIApGcm9tIER1YWw7CgogU2VsZWN0IFRyaW0oVHJhaWxpbmcgJzEnIEZyb20gJ1RpcmEgJyd0cmltX3N0cmluZycnIERpcmVpdGEgMTExMTExMTExMTEnKSBGcm9tIER1YWw7CgogU2VsZWN0IExlbmd0aCgnVGFtYW5obyBkYSBTdHJpbmcnKSBGcm9tIER1YWw7CgogU2VsZWN0IEluc3RyKCd0ZXN0ZSBlc3RhIHRlc3RhZG8nLCAndGUnKSBQb3NpY2FvX05hX1N0cmluZyBGcm9tIER1YWw7CgogU2VsZWN0IEluc3RyKCd0ZXN0ZSBlc3RhIHRlc3RhZG8nLCAndGUnLCAyKSBQb3NpY2FvX05hX1N0cmluZyBGcm9tIER1YWw7CgogU2VsZWN0IEluc3RyKCd0ZXN0ZSBlc3RhIHRlc3RhZG8nLCAndGUnLCAxLCAzKSBQb3NpY2FvX05hX1N0cmluZyBGcm9tIER1YWw7CgogU2VsZWN0IFN1YnN0cignMTExMSBUZXN0ZSB1bScsIDYpIEZyb20gRHVhbCAKVW5pb24gU2VsZWN0IFN1YnN0cignMTExMSBUZXN0ZSB1bScsIDYsIDUpIEZyb20gRHVhbDsKCiBTZWxlY3QgU291bmRleCgnUmFmYWVsJykgRnJvbSBEdWFsIApVbmlvbiBBbGwgU2VsZWN0IFNvdW5kZXgoJ1JhcGhhZWwnKSBGcm9tIER1YWw7″ hover_enabled=”0″]IFNlbGVjdCBVcHBlcignbWFpw7pzY3VsbycpIEZyb20gRHVhbDsKClNlbGVjdCBMb3dlcignTUlOw5pTQ1VMTycpCkZyb20gRHVhbDsKCiBTZWxlY3QgSW5pdGNhcCgncFJJTUVJUkEgbUFpc2N1bGEgU0VNUFJFJykgRnJvbSBEdWFsOwoKIFNlbGVjdCBDb25jYXQoJ0NvbmNhdGVuYXI6IFN0cmluZzEgJywgJ1N0cmluZzInKSBGcm9tIER1YWw7CgogU2VsZWN0ICdDb25jYXRlbmFyIHx8OiAnIHx8ICdTdHJpbmcxICcgfHwgJyBTdHJpbmcyJyBGcm9tIER1YWw7CgpTZWxlY3QgTHBhZCgnUHJlbmNoZSBjb20gYSBTVFJJTkcyIGEgRElSRUlUQSBuYSBxdWFudGlkYWRlIGRvIE5VTUVSTyAocG9zacOnw7VlcyknLCAxMDAsICcuJykgCkZyb20gRHVhbCBVbmlvbiBTZWxlY3QgTHBhZCgnc2VndW5kbyBleGVtcGxvIHBhcmEgdmVyIGNvbW8gZmljb3UnLCAxMDAsICcuJykgRnJvbSBEdWFsOwoKCiBTZWxlY3QgUnBhZCgnUHJlbmNoZSBjb20gYSBTVFJJTkcyIGEgRElSRUlUQSBuYSBxdWFudGlkYWRlIGRvIE5VTUVSTyAocG9zacOnw7VlcyknLCAxMDAsICcuJykgCkZyb20gRHVhbDsKCiBTZWxlY3QgTHRyaW0oJyBUaXJhIEVzcGHDp28gRXNxdWVyZGEnKSBGcm9tIER1YWwgClVuaW9uIFNlbGVjdCBMdHJpbSgnMTExMTExVGlyYSAnJ1N0cmluZzInJyBFc3F1ZXJkYScsICcxJykgIEZyb20gRHVhbCAKVW5pb24gU2VsZWN0IEx0cmltKCcxMTExMTFUaXJhICcnU3RyaW5nMicnIEVzcXVlcmRhJywgJzInKSBGcm9tIER1YWw7CgogU2VsZWN0IFJ0cmltKCcgVGlyYSBFc3Bhw6dvIERpcmVpdGEgJykgRnJvbSBEdWFsIApVbmlvbiBTZWxlY3QgUnRyaW0oJ1RpcmEgJydTdHJpbmcyJycgRGlyZXRpYSAxMTExMTExMTExMScsICcxJykgRnJvbSBEdWFsIApVbmlvbiBTZWxlY3QgUnRyaW0oJ1RpcmEgJydTdHJpbmcyJycgRXNxdWVyZGEgMzMzMzMzMzMzJywgJzInKSBGcm9tIER1YWw7CgogU2VsZWN0IFRyaW0oJyBEZWZhdWx0ICcpIEZyb20gRHVhbCAKVW5pb24gU2VsZWN0IFRyaW0oJyAnIEZyb20gJyBTZW0gYm90aCAnKSBGcm9tIER1YWwgClVuaW9uIFNlbGVjdCBUcmltKEJvdGggJyAnIEZyb20gJyBDb20gYm90aCAnKSBGcm9tIER1YWw7CiBTZWxlY3QgVHJpbShMZWFkaW5nICcxJyBGcm9tICcxMTExMTFUaXJhICcndHJpbV9zdHJpbmcnJyBFc3F1ZXJkYScpIApGcm9tIER1YWw7CgogU2VsZWN0IFRyaW0oVHJhaWxpbmcgJzEnIEZyb20gJ1RpcmEgJyd0cmltX3N0cmluZycnIERpcmVpdGEgMTExMTExMTExMTEnKSBGcm9tIER1YWw7CgogU2VsZWN0IExlbmd0aCgnVGFtYW5obyBkYSBTdHJpbmcnKSBGcm9tIER1YWw7CgogU2VsZWN0IEluc3RyKCd0ZXN0ZSBlc3RhIHRlc3RhZG8nLCAndGUnKSBQb3NpY2FvX05hX1N0cmluZyBGcm9tIER1YWw7CgogU2VsZWN0IEluc3RyKCd0ZXN0ZSBlc3RhIHRlc3RhZG8nLCAndGUnLCAyKSBQb3NpY2FvX05hX1N0cmluZyBGcm9tIER1YWw7CgogU2VsZWN0IEluc3RyKCd0ZXN0ZSBlc3RhIHRlc3RhZG8nLCAndGUnLCAxLCAzKSBQb3NpY2FvX05hX1N0cmluZyBGcm9tIER1YWw7CgogU2VsZWN0IFN1YnN0cignMTExMSBUZXN0ZSB1bScsIDYpIEZyb20gRHVhbCAKVW5pb24gU2VsZWN0IFN1YnN0cignMTExMSBUZXN0ZSB1bScsIDYsIDUpIEZyb20gRHVhbDsKCiBTZWxlY3QgU291bmRleCgnUmFmYWVsJykgRnJvbSBEdWFsIApVbmlvbiBBbGwgU2VsZWN0IFNvdW5kZXgoJ1JhcGhhZWwnKSBGcm9tIER1YWw7[/et_pb_dmb_code_snippet]

Funções Matemáticas:
Round(Number, Integer)
Integer se for omitido default é 0;
Trunc(Number, Integer)
Integer se for omitido default é 0;
Remainder(Number1, Number2)
Indentifica a multiplicidade do Number2 em relação ao Number1 e retorna a diferença entre os valores;
Mod(Number1, Number2);

Exemplos:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U2VsZWN0IFJvdW5kKDEuODk5OTEsIDApLCAKUm91bmQoMS44OTk5MSwgMyksIApSb3VuZCgyNSwgLTEpLCAKUm91bmQoMy4xNzQ1MTY1NDU0LCAzKSwgClJvdW5kKDAuOTE2MjQxNTE1KSAKRnJvbSBEdWFsOwoKIFNlbGVjdCBUcnVuYygxLjg5OTkxLCAwKSwgClRydW5jKDEuODk5OTEsIDMpLCAKVHJ1bmMoMjUsIC0xKSwgClRydW5jKDMuMTc0NTE2NTQ1NCwgMyksIApUcnVuYygwLjkxNjI0MTUxNSkgCkZyb20gRHVhbDsKCiBTZWxlY3QgUmVtYWluZGVyKDMsIDIpLCBSZW1haW5kZXIoNCwgMiksIApSZW1haW5kZXIoNSwgMiksIFJlbWFpbmRlcig2LCAyKSBGcm9tIER1YWw7CgogU2VsZWN0IE1vZCgzLCAyKSwgTW9kKDQsIDIpLCAKTW9kKDUsIDMpLCBNb2QoNiwgMykgRnJvbSBEdWFsOw==” hover_enabled=”0″]U2VsZWN0IFJvdW5kKDEuODk5OTEsIDApLCAKUm91bmQoMS44OTk5MSwgMyksIApSb3VuZCgyNSwgLTEpLCAKUm91bmQoMy4xNzQ1MTY1NDU0LCAzKSwgClJvdW5kKDAuOTE2MjQxNTE1KSAKRnJvbSBEdWFsOwoKIFNlbGVjdCBUcnVuYygxLjg5OTkxLCAwKSwgClRydW5jKDEuODk5OTEsIDMpLCAKVHJ1bmMoMjUsIC0xKSwgClRydW5jKDMuMTc0NTE2NTQ1NCwgMyksIApUcnVuYygwLjkxNjI0MTUxNSkgCkZyb20gRHVhbDsKCiBTZWxlY3QgUmVtYWluZGVyKDMsIDIpLCBSZW1haW5kZXIoNCwgMiksIApSZW1haW5kZXIoNSwgMiksIFJlbWFpbmRlcig2LCAyKSBGcm9tIER1YWw7CgogU2VsZWN0IE1vZCgzLCAyKSwgTW9kKDQsIDIpLCAKTW9kKDUsIDMpLCBNb2QoNiwgMykgRnJvbSBEdWFsOw==[/et_pb_dmb_code_snippet]

Funçoes Date:
Sysdate – Hora corrente do sistema;
Round(Data1, Formato)
Formato é opcional;
Arredonda conforme o Formato, mesmo que Round visto nas funções numéricas;
Trunc(Data1, Formato)
Formato é opcional;
Trunca conforme o Formato, mesmo que Trunc visto nas funções numéricas;
Next_day(Data1, Referencia)
Referencia a qual dia da semana (SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY);
Pode estar em portugues o BD (SEGUNDA, TERÇA, QUARTA, QUINTA, SEXTA, SÁBADO, DOMINGO)

para descobrir: Select To_Char(Sysdate, ‘DAY’) From Dual;
Last_day(Data1);
Add_Months(Data1, Quantidade);
Months_Between(Data1, Data2);
Numtoyminterval(Numero, Unidade_Intervalo)
Unidade_Intervalo somente MONTH ou YEAR;
Converte o Numero para Ano ou Mês, conforme parametro;
Numtodsinterval(Numero, Unidade_Intervalo)
Unidade_Intervalo DAY, HOUR, MINUTE ou SECOND;

Exemplos:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IFNlbGVjdCBTeXNkYXRlIEZyb20gRHVhbDsKCiBTZWxlY3QgU3lzZGF0ZSwgUm91bmQoU3lzZGF0ZSwgJ21tJykgQXJyZW5kb25kYV9NZXMsIFJvdW5kKFN5c2RhdGUpIEFycmVkb25kYV9EYXRhLCAKUm91bmQoU3lzZGF0ZSwgJ1lZJykgQXJyZWRvbmRhX0FubyBGcm9tIER1YWw7CgogU2VsZWN0IFN5c2RhdGUsIFRydW5jKFN5c2RhdGUsICdtbScpIFRydW5jX0VzdGVfTWVzLCBUcnVuYyhTeXNkYXRlKSBUcnVuY19Fc3RlX0RpYSwgClRydW5jKFN5c2RhdGUsICdZWScpIFRydW5jX0VzdGVfQW5vIEZyb20gRHVhbDsKCiBTZWxlY3QgTmV4dF9EYXkoU3lzZGF0ZSwgJ1PDgUJBRE8nKSwgTmV4dF9EYXkoU3lzZGF0ZSwgJ1RFUsOHQScpLCAKTmV4dF9EYXkoU3lzZGF0ZSwgJ0RvbWluZ28nKSBGcm9tIER1YWw7CgogU2VsZWN0IExhc3RfRGF5KFN5c2RhdGUpIFVsdGltb19EaWFfTWVzIEZyb20gRHVhbDsKCiBTZWxlY3QgU3lzZGF0ZSwgYWRkX21vbnRocyhTeXNkYXRlLDUpIE1haXNfQ2luY29fTWVzZXMgRnJvbSBEdWFsOwoKIFNlbGVjdCBTeXNkYXRlLCBNb250aHNfQmV0d2VlbihTeXNkYXRlLCBTeXNkYXRlICsgMzY1KSwgCk1vbnRoc19CZXR3ZWVuKFN5c2RhdGUsIEFkZF9Nb250aHMoU3lzZGF0ZSwgNSkpLAogTW9udGhzX0JldHdlZW4oQWRkX01vbnRocyhTeXNkYXRlLCA1KSwgU3lzZGF0ZSkgRnJvbSBEdWFsOwoKIFNlbGVjdCBOdW10b3ltaW50ZXJ2YWwoMjcsICdNT05USCcpIEFub3NfUGFyYV9NZXMsIApOdW10b3ltaW50ZXJ2YWwoMSwgJ1lFQVInKSBBbm9zX1BhcmFfQW5vLCAKTnVtdG95bWludGVydmFsKDI0LCAnTU9OVEgnKSBBbm9zX1BhcmFfTWVzIEZyb20gRHVhbDsKCiBTZWxlY3QgTnVtdG9kc2ludGVydmFsKDYwLCAnTUlOVVRFJykgTWludXRvX0hvcmEsIApOdW10b2RzaW50ZXJ2YWwoMjMsICdEQVknKSBEaWFfSG9yYSwgCk51bXRvZHNpbnRlcnZhbCg2MCwgJ1NFQ09ORCcpIFNlZ3VuZG9fTWludXRvIEZyb20gRHVhbDs=” hover_enabled=”0″]IFNlbGVjdCBTeXNkYXRlIEZyb20gRHVhbDsKCiBTZWxlY3QgU3lzZGF0ZSwgUm91bmQoU3lzZGF0ZSwgJ21tJykgQXJyZW5kb25kYV9NZXMsIFJvdW5kKFN5c2RhdGUpIEFycmVkb25kYV9EYXRhLCAKUm91bmQoU3lzZGF0ZSwgJ1lZJykgQXJyZWRvbmRhX0FubyBGcm9tIER1YWw7CgogU2VsZWN0IFN5c2RhdGUsIFRydW5jKFN5c2RhdGUsICdtbScpIFRydW5jX0VzdGVfTWVzLCBUcnVuYyhTeXNkYXRlKSBUcnVuY19Fc3RlX0RpYSwgClRydW5jKFN5c2RhdGUsICdZWScpIFRydW5jX0VzdGVfQW5vIEZyb20gRHVhbDsKCiBTZWxlY3QgTmV4dF9EYXkoU3lzZGF0ZSwgJ1PDgUJBRE8nKSwgTmV4dF9EYXkoU3lzZGF0ZSwgJ1RFUsOHQScpLCAKTmV4dF9EYXkoU3lzZGF0ZSwgJ0RvbWluZ28nKSBGcm9tIER1YWw7CgogU2VsZWN0IExhc3RfRGF5KFN5c2RhdGUpIFVsdGltb19EaWFfTWVzIEZyb20gRHVhbDsKCiBTZWxlY3QgU3lzZGF0ZSwgYWRkX21vbnRocyhTeXNkYXRlLDUpIE1haXNfQ2luY29fTWVzZXMgRnJvbSBEdWFsOwoKIFNlbGVjdCBTeXNkYXRlLCBNb250aHNfQmV0d2VlbihTeXNkYXRlLCBTeXNkYXRlICsgMzY1KSwgCk1vbnRoc19CZXR3ZWVuKFN5c2RhdGUsIEFkZF9Nb250aHMoU3lzZGF0ZSwgNSkpLAogTW9udGhzX0JldHdlZW4oQWRkX01vbnRocyhTeXNkYXRlLCA1KSwgU3lzZGF0ZSkgRnJvbSBEdWFsOwoKIFNlbGVjdCBOdW10b3ltaW50ZXJ2YWwoMjcsICdNT05USCcpIEFub3NfUGFyYV9NZXMsIApOdW10b3ltaW50ZXJ2YWwoMSwgJ1lFQVInKSBBbm9zX1BhcmFfQW5vLCAKTnVtdG95bWludGVydmFsKDI0LCAnTU9OVEgnKSBBbm9zX1BhcmFfTWVzIEZyb20gRHVhbDsKCiBTZWxlY3QgTnVtdG9kc2ludGVydmFsKDYwLCAnTUlOVVRFJykgTWludXRvX0hvcmEsIApOdW10b2RzaW50ZXJ2YWwoMjMsICdEQVknKSBEaWFfSG9yYSwgCk51bXRvZHNpbnRlcnZhbCg2MCwgJ1NFQ09ORCcpIFNlZ3VuZG9fTWludXRvIEZyb20gRHVhbDs=[/et_pb_dmb_code_snippet]

RESPOSTAS:

Os SQLs abaixo executam?

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IFNlbGVjdCAnVEVTVEUnCiBGcm9tIER1YWwKIFdoZXJlICdURVNURScgPSBOdWxsOwoKU2VsZWN0ICdURVNURScKIEZyb20gRHVhbAogV2hlcmUgTnVsbCA9IE51bGw7ClNJTSBOQU8KTkFPIE5BTwpTSU0gU0lNCk5BTyBTSU0=” hover_enabled=”0″]IFNlbGVjdCAnVEVTVEUnCiBGcm9tIER1YWwKIFdoZXJlICdURVNURScgPSBOdWxsOwoKU2VsZWN0ICdURVNURScKIEZyb20gRHVhbAogV2hlcmUgTnVsbCA9IE51bGw7ClNJTSBOQU8KTkFPIE5BTwpTSU0gU0lNCk5BTyBTSU0=[/et_pb_dmb_code_snippet]

Book: SQL Certified Expert Exam Guide, Steve O’Hearn pela Oracle Press
Link Book na Amazon

Att,
capin