Descripcion de Procedimientos Almacenados
¿Qué es un procedimiento almacenado?
¿Por qué utilizar procedimientos almacenados?
Práctica: seleccionar el procedimiento almacenado correcto
*****************************
¿Qué es un procedimiento almacenado?
Acceso programático a una base de datos
Los procedimientos almacenados permiten el acceso a una base de datos mediante la invocación de un procedimiento existente en lugar de tener que escribir nuestras propias instrucciones SQL. Los procedimientos almacenados están formados por secuencias de instrucciones Transact-SQL, y funcionan de modo similar a los procedimientos de una aplicación Web en cuanto a que las instrucciones se invocan por su nombre, y pueden tener tanto parámetros de entrada como de salida.
Los tres tipos de procedimientos almacenados son:
A) Procedimientos almacenados que devuelven registros
Los procedimientos almacenados que devuelven registros se utilizan para encontrar registros específicos, clasificar y filtrar esos registros, y devolver el resultado de las operaciones de búsqueda, clasificación y filtrado a un objeto DataSet o un control enlazado a lista. Estos procedimientos almacenados se basan en instrucciones Select de SQL.
Un ejemplo de un procedimiento almacenado que devuelve registros es una petición de la cantidad, fecha y receptor de los tres últimos movimientos procesados en una cuenta bancaria. Estos datos podrían cargarse en un objeto DataSet para su posterior procesamiento, o mostrarse directamente al usuario en un control ListBox.
B) Procedimientos almacenados que retornan un valor, también denominados procedimientos almacenados escalares
Los procedimientos almacenados que retornan un valor se utilizan para ejecutar un comando o función de la base de datos que devuelve un único valor. Debido a que sólo se devuelve un valor, los procedimientos almacenados que retornan un valor a menudo se utilizan en el código y, a continuación, el resultado se muestra a los usuarios.
Un ejemplo de procedimiento almacenado que retorna un valor es devolver el valor total de los tres últimos movimientos que se han procesado en una cuenta bancaria.
C) Los procedimientos almacenados de acción
Los procedimientos almacenados de acción se utilizan para realizar algunas funciones en la base de datos, pero no devuelven un registro o un valor.
Estas funciones de la base de datos pueden incluir actualizar, editar o modificar los datos.
Un ejemplo de un procedimiento almacenado de acción es una petición para actualizar una sola dirección de correo en la base de datos de clientes de una compañía.
¿Por qué utilizar procedimientos almacenados?
· Programación modular
· Distribución del trabajo
· Seguridad de la base de datos
· Ejecución más rápida
· Reduce el tráfico de red
· Proporciona flexibilidad
Programación modular
Los procedimientos almacenados son ejemplos clásicos de programación modular. Creamos el procedimiento una vez, lo probamos una vez, lo almacenamos en el servidor de la base de datos, y lo invocamos varias veces desde múltiples aplicaciones. Las actualizaciones o cambios a la base de datos permanecen ocultas para todas las aplicaciones que acceden mediante el procedimiento almacenado.
Distribución del trabajo
Un desarrollador especialista en la programación de bases de datos puede crear los procedimientos almacenados, mientras que otros desarrolladores pueden crear paralelamente las aplicaciones Web que utilizarán el procedimiento almacenado. Esta distribución del trabajo permite que cada desarrollador se concentre en su propia especialidad, y cumpla con sus propios plazos.
Incremento de la seguridad de la base de datos
El uso de procedimientos almacenados proporciona una mayor seguridad para una base de datos al limitar el acceso directo. Únicamente acceden directamente a la base de datos los procedimientos almacenados probados que han sido desarrollados por el propietario de la base de datos. Debido a que las demás aplicaciones Web y otros desarrolladores no acceden directamente a la base de datos, el riesgo de daño accidental de la estructura o el contenido de la base de datos son mínimos.
Utilizar instrucciones SQL o Transact-SQL directamente en el código Microsoft ASP.NET también supone un riesgo para la seguridad ya que las instrucciones pueden dar información a un hacker sobre la base de datos y su estructura. Además, con el acceso directo a una base de datos, también tenemos el problema de seguridad al intentar determinar qué clase de permisos deberíamos dar a la cuenta de usuario Web sobre las tablas individuales.
Ejecución más rápida
Si un procedimiento requiere una gran cantidad de código Transact-SQL o si se invoca repetidamente, el uso de procedimientos almacenados puede dar lugar a una mayor rapidez de ejecución que acceder directamente a la base de datos con código Transact-SQL. Los procedimientos almacenados son parseados y optimizados cuando se crean, y puede utilizarse una versión en memoria del procedimiento tras su primera ejecución.
El acceso directo mediante instrucciones Transact-SQL requiere que las instrucciones sean enviadas desde el cliente cada vez que se ejecutan. Las instrucciones son compiladas y optimizadas cada vez que son ejecutadas por el servidor de la base de datos.
Reducción del tráfico de red
En ocasiones, una operación que requiere cientos de líneas de código Transact- SQL puede realizarse mediante una única instrucción que invoque un procedimiento almacenado. Enviar una llamada a través de la red, en lugar de cientos de líneas de código, reduce el tráfico de red.
Proporciona flexibilidad
Si el acceso a la base de datos se realiza a través de procedimientos almacenados, el desarrollador de la base de datos puede cambiar la estructura
de la base de datos sin romper las aplicaciones Web que los utilizan. Esta protección permite la continua mejora de la base de datos sin poner en riesgo el resto del sistema.
Invocar procedimientos almacenados
· Invocar procedimientos almacenados
· Demostración: invocar un procedimiento almacenado
· Práctica: mostrar datos de un procedimiento almacenado
· Utilizar parámetros
· Pasar parámetros de entrada
· Utilizar parámetros de salida
· Demostración: pasar parámetros
A) Identificar el procedimiento almacenado
B) Configurar la propiedad SelectCommand del DataAdapter
C) Ejecutar el procedimiento almacenado y almacenar los registros devueltos
Para invocar un procedimiento almacenado, en primer lugar debemos identificarlo, crear un objeto DataAdapter, y configurar el objeto DataAdapter para que apunte a la conexión de la base de datos. Seguidamente, establecer la propiedad CommandText al nombre del procedimiento almacenado identificado y, finalmente, establecer la propiedad CommandType en CommandType.StoredProcedure.
Identificar el procedimiento almacenado
El primer paso para utilizar un procedimiento almacenado es identificar el tipo y el nombre del mismo. Podemos utilizar un objeto DataAdapter o un objeto DataReader para invocar los tres tipos de procedimientos almacenados. El método que invoquemos para ejecutar el procedimiento almacenado variará dependiendo del tipo de procedimiento almacenado que invoquemos:
A) Procedimientos almacenados que devuelven registros
Cuando invocamos un procedimiento que devuelve un conjunto de registros, necesitamos almacenar ese conjunto de registros en un DataSet, o directamente en un control enlazado a lista utilizando un DataReader. Si deseamos utilizar un DataSet, debemos utilizar un DataAdapter y el método Fill. Si deseamos utilizar un DataReader, debemos utilizar un objeto Command y el método ExecuteReader, y vincular el registro devuelto al control enlazado a lista.
B) Procedimientos almacenados que retornan un valor
Cuando invocamos un procedimiento almacenado que devuelve un valor, invocamos el método ExecuteScalar del objeto Command, y guardamos el resultado en una variable del tipo de datos apropiado.
C) Procedimientos almacenados que realizan una acción
Cuando invocamos un procedimiento almacenado que realiza alguna acción en la base de datos pero no devuelve un conjunto de registros o un valor, utilizamos el método ExecuteNonQuery del objeto Command.
Establecer la propiedad SelectCommand
Para configurar la llamada al procedimiento almacenado, debemos crear un objeto SqlCommand y guardarlo como la propiedad SelectCommand de un DataAdapter. A continuación, debemos establecer las propiedades Connection, CommandText y CommandType.
Podemos configurar visualmente la llamada a un procedimiento almacenado utilizando las herramientas para procedimientos almacenados del Cuadro de herramientas de Microsoft Visual Studio® .NET, o podemos escribir manualmente el código para invocar el procedimiento almacenado. Los siguientes ejemplos utilizan código manual para mostrar tanto el código completo como el proceso lógico de invocación de un procedimiento almacenado.
Los siguientes ejemplos invocan el procedimiento almacenado ProductCategoryList. El procedimiento almacenado ProductCategoryList devuelve una lista de IDs y nombres de categorías de la tabla Categories:
Procedure ProductCategoryList
As
SELECT CategoryID,CategoryName
FROM Categories
El siguiente código utiliza un objeto Connection y un objeto DataAdapter para invocar el procedimiento almacenado que devuelve registros
ProductCategoryList:
Visual Basic .NET
Dim daCategory as New SqlDataAdapter ()
daCategory.SelectCommand = New SqlCommand ()
daCategory.SelectCommand.Connection = conn
daCategory.SelectCommand.CommandText = _
"ProductCategoryList"
daCategory.SelectCommand.CommandType = _
CommandType.StoredProcedure
Dim daCategory As New SqlDataAdapter _
("ProductCategoryList", conn)
daCategory.SelectCommand.CommandType = _
CommandType.StoredProcedure
Ejecutar el procedimiento almacenado
Para ejecutar el procedimiento almacenado y guardar los registros devueltos en un DataSet, invocar el método Fill del objeto SqlDataAdapter. Este método rellena un objeto DataTable con los registros devueltos del procedimiento almacenado.
Por ejemplo, el siguiente código rellena el objeto DataSet ds con los registros devueltos desde el procedimiento almacenado ProductCategoryList utilizando el daCategory SqlDataAdapter:
Visual Basic .NET
daCategory.Fill (ds, "Categories")
Tras rellenar un DataTable con los resultados de un procedimiento almacenado Select, podemos vincular el DataTable a un control enlazado a lista para mostrar los datos.
Invocar un procedimiento almacenado
En esta demostración, veremos cómo invocar un procedimiento almacenado de una base de datos Microsoft SQL Server™, y vincular los resultados a un DataGrid.
A) Ejecutar la demostración
1. Abrir la página SPGetRecords.aspx del proyecto Demo09VB o Demo09CS que se pueden encontrar dentro del fichero demos9.zip.
2. Generar y examinar la página.
El DataGrid está vinculado al procedimiento almacenado Ten Most Expensive Products de la base de datos Northwind Traders.
3. En Visual Studio .NET, visualizar la página de código subyacente de la página SPGetRecords.aspx.
4. En el procedimiento de evento Page_Load, mostrar el código que realiza lo siguiente:
• Crea el objeto SqlConnection.
• Crea los objetos SqlDataAdapter y SqlCommand.
• Establece las propiedades del objeto SqlCommand para invocar el procedimiento almacenado.
• Crea un nuevo DataSet y lo rellena desde el DataAdapter.
Mostrar datos de un procedimiento almacenado
A) Ejecutar la práctica
1. Crear un nuevo proyecto de aplicación Web y establecer su ubicación en
http://localhost/Pract09VB
http://localhost/Pract09CS
2. Abrir la página WebForm1.aspx.
3. Abrir el Explorador de servidores y expandir las siguientes carpetas: nombre_máquina, Servidores SQL Server, nombre_máquina, Northwind y Procedimientos almacenados.
4. Hacer clic en el procedimiento almacenado Ten Most Expensive Products y arrastrarlo a la página WebForm1.aspx. Visual Studio .NET crea automáticamente un objeto SqlConnection denominado sqlConnection1 y un objeto SqlCommand denominado sqlCommand1.
5. Utilizando el Cuadro de herramientas, ubicar un control DataGrid en la página WebForm1.aspx.
6. Crear un procedimiento de evento Page_Load y agregar el siguiente código para crear un DataReader desde el objeto SqlCommand y vincularlo al DataGrid:
Dim dr As SqlClient.SqlDataReader
SqlConnection1.Open ()
dr = SqlCommand1.ExecuteReader ()
DataGrid1.DataSource = dr
DataGrid1.DataBind ()
dr.Close()
SqlConnection1.Close ()
7. Si se está utilizando C# para generar este proyecto, debemos introducir el siguiente código al inicio de la página de código subyacente. Using System.Data.SqlClient;
8. Generar y examinar la página WebForm1.aspx.
Utilizar parámetros
· Identificar los parámetros disponibles
· Input
· Output
· InputOutput
· ReturnValue
· Incluir parámetros en la colección de parámetros o
· Incluir valores de parámetros en la cadena de comando
Cuando utilizamos parámetros con una base de datos SQL Server, los nombres de los parámetros que se agregan a la colección Parameters del objeto Command deben coincidir con los nombres de los parámetros del procedimiento almacenado; no obstante, el orden de los parámetros es flexible.
La siguiente tabla describe los tipos de parámetros disponibles en los procedimientos almacenados.
Pasar parámetros de entrada
· Crear el parámetro, establecer la dirección y el valor, agregar a la colección de parámetros
· Ejecutar el procedimiento almacenado y almacenar los registros devueltos
Crear un parámetro
Para crear un parámetro, crear un nuevo objeto SqlParameter con el nombre y tipo de datos del parámetro, según lo especificado por el procedimiento
almacenado. A continuación, establecer la propiedad Direction del nuevo parámetro para indicar la forma en que el procedimiento almacenado utilizará el parámetro. Si el procedimiento almacenado devuelve un valor de retorno, crear un parámetro denominado returnValue. Si el parámetro es de entrada, establecer la propiedad Value para especificar los datos que deberían enviarse al SQL Server.
Por ejemplo, el procedimiento almacenado ProductsByCategory toma un parámetro de entrada, denominado @CategoryID de tipo int, como muestra el
siguiente código:
Procedure ProductsByCategory (
@CategoryID int )
As
SELECT ProductID, ModelName, UnitCost, ProductImage,
Chairman
FROM Products
WHERE CategoryID=@CategoryID
Para invocar el procedimiento almacenado ProductsByCategory, crear un parámetro de entrada denominado @CategoryID y establecer su valor con el
valor de un cuadro de texto:
Dim workParam1 As New SqlParameter _
("@CategoryID", SqlDbType.Int)
workParam1.Direction = ParameterDirection.Input
workParam1.Value = Cint (txtStartDate.Text)
Una vez creado el objeto Parameter, utilizar el método Add de la colección Parameters del objeto SelectCommand. Si un procedimiento almacenado
tiene más de un parámetro, no importa en qué orden los agreguemos puesto que los creamos por nombre:
Dim daSales as New SqlDataAdapter ()
daSales.SelectCommand = New SqlCommand ()
daSales.SelectCommand.Connection = conn
daSales.SelectCommand.CommandText = "ProductsByCategory"
daSales.SelectCommand.CommandType = _
CommandType.StoredProcedure
daSales.SelectCommand.Parameters.Add (workParam1)
Ejecutar un procedimiento almacenado
Una vez creado el objeto Command, utilizamos el método Fill para ejecutar el procedimiento almacenado y recuperar los registros:
ds = New DataSet ()
daSales. Fill (ds, "Products")
Utilizar parámetros de salida
Ejemplo de uso de parámetros de salida
El procedimiento almacenado OrdersCount toma un ID de cliente y devuelve el número de pedidos pendientes que éste tiene, pero no los pedidos actuales. El procedimiento almacenado utiliza los parámetros de entrada @CustomerID, y el parámetro de salida @ItemCount, ambos del tipo int, como muestra el siguiente procedimiento almacenado:
Procedure OrdersCount (
@CustomerID int,
@ItemCount int OUTPUT)
As
SELECT @ItemCount=COUNT (OrderID)
FROM Orders
WHERE CustomerID=@CustomerID
Como el procedimiento almacenado anterior devuelve el número de filas y no los datos de dichas filas, no necesitamos utilizar un objeto DataAdapter. En
lugar de ello, podemos utilizar un objeto Command directamente, e invocar el método ExecuteNonQuery para ejecutar el procedimiento almacenado.
Invocar un procedimiento almacenado que devuelve un valor
Para invocar el procedimiento almacenado OrdersCount, necesitamos crear un parámetro de entrada denominado @CustomerID y un parámetro de salida denominado @ItemCount y añadirlos a la colección Parameters de un objeto Command, y a continuación invocar a ExecuteNonQuery para ejecutar el procedimiento almacenado:
Dim myCmd As SqlCommand = New SqlCommand ("Orders Count", conn)
myCmd.CommandType = CommandType.StoredProcedure
'add an input parameter
Dim workParam as SqlParameter
workParam = New SqlParameter ("@CustomerID", SqlDbType.Int)
workParam.Direction = ParameterDirection.Input
workParam.Value = CInt (txtCustID.Text)
myCmd.Parameters.Add (workParam)
'add an output parameter
workParam = New SqlParameter ("@ItemCount", SqlDbType.Int)
workParam.Direction = ParameterDirection.Output
myCmd.Parameters.Add (workParam)
Ejecutar el procedimiento almacenado
El siguiente código ejecuta el procedimiento almacenado MyCmd:
conn.Open ()
myCmd.ExecuteNonQuery ()
conn.Close ()
Leer parámetros de salida
Si estamos recuperando un valor de un procedimiento almacenado que devuelve valores o establece un parámetro de salida, necesitamos utilizar el método Value del parámetro devuelto en la colección Parameters. Podemos referenciar el valor del parámetro de salida por el nombre o por el índice. El siguiente ejemplo de código recupera el valor del parámetro de salida
@ItemCount por el nombre:
curSales = myCmd.Parameters("@ItemCount").Value
Pasar parámetros
En esta demostración, veremos cómo invocar un procedimiento almacenado con dos parámetros de entrada y a continuación enlazar los resultados a un DataGrid.
A) Para ejecutar la demostración
1. Abrir la página SPUseParameters.aspx del proyecto Demo09VB o Demo09CS que se pueden encontrar dentro del fichero demos9.zip.
2. Generar y examinar.
Introducir las fechas de inicio y final en Beginning Date y Ending Date y a continuación hacer clic en Get Sales by Year. Se enlaza un DataGrid al procedimiento almacenado Sales by Year en la base de datos Northwind Traders.
3. En Visual Studio .NET, visualizar el código subyacente de la página SPUseParameters.aspx.
4. En el procedimiento de evento cmdSale_Click, mostrar el código que realiza lo siguiente:
• Crea la SqlConnection.
• Crea el SqlDataAdapter y establece las propiedades del SelectCommand para invocar el procedimiento almacenado Sales by Year.
• Crear dos parámetros de entrada.
• Crear un nuevo DataSet y rellenar el DataSet desde el DataAdapter.
