Note:

Exploración de funciones SQL en Oracle Database 23ai

Introducción

Vamos a aprender 10 características en este tutorial que usted necesita saber y cómo se comparan con sus homólogos existentes. Estas funciones son:

Objetivos

Requisitos

Función 1: Utilizar la cláusula FROM

Una característica interesante introducida en Oracle Database 23ai es la opcionalidad de la cláusula FROM en las sentencias SELECT. Hasta esta versión, se necesitaba la cláusula FROM.

Estas son algunas ventajas potenciales de una función SELECT sin FROM en Oracle Database 23ai.

Función 2: Uso del tipo de dato BOOLEAN

Oracle Database 23ai presenta el nuevo tipo de datos BOOLEAN. Esto aprovecha el uso de verdaderas columnas/variables booleanas, en lugar de simularlas con un valor numérico o Varchar. La capacidad de escribir predicados booleanos simplifica la sintaxis de las sentencias SQL.

  1. Cree una tabla denominada TEST_BOOLEAN.

    CREATE TABLE IF NOT EXISTS TEST_BOOLEAN (name VARCHAR2(100), IS_SLEEPING BOOLEAN);

Table TEST_BOOLEAN created.

Elapsed: 00:00:00.004

  2. Introduzca los datos en la tabla nueva. El valor IS_SLEEPING será NOT NULL definido en FALSE como valor por defecto.

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING boolean NOT NULL);

    Imagen en la que se muestra cómo utilizar el booleano para SQL 

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING default FALSE);

Table TEST_BOOLEAN altered.

Elapsed: 00:00:00.014

    Aquí, puedes ver los diferentes tipos de entrada booleana para Mick, Keith y Ron. Todos son válidos. Para Mick, se utiliza el valor predeterminado FALSE (FALSO): Mick no está dormido.

    1 row inserted.

Elapsed: 00:00:00.006

    Para Keith, utilizamos un valor NO: Keith NO está dormido.

    INSERT INTO TEST_BOOLEAN (name, is_sleeping) values ('Keith','NO');

1 row inserted.

Elapsed: 00:00:00.002

    Y para Ron utilizamos un valor de 1: Ron está durmiendo.

  3. Vea algunos resultados basados en nuestros valores booleanos.

    SELECT * FROM test_boolean;

    Ya no necesitas recordar qué tipo de sistema booleano pones en marcha. Como hemos mostrado, el uso de 0/1, True/False, Yes/No o cualquier otra entrada común devolverá un valor de tabla preciso.

Función 3: Uso de la cláusula DDL IF NOT EXISTS

A partir de Oracle Database 23ai, la nueva cláusula DDL IF NOT EXISTS permite decidir cómo se manejarán los errores DDL. Esto simplifica el script DDL, ya que los posibles errores debidos a la existencia o inexistencia de objetos se pueden ocultar en el script.

  1. Primero, prueba sin usar esta nueva función. Ejecute la siguiente sentencia.

    DROP TABLE DEPT;

    Puesto que no hay ninguna tabla DEPT para borrar, veremos un error: ORA-00942: table or view does not exist.

  2. Sin embargo, en Oracle Database 23ai, podemos utilizar DROP IF EXISTS sin ningún error. Esto nos da tranquilidad evitando errores. Ahora, ejecute la misma sentencia, pero incluya esta nueva función IF EXISTS.

    DROP TABLE IF EXISTS DEPT;

  3. Del mismo modo, podemos utilizar esta función para crear tablas, si aún no existen. Cree esa tabla DEPT.

    CREATE TABLE IF NOT EXISTS DEPT
	(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;

    Imagen que muestra cómo utilizar la cláusula IF [NOT] EXISTS DDL

  4. Utilice esta función para crear más tablas de ejemplo a lo largo de este tutorial. Aquí, crearemos una tabla de empleados denominada EMP.

    CREATE TABLE IF NOT EXISTS EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

Table EMP created.

Elapsed: 00:00:00.006

Función 4: Uso de INSERT para varias filas

Otra característica interesante que garantiza una mejor coexistencia y compatibilidad con otros sistemas de gestión de bases de datos utilizados con frecuencia es la sentencia INSERT con varios valores.

  1. En versiones anteriores de la base de datos Oracle, por ejemplo, la inserción de varias filas requería una sentencia de inserción independiente para cada fila.

    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO DEPT VALUES (50,'HR','LOS ANGELES');
INSERT INTO DEPT VALUES (60,'IT','SAN FRANCISCO');
INSERT INTO DEPT VALUES (70,'MANUFACTURING','DETROIT');

    Oracle Database 23ai ha introducido la nueva sintaxis que permite insertar todas estas filas en una sola sentencia INSERT, por lo que puede insertar varias tuplas en un DML. Ejecute la siguiente sentencia.

    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON'),
(50,'HR','LOS ANGELES'),
(60,'IT','SAN FRANCISCO'),
(70,'MANUFACTURING','DETROIT');

    Imagen en la que se muestra cómo utilizar INSERT de varios valores

    Además de una mejor compatibilidad con otras bases de datos, esta sentencia se puede utilizar para garantizar la coherencia de algunas operaciones de inserción en modo de confirmación automática. Esto podría ser importante, por ejemplo, para que las aplicaciones de Oracle APEX que utilizan este modo funcionen con datos.

  2. Ejecute la siguiente sentencia para utilizar esta función para rellenar los valores de la tabla EMP.

    INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20),
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30),
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30),
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30),
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30),
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30),
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20),
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30),
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20),
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

