Oracle Database-Practical Operations (3) PL/SQL

Oracle----PL/SQL

PL/SQL is a programming language that combines process language (procedural Language) with Structured Query Language (SQL)

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

Supports multiple data types, such as large objects and collection types, with control structures such as conditions and loops

Can be used to create stored procedures, triggers, and packages to add program logic to the execution of SQL statements

Tightly integrated with Oracle Server and Oracle Tools for portability, flexibility, and security

PL/SQL The advantages

Support for SQL, which can be used in PL/sql:

– Data manipulation Commands

– Transaction control commands

– Cursor Control

– SQL functions and SQL operators

Supports object-oriented programming (OOP)

Portability, ORALCE databases that can run on any operating system and platform

better performance, PL/SQL is compiled and executed

PL/SQL System Structure

pl/sql[ code ] blocks are the basic units that make up PL/SQL programs

Group logically related declarations and statements together

PL/SQL is divided into three parts, declaring parts, executable parts, and exception handling parts

PL/SQL the compound symbol in

: = Assignment operator

|| Join operator

--Single line comment

/**/Multi-line comments

<<>> label Separators

.. Range operator

* * exponentiation operator

Variables and constants

Variables and constants can be used in PL/SQL blocks

1. In the declaration section Declaration, before use must first declare

2. Declaration must specify a data type, each row declares an identifier

3. Use in SQL statements and procedure statements in the executable section

syntax for declaring variables and constants:

identifier [CONSTANT] datatype [Not NULL]

[:= | DEFAULT expr];

There are two ways to assign a value to a variable:

1 Using an assignment statement: =

2 using the Selectinto statement

DECLARE

Icode VARCHAR2 (6);

P_CATG VARCHAR2 (20);

P_rate number;

C_rate CONSTANT Number: = 0.10;

BEGIN

...

Icode: = ' i205 ';

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 '; --Check King's salary and assign to VV variable

Dbms_output. Put_Line (' SAL ' | |   VV); --Console output

End

Data type

Numeric data types

Character data type

Character data types include:

1. CHAR

2. VARCHAR2

3. LONG

4. RAW

5. LONG RAW

Date Time Type

1. Storing date and time data

2. Two commonly used date and time types

①date

②timestamp

Boolean Data types

1. This category has only one type, that is, the Boolean type

2. For storing logical values (TRUE, FALSE, and NULL)

3. Cannot insert Boolean data into the database

4. Column values cannot be saved in a Boolean variable

5. Logical operations can only be performed on Boolean variables

LOB Data Type

Used to store unstructured data such as large text, images, video clips, and sound clips.

LOB data types store up to 4GB of data.

LOB types include:

6. BLOBs Store large binary objects in the database

7. CLOB Storing large character data in the database

8. NCLOB Storing large Unicode character data

9. BFILE Storing large binary objects in operating system files

Property type

The data type used to reference the database column, and the record type that represents a row in the table

There are two types of attributes:

1. %TYPE -Reference variables and data types of database columns

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

Advantages of using attribute types:

1. 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 comparisons are used to compare the values of variables and constants, which are called Boolean expressions

Boolean expressions consist of relational operators and variables or constants

Control structure

The process Control structure supported by PL/sql:

• Condition Control

– IF statement

– Case statement

• Cycle control

– Loop loops

– While loop

– for loop

• Sequential control

– GOTO statement

– NULL statement

• Loop control for repeated execution of a series of statements

• Loop control statements include:

– LOOP, exit, and exit when

• Three types of loop control:

– Loop -Unconditional cycle

– While -looping by condition

– for-loop fixed number of times

Loop...exit...when...endloop Cycle Control

The syntax structure using the Loop...exit...when...end Loop loop control is as follows:

Loop

Circulation body;

Exitwhen cycle conditions;

End Loop

While...loop...end Loop loop control

The syntax for this loop control is as follows:

While condition

Loop

Executes the statement segment;

End Loop;

Cases:

Declare counter number:=0;

Begin

Loop--[Unconditional] Cycle

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, then execute the loop

Dbms_output. Put_Line (' LPLPL ' | | Counter);

counter:=counter+1;

Endloop;

End

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

Declare i number:=0;

Begin

Forx in 1..9 loop--for loop: x loops in 1-9

Dbms_output. Put_Line (' LPLPL ' | | x);

Endloop;

End

Case statement is used to compare multiple values against a single variable or an expression

Execution Case before the statement, the value of the selector is calculated first

BEGIN

Case ' &grade '

When the ' A ' then dbms_output. Put_Line (' excellent ');

When the ' B ' then dbms_output. Put_Line (excellent ');

When the ' C ' then dbms_output. Put_Line (' good ');

When the ' D ' then dbms_output. Put_Line (' General ');

When the ' F ' then dbms_output. Put_Line (' poor ');

ELSE Dbms_output. Put_Line (' No such result ');

END case;

END;

Error handling

• Errors that occur while running the program are called exceptions

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

• There are two types of exceptions:

– Predefined exceptions-implicitly thrown when a PL/SQL program violates an Oracle rule or exceeds system limits

– User-defined exception-the user can define an exception in the Declarations section of the PL/SQL block, and the custom exception is explicitly raised by the RAISE statement

Handling pre-defined exceptions

Access_into_null occurs when an object is not initially initialized

Case_not_found occurs when the option in the case statement does not match the data entered by the user

Collection_is_null occurs when assigning a value to a table or array that has not been initialized

Cursor_already_open occurs when a user tries to open a cursor that is already open

Dup_val_on_index occurs when a user tries to present a duplicate value in a database column that uses a unique index

Invalid_cursor occurs when an illegal cursor operation is performed (such as opening a cursor that has not yet been opened)

Invalid_number occurs when converting a string to a number

Login_denied occurs when the user name or password entered is invalid

No_data_found occurs when a requested row does not exist in the table, in addition, when the program references an element that has been deleted

Storage_Error occurs when memory corruption or PL/SQL runs out of memory

Too_many_rows occurs when multiple rows are returned after executing a selectinto statement

Value_error occurs when a size limit error is generated

Zero_divide appears when dividing by zero

Others for all exceptions

Handling user-defined exceptions

DECLARE

Invalidcategory EXCEPTION;

Category VARCHAR2 (10);

BEGIN

Category: = ' &category ';

Ifcategory not in (' Accessories ', ' top cover ', ' spares ') then

RAISE invalidcategory;

ELSE

Dbms_output. Put_Line (' The category you entered is ' | | category ');

ENDIF;

EXCEPTION

When Invalidcategory Then

Dbms_output. Put_Line (' cannot 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 (' rates are: ' | | rate);

ENDIF;

EXCEPTION

When Rate_exception Then

Raise_application_error (-20001, ' unspecified rates ');

END;

Summarize:

PL/SQL is a portable, high-performance transactional language

PL/SQL engine resides on Oracle server

A PL/SQL block consists of a declaration part, an executable part, and an exception handling part

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

Control structure including conditional control, cyclic control and sequential control

PL/SQL supports dynamic

Errors that occur at run time are called exceptions

Exceptions can be categorized into predefined exceptions and user-defined exceptions

Oracle Database-Practical Operations (3) PL/SQL