Preparación para el examen 730 Fundamentos DB2 9, Parte 4: Trabajando con datos DB2

Este tutorial le introduce al Structured Query Language (SQL) y pretende ofrecerle una buena comprensión de cómo el DB2® 9 usa SQL para manipular datos en una base de datos relacional. Este tutorial es el cuarto de una serie de siete tutoriales que usted puede utilizar para prepararse para la Certificación en Fundamentos DB2 9 (Examen 730).

Roman Melnyk, Staff Information Developer, IBM Toronto

Roman MelnykRoman B. Melnyk , Ph.D., es miembro sénior del equipo DB2 Information Development, especializado en administración de base de datos, herramientas DB2 y en SQL. Durante más de once años en IBM, Roman ha escrito numerosos libros DB2, artículos y otros materiales relacionados. Roman fue co-autor de DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies y DB2 for Dummies. Recientemente Roman editó Apache Derby -- Off to the Races.



13-02-2012

Antes de comenzar

Sobre esta serie

¿Está pensando en obtener la certificación en fundamentos DB2 (Examen 730)? Si es así, ha llegado al lugar correcto. Esta serie de siete tutoriales de preparación para la certificación DB2 cubre todo lo básico (los temas que necesitará entender antes de leer la primera pregunta del examen). Incluso si usted no está pensando en obtener la certificación justo ahora, este conjunto de tutoriales es un excelente lugar para comenzar a saber qué hay de nuevo en DB2 9.

Acerca de este tutorial

Este tutorial explica cómo el DB2 usa SQL para manipular datos en una base de datos relacional. El material que se suministra aquí principalmente cubre los objetivos de la Sección 4 del examen, titulada "Trabajando con datos DB2 usando SQL y XQuery." Usted puede ver estos objetivos en: http://www-03.ibm.com/certify/tests/obj730.shtml.

Los temas que se cubren en este tutorial incluyen:

  • Una introducción a SQL
  • Una descripción de Data Manipulation Language (DML) y ejemplos que demuestran cómo usarlo para obtener información específica.
  • Límites de transacciones
  • Procedimientos SQL y funciones definidas por usuario

Objetivos

Después de completar este tutorial, usted deberá estar en capacidad de:

  • Entender los fundamentos de SQL, con un enfoque en elementos de lenguaje SQL
  • Usar DML para seleccionar, insertar, actualizar o eliminar datos
  • Usar enunciados COMMIT y ROLLBACK para administrar transacciones, y conocer lo que constituye un límite de transacción
  • Crear y llamar procedimientos SQL o invocar funciones definidas por usuario desde la línea de comandos

Requisitos de sistema

Si todavía no lo ha hecho, puede descargar e instalar la versión gratuita de IBM DB2 9 para trabajar junto con este tutorial. Instalar DB2 le ayudará a entender muchos de los conceptos que se prueban en el examen de Certificación en Fundamentos DB2 9. La instalación DB2 no se cubre en este tutorial, pero el proceso está documentado en el DB2 Information Center.

Convenciones usadas en este tutorial

En este tutorial se utilizan las siguientes convenciones que resaltan el texto:

  • El texto mono-espaciado se utiliza para enunciados SQL. El texto en MAYÚSCULAS identifica palabras clave en SQL, y el texto en minúsculas identifica valores proporcionados por el usuario en código de ejemplo.
  • Excepto en el código, los nombres de objeto de base de datos se proporcionan en caracteres en mayúsculas, y los nombres de columna de tabla se proporcionan combinando mayúsculas y minúsculas.
  • El texto en Itálica se utiliza para introducir un nuevo término, o para identificar una variable de parámetro.

Todos los ejemplos en este tutorial están basados en la base de datos SAMPLE, que se incluye en el DB2. Como en la mayoría de los casos se proporcionan resultados de muestra, usted no necesita acceder al producto para entender los ejemplos.


Structured Query Language (SQL)

Las partes del enunciado SQL

El lenguaje SQL es un lenguaje que se utiliza para definir y manipular objetos de base de datos. Use el SQL para definir una tabla de base de datos, para insertar datos en la tabla, para cambiar los datos de la tabla y para recuperar datos de la tabla. Como todos los lenguajes, SQL tiene una sintaxis definida y un conjunto de elementos de lenguaje.

