Shulou(Shulou.com)06/01 Report--

I. Overview, PL/SQL block structure

PL/SQL is an extension of the SQL language to Oracle products. The PL/SQL block is divided into three parts: declaration part, executable part and exception part.

DECLARE

...

BEGIN

...

EXCEPTION

...

END

Variable declaration content: give the variable the appropriate name, data type, define the variable (standard, record), control the scope of the variable.

Variable naming rules: variables begin with characters; can contain numbers, underscores, $, #; length range 1-30; case-insensitive; system keywords cannot be used.

Ps. Commands commonly used in PL/SQL:

1. Turn on the output switch SET SERVEROUTPUT ON

2. Use the output information DBMS_OUTPUT.PUT_LINE of the system package (the value of'x is:'| | x)

2. 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. Circular statement

A, basic cycle (LOOP)

Unconditional loop, to avoid entering an infinite loop, the statements of the LOOP loop must use EXIT or EXIT WHEN statements.

LOOP

...

END LOOP

B, WHILE cycle

WHILE condition LOOP

...

END LOOP

C, FOR cycle

FOR counter IN [REVERSE] start..end

LOOP

...

END LOOP

3. Sequence control

GOTO statement: unconditionally transfers control to the statement specified by the label.

NULL statement: do nothing but transfer control to the next statement for situations where the statement structure requires but nothing needs to be done.

GOTO xxxx

...

NULL

III. Anomalies

System exception (predefined exception)

EXCEPTION

WHEN xxxx THEN

....

Custom exception

DECLARE

-Custom exception

Xxxxx EXCEPTION

BEGIN

-- explicitly throw an exception

RAISE xxxxx

EXCEPTION

-- exception handling

WHEN xxxxx THEN

....

END

IV. Compound variables (records)

A record is a compound variable made up of several related values, which is often used to support the return value of a SELECT statement. Using records, you can process a row of data into a single unit without having to process 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

Or you can use attribute types to refer to variables or the data type of a column in the database.

● declares the variable icode, referencing the data type of a column emp.id in the table

Icode emp.id%TYPE

● declares the variable emp_rec references all column record types in the table emp

Emp_rec emp%ROWTYPE

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.