Oracle base de datos Cheatsheet

• Base de datos de datos de usuario: Sistema de gestión de bases de datos de empresas "Clase de inscripción" Oracle Database es un sistema de gestión de bases de datos multimodelo producido y comercializado por Oracle Corporation. Es uno de los sistemas de bases de datos institucionales más utilizados, conocidos por su fiabilidad, escalabilidad y conjunto de características integrales. ▪/p] ■/div titulada

########################################################################################################################################################################################################################################################## Copiar todos los comandos

########################################################################################################################################################################################################################################################## Generar PDF seleccionado/button

■/div titulada ■/div titulada

Cuadro de contenidos

• Instalación
• Basic Commands
• Operaciones de base de datos
• Schema y Gestión de Usuarios
• Table Operations
• Tipos de datos
• CRUD Operations
• Operaciones de preguntas
• Indexes
• Vers
• Procedimientos archivados
• Funciones
• Packages
• Triggers
• Transacciones
• PL/SQL
• Backup and Recovery
• Performance Tuning
• Monitoreo
• Seguridad
• Las mejores prácticas

Instalación

Instalación de base de datos de Oracle (Linux)

# Download Oracle Database from Oracle website
# https://www.oracle.com/database/technologies/oracle-database-software-downloads.html

# Install prerequisites (Oracle Linux/RHEL)
sudo yum install -y oracle-database-preinstall-19c

# Extract installation files
unzip LINUX.X64_193000_db_home.zip -d $ORACLE_HOME

# Run installer
cd $ORACLE_HOME
./runInstaller

# Silent installation example
./runInstaller -silent \
  -responseFile /path/to/db_install.rsp \
  oracle.install.option=INSTALL_DB_SWONLY \
  ORACLE_HOSTNAME=hostname \
  UNIX_GROUP_NAME=oinstall \
  INVENTORY_LOCATION=/u01/app/oraInventory \
  ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 \
  ORACLE_BASE=/u01/app/oracle \
  oracle.install.db.InstallEdition=EE \
  oracle.install.db.OSDBA_GROUP=dba \
  oracle.install.db.OSOPER_GROUP=oper \
  oracle.install.db.OSBACKUPDBA_GROUP=backupdba \
  oracle.install.db.OSDGDBA_GROUP=dgdba \
  oracle.install.db.OSKMDBA_GROUP=kmdba \
  oracle.install.db.OSRACDBA_GROUP=racdba \
  DECLINE_SECURITY_UPDATES=true

Oracle Database Express Edition (XE)

# Download Oracle XE
wget https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol7.x86_64.rpm

# Install Oracle XE
sudo yum localinstall oracle-database-xe-21c-1.0-1.ol7.x86_64.rpm

# Configure Oracle XE
sudo /etc/init.d/oracle-xe-21c configure

# Set environment variables
echo 'export ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE' >> ~/.bashrc
echo 'export ORACLE_SID=XE' >> ~/.bashrc
echo 'export PATH=$ORACLE_HOME/bin:$PATH' >> ~/.bashrc
source ~/.bashrc

# Connect to Oracle
sqlplus sys as sysdba

Docker Instalación

# Pull Oracle Database image
docker pull container-registry.oracle.com/database/express:21.3.0-xe

# Run Oracle XE container
docker run --name oracle-xe \
  -p 1521:1521 \
  -p 5500:5500 \
  -e ORACLE_PWD=password \
  -e ORACLE_CHARACTERSET=AL32UTF8 \
  -v oracle-data:/opt/oracle/oradata \
  -d container-registry.oracle.com/database/express:21.3.0-xe

# Connect to Oracle in container
docker exec -it oracle-xe sqlplus sys/password@localhost:1521/XE as sysdba

Comandos básicos

Conexión a Oracle

# Connect as SYSDBA
sqlplus sys as sysdba

# Connect with username and password
sqlplus username/password

# Connect to specific database
sqlplus username/password@hostname:1521/service_name

# Connect using TNS alias
sqlplus username/password@tns_alias

# Connect using Easy Connect
sqlplus username/password@//hostname:1521/service_name

# Silent connection
sqlplus -s username/password

SQL*Plus Commands

-- Show user
SHOW USER;

-- Show current database
SELECT name FROM v$database;

-- Show instance
SELECT instance_name FROM v$instance;

-- Set display options
SET PAGESIZE 50;
SET LINESIZE 120;
SET TIMING ON;
SET AUTOTRACE ON;

-- Describe table
DESC table_name;
DESCRIBE table_name;

-- Show errors
SHOW ERRORS;

-- Spool output to file
SPOOL output.txt;
-- Your SQL commands here
SPOOL OFF;

-- Execute script
@script.sql;
START script.sql;

-- Exit SQL*Plus
EXIT;
QUIT;

Información del sistema

-- Oracle version
SELECT * FROM v$version;

-- Database information
SELECT name, created, log_mode FROM v$database;

-- Instance information
SELECT instance_name, host_name, version, startup_time 
FROM v$instance;

-- Show parameters
SHOW PARAMETER db_name;
SHOW PARAMETER memory;

-- Show SGA information
SELECT * FROM v$sga;

-- Show tablespaces
SELECT tablespace_name, status FROM dba_tablespaces;

-- Show datafiles
SELECT file_name, tablespace_name, bytes/1024/1024 AS mb 
FROM dba_data_files;

Operaciones de base de datos

Creación de bases de datos

-- Create database (using DBCA is recommended)
CREATE DATABASE mydb
  USER SYS IDENTIFIED BY sys_password
  USER SYSTEM IDENTIFIED BY system_password
  LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mydb/redo01.log') SIZE 100M,
          GROUP 2 ('/u01/app/oracle/oradata/mydb/redo02.log') SIZE 100M,
          GROUP 3 ('/u01/app/oracle/oradata/mydb/redo03.log') SIZE 100M
  MAXLOGFILES 5
  MAXLOGMEMBERS 5
  MAXLOGHISTORY 1
  MAXDATAFILES 100
  MAXINSTANCES 1
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  DATAFILE '/u01/app/oracle/oradata/mydb/system01.dbf' SIZE 500M REUSE
  EXTENT MANAGEMENT LOCAL
  SYSAUX DATAFILE '/u01/app/oracle/oradata/mydb/sysaux01.dbf' SIZE 500M REUSE
  DEFAULT TABLESPACE users
    DATAFILE '/u01/app/oracle/oradata/mydb/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON
  DEFAULT TEMPORARY TABLESPACE temp
    TEMPFILE '/u01/app/oracle/oradata/mydb/temp01.dbf' SIZE 100M REUSE
  UNDO TABLESPACE undotbs1
    DATAFILE '/u01/app/oracle/oradata/mydb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON;

Gestión del espacio

-- Create tablespace
CREATE TABLESPACE app_data
  DATAFILE '/u01/app/oracle/oradata/mydb/app_data01.dbf' 
  SIZE 100M 
  AUTOEXTEND ON 
  NEXT 10M 
  MAXSIZE UNLIMITED;

-- Create temporary tablespace
CREATE TEMPORARY TABLESPACE temp_large
  TEMPFILE '/u01/app/oracle/oradata/mydb/temp_large01.dbf' 
  SIZE 100M 
  AUTOEXTEND ON;

-- Add datafile to tablespace
ALTER TABLESPACE app_data 
ADD DATAFILE '/u01/app/oracle/oradata/mydb/app_data02.dbf' 
SIZE 100M AUTOEXTEND ON;

