Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T4»

Revisió del 14:01, 9 març 2017

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:

2) Modifique el bloque PL/SQL creado en el paso 1 para insertar un nuevo departamento en la tabla departments.

a) Cargue el script lab_04_01_soln.sql. Declare dos variables:

v_dept_name de tipo departments.department_name y

v_dept_id de tipo NUMBER

Asigne ‘Education’ a v_dept_name en la sección de declaraciones.

   v_dept_name departments.department_name%TYPE:= 'Education';
   v_dept_id NUMBER;

b) Ya ha recuperado el número de departamento superior actual de la tabla departments. Agréguele 10 y asigne el resultado a v_dept_id.

   v_dept_id := 10 + v_max_deptno;

c) Incluya una sentencia INSERT para insertar datos en las columnas department_name, department_id y location_id de la tabla departments.

Utilice valores en dept_name y dept_id para department_name y department_id, respectivamente, y utilice NULL para location_id.

...
   INSERT INTO departments (department_id, department_name, location_id)
   VALUES (v_dept_id, v_dept_name, NULL);

d) Utilice el atributo SQL SQL%ROWCOUNT para mostrar el número de filas que se ven afectadas.

   DBMS_OUTPUT.PUT_LINE (' SQL%ROWCOUNT gives ' || SQL%ROWCOUNT);
   ...

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;
/