Page 32
Listing 2.5. continued
North West $31,012
$21,101
$5,243
$30,011
Confidential
After you have your report broken into subunits with the break command, you can perform additional operations on the rows within each subset. You do this with the compute command. There are eight summary functions that you can use in a break report.
The Syntax for the compute Command
The syntax is
compute function of compute_column on break_column
where function represents one of the functions listed in the following bulleted list, compute_column is the column or expression you want to use in the computation, and break_column is the event that SQL*Plus will use as a break.
The eight functions to be used with the compute function are as follows:
Continuing with the example used in the earlier listings, the compute command with the sum function is added to display the total sales for each region in Listing 2.6.
Page 33
INPUT/OUTPUT
Listing 2.6. compute command using the sum function.
set pagesize 22
set linesize 60
set feedback off
ttitle `Monthly Sales|for June'
btitle `Confidential'
column sales_region heading `Sales Region' format A15
column sales_totals heading `Total Sales' format $99,999,999
break on sales_region skip 1
compute sum of sales_totals on sales_region
SELECT sales_region, sales_totals from master_sales
WHERE sales_month = `06'
Monthly Sales
For June
Sales Region Total Sales
------------ ------------
North East $55,100
$43,104
$1,004
************ -------
sum $99,204
South East $72,900
************ -------
sum $72,900
Mid West $811,460
$110,432
************ --------
sum $921,892
Plains $69,916
************ -------
sum $69,916
North West $31,012
$21,101
$5,243
$30,011
************ -------
sum $87,357
Confidential
To calculate the grand total for a column, use the report clause with the break and compute commands. In Listing 2.7, the ORDER BY command is included in this example to order the output in ascending order.
Page 34
INPUT/OUTPUT
Listing 2.7. Report with ORDER BY command.
set pagesize 22
set linesize 60
set feedback off
ttitle `Monthly Sales|for June'
btitle `Confidential'
column sales_region heading `Sales Region' format A15
column sales_totals heading `Total Sales' format $99,999,999
break on sales_region skip 1 on report
compute sum of sales_totals on sales_region on report
SELECT sales_region, sales_totals from master_sales
WHERE sales_month = `06'
ORDER BY sales_region, sales_totals
Monthly Sales
For June
Sales Region Total Sales
------------ ------------
Mid West $111,460
$810,432
************ --------
sum $921,892
North East $55,100
$43,104
$1,004
************ -------
sum $99,204
North West $31,012
$31,101
$20,243
$5,011
************ -------
sum $87,357
Plains $69,916
************ -------
sum $69,916
South East $72,900
************ -------
sum $72,900
sum $1,251,269
Confidential
Page 35
The previous section covers SQL*Plus extensively because this will be your most commonly used development tool. This section shows you an example in which PL/SQL is used with Oracle's Developer/2000 Forms.
Listing 2.8 contains a PL/SQL procedure being used inside an Oracle Forms Program Unit. PL/SQL is quite powerful when used within any of the Oracle development tools.
Listing 2.8. PL/SQL procedure within Oracle Forms.
1: PROCEDURE CREATE_INITIALS IS 2: BEGIN 3: IF :FIRST_NAME IS NOT NULL AND :LAST_NAME IS NOT NULL THEN 4: IF :SYSTEM.RECORD_STATUS = `INSERT' THEN 5: :GLOBAL.TEMP_PERSON_KEY := 0; 6: ELSE 7: :GLOBAL.TEMP_PERSON_KEY := :PERSON.PERSON_KEY; 8: END IF; 9: DECLARE 10: TEMP_INITIALS CHAR(3); 11: TEMP2_INITIALS CHAR(5); 12: TEMP3_INITIALS CHAR(5); 13: BEGIN 14: TEMP_INITIALS := SUBSTR(:FIRST_NAME,1,1)||SUBSTR(:MIDDLE_NAME,1,1)|| 15: SUBSTR(:LAST_NAME,1,1); 16: :GLOBAL.TEMP_INITIALS := TEMP_INITIALS; 17: BREAK; 18: SELECT INITIALS INTO TEMP2_INITIALS FROM PERSONS 19: WHERE SUBSTR(INITIALS,1,3) = TEMP_INITIALS AND 20: PERSON_KEY_2 = :GLOBAL.TEMP_PERSON_KEY; 21: :GLOBAL.TE_INITIALS := TEMP2_INITIALS; 22: BREAK; 23: BEGIN 24: SELECT INITIALS INTO TEMP3_INITIALS FROM employee 25: WHERE INITIALS = TEMP2_INITIALS; 26: :GLOBAL.TEMP_3 := TEMP3_INITIALS; 27: :INITIALS := TEMP_INITIALS|| 28: TO_CHAR(TO_NUMBER(NVL(SUBSTR(TEMP3_INITIALS,4,2),0))+1); 29: END; 30: EXCEPTION 31: WHEN NO_DATA_FOUND THEN 32: :GLOBAL.TEMP_INIT_NULL := -1; 33: :INITIALS := TEMP_INITIALS; 34: WHEN TOO_MANY_ROWS THEN 35: 36: BEGIN 37: SELECT MAX(INITIALS) INTO TEMP3_INITIALS FROM employee 38: WHERE SUBSTR(INITIALS,1,3) = TEMP_INITIALS;
continues
Page 36
Listing 2.8. continued
39: :GLOBAL.TEMP_3A := TEMP3_INITIALS; 40: 41: :INITIALS := TEMP_INITIALS|| 42: TO_CHAR(TO_NUMBER(NVL(SUBSTR(TEMP3_INITIALS,4,2),0))+1); 43: EXCEPTION 44: WHEN OTHERS THEN 45: NULL; 46: END; 47: END; 48: ELSE 49: :INITIALS := NULL; 50: END IF; 51: END;
The final development tool to cover is the Oracle Precompiler. The Oracle Precompiler allows you to embed Oracle code inside a non-Oracle program such as C so that you can take advantage of the power of PL/SQL within your program. In the following example, the source code for a partial C program is shown, which includes the use of a PL/SQL cursor (see Day 10, "Manipulating Data with Cursors, DELETE, and UPDATE," and Day 12, "Using Cursors for Complex Processing," for additional information on cursors):
... exec sql DECLARE emp_names cursor for SELECT emp_name from emp WHERE pay_type = `S'; exec sql open emp_names; ...
Oracle offers many development tools in which you can use PL/SQL.
SQL*Plus is an Oracle product that provides an open window into the Oracle database. It gives developers and end users the ability to interact directly with the database.
Page 37
Q Why do I need a development tool for PL/SQL?
A SQL*Plus and other development tools such as Oracle Forms provide you a means to interactively use PL/SQL blocks directly with the database. You could write PL/SQL in your preferred text editor and compile the blocks that call them by some other means. By using some of the development tools, however, not only can you interact with the database, but you can also use PL/SQL within these other tools.
Q Is there a difference in the PL/SQL code and syntax from one development tool to another?
A No. PL/SQL prescribes only one syntax. No matter when, where, or how you are using PL/SQL, it always follows the same coding rules.
Q Are the development tools mentioned in this chapter only for use with PL/SQL?
A No. SQL*Plus and the other development tools might use, for example, SQL as well as PL/SQL.
The following workshop will test your knowledge of the development tools available for use with PL/SQL. The quiz answers and exercise solution can be found in Appendix A, "Answers."
Write a simple piece of code outline that uses substitution variables and bind variables.