La mayoría de los enunciados SQL contienen uno o más de los siguientes elementos de lenguaje:

  • Los caracteres de un solo byte pueden ser una letra (A-Z, a-z, $, # y @, o algún miembro de un conjunto de caracteres extendido), un dígito (0-9), o un caracter especial (incluyendo la coma, el asterisco, el signo de suma, el signo de porcentaje, el signo "&", y muchos otros).
  • Un token es una secuencia de uno o más caracteres. Este puede contener caracteres en blanco, a menos que sea un identificador delimitado (uno o más caracteres encerrados por comillas dobles) o una cadena de caracteres constante.
  • Un identificador SQL es un token que se utiliza para formar un nombre.
  • El tipo de datos de un valor determina cómo el DB2 interpreta ese valor. DB2 so porta tipos definidos por usuario (UDTs).
  • Una constante especifica un valor. Estas están clasificadas como string constants de caracter, gráficas o hexadecimales, o constantes numéricas enteras, decimales, o puntos flotantes.
  • Un registro especial es un área de almacenamiento que es definida para un proceso de aplicación por el gestor de base de datos y que es usado para almacenar información que puede ser referenciada en enunciados SQL. Algunos ejemplos de registros especiales son CURRENT DATE, CURRENT DBPARTITIONNUM y CURRENT SCHEMA.
  • Una rutina puede ser una función, un método o un procedimiento.
    • Una función es una relación entre uno o más valores de entrada y uno o más valores de resultado. Las funciones de base de dato pueden ser tanto pre-diseñadas como definidas por usuario.

      Las funciones de Columna (o agregadas)) operan sobre un conjunto de valores en una columna para retornar un valor individual. Por ejemplo:

      • SUM(sales) retorna la suma de los valores de la columna Sales.
      • AVG(sales) retorna la suma de los valores de la columna Sales, dividida entre el número de valores que hay en esa columna.
      • MIN(sales) retorna el menor valor que haya en la columna Sales.
      • MAX(sales) retorna el mayor valore que haya en la columna Sales.
      • COUNT(sales) retorna el número de valores que no sean nulos de la columna Sales.

      Las funciones escalares funcionan sobre un valor individual para retornar otro valor individual. Por ejemplo:

      • ABS(-5) retorna el valor absoluto de -5 (es decir, 5).
      • HEX(69) retorna la representación hexadecimal del número 69 (es decir, 45000000).
      • LENGTH('Pierre') retorna el número de bytes que hay en la secuencia "Pierre" (es decir, 6). Para una cadena de caracteres GRAPHIC, la función LENGTH retorna un número de caracteres de doble byte.
      • YEAR('03/14/2002') extrae la porción de año de 03/14/2002 (es decir, 2002).
      • MONTH('03/14/2002') extrae la porción de mes de 03/14/2002 (es decir, 3).
      • DAY('03/14/2002') extrae la porción de día de 03/14/2002 (es decir, 14).
      • LCASE('SHAMAN') o LOWER('SHAMAN') retornan una cadena en la cual todos los caracteres han sido convertidos a caracteres en minúsculas (es decir, 'shaman').
      • UCASE('shaman') o UPPER('shaman') retornan una cadena en la cual todos los caracteres han sido convertidos a caracteres en mayúsculas (es decir, 'SHAMAN').

      Las funciones definidas por usuario están registradas en una base de datos en el catálogo de sistema (accesible mediante la vista de catálogo SYSCAT.ROUTINES ) usando el enunciado CREATE FUNCTION .

    • Un método también es una relación entre un conjunto de valores de datos de entrada y un conjunto de valores de resultado. Sin embargo, los métodos de base de datos están definidos, bien sea de forma implícita o explícita, como parte de la definición de un tipo estructurado definido por usuario. Por ejemplo, a un método llamado CITY (de tipo ADDRESS) se le pasa un valor de entrada de tipo VARCHAR y el resultado es un subtipo ADDRESS. Los métodos definidos por usuario están registrados en una base de datos en el catálogo de sistema (accesible mediante la vista de catálogo SYSCAT.ROUTINES ) usando el enunciado CREATE METHOD . Para más información sobre tipos estructurados, consulte Una introducción a las tablas estruturadas con tipos y de tipo de datos.
    • Un procedimiento es un programa de aplicación que se puede iniciar ejecutando un enunciado CALL . Los argumentos de un procedimiento son valores escalares individuales que pueden ser de diferentes tipos y que se pueden usar para pasar valores hacia el procedimiento, para recibir valores de retorno del procedimiento, o ambos. Los procedimientos definidos por usuario están registrados en una base de datos en el catálogo de sistema (accesible mediante la vista de catálogo SYSCAT.ROUTINES ) usando el enunciado CREATE PROCEDURE .
  • Una expresión especifica un valor. Hay expresiones de cadena de caracteres, expresiones aritméticas y expresiones de tipo de letra (may./min.), que puede utilizarse para especificar un resultado particular con base en la evaluación de una o más condiciones.
  • Un predicado especifica una condición que es cierta, falsa o desconocida con respecto a una fila o grupo dados. Existen subtipos:
    • Un predicado básico compara dos valores (por ejemplo, x > y).
    • El predicado BETWEEN compara un valor dentro de un rango de valores.
    • El predicado EXISTS comprueba a existencia de ciertas filas.
    • El predicado IN compara uno o más valores con una colección de valores.
    • El predicado LIKE busca secuencias que tengan cierto patrón.
    • El predicado NULL comprueba valores nulos.

Data Manipulation Language (DML)

Usando el enunciado SELECT para recuperar datos de tablas de bases de datos

El enunciado SELECT se utiliza para recuperar datos de tabla o de vista. En su forma más simple, el enunciado SELECT puede usarse para recuperar todos los datos de una tabla. Por ejemplo, para recuperar todos los datos STAFF de una base de datos SAMPLE, emita el siguiente comando:

SELECT * FROM staff

Este es un conjunto parcial de resultados retornados por esta consulta:

Conjunto de resultados parciales
IDNAMEDEPTJOBYEARSSALARYCOMM
10 Sanders 20 Mgr 7 18357.50 -
20 Pernal 20 Sales 8 18171.25 612.45
30 Marenghi 38 Mgr 5 17506.75 -

Para restringir el número de filas de un conjunto de resultados, use la cláusula FETCH FIRST . Por ejemplo:

SELECT * FROM staff FETCH FIRST 10 ROWS ONLY

Recupere columnas específicas de una tabla especificando una lista seleccionada de nombres de columna separados por comas. Por ejemplo:

SELECT name, salary FROM staff

Use la cláusula DISTINCT para eliminar filas duplicadas de un conjunto de resultados. Por ejemplo:

SELECT DISTINCT dept, job FROM staff

Use la cláusula AS para asignar un nombre significativo a una expresión o a un elemento de la lista seleccionada. Por ejemplo:

SELECT name, salary + comm AS pay FROM staff

Sin la cláusula AS , la columna derivada habría sido llamada 2, indicando que es la segunda columna del conjunto de resultados.

Usando la cláusula WHERE y predicados para limitar la cantidad de datos retornados por una consulta

