Transcript
Trucos de Sql Server Crear un Stored Procedure
Para crear un Stored podemos utilizar el Enterprise Manager o el Query Analizer. Si lo hacemos por el Enterprise Manager, encima de la base de datos, desplegaremos la carpeta de storeds, botón derecho y "New Stored Procedure" El Enterprise Manager por defecto pone: CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS En Procedure Name colocamos el nombre del Stored que deseamos, y a continuación del AS las consultas que queramos: CREATE PROCEDURE DIC_FORO AS SET NOCOUNT ON SELECT Campos FROM Tabla With(NoLock) SET NOCOUNT OFF GO Los Storeds admiten parámetros. Sirven para poder realizar querys más precisas. Por ejemplo: CREATE PROCEDURE DIC_FORO ( @IdUsuario int=0 ) AS SET NOCOUNT ON SELECT *
FROM Usuario With(NoLock) WHERE Usuario=@IdUsuario
SET NOCOUNT OFF GO
También podemos utilizar parámetros de salida, para retornar datos. Para ello declararemos el parámetro como Output.
A.Utilizar BREAK y CONTINUE con IF…ELSE y WHILE anidados En el ejemplo siguiente, si el precio de venta promedio de un producto es inferior a $300, el bucle WHILE dobla los precios y, a continuación, selecciona el precio máximo. Si el precio máximo es menor o igual que $500, el bucle WHILE se reinicia y vuelve a doblar los precios. Este bucle continúa doblando los precios hasta que el precio máximo es mayor que $500, después de lo cual sale del bucle WHILE e imprime un mensaje. Transact-SQL USE AdventureWorks2012; GO WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300 BEGIN UPDATE Production.Product SET ListPrice = ListPrice * 2 SELECT MAX(ListPrice) FROM Production.Product IF (SELECT MAX(ListPrice) FROM Production.Product) > $500 BREAK ELSE CONTINUE END PRINT 'Too much for the market to bear'; DECLARE @CONTADOR INT SET @CONTADOR = 1 WHILE @CONTADOR < 1000 BEGIN INSERT INTO TABLE VALUES('XXX') CONTINUE END
CONSULTA: IF NOT EXISTS(Select nombre from persona wnere nombre = @nombre) Begin Insert into persona (nombre) values (@nombre) End
[SQL] ¿Por qué utilizar Tablas Temporales Locales, Globales o Variables de Tabla?.
12062009 10 Votes
Saludos amigos en esta ocasión leyendo me encontré con un tema muy interesante acerca de las tablas temporales en SQL, les dejo lo que traduje del tema con una explicación y ejemplo de como crear tablas temporales locales, globales y variables de tabla, dentro del Motor de Base de Datos de SQL Server 2008.
Tablas Temporales. Las tablas temporales son consideradas tablas automáticamente en la base de datos de tempdb.
regulares,
y
estas
se
almacenan
Las tablas temporales se pueden usar en los siguientes escenarios: Como alternativa a los cursores: por ejemplo, en lugar de utilizar un cursor de Transact-SQL para recorrer un conjunto de resultados y realizar tareas basadas en cada fila, en su lugar puede utilizar una tabla temporal. Usando un bucle WHILE, puede recorrer cada fila de la tabla y realizar la acción de la fila especificada, posteriormente debe eliminar la fila de la tabla temporal. Como almacenamiento incremental de conjuntos de resultados: Por ejemplo, imaginemos que tiene una sola consulta SELECT que realiza una combinación “JOIN”con diez tablas. A veces las consultas con varias combinaciones “JOINS” pueden funcionar de manera incorrecta. Una técnica para intentar es la de fraccionar o fragmentar una consulta grande en consultas más pequeñas. Si usamos tablas temporales, podemos crear conjuntos de resultados intermedios basados en consultas de menor tamaño, en lugar de intentar ejecutar una consulta única que sea demasiado grande y de combinaciones múltiples “multi-joined”. Como temporal en la sobrecarga baja de búsqueda de tabla: Por ejemplo, imagine que usted está utilizando una consulta que tarda varios segundos en ejecutarse, pero sólo muestra un pequeño conjunto de resultados, el cual desea utilizar en varias áreas de su procedimiento almacenado, pero cada vez que se llama se incurre en el tiempo de ejecución de la consulta
general. Para resolver esto, puede ejecutar la consulta una sola vez en el procedimiento, llenando una tabla temporal, de esta manera se puede hacer referencia a la tabla temporal en varios lugares en su código, sin incurrir en una sobrecarga de resultados adicional. Existen dos tipos de tablas temporales: Globales y Locales. Las tablas temporales Locales se escriben temporales Globales con el doble símbolo ##.
anteponiendo
el
símbolo # y
tablas
Tablas temporales Locales. Las tablas temporales Locales están disponibles para usarse por cada conexión actual del usuario que los cree. Varias conexiones pueden crear una tabla temporal con mismo nombre, esto solo para para tablas temporales Locales sin causar conflictos. La representación interna de la tabla local tiene un nombre único, para no estar en conflicto con otras tablas temporales con el mismo nombre creado por otras conexiones en la tempdb. Las tablas temporales locales son eliminadas con el comando DROP o automáticamente de memoria cuando se cierra la conexión del usuario. Creando la tabla temporal Local: CREATE TABLE #ProductosResumen ( idProducto int NOT NULL PRIMARY KEY, nombre varchar(75) NULL, precio smallmoney NULL ); Insertando datos en la tabla temporal global. INSERT #ProductosResumen (idProducto, nombre , precio) SELECT id,nombre, precio FROM dbo.DEMO_PRODUCTO ORDER BY nombre; Seleccionando los datos de la tabla temporal. Seleccionando los datos de la tabla temporal SELECT * FROM #ProductosResumen;
Resumen de precios de la tabla temporal SELECT AVG(precio) FROM #ProductosResumen;
se
eliminan
Resultado.
Eliminando la tabla. Eliminando la tabla temporal DROP TABLE #ProductosResumen; Nota: Para que el ejemplo funcione deben de ejecutar en un solo bloque de instrucciones todo el código que coloque anteriormente. Para este caso yo llene mi tabla temporal con datos de una tabla que ya tenia creada en la base de datos, ustedes pueden utilizar un insert normal.
Tablas Temporales Globales. Las tablas temporales Globales tienen un alcance diferente al de las tablas temporalesLocales. Una vez que una conexión crea una tabla temporal Global, cualquier usuario con permisos adecuados sobre la base de datos puede acceder a la tabla. A diferencia de tablas temporales Locales, no se pueden crear versiones simultáneas de una tabla temporal Global, ya que esto generará un conflicto de nombres. Las tablas temporales Globales de eliminan explícitamente de SQL Server ejecutandoDROP TABLE. También se eliminan automáticamente después de que se cierra la conexión que la creo, la tabla temporal Global no es referenciada por otras conexiones, pero es muy raro ver que se utilicen tablas temporales Globales en bases de datos en producción. Es importante considerar cuando una tabla va o debe ser compartida a través de conexiones, se debe crear una tabla real, en lugar de una tabla temporal Global. No obstante, SQL Server ofrece esto como una opción. Creando la tabla temporal Global: Creando la tabla temporal Global CREATE TABLE ##Roles ( idRol int NOT NULL PRIMARY KEY, nombre varchar(30) NULL, activo bit NULL );
Insertando y seleccionando datos de la tabla temporal global: Insertando datos en la tabla temporal global INSERT INTO ##Roles VALUES(1,'Administrador',1), (2,'Supervisor',1), (3,'Programador',0)
Seleccionando los datos de la tabla temporal global SELECT * FROM ##Roles; Resultado:
Eliminando la tabla temporal global desde la conexión original que la creo. Eliminando la tabla temporal global DROP TABLE ##Roles;
Variables de Tabla. Por otro lado tenemos las Variables de Tabla que son un tipo de datos que puede ser utilizados en un lote Transact-SQL (Batch), procedimiento almacenado o función; estas variables de tabla son creado y definidas de forma similar a una tabla, sólo con un alcance de vida estrictamente definido. Las Variables de tabla suelen ser buenos reemplazos de tablas temporales siempre y cuando el conjunto de datos es pequeño. Razones para usar las variables de tabla:
•
Duración o alcance. La duración de la variable de tabla sólo vive durante la ejecución del lote, función, o procedimiento almacenado.
•
Tiempos de bloqueo más cortos. Por el estrecho alcance o tiempo de vida.
•
Menos re compilaciones cuando se usa en los procedimientos almacenados. Como se menciono anteriormente, hay inconvenientes para utilizar las variables de tabla. El rendimiento de las variable de tabla se ve afectado cuando el resultado es demasiado grande o cuando los datos de la columna de cardinalidad son fundamentales para la optimización del proceso de consulta.
La sintaxis para crear una variable de tabla es similar a la de crear una tabla normal, se utiliza la palabra clave DECLARE y el nombre de tabla, anteponiendo el símbolo @: DECLARE
@TableName
TABLE
(column_name [ NULL | NOT NULL ] [ ,...n ] ) Creando una variable de tabla: Creando la variable de tipo tabla. DECLARE @EstatusUsuarios TABLE ( idEstatus int NOT NULL PRIMARY KEY, nombre varchar(30) NULL ) Insertando y seleccionando datos de la variable tabla: Insertando en la variable de tipo tabla. INSERT INTO @EstatusUsuarios VALUES (1,'Activo'), (2,'Inactivo'), (3,'Bloqueado')
Consultando datos de la variable de tipo tabla. SELECT * FROM @EstatusUsuarios; Al terminar la ejecución del batch o bloque de instrucciones se eliminara la variable tabla, o si colocamos una instrucción GO automáticamente se eliminara y no la podremos utilizar como los muestro en el siguiente ejempló, recuerde que los ejemplo para claridad los coloque separados, pero los debe de ejecutar completos como lo hago abajo para el caso de la variables de tabla: Creando la variable de tipo tabla. DECLARE @EstatusUsuarios TABLE ( idEstatus int NOT NULL PRIMARY KEY, nombre varchar(30) NULL )
Insertando en la variable de tipo tabla.
INSERT INTO @EstatusUsuarios VALUES (1,'Activo'), (2,'Inactivo'), (3,'Bloqueado')
Consultando datos de la variable de tipo tabla. SELECT * FROM @EstatusUsuarios;
Al ejecutarse el go o el bloque de instrucciones la variable tabla se eliminara auitomaticamente. Go
SELECT * FROM @EstatusUsuarios; Como podrán notar en el primer select nos regreso:
Y para el segundo select:
Esto es por que como lo comente anteriormente el tiempo de vida de la variable tabla termina al ejecutarse el bloque de instrucciones, en este caso termino con el GO. Cuando encontremos problemas de rendimiento, debemos de asegurarnos de probar todas las soluciones y alternativas, y no necesariamente asumir que una de las opciones (tablas Temporales) son menos deseables que otras (variables de tabla). Espero les sea de ayuda, se que esta un poco largo el Post, pero creo vale la pena leerlo.
Apóyame votando aquí: http://blogit.ms/TopBloggers.aspx en la categoría SQL Server. Los pasos para votar están aquí te llevara 3 min:
Clic Aquí.
Etiquetas de Technorati: Tablas temporales,temporary tables,Local Tables,Global Tables,Variables de Tabla,SQL 2008
Utilizar Sentencias SQL Esta página muestra un procedimiento almacenado muy sencillo que no tiene parámetros. Aunque la mayoría de los procedimientos almacenados hacen cosas más complejas que este ejemplo, sirve para ilustrar algunos puntos básicos sobre ellos. Como paso prévio, la sintaxis para definir un procedimiento almacenado es diferente de un controlador de base de datos a otro. Por ejemplo, algunos utilizan begin . . . end u otras palabras clave para indicar el principio y final de la definición de procedimiento. En algunos controladores, la siguiente sentencia SQL crea un procedimiento almacenado.
create procedure SHOW_SUPPLIERS as select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME from SUPPLIERS, COFFEES where SUPPLIERS.SUP_ID = COFFEES.SUP_ID order by SUP_NAME El siguiente código pone la sentencia SQL dentro de variablecreateProcedure, que utilizaremos más adelante.
un
string
y
lo asigna
a
la
String createProcedure = "create procedure SHOW_SUPPLIERS " + "as " + "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " + "from SUPPLIERS, COFFEES " + "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "order by SUP_NAME";
El siguiente fragmento de código utiliza el objeto Connection, con para crear un objetoStatement, que es utilizado para enviar la sentencia SQL que crea el procedimiento almacenado en la base de datos.
Statement stmt = con.createStatement(); stmt.executeUpdate(createProcedure); El procedimiento SHOW_SUPPLIERS será compilado y almacenado en la base de datos como un objeto de la propia base y puede ser llamado, como se llamaría a cualquier otro método.
Llamar a un Procedimiento Almacenado desde JDBC
JDBC permite llamar a un procedimiento almacenado en la base de datos desde una aplicación escrita en Java. El primer paso es crear un objeto CallableStatement. Al igual que con los objetos Statement y PreparedStatement, esto se hace con una conexión abierta,Connection. Un objeto CallableStatement contiene una llamada a un procedimiento almacenado; no contiene el propio procedimiento. La primera línea del código siguiente crea una llamada al procedimiento almacenado SHOW_SUPPLIERS utilizando la conexión con. La parte que está encerrada entre corchetes es la sintaxis de escape para los precedimientos almacenados. Cuando un controlador encuentra "{call SHOW_SUPPLIERS}", traducirá esta sintaxis de escape al SQL nativo utilizado en la base de datos para llamar al procedimiento almacenado llamado SHOW_SUPPLIERS.
CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}"); ResultSet rs = cs.executeQuery(); La hoja de resultados de rs será similar a esto.
SUP_NAME Acme, Inc. Acme, Inc. Superior Coffee Superior Coffee The High Ground
COF_NAME Colombian Colombian_Decaf French_Roast French_Roast_Decaf Espresso
Observa que el método utilizado para ejecutar cs es executeQuery porque cs llama a un procedimiento almacenado que contiene una petición y esto produce una hoja de resultados. Si el procedimiento hubiera contenido una sentencia de actualziación o una sentencia DDL, se hubiera utilizado el método executeUpdate. Sin embargo, en algunos casos, cuando el procedimiento almacenado contiene más de una sentencia SQL producirá más de una hoja de resultados, o cuando contiene más de una cuenta de actualizaciónm o alguna combinación de hojas de resultados y actualizaciones. en estos casos, donde existen múltiples resultados, se debería utilizar el método execute para ejecutar CallableStatement. La clase CallableStatement es una subclase de PreparedStatement, por eso un objetoCallableStatement puede tomar parámetros de entrada como lo haría un objetoPreparedStatement. Además, un objeto CallableStatement puede tomar parámetros de salida, o parámetros que son tanto de entrada como de salida. Los parámetros INOUT y el método execute se utilizan raramente.
Ejecutar procedimiento almacenado desde Java (JDBC) En Java invocar a un procedimiento almacenado que no devuelve valores es bastante sencillo hacerlo desde JDBC, funciona de la misma manera que con cualquier base de datos, solo es cuestión de cambiar la cadena de conexión y el driver, el resto va igual. En el ejemplo veremos con MySQL, pero como vengo diciendo cambien los 2 factores y podrán usarlo con cualquier otro motor de base de datos. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
package conexionDB; import java.sql.Connection; public class ConexionMySQL { public static Connection con_mysql; public static Connection conectar(String pHost, String pUser, String pPassword, String Exception { try { String databaseURL = "jdbc:mysql://" + pHost + "/" + pDataBase; Class.forName("com.mysql.jdbc.Driver"); con_mysql = java.sql.DriverManager.getConnection(databaseURL, pUser, //System.out.println("Conexion con MySQL Establecida.."); } catch (Exception e) { e.printStackTrace(); throw new Exception(e); } return con_mysql; } }
En la siguiente clase veremos como implementar la clase anterior para hacer la conexion, además de invocar el procedimiento almacenado. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
// imports necesarios import conexionDB.ConexionMySQL; import java.sql.CallableStatement; . . Connection connMY = null; try{ // creamos la conexion connMY = ConexionMySQL.conectar("192.168.1.100", "sa", "*******", "devtroce"); // establecemos que no sea autocommit, // asi controlamos la transaccion de manera manual connMY.setAutoCommit(false); /* instanciamos el objeto callable statement * que usaremos para invocar el SP * La cantidad de "?" determina la cantidad * parametros que recibe el procedimiento */ CallableStatement prcProcedimientoAlmacenado = connMY.prepareCall("{ call NuestroProcedimientoAlmacenado(?,?,?) }"); // cargar parametros al SP prcProcedimientoAlmacenado.setInt("pParametro1", 1);
22 prcProcedimientoAlmacenado.setString("pParametro2", "Devtroce.com"); 23 prcProcedimientoAlmacenado.setInt("pParametro3", 49); 24 // ejecutar el SP 25 prcProcedimientoAlmacenado.execute(); 26 // confirmar si se ejecuto sin errores 27 connMY.commit(); 28 } catch (Exception e) { 29 // deshacer la ejecucion en caso de error 30 connMY.rollback(); 31 // informar por consola 32 e.printStackTrace(); 33 } finally { 34 // cerrar la conexion 35 connMY.close(); }
A pedido de Moise, edito el post y agrego la estructura de la tabla y un Stored Procedure que cumple para ejecutar con el código aquí: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
CREATE TABLE devtroce( id int NOT NULL, campo2 varchar(50) NOT NULL, campo3 int NOT NULL ) ON PRIMARY CREATE PROCEDURE NuestroProcedimientoAlmacenado @id int, @campo2 varchar(50), @campo3 int AS BEGIN INSERT INTO devtroce VALUES ( @id, @campo2, @campo3); END
•
Publicarlo en Twitter 1 1 1 1 1 1 1 1 1
•
Publicarlo en Delicious
•
Añadirlo a LinkedIn
•
Publicarlo en Facebook