-- Resize datafile
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/mydb/app_data01.dbf' 
RESIZE 200M;

-- Take tablespace offline/online
ALTER TABLESPACE app_data OFFLINE;
ALTER TABLESPACE app_data ONLINE;

-- Drop tablespace
DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES;

-- Show tablespace usage
SELECT 
    tablespace_name,
    ROUND(bytes/1024/1024, 2) AS total_mb,
    ROUND(maxbytes/1024/1024, 2) AS max_mb
FROM dba_data_files
ORDER BY tablespace_name;

Schema y Gestión de Usuarios

Crear usuarios

-- Create user
CREATE USER app_user IDENTIFIED BY password
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON app_data;

-- Create user with profile
CREATE USER app_user IDENTIFIED BY password
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE temp
  PROFILE default_profile
  QUOTA 100M ON app_data;

-- Alter user
ALTER USER app_user IDENTIFIED BY new_password;
ALTER USER app_user QUOTA UNLIMITED ON app_data;
ALTER USER app_user DEFAULT TABLESPACE new_tablespace;

-- Lock/unlock user
ALTER USER app_user ACCOUNT LOCK;
ALTER USER app_user ACCOUNT UNLOCK;

-- Drop user
DROP USER app_user CASCADE;

Conceder privilegios

-- System privileges
GRANT CREATE SESSION TO app_user;
GRANT CREATE TABLE TO app_user;
GRANT CREATE VIEW TO app_user;
GRANT CREATE PROCEDURE TO app_user;
GRANT CREATE SEQUENCE TO app_user;

-- Object privileges
GRANT SELECT ON hr.employees TO app_user;
GRANT INSERT, UPDATE, DELETE ON hr.employees TO app_user;
GRANT ALL ON hr.employees TO app_user;

-- Grant with admin option
GRANT CREATE USER TO app_user WITH ADMIN OPTION;

-- Grant with grant option
GRANT SELECT ON hr.employees TO app_user WITH GRANT OPTION;

-- Revoke privileges
REVOKE CREATE TABLE FROM app_user;
REVOKE SELECT ON hr.employees FROM app_user;

Función de gestión

-- Create role
CREATE ROLE app_role;

-- Grant privileges to role
GRANT CREATE SESSION, CREATE TABLE TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_role;

-- Grant role to user
GRANT app_role TO app_user;

-- Set default role
ALTER USER app_user DEFAULT ROLE app_role;

-- Enable/disable role
SET ROLE app_role;
SET ROLE ALL;
SET ROLE NONE;

-- Drop role
DROP ROLE app_role;

-- Show user privileges
SELECT * FROM user_sys_privs;
SELECT * FROM user_tab_privs;
SELECT * FROM user_role_privs;

-- Show role privileges
SELECT * FROM role_sys_privs WHERE role = 'APP_ROLE';
SELECT * FROM role_tab_privs WHERE role = 'APP_ROLE';

Operaciones

Crear tablas

