Basic concepts of PL/SQL

Basic concepts of PL/SQL

1. PL/SQLOverview

PL/SQL is Oracle's procedural extension to industry-standard SQL. PL/SQL naturally, efficiently, and safely extends SQL. its primary strength is in providing a server-side, stored Procedural language that is easy-to-use, seamless with SQL, robust, portable, and secure.

PL/SQL is a procedural extension of standard SQL languages by Oracle. It integrates the powerful flexibility of SQL language (4gl) with the process structure of 3gl.

PL/SQL is the abbreviation of Procedural Language/SQL. As shown in its name, PL/SQL expands SQL by adding a structure used in other procedural languages, for example:

Variables and types (including pre-defined and user-defined ).

Control structure, such as if-then-else statements and loops.

Process and function.

Object Types and Methods

2.Cursor

The cursor is used to process the multi-row records retrieved from the database (using the SELECT statement ). With the cursor, the program can process and traverse the entire record set returned by a retrieval one by one.

To process SQL statements, Oracle allocates a region in the memory, which is the context zone. This area contains the number of rows that have been processed and the pointer to the analyzed statement. The entire area is the data row set returned by the query statement. A cursor refers to a context handle or pointer.

2.1Display cursor

The show cursor is used to process the SELECT statement that returns multiple rows of data. The cursor name is passed through cursor .... The is statement is explicitly assigned to the SELECT statement.

The following four steps are required to process the display cursor in PL/SQL:

1) Declare the cursor; cursor cursor_name is select_statement

2) Open the cursor for the query; open cursor_name

3) Put the obtained results into PL/SQL variables;

Fetch cursor_name into list_of_variables;

Fetch cursor_name into PL/SQL _record;

4) Close the cursor. Close cursor_name

Note: When declaring a cursor,Select_statement cannot contain the into clause. When a display cursor is used, the into clause is part of the fetch statement.

2.2Implicit cursor

All implicit cursors are assumed to return only one record.

You do not need to declare, open, or close an implicit cursor. PL/SQL implicitly opens, processes, and closes the cursor.

For example:

.......

Select studentno, studentname

Into curstudentno, curstudentname

From studentrecord

Where name = 'gg ';

The cursor is automatically opened, related values are assigned to corresponding variables, and then closed. After execution, the PL/SQL variable curstudentno and curstudentname have values.