Diferenças entre FUNCTION e PROCEDURE

Uma function é um bloco PL/SQL muito semelhante a uma procedure.

O que podemos entender de início entre esses dois tipos de blocos é que os blocos functions retornam valores e as procedures podem ou não retornar um valor.

As functions tem duas características que diferem das procedures, as quais não podemos deixar de tratar:

  • As functions sempre retornam valores;
  • Functions são usadas como parte de uma expressão.

Para que possamos criar uma função no PL/SQL precisamos entender o seu funcionamento e para isso precisamos entender a sua sintaxe básica. Vejamos:

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

  • CREATE [OR REPLACE] FUNCTION: Caso uma function já exista com o mesmo nome, ela será reescrita devido ao termo ‘replace’. Caso contrário, ela será criada de acordo com o termo ‘create’.
  • Function_name: Será o nome atribuído para essa função.
  • Parameters: a lista opcional de parâmetros contém os nomes, os modos e os tipos que esses parâmetros terão. O IN representa o valor que será passado de fora, já o OUT representa que este parâmetro será utilizado para retornar um valor de fora do procedimento.
  • Return_datatype: é o tipo de retorno que será utilizado, sendo este SQL ou PL/SQL. Podemos neste caso utilizar referências como o %TYPE ou %ROWTYPE se necessário, ou mesmo utilizar qualquer tipo de dados escalar ou composto.
  • IS/AS: por convecção, temos o ‘is’ para a criação de funções armazenadas e o ‘as’ quando criamos pacotes (packages).
  • function_body: contém o bloco PL/SQL que inicia com a claúsula BEGIN e finaliza com END [function_name], e executa neste momento todas as instruções necessárias.

Podemos executar a função de várias formas:

Declarando como variável:

func_nome := funcao_de_teste;

Ou então como parte de uma instrução select:

SELECT funcao_de_teste FROM dual;

Ou também como uma instrução PL/SQL:

dbms_output.put_line(funcao_de_teste);

Uma procedure normalmente possui um cabeçalho e um corpo.

O cabeçalho consiste do nome e de parâmetros ou variáveis que serão passadas para a procedure. Já o corpo consiste da declaração de uma seção, execução de uma seção e uma seção de exceções muito similar a um bloco geral da PL/SQL.

Uma procedure pode ou não ter um valor de retorno. Normalmente as procedures são criadas dentro de pacotes ou em blocos PL/SQL.

Podemos passar os parâmetros para uma procedure de três maneiras:

  • Parâmetros IN – passamos o valor na própria procedure;
  • Parâmetros OUT – recebemos o valor a partir da chamada de blocos externos;
  • Parâmetros IN OUT – passamos um valor inicial para a procedure e recebemos de volta uma atualização.

Vejamos a sintaxe geral (estrutura básica) da criação de uma procedure:

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
IS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;

O ‘Is’ marca o início do corpo de uma procedure e é bem similar ao DECLARE de um bloco anônimo PL/SQL. O código criado entre o IS e o BEGIN forma a seção de declaração da procedure.

A sintaxe entre os colchetes [] indica que é opcional. Mas de igual forma a uma função, a utilização do CREATE é para criar uma procedure e o REPLACE irá sobrepor uma procedure existente com novas informações de código.

Para utilizar uma Procedure:

  • Usando a palavra-chave EXECUTE;
  • Chamando o nome da procedure de um bloco PL/SQL.

No primeiro caso, executamos da seguinte forma:

EXECUTE procedure_de_teste;

E no nosso outro caso, que é a partir de um bloco, temos:

BEGIN
procedure_de_teste;
END;

Para apagar uma procedure ou uma function, basta utilizar o comando DROP. Exemplos:

  • drop function funcao_de_teste;
  • drop procedure procedure_de_teste.

Abraço a todos.

Fontes para consulta:

http://www.devmedia.com.br/pl-sql-functions-e-procedures/29882
https://codigosimples.net/2016/02/24/principais-diferencas-entre-stored-procedures-e-functions/
http://www.profissionaloracle.com.br/gpo/servicos/easyblog/entry/2009/09/25/oracle-plsql-funcoes-functions-e-procedures

Diferença entre banco de dados, instância, schema, tablespaces e etc.

Vamos a um pouco de teoria de banco de dados. Tenho certeza que será muito útil para os universitários. 🙂

Banco de dados:

Banco de dados (ou base de dados), é um conjunto de registros dispostos em estrutura regular que possibilita a reorganização dos mesmos e produção de informação. Um banco de dados normalmente agrupa registros utilizáveis para um mesmo fim.
Um banco de dados é usualmente mantido e acessado por meio de um software conhecido como Sistema Gerenciador de Banco de Dados (SGBD) – um exemplo é o Oracle, mysql, SQL Server, POSTGRE. Normalmente um SGBD adota um modelo de dados, de forma pura, reduzida ou estendida. Muitas vezes o termo banco de dados é usado, de forma errônea, como sinônimo de SGDB.
O modelo de dados mais adotado hoje em dia é o modelo relacional, onde as estruturas têm a forma de tabelas, compostas por tuplas (linhas) e colunas.

Instâncias:

Os principais componentes de um típico servidor comercial são uma ou mais CPU’s, espaço em discos e memória. Enquanto o banco de dados Oracle é armazenado em um disco do servidor, uma instância Oracle existe na memória do servidor. Uma instância Oracle é composta de um grande bloco de memória alocado em uma área chamada System Global Area (SGA), juntamente com alguns processos em segundo plano que interagem entre SGA e os arquivos de banco de dados no disco.
Resumindo na linguagem do Oracle podemos definir que uma instância Oracle é a combinação da memória e dos processos que são parte de uma instalação em funcionamento e/ou a instância é usada para a gerência e acesso ao banco de dados.

Schema:

Um schema é representado por uma coleção de vários objetos de um ou mais usuário de banco de dados como exemplo: tabelas, seqüências, índices, etc. São associados a um banco de dados na razão de vários esquemas para um BD.

Tablespace e Datafile:

Um banco de dados é armazenado logicamente em uma ou mais tablespaces que, por sua vez, é armazenada fisicamente no disco em um ou mais arquivos para cada tablespace.

Extensões:

A Extensão (extent) é o próximo nível de agrupamento lógico no banco de dados – existente em apenas um datafile. Uma extent consiste em uma ou mais blocos de banco de dados. Quando um objetivo de banco de dados é expandido, o espaço adicionado ao objetivo é alocado como uma extensão

Tabelas (tables):

Imaginamos um documento do excel, onde temos vários colunas e linhas, nela temos vários informações de cliente, com nomes, telefones, endereços, CPF e entre outros. O documento do Excel é uma Tabela. Uma explicação do Livro: O manual do DBA (Kevin and Loney) – Uma tabela é a unidade básica de armazenamento em um banco de dados. Sem tabelas, um banco de dados não tem valor para uma empresa. Independente do tipo de tabela, os dados em uma tabela são armazenados em linhas e colunas, de maneira semelhante ao Excel da Microsoft.

Tabela relacional:

O modelo relacional é um modelo de dados, adequado a ser o modelo subjacente de um Sistema Gerenciador de Banco de Dados (SGBD), que se baseia no princípio em que todos os dados estão guardados em tabelas (ou, matematicamente falando, relações). Toda sua definição é teórica e baseada na lógica de predicados e na teoria dos conjuntos.
O Oracle suporta modelos relacionais e modelos relacionais de objeto.

Referências:

http://pt.wikipedia.org

http://www.kich.com.br

Livros: Oracle 10g – O manual do DBA (Kevin Loney e Bob Bryla) e Banco de dados Oracle 10g: Fundamentos de SQL I

Trabalhando com HASHBYTES no SQL Server

Ola para todos!

Hoje precisei criar uma função para gerar um Hash SHA2_512 dentro de um banco SQL Server 2016.

