Oracle PL/SQL Tutorial - An Introduction to PL/SQL (Part 3)

Our third Oracle PLSQL tutorial introduces the data types and language elements.

Oracle PLSQL Data Types

PLSQLis a block-structured language like Ada and the built-in scalar datatypes include all the SQL data types plus Boolean and integer types as well as some additional sub types. The standard PL/SQL data types can also be extended by defining your own types (not to be confused with types declared in the database) in the form of records and PLSQL collections.

Integer types

• PLS_INTEGER - signed integer type using machine arithmetic • BINARY_INTEGER - identical to PLS_INTEGER  type • SIMPLE_INTEGER - same as PLS_INTEGER  type but with an added NOT NULL constraint Numeric variables are usually declared as NUMBER, with a scale and precision if necessary, for compatibility with the SQL datatypes or as PLS_INTEGER for efficiency in calculations.

Boolean

Variables defined as Boolean can have one of 3 values: true, false or NULL (undefined)

Oracle PL/SQL Language Elements

As PL/SQL is modeled on Ada, it uses a different syntax to C or Java but the language constructs are common to all modern high-level languages..

Loops

• numeric FOR loops • cursor FOR loops (these act on the results of queries) • WHILE loops • simple LOOPs

Conditional and sequential control statements

• IF ... THEN ... END IF and IF ... THEN ... ELSE ... END IF • GOTO (but the use of this is usually not necessary) • simple and searched CASE statements • anonymous blocks • named blocks: o PROCEDURES o FUNCTIONS o PACKAGES

Assignment and Expression comparison statements

These types of statements are the major difference between PL/SQL and C/C++/Java. In Java/C/C++ the assignment operator is the single "=" and the comparison operator is "==" (i.e. 2 "=" signs together) whereas in PL/SQL, Ada, Modula-2 and Pascal, the single "=" is used as the comparison operator and ":=" is used as the assignment operator. On top of the normal language elements, the tight integration with Oracle allows SQL statements to be embedded directly in PL/SQL programs and PL/SQL functions to be called directly from SQL. Oracle PLSQL tutorial Part 2                                                                            Oracle PLSQL tutorial Part 4 To extend and enhance your knowledge of PL/SQL quickly why not consider a formal training course? We offer instructor-led training courses at centres across the country. See the Oracle training page for details of our scheduled courses or contact us for more information.  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor- led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Looking for more Oracle tips and tricks? For no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our newsletter. Jam-packed full of tips and tricks, it will help you make your Oracle systems faster and more reliable and save you hours searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox. Smartsoft Computing Ltd, Bristol, England Tel: 0845 003 1320 Need help with Oracle? Contact Us View our privacy policy This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd 2014. All rights reserved.