Use la cláusula WHERE para seleccionar filas especificar de una tabla o vista, al especificar uno o más criterios de selección o condiciones de búsqueda. Una condición de búsqueda consiste en uno o más predicados. Un predicado especifica algo sobre una fila que es o verdadera o falsa (vea Las partes del enunciado SQL) ). Cuando esté construyendo condiciones de búsqueda, asegúrese de:

  • Aplicar operaciones aritméticas solo a tipos de datos numéricos
  • Hacer comparaciones únicamente entre tipos de datos comparables
  • Encerrar valores de caracteres dentro de comillas individuales
  • Especificar valores de caracteres exactamente como aparecen en la base de datos

Observemos algunos ejemplos.

  • Halle los miembros del personal cuyos salarios sean superiores a $20.000:
    "SELECT name, salary FROM staff
      WHERE salary > 20000"

    Encerrar el enunciado en comillas dobles evita que si sistema operativo malinterprete caracteres especiales, como * o >; el símbolo mayor-que podría ser interpretado como una solicitud de redireccionamiento de resultado.
  • Haga una lista con el nombre, el cargo y el salario de los miembros que no son gerentes y cuyo salario sea superior a $20.000:
    "SELECT name, job, salary FROM staff
      WHERE job <> 'Mgr'
      AND salary > 20000"
  • Encuentre todos los miembros que inicien con la letra S:
    SELECT name FROM staff
      WHERE name LIKE 'S%'

    En este ejemplo, el signo de porcentaje (%) es el caracter comodín que representa una cadena de caracteres de cero o más caracteres.

Una sub-consulta es un enunciado SELECT que aparece dentro de la cláusula WHERE de una consulta principal y que alimenta su conjunto de resultados a esa cláusula WHERE . Por ejemplo:

"SELECT lastname FROM employee
  WHERE lastname IN
  (SELECT sales_person FROM sales
    WHERE sales_date < '01/01/1996')"

Un nombre de correlación se define en la cláusula FROM de una consulta y puede servir como un nombre corto conveniente para una tabla. Los nombres de correlación también eliminan referencias ambiguas a nombres de columna idénticos de tablas diferentes. Por ejemplo:

"SELECT e.salary FROM employee e
  WHERE e.salary <
  (SELECT AVG(s.salary) FROM staff s)"

Usando la cláusula ORDER BY para organizar resultados

Use la cláusula ORDER BY para ordenar el conjunto de resultados por valores, en una o más columnas. Los nombres de columna que se especifican en la cláusula ORDER BY no necesitan ser especificadas en la lista seleccionada. Por ejemplo:

"SELECT name, salary FROM staff
  WHERE salary > 20000
  ORDER BY salary"

Ordene el conjunto de resultados en orden descendente especificando DESC en la cláusula ORDER BY :

ORDER BY salary DESC

Usando enlaces para recuperar datos de más de una tabla

Un enlace es una consulta que combina datos de dos o más tablas. A menudo es necesario para seleccionar información proveniente de dos o más tablas porque los datos requeridos a menudo están distribuidos. Un enlace añade columnas al conjunto de resultados. Por ejemplo, un enlace completo de dos tablas de tres columnas produce un conjunto de resultados con seis columnas.

La unión más simple es aquella en la que no hay condiciones especificadas. Por ejemplo:

SELECT deptnumb, deptname, manager, id, name, dept, job 
  FROM org, staff

Este enunciado retorna todas las combinaciones de filas de la tabla ORG y de la tabla STAFF. Las primeras tres columnas provienen de la tabla ORG, y las cuatro últimas columnas provienen de la tabla STAFF. Dicho conjunto de resultados (el producto cruzado entre las dos tablas) no es muy útil. lo que se necesita es una condición de enlace para refinar el conjunto de resultados. Por ejemplo, esta es una consulta que está diseñada para identificar miembros del personal que son gerentes:

SELECT deptnumb, deptname, id AS manager_id, name AS manager 
  FROM org, staff 
  WHERE manager = id 
  ORDER BY deptnumb

Y este es un conjunto de resultados parcial retornado por esta consulta:

Identificando miembros del personal
DEPTNUMBDEPTNAMEMANAGER_IDMANAGER
10 Head Office 160 Molinare
15 New England 50 Hanes
20 Mid Atlantic 10 Sanders

El enunciado al que usted observó en la última sección es un ejemplo de un enlace interno. Los enlaces internos solo retornan filas del producto cruzado que cumplan con la condición de enlace. Si una fila existe en una tabla pero no en la otra, esto no se incluye en el conjunto de resultados. Para especificar explícitamente un enlace interno, reescriba la anterior consulta con un operador INNER JOIN en la cláusula FROM :

...
  FROM org INNER JOIN staff
  ON manager = id
...

La palabra clave ON especifica las condiciones de enlace para las tablas que se están enlazando. DeptNumb y DeptName son columnas de la tabla ORG, y Manager_ID y Manager están basadas en las columnas (ID y Name) de la tabla STAFF. El conjunto de resultados para el enlace interno consiste en filas que tienen valores que coinciden para las columnas Manager e ID de la tabla izquierda (ORG) y la tabla derecha (STAFF), respectivamente. (Cuando usted efectúa un enlace sobre dos tablas, usted designa de forma arbitraria una tabla para que sea la tabla izquierda y otra para que sea la derecha).

Los enlaces externos retornan filas que son generadas por una operación de enlace interno, además de filas que no habrían sido retornadas por la operación de enlace interno. Existen tres tipos de enlaces externos:

  • Un left outer join incluye el enlace interno y las filas de la tabla izquierda que no son retornadas por el enlace interno. Este tipo de enlace usa el operador LEFT OUTER JOIN (o LEFT JOIN) en la cláusula FROM .
  • Un right outer join incluye el enlace interno más las filas de la tabla derecha que no son retornadas por el enlace interno. Este tipo de enlace usa el operador RIGHT OUTER JOIN (o RIGHT JOIN) en la cláusula FROM .
  • Un full outer join incluye el enlace interno más las filas de ambas tablas, la derecha y la izquierda que no son retornadas por el enlace interno. Este tipo de enlace usa el operador FULL OUTER JOIN (o FULL JOIN) en la cláusula FROM .

