Oracle notes (7), PL/SQL Basics

I. Overview. PL/SQL Block Structure

2. Control Structure (branch, loop, and control)
Iii. Exceptions

Iv. Compound variables (record)

I. Overview. PL/SQL Block Structure
PL/SQL is an extension of the SQL language of Oracle products. PL/SQL blocks are divided into three parts: Declaration, executable, and exception.

DECLARE
...
BEGIN
...
EXCEPTION
...
END;

Variable declaration content: Assign the appropriate name, data type, definition variable (standard, record), and control the variable range.
Variable naming rules: a variable starts with a character. It can contain numbers, underscores, $, and #. The length ranges from 1 ~ 30; case-insensitive; the system keyword is not used.

Ps. Common commands in PL/SQL:
1. Turn ON the output switch set serveroutput on;
2. Use the system package output information DBMS_OUTPUT.PUT_LINE (the value of 'x is: '| x );

Ii. Control Structure

1. Branch statement
A. IF Branch

IF... THEN
...
ELSEIF... THEN
...
ELSE
...
End if;

B. CASE Branch

CASE
WHEN... THEN
...
ELSE
...
End case;

2. Loop statements
A. LOOP)
To avoid infinite loops, the LOOP statement must use the EXIT or exit when statement.

LOOP
...
End loop;

B. WHILE Loop

WHILE condition LOOP
...
End loop;

C. FOR Loop

FOR counter IN [REVERSE] start... end
LOOP
...
End loop;

3. Sequential Control
GOTO statement: unconditionally transfers control to the statement specified by the tag.
NULL statement: Do nothing. It only transfers control to the next statement, which is required by the statement structure, but does not require any operation.

GOTO xxxx;
...
<Xxxx>
NULL;

Iii. Exceptions

System exception (predefined exception)

EXCEPTION
WHEN xxxx THEN
....

Custom exception

DECLARE
-- Custom exception
Xxxxx EXCEPTION;
BEGIN
-- Explicitly raises an exception
RAISE xxxxx
EXCEPTION
-- Exception Handling
WHEN xxxxx THEN
....
END;

Iv. Compound variables (record)
A record is a composite variable composed of several correlation values. It is often used to support the return values of SELECT statements. A record can be used to process a row of data in a unit, instead of processing each column separately.

DECLARE
TYPE myrecord is record (id varchar2 (10), name varchar2 (10 ));
Real_record myrecord;
BEGIN
-- SELECT... INTO assignment statement
SELECT emp_id, emp_name INTO real_record FROM emp WHERE emp_id = '001 ';
.....
END;

You can also use Attribute types to reference the data types of variables or columns in the database.

● Declare the variable icode,Reference a column in a tableData Type of emp. id
Icode emp. id % TYPE;

● Declare the emp_rec variable.Reference TableAll Column Record types in emp
Emp_rec emp % ROWTYPE;