PhD. José Saúl de Lira Miramontes.
Universidad Autónoma de Chihuahua
Facultad de Ingeniería
Alumno: PTBI Jesús José Navarrete Baca (301429)
Grupo: 7HW1
Actividad: Tarea 9 (Oracle Dynamic Sql)
Fecha: 26/09/2018
~EJERCICIO #1
1. Crear una función almacenada que verifique la existencia de un registro. Se le pasa como
parámetro el nombre de la tabla y el atributo por el cual se hace la verificación. Regresa un
valor true si existe el registro .
CREATE OR REPLACE FUNCTION EXISTREG(TN VARCHAR2, AN VARCHAR2) RETURN
BOOLEAN AS
TYPE CCURC IS REF CURSOR;
MYCUR CCURC;
SQLQ VARCHAR2(200) := 'SELECT '||AN||' FROM '||TN;
FLAG BOOLEAN:=FALSE;
BEGIN
BEGIN
OPEN MYCUR FOR SQLQ;
LOOP
FLAG := NOT FLAG;
EXIT;
END LOOP;
CLOSE MYCUR;
EXCEPTION WHEN OTHERS THEN NULL;
END;
RETURN FLAG;
END;
CREATE OR REPLACE FUNCTION EXISTREGVAL(TN VARCHAR2, AN VARCHAR2, INS
VARCHAR2) RETURN BOOLEAN AS
FLAG BOOLEAN := FALSE;
SQLQ VARCHAR2(200):='SELECT COUNT(*) FROM(SELECT '||AN||' FROM
'||TN||' WHERE '||AN||' = '''||INS||''')';
PIVO INTEGER := 0;
BEGIN
BEGIN
EXECUTE IMMEDIATE SQLQ INTO PIVO;
EXCEPTION WHEN OTHERS THEN NULL;
END;
IF PIVO <> 0 THEN
FLAG := NOT FLAG;
END IF;
RETURN FLAG;
END;
CREATE OR REPLACE PROCEDURE ECH(TXT VARCHAR2) AS BEGIN
DBMS_OUTPUT.PUT_LINE(TXT); END;
DECLARE
BEGIN
IF EXISTREGVAL('REGIONS','REGION_NAME','Americas') THEN
ECH('Existe');
0
ELSE
ECH('No Existe');
END IF;
IF EXISTREG('REGIONS','LATITUDE') THEN
ECH('Existe');
ELSE
ECH('No Existe');
END IF;
END;
1
~EJERCICIO #2
2. Crear un procedimiento almacenado que despliegue los datos de los empleados de un
departamento (employee_id, first_name, manager_id), pasar como parámetro el atributo por
el cual se ordenan los registros y el tipo de ordenamiento (1 = Asc 2= Desc). Tabla: Employees.
CREATE OR REPLACE PROCEDURE SHOWEMPS_DPTO(ATTB VARCHAR2, ORDERTYPE
INTEGER) AS
TYPE XCUR IS REF CURSOR;
WRAPPERTYPE VARCHAR2(10):= 'ASC';
CUR XCUR;
SQLQ VARCHAR2(200):= 'SELECT * FROM EMPLOYEES ORDER BY '||ATTB||' ';
TMP EMPLOYEES%ROWTYPE;
BEGIN
IF ORDERTYPE = 1 THEN
WRAPPERTYPE := 'ASC';
ELSIF ORDERTYPE = 2 THEN
WRAPPERTYPE := 'DESC';
END IF;
SQLQ := SQLQ || WRAPPERTYPE;
ECH('EID FIRST NAME MANAGER ID');
OPEN CUR FOR SQLQ;
LOOP
FETCH CUR INTO TMP;
EXIT WHEN CUR%NOTFOUND;
TMP.FIRST_NAME := RPAD(TMP.FIRST_NAME,12);
ECH(TMP.EMPLOYEE_ID||' '||TMP.FIRST_NAME||' '||TMP.MANAGER_ID);
2
END LOOP;
EXCEPTION WHEN OTHERS THEN ECH(SQLERRM);
END;
BEGIN
SHOWEMPS_DPTO('SALARY', 1);
SHOWEMPS_DPTO('EMPLOYEE_ID', 2);
END;
3
~EJERCICIO #3
Crear un procedimiento almacenado que reciba como parámetro el nombre de una
tabla, y muestre la estructura de dicha tabla (atributos y tipos).
*FORM 1
CREATE OR REPLACE PROCEDURE SHOWSTRUCT(TN VARCHAR2) AS
TYPE CUSTOM_RECORD IS RECORD(
CNAME ALL_TAB_COLUMNS.COLUMN_NAME%TYPE,
CTYPE ALL_TAB_COLUMNS.DATA_TYPE%TYPE
);
TYPE CVARRAY IS TABLE OF CUSTOM_RECORD;
ARR CVARRAY;
WIDTH INTEGER := 25;
BEGIN
BEGIN
SELECT COLUMN_NAME, DATA_TYPE BULK COLLECT INTO ARR FROM
ALL_TAB_COLUMNS WHERE TABLE_NAME = TN;
FOR I IN ARR.FIRST .. ARR.LAST LOOP
ARR(I).CNAME := RPAD(ARR(I).CNAME,WIDTH);
ARR(I).CTYPE := RPAD(ARR(I).CTYPE,WIDTH);
ECH('-> '||ARR(I).CNAME||' | -> '||ARR(I).CTYPE||'|');
END LOOP;
EXCEPTION WHEN OTHERS THEN NULL;
END;
END;
BEGIN
FOR X IN (SELECT TABLE_NAME A FROM USER_TABLES) LOOP
ECH('***** >>Tabla: '||X.A||'*****');
4
SHOWSTRUCT(X.A);
ECH(' ');
END LOOP;
END;
*FORM 2
CREATE OR REPLACE PROCEDURE SHOWSTRUCT2(TNAME VARCHAR2) AS
TYPE XCUR IS REF CURSOR;
CUR XCUR;
SQLQ VARCHAR2(100):= 'SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME =
:K';
TMP ALL_TAB_COLUMNS%ROWTYPE;
BEGIN
OPEN CUR FOR SQLQ USING TNAME;
LOOP
FETCH CUR INTO TMP;
EXIT WHEN CUR%NOTFOUND;
ECH(TMP.COLUMN_NAME||' '||TMP.DATA_TYPE);
END LOOP;
EXCEPTION WHEN OTHERS THEN ECH(SQLERRM);
END;
BEGIN
SHOWSTRUCT2('EMPLOYEES');
END;
5
6
~EJERCICIO #4
Crear un procedimiento almacenado que permita agregar una columna a una
tabla, se pasa como parámetro el nombre de la tabla, nombre de la columna
nueva y el tipo de dato de dicha columna.
CREATE OR REPLACE PROCEDURE ADDCOLUMN(TN VARCHAR2, CN VARCHAR2, TYPENAME
VARCHAR2) AS
SQLQ VARCHAR2(200) := 'ALTER TABLE '||TN||' ADD '||CN||' '||TYPENAME;
BEGIN
EXECUTE IMMEDIATE SQLQ;
ECH('*Columna '||TN||' Agregada satisfactoriamente...');
EXCEPTION WHEN OTHERS THEN ECH(SQLERRM);
END;
CREATE OR REPLACE PROCEDURE DELCOLUMN(TN VARCHAR2, CN VARCHAR2) AS
SQLQ VARCHAR(200) := 'ALTER TABLE '||TN||' DROP COLUMN '||CN;
BEGIN
EXECUTE IMMEDIATE SQLQ;
ECH('*Columna '||TN||' Eliminada satisfactoriamente...');
EXCEPTION WHEN OTHERS THEN ECH(SQLERRM);
END;
BEGIN
ADDCOLUMN('XTEST','APELLIDO','VARCHAR2(30)');
ADDCOLUMN('XTEST','ALIAS','VARCHAR2(30)');
ADDCOLUMN('XTEST','SALARIO','NUMBER(9,2)');
SHOWSTRUCT('XTEST');
DELCOLUMN('XTEST','APELLIDO');
DELCOLUMN('XTEST','ALIAS');
DELCOLUMN('XTEST','SALARIO');
SHOWSTRUCT('XTEST');
END;
7
8
~EJERCICIO #5
Crear un procedimiento almacenado que permita crear una copia de una tabla,
el procedimiento recibe como parámetro el nombre de la tabla a copiar y el
nombre de la nueva tabla.
CREATE OR REPLACE PROCEDURE DUPLICATE_TABLE(TN VARCHAR2, TNNEW VARCHAR2)
AUTHID CURRENT_USER AS
SQLQ VARCHAR2(200) := 'CREATE TABLE '||TNNEW||' AS SELECT * FROM '||TN;
BEGIN
EXECUTE IMMEDIATE SQLQ;
ECH('Se ha creado una copia de la tabla '||TN||' -> '||TNNEW);
EXCEPTION WHEN OTHERS THEN ECH(SQLERRM);
END;
CREATE OR REPLACE PROCEDURE DUPLICATE_SCHEMA_TABLE(TN VARCHAR2, TNNEW
VARCHAR2) AUTHID CURRENT_USER AS
SQLQ VARCHAR2(200) := 'CREATE TABLE '||TNNEW||' AS SELECT * FROM '||TN
||' WHERE 1 <> 1';
BEGIN
EXECUTE IMMEDIATE SQLQ;
ECH('Se ha creado una copia de la tabla '||TN||' -> '||TNNEW);
EXCEPTION WHEN OTHERS THEN ECH(SQLERRM);
END;
BEGIN
DUPLICATE_TABLE('XTEST','XTEST2');
DUPLICATE_SCHEMA_TABLE('EMPLOYEES','NEWEMPS');
END;
9
10
