miércoles, 18 de mayo de 2011

PL/SQL PARTE 2

Operadores Lógicos
Veamos la tabla de verdad de los operadores lógicos, para entender
como se evalúan:


Probablemente, lo que más llamará la atención de esta tabla es que vemos que existen determinadas operaciones entre un valor NULL y otro que no lo es, que sin embargo no dan como resultado NULL, lo cual contradice algo que dijimos en un apartado anterior... esto es cierto, sin embargo Oracle recomienda que

Operador ISNULL

Se usa asi:
                                 IF variable IS NULL THEN …

Operador LIKE

Se usa asi:

         if valor_reg like '%NIZA%' then
             loc_reg := 25;
         end if;


Recordar que los caracteres de búsqueda para LIKE son CASE Sensitive por lo que se distinguen entre mayúsculas y minúsculas.
También se puede usar el caracter "_" para representar una palabra:

         ‘JUAN’ LIKE ‘J_AN’; – Devuelve TRUE…

Operador BETWEEN

Verifica un valor en un determinado rango:

                                                      45 BETWEEN 38 AND 44;

Operador IN
Es semejante que en SQL Server

                        DELETE FROM emp WHERE nom_emp IN (NULL, ’PEPE’,’PEDRO’);

Operador de Concatenación

El operador de concatenación son las dos barras verticales (||), el cual añade un string a otro. Veamos un ejemplo:
 
Si ambos operandos son del tipo CHAR, el operador de concatenación devuelve un valor CHAR… en cualquier otro caso devolverá un valor tipo VARCHAR2.

Funciones Soportadas por PL/SQL

Estas son las siguientes funciones que PL/SQL soporta:



                                ‘moto’ || ‘sierra’ = ‘motosierra’;
jamás se evalúe una expresión con un operando NULL, ya que potencialmente puede dar un valor indefinido, y eso debemos tenerlo en cuenta a pesar de esta tabla lógica de verdad.

martes, 15 de marzo de 2011

PL/SQL PARTE 1

Las siguientes lineas exponen TIP'S respecto a las consultas PL/SQL:

  1. Si queremos usar dbms_output.put_line('ABCDEF.....'); debemos tener activada set serveroutput on size 10000
         SQL> set serveroutput on size 10000
         SQL> begin
            2  dbms_output.put_line('Hola Mundo');
            3  end;
            4  /

           Hola Mundo

           DBMS_OUTPUT.put ('Texto sin avance');
           DBMS_OUTPUT.put_line (' de línea. Acá sí');

  1. Definición de SubTipos:

          Podemos usar subtipos para definir variables en base a un tipo base:
               
                declare
                subtype x is number(18,4);

          Podemos definir subtipos de otros subtipos:
               
                declare
                temp VARCHAR2(15);
                subtype x is temp%TYPE;  --La longitud máxima de palabra será de 15.




Los subtipos pueden ayudar en determinados casos al tratamiento de errores, si se definen adecuadamente dentro de algún rango. Por ejemplo si tenemos una variable y sabemos que su rango será –9 … 9, podemos
hacer la definición de la siguiente forma.


CONVERSION DE TIPO DE DATO:

Podemos convertir tipo de datos de un tipo a otro de manera implicita o de maner explicita.

  • TO_DATE, TO_NUMBER, TO_CHAR, etc.
Ejemplos:
                  La siguiente linea guarda en la variable fechaactual el valor en segundos del sistema.

                  SELECT TO_CHAR(SYSDATE,'SS') INTO fechaactual from dual;

  • TO_CHAR (número | fecha [,’fmt’]) Convierte un número o fecha en una cadena de caracteres VARCHAR2 con el modelo de formato fmt.          9: Representa un número
             0: Fuerza a que se muestra el cero
             $: Signo de dólar
             L: Usa el signo de moneda local
             .: Imprime el punto decimal
             ;: Imprime el indicador de millar 
        

Es importante que hagamos la conversión al tipo de dato de la columna de donde sacas a fin de no relentizar nuestras consultas. Las conversiones explicitas son las mejores.


