SQL from a Stored Procedure

Problem 1
kimberly > Create and populate the kimberly database by writing the kimberly.sql script, use: (a) Oracle, (b) Microsoft SQL Server.
Cree y llene la base de datos kimberly escribiendo el script kimberly.sql, use: (a) Oracle, (b) Microsoft SQL Server.

Problem 2
kimberly > The code below shows how to execute a SQL command from a stored procedure in Oracle. The name column of the SELECT command is stored in the stored procedure variable: p_name.
El código de abajo muestra como ejecutar un comando de SQL desde un procedimiento almacenado en Oracle. La columna name del comando SELECT se almacena en la variable p_name del procedimiento almacenado.

kimberly.sql
CREATE OR REPLACE PROCEDURE p_select
IS
p_name VARCHAR2(30);
BEGIN
SELECT name INTO p_name FROM item
WHERE height=12;
dbms_output.put_line(p_name || ' has a height of 12');
END p_select;
/

MSDOS: cmd.exe
SQL> connect kimberly
Enter password:
Connected.
SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE p_select;
Table has a height of 12

PL/SQL procedure successfully completed.


Tip
In Microsoft SQL Server the = operator is used instead of using INTO. You may copy several values (from a SELECT statement) to stored procedure variables as shown in the example below.
En Microsoft SQL Server el operador de = se usa en lugar de INTO. Usted puede copiar varios valores (desde un comando SELECT) a variables de un procedimiento almacenado como se muestra en el ejemplo de abajo.

severalOracle.sql
CREATE OR REPLACE PROCEDURE p_my
(
     in_item_id INTEGER
)
IS
     p_name VARCHAR2(10);
     p_width DECIMAL(10, 4);
BEGIN
     SELECT name, width
     INTO p_name, p_width
     FROM item
     WHERE item_id = in_item_id;
     ...
END p_my;
/


severalMicrosoft.sql
CREATE PROCEDURE p_my
(
     @in_object_id INTEGER
)
AS
     DECLARE @name NVARCHAR(10);
     DECLARE @width DECIMAL(10, 4);
BEGIN
SELECT @name=name, @width=width
FROM item
WHERE item_id = @in_object_id;
...
END
GO


Problem 3
kimberly > Repeat the previous problem using Microsoft SQL Server.
Repita el problema previo usando Microsoft SQL Server.

Microsoft SQL Server
USE kimberly;
EXECUTE p_select;

Output
Table has a height of 12


Problem 4
kimberly > Create a stored procedure to show the volume of an item given the item_id. Using: (a) Oracle, (b) Microsoft SQL Server.
Cree un procedimiento almacenado para mostrar el volumen de un artículo dado su item_id. Usando: (a) Oracle, (b) Microsoft SQL Server.

MSDOS: cmd.exe
SQL> connect kimberly
Enter password:
Connected.
SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE p_volu(11);
The TV volume is 70

PL/SQL procedure successfully completed.

Microsoft SQL Server
USE kimberly;
EXECUTE p_volu 11;

Output
The TV volume is 70.0000

Problem 5
city_bank > In Oracle, it is possible to request information using &, as shown in the example. Create the cb_client.sql file.
En Oracle es posible solicitar información usando &, como se muestra en el ejemplo. Cree el archivo cb_client.sql.

cb_client.sql
SELECT name
FROM client
WHERE client_id = &client_id;

MSDOS: cmd.exe
SQL> connect city_bank;
Enter password:
Connected.
SQL> @C:\sql\cb_client.sql

Enter value for client_id: 2345
old   3: WHERE client_id = &client_id
new   3: WHERE client_id = 2345

NAME
---------------
Sigal, Tobias

SQL>


@@ROWCOUNT

Whenever there is a SQL command inside a function or a stored procedure, you must check how many rows were affected. Microsoft SQL Server provides the variable @@ROWCOUNT to verify the number of rows affected by the execution of an SQL command. The use of the variable @@ROWCOUNT is an indicator of the quality of the stored procedure.
Siempre que se incluye un comando de SQL dentro de una función o un procedimiento se debe verificar que este comando afectó el número de renglones deseados. Microsoft SQL Server proporciona la variable PL @@ROWCOUNT para verificar cuantos renglones fueron afectados por la ejecución del comando SQL. El uso de la variable @@ROWCOUNT es un indicador de la calidad del procedimiento o función.

Problem 6
Discuss the Microsoft SQL Server stored procedure displayed below.
Discuta el procedimiento almacenado de Microsoft SQL Server que se muestra debajo.

school.sql
CREATE PROCEDURE [proc_submit_course_proposal]
     @in_course_id int
AS
--RETURNS bit
     DECLARE @count INT;
     DECLARE @current_date DATETIME;
     DECLARE @p_username VARCHAR(20);

     SET @count = -1;
     SET @current_date = GETDATE();

     BEGIN TRANSACTION;

     -- ____________________________________________ UPDATE the course table
     UPDATE course
          SET date_time = @current_date, status = 'SU'
          WHERE course_id = @in_course_id;
     SET @count=@@ROWCOUNT;

     IF (@count<>1) BEGIN
          ROLLBACK TRANSACTION;
          RETURN 0;
     END
     SET @count = -1;

     -- ____________________________________________ Department
     SELECT @p_username = u.username FROM main_user u, course c
          WHERE c.department = u.department_id
               AND approval_type_id = 1 AND c.course_id = @in_course_id;
     SET @count=@@ROWCOUNT;

     IF (@count<>1) BEGIN
          ROLLBACK TRANSACTION;
          RETURN 0;
     END
     SET @count = -1;

     INSERT INTO course_approval_process
          (course_id, approval_type, course_process_status_id, approver_username, date_time)
          VALUES (@in_course_id, 1, 2, @p_username, @p_current_date);          
     SET @count=@@ROWCOUNT;

     IF (@count<>1) BEGIN
          ROLLBACK TRANSACTION;
          RETURN 0;
     END
     SET @p_count = -1;

     -- ____________________________________________ Vicepresident
     SELECT @p_username =u.username FROM main_user u WHERE approval_type_id = 6;
     SET @count=@@ROWCOUNT;

     IF (@count<>1) BEGIN
          ROLLBACK TRANSACTION;
          RETURN 0;
     END
     SET @count = -1;

     INSERT INTO course_approval_process
          (course_id, approval_type, course_process_status_id, approver_username, date_time)
          VALUES (@in_course_id, 6, 1, @p_username, @p_current_date);

     SET @count=@@ROWCOUNT;

     IF (@count<>1) BEGIN
          ROLLBACK TRANSACTION;
          RETURN 0;
     END

     COMMIT TRANSACTION;
     RETURN 1;
GO


Tip
Whenever a stored procedure executes two or more commands that modifies the database (that is, INSERTs, UPDATEs or DELETEs) you must use the commands BEGIN TRANSACTION and COMMIT TRANSACTION.
Siempre que un procedimiento almacenado se ejecuten dos o más comandos que modifiquen la base de datos (esto es INSERTs, UPDATEs o DELETEs) usted debe de usar los comandos BEGIN TRANSACTION y COMMIT TRANSACTION.

Tip
You must always detect when something goes wrong in a stored procedure so that you can rollback the transaction. Additionally, you must return an appropriate value so that the calling program can know if the stored procedure was successfully executed.
Usted debe siempre detectar cuando algo sale mal en un procedimiento almacenado de tal forma que usted pueda deshacer la transacción. Adicionalmente, usted debe regresar un valor apropiado de tal forma que el programa que llama al procedimiento almacenado pueda saber si el procedimiento almacenado se ejecutó con éxito.

Problem 7
city_bank > Create an Oracle script using the & operator to search clients by branch.
Cree un script de Oracle usando el operador & para buscar clientes por sucursal.

MSDOS: cmd.exe
SQL> connect city_bank;
Enter password:
Connected.
SQL> SET SERVEROUTPUT ON;
SQL> @C:\sql\cb_find.sql

Enter value for branch: 'Centro'
old   6: AND b.name = &branch
new   6: AND b.name = 'Centro'

CLIENT          BRANCH
--------------- ----------
Sigal, Tobias   Centro
Weiss, Matthew  Centro
Sigal, Tobias   Centro
Weiss, Matthew  Centro
Vega, Adam      Centro
Volman, Shanta  Centro

6 rows selected.

SQL>

Problem 8
city_bank > Create a stored procedure to show the balance of all accounts given the name of a client. Using: (a) Oracle, (b) Microsoft SQL Server.
Cree un procedimiento almacenado para mostrar el balance de todas las cuentas dado el nombre de un cliente. Usando: (a) Oracle, (b) Microsoft SQL Server.

MSDOS: cmd.exe
SQL> EXECUTE p_balan('Weiss, Matthew');
The balance of Weiss, Matthew is $13523.45

PL/SQL procedure successfully completed.

SQL>

Microsoft SQL Server
USE city_bank;
EXECUTE p_balan 'Weiss, Matthew';

Output
The balance of Weiss, Matthew is $13523.45


Tip
The following code illustrates how to create and execute a stored procedure in MySQL.
El siguiente código ilustra cómo crear y ejecutar un procedimiento almacenado en MySQL.

MyProcedure.sql
-- ________________________________________ We define as delimiter: //
DELIMITER //

CREATE PROCEDURE payment
(
     buy DECIMAL(6,2),
     product_id INT
)
BEGIN
     DECLARE amount DECIMAL(6,2);
     SET amount = buy - 10.50;
     INSERT INTO sales VALUES (amount, CURRENT_DATE);
     IF buy > 222.00 THEN
          UPDATE stock_info
          SET info = amount + 3.5;
          WHERE product_stodk_id = product_id;
     END IF;
END;
//
-- ________________________________________ We restore the original delimiter
DELIMITER ;

CALL payment (510, 101);



Microsoft Server Variables

Microsoft SQL Server provides a set of variables that can be used to obtain information about the server. They are:
  • @@CONNECTIONS
  • @@CPU_BUSY
  • @@CURSOR_ROWS
  • @@DATEFIRST
  • @@DBTS
  • @@DEF_SORTORDER_ID
  • @@DEFAULT_LANGID
  • @@ERROR
  • @@FETCH_STATUS
  • @@IDENTITY
  • @@IDLE
  • @@IO_BUSY
  • @@LANG_ID
  • @@LANGUAGE
  • @@LOCK_TIMEOUT
  • @@MAX_CONNECTIONS
  • @@MAX_PRECISION
  • @@MICROSOFTVERSION
  • @@NEXTLEVEL
  • @@OPTIONS
  • @@PACK_RECEIVED
  • @@PACK_SENT
  • @@PACKET_ERRORS
  • @@PROCID
  • @@REMSERVER
  • @@ROWCOUNT
  • @@SERVERNAME
  • @@SERVICENAME
  • @@SPID
  • @@TEXTSIZE
  • @@TIMETICKS
  • @@TOTAL_ERRORS
  • @@TOTAL_READ
  • @@TOTAL_WRITE
  • @@TRANCOUNT
  • @@VERSION
Microsoft SQL Server proporciona un conjunto de variables que pueden ser usadas para otener información sobre el servidor.

© Copyright 2000-2021 Wintempla selo. All Rights Reserved. Jul 22 2021. Home