-- Basic table creation
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20) NOT NULL,
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(25) UNIQUE,
    phone_number VARCHAR2(20),
    hire_date DATE DEFAULT SYSDATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER(6),
    department_id NUMBER(4),
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with constraints
CREATE TABLE departments (
    department_id NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(30) NOT NULL,
    manager_id NUMBER(6),
    location_id NUMBER(4),
    CONSTRAINT dept_name_unique UNIQUE (department_name),
    CONSTRAINT dept_mgr_fk FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

-- Table with check constraints
CREATE TABLE products (
    product_id NUMBER(6) PRIMARY KEY,
    product_name VARCHAR2(50) NOT NULL,
    price NUMBER(8,2) CHECK (price > 0),
    category VARCHAR2(20) CHECK (category IN ('Electronics', 'Clothing', 'Books')),
    stock_quantity NUMBER(6) DEFAULT 0 CHECK (stock_quantity >= 0)
);

-- Partitioned table
CREATE TABLE sales (
    sale_id NUMBER(10) PRIMARY KEY,
    sale_date DATE,
    customer_id NUMBER(6),
    amount NUMBER(10,2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2023_q1 VALUES LESS THAN (DATE '2023-04-01'),
    PARTITION sales_2023_q2 VALUES LESS THAN (DATE '2023-07-01'),
    PARTITION sales_2023_q3 VALUES LESS THAN (DATE '2023-10-01'),
    PARTITION sales_2023_q4 VALUES LESS THAN (DATE '2024-01-01')
);

-- Index-organized table
CREATE TABLE lookup_data (
    code VARCHAR2(10) PRIMARY KEY,
    description VARCHAR2(100),
    value NUMBER
) ORGANIZATION INDEX;

Cuadros de modificación

-- Add column
ALTER TABLE employees ADD (middle_name VARCHAR2(20));

-- Modify column
ALTER TABLE employees MODIFY (salary NUMBER(10,2));

-- Drop column
ALTER TABLE employees DROP COLUMN middle_name;

-- Rename column
ALTER TABLE employees RENAME COLUMN phone_number TO phone;

-- Add constraint
ALTER TABLE employees ADD CONSTRAINT emp_salary_check CHECK (salary > 0);

-- Drop constraint
ALTER TABLE employees DROP CONSTRAINT emp_salary_check;

-- Enable/disable constraint
ALTER TABLE employees DISABLE CONSTRAINT emp_salary_check;
ALTER TABLE employees ENABLE CONSTRAINT emp_salary_check;

-- Rename table
ALTER TABLE employees RENAME TO staff;

-- Move table to different tablespace
ALTER TABLE employees MOVE TABLESPACE new_tablespace;

-- Drop table
DROP TABLE employees;
DROP TABLE employees CASCADE CONSTRAINTS;

Cuadro de información

-- Describe table
DESC employees;

-- Show table structure
SELECT column_name, data_type, nullable, data_default
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY column_id;

-- Show constraints
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

-- Show indexes
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

-- Show table statistics
SELECT table_name, num_rows, blocks, avg_row_len, last_analyzed
FROM user_tables
WHERE table_name = 'EMPLOYEES';

Tipos de datos

Tipos de Datos Numéricos

-- Number types
NUMBER              -- Variable precision and scale
NUMBER(p)           -- Precision p, scale 0
NUMBER(p,s)         -- Precision p, scale s
INTEGER             -- Synonym for NUMBER(38)
INT                 -- Synonym for NUMBER(38)
SMALLINT            -- Synonym for NUMBER(38)
DECIMAL(p,s)        -- Synonym for NUMBER(p,s)
NUMERIC(p,s)        -- Synonym for NUMBER(p,s)
FLOAT               -- Synonym for NUMBER
REAL                -- Synonym for NUMBER
DOUBLE PRECISION    -- Synonym for NUMBER

-- Binary floating-point types
BINARY_FLOAT        -- 32-bit floating point
BINARY_DOUBLE       -- 64-bit floating point

-- Examples
CREATE TABLE numeric_examples (
    id NUMBER(10) PRIMARY KEY,
    price NUMBER(8,2),
    quantity INTEGER,
    percentage NUMBER(5,2),
    scientific_value BINARY_DOUBLE
);

Cara Tipos de datos

-- Character types
CHAR(size)          -- Fixed-length character string
VARCHAR2(size)      -- Variable-length character string
NCHAR(size)         -- Fixed-length Unicode character string
NVARCHAR2(size)     -- Variable-length Unicode character string
CLOB                -- Character Large Object (up to 4GB)
NCLOB               -- Unicode Character Large Object

-- Examples
CREATE TABLE character_examples (
    id NUMBER PRIMARY KEY,
    code CHAR(5),
    name VARCHAR2(100),
    description CLOB,
    unicode_text NVARCHAR2(200)
);

Tipos de datos de fecha y hora

-- Date and time types
DATE                -- Date and time (no fractional seconds)
TIMESTAMP           -- Date and time with fractional seconds
TIMESTAMP(precision) -- Date and time with specified precision
TIMESTAMP WITH TIME ZONE        -- Timestamp with timezone
TIMESTAMP WITH LOCAL TIME ZONE  -- Timestamp normalized to database timezone
INTERVAL YEAR TO MONTH          -- Year-month interval
INTERVAL DAY TO SECOND          -- Day-second interval

-- Examples
CREATE TABLE datetime_examples (
    id NUMBER PRIMARY KEY,
    birth_date DATE,
    created_at TIMESTAMP,
    precise_time TIMESTAMP(6),
    global_time TIMESTAMP WITH TIME ZONE,
    local_time TIMESTAMP WITH LOCAL TIME ZONE,
    age_interval INTERVAL YEAR TO MONTH,
    duration INTERVAL DAY TO SECOND
);

Otros tipos de datos

-- Binary data types
RAW(size)           -- Variable-length binary data
LONG RAW            -- Variable-length binary data (deprecated)
BLOB                -- Binary Large Object

-- Row identifier
ROWID               -- Physical row identifier
UROWID              -- Universal row identifier

-- XML data type
XMLType             -- XML documents

-- Examples
CREATE TABLE other_examples (
    id NUMBER PRIMARY KEY,
    binary_data RAW(2000),
    large_binary BLOB,
    xml_document XMLType,
    row_identifier ROWID
);

CRUD Operaciones

Insertar operaciones

-- Insert single row
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES (1001, 'John', 'Doe', 'john.doe@company.com', SYSDATE);

-- Insert multiple rows
INSERT ALL
  INTO employees VALUES (1002, 'Jane', 'Smith', 'jane.smith@company.com', SYSDATE)
  INTO employees VALUES (1003, 'Bob', 'Johnson', 'bob.johnson@company.com', SYSDATE)
SELECT * FROM dual;

-- Insert from select
INSERT INTO employee_backup
SELECT * FROM employees WHERE hire_date < DATE '2020-01-01';

-- Insert with returning clause
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES (1004, 'Alice', 'Brown', 'alice.brown@company.com', SYSDATE)
RETURNING employee_id INTO :new_id;

-- Conditional insert
INSERT ALL
  WHEN salary > 10000 THEN
    INTO high_earners VALUES (employee_id, first_name, last_name, salary)
  WHEN salary BETWEEN 5000 AND 10000 THEN
    INTO mid_earners VALUES (employee_id, first_name, last_name, salary)
  ELSE
    INTO low_earners VALUES (employee_id, first_name, last_name, salary)
SELECT employee_id, first_name, last_name, salary FROM employees;

Seleccionar operaciones

-- Basic select
SELECT * FROM employees;

-- Select with specific columns
SELECT employee_id, first_name, last_name, salary FROM employees;

-- Select with alias
SELECT employee_id AS id, first_name || ' ' || last_name AS full_name
FROM employees;

-- Select with WHERE clause
SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;
SELECT * FROM employees WHERE first_name LIKE 'J%';
SELECT * FROM employees WHERE hire_date > DATE '2020-01-01';

-- Select with ORDER BY
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY department_id, salary DESC;

-- Select with GROUP BY
SELECT department_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id;

-- Select with HAVING
SELECT department_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

-- Select with ROWNUM (Oracle-specific pagination)
SELECT * FROM (
  SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 10;

-- Select with OFFSET/FETCH (Oracle 12c+)
SELECT * FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Operaciones de actualización

-- Update single row
UPDATE employees 
SET salary = 6000 
WHERE employee_id = 1001;

-- Update multiple columns
UPDATE employees 
SET salary = salary * 1.1, 
    commission_pct = 0.05 
WHERE department_id = 10;

-- Update with subquery
UPDATE employees 
SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id)
WHERE salary IS NULL;

-- Update with join (using merge)
MERGE INTO employees e
USING (SELECT employee_id, new_salary FROM salary_updates) s
ON (e.employee_id = s.employee_id)
WHEN MATCHED THEN
  UPDATE SET salary = s.new_salary;

-- Update with returning clause
UPDATE employees 
SET salary = salary * 1.1 
WHERE department_id = 10
RETURNING employee_id, salary INTO :emp_id, :new_salary;

Eliminar las operaciones

-- Delete specific rows
DELETE FROM employees WHERE employee_id = 1001;

-- Delete with conditions
DELETE FROM employees WHERE hire_date < DATE '2010-01-01';

-- Delete with subquery
DELETE FROM employees 
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

-- Delete all rows
DELETE FROM employees;

-- Truncate table (faster than delete)
TRUNCATE TABLE employees;

-- Delete with returning clause
DELETE FROM employees 
WHERE department_id = 10
RETURNING employee_id, first_name, last_name INTO :emp_id, :fname, :lname;

Operaciones de consulta

Joins

-- Inner join
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- Left outer join
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

-- Right outer join
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;

-- Full outer join
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

-- Cross join
SELECT e.first_name, d.department_name
FROM employees e
CROSS JOIN departments d;

-- Self join
SELECT e1.first_name || ' ' || e1.last_name AS employee,
       e2.first_name || ' ' || e2.last_name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

Subquerías

-- Subquery in WHERE clause
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery with IN
SELECT * FROM employees 
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

-- Subquery with EXISTS
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id);

-- Correlated subquery
SELECT * FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id);

-- Subquery in SELECT clause
SELECT employee_id, first_name, last_name,
       (SELECT department_name FROM departments d WHERE d.department_id = e.department_id) AS dept_name
FROM employees e;

-- Subquery in FROM clause
SELECT dept_name, avg_salary
FROM (SELECT d.department_name AS dept_name, AVG(e.salary) AS avg_salary
      FROM employees e JOIN departments d ON e.department_id = d.department_id
      GROUP BY d.department_name);

Funciones analíticas

-- ROW_NUMBER
SELECT employee_id, first_name, last_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

-- RANK and DENSE_RANK
SELECT employee_id, first_name, last_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- PARTITION BY
SELECT employee_id, first_name, last_name, department_id, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

-- LAG and LEAD
SELECT employee_id, hire_date, salary,
       LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary,
       LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

-- FIRST_VALUE and LAST_VALUE
SELECT employee_id, department_id, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS min_salary,
       LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary 
                                RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_salary
FROM employees;

-- Running totals
SELECT employee_id, hire_date, salary,
       SUM(salary) OVER (ORDER BY hire_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM employees;

-- Moving averages
SELECT employee_id, hire_date, salary,
       AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

Consultas jerárquicas

-- Connect by (Oracle-specific)
SELECT LEVEL, employee_id, first_name, last_name, manager_id,
       LPAD(' ', (LEVEL-1)*2) || first_name || ' ' || last_name AS hierarchy
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

-- With SYS_CONNECT_BY_PATH
SELECT employee_id, first_name, last_name,
       SYS_CONNECT_BY_PATH(first_name || ' ' || last_name, '/') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- Recursive CTE (Oracle 11g R2+)
WITH emp_hierarchy (employee_id, first_name, last_name, manager_id, level_num) AS (
  -- Anchor member
  SELECT employee_id, first_name, last_name, manager_id, 1
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive member
  SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, eh.level_num + 1
  FROM employees e
  JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM emp_hierarchy;

Índices

Crear índices

-- Simple index
CREATE INDEX idx_emp_last_name ON employees(last_name);

-- Composite index
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);

-- Unique index
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- Function-based index
CREATE INDEX idx_emp_upper_last_name ON employees(UPPER(last_name));

-- Partial index (with WHERE clause)
CREATE INDEX idx_emp_active ON employees(employee_id) WHERE status = 'ACTIVE';

-- Bitmap index (for low cardinality columns)
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);

-- Reverse key index
CREATE INDEX idx_emp_id_reverse ON employees(employee_id) REVERSE;

-- Compressed index
CREATE INDEX idx_emp_dept_job COMPRESS ON employees(department_id, job_id);

-- Invisible index (Oracle 11g+)
CREATE INDEX idx_emp_hire_date ON employees(hire_date) INVISIBLE;

Gestión de índices

-- Show indexes
SELECT index_name, table_name, uniqueness, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

-- Show index columns
SELECT index_name, column_name, column_position, descend
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

-- Rebuild index
ALTER INDEX idx_emp_last_name REBUILD;

-- Rebuild index online
ALTER INDEX idx_emp_last_name REBUILD ONLINE;

-- Make index visible/invisible
ALTER INDEX idx_emp_hire_date VISIBLE;
ALTER INDEX idx_emp_hire_date INVISIBLE;

-- Drop index
DROP INDEX idx_emp_last_name;

-- Analyze index
ANALYZE INDEX idx_emp_last_name VALIDATE STRUCTURE;

-- Monitor index usage
ALTER INDEX idx_emp_last_name MONITORING USAGE;
SELECT * FROM v$object_usage WHERE index_name = 'IDX_EMP_LAST_NAME';

Vistas

Creando vistas

-- Simple view
CREATE VIEW emp_view AS
SELECT employee_id, first_name, last_name, email, department_id
FROM employees
WHERE status = 'ACTIVE';

-- Complex view with joins
CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, e.salary,
       d.department_name, d.location_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- View with check option
CREATE VIEW high_salary_emp AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 10000
WITH CHECK OPTION;

-- Read-only view
CREATE VIEW emp_summary AS
SELECT department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
WITH READ ONLY;

-- Materialized view
CREATE MATERIALIZED VIEW emp_dept_mv
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT d.department_name, COUNT(*) AS emp_count, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

Gestión de opiniones

-- Show views
SELECT view_name, text FROM user_views;

-- Show view definition
SELECT text FROM user_views WHERE view_name = 'EMP_VIEW';

-- Update view
CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id, first_name, last_name, email, department_id, salary
FROM employees
WHERE status = 'ACTIVE';

-- Drop view
DROP VIEW emp_view;

-- Refresh materialized view
EXEC DBMS_MVIEW.REFRESH('EMP_DEPT_MV');

-- Drop materialized view
DROP MATERIALIZED VIEW emp_dept_mv;

Procedimientos almacenados

Crear procedimientos

-- Simple procedure
CREATE OR REPLACE PROCEDURE get_employee_count
IS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM employees;
  DBMS_OUTPUT.PUT_LINE('Total employees: ' || v_count);
END;
/

-- Procedure with parameters
CREATE OR REPLACE PROCEDURE get_emp_by_dept(
  p_dept_id IN NUMBER,
  p_count OUT NUMBER
)
IS
BEGIN
  SELECT COUNT(*) INTO p_count 
  FROM employees 
  WHERE department_id = p_dept_id;
END;
/

-- Procedure with IN OUT parameter
CREATE OR REPLACE PROCEDURE update_salary(
  p_emp_id IN NUMBER,
  p_salary IN OUT NUMBER
)
IS
  v_current_salary NUMBER;
BEGIN
  SELECT salary INTO v_current_salary 
  FROM employees 
  WHERE employee_id = p_emp_id;

  UPDATE employees 
  SET salary = p_salary 
  WHERE employee_id = p_emp_id;

  p_salary := v_current_salary; -- Return old salary
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

-- Procedure with cursor
CREATE OR REPLACE PROCEDURE print_high_earners(p_min_salary IN NUMBER)
IS
  CURSOR c_employees IS
    SELECT first_name, last_name, salary
    FROM employees
    WHERE salary >= p_min_salary
    ORDER BY salary DESC;
BEGIN
  FOR emp_rec IN c_employees LOOP
    DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name || 
                        ': $' || emp_rec.salary);
  END LOOP;
END;
/

Procedimientos de convocatoria

-- Call simple procedure
EXEC get_employee_count;

-- Call procedure with parameters
DECLARE
  v_count NUMBER;
BEGIN
  get_emp_by_dept(10, v_count);
  DBMS_OUTPUT.PUT_LINE('Department 10 has ' || v_count || ' employees');
END;
/

-- Call procedure with IN OUT parameter
DECLARE
  v_salary NUMBER := 7000;
BEGIN
  update_salary(101, v_salary);
  DBMS_OUTPUT.PUT_LINE('Previous salary was: ' || v_salary);
END;
/

Funciones

Creación de funciones

-- Simple function
CREATE OR REPLACE FUNCTION get_full_name(
  p_emp_id IN NUMBER
) RETURN VARCHAR2
IS
  v_full_name VARCHAR2(100);
BEGIN
  SELECT first_name || ' ' || last_name 
  INTO v_full_name
  FROM employees
  WHERE employee_id = p_emp_id;

  RETURN v_full_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

-- Function with complex logic
CREATE OR REPLACE FUNCTION calculate_bonus(
  p_emp_id IN NUMBER,
  p_bonus_pct IN NUMBER DEFAULT 0.1
) RETURN NUMBER
IS
  v_salary NUMBER;
  v_performance_rating NUMBER;
  v_bonus NUMBER;
BEGIN
  SELECT salary INTO v_salary
  FROM employees
  WHERE employee_id = p_emp_id;

  -- Get performance rating (assume this exists)
  SELECT rating INTO v_performance_rating
  FROM performance_reviews
  WHERE employee_id = p_emp_id
  AND review_year = EXTRACT(YEAR FROM SYSDATE);

  -- Calculate bonus based on performance
  CASE v_performance_rating
    WHEN 5 THEN v_bonus := v_salary * (p_bonus_pct * 2);
    WHEN 4 THEN v_bonus := v_salary * (p_bonus_pct * 1.5);
    WHEN 3 THEN v_bonus := v_salary * p_bonus_pct;
    ELSE v_bonus := 0;
  END CASE;

  RETURN v_bonus;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 0;
END;
/

-- Pipelined table function
CREATE OR REPLACE TYPE emp_obj AS OBJECT (
  employee_id NUMBER,
  full_name VARCHAR2(100),
  salary NUMBER
);
/

CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_obj;
/

CREATE OR REPLACE FUNCTION get_employees_by_dept(p_dept_id NUMBER)
RETURN emp_tab PIPELINED
IS
  v_emp emp_obj;
BEGIN
  FOR rec IN (SELECT employee_id, first_name || ' ' || last_name AS full_name, salary
              FROM employees
              WHERE department_id = p_dept_id) LOOP
    v_emp := emp_obj(rec.employee_id, rec.full_name, rec.salary);
    PIPE ROW(v_emp);
  END LOOP;
  RETURN;
END;
/

Utilizando Funciones

-- Use function in SELECT
SELECT employee_id, get_full_name(employee_id) AS full_name
FROM employees;

-- Use function in WHERE clause
SELECT * FROM employees
WHERE calculate_bonus(employee_id) > 5000;

-- Use pipelined function
SELECT * FROM TABLE(get_employees_by_dept(10));

Paquetes

Creación de paquetes

-- Package specification
CREATE OR REPLACE PACKAGE emp_mgmt AS
  -- Public constants
  c_max_salary CONSTANT NUMBER := 50000;

  -- Public types
  TYPE emp_cursor_type IS REF CURSOR RETURN employees%ROWTYPE;

  -- Public procedures and functions
  PROCEDURE hire_employee(
    p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2,
    p_email IN VARCHAR2,
    p_job_id IN VARCHAR2,
    p_dept_id IN NUMBER,
    p_emp_id OUT NUMBER
  );

  FUNCTION get_employee_salary(p_emp_id IN NUMBER) RETURN NUMBER;

  PROCEDURE get_dept_employees(
    p_dept_id IN NUMBER,
    p_cursor OUT emp_cursor_type
  );

END emp_mgmt;
/

-- Package body
CREATE OR REPLACE PACKAGE BODY emp_mgmt AS

  -- Private variables
  g_package_name CONSTANT VARCHAR2(30) := 'EMP_MGMT';

  -- Private procedure
  PROCEDURE log_action(p_action IN VARCHAR2) IS
  BEGIN
    INSERT INTO audit_log (package_name, action, timestamp)
    VALUES (g_package_name, p_action, SYSDATE);
  END log_action;

  -- Public procedure implementation
  PROCEDURE hire_employee(
    p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2,
    p_email IN VARCHAR2,
    p_job_id IN VARCHAR2,
    p_dept_id IN NUMBER,
    p_emp_id OUT NUMBER
  ) IS
  BEGIN
    SELECT employees_seq.NEXTVAL INTO p_emp_id FROM dual;

    INSERT INTO employees (
      employee_id, first_name, last_name, email, 
      job_id, department_id, hire_date
    ) VALUES (
      p_emp_id, p_first_name, p_last_name, p_email,
      p_job_id, p_dept_id, SYSDATE
    );

    log_action('HIRE_EMPLOYEE: ' || p_emp_id);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RAISE;
  END hire_employee;

  -- Public function implementation
  FUNCTION get_employee_salary(p_emp_id IN NUMBER) RETURN NUMBER IS
    v_salary NUMBER;
  BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_emp_id;

    RETURN v_salary;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN NULL;
  END get_employee_salary;

  -- Public procedure with cursor
  PROCEDURE get_dept_employees(
    p_dept_id IN NUMBER,
    p_cursor OUT emp_cursor_type
  ) IS
  BEGIN
    OPEN p_cursor FOR
      SELECT * FROM employees
      WHERE department_id = p_dept_id
      ORDER BY last_name;
  END get_dept_employees;

END emp_mgmt;
/

Uso de paquetes

-- Call package procedure
DECLARE
  v_emp_id NUMBER;
BEGIN
  emp_mgmt.hire_employee(
    p_first_name => 'John',
    p_last_name => 'Smith',
    p_email => 'john.smith@company.com',
    p_job_id => 'IT_PROG',
    p_dept_id => 60,
    p_emp_id => v_emp_id
  );
  DBMS_OUTPUT.PUT_LINE('New employee ID: ' || v_emp_id);
END;
/

-- Call package function
SELECT employee_id, emp_mgmt.get_employee_salary(employee_id) AS salary
FROM employees;

-- Use package cursor
DECLARE
  v_cursor emp_mgmt.emp_cursor_type;
  v_emp employees%ROWTYPE;
BEGIN
  emp_mgmt.get_dept_employees(60, v_cursor);

  LOOP
    FETCH v_cursor INTO v_emp;
    EXIT WHEN v_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ' ' || v_emp.last_name);
  END LOOP;

  CLOSE v_cursor;
END;
/

Triggers

Creando desencadenantes

-- Before insert trigger
CREATE OR REPLACE TRIGGER trg_emp_before_insert
  BEFORE INSERT ON employees
  FOR EACH ROW
BEGIN
  -- Auto-generate employee ID if not provided
  IF :NEW.employee_id IS NULL THEN
    SELECT employees_seq.NEXTVAL INTO :NEW.employee_id FROM dual;
  END IF;

  -- Set hire date to current date if not provided
  IF :NEW.hire_date IS NULL THEN
    :NEW.hire_date := SYSDATE;
  END IF;

  -- Convert email to lowercase
  :NEW.email := LOWER(:NEW.email);
END;
/

-- After update trigger
CREATE OR REPLACE TRIGGER trg_emp_after_update
  AFTER UPDATE ON employees
  FOR EACH ROW
BEGIN
  -- Log salary changes
  IF :OLD.salary != :NEW.salary THEN
    INSERT INTO salary_audit (
      employee_id, old_salary, new_salary, change_date, changed_by
    ) VALUES (
      :NEW.employee_id, :OLD.salary, :NEW.salary, SYSDATE, USER
    );
  END IF;
END;
/

-- Instead of trigger (for views)
CREATE OR REPLACE TRIGGER trg_emp_view_insert
  INSTEAD OF INSERT ON emp_dept_view
  FOR EACH ROW
BEGIN
  INSERT INTO employees (
    employee_id, first_name, last_name, email, department_id
  ) VALUES (
    :NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.email, :NEW.department_id
  );
END;
/

-- Compound trigger (Oracle 11g+)
CREATE OR REPLACE TRIGGER trg_emp_compound
  FOR INSERT OR UPDATE OR DELETE ON employees
  COMPOUND TRIGGER

  -- Declaration section
  TYPE emp_id_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  g_emp_ids emp_id_array;
  g_count PLS_INTEGER := 0;

  -- Before statement
  BEFORE STATEMENT IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Starting DML operation on employees table');
    g_count := 0;
  END BEFORE STATEMENT;

  -- Before each row
  BEFORE EACH ROW IS
  BEGIN
    IF INSERTING THEN
      g_count := g_count + 1;
      g_emp_ids(g_count) := :NEW.employee_id;
    ELSIF UPDATING THEN
      g_count := g_count + 1;
      g_emp_ids(g_count) := :NEW.employee_id;
    ELSIF DELETING THEN
      g_count := g_count + 1;
      g_emp_ids(g_count) := :OLD.employee_id;
    END IF;
  END BEFORE EACH ROW;

  -- After statement
  AFTER STATEMENT IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Processed ' || g_count || ' rows');

    -- Process collected employee IDs
    FOR i IN 1..g_count LOOP
      -- Update some summary table
      NULL; -- Placeholder for actual logic
    END LOOP;
  END AFTER STATEMENT;

END trg_emp_compound;
/

Gestionar los desencadenantes

-- Show triggers
SELECT trigger_name, table_name, triggering_event, status
FROM user_triggers
WHERE table_name = 'EMPLOYEES';

-- Show trigger source
SELECT trigger_body FROM user_triggers WHERE trigger_name = 'TRG_EMP_BEFORE_INSERT';

-- Enable/disable trigger
ALTER TRIGGER trg_emp_before_insert DISABLE;
ALTER TRIGGER trg_emp_before_insert ENABLE;

-- Drop trigger
DROP TRIGGER trg_emp_before_insert;

Transacciones

Control de transacciones

-- Start transaction (implicit)
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1001, 'John', 'Doe');

-- Savepoint
SAVEPOINT sp1;

UPDATE employees SET salary = 5000 WHERE employee_id = 1001;

SAVEPOINT sp2;

DELETE FROM employees WHERE employee_id = 1002;

-- Rollback to savepoint
ROLLBACK TO sp1;

-- Commit transaction
COMMIT;

-- Rollback entire transaction
ROLLBACK;

Transacciones autónomas

-- Autonomous transaction procedure
CREATE OR REPLACE PROCEDURE log_error(
  p_error_msg IN VARCHAR2
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (error_message, error_date)
  VALUES (p_error_msg, SYSDATE);
  COMMIT; -- This commit doesn't affect the calling transaction
END;
/

-- Using autonomous transaction
BEGIN
  INSERT INTO employees (employee_id, first_name, last_name)
  VALUES (1001, 'John', 'Doe');

  -- This will be logged even if the main transaction rolls back
  log_error('Processing employee 1001');

  -- Simulate error
  RAISE_APPLICATION_ERROR(-20001, 'Simulated error');
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK; -- Main transaction rolls back, but log entry remains
    RAISE;
END;
/

Lock Management

-- Explicit locking
SELECT * FROM employees WHERE employee_id = 1001 FOR UPDATE;

-- Lock with NOWAIT
SELECT * FROM employees WHERE employee_id = 1001 FOR UPDATE NOWAIT;

-- Lock with timeout
SELECT * FROM employees WHERE employee_id = 1001 FOR UPDATE WAIT 5;

-- Table-level lock
LOCK TABLE employees IN EXCLUSIVE MODE;

-- Check locks
SELECT object_name, object_type, session_id, locked_mode
FROM v$locked_object lo
JOIN dba_objects do ON lo.object_id = do.object_id;

PL/SQL

Bloque básico PL/SQL

-- Anonymous block
DECLARE
  v_emp_count NUMBER;
  v_avg_salary NUMBER;
BEGIN
  SELECT COUNT(*), AVG(salary)
  INTO v_emp_count, v_avg_salary
  FROM employees;

  DBMS_OUTPUT.PUT_LINE('Total employees: ' || v_emp_count);
  DBMS_OUTPUT.PUT_LINE('Average salary: ' || v_avg_salary);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Variables y Constantes

DECLARE
  -- Variable declarations
  v_employee_id NUMBER(6);
  v_first_name VARCHAR2(20);
  v_hire_date DATE := SYSDATE;
  v_salary NUMBER(8,2) DEFAULT 5000;

  -- Constants
  c_max_salary CONSTANT NUMBER := 50000;
  c_company_name CONSTANT VARCHAR2(30) := 'ACME Corp';

  -- %TYPE attribute
  v_last_name employees.last_name%TYPE;

  -- %ROWTYPE attribute
  v_employee employees%ROWTYPE;

  -- Record type
  TYPE emp_record_type IS RECORD (
    emp_id NUMBER(6),
    full_name VARCHAR2(50),
    annual_salary NUMBER(10,2)
  );
  v_emp_rec emp_record_type;

  -- Collection types
  TYPE number_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_salaries number_array;

  TYPE string_list IS TABLE OF VARCHAR2(100);
  v_names string_list := string_list();

BEGIN
  -- Variable assignments
  v_employee_id := 100;
  v_first_name := 'John';

  -- Using %ROWTYPE
  SELECT * INTO v_employee FROM employees WHERE employee_id = 100;

  -- Using record
  v_emp_rec.emp_id := v_employee.employee_id;
  v_emp_rec.full_name := v_employee.first_name || ' ' || v_employee.last_name;
  v_emp_rec.annual_salary := v_employee.salary * 12;

  -- Using collections
  v_salaries(1) := 5000;
  v_salaries(2) := 6000;

  v_names.EXTEND;
  v_names(1) := 'John Doe';

  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_rec.full_name);
  DBMS_OUTPUT.PUT_LINE('Annual salary: ' || v_emp_rec.annual_salary);
END;
/
```_

### Estructuras de control
```sql
DECLARE
  v_grade CHAR(1) := 'B';
  v_salary NUMBER := 5000;
  v_bonus NUMBER;
  v_counter NUMBER := 1;
BEGIN
  -- IF-THEN-ELSE
  IF v_salary > 10000 THEN
    v_bonus := v_salary * 0.2;
  ELSIF v_salary > 5000 THEN
    v_bonus := v_salary * 0.1;
  ELSE
    v_bonus := v_salary * 0.05;
  END IF;

  -- CASE statement
  CASE v_grade
    WHEN 'A' THEN
      DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN
      DBMS_OUTPUT.PUT_LINE('Good');
    WHEN 'C' THEN
      DBMS_OUTPUT.PUT_LINE('Average');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Below Average');
  END CASE;

  -- CASE expression
  v_bonus := CASE
    WHEN v_salary > 10000 THEN v_salary * 0.2
    WHEN v_salary > 5000 THEN v_salary * 0.1
    ELSE v_salary * 0.05
  END;

  -- Simple LOOP
  LOOP
    DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
    v_counter := v_counter + 1;
    EXIT WHEN v_counter > 5;
  END LOOP;

  -- WHILE LOOP
  v_counter := 1;
  WHILE v_counter <= 5 LOOP
    DBMS_OUTPUT.PUT_LINE('While counter: ' || v_counter);
    v_counter := v_counter + 1;
  END LOOP;

  -- FOR LOOP
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('For counter: ' || i);
  END LOOP;

  -- Reverse FOR LOOP
  FOR i IN REVERSE 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Reverse counter: ' || i);
  END LOOP;
END;
/

Cursors

DECLARE
  -- Explicit cursor
  CURSOR c_employees IS
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = 10;

  -- Cursor with parameters
  CURSOR c_emp_by_dept(p_dept_id NUMBER) IS
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = p_dept_id;

  -- Cursor variables
  v_emp_id NUMBER;
  v_first_name VARCHAR2(20);
  v_last_name VARCHAR2(25);
  v_salary NUMBER;

  -- Record for cursor
  v_emp_rec c_employees%ROWTYPE;

BEGIN
  -- Explicit cursor processing
  OPEN c_employees;
  LOOP
    FETCH c_employees INTO v_emp_id, v_first_name, v_last_name, v_salary;
    EXIT WHEN c_employees%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name || ': $' || v_salary);
  END LOOP;
  CLOSE c_employees;

  -- Cursor FOR loop (implicit open, fetch, close)
  FOR emp_rec IN c_employees LOOP
    DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
  END LOOP;

  -- Parameterized cursor
  FOR emp_rec IN c_emp_by_dept(20) LOOP
    DBMS_OUTPUT.PUT_LINE('Dept 20: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
  END LOOP;

  -- Cursor with SELECT statement
  FOR emp_rec IN (SELECT first_name, last_name FROM employees WHERE department_id = 30) LOOP
    DBMS_OUTPUT.PUT_LINE('Dept 30: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
  END LOOP;

  -- REF CURSOR
  DECLARE
    TYPE emp_cursor_type IS REF CURSOR;
    v_emp_cursor emp_cursor_type;
    v_sql VARCHAR2(1000);
  BEGIN
    v_sql := 'SELECT first_name, last_name FROM employees WHERE department_id = 40';
    OPEN v_emp_cursor FOR v_sql;

    LOOP
      FETCH v_emp_cursor INTO v_first_name, v_last_name;
      EXIT WHEN v_emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Dept 40: ' || v_first_name || ' ' || v_last_name);
    END LOOP;

    CLOSE v_emp_cursor;
  END;
END;
/

Excepciones de manipulación

DECLARE
  v_emp_id NUMBER := 999;
  v_first_name VARCHAR2(20);
  v_salary NUMBER;

  -- User-defined exception
  salary_too_high EXCEPTION;
  PRAGMA EXCEPTION_INIT(salary_too_high, -20001);

BEGIN
  -- This will raise NO_DATA_FOUND
  SELECT first_name, salary 
  INTO v_first_name, v_salary
  FROM employees 
  WHERE employee_id = v_emp_id;

  -- Check business rule
  IF v_salary > 50000 THEN
    RAISE salary_too_high;
  END IF;

  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_first_name);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee ' || v_emp_id || ' not found');

  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Multiple employees found');

  WHEN salary_too_high THEN
    DBMS_OUTPUT.PUT_LINE('Salary exceeds maximum allowed');

  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
    -- Re-raise the exception
    RAISE;
END;
/

Copia de seguridad y recuperación

RMAN Backup

# Connect to RMAN
rman target /

# Show configuration
SHOW ALL;

# Configure backup settings
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;

# Full database backup
BACKUP DATABASE;

# Incremental backup
BACKUP INCREMENTAL LEVEL 0 DATABASE;
BACKUP INCREMENTAL LEVEL 1 DATABASE;

# Backup specific tablespace
BACKUP TABLESPACE users;

# Backup archive logs
BACKUP ARCHIVELOG ALL;

# Backup with compression
BACKUP AS COMPRESSED BACKUPSET DATABASE;

# List backups
LIST BACKUP;
LIST BACKUP SUMMARY;

# Delete obsolete backups
DELETE OBSOLETE;

# Crosscheck backups
CROSSCHECK BACKUP;

Exportación/importación (bomba de datos)

# Full database export
expdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=full_db.dmp LOGFILE=full_db.log

# Schema export
expdp hr/password SCHEMAS=hr DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp LOGFILE=hr_schema.log

# Table export
expdp hr/password TABLES=employees,departments DIRECTORY=dpump_dir DUMPFILE=hr_tables.dmp

# Export with query
expdp hr/password TABLES=employees QUERY=employees:"WHERE hire_date > DATE '2020-01-01'" DIRECTORY=dpump_dir DUMPFILE=recent_employees.dmp

# Parallel export
expdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=full_db_%U.dmp PARALLEL=4

# Full database import
impdp system/password FULL=Y DIRECTORY=dpump_dir DUMPFILE=full_db.dmp LOGFILE=import.log

# Schema import
impdp system/password SCHEMAS=hr DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp REMAP_SCHEMA=hr:hr_new

# Table import
impdp hr/password TABLES=employees DIRECTORY=dpump_dir DUMPFILE=hr_tables.dmp TABLE_EXISTS_ACTION=REPLACE

# Import with tablespace remapping
impdp system/password SCHEMAS=hr DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp REMAP_TABLESPACE=users:users_new
```_

### Punto en tiempo Recuperación
```bash
# RMAN point-in-time recovery
rman target /

# Restore and recover to specific time
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RESTORE DATABASE UNTIL TIME "TO_DATE('2023-12-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";
RECOVER DATABASE UNTIL TIME "TO_DATE('2023-12-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";
ALTER DATABASE OPEN RESETLOGS;

# Restore and recover to specific SCN
RESTORE DATABASE UNTIL SCN 1234567;
RECOVER DATABASE UNTIL SCN 1234567;

# Tablespace point-in-time recovery
RECOVER TABLESPACE users UNTIL TIME "TO_DATE('2023-12-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";

Performance Tuning

SQL Tuning

-- Explain plan
EXPLAIN PLAN FOR
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Autotrace
SET AUTOTRACE ON;
SELECT * FROM employees WHERE salary > 5000;

-- SQL Trace
ALTER SESSION SET SQL_TRACE = TRUE;
-- Run your SQL statements
ALTER SESSION SET SQL_TRACE = FALSE;

-- 10046 trace
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- Run your SQL statements
ALTER SESSION SET EVENTS '10046 trace name context off';

-- AWR report
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

-- SQL Tuning Advisor
DECLARE
  task_name VARCHAR2(30);
  sql_text CLOB;
BEGIN
  sql_text := 'SELECT * FROM employees WHERE salary > 5000';

  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text => sql_text,
    task_name => 'tune_employees_query'
  );

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
END;
/

-- View tuning recommendations
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_employees_query') FROM dual;

Gestión de las Estadísticas

-- Gather table statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

-- Gather schema statistics
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');

-- Gather database statistics
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

-- Gather statistics with options
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'HR',
  tabname => 'EMPLOYEES',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  degree => DBMS_STATS.AUTO_DEGREE,
  cascade => TRUE
);

-- Delete statistics
EXEC DBMS_STATS.DELETE_TABLE_STATS('HR', 'EMPLOYEES');

-- Lock statistics
EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');

-- Unlock statistics
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('HR', 'EMPLOYEES');

-- View statistics
SELECT table_name, num_rows, blocks, avg_row_len, last_analyzed
FROM user_tables;

SELECT column_name, num_distinct, density, num_nulls, histogram
FROM user_tab_col_statistics
WHERE table_name = 'EMPLOYEES';

Gestión de memoria

-- Show SGA components
SELECT component, current_size/1024/1024 AS current_mb, 
       max_size/1024/1024 AS max_mb
FROM v$sga_dynamic_components;

-- Show PGA usage
SELECT name, value/1024/1024 AS mb FROM v$pgastat;

-- Buffer cache hit ratio
SELECT name, value FROM v$sysstat WHERE name = 'db block gets';
SELECT name, value FROM v$sysstat WHERE name = 'consistent gets';
SELECT name, value FROM v$sysstat WHERE name = 'physical reads';

-- Library cache hit ratio
SELECT namespace, gethitratio FROM v$librarycache;

-- Show top SQL by buffer gets
SELECT sql_id, buffer_gets, executions, 
       buffer_gets/executions AS gets_per_exec
FROM v$sql
WHERE executions > 0
ORDER BY buffer_gets DESC
FETCH FIRST 10 ROWS ONLY;

Supervisión

Supervisión de la base de datos

-- Database status
SELECT name, open_mode, database_role FROM v$database;

-- Instance information
SELECT instance_name, host_name, version, status, startup_time
FROM v$instance;

-- Session information
SELECT sid, serial#, username, program, machine, status
FROM v$session
WHERE username IS NOT NULL;

-- Active sessions
SELECT COUNT(*) AS active_sessions
FROM v$session
WHERE status = 'ACTIVE';

-- Blocking sessions
SELECT blocking_session, sid, serial#, wait_class, event
FROM v$session
WHERE blocking_session IS NOT NULL;

-- Tablespace usage
SELECT tablespace_name,
       ROUND(used_mb, 2) AS used_mb,
       ROUND(free_mb, 2) AS free_mb,
       ROUND(total_mb, 2) AS total_mb,
       ROUND((used_mb/total_mb)*100, 2) AS used_percent
FROM (
  SELECT tablespace_name,
         SUM(bytes)/1024/1024 AS total_mb,
         SUM(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END)/1024/1024 AS max_mb
  FROM dba_data_files
  GROUP BY tablespace_name
) df
JOIN (
  SELECT tablespace_name,
         SUM(bytes)/1024/1024 AS free_mb
  FROM dba_free_space
  GROUP BY tablespace_name
) fs ON df.tablespace_name = fs.tablespace_name
CROSS APPLY (
  SELECT df.total_mb - fs.free_mb AS used_mb FROM dual
);

-- Archive log generation
SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour,
       COUNT(*) AS log_count,
       SUM(blocks * block_size)/1024/1024 AS mb_generated
