En aquest tema, explorarem diverses tècniques per optimitzar el codi PL/SQL. L'objectiu és millorar el rendiment i l'eficiència del codi, reduint el temps d'execució i l'ús de recursos. A continuació, es presenten les tècniques clau per a l'optimització del codi PL/SQL.
- Utilitzar Cursors Implícits i Explícits de Manera Eficient
Cursors Implícits
Els cursors implícits són creats automàticament per Oracle per a sentències SQL individuals. Són fàcils d'utilitzar però poden ser menys eficients en operacions complexes.
Cursors Explícits
Els cursors explícits són definits pel programador i ofereixen més control sobre el procés de recuperació de dades. Són útils per a operacions que requereixen un control més fi sobre el conjunt de resultats.
Exemple de Cursor Explícit:
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, last_name FROM employees;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_record.first_name || ' ' || emp_record.last_name);
END LOOP;
CLOSE emp_cursor;
END;
/Explicació:
- DECLARE: Es declara un cursor explícit
emp_cursor. - OPEN: S'obre el cursor.
- FETCH: Es recuperen les dades fila per fila.
- EXIT WHEN: Es surt del bucle quan no hi ha més files.
- CLOSE: Es tanca el cursor.
- Utilitzar la Recollida Massiva (Bulk Collect)
La recollida massiva permet recuperar múltiples files en una sola operació, reduint el nombre de commutacions de context entre PL/SQL i SQL.
Exemple de Recollida Massiva:
DECLARE
TYPE emp_table_type IS TABLE OF employees%ROWTYPE;
emp_table emp_table_type;
BEGIN
SELECT * BULK COLLECT INTO emp_table FROM employees;
FOR i IN emp_table.FIRST .. emp_table.LAST LOOP
DBMS_OUTPUT.PUT_LINE(emp_table(i).first_name || ' ' || emp_table(i).last_name);
END LOOP;
END;
/Explicació:
- TYPE: Es defineix un tipus de taula per emmagatzemar les files.
- BULK COLLECT INTO: Es recuperen totes les files en una sola operació.
- FOR LOOP: Es processen les files recuperades.
- Utilitzar la Sentència FORALL
La sentència FORALL permet executar una operació DML (INSERT, UPDATE, DELETE) en múltiples files amb una sola instrucció, millorant significativament el rendiment.
Exemple de Sentència FORALL:
DECLARE
TYPE emp_id_table IS TABLE OF employees.employee_id%TYPE;
emp_ids emp_id_table := emp_id_table(100, 101, 102);
BEGIN
FORALL i IN emp_ids.FIRST .. emp_ids.LAST
DELETE FROM employees WHERE employee_id = emp_ids(i);
COMMIT;
END;
/Explicació:
- TYPE: Es defineix un tipus de taula per emmagatzemar els identificadors d'empleats.
- FORALL: S'executa una operació DELETE per a cada identificador d'empleat en la taula.
- Evitar l'ús Innecessari de Cursors
L'ús innecessari de cursors pot degradar el rendiment. Utilitza operacions SQL directes sempre que sigui possible.
Exemple d'Operació SQL Directa:
Explicació:
- Es realitza una operació UPDATE directa sense utilitzar un cursor.
- Utilitzar la Funció NOCOPY
La funció NOCOPY pot millorar el rendiment passant paràmetres per referència en lloc de per valor, reduint la sobrecàrrega de còpia de dades.
Exemple d'Ús de NOCOPY:
CREATE OR REPLACE PROCEDURE update_salary (p_salary IN OUT NOCOPY NUMBER) IS
BEGIN
p_salary := p_salary * 1.1;
END;
/Explicació:
- IN OUT NOCOPY: El paràmetre
p_salaryes passa per referència, millorant el rendiment.
Exercicis Pràctics
Exercici 1: Utilitzar Recollida Massiva
Escriu un bloc PL/SQL que utilitzi la recollida massiva per recuperar totes les files de la taula departments i mostri el nom de cada departament.
Solució:
DECLARE
TYPE dept_table_type IS TABLE OF departments%ROWTYPE;
dept_table dept_table_type;
BEGIN
SELECT * BULK COLLECT INTO dept_table FROM departments;
FOR i IN dept_table.FIRST .. dept_table.LAST LOOP
DBMS_OUTPUT.PUT_LINE(dept_table(i).department_name);
END LOOP;
END;
/Exercici 2: Utilitzar la Sentència FORALL
Escriu un bloc PL/SQL que utilitzi la sentència FORALL per actualitzar el salari de tots els empleats en un conjunt d'identificadors d'empleats.
Solució:
DECLARE
TYPE emp_id_table IS TABLE OF employees.employee_id%TYPE;
emp_ids emp_id_table := emp_id_table(100, 101, 102);
BEGIN
FORALL i IN emp_ids.FIRST .. emp_ids.LAST
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_ids(i);
COMMIT;
END;
/Resum
En aquesta secció, hem explorat diverses tècniques per optimitzar el codi PL/SQL, incloent l'ús eficient de cursors, la recollida massiva, la sentència FORALL, l'evitació de l'ús innecessari de cursors i l'ús de la funció NOCOPY. Aquestes tècniques poden ajudar a millorar significativament el rendiment del teu codi PL/SQL. En el proper tema, explorarem les millors pràctiques per escriure codi PL/SQL eficient i mantenible.
Curs de PL/SQL
Mòdul 1: Introducció a PL/SQL
Mòdul 2: Fonaments de PL/SQL
- Estructura del bloc PL/SQL
- Variables i tipus de dades
- Estructures de control
- Cursors
- Gestió d'excepcions