[Resolvido] Stored Procedures ...

[xanburzum 169]

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 :

1. Cria a Stored Procedures com o nome determinado

2. Cria o parâmetro de entrada - nome_do_parametro1 - cujo tipo de dados é - tipos_de_dados1

3. Cria o parâmetro de SAIDA ( OUTPUT) - nome_do_parametro2 - com o tipo de dados - tipos_de_dados2

4. 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.

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.

 

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

Long Number

AutoNumber

Char(x)

VarChar(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

SmallMoney 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

 

A declaração DROP PROCEDURE deve ser utilizado para remover um procedimento armazenado. Se você quiser atualizar um procedimento, você deve primeiro “dar um drop” e recarregá-lo a um novo processo com o mesmo nome.

 

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 é :

 

<%
Option Explicit
Dim DbConn , rsProdutos
Dim sql
on error resume next
Set DbConn = Server.CreateObject("ADODB.Connection")
Set rsProdutos = Server.CreateObject("ADODB.Recordset")
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) & " "
Response.write rsProdutos(1) & " "
Response.write FormatNumber(rsProdutos(2),2) & "<BR>"
rsProdutos.MoveNext
Wend
rsProdutos.Close
DbConn.Close
Set DbConn = Nothing
set rsProdutos = NOthing
%>

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.

 

<%
Option Explicit
Dim DbConn , rsCategorias
Dim sql
on error resume next 
'obtem os valores das variáveis informadas no formulário e remove os espaços
varCodigoCategoria = Trim(Request.Form("codigoCategoria")
varNomeCategoria = Trim(Request.Form("nomeCategoria")
varDescricaoCategoria = Trim(Request.Form("descricaoCategoria")
'Monta a instrução que chama a stored procedure passando os valores dos parâmetros
'valores não numéricos devem vir entre aspas(') , valores numéricos não.
sql "execute Incluir " & varCodigoCategoria & ",'" & varNomeCategoria & "','" & varDescricaoCategoria &"'"
Set DbConn = Server.CreateObject("ADODB.Connection")
Set rsCategorias = Server.CreateObject("ADODB.Recordset")
DbConn.Open "Provider=SQLOLEDB;Data Source=teste;Initial Catalog=TesteWind;Integrated Security=SSPI"
set rsCategorias = DbConn.Execute(sql)
rsCategorias.Close
DbConn.Close
Set DbConn = Nothing
set rsCategorias = NOthing
%>

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.

 

<%
Option Explicit
Dim DbConn , rsCategorias
Dim sql
on error resume next 
'obtem os valores das variáveis informadas no formulário e remove os espaços
varCodigoCategoria = Trim(Request.Form("codigoCategoria")
varDescricaoCategoria = Trim(Request.Form("descricaoCategoria")
'Monta a instrução que chama a stored procedure passando os valores dos parâmetros
'valores não numéricos devem vir entre aspas(') , valores numéricos não.
sql "execute Atualizar " & varCodigoCategoria & ",'"  & varDescricaoCategoria &"'"
Set DbConn = Server.CreateObject("ADODB.Connection")
Set rsCategorias = Server.CreateObject("ADODB.Recordset")
DbConn.Open "Provider=SQLOLEDB;Data Source=teste;Initial Catalog=TesteWind;Integrated Security=SSPI"
set rsCategorias = DbConn.Execute(sql)
rsCategorias.Close
DbConn.Close
Set DbConn = Nothing
set rsCategorias = NOthing
%>

 

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 é :

 

<%
Option Explicit
Dim DbConn , rsCategorias
Dim sql
on error resume next 
'obtem os valores das variáveis informadas no formulário e remove os espaços 

varCodigoCategoria = Trim(Request.Form("codigoCategoria")
'Monta a instrução que chama a stored procedure passando os valores dos parâmetros
'valores não numéricos devem vir entre aspas(') , valores numéricos não.
sql "execute Excluir " & varCodigoCategoria

Set DbConn = Server.CreateObject("ADODB.Connection")
Set rsCategorias = Server.CreateObject("ADODB.Recordset")
DbConn.Open "Provider=SQLOLEDB;Data Source=TESTE;Initial Catalog=TesteWind;Integrated Security=SSPI"
set rsCategorias = DbConn.Execute(sql)
rsCategorias.Close
DbConn.Close
Set DbConn = Nothing
set rsCategorias = NOthing
%>