FROM v$archived_log
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

Supervisión de la ejecución

-- Top wait events
SELECT event, total_waits, time_waited_micro/1000000 AS time_waited_sec
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY;

-- Top SQL by elapsed time
SELECT sql_id, elapsed_time/1000000 AS elapsed_sec, executions,
       elapsed_time/executions/1000000 AS avg_elapsed_sec
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

-- I/O statistics
SELECT name, phyrds, phywrts, readtim, writetim
FROM v$filestat f
JOIN v$datafile d ON f.file# = d.file#
ORDER BY phyrds + phywrts DESC;

-- Redo log switches
SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour,
       COUNT(*) AS switches
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

Seguridad

Seguridad del usuario

-- Password policy
CREATE PROFILE secure_profile LIMIT
  SESSIONS_PER_USER 2
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL 3000
  CONNECT_TIME 45
  IDLE_TIME 10
  LOGICAL_READS_PER_SESSION DEFAULT
  LOGICAL_READS_PER_CALL 1000
  PRIVATE_SGA 15K
  COMPOSITE_LIMIT 5000000
  PASSWORD_LIFE_TIME 60
  PASSWORD_GRACE_TIME 10
  PASSWORD_REUSE_TIME 1800
  PASSWORD_REUSE_MAX 10
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME 1/24
  PASSWORD_VERIFY_FUNCTION verify_function;

-- Apply profile to user
ALTER USER app_user PROFILE secure_profile;

-- Audit settings
AUDIT SELECT, INSERT, UPDATE, DELETE ON hr.employees BY ACCESS;
AUDIT CREATE SESSION BY app_user BY ACCESS;

-- Enable unified auditing (Oracle 12c+)
AUDIT POLICY ora_database_parameter;
AUDIT POLICY ora_secureconfig;

-- View audit records
SELECT username, action_name, object_name, timestamp
FROM dba_audit_trail
WHERE username = 'APP_USER'
ORDER BY timestamp DESC;

Encryption

-- Transparent Data Encryption (TDE)
-- Create encrypted tablespace
CREATE TABLESPACE secure_data
DATAFILE '/u01/app/oracle/oradata/mydb/secure_data01.dbf' SIZE 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

-- Create table with encrypted column
CREATE TABLE sensitive_data (
  id NUMBER,
  ssn VARCHAR2(11) ENCRYPT USING 'AES256',
  credit_card VARCHAR2(16) ENCRYPT USING 'AES256',
  name VARCHAR2(100)
) TABLESPACE secure_data;

-- Network encryption (sqlnet.ora)
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256, SHA1)

Base de datos virtual privada (VPD)

-- Create security policy function
CREATE OR REPLACE FUNCTION emp_security_policy(
  schema_var IN VARCHAR2,
  table_var IN VARCHAR2
) RETURN VARCHAR2
IS
  v_predicate VARCHAR2(400);
BEGIN
  IF USER = 'HR_MANAGER' THEN
    v_predicate := '1=1'; -- See all records
  ELSE
    v_predicate := 'employee_id = SYS_CONTEXT(''USERENV'', ''SESSION_USER'')';
  END IF;

  RETURN v_predicate;
END;
/

-- Apply security policy
BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema => 'HR',
    object_name => 'EMPLOYEES',
    policy_name => 'EMP_SECURITY_POLICY',
    function_schema => 'HR',
    policy_function => 'EMP_SECURITY_POLICY',
    statement_types => 'SELECT, INSERT, UPDATE, DELETE'
  );
END;
/

Buenas prácticas

Schema Design Best Practices

-- Use appropriate data types
CREATE TABLE orders (
  order_id NUMBER(10) PRIMARY KEY,           -- Use NUMBER for IDs
  order_date DATE NOT NULL,                  -- Use DATE for dates
  amount NUMBER(10,2) NOT NULL,              -- Use NUMBER(p,s) for money
  status VARCHAR2(20) DEFAULT 'PENDING',     -- Use VARCHAR2 for strings
  customer_id NUMBER(10) NOT NULL,
  CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Use constraints for data integrity
ALTER TABLE orders ADD CONSTRAINT chk_amount CHECK (amount > 0);
ALTER TABLE orders ADD CONSTRAINT chk_status CHECK (status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'CANCELLED'));

-- Use sequences for primary keys
CREATE SEQUENCE orders_seq START WITH 1 INCREMENT BY 1 CACHE 20;

-- Use indexes appropriately
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_date_status ON orders(order_date, status);

Prácticas óptimas de rendimiento

-- Use bind variables
-- Bad: SELECT * FROM employees WHERE employee_id = 100;
-- Good: SELECT * FROM employees WHERE employee_id = :emp_id;

-- Use appropriate hints when necessary
SELECT /*+ INDEX(e idx_emp_dept_id) */ *
FROM employees e
WHERE department_id = 10;

-- Avoid SELECT *
-- Bad: SELECT * FROM employees;
-- Good: SELECT employee_id, first_name, last_name FROM employees;

-- Use EXISTS instead of IN for subqueries
-- Good: 
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id);

-- Use ROWNUM for limiting results
SELECT * FROM (
  SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 10;

Prácticas óptimas de seguridad

-- Use least privilege principle
-- Create role for specific access
CREATE ROLE app_read_role;
GRANT SELECT ON hr.employees TO app_read_role;
GRANT SELECT ON hr.departments TO app_read_role;
GRANT app_read_role TO app_user;

-- Use strong authentication
-- Enable password complexity
ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;

-- Regular maintenance
-- Gather statistics regularly
EXEC DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

-- Monitor and tune
-- Regular AWR reports
-- Monitor alert log
-- Check tablespace usage
-- Review slow queries

-...

Resumen

Oracle Database es un sistema integral de gestión de bases de datos institucionales que proporciona características sólidas para la gestión de datos, seguridad, rendimiento y escalabilidad. Esta hoja de cálculo cubre las operaciones esenciales de la base de datos de Oracle desde la administración básica a funciones avanzadas.

Key Strengths - Características de entretenimiento: Seguridad avanzada, partición, compresión y alta disponibilidad - Performance: Optimizador de consultas sofisticado y capacidades de ajuste amplia - Scalability: Support for very large databases and high-concurrency burdens - PL/SQL: Potente lenguaje procesal para la lógica empresarial compleja - Reliability: ACID compliance with advanced backup and recovery options

Mejores casos de uso: - Aplicaciones de gran empresa - Almacenamiento de datos y análisis - Sistemas críticos de la Misión que requieren alta disponibilidad - Aplicaciones con lógica empresarial compleja - Sistemas que requieren características de seguridad avanzadas

** Consideraciones importantes:** - Los costos de concesión de licencias pueden ser importantes - Requiere administradores de bases de datos calificados - Instalación y configuración complejas - Recursos intensivos (memoria y CPU) - El mantenimiento y la vigilancia periódicos son esenciales

Al seguir las prácticas y técnicas descritas en esta hoja de trampa, puede diseñar, implementar y mantener eficazmente los sistemas de base de datos de Oracle que cumplen con los requisitos empresariales para el rendimiento, la seguridad y la fiabilidad.

" copia de la funciónToClipboard() {} comandos const = document.querySelectorAll('code'); que todos losCommands = '; comandos. paraCada(cmd = confianza allCommands += cmd.textContent + '\n'); navigator.clipboard.writeText(allCommands); alerta ('Todos los comandos copiados a portapapeles!'); }

función generaPDF() { ventana.print(); } ■/script título