guia plsql breve

Gúia Básica PL/SQL

para DBAs

Juan Sánchez – [email protected]

Indentificadores 1. Longitud Máxima 30 caracteres 2. Siempre empiezan por 1 letra 3. Valores válidos: letras, números, $, _, # 4. No son “case sensitive” por defecto 5. No pueden utilizar palabras reservadas

Comentarios 1. De 1 línea -- <comentario>

2. De más de 1 línea /* <comentario> */

Operadores Ope rador Descripción Ejemplo

. Selector componente

tabla.columna

(,) Expresión o delimitador

nombre varchar2(20);

: Indicador de host

, Separador edad number(3,0)

“ Comillas nombre:=”Steve”

= Igualdad fecha_entrega=sysdate

@ Indicador de acceso remoto

; Terminador de comando

nombre varchar2(20);

:= Asignación nombre:=”Steve”

=> Operador de asociación

cortar(cadena=>”a”)

|| Concatenación nombre:=”Steve” || “Mar”

** Exponenciación cuadrado:=5**2

<<, >> Identificadores de etiqueta

<<bloque_principal>>

/*, */ Comentarios multilínea

/* Comentario */

-- Comentario en 1 línea

-- Comentario

. . Operador de rango

<,>,<=,>= Operadores relacionales

edad > sysdate

<>,’=,~=, ^=

Desigualdad fecha_entrega<>sysdate

% Comodín nombre LIKE ‘A%’

+, -, *, / Suma, Resta, Mult y División

edad := sysdate+10

IS NULL Comparar NULL edad IS NULL

LIKE Como nombre LIKE ‘A%’

BETWEEN Entre edad BETWEEN 1 an 18

IN En edad in (2,4,18)

NOT No NOT edad = 19

AND Y cond1 AND cond2

OR O cond1 OR cond2

Declaración variables <nombre> [constant] <tipo>; <nombre> [constant] <tipo> DEFAULT {<valor> | NULL}; <nombre> [constant] <tipo> := <valor>; <nombre> <variable>%TYPE; <nombre> <tabla>.<columna>%TYPE; <nombre> <tabla>%ROWTYPE;

[ ] Valor Opcional, { } Valor obligatorio, < > Valor o Tipo

Tipos de variables Tipo Descripción Ejemplo

number(dig,dec)

Número Dig=Dígitos, Dec=Decimales

edad number(3,0); importe number(6,2);

varchar2(long)

Carácter nombre varchar2(20);

%TYPE Mismo tipo que variable

edad2 edad%TYPE; edad cliente.edad%TYPE;

%ROWTYPE Colección columnas

registro tab_cliente%ROWTYPE

Ámbito variables 1. Si variable/parámetro mismo nombre que columna

tabla, precede columna tabla 2. Una variable es visible en el bloque que se ha

declarado, así como en sus sub bloques 3. Una variable declarada en un sub bloque precede a la

del bloque 4. Una variable de bloque precedida por una de sub

bloque es visible mediante el identificador

<<ppal>>

declare

mensaje varchar2(20):='Principal';

begin

dbms_output.put_line('mensaje = ' || mensaje );

declare

mensaje varchar2(20) := 'Sub bloque';

begin

dbms_output.put_line('mensaje=' || mensaje );

dbms_output.put_line('mensaje=' ||

ppal.mensaje );

end;

end;

/

Procedimientos create or replace procedure <nombre>

(<parámetros>)

is

[<declaración de variables>]

begin

comando1;

….

[exception]

<manejo de excepciones>

end;

Funciones create or replace procedure <nombre>

(<parámetros>) return <tipo>

is

[<declaración de variables>]

begin

comando1;

….

[exception]


end;

Bloque anónimo [<<etiqueta>>]

[declare

<declaración de variables>

<declaración funciones|proced>]

begin

comando1;

….

[exception]


end;

Bloque anónimo <<ppal>>

declare

mensaje varchar2(20):='Hola Mundo';

begin

dbms_output.put_line(mensaje );

end;

/

Procedimientos create or replace procedure

mostrar_mensaje (mensaje IN varchar2)

is

begin

dbms_output.put_line(mensaje);

end;

Funciones create or replace

function hoy return timestamp

is

begin

return systimestamp;

end;

Tipos de parámetros Tipo Descripción Ejemplo

IN Entrada Por defecto Por referencia

edad IN number edad number

OUT Salida Por valor

edad OUT number

OUT NOCOPY Salida Por referencia

edad OUT NOCOPY number

IN OUT Entrada/Salida Por valor

edad IN OUT number

IN OUT NOCOPY

Entrada/Salida Por Referencia

edad IN OUT NOCOPY number

Tipo Descripción Ejemplo

Obligatorio Con valor edad IN number

Opcional Predefinidos edad IN number := 18


Cuerpo del paquete create or replace package body <paquete>

as

<funciones>;

<procedimientos>;

<variables globales>;

<constantes>;

end;

Paquete create or replace package pkg <paquete>

as

<cabeceras funciones>;

<cabecerar procedimientos>;

<variables globales>;

<constantes>;

end;

Paquete create or replace package prueba

as

function hoy return timestamp;

