Oracle Database-practical operations (3) PL/SQL

Oracle Database-practical operations (3) PL/SQL

Oracle ---- PL/SQL

PL/SQL is a programming Language that combines Procedural Language and Structured Query Language (SQL ).

PL/SQL is unique to Oracle and is an extension of SQL. Different database vendors have similar "dialects" and provide new features.

Supports multiple data types, such as large objects and Collection types, and control structures such as conditions and loops.

It can be used to create stored procedures, triggers, and packages, and add program logic to SQL statement execution.

Tightly integrated with Oracle servers and Oracle tools, with portability, flexibility, and security

Advantages of PL/SQL

SQL is supported, which can be used in PL/SQL:

-Data manipulation command

-Transaction Control Command

-Cursor control

-SQL functions and SQL Operators

Support for Object-Oriented Programming (OOP)

Portability: Oralce databases that can run on any operating system and Platform

Better performance, PL/SQL compiled and executed

 

PL/SQL Architecture

 

The PL/SQL [Code] block is the basic unit of the PL/SQL program.

Combine logic-related statements and statements

PL/SQL consists of three parts: Declaration, executable, and exception handling.

 

Composite symbols in PL/SQL

: = Value assignment operator

| Connection Operator

-- Single line comment

/**/Multi-line comment

<> Tag Separator

... Range Operator

** Power operators

Variables and constants

Variables and constants can be used in PL/SQL blocks.

1. Declare part of the Declaration before use

2. The data type must be specified during declaration. Each row declares an identifier.

3. Use

Syntax for declaring variables and constants:

Identifier[CONSTANT]Datatype[Not null]

[: = | DEFAULTExpr];

There are two ways to assign values to variables:

1 use the value assignment statement: =

2. Use the SELECTINTO statement

DECLARE

Icode VARCHAR2 (6 );

P_catg VARCHAR2 (20 );

P_rate NUMBER;

C_rate constant number: = 0.10;

BEGIN

...

Icode: = 'i20 ';

SELECT p_category, itemrate * c_rate

INTO p_catg, p_rate

FROM itemfile WHERE itemcode = icode;

...

END;

DECLARE vv number: = 100; -- anonymous code block, not saved

Begin

Select sal into vv from emp where ename = 'King'; -- find KING's salary and assign it to vv variable

 

DBMS_OUTPUT.PUT_LINE ('sal '| vv); -- console output

End;

Data Type

 

Digital Data Type

 

Character Data Type

Character data types include:

1. CHAR

2. VARCHAR2

3. LONG

4. RAW

5. LONG RAW

Date and Time Type

1. Store Date and Time Data

2. Two common date and time types

① DATE

② TIMESTAMP

Boolean data type

1. This category has only one type, namely, BOOLEAN.

2. used to store logical values (TRUE, FALSE, and NULL)

3. BOOLEAN data cannot be inserted into the database.

4. The column value cannot be saved to a BOOLEAN variable.

5. logical operations can only be performed on BOOLEAN variables

LOB Data Type

It is used to store unstructured data such as big text, images, video clips, and audio clips.

The LOB data type can store up to 4 GB Data.

LOB types include:

6.BLOB stores large binary objects in the database

7.CLOB stores large character data in the database

8.NCLOB stores large UNICODE character data

9.BFILE stores large binary objects in operating system files

Attribute type

Used to reference the data type of database columns and the record type of a row in the table

There are two attribute types:

1.% TYPE-Data Types of referenced variables and database Columns

2.% ROWTYPE-Provides a record type that represents a row in the table.

Advantages of using attribute types:

1. You do not need to know the specific type of the referenced table column

2. If the data type of the referenced object changes, the Data Type of the PL/SQL variable also changes.

Logical comparison

Logical comparison is used to compare values of variables and constants. These expressions are called boolean expressions.

A boolean expression is composed of Relational operators and variables or constants.

Control Structure

PL/SQL supports the following process control structures:

• Condition Control

-IF statement

-CASE statement

• Loop Control

-LOOP

-WHILE Loop

-FOR Loop

• Sequential Control

-GOTO statement

-NULL statement

• Loop control is used to repeatedly execute a series of statements

• Cyclic control statements include:

-LOOP, EXIT, and EXIT WHEN

• Three Types of loop control:

-LOOP-unconditional LOOP

-WHILE-conditional Loop

-FOR-fixed cycles

Loop... exit... when... endloop loop Control

