Usando procedimentos armazenados na análise incorporada

O uso de procedimentos armazenados é uma maneira fácil de economizar tempo de escrever frequentemente as mesmas linhas de código e também agilizar a chamada e a execução da consulta desejada.

Mas o que você sabe sobre procedimentos armazenados, como eles funcionam e quais vantagens eles oferecem aos usuários?

Neste artigo, você aprenderá o que é um procedimento armazenado, seus benefícios, como ele se diferencia em comparação com funções, como criar um procedimento armazenado e, por último, mas não menos importante, como usar procedimentos armazenados em análises incorporadas.

O que é um procedimento armazenado?

Um procedimento armazenado é um conjunto de instruções SQL e outras construções PL/SQL armazenadas em um sistema de gerenciamento de banco de dados relacional (RDBMS) como um grupo que você pode salvar e reutilizar repetidamente. Um procedimento armazenado pode consistir em várias instruções SQL, como SELECT, INSERT, UPDATE ou DELETE. Eles são executados como uma unidade e são usados para resolver um problema específico ou executar um conjunto de tarefas relacionadas. Isso significa que, se você precisar de uma consulta SQL e escrevê-la repetidamente, poderá salvá-la como um procedimento armazenado e chamá-la para executar a consulta.

Se você estiver usando o Microsoft SQL Server, poderá encontrar procedimentos armazenados expandindo o nó Programabilidade em seu banco de dados no Pesquisador de Objetos do SQL Server Management Studio.

Benefícios do uso de procedimentos armazenados

Os procedimentos armazenados são um dos assuntos de banco de dados mais usados e populares e, como tal, oferecem alguns benefícios. O uso de procedimentos armazenados pode simplificar e acelerar a execução de consultas SQL.

Por exemplo, o armazenamento de procedimentos pode reduzir o tráfego de rede entre servidores e clientes. Isso é resultado dos comandos serem executados como um único lote de código – portanto, em vez de cada linha de código ser enviada para execução individualmente, apenas a chamada para executar o procedimento armazenado é enviada ao servidor.

Aqui estão alguns dos outros benefícios do uso de procedimentos armazenados:

Manutenibilidade

Manter um procedimento armazenado é bastante fácil no servidor, em contraste com a manutenção de cópias em várias máquinas clientes.

Além disso, os scripts de procedimentos são armazenados em um local, mas os procedimentos armazenados também podem ser usados livremente em outros aplicativos. No caso de uma alteração de definição, isso não é um problema, pois apenas o procedimento armazenado será afetado e não o aplicativo que o chama.

Desempenho e Eficiência

Os procedimentos armazenados são compilados apenas uma vez e podem ser usados várias vezes. Isso economiza muito tempo escrevendo o mesmo várias vezes se você estiver fazendo consultas ad hoc e oferece uma ligeira vantagem de desempenho na primeira execução de um procedimento armazenado em comparação com a primeira execução de uma consulta ad hoc.

Veja Reveal em ação

Inclui lógica de negócios complexa

Embora não seja o melhor lugar para lógica de negócios complexa, um procedimento armazenado é basicamente uma chamada de função em um banco de dados. Você pode incluir instruções IF, LOOPS, verificações de segurança e muito mais para garantir que os dados corretos estejam sendo enviados de volta para a chamada do procedimento armazenado.

Embora um tanto discutíveis com base nos conjuntos de habilidades de sua equipe, as práticas recomendadas tendem a colocar a lógica de negócios de um aplicativo no lado do aplicativo. No entanto, os procedimentos armazenados e o T-SQL são sintaxes muito poderosas para a execução lógica.

Fácil de modificar

Você pode alterar rapidamente uma ou mais instruções em um procedimento de armazenamento com a ajuda do comando ALTER PROCEDURE, o que torna muito rápido e fácil atualizar os resultados enviados de volta ao aplicativo de chamada. Ao contrário de um componente de camada intermediária que inclui lógica de negócios, que precisaria de uma reimplantação potencialmente cara, uma alteração rápida em um procedimento armazenado leva segundos.

Considere este procedimento armazenado, em que o valor @OrdYear é codificado. Um desenvolvedor ou administrador de banco de dados pode atualizar a variável @OrdYear com facilidade usando o comando ALTER PROCEDURE.

ALTER PROCEDURE [dbo].[SalesByCategory] @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998' AS IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' BEGIN SELECT @OrdYear = '1998' END SELECT ProductName, TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0) FROM [Order Details] OD, Orders O, Products P, Categories C WHERE OD.OrderID = O.OrderID AND OD.ProductID = P.ProductID AND P.CategoryID = C.CategoryID AND C.CategoryName = @CategoryName AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear GROUP BY ProductName ORDER BY ProductName

Segurança

