sábado, 29 de julio de 2017

Procedimientos Almacenados SQL

on julio 29, 2017 , , , , No hay comentarios.


El procedimiento almacenado (stored procedure) es un conjunto de comando SQL que pueden almacenarse en el servidor el cual ejecuta una acción o un conjunto de acciones.

En los procedimientos almacenados pueden contener instrucciones de lenguaje COF, como pueden contener muchas instrucciones distintas, puede haber instrucciones SELECT, INSERT, UPDATE, DELETE contenidas dentro de un procedimiento almacenado.

Sintaxis de creación, modificación y eliminado de los procedimientos almacenados.

Crear:
CREATE PROCEDURE <nombre>
(<Parametro><TipodeDato>,…n) 
AS
<Instruccion, operacion, etc.>
      
Modificar:
ALTER PROCEDURE <nombre>
(<Parametro><TipodeDato>,…n) 
AS
<Instruccion, operacion, etc.>

Borrar:
DROPPROCEDURE <nombre>

Ejemplos Prácticos.

Query para el siguiente ejemplo, tabla Personas.

CREATE TABLE Personas(
       PersId int PRIMARY KEY IDENTITY(1,1) NOT NULL,
       PersNombre nvarchar (80) NOT NULL,
       PersFecNacimiento datetime NULL,
       PersActivo bit NOT NULL
       )


Procedimiento almacenado para guardar o actualizar una Persona.

CREATE PROCEDUREagregarPersona
@Id INT,
@Nombre NVARCHAR(50),
@FecNac DATETIME,
@Activo bit
AS
IF NOT EXISTS (SELECT * FROMPersonas WHERE PersId = @Id)
BEGIN
       INSERT INTOPersonas
       VALUES (@Nombre,@FecNac,@Activo)
END
ELSE
BEGIN
       UPDATEPersonas SET
       PersNombre = @Nombre,
       PersFecNacimiento = @FecNac,
       PersActivo = @Activo
       WHEREPersId = @Id
END

Como se puede apreciar en el ejemplo anterior, el procedimiento almacenado, revisa si el Idexiste en la tabla Personas, en caso de no existir, ingresa una nueva Persona en la tabla y en caso de existir una persona con el Id, procede a actualizar los datos de la tabla.

CREATE PROCEDUREeliminarPersona
@IdPersona INT
AS
IF EXISTS (SELECT * FROMTelefonos WHERE PersId = @IdPersona)
BEGIN
       DELETE FROMTelefonos WHERE PersId = @IdPersona
END

DELETE FROM Personas WHERE PersId = @IdPersona

En el ejemplo anterior, podemos ver que al eliminar una Persona y este tiene una relación en otra tabla, lo primero que se realiza es eliminar los datos de la tabla relacionada para terminar de eliminar la Persona y este Id no esté relacionado en tablas secundarias.

Ejecutar un procedimiento almacenado dentro de SQL.

Los procedimientos almacenados al ser ejecutados pueden regresar un valor único, un conjunto de resultados o no regresan ningún valor y solo se encargan de procesar sus instrucciones.

Para la ejecutar un procedimiento almacenado debemos utilizar la sentencia EXEC.

Ejemplo sin regresar valor:
EXECagregarPersona @Id =1, @Nombre = 'Thom',@FecNac = '1997-06-16'@Activo = 1

EXECeliminarPersona @IdPersona =1

Ejemplo con retorno de resultados:

CREATE PROCEDURE ObtenerPersonas
AS
SELECT P.PersNombre,
       P.PersFecNacimiento,
       ISNULL(T.TeleNumero, 'S/T') Telefono
FROMPersonas P
LEFT JOIN Telefonos T ON P.PersId = T.PersId

EXEC ObtenerPersonas

Resultado:



Estos procedimientos almacenados son utilizados cuando se requiere mostrar informes al usuario, o generar reportes, con las instrucciones dentro de ellas podemos hacer cálculos de las tablas y regresar el valor ya sea en un informe o en la vista de nuestra aplicación.


Sigue este Blog desde Telegram: Clic aquí para unirte al canal

Compartir
Compartir
Compartir

martes, 18 de julio de 2017

Funciones SQL Server

on julio 18, 2017 , , , , , No hay comentarios.
funciones
SQL Server nos proporciona una numerosa cantidad de funciones integradas del sistema, el cual en esta ocasión hablaré sólo de algunas, además de estas funciones, SQL permite crear funciones de usuario, las cuales podemos crear consultando tablas de nuestra base de datos e incluso combinando con funciones del sistema u otras funciones de usuario.

