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;

112011 Erwin Fischer

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;

122011 Erwin Fischer

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

132011 Erwin Fischer

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

142011 Erwin Fischer

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;

152011 Erwin Fischer

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’

162011 Erwin Fischer

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

172011 Erwin Fischer

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.

182011 Erwin Fischer

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.

192011 Erwin Fischer

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

202011 Erwin Fischer

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.

212011 Erwin Fischer

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

222011 Erwin Fischer

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.

232011 Erwin Fischer

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;

242011 Erwin Fischer

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.

252011 Erwin Fischer

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

262011 Erwin Fischer

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.

272011 Erwin Fischer

ej

SELECT DEPARTMENT_ID,decode(DEPARTMENT_ID,

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

FROM employees;

282011 Erwin Fischer

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

292011 Erwin Fischer

EJ:

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

302011 Erwin Fischer

FUNCIONES

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

312011 Erwin Fischer

Built-In Functions (By Category)

322011 Erwin Fischer

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’

332011 Erwin Fischer

Unidad - PL/SQL

• Fin