Sql tutorial free download pdf

Programming languages provide various control structures that allow for more complicated execution paths. It tests the condition before executing the loop body. With each iteration, the sequence of statements is executed and then control resumes at the top of the loop.

This step allows you to declare and initialize any loop control variables. If it is TRUE, the body of the loop is executed. If it is FALSE, the body of the loop does not execute and flow of control jumps to the next statement just after the for loop. If it is TRUE, the loop executes and the process repeats itself body of loop, then increment step, and then again condition.

In such case, iteration proceeds the other way. After each iteration, the loop counter is decremented. However, you must write the range bounds in ascending not descending order. Following section shows few examples to illustrate the concept. The label name can also appear at the end of the LOOP statement. You may use the label in the EXIT statement to exit from the loop. When execution leaves a scope, all automatic objects that were created in that scope are destroyed.

Labeling loops also helps in taking the control outside a loop. Transfers control to the labeled statement.

In other words, it forces the next iteration of the loop to take place, skipping any code in between. NOTE: Use of GOTO statement is highly discouraged in any programming language because it makes difficult to trace the control flow of a program, making the program hard to understand and hard to modify. However, a GOTO statement can branch from an exception handler into an enclosing block.

The characters could be numeric, letters, blank, special characters or a combination of all. The string is right-padded with spaces to the length so specified.

A string literal is enclosed within quotation marks. For example, 'This is a string literal. The datatypes prefixed with an 'N' are 'national character set' datatypes, that store Unicode character data. If you need to declare a variable-length string, you must provide the maximum length of that string. I''m John Smith from Infotech. I'm John Smith from Infotech. Here you do not have to specify a maximum length for a fixed-length variable.

If you leave off the length constraint, Oracle Database automatically uses a maximum length required. Must be specified before any NLS function; otherwise, the default sort will be used. NVL x, value ; 17 Returns value if x is null; otherwise, x is returned. NVL2 x, value1, value2 ; 18 Returns value1 if x is not null; if x is null, value2 is returned.

An optional length for the substring may be supplied. Hello World Hello World Hello World A varray is used to store an ordered collection of data, but it is often more useful to think of an array as a collection of variables of the same type. All varrays consist of contiguous memory locations.

The lowest address corresponds to the first element and the highest address to the last element. An array is a part of collection type data and it stands for variable-size arrays.

Each element in a varray has an index associated with it. It also has a maximum size that can be changed dynamically. You must specify the maximum size and the type of elements stored in the varray. These subprograms are combined to form larger programs. This is basically called the 'Modular design'. A subprogram can be invoked by another subprogram or program, which is called the calling program. A subprogram created inside a package is a packaged subprogram. It contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms.

These items are local to the subprogram and cease to exist when the subprogram completes execution. Executable Part 2 This is a mandatory part and contains statements that perform the designated action. Exception-handling 3 This is again an optional part. It contains the code that handles run-time errors. IN represents that value will be passed from outside and OUT represents that this parameter will be used to return a value outside of the procedure.

Example: The following example creates a simple procedure that displays the string 'Hello World! It is a read-only parameter. Inside 1 the subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter. It is the default mode of parameter passing. Parameters are passed by reference. Inside the subprogram, an OUT 2 parameter acts like a variable.

You can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed by value. It can be assigned a value and its value can be read.

Formal parameter must be assigned a value. Actual parameter is passed by value. This example shows how we can use same parameter to accept a value and then return another result. So, a is substituted for x, b is substituted for y, c is substituted for z and d is substituted for m.

Therefore, all the discussions of the previous chapter are true for functions too. Calling a Function While creating a function, you give a definition of what the function has to do.

To use a function, you will have to call that function to perform the defined task. When a program calls a function, program control is transferred to the called function. A called function performs defined task and when its return statement is executed or when it last end statement is reached, it returns program control back to the main program. To call a function you simply need to pass the required parameters along with function name and if function returns a value then you can store returned value.

When a subprogram calls itself, it is referred to as a recursive call and the process is known as recursion. To illustrate the concept, let us calculate the factorial of a number.

Factorial of a number n is defined as: n! A cursor is a pointer to this context area. A cursor holds the rows one or more returned by a SQL statement. You can expect general availability of the product to begin on April 1, Chapter 2: Advanced options. Chapter 3: Aggregate Functions. Chapter 4: Alias Names in Sql Server.

Chapter 5: Analyzing a Query. Chapter 6: Backup and Restore Database. You can expect general availability of the product to begin on April 1, Nice reference, short and to the point. Good job. Thank you. This will help me with my studies. Have to add to my SQL notes! Great stuff. High-Availability and Disaster-Recovery Enhancements 3.

Performance and Scalability 4.