PL/SQL Programming   «Prev 

PL/SQL Datatypes tight integration with SQL

Question: Do PL/SQL datatypes have tight integration with the structured query language?
PL/SQL datatypes have tight integration with the structured query language (SQL). PL/SQL, which stands for Procedural Language/Structured Query Language, is a powerful extension to SQL that adds procedural constructs and capabilities to the SQL language. It is commonly used in Oracle Database systems for developing stored procedures, functions, triggers, and packages. PL/SQL datatypes are designed to seamlessly integrate with the SQL language, allowing developers to manipulate and store data within the database using a wide range of data types. These datatypes provide a means to define variables, parameters, and return types for PL/SQL program units, enabling efficient data processing and manipulation within SQL statements. The integration between PL/SQL datatypes and SQL is evident in several ways:
  1. Compatibility: PL/SQL datatypes are fully compatible with the SQL datatypes supported by the underlying database. This ensures that data can be easily transferred between PL/SQL variables and database columns, enabling smooth interaction between PL/SQL and SQL code.
  2. Data Manipulation: PL/SQL provides a set of built-in datatypes that can be used to declare variables and parameters within PL/SQL blocks. These datatypes include numeric types (INTEGER, NUMBER), character types (VARCHAR2, CHAR), date and time types (DATE, TIMESTAMP), and more. These datatypes align with the corresponding SQL datatypes, allowing for seamless data manipulation and storage.
  3. SQL Statements: PL/SQL allows developers to embed SQL statements within their procedural code. This integration allows PL/SQL programs to leverage the full power of SQL for querying, updating, and manipulating data. PL/SQL variables of appropriate datatypes can be used within SQL statements, and the results of SQL queries can be fetched into PL/SQL variables for further processing.
  4. Implicit Data Conversion: PL/SQL provides implicit data conversion between compatible datatypes, ensuring that data can be seamlessly transferred between SQL and PL/SQL constructs. For example, when a PL/SQL variable of type NUMBER is used in a SQL statement that expects a VARCHAR2 parameter, the implicit conversion takes place automatically.

PL/SQL datatypes are tightly integrated with the structured query language. This integration allows for seamless interaction between SQL and PL/SQL code, enabling efficient data manipulation, storage, and retrieval within Oracle Database systems.

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.

Oracle Database PL/SQL Programming

Oracle PL/SQL Programming