Inicio > ORACLE, PL/SQL > ORACLE PL/SQL Repaso

ORACLE PL/SQL Repaso

Cursores:

	-- Declarar Cursor
	CURSOR emp_cursor IS
		SELECT last_name, department_id
		FROM employees;
	emp_record emp_cursor%ROWTYPE;

	-- Recorrer Secursar
	FOR emp_record IN emp_cursor LOOP --implicitamente abre y recupera la ocurrencia
     	IF emp_record.department_id = 80 THEN
			DBMS_OUTPUT.PUT_LINE(TO_CHAR(emp_record.department_id)||' '||emp_record.last_name);
		END IF;
   	END LOOP; -- implícitamente cierra la ocurrencia

Funciones

CREATE OR REPLACE FUNCTION g_job (p_jobid IN jobs.job_id%TYPE)
RETURN VARCHAR2
IS
	v_jobtitle jobs.job_title%TYPE;
BEGIN
	SELECT job_title
	INTO v_jobtitle
	FROM jobs
	WHERE job_id = p_jobid;
	RETURN (v_jobtitle);
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		RETURN(NULL);
END g_job;
/
VARIABLE g_title VARCHAR2(30)
EXECUTE :g_title:= g_job('SA_REP')
PRINT g_title

Packages

CREATE OR REPLACE PACKAGE job_pack IS
	PROCEDURE add_job (p_job_id IN jobs.job_id%TYPE, p_job_title IN jobs.job_title%TYPE);
	PROCEDURE upd_job (p_job_id IN jobs.job_id%TYPE,p_job_title IN jobs.job_title%TYPE);
	PROCEDURE del_job (p_job_id IN jobs.job_id%TYPE);
	FUNCTION q_job    (p_job_id IN jobs.job_id%TYPE)RETURN VARCHAR2;
END job_pack;
/

CREATE OR REPLACE PACKAGE BODY job_pack IS
	PROCEDURE add_job (p_job_id IN jobs.job_id%TYPE, p_job_title IN jobs.job_title%TYPE) IS BEGIN
		INSERT INTO jobs(job_id, job_title)
		VALUES (p_jobid, p_jobtitle);
	END add_jobs;

	PROCEDURE upd_job (p_job_id IN jobs.job_id%TYPE, p_job_title IN jobs.job_title%TYPE) IS BEGIN
		UPDATE jobs
		SET job_title=p_jobtitle
		WHERE job_id=p_jobid;
		IF SQL%NOTFOUND THEN
			RAISE_APPLICATION_ERROR(-20202,'No Job Updated');
		END IF;
	END upd_job;

	PROCEDURE del_job (p_job_id IN jobs.job_id%TYPE) IS BEGIN
		DELETE FROM jobs
		WHERE job_id = p_job_id;
		IF SQL%NOTFOUND THEN
			RAISE_APPLICATION_ERROR(-20203,'No job deleted');
		END IF;
	END del_job;

	FUNCTION q_job (p_jobid IN jobs.job_id%TYPE) RETURN VARCHAR2 IS
		v_jobtitle jobs.job_title%TYPE;
	BEGIN
		SELECT job_title
		INTO v_jobtitle
		FROM jobs
		WHERE job_id = p_jobid;
		RETURN (v_jobtitle);
	END q_job
END job_pack;
/	


Procedimientos

create or replace procedure procedimiento (p_empid IN employees.employee_id%TYPE, p_sal OUT employees.salary%TYPE, p_job OUT employees.job_id%TYPE)
IS BEGIN
	SELECT salary,job_id
	INTO p_sal, p_job
	FROM employees
	WHERE employee_id = p_empid;
END procedimiento;
/

-- imprimir
VARIABLE g_sal NUMBER
VARIABLE g_job VARCHAR2(15)
EXECUTE procedimiento(120,:g_sal,:g_job)
PRINT g_sal
PRINT g_job


Triggers

create or replace trigger xxx
after update or insert or delete on employees for each row -- After:Antes
	   if updating  then

	elsif inserting then

	elsif deleting then

	end if;
end xxx;
/
Categorías:ORACLE, PL/SQL Etiquetas: , ,
  1. junio 15, 2009 a las 9:15 pm

    Soma, mala tu wea de resumen xD el trigger nunca funcionó jaja

  1. No trackbacks yet.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: