free barcode generator source code in vb.net SELECT @MSG_TEXT = high order total in Software

Generator Code-128 in Software SELECT @MSG_TEXT = high order total

20:

/* Check order total for a customer */ create proc chk_tot @c_num integer /* one input parameter */ as /* Declare two local variables */ declare @tot_ord money, @msg_text varchar(30) begin /* Calculate total orders for customer */ select @tot_ord = sum(amount) from orders where cust = @c_num /* Load appropriate message, based on total */ if tot_ord < 30000.00 select @msg_text = high order total else select @msg_text = low order total /* Do other processing for message text */ . . . end

Figure 20-4.

The assignment of the total order amount at the beginning of the procedure body is a more complex example, where the SELECT is used both to assign a value and as the introducer of the query that generates the value to be assigned. Figure 20-5 shows the Informix SPL version of the same stored procedure. There are several differences from the Transact-SQL version: I Local variables are declared using the DEFINE statement. This example shows only a very limited subset of the options that are available. I Variable names are ordinary SQL identifiers; there is no special first character. I A specialized SELECT INTO statement is used within SPL to assign the results of a singleton SELECT statement into a local variable. I The LET statement provides simple assignment of variable values.

/* Check order total for a customer */ create procedure chk_tot (c_num integer) /* Declare two local variables */ define tot_ord money(16,2); define msg_text varchar(30); /* Calculate total orders for requested customer */ select sum(amount) into tot_ord from orders where cust = c_num; /* Load appropriate message, based on total */ if tot_ord < 30000.00 let msg_text = high order total else let msg_text = low order total /* Do other processing for message text */ . . . end procedure;

Figure 20-5.

Figure 20-6 shows the Oracle PL/SQL version of the same stored procedure. Again, there are several differences to note from the Transact-SQL and Informix SPL examples: I Local variable declarations occur in a separate DECLARE section. This section is actually an integral part of the Oracle BEGIN END block structure; it declares local variables for use within the block. I The SELECT INTO statement has the same form as the Informix procedure; it is used to select values from a single-row query directly into local variables. I The assignment statements use Pascal-style (:=) notation instead of a separate LET statement. Local variables within a stored procedure can be used as a source of data within SQL expressions anywhere that a constant may appear. The current value of the

20:

/* Check order total for a customer */ create procedure chk_tot (c_num in integer) as declare /* Declare two local variables */ tot_ord number(16,2); msg_text varchar(30); begin /* Calculate total orders for requested customer */ select sum(amount) into tot_ord from orders where cust = c_num; /* Load appropriate message, based on total */ if tot_ord < 30000.00 msg_text := high order total ; else msg_text := low order total ; /* Do other processing for message text */ . . . end;

Figure 20-6.

variable is used in the execution of the statement. In addition, local variables may be destinations for data derived from SQL expressions or queries, as shown in the preceding examples.

In all but the very simplest stored procedures, it is often necessary to group a sequence of SQL statements together so that they will be treated as if they were a single statement. For example, in the IF THEN ELSE structure typically used to control the flow of execution within a stored procedure, most stored procedure dialects expect a single statement following the THEN keyword. If a procedure needs to perform a sequence of several SQL statements when the tested condition is true, it must group the statements together as a statement block, and this block will appear after THEN.