Funciones

12011 Erwin Fischer

Bases de Datos

Unidad

El Lenguaje PL/SQL(Funciones)

22011 Erwin Fischer

Funciones

• Uso de funciones de conversión y expresiones de condicionales

42011 Erwin Fischer

Usando la tabla dual de Oracle

• La tabla dual de Oracle se utiliza cuando se necesita ejecutar una instrucción SQL que no necesariamente tiene una tabla asociada.

• Select user from dual;

52011 Erwin Fischer

Usando dual

• select sysdate from dual;

• ¿Qué día de la semana nació usted?select to_char(to_date(‘09-SEP-1958','dd-mon-yyyy'),'day')

from dual;

62011 Erwin Fischer

Recordando %TYPE

create procedure update_emp (empid in number) is v_empid number; v_fname varchar2(20); v_lname varchar2(30); v_hire date; v_sal number; begin -- Proceso de registrosend;

72011 Erwin Fischer

Recordando %TYPE

create procedure update_emp (empid in number) is v_empid employees.employee_id%type; v_fname employees.first_name%type; v_lname employees.last_name%type; v_hire employees.hire_date%type; v_sal employees.salary%type; begin --Proceso de registrosend;

82011 Erwin Fischer

En Proceso de registros

begin select employee_id,first_name, last_name, hire_date, salary into v_empid, v_fname, v_lname, v_hire, v_sal

from employees where employee_id = empid; --continuar el proceso…end;

92011 Erwin Fischer

Declarando un tipo RECORD

create procedure update_emp (empid in number) is --declare TYPE emp_record_type IS RECORD (v_empid employees.employee_id%type; v_fname employees.first_name%type; v_lname employees.last_name%type; v_hire employees.hire_date%type; v_sal employees.salary%type; ); --instantiate emp_record emp_record_type; begin ...

102011 Erwin Fischer

Finalmente tenemos

Begin select employee_id, first_name, last_name, hire_date, salary

into emp_record from employees where employee_id = empid; -- continuar el proceso…end;


Creando tablas para pruebas

create table tx (x int,y varchar(5));

insert into txselect rownum, trunc(dbms_random.value(1,99999))

from dualconnect by rownum <= 10;

select * from tx;


FUNCIONES DE CONVERSIÓN

TO_CHAR: Transforma un tipo DATE ó NUMBER en una cadena de caracteres. Ejemplos:

to_char(45.31, '99.9') retorna '45.3' to_char(9,125.33, '9,999.99') retorna '9,125.33'to_char(77, '0099') retorna '0077'

TO_DATE: Transforma un tipo NUMBER ó CHAR en DATE. Ejemplo:

to_date('31/10/2007','DD/MM/YYYY‘) retorna ‘31/10/2007’

TO_NUMBER: Transforma una cadena de caracteres en NUMBER. Ejemplo:

to_number('123') retorna 123


FUNCIONES QUE DEVUELVEN VALORES NUMÉRICOS

ASCII(cad)= Devuelve el valor ASCII de la primera letra de la cadena "cad". Ejemplo:

ASCII('R') Retorna 82

INSTR (cad1, cad2 [, comienzo [,m]])= Permite una búsqueda de un conjunto de caracteres en una cadena pero no suprime ningún carácter después. Ejemplo:

INSTR('CORPORATE FLOOR','OR', 3, 2) El resultado obtenido es 14, busca la segunda ocurrencia de la cadena OR a partir de la tercer posición

LENGTH (cad)= Devuelve el numero de caracteres de cad. Ejemplo:

LENGTH(‘HOLA’) retorna 4


Ejemplos

select 'R=',ASCII('R'), 'r=', ASCII('r') from dual;

select INSTR('CORPORATE FLOOR','OR', 3, 2) from dual;

select LENGTH ('HOLA nundo')from dual;


FUNCIONES PARA EL MANEJO DE FECHAS

SYSDATE= Devuelve la fecha del sistema. ADD_MONTHS (fecha, n)= Devuelve la fecha "fecha" incrementada en "n" meses. LAST_DAY (fecha)= Devuelve la fecha del último día del mes que contiene "fecha". MONTHS_BETWEEN (fecha1, fecha2)= Devuelve la diferencia en meses entre las fechas "fecha1" y "fecha2". NEXT_DAY (fecha, “cad”= Devuelve la fecha del primer día de la semana indicado por "cad" después de la fecha indicada por "fecha“, donde cad corresponde al día de la semana EJ: ‘sábado’


select sysdate from dual;

select sysdate, add_months(sysdate, 3) from dual;

select sysdate, last_day(sysdate) from dual;

select months_between(sysdate, '1/1/2011') from dual;

select next_day(sysdate,'sábado') from dual;

Ejemplos de Funciones para el manejo de fechas


FUNCIONES QUE DEVUELVEN VALORES DE CARACTERES

CHR(n) = Devuelve el carácter cuyo valor en binario es equivalente a "n". CONCAT (cad1, cad2)= Devuelve "cad1" concatenada con "cad2". LOWER (cad)= Devuelve la cadena "cad" en minúsculas. UPPER (cad)= Devuelve la cadena "cad" en mayúsculas. INITCAP (cad)= Convierte la cadena "cad" a tipo titulo. LPAD (cad1, n[,cad2])= Añade caracteres a la izquierda de la cadena hasta que tiene una cierta longitud. RPAD (cad1, n[,cad2])= Añade caracteres a la derecha de la cadena hasta que tiene una cierta longitud.

http://www.techonthenet.com/ascii/chart.php


FUNCIONES QUE DEVUELVEN VALORES DE CARACTERES

LTRIM (cad [,set])= Suprime un conjunto de caracteres a la izquierda de la cadena. RTRIM (cad [,set])= Suprime un conjunto de caracteres a la derecha de la cadena. REPLACE (cad, cadena_busqueda [, cadena_sustitucion])= Sustituye un carácter o caracteres de una cadena con 0 o más caracteres. SUBSTR (cad, m [,n])= Obtiene parte de una cadena. TRANSLATE (cad1, cad2, cad3)= Convierte caracteres de una cadena en caracteres diferentes, según un plan de sustitución marcado por el usuario.


ltrim('000123', '0');ltrim('123123Tech', '123');ltrim('123123Tech123', '123'); 'ltrim('xyxzyyyTech', 'xyz');ltrim('6372Tech', '0123456789');rtrim('Techxyxzyyy', 'xyz');rtrim('Tech6372', '0123456789');


FUNCIONES DE GRUPOS DE VALORES

AVG(n)= Calcula el valor medio de "n" ignorando los valores nulos. COUNT (* | Expresión)= Cuenta el número de veces que la expresión evalúa algún dato con valor no nulo. La opción "*" cuenta todas las filas seleccionadas. MAX (expresión)= Calcula el máximo. MIN (expresión)= Calcula el mínimo. SUM (expresión)= Obtiene la suma de los valores de la expresión. GREATEST (valor1, valor2…)= Obtiene el mayor valor de la lista. LEAST (valor1, valor2…)= Obtiene el menor valor de la lista.


ejemplos

select greatest(20, 10, 7, 5) from dual

select least(20, 10, 7, 5) from dual

select max(salary) from employees

select COUNT (*)from employees

select sum(salary) from employees


FUNCIONES DE VALORES SIMPLES

ABS(n)= Devuelve el valor absoluto de (n). CEIL(n)=Obtiene el valor entero inmediatamente superior o igual a "n". FLOOR(n) = Devuelve el valor entero inmediatamente inferior o igual a "n". MOD (m, n)= Devuelve el resto resultante de dividir "m" entre "n". NVL (valor, expresión)= Sustituye un valor nulo por otro valor. POWER (m, exponente)= Calcula la potencia de un numero.


Ej:

select ceil(23.56) from dual

select floor(23.56) from dual

select mod(25,2) from dual

select power(2,3) from dual

select NVL(commission, 0)from sales;


FUNCIONES DE VALORES SIMPLES

ROUND (numero [, m])= Redondea números con el numero de dígitos de precisión indicados. SIGN (valor)= Indica el signo del "valor". SQRT(n)= Devuelve la raíz cuadrada de "n". TRUNC (numero, [m])= Trunca números para que tengan una cierta cantidad de dígitos de precisión. VARIANCE (valor)= Devuelve la varianza de un conjunto de valores.


select ROUND (123.345, 2)from dual

select SIGN (-35.27) from dual

select SQRT(16)from dual

select TRUNC (123.345, 2)from dual

select VARIANCE (salary) from employees


FUNCIONES AVANZADAS

DECODE(expr, value1 [, return1, value2, return2....,] default ):

Traduce una expresión a un valor de retorno. Si expr es igual a value1, la función devuelve Return1. Si expr es igual a value2, la función devuelve Return2. Y así sucesivamente. Si expr no es igual a ningún valor la función devuelve el valor por defecto.


ej

SELECT DEPARTMENT_ID,decode(DEPARTMENT_ID,

60, 'Ventas',70, 'RRHH',80, 'MKT',90, 'Produccion',100, 'Servicios Generales','No definido')

FROM employees;


FUNCIONES AVANZADAS

CASE: La expresión CASE permite utilizar la lógica IF-THEN-ELSE en sentencias SQL sin tener que invocar procedimientos.

CASE expr WHEN comparison_expr1 THEN return_expr1[ WHEN comparison_expr2 THEN return_expr2WHEN comparison_exprn THEN return_exprnELSE else_expr ]END


EJ:

select SALARY,CASE WHEN SALARY < 4000 THEN ‘GANA POCO' WHEN sALARY >= 4000 AND SALARY < 5000 THEN ‘GANA BIEN' ELSE 'GANA MUCHO'ENDfrom EMPLOYEES;


FUNCIONES

CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ] RETURN return_datatypeIS | AS [declaration_section]BEGIN executable_section[EXCEPTION exception_section]END [function_name];


Built-In Functions (By Category)

http://www.techonthenet.com/oracle/functions/index.php


Tarea : Cree la función ParImpar

Escriba una función que reciba un numero entero y Retorne un texto indicando si es ‘par’ o ‘impar’


Unidad - PL/SQL

• Fin

Funciones

Education

Transcript of Funciones