tutorial - Características ocultas de PL/SQL

pl sql oracle

Question

A la luz de la serie de preguntas "Características ocultas de ..." , ¿qué características poco conocidas de PL / SQL le han resultado útiles?

Edición: las características específicas de PL / SQL se prefieren a las características de la sintaxis SQL de Oracle. Sin embargo, dado que PL / SQL puede usar la mayoría de las construcciones SQL de Oracle, pueden incluirse si facilitan la programación en PL / SQL.

Answer 1

1. Una función no documentada: dbms_system.ksdwrt (escribe en los archivos de alerta / rastreo)
2. Paquete DBMS_SQL (como ejemplo de su uso, vea esta pregunta
3. AUTHID CURRENT_USER cláusula
4. Compilación condicional

Answer 2

¿Sabe que con la opción MUESTRA (K) puede SELECCIONAR solo una muestra compuesta por hasta el K por ciento de una tabla de Oracle?

SELECT *
  FROM MASSIVE_TABLE SAMPLE (5);

La declaración anterior recupera un conjunto aleatorio compuesto por hasta el 5% de los registros almacenados en la tabla masiva llamada MASSIVE_TABLE.

Answer 3

Dynamic PL / SQL es feo, pero puede hacer algunas cosas interesantes. Por ejemplo, los nombres pueden tratarse como variables, que he usado anteriormente para recorrer%% rowtipo variables como matrices, y para crear una función que, para un nombre de tabla dado, devolverá un cursor que selecciona una sola fila con los valores predeterminados de cada columna. Ambas son soluciones útiles para tablas desnormalizadas.

Answer 4

Esta es una construcción de procedimiento PL / SQL que uso mucho (créditos para Steven Feuerstein y Chen Shapira). Una matriz asociativa utilizada para el chaching, pero no carga previamente todos los datos, pero obtiene datos de la base de datos si es necesario y los coloca en la matriz asociativa.

create or replace
PACKAGE justonce
IS
  FUNCTION hair (code_in IN hairstyles.code%TYPE)
    RETURN hairstyles%ROWTYPE;
  TYPE hair_t IS TABLE OF hairstyles%ROWTYPE
    INDEX BY BINARY_INTEGER;
  hairs          hair_t;
END justonce;

create or replace 
PACKAGE BODY justonce
IS
  FUNCTION hair (code_in IN hairstyles.code%TYPE) RETURN hairstyles%ROWTYPE
  IS
    return_value   hairstyles%ROWTYPE;
    FUNCTION hair_from_database RETURN hairstyles%ROWTYPE
    IS
      CURSOR hair_cur IS
      SELECT * FROM hairstyles WHERE code = code_in;
    BEGIN
      OPEN hair_cur;
      FETCH hair_cur INTO return_value;
      CLOSE hair_cur;
      RETURN return_value;
    END hair_from_database;
  BEGIN
    IF NOT (hairs.exists(code_in))
    THEN
      dbms_output.put_line(''Get record from database'');
      hairs (code_in) := hair_from_database;
    END IF;
    RETURN hairs (code_in);
  END hair;
END justonce;

Pruébalo :

declare
    h hairstyles%ROWTYPE;
begin
   for i in 1000..1004
   loop
      h := justonce.hair(i);
      dbms_output.put_line(h.description);
   end loop;
   for i in 1000..1004
   loop
      h := justonce.hair(i);
      dbms_output.put_line(h.description||'' ''||h.price);
   end loop;

end;
/

Get record from database
CREWCUT
Get record from database
BOB
Get record from database
SHAG
Get record from database
BOUFFANT
Get record from database
PAGEBOY
CREWCUT 10
BOB 20
SHAG 21
BOUFFANT 11
PAGEBOY 44

Answer 5

La función de Oracle realmente oculta es la función OVERLAPS, pero es probable que no sea muy inteligente utilizar funciones no compatibles.

select ''yes'' from dual where (sysdate-5,sysdate) overlaps (sysdate-2,sysdate-1);

Answer 6

Los procedimientos y funciones pueden definirse dentro de los bloques DECLARE :

DECLARE

    PROCEDURE print(text VARCHAR2) IS
    BEGIN
        DBMS_OUTPUT.put_line(text);
    END;

BEGIN

    print(''Yay!'');
    print(''Woo hoo!'');

END;

Esto es útil para crear scripts independientes.

Answer 7

Mi respuesta a las características ocultas en Oracle es relevante aquí:

Como Apex ahora forma parte de cada base de datos Oracle, estas funciones de la utilidad Apex son útiles incluso si no está utilizando Apex:

SQL> declare
  2    v_array apex_application_global.vc_arr2;
  3    v_string varchar2(2000);
  4  begin
  5  
  6    -- Convert delimited string to array
  7    v_array := apex_util.string_to_table(''alpha,beta,gamma,delta'', '','');
  8    for i in 1..v_array.count
  9    loop
 10      dbms_output.put_line(v_array(i));
 11    end loop;
 12  
 13    -- Convert array to delimited string
 14    v_string := apex_util.table_to_string(v_array,''|'');
 15    dbms_output.put_line(v_string);
 16  end;
 17  /
alpha
beta
gamma
delta
alpha|beta|gamma|delta

PL/SQL procedure successfully completed.

Answer 8

Puede anular variables, puede nombrar bloques anónimos y aún puede referirse a las variables anuladas por nombre:

PROCEDURE myproc IS
   n NUMBER;
BEGIN
   n := 1;
   <<anon>>
   DECLARE
      n NUMBER;
   BEGIN
      n := 2;
      dbms_output.put_line(''n='' || n);
      dbms_output.put_line(''anon.n='' || anon.n);
      dbms_output.put_line(''myproc.n='' || myproc.n);
   END anon;
END myproc;

Answer 9

Puede indexar tablas pl / sql por otros tipos además de enteros. De esta manera, puede crear estructuras tipo "diccionario", que pueden hacer que su código sea mucho más fácil de leer:

Ejemplo:

DECLARE
  TYPE dictionary IS TABLE OF VARCHAR2(200) INDEX BY VARCHAR2(100);
  dict dictionary;
BEGIN
  dict(''NAME'') := ''John Doe'';
  dict(''CITY'') := ''New York'';

  dbms_output.put_line(''Name:'' || dict(''NAME''));
END;

Answer 10

Puede simular un CONTINUAR agregando una etiqueta a un bucle y luego una etiqueta GOTO de esta:

declare
   i integer;
begin
   i := 0;

   <<My_Small_Loop>>loop

      i := i + 1;
      if i <= 3 then goto My_Small_Loop; -- => means continue
      end if;

      exit;

   end loop;
end;

Answer 11

Tal vez no lo suficientemente oculto, pero me encanta la declaración de combinación que permite realizar actualizaciones (insertar o actualizar)

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

Answer 12

Una característica poco conocida con la que he tenido mucho éxito es la capacidad de insertar en una tabla utilizando una variable declarada como su %ROWTYPE . Por ejemplo:

CREATE TABLE CUSTOMERS (
    id NUMBER,
    name VARCHAR2(100),
    birth DATE,
    death DATE
)

PROCEDURE insert_customer IS
    customer CUSTOMERS%ROWTYPE;
BEGIN
    customer.id := 45;
    customer.name := ''John Smith'';
    customer.birth := TO_DATE(''1978/04/03'', ''YYYY/MM/DD'');

    INSERT INTO CUSTOMERS VALUES customer;
END;

Aunque reduce un poco más el espacio de tablas de rehacer, sin duda hace que la inserción de datos (especialmente en tablas más grandes) sea mucho más clara. También evita la multitud de variables necesarias para almacenar el valor de cada columna que desea insertar.