Unit_8 Flashcards
Stored Procedure
structures allow a single command, or a group of statements, to be executed repeatedly.
Looping / Loops
Stored Procedure
WHILE loops are commonly used with ________ to process a set of data one row at a time.
cursors
Using Loops
Three loop statements in MySQL
(W,R,L)
While
Repeat
Loop
loop checks the expression at the beginning of each iteration.
WHILE
loop is called pretest loop because it checks the expression before the statements execute.
WHILE
First, MySQL executes the statements, and then it evaluates the expression.
What loop is this?
REPEAT loop
If the expressionevaluates to FALSE, MySQL executes the statements repeatedly until the expression evaluates to TRUE.
What loop is this?
REPEAT Loop
Because this checks the statements expression after the execution of statement thefore this loop statement is also known as post-test loop
REPEAT Loop
This command specifies that the control should pass to the command immediately following the current loop.
BREAK command
In MySQL, ________ is used to exit the flow control construct that has the given label. If the label is for the outermost stored program block, ________ exits the program.
LEAVE
If you wish to terminate a single iteration of a loop you can use the ________ command.
This command immediately stops the current iteration and rechecks the loop’s condition.
CONTINUE
In MySQL, The ________ statement allows you to skip the entire code under it and start a new iteration.
Also means “start the loop again”
ITERATE
A ________ is a database object that permits the data set generated by a query to be processed one row at a time
cursor
This provides a pointer to a single row and allows the information in that row to be extracted and processed.
cursor
Commonly a ________ is combined with a ________ loop to enable row-by-row processing of an entire data set.
cursor, WHILE
True or False
cursors use resources whilst they are open and read data from the database each time a new row is fetched.
This can increase network traffic, lower the available resources of the SQL Server instance and lead to poor performance.
True
A cursor cannot be used until it has been opened using the ____ statement.
OPEN
Once a cursor is opened, how can you determine the number of rows it has been using?
@@CURSOR_ROWS
there is only one available command that can be used to obtain a row from the cursor. This command is _____ _____
FETCH NEXT
Once you have finished working with a cursor it must be closed to free the resources associated with it and to clear any locks that the cursor has created.
How will you close the cursor?
what syntax??
CLOSE table_name
To release these structures and destroy the cursor after closing you should ________ it.
what syntax??
DEALLOCATE table_name
if one is present, and sets a flag that specifies whether the cursor is exhausted or if further rows are present. The flag is accessed using the____________ function.
what syntax??
@@FETCH_STATUS
An ____________ ________ is not affected by changes to the underlying data.
insensitive cursor
These are a special type of table that, as the name suggests, are used to hold data temporarily.
Temporary tables
A ____ ____ ____ is visible only to the session that it is created from and is dropped automatically when the connection is closed.
Local Temporary Table
______ ______ ____ are available to all users and are automatically dropped when the last user accessing the table disconnects from the database.
Global Temporary Tables
A ______ ______ provides functionality similar to a standard variable and a local temporary table combined.
Table Variables
For SQL Server
What syntax do we use if we want to skip or move on to the next condition?
CONTINUE (keyword)
For SQL Server
What syntax do we use if we want to exit out of the loop we use??
BREAK (keyword)
For MySQL
What syntax do we use if we want to skip or move on to the next condition?
ITERATE (keyword) label_ofLoop
For MySQL
What syntax do we use if we want to exit out of the loop we use??
LEAVE label_ofLoop