SYSDATE |
| SYSDATE returns the number of seconds after midnight. Thus, the maximum value that SYSDATE can return is 86699 seconds. SYSDATE regresa el número de segundos después de media noche. Así el valor máximo que SYSDATE puede regresar es 86699 segundos. |
| Problem 1 |
| Create a Wintempla dialog application called FillNames to insert 10,000 employees in the employee table of the motorola database. Cree una aplicación de Wintempla de diálogo FillNames para insertar 10,000 empleados en la tabla employee de la base de datos de motorola. |
| FillNames.h |
| #pragma once //______________________________________ FillNames.h #include "Resource.h" class FillNames: public Win::Dialog { public: FillNames() { } ~FillNames() { } wstring CreateRandomName(); protected: ... }; |
| FillNames.cpp |
| ... void FillNames::Window_Open(Win::Event& e) { ::srand(::GetTickCount()); const int numNames = 10000; Sql::SqlConnection conn; wstring cmd; int emp_id; int rows = 0; int phone = 0; try { //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase conn.OpenSession(hWnd, CONNECTION_STRING); //___________________________________________________________ DELETE conn.ExecuteNonQuery(L"DELETE FROM emp_skill"); conn.ExecuteNonQuery(L"DELETE FROM employee"); //___________________________________________________________ INSERT for (emp_id = 1; emp_id < numNames; emp_id++, phone++) { Sys::Format(cmd, L"INSERT INTO employee (emp_id, last_name, first_name, phone, dept_id) VALUES(%d, '%s', '%s', '345-678-%04d', 60)", emp_id, CreateRandomName().c_str(), CreateRandomName().c_str(), phone); rows = conn.ExecuteNonQuery(cmd); if (rows != 1) { this->MessageBox(Sys::Convert::ToString(rows), L"Error: number of inserted rows", MB_OK | MB_ICONERROR); } } Sys::Format(cmd, L"INSERT INTO employee (emp_id, last_name, first_name, phone, dept_id) VALUES(%d, '%s', '%s', '345-678-%04d', 60)", emp_id, L"Huerta", L"Alix", phone); rows = conn.ExecuteNonQuery(cmd); if (rows != 1) { this->MessageBox(Sys::Convert::ToString(rows), L"Error: number of inserted rows", MB_OK | MB_ICONERROR); } } catch (Sql::SqlException e) { this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR); } } wstring FillNames::CreateRandomName() { const int len = 2 + ( 6*rand() )/ RAND_MAX; const int numChar = 'Z' - 'A' +1; wstring randomName; randomName.resize(len); int randomNumber; for (int i = 0; i < len; i++) { randomNumber = (numChar * rand()) / RAND_MAX; if (i == 0) { randomName[i] = 'A' + randomNumber; } else { randomName[i] = 'a' + randomNumber; } } return randomName; } |
| Problem 2 |
| motorola > Modify the motorola.sql script to add p_emp stored procedure. Discuss and execute the stored procedure using: (a) Oracle, (b) Microsoft SQL Server. Modifique el script motorola.sql para agregar el procedimiento almacenado p_emp. Discuta y ejecute el procedimiento almacenado usando: (a) Oracle, (b) Microsoft SQL Server. |
| motorola.sql |
| -- _______________________________________________________ Oracle CREATE OR REPLACE PROCEDURE p_emp IS p_last_name VARCHAR2(20); p_count INTEGER:=0; MAX_VALUE CONSTANT INTEGER:=10000; BEGIN FOR p_count IN 1..MAX_VALUE LOOP SELECT last_name INTO p_last_name FROM employee WHERE last_name='Huerta'; END LOOP; END p_emp; / -- ________________________________________________________ Microsoft SQL Server CREATE PROCEDURE p_emp AS DECLARE @p_last_name NVARCHAR(25); DECLARE @p_count INT; DECLARE @MAX_VALUE INT; -- _________________________ SET @p_count = 0; SET @MAX_VALUE = 10000; BEGIN WHILE (@p_count < @MAX_VALUE) BEGIN SELECT @p_last_name = last_name FROM employee WHERE apellido='HUERTA'; END END GO |
| Problem 3 |
| motorola > Create the mot_emp.sql script in Oracle as shown below. Discuss and execute the stored procedure. Cree el script mot_emp.sql en Oracle como se muestra debajo. Discuta y ejecute el procedimiento almacenado. |
| mot_emp.sql |
| -- DROP INDEX indx_employee_last_name; TIMING START; EXECUTE p_emp; TIMING STOP; CREATE INDEX indx_employee_last_name ON employee(last_name); TIMING START; EXECUTE p_emp; TIMING STOP; |
| MSDOS: cmd.exe |
| SQL> @C:\mot_emp.sql PL/SQL procedure successfully completed. Elapsed: 00:00:00.32 Index created. PL/SQL procedure successfully completed. Elapsed: 00:00:00.18 |
| Tip |
| You cannot use SYSDATE to measure runtime if the procedure runtime crosses over midnight. No es posible usar SYSDATE para medir el tiempo de ejecución si el procedimiento corre a través de media noche. |
| Problem 4 |
| motorola > Modify the motorola.sql script to add p_motem stored procedure. Discuss and execute the stored procedure. When done, do not forget to restore the database running the original SQL script of the database. Using: (a) Oracle, (b) Microsoft SQL Server. Modifique el script motorola.sql para agregar el procedimiento almacenado p_ motem. Discuta y ejecute el procedimiento almacenado. Cuando termine, no se olvide de restaurar la base de datos ejecutando el script de SQL de la base de datos. Usando: (a) Oracle, (b) Microsoft SQL Server. |
| motorola.sql (Oracle) |
| CREATE OR REPLACE PROCEDURE p_motem IS p_last_name VARCHAR2(20); p_count INTEGER:=0; MAX_VALUE CONSTANT INTEGER:=100000; p_start_time CHAR(5):=0; p_end_time CHAR(5):=0; p_duration NUMBER(10,5):=0; BEGIN -- We execute 100 000 SELECT statements p_start_time:=TO_CHAR(SYSDATE, 'SSSSS'); FOR p_count IN 1..MAX_VALUE LOOP SELECT last_name INTO p_last_name FROM employee WHERE last_name='Huerta'; END LOOP; p_end_time:=TO_CHAR(SYSDATE, 'SSSSS'); p_duration:=TO_NUMBER(p_end_time)-TO_NUMBER(p_start_time); dbms_output.put_line('Running time is: ' || p_duration || ' sec'); END p_motem; / |
| MSDOS: cmd.exe |
| SQL> DROP INDEX indx_employee_last_name; Index dropped. SQL> SET SERVEROUTPUT ON; SQL> EXECUTE p_motem; Running time is: 4 sec PL/SQL procedure successfully completed. SQL> CREATE INDEX indx_employee_last_name 2 ON employee(last_name); Index created. SQL> EXECUTE p_motem; Running time is: 2 sec PL/SQL procedure successfully completed. |