"Is SQL really a programming language?" Some people argue that SQL should definitely be considered a programming language, as it executes code written on computer. On the contrary, others contend that SQL cannot be classified as a programming language due to its lack of Turing completeness.
In computer science, Turing completeness describes a system's capability of calculating any computable functions. A system is said to be weakly[1] Turing complete if it can:
- Access and modify values at any memory location.
- Branch instructions based on a given condition.
- Perform an execution iteratively.
And if a language is weakly Turing complete, we can consider it as a programming language. For example, Java is a programming language in that it supports all three features through variable assignments and control structures. These features enable us to process data procedurally, ultimately allowing us to build applications.
Let us return to our question: is SQL a programming language? SQL's capability is limited to querying and manipulating data stored in a database. While SQL is an ideal tool for accessing information from database tables, it cannot process data in computer memory procedurally. Thus, you cannot build an application solely based on SQL as you can do with Java programming language.
PL/SQL, introduced by Oracle in the early 1990s, is the procedural extension of SQL. As the name suggests, PL/SQL bridges the gap between SQL and the procedural aspects of programming languages. At the same time, it is tightly integrated with the Oracle database and SQL, allowing for seamless data retrieval and maintenance. Using PL/SQL, you can build an application that processes database table rows with far more control than stand-alone SQL scripts allows.
Fundamentals of PL/SQL Blocks
PL/SQL scripts are organized into blocks, which serve as the program unit. These blocks group related declarations and statements, providing a structured and modular approach to writing PL/SQL code.
PL/SQL code blocks can be either anonymous or named. Any block without a given name is considered as an anonymous block, which is commonly used for testing and debugging. An anonymous block can be also included in files submitted directly to the database. SQL*Plus command-line interface easily accepts anonymous PL/SQL blocks for execution.
On the other hand, named blocks are those that are stored as procedures or functions. These blocks are assigned callable names, enabling them to be reused for specific tasks. In practice, database programming typically involves creating named blocks using the PL/SQL programming language.
- Declaration Section: This section begins with the keyword DECLARE[2] and includes declarations of variables, constants, cursors, and user-defined exceptions for use in the subsequent executable section. It is optional and only necessary if there are elements to be referenced in the executable section. Declarations are local to the block and cease to exist once the block completes, helping to prevent namespace clutter.
- Executable Section: This section contains the PL/SQL statements and forms the main body of a block, is the only mandatory section for a block to be successfully compiled. Begins with the keyword BEGIN, it includes assignment statements, conditional logic, loops, and other constructs.
- Exception-Handling Section: This optional section begins with the keyword EXCEPTION and handles any exceptions that may be raised during runtime. While this section is optional, including it is highly recommended for effectively managing runtime errors and ensuring robust program behavior.
Lastly, every block must conclude with the END statement (END;). If a PL/SQL script contains more than one blocks, each should be separated by a slash (/), which indicates the end of the block. Including slash is particularly important when submitting a PL/SQL script with multiple blocks in a command-line tool like SQL*Plus.
Basic Syntax of PL/SQL Statements
In PL/SQL, as with any programming language, statements are the basic unit of executable code. These statements, organized within a PL/SQL block, perform specific operations or call other program units, following the syntax of PL/SQL language. Similar to Oracle SQL, PL/SQL statements must be terminated with a semicolon and can span multiple lines until a semicolon is encountered. Each statement consists of four key elements: keywords, literals, identifiers, operators, and SQL functions.
keywords
Keywords are predefined, reserved words that have specific meaning within the PL/SQL language. For example, as mentioned earlier, the keywords BEGIN and END are used to define the start and end of a PL/SQL block, marking the boundary where executable code resides. Because they are reserved, keywords cannot be used as variable names or any other identifiers in PL/SQL programs.
PL/SQL keywords encompass the full set of SQL keywords, allowing developers to use SQL commands directly within PL/SQL blocks. This integration enables seamless interaction with the database, facilitating data manipulation and retrieval alongside procedural programming features.
In addition to the standard SQL keywords, PL/SQL introduces its own set of keywords tailored for control structures, error handling and variable declaration. These include keywords like BEGIN, END, DECLARE, IF, ELSE, LOOP, and EXCEPTION. The combination of SQL and PL/SQL keywords allows for a powerful and flexible programming environment, enabling developers to write comprehensive database applications that leverage both declarative and procedural paradigms.
Literals
A literal is an explicit representation of a numeric, character, string, or Boolean value as it is written in source code. The rules for literals in PL/SQL are the same as the rules used in SQL. Numeric literals can be any signed integers and rational numbers with no quotes. Datetime literals are enclosed by single quotes and must be in the appropriate format the database.
A string literal is a sequence of character values delimited with single quotes. To include a single quote within a string without delimiting it, you can insert an additional single quote character before the original quote as an escape character. For example:
'It''s a beautiful day!'
Represents the string: 'It's a beautiful day!'.
One additional type of data used in PL/SQL is the Boolean, which can take on the values true, false, and null. These values are not recognized in SQL but are used in PL/SQL only. Boolean literals are not enclosed in quotes, with true representing logical true and false representing logical false.
Null is considered a valid value for any PL/SQL variable, regardless of its datatype. It is defined as "the absence of information," meaning it is neither a blank nor is it a zero; both a blank and a zero are defined values, whereas null indicates that PL/SQL does not know what the value is.
Identifiers
Identifiers are names given to elements like variables, constants, cursors, procedures, functions, and/or packages. They serve as unique labels that allow you to reference and manipulate these elements within your code.
All PL/SQL identifiers, including variables, constants, and named blocks, must adhere naming rules, which are technical restrictions imposed by Oracle. Violating any of these rules will result in a compilation error:
- Length: Must be less than 31 characters.
- Start Character: Must begin with a letter (A-Z, a-z)[3].
- Subsequent Characters: Can include letters, numbers, underscores (_), and dollar signs ($).
- Reserved Words: Cannot use any reserved words that are already defined by Oracle.
In addition to naming rules, you should also consider naming conventions, which are widely accepted practices to enhance code readability and maintainability. While not enforced by PL/SQL syntax itself, following these conventions is highly recommended.
- Meaningful Names: Choose names that clearly reflect the purpose of the object.
- Snake: It is generally recommended to use lowercase to name an element, with words separated by underscores.
- Prefixes and Suffixes: Use prefixes and suffixes sparingly and consistently.
- Variables: v_variable_name, e.g., vn_current_inventory[4]
- Constants: c_constant_name, e.g., cn_sales_tax_rate
- Bind Variable: b_bine_name, e.g., b_customer_id
- Cursors: cur_cursor_name, e.g., cur_custoemrs
- Exception: e_exception_name, e.g., e_invalid_unit_price
- Procedures: proc_procedure_name, e.g., proc_upd_order_status
- Functions: fn_function_name, e.g., fn_get_sales_revenue
- File Handlers: fh_file_handler_name e.g., fh_store_logo_img
Operators
Operators are special symbols that are used to perform operations on variables and literal values placed on its either sides of them. Combined with the variables and literals, they make up expressions, which in turn form PL/SQL statements.
Here is the full list of PL/SQL operators:
| Operator | Description | Example |
|---|---|---|
| := | Assigns a literal value from its right side to the variable on the left side. | v_text CHAR(15) := 'Hello, PL/SQL!'; |
| || | Concatenation (join strings). | 'Hello, ' || 'PL/SQL!' |
| + | Addition | 5 + 10 |
| - | Subtraction or negation | 5 - 10 |
| * | Multiplication | 5 * 10 |
| / | Division | 5 / 10 |
| ** | Exponentiation | 2 ** 3 |
| = | Relational operator (equal) | 2 ** 3 = 8 |
| <> | Relational operator (not equal) | 2 ** 3 <> 8 |
| != | Relational operator (not equal) | 2 ** 3 != 8 |
| ~= | Relational operator (not equal) | 2 ** 3 ~= 8 |
| ^= | Relational operator (not equal) | 2 ** 3 ^= 8 |
| < | Relational operator (less than) | 2 ** 3 < 8 |
| > | Relational operator (greater than) | 2 ** 3 > 8 |
| <= | Relational operator (less than or equal) | 2 ** 3 <= 8 |
| >= | Relational operator (greater than or equal) | 2 ** 3 >= 8 |
| IS NULL | Relational operator (checks if value on the left side is null) | v_text IS NULL |
| LIKE | Pattern matching operator | LIKE '%SQL' |
| IN | Membership operator | IN ('SQL', 'PL/SQL', 'Java') |
| BETWEEN | Range operator (checks if value is within a specified range) | 1 BETWEEN 1 AND 5 |
| .. | Range operator (creates a range of values) | 1..5 |
| AND | Logical operator (multiplication, where TRUE is 1 and FALSE is 0) | 2 ** 3 <> 8 AND 2 ** 3 = 8 |
| OR | Logical operator (addition, where TRUE is 1 and FALSE is 0) | 2 ** 3 <> 8 OR 2 ** 3 = 8 |
| NOT | Logical operator (negation, where NOT TRUE = FALSE and NOT FALSE = TRUE) | NOT FALSE AND TRUE |
Precedence between the operators is:
- Exponentiation (**)
- Sign (+, -)
- Multiplication (*), Division (/)
- Addition (+), Subtraction (-), Concatenation (||)
- Comparisons (=, <, >, <=, >=, <>, !=, ~=, IS NULL, LIKE, BETWEEN, IN)
- NOT (Logical negation)
- AND (Logical multiplication)
- OR (Logical addition)
For example:
BEGIN-- Outputs -8 (neg sign precedes to exponent)dbms_output.put_line(-2**3);-- Outputs 36 (exponent precedes to multiplication)dbms_output.put_line(2**2*3**2);END;/
To override the rules of operator precedence, you can use parentheses. For example:
BEGIN-- Outputs 6dbms_output.put_line(2*3);-- Outputs 6^2dbms_output.put_line((2*3)**2);-- Outputs 2^36dbms_output.put_line(2**((2*3)**2));END;/
As mentioned earlier, null in Oracle refers to an absence of information. A null can be assigned to a variable, but it cannot be equated with anything, including itself. Thus, null is not the same as an empty data string, numerical value 0, or false.
Here are some examples of logical expressions, involving null values:
- TRUE AND NULL → NULL
- TRUE OR NULL → TRUE
- FALSE AND NULL → FALSE
- FALSE OR NULL → NULL
- NOT NULL → NULL
SQL Functions
In addition to the operators, the Oracle SQL functions can be used in PL/SQL expressions. For example:
Comments
Comments can be included in code scripts to provide explanations, clarify the purpose of the code, or document important information. They are essential for improving code readability and maintainability. In PL/SQL, comments can be added using double dashes (--) or /* and */.
- Single-line comments begin with two consecutive dashes (--). They can be placed on a line by themselves or at the end of a line of valid PL/SQL code[5].
- Multiline comments start with /* and end with */. They can span multiple lines any may appear within a line of code or be followed by additional PL/SQL code on the same line after the termination characters.
The code snippet below shows examples of single-line comments and a multiline comment:
Nested Blocks and Scope
PL/SQL blocks can be nested within each other. For example:
In this example, observe that a variable v_outer is declared in the declaration section of the outer block. Then in the following executable section, a nested block is introduced with its own declaration section.
When referencing variables across different levels in a nested block, as shown in this example, it is important to consider their scope, which determines the visibility of the variable within the code. In the example above, v_outer is accessed from the inner block. During compilation, the PL/SQL compiler resolves references to variables based on their scope. So, the inner block will refer to the inner variables, while outer block will refer to the outer variables. If the compiler doesn't find a reference within the inner block, it will then look to the outer variable. However, the outer block cannot reach the inner variables. For example:
In this example, when we attempt to access v_inner from the outer block at line 12, Oracle throws an error, saying "PLS-00201: identifier 'V_INNER' must be declared." This error occurs because v_inner is not visible or accessible outside its own inner block.
Block Labels
We often need to override the visibility of variables. To do this, we can label a block and reference the variables with the label. For example:
We see that the v_text in the outer block is referenced with its label. So, even after the variable is modified in the inner block, v_text is resolved to 'Outer' when it is referenced with the label.
Aside: Code Indentation
In computer programming, code indentation refers to the practice of aligning code lines and visually grouping related statements. This practice makes it easier to debug and collaborate among developers of your program by showing the overall structure of code blocks.
To facilitate this, the Oracle SQL Developer automatically formats messy codes. For example:
Although there is no syntactical error in the code block, it is hard to identify the overall structure of the program as it is all in lower case and there is no indentation. If you select this code and press Ctrl + F7, the SQL Developer automatically formats it.
To customize the default code format setting, navigate to "Tools" → "Preferences" → "Code Editor," and then expand "Format."
Aside: Enabling Server Outputs by Startup File
As its name might suggest, PL/SQL language is designed for running stored procedures rather than generating outputs. Thus, we cannot take outputs after running a PL/SQL code block by default. However, when learning PL/SQL language or developing database applications, it is often necessary to see immediate outputs to verify that our work is correct.
To achieve this, we use dbms_output.put_line, along with the SQL*PLUS command SET SERVEROUTPUT ON. This command is valid only for session, so you must run it at the beginning of each session, which can be tedious. Fortunately, Oracle SQL Developer offer a useful feature that allows you to roundabout this inconvenience.
First, create an SQL script containing the command:
SET SERVEROUTPUT ON
Save this script with an appropriate name, such as startup.sql.
Next, open Oracle SQL Developer and go to "Tools" → "Preference." In the Preferences window, navigate to "Database" section. Here, you can select a startup file that will automatically run when a new database connection is established. Set the path to your saved startup.sql file.
This will ensure that the SET SERVEROUTPUT ON command is executed at the start of each session, saving you the trouble of running it manually every time.
In programming languages, collections are data structures that group multiple values or objects into a single unit. They are essential for managing and manipulating groups of related data efficiently. PL/SQL supports three types of collections: Index-by tables (also known as associated array), varrays (Variable-size arrays), and nested tables.
0 Comments