A considerar al implementar Funciones.

  • Las funciones no pueden ejecutar Procedimientos Almacenados (S.P.).
  • Solo se puede anidar hasta 32 funciones al momento de ejecutar.
  • No pueden insertar datos en otra tabla con la instrucción INTO ejemplo.

INSERT INTO Tabla2
       SELECT * FROM Tabla
  • No se puede formatear el resultado en XML.
  • En las funciones tipo tablas, el resultado no se puede regresar con la instrucción ORDERBY en el RETURN.
  • Solo las tablas temporales como variables están permitidas dentro de las funciones.

Sintaxis de creación, modificación y eliminado de las funciones.

Crear:

        CREATE FUNCTION <nombreFuncion>(<Parametro><TipodeDato>,…n) 

        RETURNS <TipodeDato> 
        AS
       BEGIN
      <Instruccion, operacion, etc.>
             RETURN <campo de regreso del mismo valor del regreso de la función>
       END

Modificar:
       ALTER FUNCTION <nombreFuncion>(<Parametro><TipodeDato>,…n) 
       RETURNS <TipodeDato> 
       AS
       BEGIN
     <Instruccion, operacion, etc.>
            RETURN <campo de regreso del mismo valor del regreso de la función>
       END

Borrar:
          DROPFUNCTION <nombreFuncion>

Tipos de Funciones

Existen 3 tipos de funciones que se puede utilizar dentro de SQL Server, Funciones Escalares, Funciones con valor de tablas y funciones integradas. (Con miedo a equivocarme, pero aquí explicare estos 3 tipos.)



Funciones escalares: Estas funciones devuelven un único valor, al momento de crear la función, se debe indicar que tipo de valor regresa la función. En el siguiente ejemplo es una función que regresa el dato nvarchar en mayusculas:

CREATE FUNCTIONEnMayusculas
(
@Nombre Varchar(50),
@Apellido Varchar(50)
)
RETURNS Varchar(100)
AS
BEGIN
 RETURN (UPPER(@Apellido) + ', ' + UPPER(@Nombre))
END
--Ejecutar
Printdbo.EnMayusculas('damian','ruiz')

Funciones con valor de tabla: Estas funciones, como su nombre lo indica, el valor de retorno es de tipo tabla, esto indica que tiene más de un valor de retorno, se construyen de la siguiente manera.

Anexo el query para el siguiente ejemplo, tabla Personas con 3 datos.

CREATE TABLE Personas(
       PersId int PRIMARY KEY IDENTITY(1,1) NOT NULL,
       PersNombre nvarchar (80) NOT NULL,
       PersFecNacimiento datetime NULL,
       PersActiv bit NOT NULL
       )

INSERT INTOPersonas VALUES ('angelica','1991-02-14',1)
INSERT INTOPersonas VALUES ('braulio','1985-12-25',1)
INSERTINTO Personas VALUES ('carlos','1995-05-04',1)

Ejemplo de la función.

CREATE Function Tabla(@IdEmpleado int)
Returns Table
AS
       Return (Select dbo.enMayusculas(PersNombre) Nombre, PersFecNacimiento, PersActivo
       FromPersonas WHERE PersId = @IdEmpleado)

--Ejecutar
Select * From dbo.Tabla(1)


Nota: Es este ejemplo se utiliza las funciones anidadas, el cual solo se puede llegar hasta 32 sub niveles en las funciones.

Funciones Integradas: Las funciones integradas son las que vienen directamente al instalar nuestra instancia y nuestro servidor de base de datos SQL Server. Aquí enlistare algunas funciones.
  • AVG(): Calcula el promedio.
  • SUM(): Realiza la suma.
  • COUNT(): Contabiliza el total de registros.
  • DATETIME(): Regresa la fecha y hora del sistema en SQL Server.
  • CONCAT(): Concatena cadenas de texto.
  • LOWER(): Regresa el texto en minúsculas.
  • UPPER(): Regresa el texto en mayúsculas.
  • MAX(): Regresa el valor máximo.
  • MIN(): Regresa el valor mínimo.
Nota: Dado a la tendencia de las publicaciones, creo que tomaré la decisión de subir nuevas entradas los días Martes, por el momento, seguiré subiendo lecciones sobre SQL Server.