The syntax structure of loop... exit... when... end loop control is as follows:

Loop

Loop body;

Exitwhen cycle condition;

End loop

While... loop... end loop control

The loop control syntax is as follows:

While Condition

Loop

Execution statement segment;

End loop;

Example:

Declare counter number: = 0;

Begin

Loop -- [unconditional] loop

Exit when counter> 10;

DBMS_OUTPUT.PUT_LINE ('lplpl' | counter );

Counter: = counter + 1;

 

End loop;

End;

............................................................

Declare counter number: = 0;

Begin

While counter <9 loop -- first judge and then execute loop

DBMS_OUTPUT.PUT_LINE ('lplpl' | counter );

Counter: = counter + 1;

Endloop;

End;

 

....................................................................

Declare I number: = 0;

Begin

Forx in 1 .. 9 loop -- for loop: If x is 1-9

DBMS_OUTPUT.PUT_LINE ('lplpl' | x );

 

Endloop;

 

End;

The CASE statement is used to compare a single variable or expression with multiple values.

Calculate the selector value before executing the CASE statement.

BEGIN

CASE '& grade'

WHEN 'a 'then DBMS_OUTPUT.PUT_LINE ('excellent ');

WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE (excellent ');

WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE ('good ');

WHEN 'd 'then DBMS_OUTPUT.PUT_LINE ('General ');

WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE ('poorer ');

ELSE DBMS_OUTPUT.PUT_LINE ('no such score ');

End case;

END;

Error Handling

• An exception occurs when running the program.

• When an exception occurs, the statement stops execution and the control is transferred to the exception handling section of the PL/SQL block.

• There are two types of exceptions:

-Pre-defined exception-implicit triggering when PL/SQL programs violate Oracle rules or exceed System Restrictions

-User-defined exceptions-you can define exceptions in the Declaration section of PL/SQL blocks. Custom exceptions are explicitly thrown by RAISE statements.

Handle predefined exceptions

Access_1__null appears when the object is not initialized

Case_not_found appears when the options in the CASE statement do not match the data entered by the user.

Collection_is_null appears when assigning values to uninitialized tables or arrays.

Cursor_already_open appears when the user attempts to open an opened cursor

Dup_val_on_index appears when you try to store duplicate values in database columns with unique indexes.

When invalid_cursor executes an illegal cursor operation (for example, opening an unopened cursor ),

Invalid_number appears when converting a string to a number

Login_denied appears when the entered user name or password is invalid

No_data_found appears when there is no request row in the table. In addition, when the program references the deleted element

Storage_error occurs when the memory is damaged or PL/SQL runs out of memory.

Too_many_rows appears when multiple rows are returned after the SELECTINTO statement is executed.

Value_error occurs when a size limit error is generated.

When zero_divide is used as the divisor

Others for all exceptions

 

Handle user-defined exceptions

DECLARE

InvalidCATEGORY EXCEPTION;

Category VARCHAR2 (10 );

BEGIN

Category: = '& Category ';

IFcategory not in ('attachment ', 'topic', 'spare parts') THEN

RAISE invalidCATEGORY;

ELSE

DBMS_OUTPUT.PUT_LINE ('the type you entered is '| category );

ENDIF;

EXCEPTION

WHEN invalidCATEGORY THEN

DBMS_OUTPUT.PUT_LINE ('unable to recognize this category ');

END;

 

DECLARE

Rate itemfile. itemrate % TYPE;

Rate_exception EXCEPTION;

BEGIN

Select nvl (itemrate, 0) INTO rate FROM itemfile

WHERE itemcode = 'i207 ';

IFrate = 0 THEN

RAISE rate_exception;

ELSE

DBMS_OUTPUT.PUT_LINE ('item rate: '| rate );

ENDIF;

EXCEPTION

WHEN rate_exception THEN

RAISE_APPLICATION_ERROR (-20001, 'Unspecified item rate ');

END;

 

Summary:

PL/SQL is a portable high-performance transaction processing language.

PL/SQL engines reside on Oracle servers

The PL/SQL block consists of the declaration part, executable part, and Exception Handling part.

PL/SQL data types include scalar data type, LOB data type, and attribute type

The control structure includes Conditional Control, cyclic control, and sequential control.

PL/SQL supports dynamic SQL

An error occurred during running is called an exception.

Exceptions can be classified into pre-defined exceptions and user-defined exceptions.