Funciones de sql server

  • Published on
    14-Jun-2015

  • View
    2.436

  • Download
    4

Transcript

  • 1. Funciones de SQL serverQu es SQL? Funciones BsicasSQL es un lenguaje universal dedicado a las bases de datos. Es un lenguaje de sintaxis simple y muypotente. Mediante l se puede recorrer, modificar o borrar registros de las tablas de datos.Propiedad SELECT: Hace la seleccin en una tabla de la BD.SELECT * FROM datosEsta sentencia seleccionara todos, absolutamente todos los registros dentro de la tabla datos.SELECT * FROM datos WHERE usuario=JuanSeleccionaramos todos los registros dentro de la tabla datos que tengan como usuario a "Juan".Propiedad INSERT INTO: Agrega un nuevo registro a la tabla elegidaINSERT INTO datos (usuario) VALUES (corsa) Insertamos en la tabla datos en la columnausuario, un registro nuevo.INSERT INTO datos (usuario, edad) VALUES (corsa,20) Aqu insertamos 2 registros almismo tiempo.Propiedad DELETE: Borra registros de nuestra tablaDELETE FROM datos WHERE usuario = PedritoBorramos los registros donde el usuario sea "Pedrito".DELETE FROM datos WHERE usuario = Mario AND edad = 16 Borramos solo los usuario denombre Mario que tenan 16 aos.Propiedad UPDATE: Actualiza registros, modificando datos ya existentes.UPDATE datos SET usuario = Pedrito Esta modificacin renombrar todos los usuarios a"Pedrito".Tipos de funcionesFunciones de conjuntos de filas: Devuelven un objeto que se puede usar como las referencias a tablasen una instruccin SQL.Funciones de agregado: Operan sobre una coleccin de valores y devuelven un solo valor de resumen.Funciones de categora: Devuelven un valor de categora para cada fila de una particin.Funciones escalares: Operan sobre un valor y despus devuelven otro valor. Las funciones escalares sepueden usar donde la expresin sea vlida.Funciones de cadenaLas funciones de cadena trabajan con campos char y varchar por lo que los literales que escribamos sedeben encerrar entre comillas simples. Estas funciones que vamos a explicar a continuacin puedenmanipular cadenas de letras u otros caracteres por lo que las vamos a dividir en dos grupos:Funciones que devuelven caracteresEste tipo de funciones devuelven un carcter o varios caracteres.FuncinPropsitoCHR(n) Nos devuelve el carcter cuyo valor en binario es nCONCAT Nos devuelve cad1 concatenada con cad2UPPERConvierte cad a maysculasLOWERConvierte cad a minsculasLPAD Con esta funcin aadimos caracteres a cad1 por la izquierda hasta una longitud mxima dada por nINITCAPConvierte la primera letra de cad a maysculaLTRIMElimina un conjunto de caracteres a la izquierda de cad, siendo set el conjunto de

