1
UNIVERSIDAD DON BOSCO
FACULTAD DE ESTUDIOS TECNOLOGICOS
ESCUELA DE COMPUTACION
CICLO 01-2012
GUIA DE LABORATORIO Nº 6 Nombre de la practica: Funciones de Transact SQL
Lugar de ejecución: Laboratorio de Informática Tiempo estimado: 2:30 horas
Materia: Base de datos I I. Objetivos
Qué el estudiante:
• Utilice las funciones que trae integradas el SQL Server 2008 • Cree Funciones propias en Transact SQL
II. Introducción Teórica Funciones Transact Sql
SQL Server pone a nuestra disposición multitud de funciones predefinidas que proporcionan un amplio abanico de posibilidades, así como la posibilidad de que el usuario cree sus propias funciones.
Funciones determinista y no determinista
Las funciones deterministas devuelven el mismo resultado cada vez que se llaman con un conjunto específico de valores de entrada y cuando el estado de la base de datos es el mismo. Las funciones no deterministas pueden devolver resultados diferentes cada vez que se llaman con un conjunto específico de valores de entrada aunque el estado de la base de datos a la que tienen acceso permanezca sin cambios. Las funciones integradas son deterministas o no deterministas según el modo en que SQL Server implementa cada función. Todas las funciones integradas de cadena y de agregados son deterministas. Las siguientes funciones integradas procedentes de categorías que no son de agregados ni de cadena siempre son deterministas.
ABS DATEDIFF POWER
ACOS DAY RADIANS
ASIN DEGREES ROUND
ATAN EXP SIGN
ATN2 FLOOR SIN
CEILING ISNULL SQUARE
COALESCE ISNUMERIC SQRT
COS LOG TAN
COT LOG10 YEAR
DATALENGTH MONTH
2 Ejemplos
ABS
Función matemática que devuelve el valor absoluto positivo de una expresión numérica específica. ABS ( numeric_expression )
SQRT
Devuelve la raíz cuadrada del valor de tipo flotante especificado.
SQRT ( float_expression )
Otras funciones de SQL Server
Función Descripción
ROUND Redondea un valor a la longitud y precisión indicadas. CAST y CONVERT Convierten de un tipo de datos a otro de forma explícita. CASE Evalúa una lista de condiciones.
ISNULL Reemplaza el valor NULL por otro especificado.
COALESCE Devuelve la primera expresión distinta de NULL entre sus argumentos.
Cast y Convert
Convierten una expresión de un tipo de datos en otro de forma explícita. CAST y CONVERT proporcionan funciones similares.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) Donde:
data_type, es el tipo de destino al que queremos convertir la expresion expresion, la expresion que queremos convertir
style, parametro opcional que especifica el formato que tiene expresion. Por ejemplo, si queremos convertir un varchar a datetime, aqui debemos especificar el formato de la fecha (el tipo varchar). A continuación mostramos la tabla de códigos de estilo (obtenida de MicroSoft).
Sin el siglo (aa) (1)
Con el siglo ?(aaaa)
Estándar Entrada/salida (3) - 0 o 100 (1,2) Valor predeterminado mes dd aaaa hh:mia.m. (o p. m.)
1 101 EE.UU. mm/dd/aaaa 2 102 ANSI aa.mm.dd 3 103 Británico/Francés dd/mm/aa 4 104 Alemán dd.mm.aa 5 105 Italiano dd-mm-aa 6 106 (1) - dd mes aa 7 107 (1) - Mes dd, aa
3
8 108 - hh:mi:ss
- 9 o 109 (1,2) Valor predeterminado + milisegundos
mes dd aaaa hh:mi:ss:mmma.m. (o p. m.) 10 110 EE.UU. mm-dd-aa 11 111 JAPÓN aa/mm/dd 12 112 ISO aammdd - 13 o 113 (1,2) Europeo predeterminado + milisegundos dd mes aaaa hh:mi:ss:mmm(24h) 14 114 - hh:mi:ss:mmm(24h)
- 20 o 120 (2) ODBC canónico aaaa-mm-dd hh:mi:ss(24h) - 21 o 121 (2) ODBC canónico (con
milisegundos)
aaaa-mm-dd hh:mi:ss.mmm(24h)
- 126 (4) ISO8601 aaaa-mm-ddThh:mi:ss.mmm (sin
espacios)
127(6, 7) ISO8601 con zona horaria Z. aaaa-mm-ddThh:mi:ss.mmmZ (sin espacios)
- 130 (1,2) Hijri (5) dd mes aaaa hh:mi:ss:mmma.m.
- 131 (2) Hijri (5) dd/mm/aa hh:mi:ss:mmma.m.
Isnull
Evalúa una expresión de entrado y si esta es NULL, reemplaza NULL con el valor de reemplazo especificado. El valor de reemplazo debe ser del mismo tipo de datos que la expresión a evaluar.
ISNULL ( expression , replacement_value )
COALESCE
Devuelve la primera expresión distinta de NULL entre sus argumentos. Un aspecto a tener en cuenta es que todos los argumentos deben ser del mismo tipo.
COALESCE ( expression [ ,...n ] ) ¿Qué valor devuelve?
GetDate y GetUTCDate
GetDate devuelve la fecha y hora actuales del sistema en el formato interno estándar de SQL Server 2005 para los valores datetime.
GetUTCDate devuelve el valor datetime que representa la hora UTC (hora universal coordinada u hora del meridiano de Greenwich) actual.
Funciones definidas por el usuario
SQL Server proporciona al usuario la posibilidad de definir sus propias funciones, conocidad como UDF (user defined functions). Exisiten tres tipos de funciones. Estas son:
Funciones escalares. Funciones en línea.
4 Funciones escalares
Las funciones escalares devuelven un único valor de cualquier tipo de los datos tal como int, money, varchar, real, etc.
La sintaxis para una función escalar es la siguiente:
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> (
-- Lista de parámetros
<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>, ... )
-- Tipo de datos que devuelve la función. RETURNS <Function_Data_Type, ,int> AS BEGIN ... END III. Requerimientos • Microsoft SQL Server 2008 R2 • Guía Número 6 IV. Procedimiento
Parte 1: Iniciando sesión desde SQL Server Managment Studio 1. Hacer clic en el botón Inicio
2. Hacer clic en la opción Todos los programas y hacer clic en Microsoft SQL Server 2008 R2 3. Cargue SQL Server 2008 R2.
Para conectarse con el servidor de base de datos elija los siguientes parámetros de autenticación: Tipo de servidor: Database Engine
Nombre del servidor: SALA02-PCNumMaquina Nota: NumMaquina es el numero de maquina local Autenticación: SQL Server Authentication
Login: sa
Password: 123456
4. Luego de clic en el botón conectar y asi ingresar a la ventana del SQL Server Managment Studio.
5. Luego seleccionara del menú estándar la opción (Nueva Consulta/New Query) para empezar a trabajar con las sentencia de T-SQL.
6. Localice el icono de guardar, con el fin de guardar el nuevo archivo de sentencias T-SQL (SQLQuery1.sql). En el asistente para guardar archivo, cree una carpeta llamada Practica6_BDD en el escritorio (ahí se ira guardando todos los scripts del procedimiento restante).
Parte 2: Funciones Transact Sql
5 Ejemplos
1. ABS
SELECT ABS(-1.0), ABS(0.0), ABS(1.0) ¿Cuál es el resultado?
2. SQRT
En el ejemplo siguiente se devuelve la raíz cuadrada de los números comprendidos entre 1.00 y 10.00. DECLARE @myvalue float;
SET @myvalue = 1.00; WHILE @myvalue < 10.00 BEGIN
SELECT SQRT(@myvalue); SET @myvalue = @myvalue + 1 END;
GO
3. Cast y Convert
DECLARE @fecha varchar(20)
-- Convertimos un valor varchar a datetime
-- El 103 indica el formato en el que esta escrita la fecha -- 103 => dd/mm/aa
SET @fecha = CONVERT(datetime, '19/03/2008',103) SELECT @fecha
Escriba lo que ve en el resultado: DECLARE @fecha datetime,
@fechaFormateada varchar(20)
-- Convertimos ahora una fecha a varchar y la formateamos -- 3 => dd/mm/aa
SET @fecha = GETDATE()
SET @fechaFormateada = CONVERT (varchar(20), @fecha, 3)
SELECT @fecha,@fechaFormateada
Explique qué sucede cuando se muestra el valor que contienen las dos variables: -- Un ejemplo utilizando CAST
DECLARE @dato varchar(2), @dato2 int
SET @dato = '27'
SET @dato2 = cast(@dato AS int)
SELECT @dato2
6 4. IsNull
DECLARE @datoInt int,
@datoVarchar varchar(100)
SET @datoInt = NULL SET @datoVarchar = NULL
SELECT ISNULL(@dato, -1),
ISNULL(@datoVarchar, 'No hay dato')
5. COALESCE
DECLARE @dato1 int, @dato2 int,
@dato3 int, @dato4 int, @dato5 int
SET @dato1 = null SET @dato2 = NULL SET @dato3 = NULL SET @dato4 = 100 SET @dato5 = 125
SELECT COALESCE(@dato1,@dato2,@dato3,@dato4,@dato5)
GetDate y GetUTCDate
DECLARE @fechaLocal datetime, @fechaUTC datetime
SET @fechaLocal = getdate()
SET @fechaUTC = GETUTCDATE()
SELECT @fechaLocal, @fechaUTC
Anote los valores que se muestran en las dos variables.
6. El siguiente ejemplo muestra cómo crear una función escalar.
Nota: para poder probar el funcionamiento de la función cree una base de datos prueba, que contendrá la tabla llamada CUENTAS y con los campos siguientes: SALDO decimal (10,2), NUMCUENTA decimal(10,2) y FXALTA char(2).
Ingrese datos a la tabla CUENTAS para luego realizar pruebas. CREATE FUNCTION fn_MultiplicaSaldo
( @NumCuenta VARCHAR(20), @Multiplicador DECIMAL(10,2) ) RETURNS DECIMAL(10,2) AS BEGIN
7
@Return DECIMAL(10,2)
SELECT @Saldo = SALDO
FROM CUENTAS
WHERE NUMCUENTA = @NumCuenta
SET @Return = @Saldo * @Multiplicador
RETURN @Return END
Pueden ser utilizadas en cualquier sentencia Transact SQL. Un aspecto a tener en cuenta, es que para utilizar una función escalar debemos identificar el nombre de la función con el propietario de la misma.
7. El siguiente ejemplo muestra cómo utilizar la función anteriormente creada en una sentencia Transact SQL. Un aspecto muy a tener en cuenta es que la función ejecutará sus sentencias SELECT una vez por cada fila del conjunto de resultados devuelto por la consulta SELECT principal.
SELECT IDCUENTA, NUMCUENTA,
SALDO, FXALTA,
-- Ejecucion de la funcion:
dbo.fn_MultiplicaSaldo( NUMCUENTA, IDCUENTA) AS RESULTADO
FROM CUENTAS
8. El siguiente ejemplo muestra cómo utilizar una función escalar en un script Transact SQL. DECLARE @NumCuenta VARCHAR(20),
@Resultado DECIMAL(10,2)
SET @NumCuenta = '200700000001' --(Sustituya por valores valor
válido que ud.ud. ---ingreso antetiormente)
SET @Resultado = dbo.fn_MultiplicaSaldo(@NumCuenta, 30.5)
PRINT @Resultado V. Análisis de resultados
Teniendo los datos siguientes, asigne los valores a variables como se indica: o Salario del empleado. Salario de tipo varchar (10)=’22.18’
o Horas trabajadas por el empleado Horas varchar(6)=’38.50’
o Con los datos del salario y las horas trabajadas calcular el pago de la semana del empleado y será guardad en una variable llamada salariosemana de tipo decimal (6,2). El cálculo lo hará de la siguientes manera: salariosemana= salario*Horas. Tome en cuenta que para realizar la operación tiene que hacer conversiones de tipos de datos.
o Muestre el resultado que tiene la variable salariosemana. Utilización de la función Square, para ello se ha definido lo siguiente:
o Va a declarar tres variables como sigue: Lado decimal(10,3)=48.126
8 Perimetro decimal(10,3)
Area decimal(10,3)
o Deberá realizar los siguientes cálculos Perimetro=Lado*4
Area= Lado*Lado
o Una vez realizado los cálculos deberá mostrar lo siguiente: Lado = valor que contiene variable lado
Perimetro = valor que contiene la variable perímetro ya con el cálculo realizado. Area= valor que tiene la variable área ya con el cálculo realizado.
Recuerde que debe hacer conversiones de datos.
VI. Investigación complementaria
Investigue sobre el uso de las funciones de agrupación y sumarización en SQL Server, debe realizar ejemplos.
VII. Referencia Bibliográfica