funciton mañana return timestamp;

procedure mensaje(texto varchar2);

end;

Cuerpo del paquete create or replace package pkg prueba

as

function hoy return timestamp

is

return systimestamp;

end;

end;

Disparadores create or replace trigger <disparador>

{before insert or update or delete |

instead of insert or update or delete}

of <campos>

on <table>

[for each row]

declare

<variables>

begin

…

end;

Campos especiales disparad. 1. :new.<campo> = Valor Nuevo 2. :old.<campo> = Valor Previo

Abortar DML mdte excepción raise_application_error(-<num>,’texto’);

Paquetes Estándar Nombre Descripción

dbms_output Muestra mensaje en consola

utl_file Gestión de ficheros externos localizados en el sistema

dbms_utility Utilidades del sistema

dbms_job Gestión de tareas y trabajos

Transacciones autónomas create or replace trigger <nombre>

is

pragma autonomous_transaction;

begin

<comandos>;

commit;

end;


Condicional IF if <condición> then

<commandos>

elsif <condición> then

<commandos>

else

<commandos>

end if;

Condicional IF if edad > 18 then

dbms_output.put_line(‘OK’);

elsif edad = 18 then

dbms_output.put_line(‘Casi OK’);

else

dbms_output.put_line(‘KO’);

end if;

Condicional CASE case <selector>

when <valor1> then

<comandos>

when <valor2> then

<comandos>

else

<comandos>

end case;

Condicional CASE case edad

when 18 then

dbms_output.put_line(‘ok’);

else

dbms_output.put_line(‘ko’);

end case;

Condicional CASE case

when <condicion1> then

<comandos>

when <condicion2> then

<comandos>

else

<comandos>

end case;

Condicional CASE case

when edad >18 then

dbms_output.put_line(‘ok’);

else

dbms_output.put_line(‘ko’);

end case;

Iteración LOOP loop

<comandos>;

exit when <condicion>;

<comandos>;

end loop;

Iteración LOOP loop

edad:=edad+1;

exit when edad=40;

end loop;

Iteración WHILE while <condicion>

loop

<comandos>

end loop;

Iteración WHILE while edad <= 40

loop

edad:=edad+1;

end loop;

Iteración FOR for <contador> in [reverse] <min>..<max>

loop

<comandos>

end loop;

Iteración FOR for edad in 10..18

loop

dbms_output.put_line(edad)

end loop;


Excepciones Estándar Excepción Error Oracle ID

ACCESS_INTO_NULL ORA-06530 -6530 CASE_NOT_FOUND ORA-06592 -6592 COLLECTION_IS_NULL ORA-06531 -6531 CURSOR_ALREADY_OPEN ORA-06511 -6511 DUP_VAL_ON_INDEX ORA-00001 -1 INVALID_CURSOR ORA-01001 -1001 INVALID_NUMBER ORA-01722 -1722 LOGIN_DENIED ORA-01017 -1017 NO_DATA_FOUND ORA-01403 +100 NOT_LOGGED_ON ORA-01012 -1012 PROGRAM_ERROR ORA-06501 -6501 ROWTYPE_MISMATCH ORA-06504 -6504 SELF_IS_NULL ORA-30625 -30625 STORAGE_ERROR ORA-06500 -6500 SUBSCRIPT_BEYOND_CO

UNT ORA-06533 -6533

SUBSCRIPT_OUTSIDE_L

IMIT ORA-06532 -6532

SYS_INVALID_ROWID ORA-01410 -1410 TIMEOUT_ON_RESOURCE ORA-00051 -51 TOO_MANY_ROWS ORA-01422 -1422 VALUE_ERROR ORA-06502 -6502 ZERO_DIVIDE ORA-01476 -1476

Gestión excepción create or replace procedure <nombre>

is

begin

<comandos>;

exception

when <codigo> then

<comandos>;

when others then

<comandos>;

end;

Gestión excepcion create or replace procedure <nombre>

is

begin

<comandos>;

exception

when ZERO_DIVIDED then

<comandos>;

when others then

<comandos>;

end;

Tipos de Excepciones Nombre Descripción

ORA Errores Oracle

PLS Errores PL/SQL

FRM Oracle Forms

REP Oracle Reports

Partes de una excepción Nombre Descripción

Nombre Abreviatura error

Tipo Tipo de Error

ID Código Identificador

Mensaje Mensaje descriptivo

Renombrar excepción create or replace procedure <nombre>

is

<excepción> exception;

pragma exception_init(<excepcion>,<id>);

begin

<comandos>;

exception

when <excepcion> then

<comandos>;

end;

Crear excepción create or replace procedure <nombre>

is

<excepción> exception;

pragma exception_init(<excepcion>,<id>);

begin

<comandos>;

raise <excepcion>;

<comandos>

exception

when <excepcion> then

<comandos>;

end;


Cursor implicito select <campo> into <variable> from ...;

insert into ...

update ...

delete ...

Cursor implicito select sysdate into ahora from dual;

<registro> <tabla>%ROWTYPE;

select * into <registro> from <tabla>

Cursor Explicito declare

cursor <cursor> is <consulta>;

