FOR Loops |
The FOR loop is used to repeat a group of statements a specified number of times. The FOR loop uses a counter variable to count the number of iterations. The counter is incremented starting from the lower limit specified or decremented starting from the upper limit specified at the end of each iteration or loop. If it is out of the range the looping stops However, this use of FOR is not very common in PL/SQL. El comando FOR es usado para repetir un grupo de sentencias específicas un número conocido de veces. El comando FOR usa una variable para contar el número de repeticiones. Este contador inicia en el límite inferior especificado y aumenta o disminuye su valor según se indique. Cuando el contador alcanza el límite superior especificado el bloque FOR se detiene. Sin embargo, este uso de FOR no es el más común en PL/SQL. |
Problem 1 |
kimberly > Create and test the following stored procedure using Oracle. Cree y pruebe el siguiente procedimiento almacenado usando Oracle. |
MSDOS: cmd.exe |
SQL> CREATE OR REPLACE PROCEDURE p_for 2 ( 3 in_max NUMBER 4 ) 5 IS 6 p_index NUMBER(4, 0); 7 BEGIN 8 FOR p_index IN 1..in_max 9 LOOP 10 dbms_output.put_line(p_index); 11 END LOOP; 12 END p_for; 13 / Procedure created. SQL> SET SERVEROUTPUT ON; SQL> EXECUTE p_for(5); 1 2 3 4 5 PL/SQL procedure successfully completed. |
WHILE |
This command repeats a block of commands an unknown number of times until a condition is met. This command is useful when the number of times that the block will be executed is not known or when it is not desired to calculate it. Este comando permite repetir la ejecución de un bloque de comandos un número descocido de veces hasta que una condición se cumpla. Este comando es útil cuando no se conoce o no se desea calcular el número de veces que se repetirá el bloque. |
Tip |
A WHILE loop is useful for situations when the number of loop iterations is not known in advance. El bloque WHILE es útil en situaciones cuando el número de veces que se repetirá el bloque no es conocido con anterioridad. |
Tip |
Microsoft SQL Server supports the FOR sentence for other purposes rather than repeating a block of code, you must use the WHILE sentence to repeat a block of code. Microsoft SQL Server soporta el comando FOR para otros propósitos diferentes al de repetir un bloque de código, usted debe usar el comando WHILE para repetir un bloque de instrucciones. |
Problem 2 |
kimberly > Repeat the previous problem using Microsoft SQL Server and the WHILE sentence. Do not forget that the blocks of code are defined using BEGIN and END. Repita el problema anterior usando Microsoft SQL Server y la sentencia WHILE. No olvide que los bloques de código están definidos usando BEGIN y END. |
Microsoft SQL Server |
USE kimberly; EXECUTE p_while 5; |
Output |
1 2 3 4 5 |
Problem 3 |
kimberly > Create and test the following stored procedure using Oracle. Cree y pruebe el siguiente procedimiento almacenado usando Oracle. |
MSDOS: cmd.exe |
SQL> CREATE OR REPLACE PROCEDURE p_rev 2 ( 3 in_max NUMBER 4 ) 5 IS 6 p_index NUMBER(4, 0); 7 BEGIN 8 FOR p_index IN REVERSE 3..in_max 9 LOOP 10 dbms_output.put_line(p_index); 11 END LOOP; 12 END p_rev; 13 / Procedure created. SQL> EXECUTE p_rev(6); 6 5 4 3 PL/SQL procedure successfully completed. |
Problem 4 |
kimberly > Repeat the previous problem using Microsoft SQL Server and the WHILE sentence. Repita el problema anterior usando Microsoft SQL Server y la sentencia WHILE. |
Microsoft SQL Server |
USE kimberly; EXECUTE p_rev 6; |
Output |
6 5 4 3 |
Problem 5 |
kimberly > Create a stored procedure called p_filltemp to fill the temperature table. The procedure takes three parameters: the first temperature value, the increment, and the number of temperatures. The procedure must delete previous values in the temperature table. Using: (a) Oracle (Use: SAVEPOINT and COMMIT). (b) Microsoft SQL Server (Use: BEGIN TRANSACTION and COMMIT TRANSACTION.). Cree un procedimiento almacenado llamado p_filltemp para llenar la tabla temperature. El procedimiento toma tres parámetros: el primer valor de temperatura, un incremento y el número de temperaturas. El procedimiento debe borrar los valores previos en la tabla temperature. Usando: (a) Oracle (Use: SAVEPOINT y COMMIT). (b) Microsoft SQL Server (Use: BEGIN TRANSACTION y COMMIT TRANSACTION). |
MSDOS: cmd.exe |
SQL> EXECUTE p_filltemp(-40, 5, 6); PL/SQL procedure successfully completed. SQL> SELECT temperature_id, 2 value 3 FROM temperature; TEMPERATURE_ID VALUE -------------- ---------- 1 -40 2 -35 3 -30 4 -25 5 -20 6 -15 |
Microsoft SQL Server |
USE kimberly; EXECUTE p_filltemp -40, 5, 6; |
Output |
(4 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) |
Microsoft SQL Server |
SELECT temperature_id, value FROM temperature; |
Problem 6 |
kimberly > Test the function to_celsius previously created in the temperature table. Using: (a) Oracle. (b) Microsoft SQL Server. Pruebe la función to_celsius que se implementó previamente en la tabla temperature. Usando: (a) Oracle. (b) Microsoft SQL Server. |
MSDOS: cmd.exe |
SQL> SELECT temperature_id, 2 value, 3 to_celsius(value) AS celsius 4 FROM temperature; TEMPERATURE_ID VALUE CELSIUS -------------- ---------- ---------- 1 -40 -40 2 -35 -37.22 3 -30 -34.44 4 -25 -31.67 5 -20 -28.89 6 -15 -26.11 6 rows selected. |
Microsoft SQL Server |
SELECT temperature_id, value, dbo.to_celsius(value) AS celsius FROM temperature; |
Problem 7 |
kimberly > Test the following stored procedure that illustrates how to use WHILE in Oracle Pruebe el siguiente procedimiento almacenado que ilustra cómo usar WHILE en Oracle. |
MSDOS: cmd.exe |
SQL> CREATE OR REPLACE PROCEDURE p_while 2 IS 3 required_amount CONSTANT NUMBER(10, 2) := 5500; 4 total NUMBER(10, 2) := 0; 5 balance NUMBER(10, 2) := 500; 6 BEGIN 7 WHILE (total < required_amount) 8 LOOP 9 total := total+balance; 10 dbms_output.put_line(total); 11 END LOOP; 12 END p_while; 13 / Procedure created. SQL> SET SERVEROUTPUT ON; SQL> EXECUTE p_while; 500 1000 1500 2000 2500 3000 3500 4000 4500 5000 5500 PL/SQL procedure successfully completed. |
Problem 8 |
kimberly > Repeat the previous problem using Microsoft SQL Server. Repita el problema anterior usando Microsoft SQL Server. |
Microsoft SQL Server |
USE kimberly; EXECUTE p_while; |
Output |
500.00 1000.00 1500.00 2000.00 2500.00 3000.00 3500.00 4000.00 4500.00 5000.00 5500.00 |
Problem 9 |
kimberly > Modify the kimberly.sql script to create the pi_value table and the p_pi stored procedure. The procedure takes an integer values indicating the number of rows to be inserted, and inserts the values in the series. You must call you stored procedure directly from the kimberly.sql file to fill the table with 1000 values. Using: (a) Oracle, (b) Microsoft SQL Server. Modifique el script kimberly.sql a fin de crear la tabla pi_value y el procedimiento p_pi. El procedimiento toma un entero indicando cuantos renglones se desea insertar, e inserta los valores mostrados en la serie. Llame directamente el procedimiento desde del archivo kimberly.sql para llenar la tabla con 1000 valores. Usando: (a) Oracle, (b) Microsoft SQL Server. |
MSDOS: cmd.exe |
SQL> SELECT pi_value_id, 2 valuex 3 FROM pi_value 4 WHERE pi_value_id < 10; PI_VALUE_ID VALUE ----------- ---------- 1 1 2 .333333333 3 .2 4 .142857143 5 .111111111 6 .090909091 7 .076923077 8 .066666667 9 .058823529 9 rows selected. |
Microsoft SQL Server |
SELECT pi_value_id, value FROM pi_value WHERE pi_value_id < 22; |
Problem 10 |
kimberly > Create the p_pineg stored procedure to produce the series shown. Using: (a) Oracle, (b) Microsoft SQL Server. Cree el procedimiento almacenado p_pineg para producir la serie mostrada. Usando: (a) Oracle, (b) Microsoft SQL Server. |
MSDOS: cmd.exe |
SQL> SELECT pi_value_id, 2 value 3 FROM pi_value 4 WHERE pi_value_id < 22; PI_VALUE_ID VALUE ----------- ---------- 1 1 2 -.33333333 3 .2 4 -.14285714 5 .111111111 6 -.09090909 7 .076923077 8 -.06666667 9 .058823529 10 -.05263158 11 .047619048 PI_VALUE_ID VALUE ----------- ---------- 12 -.04347826 13 .04 14 -.03703704 15 .034482759 16 -.03225807 17 .03030303 18 -.02857143 19 .027027027 20 -.02564103 21 .024390244 21 rows selected. |
Microsoft SQL Server |
SELECT pi_value_id, value FROM pi_value WHERE pi_value_id < 22; |
Problem 11 |
kimberly > The summation of the series of the previous problem is equal to π/4. Create the p_pita store procedure to compute the value of π. The procedure takes the number of terms in the series (which should be less than 1000) and prints the approximate value of π. Using: (a) Oracle, (b) Microsoft SQL Server. La suma de los términos de la serie del problema anterior es igual a π/4. Cree el procedimiento p_pita que usa la tabla pi_value para calcular el valor de π como se muestra. El procedimiento toma como entrada el número de términos a usarse en la serie para realizar el cálculo (el cual debe ser menor a 1000) e imprime el valor aproximado de π. Usando: (a) Oracle, (b) Microsoft SQL Server. |
MSDOS: cmd.exe |
SQL> SET SERVEROUTPUT ON; SQL> EXECUTE p_pita(100); 3.131592904 PL/SQL procedure successfully completed. SQL> EXECUTE p_pita(500); 3.139592656 PL/SQL procedure successfully completed. SQL> EXECUTE p_pita(1000); 3.140592632 PL/SQL procedure successfully completed. |
Microsoft SQL Server |
EXECUTE p_pita 100; |
Output |
3.1315929040 |
Microsoft SQL Server |
EXECUTE p_pita 500; |
Output |
3.1395926560 |
Microsoft SQL Server |
EXECUTE p_pita 1000; |
Output |
3.1405926320 |
Problem 12 |
kimberly > (a) Create the get_term function as shown. (b) Use the get_term function to create the function wallis_pi to compute the value of π using Wallis product as shown below. Use Oracle and test your function using the DUAL table. Do not use the pi_value table previously created. (a) Cree la función get_term como se muestra. (b) Use la funcion get_term para crear la función wallis_pi para calcular el valor de π usando el producto de Wallis como se muestra debajo. Use Oracle y pruebe su función usando la tabla DUAL. No use la tabla pi_value que se creó previamente. |
MSDOS: cmd.exe |
SQL> SELECT get_term(1) FROM dual; GET_TERM(1) ----------- .66666667 SQL> SELECT get_term(2) FROM dual; GET_TERM(2) ----------- 1.33333333 SQL> SELECT get_term(3) FROM dual; GET_TERM(3) ----------- .8 SQL> SELECT get_term(4) FROM dual; GET_TERM(4) ----------- 1.2 SQL> SELECT get_term(5) FROM dual; GET_TERM(5) ----------- .85714286 SQL> SELECT get_term(6) FROM dual; GET_TERM(6) ----------- 1.14285714 SQL> SELECT get_term(7) FROM dual; GET_TERM(7) ----------- .88888889 SQL> SELECT get_term(8) FROM dual; GET_TERM(8) ----------- 1.11111111 SQL> SELECT wallis_pi(5000) FROM dual; WALLIS_PI(5000) -------------- 3.14190524 SQL> SELECT wallis_pi(10000) FROM dual; WALLIS_PI(10000) --------------- 3.14174764 SQL> SELECT wallis_pi(50000) FROM dual; WALLIS_PI(50000) --------------- 3.1416208 |
Problem 13 |
kimberly > Repeat the previous problem using Microsoft SQL Server. Create the DUAL table using the code below, then, test the function. Repita el problema anterior usando Microsoft SQL Server. Cree la tabla DUAL usando el código de abajo, entonces, pruebe la función. |
Microsoft SQL Server |
CREATE TABLE DUAL ( DUMMY VARCHAR(1) ) GO INSERT INTO DUAL (DUMMY) VALUES ('X') GO |
Microsoft SQL Server |
SELECT dbo.get_term(0) AS term FROM dual; |
Microsoft SQL Server |
SELECT dbo.get_term(1) AS term FROM dual; |
Microsoft SQL Server |
SELECT dbo.get_term(2) AS term FROM dual; |
Microsoft SQL Server |
SELECT dbo.get_term(3) AS term FROM dual; |
Microsoft SQL Server |
SELECT dbo.get_term(4) AS term FROM dual; |
Microsoft SQL Server |
SELECT dbo.get_term(5) AS term FROM dual; |
Microsoft SQL Server |
SELECT dbo.wallis_pi(50000) AS term FROM dual; |
Microsoft SQL Server |
SELECT dbo.wallis_pi(100000) AS term FROM dual; |
Problem 14 |
kimberly > Modify the kimberly.sql file to create the fact_value table. Then, write a stored procedure to populate the fact_val Table. The first row should have the factorial of 1, the second row the factorial of 2, and so on. The procedure must take the number of rows to be inserted in the table. The procedure must delete previous rows in the table. Using: (a) Oracle, (b) Microsoft SQL Server. Modifique el archivo kimberly.sql para crear la tabla fact_value. Escriba entonces el procedimiento p_fact para llenar la tabla fact_value. El primer renglón debe tener el factorial de 1, el segundo renglón el factorial de 2, etc. El procedimiento toma el número de renglones que se insertará en la tabla. El procedimiento debe borrar los renglones previamente insertados a la tabla. Usando: (a) Oracle, (b) Microsoft SQL Server. |
MSDOS: cmd.exe |
SQL> EXECUTE p_fact(10); PL/SQL procedure successfully completed. SQL> SELECT factorial_id, 2 value 3 FROM fact_value; FACTORIAL_ID VALUE ------------ ---------- 1 1 2 2 3 6 4 24 5 120 6 720 7 5040 8 40320 9 362880 10 3628800 |
Microsoft SQL Server |
USE kimberly; EXECUTE p_fact 10; SELECT factorial_id, value FROM fact_value; |