Sigue este Blog desde Telegram: Clic aquí para unirte al canal

Compartir
Compartir
Compartir

martes, 11 de julio de 2017

Tablas Temporales SQL Server

on julio 11, 2017 , , , , , , , , No hay comentarios.
Las tablas temporales son utilizadas para mantener copia de uno o varios resultados de consultas, y  a estos datos se puede cambiar el valor en la tabla temporal, y no afecta en lo absoluto la tabla o tablas real de donde se obtuvieron los datos.

Todas tablas temporales se almacenan en la base de datos tempdb en las bases de datos del sistema.

Tipos de Tablas Temporales

(#) Tabla Temporal Local: Se utiliza con un # (hash) para indicar que la tabla temporal será utilizada localmente (Solo será accedida a través de la conexión con cual fue creada). Esta tabla es destruida automáticamente en cuanto se cierre la conexión de SQL Server al menos que no se haya eliminado la tabla manualmente mediante el Query.


(##) Tabla Temporal Global: Se utiliza con doble ## (hash) para indicar que la tabla temporal será utilizada globalmente. Esta tabla puede utilizarla cualquier conexión de SQL Server y estas conexiones pueden modificar, borrar o eliminar la tabla temporal y sus registros. Al igual que las tablas temporales locales, esta se destruye cuando la última sesión utilizada por esta, cierre sesión.


(@) Tabla Temporal Variable: Se utiliza con arroba (@) para indicar que se utiliza como variable, esta nueva variable se implementó en la versión 2000 de SQL Server, al crear la tabla temporal como variable, se debe construir la estructura de la tabla en el Query. El debate desde esta implementación siempre fue y será, cuando utilizar una tabla temporal o tabla variable. Una de las recomendaciones para saber cuál utilizar es sí en tu procedimiento se necesita indexar la table (KEY o UNIQUE), lo mejor es utilizar tablas de variables (@).


Ejemplos Prácticos:


Declaración de la variable tabla:



DECLARE@Datos TABLE

(
Id INT IDENTITY,
Nombre Nvarchar(80)
)

Insertar datos a la tabla temporal.

INSERT INTO@Datos VALUES ('Ariel')
INSERT INTO@Datos VALUES ('Braulio')
INSERT INTO@Datos VALUES ('Cassandra')
INSERT INTO@Datos VALUES ('Damian')
INSERT INTO@Datos VALUES ('Erick')
INSERT INTO@Datos VALUES ('Fabiola')
INSERT INTO@Datos VALUES (NULL)
INSERT INTO @Datos VALUES (NULL)


Al ejecutar hasta este paso, SQL solo muestra las filas (rows) fueron afectados.

Para ver los resultados es necesario hacer el SELECT de la tabla temporal y mostrara los datos de la tabla.

SELECT * FROM@Datos


Ahora basados en la tabla temporal del ejemplo de arriba, vamos a copiar los datos a una tabla temporal local (#).

SELECT *
INTO#temporal
FROM @Datos

SELECT * FROM#temporal


Si seguimos ejecutando el SELECT de la table temporal local, los resultados se seguirán mostrando, porque la tabla temporal local no se ha eliminado y no se ha cerrado la sesión de SQL, esto quiere decir que, si ejecutamos el SELECT de la tabla temporal, los datos estarán en la tabla.

Para borrar la tabla temporal local o global se debe ejecutar al iniciar o al finalizar el Query con la siguiente instrucción.

DROP TABLE #temporal

Ejecutada esta acción, sí se trata de correr el SELECT de la tabla temporal local, el mensaje de error aparece indicando que la tabla no existe.



En la tabla temporal como variable hacemos una modificación a la estructura de las columnas, indicando que la columna nombre no acepte nulos (NOT NULL).

DECLARE@Datos TABLE
(
Id INT IDENTITY,
Nombre Nvarchar(80) NOT NULL
)

Si en la tabla se intenta agregar datos nulos, el Query regresa una excepción el cual indica que no se puede agregar datos nulos en la columna nombre.


Nota: En este ejemplo tome los datos de una tabla temporal para ingresarlos en otra tabla temporal, pero esto funciona tomando los datos de cualquier tabla, vista, procedimiento almacenado, etc.

Sigue este Blog desde Telegram: Clic aquí para unirte al canal

Compartir
Compartir
Compartir
Suscribirse a: Comentarios ( Atom )