PL/SQL Programming   «Prev 

Oracle PL/SQL Datatypes have tight integration with SQL

How to program with PL/SQL Datatypes?

SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like statements such as
  1. SELECT,
  2. INSERT,
  3. UPDATE, and
make it easy to manipulate the data stored in a relational database. PL/SQL is tightly integrated with SQL. With PL/SQL, you can use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators, and pseudocolumns. PL/SQL fully supports SQL data types and you need not convert between PL/SQL and SQL data types.
For example, if your PL/SQL program retrieves a value from a database column of the SQL type VARCHAR2, it can store that value in a PL/SQL variable of the type VARCHAR2. Special PL/SQL language features let you work with table columns and rows without specifying the data types, saving on maintenance work when the table definitions change.
Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages. Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation. Many database features, such as triggers and object types, use PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL and PL/SQL supports both
  1. static and
  2. dynamic
SQL. Static SQL is SQL whose full text is known at compilation time. Dynamic SQL is SQL whose full text is not known until run time. Dynamic SQL enables you to make your applications more flexible and versatile.

1) Scalar Type, 2) Composite, 3) Reference Type, and 4) LOB Types in PL/SQL

A scalar datatype has no internal components and holds a single value

A composite datatype has internal components that can be manipulated individually. PL/SQL TABLE and RECORD fall under the category of a composite datatype.

A reference datatype variable holds a value, called pointer, that designates other program items. Variables that are declared with the %TYPE and %ROWTYPE would be good examples of reference datatype variables.

1) LOB large objects datatype holds a value, called locator, that specifies the location of a large object.
2) With LOB datatypes, you can store blocks of unstructured data such as text, graphic images, video clips, and audio files up to 4 gigabytes in size. 3) LOB datatypes allow efficient, random, piecewise access to the data and can be further divided into 4 categories as show above.