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.