Construya consultas más complejas para responder a preguntas más difíciles. La siguiente consulta está diseñada para generar una lista de empleados que sean responsables por proyectos, identificando aquellos empleados que también sean gerentes, haciendo una lista de los departamentos que pueden gerenciar:

SELECT empno, deptname, projname 
  FROM (employee 
  LEFT OUTER JOIN project 
  ON respemp = empno) 
  LEFT OUTER JOIN department 
  ON mgrno = empno

El primer enlace externo obtiene el nombre de cualquier proyecto del cual el empleado sea responsable; este enlace externo está encerrado entre paréntesis y se resuelve primero. El segundo enlace externo obtiene su nombre del departamento del empleado, si ese empleado es un gestor.

Usando un conjunto de operadores para combinar dos o más consultas en una sola consulta

Combine dos o más consultas en una sola consulta usando los operadores de conjunto UNION, EXCEPT, o INTERSECT . Los operadores de conjunto procesan el resultado de las consultas, eliminan duplicados y retornan el conjunto final de resultados.

  • El conjunto UNION genera una tabla de resultados al combinar dos o más tablas de otros resultados.
  • El conjunto EXCEPT genera una tabla de resultados incluyendo todas las filas que son retornadas por la primera consulta, pero no por la segunda consulta ni por las subsiguientes.
  • El conjunto INTERSECT genera una tabla de resultados incluyendo solo las filas que son retornadas por todas las consultas.

El siguiente es un ejemplo de una consulta que utiliza el operador de conjunto UNION . La misma consulta puede usar el operador de conjunto EXCEPT o INTERSECT , sustituyendo la palabra clave apropiada por UNION.

"SELECT sales_person FROM sales 
  WHERE region = 'Ontario-South' 
UNION 
SELECT sales_person FROM sales 
  WHERE sales > 3"

Usando la cláusula GROUP BY para resumir resultados

Use la cláusula GROUP BY para organizar las filas de un conjunto de resultados. Cada grupo es representado por una sola fila del conjunto de resultados. Por ejemplo:

SELECT sales_date, MAX(sales) AS max_sales FROM sales 
  GROUP BY sales_date

Este enunciado retorna una lista de las fechas de venta de la tabla SALES. La tabla SALES de la base de datos de muestra SAMPLE contiene datos de ventas, incluyendo el número de transacciones exitosas de un vendedor particular, en una fecha en particular. Normalmente hay más de un registro por fecha. La cláusula GROUP BY agrupa los datos por fecha de venta, y la función MAX en este ejemplo retorna el número máximo de ventas registradas para cada fecha de ventas.

Un sabor diferente de la cláusula GROUP BY incluye la especificación de la cláusula GROUPING SETS . Los grouping sets pueden utilizarse para analizar datos a diferentes niveles de agregación en una sola pasada. Por ejemplo:

SELECT YEAR(sales_date) AS year, region, SUM(sales) AS tot_sales 
  FROM sales
  GROUP BY GROUPING SETS (YEAR(sales_date), region, () )

Aquí, la función YEAR es usada para retornar la porción de año de los valores de datos, y la función SUM se utiliza para retornar el total de cada conjunto de cifras de ventas agrupadas. La lista de conjuntos de agrupación especifica cómo serán agrupados los datos, o agregados. Un para de paréntesis desocupados es añadido a la lista de conjuntos agrupados para obtener un gran total en el conjunto de resultados. El enunciado retorna los siguiente:

Resultado del comando de conjuntos de agrupación
YEARREGIONTOT_SALES
- - 155
- Manitoba 41
- Ontario-North 9
- Ontario-South 52
- Quebec 53
1995 - 8
1996 - 147

Un enunciado que es casi idéntico al anterior, pero que especifica la cláusula ROLLUP o la cláusula CUBE en lugar de la cláusula GROUPING SETS , retorna un conjunto de resultados que proporciona una perspectiva más detallada sobre los datos. Esta puede proporcionar resúmenes por ubicación u hora.

La cláusula HAVING a menudo es usada con la cláusula GROUP BY para recuperar resultados que solo satisfagan una condición específica. Una cláusula HAVING puede contener uno o más predicados que comparan algunas propiedades del grupo con otras propiedades del grupo o con una constante. Por ejemplo:

"SELECT sales_person, SUM(sales) AS total_sales FROM sales 
  GROUP BY sales_person 
  HAVING SUM(sales) > 25"

Este enunciado retorna una lista de los vendedores cuyos totales de ventas excedan 25.

Usando el enunciado INSERT para añadir nuevas filas a tablas o vistas

El enunciado INSERT se utiliza para añadir nuevas filas a una tabla o a una vista. Insertar una fila en una vista también inserta la fila en la que se basa la lista.

  • Use una clausula VALUES para especificar datos de columna para una o más filas. Por ejemplo:
    INSERT INTO staff VALUES (1212,'Cerny',20,'Sales',3,90000.00,30000.00)
    
    INSERT INTO staff VALUES (1213,'Wolfrum',20,'Sales',2,90000.00,10000.00)

    O el equivalente:
    INSERT INTO staff (id, name, dept, job, years, salary, comm)
      VALUES
      (1212,'Cerny',20,'Sales',3,90000.00,30000.00),
      (1213,'Wolfrum',20,'Sales',2,90000.00,10000.00)
  • Especifique un fullselect para identificar datos que se copiarán desde otras tablas o vistas. Un fullselect es un enunciado que genera una tabla de resultados. Por ejemplo:
    CREATE TABLE pers LIKE staff
    
    INSERT INTO pers
      SELECT id, name, dept, job, years, salary, comm
        FROM staff
        WHERE dept = 38