Definición de Tipos
             DECLARE
             temp NUMBER(1,0);
             SUBTYPE Escala IS temp%TYPE;
             eje_x Escala; -- El rango será entre –9 y 9
             eje_y Escala;
             BEGIN
                          eje_x := 10; -- Esto nos provocará un VALUE_ERROR


En el siguiente enlace podemos hallas mas TIPS del diario quehacer del DBA Oracle: http://www.dataprix.com/oracle_decode_con_like

Uso de DEFAULT:
 
Se puede utilizar la palabra clave DEFAULT, en lugar del operador de
asignación, para inicializar variables. Por ejemplo, las siguientes
declaraciones:

USO DE CURSORES IMPLICITOS Y EXPLICITOS

Los cursores implicitos se usan con la instrucción SELECT que devuelven un registro
Deben tenerse en cuenta los siguientes puntos cuando se utilizan cursores implicitos:
  • Con cada cursor implicito debe existir la palabra clave INTO.
  • Las variables que reciben los datos devueltos por el cursor tienen que contener el mismo tipo de dato que las columnas de la tabla.
  • Los cursores implicitos solo pueden devolver una única fila. En caso de que se devuelva más de una fila (o ninguna fila) se producirá una excepcion. No se preocupe si aún no sabe que es una excepcion, le valdrá conocer que es el medio por el que PL/SQL gestiona los errores.
declarevdescripcion VARCHAR2(50);
begin      SELECT DESCRIPCION
      INTO vdescripcion
      from PAISES
      WHERE CO_PAIS =
'ESP'      
       dbms_output.put_line(
'La lectura del cursor es: ' || vdescripcion);
end;

Los cursores Explicitos Los cursores explicitos se emplean para realizar consultas SELECT que pueden devolver cero filas, o más de una fila.
Para trabajar con un cursor explicito necesitamos realizar las siguientes tareas:
  • Declarar el cursor (En el bloque de declaraciones).
  • Abrir el cursor con la instrucción OPEN.
  • Leer los datos del cursor con la instrucción FETCH.
  • Cerrar el cursor y liberar los recursos con la instrucción CLOSE.
Ejemplo:

DECLARE
  CURSOR cpaises
  IS
  SELECT CO_PAIS, DESCRIPCION, CONTINENTE
  FROM PAISES;
   co_pais VARCHAR2(3);
  descripcion VARCHAR2(50);
  continente  VARCHAR2(25);
BEGIN
  OPEN cpaises;
  FETCH cpaises INTO co_pais,descripcion,continente;
  CLOSE cpaises;
END;

  tipo_sangre CHAR := 'O';
  valido BOOLEAN := FALSE;

Pero es mejor usar el atributo %ROWTYPE

DECLARE   CURSOR cpaises
  IS
  SELECT CO_PAIS, DESCRIPCION, CONTINENTE
  FROM PAISES;
 
  registro cpaises%ROWTYPE;
BEGIN
  OPEN cpaises;
  FETCH cpaises INTO registro;
  CLOSE cpaises;
END;

Atributos de cursores   

   Toman los valores TRUE, FALSE o NULL dependiendo de la situación:  


Antes de abrirAl abrirDurante la recuperaciónAl finalizar la recuperaciónDespués de cerrar
%NOTFOUNDORA-1001NULLFALSETRUEORA-1001
%FOUNDORA-1001NULLTRUEFALSEORA-1001
%ISOPENFALSETRUETRUETRUEFALSE
%ROWCOUNTORA-10010***ORA-1001


   tipo_sangre CHAR DEFAULT 'O';
   valido BOOLEAN DEFAULT FALSE;
Se utiliza DEFAULT para las variables que tienen un valor típico, mientras que el operador de asignación, se usa en aquellos casos en que las variables no tienen dicho valor, como por ejemplo en contadores y acumuladores. Veamos un ejemplo:

horas_trabajo INTEGER DEFAULT 40;
contador INTEGER:=0;

Uso de NOT NULL:

Además de inponer un valor por defecto a una variable, podemos restringuir a una variable para que sea NOT NULL:
id_acc INTEGER(4) NOT NULL := 9999;

