SUBSTR

Las funciones de Oracle SUBSTR le permite extraer una subcadena de una cadena.

• Si start_position es 0, entonces las delicias de función SUBSTR start_position como 1 (es decir: la primera posición en la cadena).
• Si start_position es un número positivo, entonces la función SUBSTR se inicia desde el principio de la cadena.
• Si start_position es un número negativo, entonces la función SUBSTR se inicia desde el final de la cadena y cuenta hacia atrás.
• Si la longitud es un número negativo, entonces la función SUBSTR devolverá un valor NULL.

SUBSTR ('This is a test', 6, 2)
Result: 'is'

SUBSTR ('This is a test', 6)
Result: 'is a test'

SUBSTR ('OpenMind', 1, 4)
Result: 'Open'

SUBSTR ('OpenMind', -3, 3)
Result: 'ind'

SUBSTR ('OpenMind', -6, 3)
Result: 'enM'

Read More

CONCAT

La función CONCAT Oracle  le permite concatenar dos cadenas juntas.

Ejemplo:

SELECT CONCAT(CONCAT(CONCAT('A', 'B'),'C'),'D')
   FROM dual;

Resultado: 'ABCD'


Este ejemplo podría concatenar los 4 valores juntos y volver 'ABCD'.

Read More

INITCAP

La función de Oracle INITCAP establece el primer carácter de cada palabra en mayúscula y el resto en minúsculas.

SELECT employee_id, INITCAP (last_name), department_id 
   FROM  employees; 

SELECT employee_id, last_name, department_id 
   FROM  employees 
WHERE  INITCAP  (last_name) = 'higgins'; 

Read More

UPPER

La función de UPPER convierte todas las letras en la cadena especificada en mayúsculas. Si hay caracteres en la cadena que no son letras, no se ven afectados por esta función.

SELECT employee_id, UPPER (last_name), department_id 
   FROM  employees; 

SELECT employee_id, last_name, department_id 
   FROM  employees 
WHERE  UPPER (last_name) = 'higgins'; 

Read More

LOWER

La función de Oracle / PLSQL LOWER convierte todas las letras en la cadena especificada a minúsculas. Si hay caracteres en la cadena que no son letras, no se ven afectados por esta función.

SELECT employee_id, LOWER(last_name), department_id 
   FROM  employees; 

SELECT employee_id, last_name, department_id 
   FROM  employees 
WHERE  LOWER(last_name) = 'higgins'; 

Read More

DECODE

La función DECODE descodifica una expresión de forma parecida a la lógica IF-THEN-ELSE que se utiliza en varios lenguajes. La función DECODE descodifica expression tras compararla con cada valor search. Si la expresión es igual que search, se devuelve result.

Si se omite el valor por defecto, se devuelve un valor nulo donde un valor de búsqueda no corresponda a ninguno de los valores del resultado.

SELECT last_name, job_id, salary 

       DECODE(job_id, 'IT_PROG',  1.10*salary, 

                      'ST_CLERK', 1.15*salary, 

                      'SA_REP',   1.20*salary, 

              salary) 

       REVISED_SALARY 

FROM employees; 

Read More

CASE

Las expresiones CASE le permiten utilizar la lógica IF-THEN-ELSE en sentencias SQL sin llamar a procedimientos.

En una expresión CASE simple, Oracle Server busca el primer par WHEN ... THEN en el que expr sea igual a comparison_expr y devuelve return_expr. Si ninguno de los pares WHEN ... THEN cumplen esta condición y si existe una cláusula ELSE, Oracle Server devuelve else_expr. De lo contrario, Oracle Server devuelve un valor nulo. No puede especificar el literal NULL para todas las expresiones return_exprs
y else_expr.

Todas las expresiones ( expr, comparison_expr y return_expr) deben ser del mismo tipo de datos, que puede ser CHAR, VARCHAR2, NCHAR o NVARCHAR2.

SELECT last_name, job_id, salary CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary 

                                                                               WHEN 'ST_CLERK' THEN  1.15*salary 

                                                                               WHEN 'SA_REP'   THEN  1.20*salary 

                                                                               ELSE      salary END     "REVISED_SALARY" 