Usando el enunciado UPDATE para cambiar datos de tablas o vistas

El enunciado UPDATE se utiliza para cambiar los datos de una tabla o una vista. Cambie el valor de una o más columnas de cada fila que satisfaga las condiciones especificadas por la cláusula WHERE . Por ejemplo:

UPDATE staff
  SET dept = 51, salary = 70000
    WHERE id = 750

O el equivalente:

UPDATE staff
  SET (dept, salary) = (51, 70000)
    WHERE id = 750

Si usted no especifica una cláusula WHERE , ¡DB2 actualizará cada fila de la tabla o vista!

Usando el enunciado DELETE para eliminar datos

El enunciado DELETE se utiliza para eliminar filas enteras de datos de una tabla. Elimine cada fila que satisfaga las condiciones especificadas por una cláusula WHERE . Por ejemplo:

DELETE FROM staff
  WHERE id IN (1212, 1213)

Si usted no especifica una cláusula WHERE , ¡DB2 eliminará todas las filas de la tabla o vista!

Usar el enunciado MERGE para combinar operaciones condicionales de actualizar, insertar o eliminar

El enunciado MERGE actualiza una tabla objetivo o una vista actualizable usando datos desde una tabla fuente. Durante una operación individual las filas en el objetivo que coinciden con la fuente pueden ser actualizadas o eliminadas, y las filas que no existen en el objetivo pueden ser insertadas.

Por ejemplo, considere que la tabla EMPLOYEE sea la tabla objetivo que contiene información actualizada sobre los empleados de una compañía grande. Las sucursales manejan actualizaciones de los registros de los empleados locales, manteniendo su propia versión de la tabla EMPLOYEE llamada MY_EMP. El enunciadoMERGE puede usarse para actualizar la tabla EMPLOYEE con información que esté contenida en la tabla MY_EMP, que es la tabla fuente para la operación de fusión.

El siguiente enunciado inserta una fila para número de nuevo empleado 000015 en la tabla MY_EMP.

INSERT INTO my_emp (empno, firstnme, midinit, lastname, workdept,
  phoneno, hiredate, job, edlevel, sex, birthdate, salary)
  VALUES ('000015', 'MARIO', 'M', 'MALFA', 'A00',
  '6669', '05/05/2000', 'ANALYST', 15, 'M', '04/02/1973', 59000.00)

Y el siguiente enunciado inserta datos de salario actualizados para el número del empleado existente 000010 en la tabla MY_EMP.

INSERT INTO my_emp (empno, firstnme, midinit, lastname, edlevel, salary)
  VALUES ('000010', 'CHRISTINE', 'I', 'HAAS', 18, 66600.00)

En este punto, los datos insertados solo existen en la tabla MY_EMP porque todavía no ha sido fusionada con la tabla EMPLOYEE. A continuación está el enunciado MERGE que toma el contenido de la tabla MY_EMP y lo integra con la tabla EMPLOYEE.

MERGE INTO employee AS e
  USING (SELECT
   empno, firstnme, midinit, lastname, workdept, phoneno,
   hiredate, job, edlevel, sex, birthdate, salary
   FROM my_emp) AS m
  ON e.empno = m.empno
  WHEN MATCHED THEN
    UPDATE SET (salary) = (m.salary)
  WHEN NOT MATCHED THEN
    INSERT (empno, firstnme, midinit, lastname, workdept, phoneno,
     hiredate, job, edlevel, sex, birthdate, salary)
      VALUES (m.empno, m.firstnme, m.midinit, m.lastname,
       m.workdept, m.phoneno, m.hiredate, m.job, m.edlevel,
       m.sex, m.birthdate, m.salary)

Se han asignado nombres de correlación tanto a la tabla fuente como a la tabla objetivo, para evitar referencias de tabla ambiguas en la condición de búsqueda. El enunciado identifica las columnas en la tabla MY_EMP que se considerarán. El enunciado también especifica las acciones que se deben tomar cuando se encuentra que la tabla MY_EMP debe tener una coincidencia en la tabla EMPLOYEE, o cuando una fila no tiene una coincidencia.

La siguiente consulta ejecutada contra la tabla EMPLOYEE ahora retorna un registro para el empleado 000015:

SELECT * FROM employee WHERE empno = '000015'

Y la siguiente consulta retorna el registro para el empleado 000010 con un valor actualizad o para la columna SALARY.

SELECT * FROM employee WHERE empno = '000010'

Usando la cláusula data-change-table-reference para recuperar conjuntos de resultados inmediatos en la misma unidad de trabajo

Suponga que desea dar al empleado 000220 un aumento del 7% y recuperar su antiguo salario, ambos en la misma unidad de trabajo (UOW). Usted podría lograr esto usando una cláusula data-change-table-reference , la cual es parte de la cláusula FROM de un enunciado SQL.

SELECT salary FROM OLD TABLE (
  UPDATE employee SET salary = salary * 1.07
  WHERE empno = '000220'
);

SALARY
-----------
   29840.00

  1 record(s) selected.

Las columnas en el objetivo de la operación data-change (inser, update o delete) se convierten en las columnas de la tabla de resultados intermedia, y pueden ser referenciados por nombre (en este caso, Salary) en la lista seleccionada de la consulta. Las palabras clave OLD TABLE especifican que la tabla intermedia de resultados contendrá valores que preceden a la operación data-change. Las palabras clave NEW TABLE especifican que la tabla intermedia de resultados contendrá valores que siguen inmediatamente la operación data-change (antes que de la evaluación de integridad referencial y de la activación de los disparadores posteriores se haya presentado). Las palabras clave FINAL TABLE especifican que la tabla intermedia de resultados contendrá valores que siguen a la operación data-change, la evaluación de integridad referencial y a la activación de los disparadores posteriores.

Suponga que usted tiene una tabla CUSTOMERS que se define así:

CREATE TABLE customers (
  cust_id INTEGER GENERATED ALWAYS AS IDENTITY (
   START WITH 10001
  ),
  cust_name VARCHAR(12),
  PRIMARY KEY (cust_id)
);

La clave primaria de esta tabla, Cust_ID, es una columna de identidad generada automáticamente. Usted puede usar una cláusula data-change-table-reference para recuperar el valor de la columna de identidad generado, que está siendo usado como número de cliente.

SELECT * FROM FINAL TABLE (
  INSERT INTO customers (cust_name) VALUES ('Lamarr')
);

CUST_ID     CUST_NAME
----------- ------------
      10001 Lamarr

  1 record(s) selected.

Los enunciados COMMIT y ROLLBACK y los límites de transacción

Unidades de trabajo y savepoints

Una unidad de trabajo (UOW), también conocida como transacción, es una secuencia recuperable de operaciones dentro de un proceso de aplicación. El ejemplo clásico de una UOW es una transacción bancaria simple para transferir fondos de una cuanta a otra. Existe una inconsistencia (inmediatamente después la aplicación sustrae una cantidad de dinero de una cuenta). Cuando estos cambios se han confirmado, quedan disponibles para otras aplicaciones.

Una UOW inicia implícitamente cuando el primer enunciado SQL dentro de un proceso de aplicación se emite contra la base de datos. Todas las lecturas y escrituras subsiguientes hechas por el mismo proceso de aplicación son consideradas parte de la misma UOW. La aplicación termina el UOW usando un enunciado COMMIT o un enunciado ROLLBACK , cualquiera que sea apropiado. El enunciado COMMIT hace permanentes todos los cambios que suceden dentro de la UOW, mientras que el enunciado ROLLBACK revierte estos cambios. Si la aplicación termina normalmente sin un enunciado COMMIT ni uno ROLLBACK , la UOW se confirma automáticamente. Si la aplicación termina anormalmente antes de que termine una UOW, esa unidad de trabajo es revertida automáticamente.

Un savepoint le permite revertir un subconjunto de acciones que constituyen una UOW, sin perder toda la transacción. Usted puede anidar savepoints y tener varios niveles de savepoint activos al mismo tiempo; esto le permite a su aplicación revertirse hacia un savepoint específico, según sea necesario. Suponga que usted tiene tres savepoints (A, B y C) definidos dentro de una UOW particular:

do some work;
 savepoint A;
 do some more work;
  savepoint B;
  do even more work;
   savepoint C;
   wrap it up;
  roll back to savepoint B;

La retroacción al savepoint B libera automáticamente el savepoint C, pero los savepoints A y B permanecen activos.

Para más información sobre niveles de savepoint y para un ejemplo detallado que ilustra el soporte sobre savepoint del DB2, vea Recursos.


Procedimientos SQL y funciones definidas por usuario

Creando y llamando un procedimiento SQL

Un procedimiento SQL es un procedimiento almacenado cuyo cuerpo está escrito en SQL. El cuerpo contiene la lógica del procedimiento SQL. Este puede incluir declaraciones de variable, manejo de condiciones, enunciados de flujo de control y DML. Se pueden especificar múltiples enunciados SQL dentro de un enunciado compuesto, el cual agrupa varios enunciados en un bloque ejecutable.

Un procedimiento SQL es creado cuando usted invoca exitosamente un enunciado CREATE PROCEDURE (SQL) , que define el procedimiento SQL con un servidor de aplicaciones. los procedimientos SQL son una forma fácil de definir consultas más complejas o tareas que puedan llamarse cuando se necesiten.

Una forma fácil para crear un procedimiento SQL es codificar el enunciado CREATE PROCEDURE (SQL) en un script de procesador de línea de comandos (CLP). Por ejemplo, si el enunciado mostrado abajo estuviera en el archivo llamado createSQLproc.db2, ese archivo podría ejecutarse para crear el procedimiento SQL:

  1. Conéctese a la base de datos SAMPLE.
  2. Emita el siguiente comando:
    db2 -td@ -vf createSQLproc.db2

Este comando db2 especifica la banderilla de opción -td , la cual le dice al procesador de línea de comandos que defina y use @ como el caracter de terminación de enunciado (porque el punto y coma ya está siendo utilizado como caracter de terminación de enunciado dentro del cuerpo de procedimiento); la banderilla de opción -v , que le dice al procesador de línea de comandos que haga eco texto de comando a resultado estándar; y la banderilla de opción -f , que le dice al procesador de línea de comandos que lea un ingreso de comando desde el archivo especificado, en lugar de la entrada estándar.

CREATE PROCEDURE sales_status
(IN quota INTEGER, OUT sql_state CHAR(5))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
  DECLARE SQLSTATE CHAR(5);
  DECLARE rs CURSOR WITH RETURN FOR
  SELECT sales_person, SUM(sales) AS total_sales
    FROM sales
    GROUP BY sales_person 
    HAVING SUM(sales) > quota;
  OPEN rs;
  SET sql_state = SQLSTATE;
END @

Este procedimiento, llamado SALES_STATUS, acepta un parámetro de entrada llamado quota y retorna un parámetro de salida llamado sql_state. El cuerpo del procedimiento consiste en de un solo enunciado SELECT que retorna el nombre y las cifras de las ventas totales de cada vendedor cuyas ventas totales excedan la cuota especificada.

La mayoría de los procedimientos SQL aceptan por lo menos un parámetro de entrada. En nuestro ejemplo, el parámetro de entrada contiene un valor (quota) que se usa en el enunciado SELECT contenido en el cuerpo de procedimiento.