Siempre que se asigne como NOT NULL debemos inicializar la variable a algún valor
id_acc INTEGER(4) NOT NULL; -- Falta la inicialización…
los subtipos NATURALN y POSITIVEN, ya están predefinidos como NOT NULL.

cont_emp NATURAL NOT NULL := 0;
cont_emp NATURALN := 0; -- La sentencia de arriba y esta, son equivalentes…
cont_emp NATURALN; -- Declaración Ilegal, falta la inicialización…

USO DE TYPE


El atributo TYPE se usara para definir un tipo de dato a una variable, usado bastante para declarar una variable a un tipo de dato de una columna de b.d.

Ejm 1:
fechanacimiento globalweb.usuario.fechanacimiento%TYPE;
Con esto definimos a fechanacimiento de tipo de datos a la columna fechanacimiento de la table usuario.

Ejm 2:
           SET SERVEROUTPUT ON SIZE 10000;
              declare
              region regions.region_name%type;
              begin
                      SELECT REGION_NAME INTO region from regions where region_id = 4;
                       dbms_output.put_line(region);
              end;






Seleccionar TOP Filas (Usar la seudocolumna "ROWNUM")

    SELECT employee_id  FROM employees where rownum < 5;

En la siguiente linea devolvemos las 4 primeras tuplas de la tabla employees.

 
 

domingo, 20 de febrero de 2011

Crear un Nuevo Esquema


Hacemos tres sencillos pasos cómo crear un nuevo esquema-usuario de Oracle. Para poder realizar estos pasos es necesario iniciar la sesión en la base de datos con un usuario con permisos de administración, lo más sencillo es utilizar directamente el usuario SYSTEM:
  • Creación de un tablespace para datos y otro para índices. Estos tablespaces son la ubicación donde se almacenarán los objetos del esquema que vamos a crear.
Tablespace para datos, con tamaño inicial de 1024 Mb, y auto extensible
CREATE TABLESPACE "APPDAT" LOGGING
DATAFILE '/export/home/oracle/oradata/datafiles/APPDAT.dbf' SIZE 1024M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
Tablespace para índices, con tamaño inicial de 512 Mb, y auto extensible
CREATE TABLESPACE "APPIDX" LOGGING
DATAFILE '/export/home/oracle/oradata/datafiles/APPIDX.dbf' SIZE 512M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
La creación de estos tablespaces no es obligatoria, pero sí recomendable, así cada usuario de la BD tendrá su propio espacio de datos.
  • Creación del usuario que va a trabajar sobre estos tablespaces, y que será el propietario de los objetos que se se creen en ellos
CREATE USER "APP" PROFILE "DEFAULT" IDENTIFIED BY "APPPWD"
DEFAULT TABLESPACE "APPDAT" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
Si no se especifica un tablespace, la BD le asignará el tablespace USERS, que es el tablespace que se utiliza por defecto para los nuevos usuarios.
Se puede apreciar también que no hay ninguna referencia al tablespace de índices APPIDX que hemos creado. Si queremos mantener datos e índices separados habrá que acordarse de especificar este tablespace en las sentencias de creación de índices de este usuario, si no se hace éstos se crearán en APPDAT:
CREATE INDEX mi_indice ON mi_tabla(mi_campo)
TABLESPACE APPIDX;
  • Sólo falta asignarle los permisos necesarios para trabajar. Si se le asignan los roles 'Connect' y 'Resource' ya tiene los permisos mínimos, podrá conectarse y poder realizar las operaciones más habituales de consulta, modificación y creación de objetos en su propio esquema.