begin

open <cursor>;

fetch <cursor>.<campo> into <var>;

close <cursor>;

end;

/


cursor <cursor> is <consulta>;

<registro> <cursor>%ROWTYPE;

begin

open <cursor>;

loop

fetch <cursor> into <registro>;

exit when <cursor>%NOTFOUND;

end;

/

Variables predefinidas Implicito Nombre Descripción

SQL%NOTFOUND No Existen registros afectados

SQL%FOUND Existen registros afectados

SQL%ISOPEN Siempre devuelve falso

SQL%ROWCOUNT Nº registros recuperados


cursor <cursor>

(<parámetro> <tipo>) is

<consulta>;

<registro> <cursor>%ROWTYPE;

begin

open <cursor>(<valor>);

loop

fetch <cursor> into <registro>;

exit when <cursor>%NOTFOUND;

end loop;

close <cursor>

end;

Variables predefinidas Explicito Nombre Descripción

<cursor>%NOTFOUND No Existen registros afectados

<cursor>%FOUND Existen registros afectados

<cursor>%ISOPEN Esta el cursor abierto

<cursor>%ROWCOUNT Nº registros recuperados


cursor <cursor>

(<parámetro> <tipo> is

<consulta>;

begin

for <reg> in <cursor>(<valor>) loop

<comandos>

end loop;

end;

/

Cursor Explicito begin

for <reg> in <consulta> loop

<comandos>

end loop;

end;

/


Leer datos actuales después inicio transacción begin

set transaction isolation level read

commited;

<comandos>;

end;

Obviar datos actuales después inicio transacción begin

set transaction isolation level

serializable;

<comandos>;

end;

SQL Dinámico DML declare

<consulta> varchar2 :=<query>;

begin

execute immediate <consulta>;

end;

SQL Dinámico SELECT declare


<variable> <tipo>;

begin

execute immediate <consulta> into

<variable>;

end;

SQL Dinámico SELECT con BIND declare


<variable> <tipo>;

<parametro> <tipo>;

begin


<variable> using <parametro>;

end;

query = ‘select * from tabla where campo

= :parametro’

PL/SQL Dinámico con BIND declare

<plsql> varchar2 :=<codigo>;

<variable> <tipo>;

<parametro> <tipo>;

begin


<variable> using in out <parametros>;

end;

query = ‘begin :1:=:2+:3; end;’



Tamaño tabla select avg_row_len*num_rows from dba_tables;

select segment_name, sum(bytes)/(1024*1024)from user_extents where

segment_type='TABLE' and segment_name = <tabla>;

SQL Ejecutado o Ejecuntándose select sql_fulltext, executions, disk_reads, elapsed_time, from v_$sql;

Enviar correo electrónico DECLARE

v_From VARCHAR2(80) := '[email protected]';

v_Recipient VARCHAR2(80) := '[email protected]';

v_Subject VARCHAR2(80) := 'test subject';

v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com';

v_Mail_Conn utl_smtp.Connection;

crlf VARCHAR2(2) := chr(13)||chr(10);

BEGIN

v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

utl_smtp.Mail(v_Mail_Conn, v_From);

utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

utl_smtp.Data(v_Mail_Conn,

'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||

'From: ' || v_From || crlf ||

'Subject: '|| v_Subject || crlf ||

'To: ' || v_Recipient || crlf ||

crlf ||

'some message text'|| crlf || -- Message body

'more message text'|| crlf

);

utl_smtp.Quit(v_mail_conn);

EXCEPTION

WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then

raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);

END;

/

Tamaño libre tablespace SELECT a.tablespace_name,

b.size_kb/1024 SIZE_MB,

a.free_kb/1024 FREE_MB,

Trunc((a.free_kb/b.size_kb) * 100) "FREE_%"

FROM (SELECT tablespace_name,

Trunc(Sum(bytes)/1024) free_kb

FROM dba_free_space

GROUP BY tablespace_name) a,

(SELECT tablespace_name,

Trunc(Sum(bytes)/1024) size_kb

FROM dba_data_files

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

ORDER BY 4 desc

[ ] Valor Opcional, { } Valor obligatorio, < > Valor o Tipo, | Separador Opciones

Quién está conectado SELECT s.inst_id,

s.sid,

s.serial#,

p.spid,

s.username,

s.program

FROM gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND';

ALTER SYSTEM KILL SESSION 'sid,serial#' [{IMMEDIATE|POST_TRANSACTION}];

Parametrización y utilidades SQL/PLUS

Nombre Descripción

&parámetro Parámetro que se pregunta cada vez que se necesita

&&parámetro Parámetro que se pregunta solo la primera vez que se necesita

!, host Ejecuta comando del host

set sqlprompt ‘prompt’ Prompt de SQL/Plus

Set pagesize <línea> Número de Líneas por página

Set linesize <caracteres> Número de caracteres por línea

Define _editor=<editor> Editor del sistema (vi,notepad)

edit Entra en modo de edición

Show all Muestra todos los parametros

Set timing ON|OFF Muestra el tiempo de ejecución

Set serveroutput ON Muestra los resultados por consola

guia plsql breve

Documents