Característica 5: Utilizar el nuevo constructor VALUE

A partir de la base de datos Oracle 23ai, se ha ampliado el constructor de valores de tabla. Ahora se puede utilizar en sentencias INSERT para crear varias filas en un único comando. También se puede utilizar en sentencias SELECT y en la sintaxis de factorización de vista. En este último caso, simplifica la sintaxis de las sentencias y evita el uso de la tabla DUAL.

La siguiente sentencia parece un tipo de función de tabla sobre la marcha.

SELECT * FROM (VALUES (50,'HR'), (60,'DEV'), (70,'AI')) virt_dept (deptno, dname);

Imagen en la que se muestra cómo utilizar el nuevo constructor de valores de tabla

Función 6: Uso de alias en la cláusula GROUP BY

Oracle Database 23ai introduce la capacidad de utilizar alias en la cláusula GROUP BY de una sentencia SELECT. Esta función simplifica la escritura de consultas con expresiones complejas y garantiza una mejor compatibilidad con otras bases de datos relacionales, como Teradata, MySQL y PostgreSQL.

Por ejemplo:

SELECT to_char(hiredate,'YYYY') "Year", count(*)
FROM emp
GROUP BY to_char(hiredate,'YYYY');

En Oracle Database 23ai, se puede escribir de una forma más sencilla, como se muestra en la siguiente imagen:

Imagen en la que se muestra cómo utilizar alias en la cláusula GROUP BY

Función 7: Uso de la cláusula RETURNING de la sentencia UPDATE y MERGE

Esta cláusula se había aplicado anteriormente como parte de la sentencia EXECUTE IMMEDIATE. Sin embargo, en Oracle Database 23ai podemos encontrarlo como parte de las sentencias DML estáticas tradicionales.

  1. En este caso, permite obtener valores antiguos y nuevos de columnas de una fila procesada. Primero, veamos el salario actual de King.

    SELECT ename, sal FROM emp WHERE ename = 'KING';

  2. Para utilizar variables en LiveSQL, terminaremos las sentencias en PL/SQL. Ejecute este script. En primer lugar, creará las variables para el salario nuevo y antiguo y, a continuación, actualizará el salario de King mediante la cláusula RETURNING para definir nuestras variables. A continuación, observaremos los resultados.

    BEGIN
	DECLARE
		old_salary NUMBER;
		new_salary NUMBER;
	BEGIN
		UPDATE emp
		SET sal = sal + 1000
		WHERE ename = 'KING'
		RETURNING OLD sal, NEW sal INTO old_salary, new_salary;
		DBMS_OUTPUT.PUT_LINE('Old Salary: ' || old_salary);
		DBMS_OUTPUT.PUT_LINE('New Salary: ' || new_salary);
	END;
END;

    Imagen en la que se muestra cómo utilizar la cláusula RETURNING de las sentencias UPDATE y MERGE 

    Old Salary: 6000
New Salary: 7000

    En este ejemplo se ha utilizado una sentencia UPDATE, pero la cláusula RETURNING se puede utilizar de forma similar con sentencias MERGE.

Función 8: Uso de uniones en UPDATE y DELETE

Puede actualizar los datos de la tabla mediante uniones basadas en condiciones de tabla ajena. No es necesario utilizar subselecciones ni la cláusula IN.

  1. Ejecute la siguiente sentencia para ver la información de salario del empleado del departamento de investigación.

    select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';

  2. Para actualizar la información salarial, antes de Oracle Database 23ai necesitamos utilizar una sentencia anidada.

    UPDATE emp e set e.sal=e.sal*2
WHERE e.deptno in
(SELECT d.deptno FROM dept d WHERE e.deptno=d.deptno
and d.dname='RESEARCH');

    Con Oracle Database 23ai, puede utilizarlo de la siguiente manera:

    UPDATE emp e set e.sal=e.sal*2  FROM dept d  WHERE e.deptno=d.deptno  and d.dname='RESEARCH';

    Imagen en la que se muestra cómo utilizar uniones en UPDATE y DELETE

  3. Puede ver que el salario se ha actualizado correctamente.

    select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';

    Imagen en la que se muestra cómo utilizar uniones en UPDATE y DELETE

Función 9: Uso de anotaciones