Outro grande benefício do uso de procedimentos armazenados é a segurança que ele fornece à sua fonte de dados. Você pode aumentar a segurança de um aplicativo usando procedimentos armazenados restringindo o acesso não autorizado. Por exemplo, você pode conceder a alguém acesso a um procedimento que atualiza a tabela do banco de dados sem permitir o acesso à própria tabela. Você também pode usar procedimentos armazenados para "ocultar" os nomes de tabela subjacentes do usuário final do procedimento armazenado. Como o chamador do procedimento armazenado tem apenas o nome do procedimento armazenado, não o nome das tabelas usadas no procedimento armazenado, ele não poderá acessar diretamente as tabelas com seus dados de forma mal-intencionada.

Diferenças entre procedimentos armazenados e funções

Procedimentos armazenados e funções são objetos de banco de dados que contêm conjuntos de instruções SQL para concluir tarefas. E embora possam parecer a mesma coisa à primeira vista, são bem diferentes.

Estas são algumas das coisas que diferenciam os procedimentos armazenados e as funções:

• As funções podem ser chamadas de um procedimento armazenado, enquanto o procedimento armazenado não pode ser chamado de uma função.
• Uma função não permite parâmetros de saída, enquanto um procedimento armazenado permite parâmetros de saída e entrada.
• Você não pode gerenciar transações dentro de uma função, mas pode fazê-lo dentro de um procedimento armazenado.
• Você pode chamar uma função usando uma instrução select, mas não pode chamar um procedimento armazenado usando uma instrução select.
• Uma função tem um tipo de retorno e retorna um valor, enquanto um procedimento armazenado não tem um tipo de retorno e o retorno de um valor é opcional.
• Uma função permite apenas uma instrução SELECT nela, enquanto um procedimento armazenado permite SELECT, INSERT, UPDATE E DELETE.
• Um procedimento armazenado pode ser usado para ler e modificar dados, enquanto uma função só pode lê-los.
• Uma função pode ser inserida em uma instrução SELECT, enquanto um procedimento armazenado não pode ser utilizado em uma instrução SELECT.
• Uma função não pode retornar vários conjuntos de resultados, enquanto um procedimento armazenado pode.
• Podemos usar transações em procedimentos armazenados, mas elas não são permitidas em funções.

Como criar um procedimento armazenado?

Criar um novo procedimento armazenado é simples. Existem 3 etapas básicas:

1. No Pesquisador de Objetos do SQL Server, clique com o botão direito do mouse no nó Procedimentos Armazenados em Programabilidade.

2. No Novo modelo de procedimento armazenado, modifique a instrução CREATE PROCEDURE para incluir as tabelas, os parâmetros e as opções de consulta para retornar os dados desejados do procedimento armazenado.

3. Clique no botão Executar na barra de ferramentas para compilar e salvar o procedimento armazenado.

Existem várias ferramentas para criar procedimentos armazenados, usar o SQL Server Management Studio interativo é o mais fácil, pois oferece uma maneira fácil de começar a se conectar a muitos tipos diferentes de servidores de banco de dados, incluindo Azure e SQL Server Analysis Services.

Como usar os procedimentos da loja na análise incorporada?

Ao usar o SDK de inserção do Reveal, há 2 maneiras de usar procedimentos armazenados.

1. Ao criar uma nova visualização em um banco de dados que dá suporte a procedimentos armazenados, você verá uma lista de procedimentos armazenados que estão disponíveis para você. Você tratará o produto armazenado selecionado como qualquer outra fonte de dados em Reveal, a única diferença é um procedimento armazenado parametrizado, que você pode usar em um Filter passando variáveis para o procedimento armazenado.

Você pode assistir a um vídeo rápido de 6 minutos sobre como usar procedimentos armazenados por meio da interface do usuário do Reveal aqui:

2. Como Reveal é um SDK, você também tem controle programático completo sobre procedimentos armazenados e parâmetros por meio de código. Para passar um nome de procedimento armazenado, juntamente com os parâmetros necessários, use a função ChangeVisualizationDataSourceItemAsync. Este exemplo demonstra a passagem de um procedimento armazenado chamado CustOrderHist e a passagem do parâmetro CustomerID.

public async override Task<RVDataSourceItem> ChangeVisualizationDataSourceItemAsync (string userId, string dashboardId, RVVisualization visualization, RVDataSourceItem dataSourceItem) { if (dashboardId == "StoredProcNorthWind") { var msSqlDsi = dataSourceItem as RVSqlServerDataSourceItem; if (msSqlDsi?.Procedure == "CustOrderHist") { msSqlDsi.ProcedureParameters["@CustomerID"] = "AROUT"; } else { msSqlDsi.Procedure = "MyProcedure"; msSqlDsi.ProcedureParameters["@SampleParam"] = "SampleParamValue"; } return dataSourceItem; } }

Embrulhar

Os procedimentos armazenados são uma ferramenta poderosa para consultar um banco de dados e retornar resultados para um aplicativo de chamada. Você deve considerar o uso de procedimentos armazenados para simplificar a maneira como você apresenta dados para Reveal usuários do SDK e para simplificar a maneira como você lida com parâmetros no código do aplicativo em vez de usar consultas ad hoc longas e propensas a erros.

Você pode saber mais sobre Reveal análise incorporada e como ela funciona baixando nosso SDK ou agendando uma demonstração rápida.