FROM   employees;

Read More

COALESCE

La función de Oracle / PLSQL COALESCE devuelve la primera expresión no nulo en la lista. Si todas las expresiones dan como resultado nulo, entonces la función COALESCE devolverá un valor nulo.

La ventaja de la función COALESCE sobre la función NVL es que la primera puede tomar varios valores alternativos.


Si la primera expresión no es nula, la función COALESCE devuelve esa expresión; en caso contrario, realiza una fusión (COALESCE) de las expresiones restantes.

Nota:
           La función COALESCE comparará cada valor, uno por uno.

Ejemplo:
               SELECT last_name, COALESCE(manager_id,commission_pct, -1) comm  
                  FROM   employees  
               ORDER BY commission_pct;  


Lo contrario a esta función seria la función NULLIF

Read More

NULLIF

La función NULLIF compara dos expresiones. Si son iguales, la función devuelve un valor nulo. Si no son iguales, la función devuelve la primera expresión. No puede especificar el literal NULL para la primera expresión.

En la sintaxis:

•expr1 es el valor de origen que se compara con expr2

•expr2 es el valor de origen que se compara con expr1 (Si no es igual que expr1, se devuelve expr1.)

En el ejemplo que se muestra en la diapositiva, la longitud del nombre de la tabla EMPLOYEES se compara con el apellido de la tabla EMPLOYEES. Si las longitudes del nombre y el apellido son iguales, se devuelve un valor nulo. Si las longitudes del nombre y el apellido no son iguales, se muestra la longitud del nombre.

Nota: La función NULLIF es lógicamente equivalente a esta expresión CASE. La expresión CASE se analiza en una página posterior:

CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END

SELECT first_name, LENGTH(first_name) "expr1",  

       last_name,  LENGTH(last_name)  "expr2", 

       NULLIF(LENGTH(first_name), LENGTH(last_name)) result 

FROM   employees; 

Lo contrario a esta función seria la función COALESCE.

Read More

NVL2

La función NVL2 amplía la funcionalidad que se encuentra en la función NVL . Le permite sustitutos de un valor cuando se encuentra un valor nulo, así como cuando se encuentra un valor no nulo.

Ejemplo:

SELECT NVL2(employee_id, 'Bien', 'Mal')
   FROM employees;

La sentencia SQL anterior volvería "Mal" si el campo employee_id contenía un valor nulo. De lo contrario, sería devolver el 'Bien'.

Ejemplo:

SELECT employee_id, NVL2(last_name, first_name, last_name2)
   FROM employees;

Esta instrucción SQL devolvería el last_name2 campo si el last_name contenía un valor nulo. De lo contrario, sería devolver el campo first_name.

Read More

NVL

La función NVL le permite sustituir un valor cuando se encuentra un valor nulo.

Ejemplo: 

               SELECT NVL(last_name, 'n/a')

                  FROM employees;

La sentencia SQL anterior volvería "n / a" si el campo supplier_city contenía un valor nulo. De lo contrario, sería devolver el valor supplier_city. Ejemplo:

Ejemplo:

              SELECT employee_id, NVL(employee_desc, employee_name)

                 FROM employees;

Esta instrucción SQL devolvería el employee_name campo si el employee_desc contenía un valor nulo. De lo contrario, sería devolver el employee_desc.

Read More

VERIFY

Utilice el comando VERIFY para alternar la visualización de la variable de sustitución, tanto antes como después de que se reemplace con valores las variables de sustitución:

Uso del Comando VERIFY

Para confirmar los cambios en la sentencia SQL, utilice el comando VERIFY . Al definir SET VERIFY ON se fuerza a que muestre el texto de un comando antes y después de reemplazar con valores las variables de sustitución.

El ejemplo de la diapositiva muestra los valores antiguo y nuevo de la columna EMPLOYEE_ID.

Variables de Sistema

Utiliza diversas variables de sistema que controlan el entorno de trabajo. Una de esas variables es VERIFY. Para obtener una lista completa de todas las variables de sistema, puede emitir el comando SHOW ALL.

SET VERIFY ON 
SELECT employee_id, last_name, salary, department_id
   FROM  employees
WHERE  employee_id = &employee_num;