Muchos procedimientos SQL retornan por lo menos un parámetro de salida. Nuestro ejemplo incluye un parámetro de resultado (sql_state) que se utiliza para informar el éxito o la falla del procedimiento SQL. El DB2 retorna un valor SQLSTATE en respuesta a las condiciones que podrían ser resultado de un enunciado SQL. Como el valore retornado SQLCODE o SQLSTATE pertenece al último enunciado SQL emitido en el cuerpo de procedimiento, y acceder a los valores altera los valores subsiguientes de estas variables (porque se utiliza una enunciado SQL para acceder a ellos), el valor SQLCODE o SQLSTATE debe ser asignado a, y retornado a través de, una variable definida localmente (como la variable sql_state de nuestro ejemplo).

La lista de parámetros para un procedimiento SQL puede especificar cero o más parámetros, cada uno de los cuales puede tener uno de tres tipos posibles:

  • Los parámetros IN pasan un valor de entrada hacia un procedimiento SQL, este valor no puede ser modificado dentro del cuerpo del procedimiento.
  • Los parámetros OUT retornan un valor de resultado a partir de un procedimiento SQL.
  • Los parámetros INOUT pasan un valor de entrada a un procedimiento SQL y retornan un valor de salida desde el procedimiento.

Los procedimientos SQL pueden retornar cero o más conjuntos de resultados. En nuestro ejemplo, el procedimiento SALES_STATUS retorna un conjunto de resultados. Esto se ha llevado a cabo:

  1. Declarando el número de conjuntos de resultados que ese procedimiento SQL retorna en la cláusula DYNAMIC RESULT SETS .
  2. Declarando un cursor en el cuerpo del procedimiento (usando la cláusula WITH RETURN FOR ) para cada conjunto de resultados que se retorna. Un cursor es una estructura de control con nombre, que es usada por un programa de aplicaciones para apuntar a una fila específica dentro de un conjunto ordenado de filas. Los cursores se utilizan para recuperar filas de un conjunto.
  3. Abrir el cursor para cada conjunto de resultados que se retorna.
  4. Dejar el cursor(es) abierto(s) cuando retorne el procedimiento SQL.

Las variables se deben declarar al comienzo del cuerpo del procedimiento SQL. Para declarar una variable, asígnele un identificador único y especifique un tipo de datos SQL para la variable y, opcionalmente, asigne un valor inicial a la variable.

La cláusula SET en nuestro procedimiento SQL de muestra es un ejemplo de cláusulaflow-of-control . Los siguientes enunciados, estructuras y cláusulas flow-of-control pueden usarse para procesamiento condicional dentro de un cuerpo de procedimiento SQL:

  • La estructura CASE selecciona una ruta de ejecución con base en la evaluación de una o más condiciones.
  • La estructura FOR ejecuta un bloque de código para cada fila de una tabla.
  • El enunciado GET DIAGNOSTICS retorna información sobre el enunciado SQL anterior en una variable SQL.
  • El enunciado GOTO transfiere control hacia un bloque etiquetado (una sección de uno o más enunciados identificados por un nombre SQL único seguido por dos puntos).
  • La estructura IF selecciona una ruta de ejecución con base en la evaluación de condiciones. Las cláusulas ELSEIF y ELSE le permiten ramificar o especificar una acción predeterminada si otras condiciones son falsas.
  • La cláusula ITERATE pasa el flujo de control al comienzo del bucle etiquetado.
  • La cláusula LEAVE transfiere control de programa hacia afuera del bucle o bloque de código.
  • La cláusula LOOP ejecuta un bloque de código múltiples veces hasta que un enunciado LEAVE, ITERATE o GOTO transfiere el control hacia afuera del bucle.
  • La cláusula REPEAT ejecuta un bloque de código hasta que de una condición de búsqueda específica retorne verdadero.
  • La cláusula RETURN retorna control desde el procedimiento SQL hacia quien hace el llamado.
  • La cláusula SET asigna un valor a un parámetro de salida o variable SQL.
  • La cláusula WHILE ejecuta repetidamente un bloque de código mientras una condición especificada retorne verdadero.

Para crear exitosamente procedimientos SQL, usted debe tener instalado el DB2 Application Development Client en el servidor de base de datos. (Vea el primer tutorial de esta serie para más información sobre el Application Development Client.) La dependencia en un compilador C para crear procedimientos SQL fue eliminada en el DB2 Universal Database Version 8. Todas las operaciones que dependían de un compilador C son ahora efectuadas por código de byte generado por el DB2, que es hospedado en una máquina virtual. Para más información sobre esta mejora, vea Recursos.

Use el enunciado SQL CALL para llamar procedimientos SQL desde la línea de comandos DB2. El procedimiento que se esté llamando debe estar definido en el catálogo de sistema. Las aplicaciones de cliente escritas en cualquier lenguaje soportado pueden llamar procedimientos SQL. Para llamar al procedimiento SQL SALES_STATUS, siga estos pasos:

  1. Conéctese a la base de datos SAMPLE.
  2. Emita el siguiente enunciado:
    db2 CALL sales_status (25, ?)

Como los paréntesis tienen un significado especial para el shell de comandos en sistemas basados en UNIX, en esos sistemas estos deben estar precedidos por un caracter de barra invertida (\) o estar encerrados por comillas dobles:

db2 "CALL sales_status (25, ?)"

No incluya comillas dobles si está usando el procesador de línea de comandos (CLP) en modo de ingreso interactivo, caracterizado por la ventana de ingreso db2 => .

En este ejemplo, un valor de 25 para el parámetro de entrada quota es pasado al procedimiento SQL, así como un posicionador de signo de interrogación (?) para el parámetro de salida sql_state. El procedimiento retorna el nombre y las ventas totales de cada vendedor cuyas ventas totales excedan la cuota especificada (25). El siguiente es un resultado de ejemplo retornado por este enunciado:

SQL_STATE: 00000
SALES_PERSON            TOTAL_SALES
GOUNOT                       50
LEE                          91
 "SALES_STATUS" RETURN_STATUS: "0"

