Archives for February 2022

You have learned to write PL/SQL blocks containing declarative and executable sections. You have also learned to include expressions and SQL statements in the executable block. In this post, you will learn how to use control structures such as IF statements in a PL/SQL block. IF Statement The structure of the PL/SQL IF statement is […]

A cursor is a pointer to the private memory area allocated by the Oracle Server. It is used to handle the result set of a SELECT statement. There are two types of cursors: implicit and explicit. Implicit: Created and managed internally by the Oracle Server to process SQL statements. Explicit: Declared explicitly by the programmer […]

You can manipulate data in the database by using DML commands. You can issue DML commands such as INSERT, UPDATE, DELETE, and MERGE without restriction in PL/SQL. Row locks (and table locks) are released by including the COMMIT or ROLLBACK statements in the PL/SQL code. The INSERT statement adds new rows to the table. The […]

SQL Statements in PL/SQL In a PL/SQL block, you use SQL statements to retrieve and modify data from the database table. PL/SQL supports data manipulation language (DML) and transaction control commands. You can use DML commands to modify the data in a database table. However, remember the following points while using DML statements and transaction […]

The operations in an expression are performed in a particular order depending on their precedence (priority). The following table shows the default order of operations from high priority to low priority: Operator Operation ** Exponentiation +, – Identity, negation *, / Multiplication, division +, -, || Addition, subtraction, concatenation =,, =, , !=, ~=, ^=, […]

Being procedural gives PL/SQL the ability to nest statements. You can nest blocks wherever an executable statement is allowed, thus making the nested block a statement. If your executable section has code for many logically related functionalities to support multiple business requirements, you can divide the executable section into smaller blocks. The exception section can […]

Lexical Units in a PL/SQL Block Lexical units include letters, numerals, special characters, tabs, spaces, returns, and symbols. Identifiers Identifiers are the names given to PL/SQL objects. You learned to identify valid and invalid identifiers. Recall that keywords cannot be used as identifiers. Quoted identifiers: Make identifiers case-sensitive Include characters such as spaces Use reserved […]

With PL/SQL, you can declare variables, and then use them in SQL and procedural statements. Variables are mainly used for storage of data and manipulation of stored values. Consider the PL/SQL statement given below: SELECT first_name, department_id INTO v_fname, v_deptno FROM … The statement retrieves first_name and department_id from the table. If you have to […]