2. caracteres a eliminarRPAD Con esta funcin aadimos caracteres de la misma forma que con la funcin LPAD pero esta vez los aadimos a la derechaRTRIMHace lo mismo que LTRIM pero por la derechaREPLACESustituye un conjunto de caracteres de 0 o ms caracteres, devuelve cad con cada ocurrencia de cadena_buscada sustituida por cadena_sustitucionSUBSTR Devuelve la subcadena de cad que abarca desde m hasta el numero de caracteres dados por n.TRANSLATEConvierte caracteres de una cadena en caracteres diferentes. Devuelve cad1 con los caracteres encontrados en cad2 y sustituidos por los caracteres de cad3Ponemos algunos ejemplos de utilizacin de estas funciones:Sentencia sql que nos devuelve las letras cuyo valor asccii es el 45 y el 23select CHR(45), CHR(23) FROM TABLA;Sentencia sql que obtiene el nombre de los alumnos sacando por pantalla la siguiente frase: el nombredel alumno es (nombre que est almacenado en la tabla) select CONCAT (el nombre de alumno es, nombre) from alumno;Sentencia sql que me devuelve los nombres de los alumnos en maysculasselect UPPER(nombre) from alumno;Sentencia sql que obtiene de un campo nombre, las 3 primeras letrasselect SUBSTR(nombre,0,3) from alumno;Y asi con el resto de funcionesFunciones que devuelven valores numricosEstas funciones nos devuelven nmeros a modo de informacin.FuncinPropsitoASCII(cad) Devuelve el valor ASCII de la primera letra de cadINSTR(cad1, cad2[,comienzo[,m]]) Funcin que busca un conjunto de caracteres dentro de una cadena. Nos devuelve la posicin de cad2 en cad1 empezando a buscar en comienzoLENGTH(cad)Devuelve en nmero de caracteres de cadComo con las funciones anteriores dejamos unos ejemplos para que veis su funcionamiento.Sentencia sql que nos devuelve el valor ASCII de la letra (s)select ASCII(s) from tabla;Sentencia que nos devuelve la posicin de la ocurrencia pe dentro de la cadena Los perros estn biena partir de la posicin 2select INSTR(Los perros estn bien,pe,2) from tabla;Sentencia sql que nos devuelve el numero de caracteres de los nombres de los alumnosselect LENGTH(nombre) from alumnos;Funciones en lnea Las funciones en lnea son las funciones que devuelven un conjunto de resultados correspondientes ala ecuacin de una sentencia SELECT.El siguiente ejemplo muestra cmo crear una funcin en lnea. 3. CREATE FUNCTION fn_MovimientosCuenta(@NumCuenta VARCHAR(20))RETURNS TABLEASRETURN(SELECT MOVIMIENTOS.*FROM MOVIMIENTOSINNER JOIN CUENTAS ON MOVIMIENTOS.IDCUENTA = CUENTAS.IDCUENTAWHERE CUENTAS.NUMCUENTA = @NumCuenta)No podemos utilizar la clausula ORDER BY en la sentencia de una funcin el lnea.Las funciones en lnea pueden utilizarse dentro de joins o querys como si fueran una tabla normal.SELECT * FROM fn_MovimientosCuenta(200700000001)SELECT *FROM CUENTASINNER JOIN CUENTAS_CLIENTE ON CUENTAS_CLIENTE.IDCUENTA = CUENTAS.IDCUENTAINNER JOIN CLIENTES ON CLIENTES.id = CUENTAS_CLIENTE.IDCLIENTEINNER JOIN fn_MovimientosCuenta(200700000001) A ON A.IDCUENTA= CUENTAS.IDCUENTATipos de datos de fecha y horaEn la tabla siguiente se enumeran los tipos de datos de fecha y hora de Transact-SQL.Tamao deDesplTipo de almacenam FormatoIntervaloPrecisinPrecisin azami datos iento ento(bytes)timehh:mm:ss[.nnnn De 00:00:00.0000000 a100 De 3 a 5 SNonnn] 23:59:59.9999999 nanosegundodateAAAA-MM-DD De 0001-01-01 a 9999-12-31 1 da3 NoNosmalldate AAAA-MM-DD De 1900-01-01 a 2079-06-061 minuto4 NoNotimehh:mm:ssdate time AAAA-MM-DD De 1753-01-01 a 9999-12-310,00333 8 NoNohh:mm:ss[.nnn] segundosdatetimeAAAA-MM-DD De 0001-01-01 00:00:00.0000000 a 100 De 6 a 8 SNo2 hh:mm:ss 9999-12-31 23:59:59.9999999nanosegundodatetimeo AAAA-MM-DD De 0001-01-01100 De 8 a 10SSffset hh:mm:ss[.nnnn 00:00:00.0000000 a 9999-12-nanosegundonnn] [+|]hh:mm 31 23:59:59.9999999 (en UTC)NOTAEl tipo de datos rowversion de Transact-SQL no es un tipo de datos de fecha y hora. timestamp es un sinnimoobsoleto de rowversin. 4. Funciones de fecha y horaEn las tablas siguientes se enumeran las funciones de fecha y hora de Transact-SQL. Para obtener msinformacin acerca del determinismo, vea Funciones deterministas y no deterministas.Funciones que obtienen valores de fecha y hora del sistemaTodos los valores de fecha y hora del sistema se derivan del sistema operativo del equipo en el que seejecuta la instancia de SQL Server.Funciones de fecha y hora del sistema de precisin elevadaSQL Server 2008 R2 obtiene los valores de fecha y hora utilizando la APIde WindowsGetSystemTimeAsFileTime(). La exactitud depende del hardware del equipo y de la versin deWindows en las que la instancia de SQL Server se est ejecutando. La precisin de esta API se fija en100 nanosegundos. La precisin se puede determinar mediante la API de WindowsGetSystemTimeAdjustment().FuncinSintaxisValor devueltoTipo de Determinis datos moSYSDATETIMESYSDATETIME ()Devuelve un valor datetime2(7) que contiene la fecha ydatetime2 No hora del equipo en el que la instancia de SQL Server se determinista est ejecutando. El ajuste de zona horaria no est incluido.SYSDATETIMEO SYSDATETIMEOFFSET Devuelve un valor datetimeoffset(7) que contiene la datetimeoffsetNoFFSET( ) fecha y hora del equipo en el que la instancia de SQL determinista Server se est ejecutando. El ajuste de zona horaria est incluido. SYSUTCDATETIME ( )Devuelve un valor datetime2(7) que contiene la fecha y datetime2NoSYSUTCDATETI hora del equipo en el que la instancia de SQL Server se deterministaME est ejecutando. La fecha y hora se devuelven como hora universal coordinada (UTC).Funciones de fecha y hora del sistema de precisin bajaFuncinSintaxis Valor devuelto Tipo de Determinismo datos devueltoCURRENT_TIMESTAMPCURRENT_TIMESTAMPDevuelve un valor que contiene la fecha y hora deldatetime No deterministaequipo en el que la instancia de SQL Server seest ejecutando.GETDATEGETDATE ( )Devuelve un valor que contiene la fecha y hora deldatetime No deterministaequipo. El ajuste de zona horaria no est incluido.GETUTCDATE GETUTCDATE ( ) Devuelve un valor que contiene la fecha y hora deldatetime No deterministaequipo. La fecha y hora se devuelven como unahora universal coordinada (UTC). 5. Funciones que obtienen partes de la fecha y hora FuncinSintaxis Valor devuelto TipoDeterminismoDATENAME DATENAME Devuelve una cadena de caracteres que representa el datepartnvarchar No determinista(datepart date) especificado de la fecha especificada. DATEPART DATEPARTDevuelve un entero que representa el datepart especificadointNo determinista DAY DAY ( date ) Devuelve un entero que representa la parte del da de dateint Deterministaespecificado.MONTH MONTH ( date )Devuelve un entero que representa la parte del mes de un date int Deterministaespecificado. YEARYEAR ( date )Devuelve un entero que representa la parte del ao de un date int Deterministaespecificado.Funciones escalaresCategora de la funcinFunciones de configuracin: Devuelven informacin acerca de la configuracin actual.Funciones criptogrficas: Admiten cifrado, descifrado, firma digital y la validacin de firmas digitales.Funciones del cursor: Devuelven informacin acerca de los cursores.Funciones de tipo de datos : Devuelven informacin sobre los valores de identidad y los valores de otrostipos de datos.Tipos de datos y funciones de fecha y hora: Llevan a cabo operaciones sobre un valor de entrada de fechay hora, y devuelven un valor numrico, de cadena o de fecha y hora.Funciones matemticas: Realizan clculos basados en valores de entrada proporcionados como parmetros alas funciones y devuelven valores numricos. Funciones de metadatos: Devuelven informacin acerca de la base de datos y los objetos de la base dedatos.Funciones escalares ODBC: Devuelven informacin sobre funciones ODBC escalares en una instruccinTransact-SQL.Funciones de replicacin: Devuelven informacin que se usa para administrar, supervisar y mantener latopologa de replicacinFunciones de seguridad: Devuelven informacin acerca de usuarios y roles.Funciones de cadena: Realizan operaciones en el valor de entrada de una cadena (char o varchar) y devuelvenuna cadena o un valor numrico.Funciones del sistema: Realizan operaciones y devuelven informacin acerca de valores, objetos yconfiguraciones de una instancia de SQL Server.Funciones estadsticas del sistema: Devuelven informacin estadstica acerca del sistema.Funciones de texto e imagen: Realizan operaciones sobre los valores de entrada o columnas de texto oimagen, y devuelven informacin acerca del valor.Funciones de desencadenador: Devuelven informacin acerca de los desencadenadores. 6. Determinismo de funcinLas funciones integradas de SQL Server son deterministas o no deterministas. Las funciones sondeterministas cuando devuelven siempre el mismo resultado cada vez que se llaman con un conjuntoespecfico de valores de entrada. Las funciones son no deterministas cuando es posible que devuelvandistintos resultados cada vez que se llaman con un mismo conjunto especfico de valores de entrada.Para obtener ms informacin, vea Funciones deterministas y no deterministasIntercalacin de funcionesLas funciones que toman una entrada de cadena de caracteres y devuelven una salida de cadena decaracteres utilizan la intercalacin de la cadena de entrada para la salida.Las funciones que toman entradas que no son de caracteres y devuelven una cadena de caracteresutilizan la intercalacin predeterminada de la base de datos actual para la salida.Las funciones que toman varias entradas de cadena de caracteres y devuelven una cadena decaracteres utilizan las reglas de prioridad de intercalacin para establecer la intercalacin de la cadenade salida. Para obtener ms informacin, vea Prioridad de intercalacin (Transact-SQL).Funciones escalaresLas funciones escalares definidas por el usuario devuelven un nico valor de datos del tipo definido enla clusula RETURNS. Las funciones escalares en lnea no tienen cuerpo; el valor escalar es el resultadode una sola instruccin. Para una funcin escalar de mltiples instrucciones, el cuerpo de la funcin,definido en un bloque BEGIN...END, contiene una serie de instrucciones Transact-SQL que devuelven elvalor nico. El tipo devuelto puede ser de cualquier tipo de datos excepto text, ntext, image, cursor ytimestamp.En el ejemplo siguiente se crea una funcin escalar con mltiples instrucciones. La funcin toma un valorde entrada, ProductID, y devuelve un solo valor de datos, la cantidad agregada del productoespecificado en el inventario.otherCopiarUSE AdventureWorks;GOIF OBJECT_ID (Ndbo.ufnGetInventoryStock, NFN) IS NOT NULL DROP FUNCTION ufnGetInventoryStock;GOCREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)RETURNS intAS-- Returns the stock level for the product.BEGIN DECLARE @ret int; SELECT @ret = SUM(p.Quantity) FROM Production.ProductInventory p WHERE p.ProductID = @ProductIDAND p.LocationID = 6;IF (@ret IS NULL)SET @ret = 0; RETURN @ret;END;GOFunciones integradasSQL Server proporciona las funciones integradas para ayudarle a realizar diversas operaciones. No sepueden modificar. Puede utilizar funciones integradas en instrucciones Transact-SQL para: 7. Tener acceso a informacin de las tablas del sistema de SQL Server sin tener acceso a lastablas del sistema directamente. Para obtener ms informacin, vea Usar las funciones delsistema.Realizar tareas habituales como SUM, GETDATE o IDENTITY. Para obtener ms informacin,vea Funciones (Transact-SQL).Las funciones integradas devuelven tipos de datos escalares o table. Por ejemplo, @@ERROR devuelve0 si la ltima instruccin Transact-SQL se ejecut correctamente. Si la instruccin gener un error,@@ERROR devuelve el nmero de error. Y la funcin SUM( parameter) devuelve la suma de todos losvalores del parmetro.Consultas agregadasLa clausula GROUP BY combina los registros con valores idnticos en un nico registro. Para cadaregistro se puede crear un valor agregado si se incluye una funcin SQL agregada, como por ejemploSum o Count, en la instruccin SELECT. Su sintaxis es:SELECT [ALL | DISTINCT ] [{,}][{,}]FROM |[{,|}][WHERE [{ AND|OR }]][GROUP BY [{,}]][HAVING [{ AND|OR }]][ORDER BY | [ASC | DESC] [{,| [ASC | DESC ]}]] GROUP BY es opcional. Si se utiliza GROUP BY pero no existe una funcin SQL agregada en lainstruccin SELECT se obtiene el mismo resultado que con una consulta SELECT DISTINCT. Losvalores Null en los campos GROUP BY se agrupan y no se omiten. No obstante, los valores Null no seevalan en ninguna de las funciones SQL agregadas. Todos los campos de la lista de campos de SELECT deben incluirse en la clusula GROUP BY o comoargumentos de una...