Download pdf sql tutorial

Carousel Previous. Carousel Next. What is Scribd? Uploaded by Hana hana. Did you find this document useful? Is this content inappropriate?

Report this Document. Flag for inappropriate content. Download now. Related titles. Carousel Previous Carousel Next. Jump to Page. Search inside document. Gaurav Sukhija. Atiullah Khan. Aravind Rajagopalan. Ruchi Anish Patel. Danny Lekutovich. Muhammad Bilal Ayub. Yogesh Kumar. Pranav Lakde. Game Twitcher. Waseem Ahmed. Shaik Mahaboob Basha. Adam Zildan. K Harish. More From Hana hana. Hana hana. Lourdes Cz. Chapple M.

Vio Putri. Sahil Kumar Sree Siva. Rhenielou Cristobal. Marielle Salan. Tim Murison. Selvaraj Villy. El Bucho. A subtype has the same valid operations as its base type, but only a subset of its valid values. A null can be assigned but it cannot be equated with anything, including itself. By default, variable names are not case-sensitive.

For this chapter, let us study only basic variable types. It is a good programming practice to initialize variables properly otherwise, sometime program would produce unexpected result. If a variable is declared within an inner block, it is not accessible to the outer block. A constant declaration specifies its name, data type, and value, and allocates storage for it.

Relational Operators Relational operators compare two expressions or values and return a Boolean result. All these operators work on Boolean operands and produces Boolean results. If both the operands are true then A and B is and condition becomes true. Called logical OR Operator. If any of the two operands is true then or A or B is true. Called logical NOT Operator. Used to reverse the logical state of its not A and B is not operand. If a condition is true then Logical NOT operator will make true.

This affects how an expression is evaluated. Here operators with the highest precedence appear at the top of the table, those with the lowest appear at the bottom.

Within an expression, higher precedence operators will be evaluated first. Following is the general from of a typical conditional i. Click the following links to check their detail. It ensures that either of the sequence of statements is executed. However, to select the Case statement sequence, the CASE statement uses a selector rather than multiple Boolean expressions.

A selector is an expression whose value is used to select one of several alternatives. IF - THEN statement It is the simplest form of IF control statement, frequently used in decision making and changing the control flow of the program execution. If boolean expression evaluates to false then the first set of code after the end of the if statement after the closing end if will be executed. ELSE statement. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions.

In general statements are executed sequentially: The first statement in a function is executed first, followed by the second, and so on. 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.

The set of rows the cursor holds is referred to as the active set. You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. Programmers cannot control the implicit cursors and the information in it.

Records consist of different fields, similar to a row of a database table. For example, you want to keep track of your books in a library. A record containing a field for each of these items allows treating a BOOK as a logical unit and allows you to organize and represent its information in a better way.

The following example would illustrate the concept of table-based records. Cursor-Based Records The following example would illustrate the concept of cursor-based records.

Records consist of different fields. Suppose you want to keep track of your books in a library. The member access operator is coded as a period between the record variable name and the field that we wish to access. Records as Subprogram Parameters You can pass a record as a subprogram parameter in very similar way as you pass any other variable.