Para quem não conhece ou não sabe, uma função hash é um algoritmo que mapeia dados de comprimento variável para dados de comprimento fixo. Os valores retornados por uma função hash são chamados valores hash, códigos hash, somas hash (hash sums), checksums ou simplesmente hashes. É muito utilizado para armazenar senhas… cria-se um hash da senha do usuário e o resultado é armazenado, não deixando no banco a senha original.

Importante dizer: o hash não é uma criptografia. Ou seja: não há retorno. No caso da senha, por exemplo, será necessário fazer o comparativo pelo hash.

Imagine um formulário de login, por exemplo. Quando ele for submetido pelo usuário, o sistema deverá gerar o hash code do que foi preenchido no campo de senha e comparar com o hash que está no banco.

O SQL Server possui uma função nativa para retornar hash nos modelos mais utilizados (MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512). Chama-se HASHBYTES.

HASHBYTES ( ‘algorítimo’, { @input | ‘input’ } )
algorítimo::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

Exemplo:

DECLARE @HashThis nvarchar(4000);
SET @HashThis = CONVERT(nvarchar(4000),’dslfdkjLK85kldhnv$n000#knf’);
SELECT HASHBYTES(‘SHA1’, @HashThis);

No meu caso, para facilitar, eu criei uma função… daí fica mais fácil para desenvolvedores inexperientes fazerem o uso correto. Segue abaixo o código:

CREATE OR ALTER FUNCTION HashSenhaUsuario(@valorSenha VARCHAR(max))
RETURNS VARCHAR(max)
   BEGIN
   RETURN(select convert(varchar,HASHBYTES(‘SHA2_512’,@valorSenha)))
END

A utilização fica mais ou menos assim:

select (HashSenhaUsuario(‘teste’))

Espero ter ajudado.
Obrigado pela visita.

Diferença entre comandos DDL, DML, DCL e TCL

DDL – Data Definition Language ( DDL) são usadas para definir a estrutura de banco de dados ou esquema. Alguns exemplos:

CREATE- para criar objetos no banco de dados
ALTER – altera a estrutura da base de dados
TRUNCATE – remover todos os registros de uma tabela, incluindo todos os espaços alocados para os registros são removidos
COMMENT – adicionar comentários ao dicionário de dados
RENAME – para renomear um objeto

DML – Data Manipulation Language ( DML) são utilizados para o gerenciamento de dados dentro de objetos do banco. Alguns exemplos:

SELECT- recuperar dados do banco de dados
INSERT – inserir dados em uma tabela
UPDATE – atualiza os dados existentes em uma tabela
DELETE – exclui registros de uma tabela,
CALL – chamar um subprograma PL / SQL
EXPLAIN PLAN – explicar o caminho de acesso aos dados
LOCK TABLE – controle de concorrência

DCL – Data Control Language ( DCL ) declarações. Alguns exemplos:

GRANT – atribui privilégios de acesso do usuário a objetos do banco de dados
REVOKE – remove os privilégios de acesso aos objetos obtidos com o comando GRANT

TCL – Transaction Control Language – (Controle de Transações) são usados ​​para gerenciar as mudanças feitas por instruções DML . Ele permite que as declarações a serem agrupadas em transações lógicas .

COMMIT – salvar o trabalho feito
SAVEPOINT – identificar um ponto em uma transação para que mais tarde você pode efetuar um ROLLBACK
ROLLBACK – restaurar banco de dados ao original desde o último COMMIT

Procurando textos nos objetos do Oracle

Pessoal, algumas vezes precisamos buscar uma palavra no nosso banco de dados e não sabemos como fazer.
Pois bem, segue uma query que pode ajudar muito.

SELECT *
FROM ALL_SOURCE
WHERE UPPER(TEXT) LIKE ‘%TEXTO%’

Nessa query, ele irá procurar a palavra “TEXTO” em todos os objetos do banco, se encontra algum objeto que contenha, ele irá mostrar.

Espero que tenha ajudado.