M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T4
T4- Solución : Ineracción con Oracle Server
1) Cree un bloque PL/SQL que seleccione el identificador de departamento superior en la tabla departments y lo almacene en la variable v_max_deptno. Muestre el identificador de departamento superior.
- a) Declare una variable v_max_deptno de tipo NUMBER en la sección de declaraciones.
DECLARE v_max_deptno NUMBER;
- b) Inicie la sección ejecutable con la palabra clave BEGIN e incluya una sentencia SELECT para recuperar el valor máximo de department_id de la tabla departments.
BEGIN SELECT MAX(department_id) INTO v_max_deptno FROM departments;
c) Muestre v_max_deptno y termine el bloque ejecutable.
DBMS_OUTPUT.PUT_LINE('The maximum department_id is : ' || v_max_deptno); END;
d) Ejecute y guarde el script como lab_04_01_soln.sql. La salida de ejemplo es la siguiente:
c) Ejecute una sentencia SELECT para verificar que el bloque PL/SQL ha funcionado.
SELECT * FROM messages;
Resultado: debe ver la siguiente salida:
T4- Sentencies executables
- Exercici 1
- Observa el següent bloc PL/SQL i determina els valors de les variables següents d'acord amb les regles d'àmbit
- DECLARE
- v_weight NUMBER(3):=600;
- v_message VARCHAR2(255):='Product 10012';
- BEGIN
- DECLARE
- v_weight NUMBER(3):=1;
- v_message VARCHAR2(255):='Product 11001';
- v_new_locn VARCHAR2(50):='Europe';
- BEGIN
- v_weight:= v_weight+1;
- v_new_locn:='Western'|| v_new_locn;
- END;
- v_weight:= v_weight+1;
- v_message:=v_message||'is in stock';
- v_new_locn:='Western'|| v_new_locn;
- END;
- a) El valor de v_weight en el subbloc és:
"2" i el tipus de dada és NUMBER.
- b) El valor de v_new_locn en el subbloc és:
"Wetern Europe" i el tipus de dada és VARCHAR2.
- c) El valor de v_weight en el bloc principal és:
"601" i el tipus de dada és NUMBER.
- d) El valor de v_message en el bloc principal és:
"Product 10012 is in stock" i el tipus de dada és VARCHAR2.
- e) El valor de v_new_locn en el bloc principal es
Ilegal perquè v_new_locn no és visible fora del sub-bloc.
- Exercici 2
Suposem que incloem un subbloc en un bloc tal com es mostra tot seguit. Determina els valors en els casos següents:
- DECLARE
- v_customer VARCHAR2(50):='Womansport';
- v_credit_rating VARCHAR2(50):='excellent';
- BEGIN
- DECLARE
- v_customer NUMBER(7):=201;
- v_name VARCHAR2(25):='unisports';
- BEGIN
- vcustomer v_name v_credit_rating
- END;
- vcustomer v_name v_credit_rating
- END;
- a) El valor de v_customer en el subbloc és:
"201" i el tipus de dada és NUMBER.
- b) El valor de v_name en el subbloc és:
"Unisports" i el tipus de dada és VARCHAR2.
- c) El valor de v_credit_rating en el subbloc és:
"EXCELLENT" i el tipus de dada és VARCHAR2.
- d) El valor de v_customer en el bloc principal és:
"Womansport" i el tipus de dada és VARCHAR2.
- e) El valor de v_name en el bloc principal es
V_NAME no és visible en el bloc principal i per tant es veuria un error.
- f) El valor de v_credit_rating en el bloc principal és:
"EXCELLENT" i el tipus de dada és VARCHAR2.
- Exercici 3
Crea i executa un bloc PL/SQL anomenat “T3E3.sql” que accepti dos números mitjançant variables SQL*PLUS. S'ha de dividir el primer número pel segon i després afegir el segon número al resultat. El resultat s'ha d'escriure en una variable PL/SQL i s'ha d'imprimir en pantalla amb una variable SQL*PLUS.
- Please enter the first number: 2
- Please enter the second number: 4
- PL/SQL procedure successfully completed._
- V_RESULT
- --------
- 4.5
SET VERIFY OFF VARIABLE g_result NUMBER ACCEPT p_num1 PROMPT 'Please enter the first number: ' ACCEPT p_num2 PROMPT 'Please enter the second number: ' DECLARE v_num1 NUMBER(9,2) := &p_num1; v_num2 NUMBER(9,2) := &p_num2; BEGIN :g_result := (v_num1/v_num2) + v_num2; END; / PRINT g_result SET VERIFY ON SQL> START p4q3.sql
ACCEPT p_num1 PROMPT 'Please enter the first number: ' ACCEPT p_num2 PROMPT 'Please enter the second number: ' DECLARE v_num1 NUMBER(9,2) := &p_num1; v_num2 NUMBER(9,2) := &p_num2; BEGIN dbms_output.put_line(TO_CHAR(v_num1/v_num2) + v_num2); END; /
- Exercici 4
- Genereu un bloc PL/SQL que calculi la compensació total per un any. El salari anual i el percentatge anual de bonificacions es passaran al bloc PL/SQL mitjançant variables de substitució SQL*PLUS i l'import de bonificació haurà de ser convertit de número enter a decimal (per exemple 15 a 0,15). Si el salari és nul assigneu-li zero abans de calcula la compensació. Utilitzeu la funció NVL per gestionar els valors nuls.
- Please enter the salary amount: 50000
- Please enter the bonus percentage: 10
- PL/SQL procedure successfully completed._
- G_TOTAL
- --------
- 55000
SET VERIFY OFF VARIABLE g_total NUMBER ACCEPT p_salary PROMPT 'Please enter the salary amount: ' ACCEPT p_bonus PROMPT 'Please enter the bonus percentatge: ' DECLARE v_salary NUMBER := &p_salary; v_bonus NUMBER := &p_bonus; BEGIN :g_total := NVL(v_salary,0) * (1 + NVL(v_bonus,0) / 100); END; / PRINT g_total SET VERIFY ON SQL> START p4q4.sql
SET SERVEROUTPUT ON ACCEPT p_salary PROMPT 'Please enter the salary amount: ' ACCEPT p_bonus PROMPT 'Please enter the bonus percentatge: ' DECLARE v_salary NUMBER := &p_salary; v_bonus NUMBER := &p_bonus; BEGIN dbms_output.put_line(TO_CHAR(NVL(v_salary,0) * (1 + NVL(v_bonus,0) / 100))); END; /