Working with REF CURSOR in PL/SQL

...

Read more »

Oracle By Example (OBE)

Oracle By Example (OBE)The Oracle by Example (OBE) series provides hands-on, step-by-step instructions on how to implement various technology solutions to business problems. OBE solutions are built for practical real-world situations, allowing you to gain valuable hands-on experience as well as use the presented solutions as the foundation for production implementation, dramatically reducing time to deployment. In addition to...

Read more »

Oracle Learning - 13

Oracle/PLSQL: Set TransactionThere are three transaction control functions. These are:1. SET TRANSACTION READ ONLY;2. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;3. SET TRANSACTION USE ROLLBACK SEGMENT name;Oracle/PLSQL: Lock TableThe syntax for a Lock table is:LOCK TABLE tables IN lock_mode MODE [NOWAIT];Tables is a comma-delimited list of tables.Lock_mode is one of:ROW SHAREROW EXCLUSIVESHARE UPDATESHARESHARE ROW EXCLUSIVEEXCLUSIVE.NoWait...

Read more »

Oracle Learning - 12

Oracle/PLSQL: Sequences (Autonumber)In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.The syntax for a sequence is:CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value;For example:CREATE...

Read more »

Oracle Learning - 11

Oracle/PLSQL: FOR LoopThe syntax for the FOR Loop is:FOR loop_counter IN [REVERSE] lowest_number..highest_numberLOOP {.statements.}END LOOP;You would use a FOR Loop when you want to execute the loop body a fixed number of times.Let's take a look at an example.FOR Lcntr IN 1..20LOOP LCalc := Lcntr * 31;END LOOP;This example will loop 20 times. The counter will start at 1 and end at 20.The FOR Loop can also loop in reverse....

Read more »

Oracle Learning - 10

Oracle/PLSQL: Case StatementIn Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.The syntax for the case statement is:CASE expressionWHEN condition_1 THEN result_1WHEN condition_2 THEN result_2...WHEN condition_n THEN result_nELSE result ENDexpression is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)condition_1...

Read more »

Oracle Learning - 9

Oracle/PLSQL: IS NULLIn other languages, a null value is found using the = null syntax. However in PLSQL to check if a value is null, you must use the "IS NULL" syntax.To check for equality on a null value, you must use "IS NULL".For example,IF Lvalue IS NULL then.END IF;If Lvalue contains a null value, the "IF" expression will evaluate to TRUE.You can also use "IS NULL" in an SQL statement. For example:select * from supplierswhere...

Read more »

Oracle Learning - 8

SQL: CREATE TableThe basic syntax for a CREATE TABLE is:CREATE TABLE table_name(column1 datatype null/not null,column2 datatype null/not null, ...);Each column must have a datatype. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.For example:CREATE TABLE supplier ( supplier_id numeric(10) not null, supplier_name varchar2(50)...

Read more »

Oracle Learning - 7

SQL: INTERSECT QueryThe INTERSECT query allows you to return the results of 2 or more "select" queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.Each SQL statement within the INTERSECT query must have the same number of fields in the result sets with similar data types.The syntax for an INTERSECT query is:select...

Read more »

Oracle Learning - 6

SQL: UNION QueryThe UNION query allows you to combine the result sets of 2 or more "select" queries. It removes duplicate rows between the various "select" statements.Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.The syntax for a UNION query is:select field1, field2, . field_nfrom tablesUNIONselect field1, field2, . field_nfrom tables;Example #1The following...

Read more »

Oracle Learning - 5

SQL: JoinsA join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.There are different kinds of joins. Let's take a look at a few examples.Inner Join (simple join)Chances are, you've already written an SQL statement that uses an inner join. It is is the most common type of join. Inner joins return all rows from multiple tables where the...

Read more »

Oracle Learning - 4

SQL: GROUP BY ClauseThe GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.The syntax for the GROUP BY clause is:SELECT column1, column2, ... column_n, aggregate_function (expression)FROM tablesWHERE predicatesGROUP BY column1, column2, ... column_n;aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.Example using the SUM functionFor...

Read more »

Oracle Learning - 3

SQL: BETWEEN ConditionThe BETWEEN condition allows you to retrieve values within a range.The syntax for the BETWEEN condition is:SELECT columnsFROM tablesWHERE column1 between value1 and value2;This SQL statement will return the records where column1 is within the range of value1 and value2 (inclusive). The BETWEEN function can be used in any valid SQL statement - select, insert, update, or delete.Example #1 - NumbersThe following...

Read more »

Oracle Learning - 2

SQL: Combining the "AND" and "OR" ConditionsThe AND and OR conditions can be combined in a single SQL statement. It can be used in any valid SQL statement - select, insert, update, or delete.When combining these conditions, it is important to use brackets so that the database knows what order to evaluate each condition.Example #1The first example that we'll take a look at an example that combines the AND and OR conditions.SELECT...

Read more »

Oracle Learning -1

SQL: SELECT StatementThe SELECT statement allows you to retrieve records from one or more tables in your database.The syntax for the SELECT statement is:SELECT columnsFROM tablesWHERE predicates;Example #1Let's take a look at how to select all fields from a table.SELECT *FROM supplierWHERE city = 'Newark';In our example, we've used * to signify that we wish to view all fields from the supplier table where the supplier resides...

Read more »

Views, Oracle

What is View?A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables. The view is stored as a SELECT statement in the DD.Advantages of views;->View restrict access to data bcz the view can display selective columns from the table.->View can be used...

Read more »

Decode Function

n Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement. The syntax for the decode function is: decode( expression , search , result [, search , result]... [, default] ) expression is the value to compare. search is the value that is compared against expression. result is the value returned, if expression is equal to search. ...

Read more »