ASP – Iniciando Stored Procedures
Stored Procedures …
Considere esta procedimentos armazenados…
Um breve e muito útil tutorial sobre como criar e usar procedimentos armazenados com seus aplicativos ASP.
O que é um procedimento armazenado?
Um procedimento armazenado não é nada mais do que um comando SQL armazenado dentro de um banco de dados. O banco de dados pode ser o SQL Server, MS Access,MySQL ou assim como outros … Um procedimento armazenado é compilado pelo seu banco de dados (em sua maior parte), quando é introduzido. Isso resulta em desempenho global de execuções e atualizações mais rápida , ele ainda separa a declaração do seu sql asp deixando-o com código mais legível.
Escrever procedimentos armazenados
Stored procedures são escritas usando o SQL (Structured Query Language). A parte mais importante de escrever um procedimento armazenado é a declaração CREATE PROCEDURE. Sem entrar em todas as partes opcionais do processo para criar, aqui está:
CREATE PROCEDURE [sp_]ProcedureName
(
parameters
)
AS
sql statement – pode ser INSERT, DELETE, SELECT, UPDATE…
A sintaxe básica de uma Stored Procedures é a seguinte:
1. CREATE PROCEDURE nome_da_SP
2. @nome_do_Parametro1 tipo_de_dados1,
3. @nome_do_Parametro2 tipo_de_dados2 OUTPUT
4. @nome_do_Parametro3 tipo_de_dados3,
5. AS sql_declaração
onde :
- Cria a Stored Procedures com o nome determinado
- Cria o parâmetro de entrada – nome_do_parametro1 – cujo tipo de dados é – tipos_de_dados1
- Cria o parâmetro de SAIDA ( OUTPUT) – nome_do_parametro2 – com o tipo de dados – tipos_de_dados2
- Cria o parâmetro de entrada – nome_do_parametro3 – cujo tipo de dados é – tipos_de_dados3
As principais motivações para a construção de stored procedures são: modularização de funcionalidades que mais de uma aplicação cliente necessitaria facilitando o reaproveitamento de código, separação de regra de negócio da aplicação cliente, concentração de lógica de manipulação de dados no banco de dados.
Atualmente, com o crescimento dos servidores de aplicações a tendência é que a programação de regra de negócio seja retirada de stored procedures e colocadas em componentes de negócio nesses servidores. Em stored procedures ficariam apenas programas que específicos para manipulação de dados como migrações entre bases.
Bem isso é muito inútil … Vamos tentar uma abordagem diferente. Suponha que você está trabalhando com o banco de dados teste.mdb, e você quis selecionar todos os novos exemplos a partir da base de dados. Seu padrão de instrução SQL seria provavelmente, usando a clásula WHERE ,que é usada para extrair apenas os registros que cumprir um determinado critério.
Sintaxe SQL WHERE
SELECT column_name(s)
FROM table_name
WHERE column_name operator value
Seguindo o exemplo:
SELECT * FROM exemplos WHERE new = 1;
Agora vamos transformar a instrução SQL em um procedimento armazenado.
CREATE PROCEDURE sp_GetNewExemplos
AS
SELECT * FROM exemplos WHERE new = 1
Stored Procedures no banco de dados.
Existem algumas maneiras de fazer um procedimento armazenado em seu banco de dados. A maneira que eu te mostrarei trabalhará para MS Access ou o SQL Server e deve ser bastante familiar.Você precisará criar um arquivo que servirá como procedimento no sql server/Access para carregar página. Para resolver este problema, usaremos uma app consistindo de uma forma simples de rotina para acessar o db e adicionar o nosso procedimento.
O código fonte para “add.asp”
<% @ Language = vbscript %>
<%
Option Explicit
Response.Buffer = True
Private Sub SQLExec(byVal sql)
dim c
set c = server.createobject(“adodb.connection”)
c.Open Application(“dbConn”)
c.Execute sql
c.Close
set c = Nothing
Response.Write “<h1><big>PROCEDURE ADICIONADA!</big></h1>”
End Sub
dim tmp : tmp = Request.Form(“SQL”)
If left( ucase( trim( tmp ) ), 16 ) = “CREATE PROCEDURE” OR _
left( ucase( trim( tmp ) ), 14 ) = “DROP PROCEDURE” Then
Call SQLExec( trim( tmp ) )
Else
%>
<FORM ACTION=”./add.asp” METHOD=POST>
<INPUT TYPE=SUBMIT VALUE=”Add Procedure”><BR><BR>
<TEXTAREA NAME=”SQL” COLS=65 ROWS=45></TEXTAREA><BR><BR>
<INPUT TYPE=SUBMIT VALUE=”Add Procedure”></FORM>
<%
End If
%>
Para usar este aplicativo, basta nomear “add.asp” e colocá-lo em algum lugar do seu servidor (não faria mal nenhum a acrescentar alguma senha de proteção). Acesse a página através da web e digite o novo procedimento armazenado no formulário de entrada assim:
CREATE PROCEDURE sp_GetNewExamplos
AS
SELECT * FROM examples WHERE new = 1
Clique no botão enviar e o processo é adicionado à base de dados!
Chamando procedimentos armazenados em páginas ASP
SELECT * FROM exemplos WHERE new = 1;
Suponhamos que expandimos o SQL original e o resto do documento asp ficou assim:
<% @ Language = vbscript %>
<%
Option Explicit
Response.Buffer = True
dim c, r, sql
‘ Este é o velho SQL que deve ser substituído
sql = “SELECT * FROM exemplos WHERE new = 1;”
set c = server.createobject(“adodb.connection”)
c.Open Application(“dbConn”)
set r = c.Execute(sql)
do while not r.bof and not r.eof
response.write r(“exemploName”) & “<BR>”
r.movenext
loop
r.close
c.Close
set r = Nothing
set c = Nothing
%>
Substituir as velhas statement é fácil. Você sabe o nome do stored procedure sp_GetNewExamples assim tudo que você precisa fazer é colocar o SQLserver/access à palavra-chave antes de executar o nome da stored procedure:
EXECUTE sp_GetNewExemplos
A nova string sql é como este:
<% @ Language = vbscript %>
<%
Option Explicit
Response.Buffer = True
dim c, r, sql
‘ Este é o novo comando SQL que chama
‘a Stored Procedure
…..
sql = “EXECUTE sp_GetNewExamples”
set c = server.createobject(“adodb.connection”)
c.Open Application(“dbConn”)
set r = c.Execute(sql)
do while not r.bof and not r.eof
response.write r(“exampleName”) & “<BR>”
r.movenext
loop
r.close
c.Close
set r = Nothing
set c = Nothing %>
O procedimento é adicionado e está sendo usado. Bem, isso é grande, mas como é que se crie um processo complexo que é reutilizável e tem entrada diferente cada vez que se executa.
Stored Procedures Mais Complexas
Suponha que temos um novo comando SQL:
SELECT
*
FROM
exemplos
WHERE
exemploName LIKE ‘%” & Request.Form(“frmName”) & “%’
OR
exemploDesc LIKE ‘%” & Request.Form(“frmName”) & “%’
ORDER BY
exemploName;
Nesta declaração criamos uma string sql com base no formulário de entrada “frmName”, que é usado para pesquisar dois campos na base de dados. Para criar Stored Procedure baseada nesta nova string sql, vamos ter de criar um parâmetro para criar a nossa declaração de procedimento. Um parâmetro é uma variável ou grupo de variáveis que o SQLserver/access irá utilizar e aceitar dados de fontes externas. Abaixo:
CREATE PROCEDURE sp_SrchNomeDesc
(
@frmInput1 VarChar(255),
@frmInput2 Text
)
AS
SELECT
*
FROM
exemplos
WHERE
exemploName LIKE @frmInput1
OR
exemploDesc LIKE @frmInput2
ORDER BY
examploNome;
:
|
sql server datatype |
ms access datatype |
notas |
|
Int |
Number |
|
|
Char(x) |
Text |
No acesso a design view, utilizar o Campo propriedade do domínio em questão para determinar o valor de x |
|
Text |
Memo |
|
|
DateTime |
Date/Time |
|
|
Money |
Currency |
|
|
Bit |
Yes/No |
|
Os parâmetros são separados do resto da declaração create procedure com parênteses e vários parâmetros são separados por vírgulas,depois de adicionar o nosso novo procedimento para o banco de dados, temos de substituir o nosso velho SQL com a nova chamada para Stored Procedure:
Old SQL Statement:
SELECT
*
FROM
exemplos
WHERE
exemploNome LIKE ‘%” & Request.Form(“frmName”) & “%’
OR
exemploDesc LIKE ‘%” & Request.Form(“frmName”) & “%’
ORDER BY
exemploNome;
Novo SQL Statement:
Isto pode ser escrito de maneiras diferentes:
1.) com nomes de variável sql procedure:
“EXECUTE sp_SrchNameDesc @frmInput1=’%” & Request.Form(“frmName”) & _
“%’, @frmInput2=’%” & Request.Form(“frmName”) & “%’”
2.) sem nomes de variável:
“EXECUTE sp_SrchNameDesc ‘%” & Request.Form(“frmName”) & “%’, ‘%” & _
Request.Form(“frmName”) & “%’”
Nota: Se você não especificar nomes da sql variável, você deve digitar o input na mesma ordem, conforme especificado no procedimento.
Modificando e eliminando procedimentos armazenados
DROP PROCEDURE ProcedureName
Você percebe os parâmetros diretamente após o nome do procedimento. Uma vez que queremos ser bons programadores, vamos escrever todas as nossas procedure statements para trabalhar no SQL Server,também vão trabalhar sobre o Access, bem. TODAS VARIÁVEIS procedure deve começar com @. O acesso não importa. Imediatamente após o nome da variável vem o datatype do campo. Nós estaremos usando o SQL Server datatypes porque Access irá convertê-los para acessar o SQL Server equivalentes, mas não irá converter formatos de Access. Este quadro pode servir como uma orientação:
Vamos criar uma SP para selecionar todos os registros de uma tabela da base de dados.
StoredProcedure Mostrar:
CRAEATE PROCEDURE mostrar
AS
SELECT * FROM tabela
Usando a SP criada Mostrar. O código que usa esta SP em uma página ASP é :
|
<% Dim DbConn , rsProdutos on error resume next Set DbConn = Server.CreateObject(“ADODB.Connection”) sql = “execute Mostrar “ DbConn.Open “Provider=SQLOLEDB;Data Source=teste;Initial Catalog=TesteWind;Integrated Security=SSPI” set rsProdutos = DbConn.Execute(sql) While Not rsProdutos.EOF Response.write rsProdutos(0) & ” ” rsProdutos.MoveNext Wend rsProdutos.Close %> |
Inserindo dados
A stored procedure Incluir para incluir dados na tabela Categorias teria o seguinte código :
CRAEATE PROCEDURE Incluir
@Codigo int,
@Nome varchar(60),
@Descricao varchar(120)
AS
INSERT INTO tabela (CODIGO,nOME,DESCRICAO) VALUES (@codigo,@Nome,@Descricao)
GO
Nesta StoredProcedure temos 3 parâmetros que deverão ser informados.
|
<% Dim DbConn , rsCategorias on error resume next ‘obtem os valores das variáveis informadas no formulário e remove os espaços ‘Monta a instrução que chama a stored procedure passando os valores dos parâmetros Set DbConn = Server.CreateObject(“ADODB.Connection”) DbConn.Open “Provider=SQLOLEDB;Data Source=teste;Initial Catalog=TesteWind;Integrated Security=SSPI” set rsCategorias = DbConn.Execute(sql) rsCategorias.Close %> |
Atualizando dados
A atualização de dados utiliza a instrução SQL – UPDATE/SET. A stored procedure Atualizar atualiza a tabela:
CRAEATE PROCEDURE Atualizar
@Codigo int,
@Descricao varchar(120)
AS
UPDATE tabela SET DESCRICAO=@Descricao)
WHERE Codigo = @Codigo
GO
ASP abaixo que executará a stored procedure.
|
<% Dim DbConn , rsCategorias on error resume next ‘obtem os valores das variáveis informadas no formulário e remove os espaços ‘Monta a instrução que chama a stored procedure passando os valores dos parâmetros Set DbConn = Server.CreateObject(“ADODB.Connection”) DbConn.Open “Provider=SQLOLEDB;Data Source=teste;Initial Catalog=TesteWind;Integrated Security=SSPI” set rsCategorias = DbConn.Execute(sql) rsCategorias.Close %> |
Excluindo dados
A exclusão de dados é feita via instrução SQL DELETE/FROM (sempre utilize esta instrução usando a cláusula WHERE para especificar o que você deseja excluir , senão…). Abaixo temos a stored procedure:
CRAEATE PROCEDURE Excluir
@Codigo int,
AS
DELETE FROM tabela
WHERE Codigo = @Codigo
GO
O script ASP que utiliza esta SP é :
|
<% Dim DbConn , rsCategorias on error resume next ‘obtem os valores das variáveis informadas no formulário e remove os espaços
‘Monta a instrução que chama a stored procedure passando os valores dos parâmetros DbConn.Open “Provider=SQLOLEDB;Data Source=TESTE;Initial Catalog=TesteWind;Integrated Security=SSPI” set rsCategorias = DbConn.Execute(sql) rsCategorias.Close %> |
Uma stored procedure pode ser escrita em diferentes linguagens, tais como COBOL, C, C++, PL/SQL, FORTRAN, Assembly e Java, dependendo apenas do suporte a linguagens que o servidor de banco de dados oferece.
Escrito por Autor do Artigo
X@nBuRzUm
Últimos Comentários