Creando y usando funciones SQL definidas por usuario

Cree funciones definidas por usuario para extender el conjunto de funciones DB2 integradas. Por ejemplo, cree funciones que evalúen expresiones matemáticas complejas o que manipulen secuencias de caracteres, y luego referencie estas funciones en enunciados SQL como lo haría con cualquier otra función incorporada existente.

Suponga que usted necesita una función que retorne el área de un círculo cuando el radio de ese círculo sea especificado como argumento de la función. Tal función no está disponible como una función incorporada en DB2, pero usted puede crear una función escalar SQL definida por usuario para efectuar esta tarea y referenciar la función donde se soporten funciones escalares dentro de un enunciado SQL.

CREATE FUNCTION ca (r DOUBLE)
  RETURNS DOUBLE
  LANGUAGE SQL
  CONTAINS SQL
  NO EXTERNAL ACTION
  DETERMINISTIC
  RETURN 3.14159 * (r * r);

La cláusula NO EXTERNAL ACTION especifica que la función no necesita realizar ninguna acción que cambie el estado de un objeto que no sea administrado por el gestor de base de datos. La palabra claveDETERMINISTIC especifica que la función siempre retorna el mismo resultado para un valor de argumento dado. Esta información es usada durante optimización de secuencia. Una manera conveniente de ejecutar la función es referenciarla en una consulta. En el siguiente ejemplo, la consulta es ejecutada (arbitrariamente) contra la vista de catálogo SYSIBM.SYSDUMMY1 , que solo tiene una fila:

db2 SELECT ca(96.8) AS area FROM sysibm.sysdummy1

AREA
------------------------
  +2.94374522816000E+004

  1 record(s) selected.

Usted también puede crear una función de tabla definida por usuario, la cual toma cero o más argumentos de entrada y retorna datos como una tabla. Una función de tabla solo puede usarse en la cláusula FROM como un enunciado SQL.

Suponga que usted necesita una función que retorne los nombres y números de empleado de todos los empleados que tengan un trabajo específico, con el título de ese trabajo especificado como un argumento de la función. El siguiente es un ejemplo de una función de tabla que efectúa la tarea:

CREATE FUNCTION jobemployees (job VARCHAR(8))
  RETURNS TABLE (
   empno CHAR(6),
   firstname VARCHAR(12),
   lastname VARCHAR(15)
  )
  LANGUAGE SQL
  READS SQL DATA
  NO EXTERNAL ACTION
  DETERMINISTIC
  RETURN
   SELECT empno, firstnme, lastname
     FROM employee
     WHERE employee.job = jobemployees.job;

La siguiente consulta referencia la nueva función de tabla de la cláusula FROM y pasa el cargo 'CLERK' como el argumento para la función. Un nombre de correlación, introducido por la palabra clave AS, es requerido por la sintaxis:

db2 SELECT * FROM TABLE(jobemployees('CLERK')) AS clerk

EMPNO  FIRSTNAME    LASTNAME
------ ------------ ---------------
000120 SEAN         O'CONNELL
000230 JAMES        JEFFERSON
000240 SALVATORE    MARINO
000250 DANIEL       SMITH
000260 SYBIL        JOHNSON
000270 MARIA        PEREZ

  6 record(s) selected.

Resumen

Este tutorial fue diseñado para introducirle al Structured Query Language (SQL) y a algunas de las formas en que DB2 9 usa SQL para manipular datos en una base de datos relacional. También cubrió los fundamentos de SQL, incluyendo elementos de lenguaje SQL, Data Manipulation Language (DML), procedimientos SQL y funciones definidas por usuario. La Parte 5: Trabajando con objetos DB2, que trata sobre tipos de datos, tablas, vistas e índices según lo definido por DB2, le ayudará a entender cómo crearlos y utilizarlos.

Para seguir esta serie, añada un marcador a esta página, Tutoriales de preparación para el examen 730 DBA DB2 9 DBA.

Recursos

Aprender

Obtener los productos y tecnologías

  • Una versión de prueba del DB2 9 está disponible para descarga.
  • Descargue DB2 Express-C, una versión gratuita del DB2 Express Edition para la comunidad que ofrece los mismos recursos de datos principales que el DB2 Express Edition y proporciona una base sólida para construir e implementar aplicaciones.

Comentarios

developerWorks: Ingrese

Los campos obligatorios están marcados con un asterisco (*).


¿Necesita un IBM ID?
¿Olvidó su IBM ID?


¿Olvidó su Password?
Cambie su Password

Al hacer clic en Enviar, usted está de acuerdo con los términos y condiciones de developerWorks.

 


La primera vez que inicie sesión en developerWorks, se creará un perfil para usted. La información en su propio perfil (nombre, país/región y nombre de la empresa) se muestra al público y acompañará a cualquier contenido que publique, a menos que opte por la opción de ocultar el nombre de su empresa. Puede actualizar su cuenta de IBM en cualquier momento.

Toda la información enviada es segura.

Elija su nombre para mostrar



La primera vez que inicia sesión en developerWorks se crea un perfil para usted, teniendo que elegir un nombre para mostrar en el mismo. Este nombre acompañará el contenido que usted publique en developerWorks.

Por favor elija un nombre de 3 - 31 caracteres. Su nombre de usuario debe ser único en la comunidad developerWorks y debe ser distinto a su dirección de email por motivos de privacidad.

Los campos obligatorios están marcados con un asterisco (*).

(Por favor elija un nombre de 3 - 31 caracteres.)

Al hacer clic en Enviar, usted está de acuerdo con los términos y condiciones de developerWorks.

 


Toda la información enviada es segura.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=90
Zone=Information mgmt
ArticleID=792292
ArticleTitle=Preparación para el examen 730 Fundamentos DB2 9, Parte 4: Trabajando con datos DB2
publish-date=02132012