Las anotaciones son metadatos opcionales para los objetos de base de datos. Una anotación es un par nombre-valor o un nombre por sí mismo. El nombre y el valor opcional son campos de texto de formato libre. Una anotación se representa como un elemento subordinado al objeto de base de datos al que se ha agregado la anotación. Los objetos de esquema soportados incluyen tablas, vistas, vistas materializadas e índices. Con las anotaciones, puede almacenar y recuperar metadatos sobre objetos de base de datos. Puede utilizarlo para personalizar la lógica de negocio, las interfaces de usuario o proporcionar metadatos a los repositorios de metadatos. Se puede agregar con una sentencia CREATE o ALTER en el nivel de tabla o columna.

Con las anotaciones, puede almacenar y recuperar metadatos sobre objetos de base de datos. Puede utilizarlo para personalizar la lógica de negocio, las interfaces de usuario o proporcionar metadatos a los repositorios de metadatos.

  1. Cree una tabla anotada EMP_ANNOTATED_NEW con anotaciones de columna y tabla.

    CREATE TABLE emp_annotated_new
	(empno number annotations(identity, display 'person_identity', details 'person_info'),
	ename varchar2(50),
	salary number annotations (display 'person_salary', col_hidden))
	annotations (display 'employee_table');

    Imagen en la que se muestra cómo utilizar anotaciones, nuevos metadatos para objetos de base de datos

  2. Las vistas del diccionario de datos, como USER_ANNOTATIONS y USER_ANNOTATIONS_USAGE, pueden ayudar a supervisar el uso.

    SELECT object_name, object_type, column_name, annotation_name, annotation_value FROM user_annotations_usage;

    Imagen en la que se muestra cómo utilizar anotaciones, nuevos metadatos para objetos de base de datos

Función 10: Uso de dominios SQL

Un dominio SQL es un objeto de diccionario que pertenece a un esquema y encapsula un juego de propiedades y restricciones opcionales y se crea con una sentencia CREATE DOMAIN. Los dominios proporcionan atributos de restricciones, visualización, ordenación y anotaciones. Después de definir un dominio SQL, puede definir las columnas de tabla que se van a asociar a ese dominio, aplicando de forma explícita las propiedades y restricciones opcionales del dominio a esas columnas.

Los dominios SQL permiten a los usuarios declarar el uso previsto para las columnas. Son objetos de diccionario de datos para que los conocimientos específicos de dominio abstracto se puedan reutilizar fácilmente.

  1. Cree un dominio denominado yearbirth y una tabla denominada person.

    CREATE DOMAIN yearbirth as number(4)
constraint check ((trunc(yearbirth) = yearbirth) and (yearbirth >= 1900))
display (case when yearbirth < 2000 then '19-' ELSE '20-' end)||mod(yearbirth, 100)
order (yearbirth -1900)
annotations (title 'yearformat');

    Imagen en la que se muestra cómo utilizar tipos de objetos ligeros con dominios SQL

    CREATE TABLE person
(id number(5),
name varchar2(50),
salary number,
person_birth number(4) DOMAIN yearbirth
)
annotations (display 'person_table');

    Imagen en la que se muestra cómo utilizar tipos de objetos ligeros" con dominios SQL

    ``` persona descendente;

    Nombre ¿Nulo? Tipo

NÚMERO DE IDENTIFICACIÓN(5) NOMBRE VARCHAR2(50) NÚMERO DE SALARIO PERSON_BIRTH NÚMERO(4) NACIMIENTO DEL AÑO DEL DOMINIO

INSERTAR valores de persona (1,'MARTIN', 3000, 1988);

2. With the new function `DOMAIN_DISPLAY` you can display the property.

SELECT DOMAIN_DISPLAY(person_birth) FROM persona;


   ![Image showing how to use Light weight object types" with SQL Domains ](./images/Picture14.png "Light weight object types with SQL Domains")

3. Domain usage and annotations can be monitored with data dictionary views. Let us view `user_annotations_usage`.

SELECCIONE * DE user_annotations_usage;


   Define reusable domain types (lightweight objects).

CREAR DOMINIO EmailAddress COMO VARCHAR2(100) COMPRUEBE (REGEXP_LIKE(VALOR, '^[^@]+@[^@]+.[^@]+$'));

CREATE TABLE usuarios (user_id NÚMERO, correo electrónico EmailAddress ); ```

Imagen en la que se muestra cómo utilizar tipos de objetos ligeros" con dominios SQL

Limitaciones y restricciones en las funciones de Oracle Database 23ai

Restricciones generales

Acuses de recibo

Más recursos de aprendizaje

Explore otros laboratorios en docs.oracle.com/learn o acceda a más contenido de aprendizaje gratuito en el canal YouTube de Oracle Learning. Además, visite education.oracle.com/learning-explorer para convertirse en un explorador de Oracle Learning.

Para obtener documentación sobre el producto, visite Oracle Help Center.