Within the create request of a procedure, called the procedure body, there will normally be Data Manipulation Language (DML) and SPL. Using Data Definition Language (DDL) and Data Control Language (DCL) is not currently permitted within a procedure.
The DML has already been covered in this book. This chapter primarily concentrates on the SPL. The only exception will be a discussion on SELECT, UPDATE and DELETE regarding cursors and the new SELECT-INTO for setting initial values of variables in a procedure.
The <procedure-body> may contain any or all of the following SPL commands:
These commands have been listed above in alphabetic sequence. They are normally written in a sequence to accomplish a specific task. Each of these is discussed below in more detail with examples to demonstrate their functionality.
The syntax of the <procedure-body> must contain a BEGIN and END combination statement formatted as:
BEGIN / END Statements
The BEGIN and END specifications are both required and they define the body of the procedure. All other SPL commands will be contained within the boundaries delineated by these two statements. Again, they are both required and because of the dependency on each other, they are referred to as a compound statement
The following is the syntax of the BEGIN and END commands:[ <label-name>: ]BEGIN END [ <label-name> ] ;
Optionally, a BEGIN may specify a label name to identify the SPL section within the procedure. When a label name is desired, it connects to the BEGIN via a colon (:). The END will only use a label name if one is defined on the BEGIN. A label name is optional whenever there is a single section of SPL. When multiple sections (more than one BEGIN and END statements) are used, the interior sections must have a label name.
The next table is used in the next examples:
The following demonstrates a simple procedure with a single section used to INSERT a row with all null values into the Customer table:CREATE PROCEDURE First_Procedure ( )BEGIN INSERT INTO Customer_table WITH DEFAULT VALUES;END;
After the execution of the above procedure, the next SELECT returns:SELECT * FROM CUSTOMER_TABLE;
Notice that a separate SELECT (outside the procedure) is needed to see the row of null values instead of returning them from the procedure. The only output to the client from the execution of this procedure is a resultant status code. It indicates either a successful completion or a failure in the procedure.
More discussion will appear in this chapter to explain why this is the case. For now, suffice it to say that a stored procedure can't return rows to the client as noted in Figure.
The following demonstrates a technique to nesting one section within another using a label name on the inner section:
In both of these examples, the indentation is optional and used by us to assist in understanding how the coding works. The first section does not use the optional label for the BEGIN and END. It contains an INSERT into the Customer table. Then, another BEGIN and an END called SecondSection is used. This SPL section contains a DELETE from the order table. The END for SecondSection comes before the END for the first BEGIN and specifies the label name SecondSection as defined in the BEGIN. It is nested within the first BEGIN and last END as compound statements.
The normal flow is from top to bottom. So, the customer INSERT is performed first, then the order DELETE is performed. Although this same processing occurs without a nested BEGIN / END for SecondSection, it introduces this technique for use with other commands covered below and the ability to allow repeated processing of one or more sections. It may not always be desirable to repeat all the commands, only specific ones. The procedure must be written to manage all desired processing.
Like all CREATE statements, the procedure name may be qualified to request its creation in a particular database. The parameter list is optional as in a macro. However, the parentheses are not optional and must be coded, even without parameters. When used, the parameters are normally included to make the procedure more flexible and provide communications with the outside world regarding each execution. The procedure body contains SPL to manage the execution of the SQL. The setup and use of parameters and SPL is covered progressively in this chapter to facilitatelearning how to use SPL.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.