Resultado;

old   3: WHERE  employee_id = &employee_num 

new   3: WHERE  employee_id = 200 

Read More

DEFINE - UNDEFINE

Utilice el comando DEFINE para crear y asignar un valor a una variable.

Utilice el comando UNDEFINE para eliminar una variable.

Ejemplo:

DEFINE employee_num = 200

SELECT employee_id, last_name, salary, department_id
   FROM   employees
WHERE  employee_id = &employee_num ;

UNDEFINE employee_num

El ejemplo que se muestra crea una variable de sustitución para un número de empleado mediante el comando DEFINE. En tiempo de ejecución, esto muestra el número de empleado, el apellido, el salario y el número de departamento de ese empleado.

Como la variable se crea mediante un comando DEFINE, no se pide al usuario que introduzca un valor para el número de empleado. En vez de eso, se sustituye automáticamente el valor de variable definido en la sentencia SELECT.

La variable de sustitución EMPLOYEE_NUM está presente en la sesión hasta que el usuario anule la definición o salga de la sesión.

Read More

VARIABLES DE SUSTITUCIÓN (&)

Especificación de Valores de Carácter y de Fecha con Variables de Sustitución

En una cláusula WHERE, los valores de fecha y de carácter deben ir entre comillas simples. Se aplica la misma regla a las variables de sustitución.

Ponga la variable entre comillas simples dentro de la propia sentencia SQL.

La diapositiva muestra una consulta para recuperar los apellidos de empleado, los números de departamento y los salarios anuales de todos los empleados basándose en el valor del cargo de la variable de sustitución. 

Ejemplo:

                  SELECT last_name, department_id, salary * 12 

                     FROM  employees 

                  WHERE  job_id = '&job_title' ; 

                   SELECT employee_id, last_name, job_id,&column_name 

                      FROM  employees 

                    WHERE &condition 

                    ORDER BY &order_column; 

Se puede utilizar el ampersan doble (&&) si se desea volver a utilizar el valor de variable sin pedir al usuario que realice una acción cada vez.

Ejemplo:

              SELECT  employee_id, last_name, job_id, &&column_name 

                 FROM   employees 

         ORDER BY &column_name;

Read More

ORDER BY

La clausula ORDER BY se utiliza para ordenar los registros en el conjunto de resultados. La cláusula ORDER BY sólo se puede utilizar en los comando SELECT.

Si el ASC o DESC modificador no está prevista en la cláusula ORDER BY, los resultados se ordenarán por la expresión por defecto de manera desendente.

   EJEMPLO:
                         SELECT *
                             FROM EMPLOYEES
                          WHERE DEPARTMENT_ID = 10
                          ORDER BY EMPLOYEE_ID ASC

                          SELECT *
                             FROM EMPLOYEES
                          WHERE DEPARTMENT_ID = 10
                          ORDER BY EMPLOYEE_ID DESC

De igual manera, podemos ordenar por numero de colunmas.

  EJEMPLO:
                        SELECT EMPLOYEE_ID, LAST_NAME
                           FROM EMPLOYEE
                        WHERE DEPARTMENT_ID = 10
                        ORDER BY 1;

También podemos usar ambas al mismo tiempo.

 EJEMPLO:
                        SELECT EMPLOYEE_ID, LAST_NAME
                           FROM EMPLOYEE
                        WHERE DEPARTMENT_ID = 10
                        ORDER BY EMPLOYEE_ID ASC, LAST_NAME DESC;

Con este ultimo tendremos como resultado los datos de la consulta con EMPLOYEE_ID 
de modo ascendente y LAST_NAME  de manera descendente.
  

Read More

OPERADORES SQL

Listado de operadores para manipulación de SQL:

SIGNOS	DESCRIPCION
-	MENOS
+	MAS
*	MULTIPLICACION
=	IGUAL QUE	COMPARATIVOS
>=	MAYOR O IGUAL
<=	MENOR O IGUAL
<>	DIFERENTE
!=	DIFERENTE
^=	DIFERENTE
||	CONCATENACION
AND	Y
OR	O
NOT	NO
IN	DENTRO
LIKE	COMO
IS NULL	ES NULO

Read More