GRANT "CONNECT" TO "APP";
GRANT "RESOURCE" TO "APP";
Completamos la asignación de permisos con privilegios específicos sobre objetos para asegurarnos de que el usuario pueda realizar todas las operaciones que creamos necesarias
GRANT ALTER ANY INDEX TO "APP";
GRANT ALTER ANY SEQUENCE TO "APP";
GRANT ALTER ANY TABLE TO "APP";
GRANT ALTER ANY TRIGGER TO "APP";
GRANT CREATE ANY INDEX TO "APP";
GRANT CREATE ANY SEQUENCE TO "APP";
GRANT CREATE ANY SYNONYM TO "APP";
GRANT CREATE ANY TABLE TO "APP";
GRANT CREATE ANY TRIGGER TO "APP";
GRANT CREATE ANY VIEW TO "APP";
GRANT CREATE PROCEDURE TO "APP";
GRANT CREATE PUBLIC SYNONYM TO "APP";
GRANT CREATE TRIGGER TO "APP";
GRANT CREATE VIEW TO "APP";
GRANT DELETE ANY TABLE TO "APP";
GRANT DROP ANY INDEX TO "APP";
GRANT DROP ANY SEQUENCE TO "APP";
GRANT DROP ANY TABLE TO "APP";
GRANT DROP ANY TRIGGER TO "APP";
GRANT DROP ANY VIEW TO "APP";
GRANT INSERT ANY TABLE TO "APP";
GRANT QUERY REWRITE TO "APP";
GRANT SELECT ANY TABLE TO "APP";
GRANT UNLIMITED TABLESPACE TO "APP";

Ahora el usuario ya puede conectarse y comenzar a trabajar sobre su esquema
Acceso mediante ORACLE 11gr2:

Por defecto Oracle 11 gr2 instala un usuario llamado: system con clave que ustede lo especifica. Y la dirección Web local es: https://enterprise:1158/em o según el puerto que se haya especificado. Para acceder se usa:



Conceptos Fundamentales de Base de Datos Oracle

INSTANCIA

Una instancia de Oracle esta conformada por los procesos de usuario, procesos de instancia y espacios de memoria compartida. ES UN CONJUNTO DE PROCESOS COMPARTIDOS POR USUARIOS
Para permitir el acceso a los datos, Oracle utiliza un conjunto de procesos que son compartidos por todos los usuarios. Además, existen estructuras de memoria que son utilizadas para almacenar los datos más recientemente solicitados a la BD. Una instancia de BD es el conjunto de estructuras de memoria y de procesos que acceden a los ficheros de datos.
Los parámetros que determinan el tamaño y composición de una instancia están almacenados en un fichero llamado init.ora. Este fichero es leído durante el arranque de la BD y puede ser modificado por el DBA. Cualquier modificación de este fichero no tiene efecto hasta la siguiente vez que se arranque la BD.

AREA GLOBAL DEL SISTEMA (SGA)

Es un area de memoria compartida donde se guarda los datos de control y datos de la instancia. Se crea cuando la instancia es levantada y se borra cuando se baja la instancia (shutdown)
Esta conformada por Buffer de Cache (De bloque de datos de Redelogs, sharepoll, cache de biblioteca y                 cache de diccionario.

  PROCESOS DE LA INSTANCIA.          

  DBWR (DataBase Write) Responsable de guardar al inf. en la bd. desde el buffer de cache de datos
  LGWR (Log Write) Responsable de guard la inf. de los buffer de los log de la bd a los redelog.
  CKPT (CheckPoint) Responsable de adv, sobre la ejecucion del DBWR
  PMON (Process Monitor) Responsable de monitorizar los procesos del servidor

¿QUE ES EL SID?

El Sistema de Identificación de Oracle ó Oracle System ID(SID) se utiliza para identificar de forma exclusiva una determinada base de datos en un sistema. Por esta razón, uno no puede tener más de una base de datos con el mismo SID en un sistema informático.


¿QUE ES EL NOMBRE DE LA BASE DE DATOS GLOBAL?
Es el nombre que identifica únicamente a una base de datos dentro de un sistema de bases de datos 
distribuida, por ejemplo:







El nombre de una base de datos está formado a partir de la hoja del árbol y después siguiendo un camino a la raíz. Por ejemplo, la base de datos está en MFG division3 de la rama acme_tools com. El nombre de base de datos mundial para MFG se crea concatenando los nodos en el árbol de la siguiente manera:

mfg.division3.acme_tools.com



ESQUEMAS
Son grupos lógicos de objetos de base de datos (tablas, vistas, paquetes, etc) donde se agrupa para tener una mejor organización

Podemos hallar mas Tips en: DATAPRIX