Oracle Database-Practical Operations (3) PL/SQL

Source: Internet
Author: User
Tags case statement


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


Icode VARCHAR2 (6);


P_rate number;

C_rate CONSTANT Number: = 0.10;



Icode: = ' i205 ';

SELECT p_category, itemrate* c_rate

Into P_CATG, p_rate

From Itemfile WHERE itemcode= Icode;



DECLARE VV number:=100; --Anonymous code block, not saved


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


Data type

Numeric data types

Character data type

Character data types include:




4. RAW


Date Time Type

1. Storing date and time data

2. Two commonly used date and time types



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:


Circulation body;

Exitwhen cycle conditions;

End Loop

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

The syntax for this loop control is as follows:

While condition


Executes the statement segment;

End Loop;


Declare counter number:=0;


Loop--[Unconditional] Cycle

Exit when counter>10;

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


End Loop;



Declare counter number:=0;


While Counter<9 Loop--First judge, then execute the loop

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





Declare i number:=0;


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

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



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


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;


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


Invalidcategory EXCEPTION;

Category VARCHAR2 (10);


Category: = ' &category ';

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

RAISE invalidcategory;


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



When Invalidcategory Then

Dbms_output. Put_Line (' cannot recognize this category ');



Rate Itemfile.itemrate%type;

Rate_exception exception;


SELECT NVL (itemrate,0) into rate from Itemfile

WHERE ItemCode = ' i207 ';

ifrate = 0 Then

RAISE rate_exception;


Dbms_output. Put_Line (' rates are: ' | | rate);



When Rate_exception Then

Raise_application_error (-20001, ' unspecified rates ');



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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.