Oracle --- basic PL/SQL knowledge

What is PL/SQL?

• PL/SQL is a programming Language developed by Oracle and dedicated to Oracle • PL stands for ProceduralLanguage • SQL stands for StructuredQuery Language • PL/SQL is an Oracle database extension of SQL statements, added programming language features • PL/SQL contains procedural and SQL statements • data operation and query statements are included in PL/SQL code program units (PL/SQL blocks ), complex functions or computation are completed through logical judgment, loop, and other operations.
PL/SQL Block
• PL/SQL is a block structured language (a program can be divided into several logical blocks) • PL/SQL Block Composition -The PL/SQL language is in blocks, and child blocks can be nested in the blocks. -A Basic PL/SQL block consists of three parts:

Definition section (DECLARE)

Executable part (BEGIN)

EXCEPTION Handling)

Definition of PL/SQL Blocks

Like other languages, the names of variables, constants, cursors, and Exception Handling used in PL/SQL must be defined first and then used. And the part starting with the DECLARE keyword must be defined.

Executable part of PL/SQL Block

This part is the main body of the PL/SQL block and contains the executable statements of the block. This part defines the block function and is required. Start with the keyword BEGIN and END with END.

PL/SQL block Exception Handling Section

This part contains the exception handler (error handler) of the block ). When a statement in the block program body encounters an exception (an error is detected), oracle transfers the program control to the corresponding exception handler in the exception Section for further processing. This part starts with the keyword EXCEPTION and ends with the END keyword.

• DECLARE-optional

-Variables, constants, cursors, and custom special types • BEGIN-must-SQL statements-PL/SQL statements • EXCEPTION-optional-processing actions when an error occurs • END; -required

1. DECLARE
2. V_variable VARCHAR2 (5 );
3. BEGIN
4. SELECT column_name
5. INTO v_variable
6. FROM table_name;
7. EXCEPTION
8. WHEN exception_name THEN
9. ...
10. END;

1 2 3 Next Page