This chapter describes the basic structure of PLSQL statements. Definitions of PLSQL variables and constants. Execution of PLSQL statements. PLSQL Introduction a) PLSQL (ProceduralLanguageSQL, process language SQL) I. it is an extension language ii that combines Oracle process language and structured query language. PLS
This chapter describes the basic structure of PL/SQL statements. PL/SQL statements about PL/SQL common PL/SQL functions. PL/SQL Introduction a) PL/SQL (Procedural Language/SQL, process Language/SQL) I. it is an extension language ii that combines Oracle process language and structured query language. PL/S
Objectives of this Chapter
Basic PL/SQL Structure
PL/SQL variable and constant Declaration
Execution of PL/SQL programs
Understand common PL/SQL Functions
PL/SQL Introduction
1. PL/SQL Overview
A) PL/SQL (Procedural Language/SQL, process Language/SQL)
I. It is an extension language that combines Oracle process language and Structured Query Language
Ii. PL/SQL supports multiple data types. control structures such as conditional statements and cyclic statements can be used.
Iii. PL/SQL can be used to create stored procedures, triggers, and packages. It can also be used to process business rules, database events, or add program logic to SQL command execution.
2. Advantages of PL/SQL
A) supports SQL
B) Support for Object-Oriented Programming (OOP)
C) better performance
D) portability
E) Integration with SQL
F) Security
Basic Structure of PL/SQL
1. Basic Structure of PL/SQL
A) PL/SQL is a programming language.
Blocks are the most basic structure in PL/SQL programs. All PL/SQL programs are composed of blocks.
B) the PL/SQL block consists of three parts: Variable declaration, program code, and Exception Handling Code:
I. DECLARE
1. Mark declaration part
2. Declare some variables, constants, user-defined data types and cursors
Ii. BEGIN
1. mark the start of the main program body
2. The main program body. Various valid statements can be added here.
Iii. EXCEPTION
1. mark the start of Exception Handling
2. Exception Handling program. Execute this part when an error occurs in the program.
Iv. END
Mark the end part of the main program body
2. PL/SQL Character Set
Valid characters in I. PL/SQL include the following three types:
Ii. all uppercase and lowercase English letters
Iii. 0 ~ 9 Arabic numerals
Iv. operators, including (,), +,-, *,/, <,>, and ,! , =, @, %, Etc.
B) PL/SQL identifier
The maximum length of an identifier is 30 characters, which is case-insensitive. However, we recommend that you use case-insensitive characters in the identifier to increase the readability of the program.
3. PL/SQL Operators
A) PL/SQL Operators
I. Arithmetic Operators
Add (+), subtract (-), multiply (*), divide (/), multiply (*), and connect (|)
Ii. Relational operators
=, <> (Or! =), <,>, >=, <=, BETWEEN... AND..., IN, LIKE, IS NULL
Iii. logical operators
Logic AND (AND), logic OR (OR), logic NOT (NOT)
PL/SQL constants and variables
1. PL/SQL constants and variables
A) when the PL/SQL program is running, some variables need to be defined to store some data. Constants and variables must be declared before use. You can use DECLARE to DECLARE variables. The syntax is as follows:
DECLARE
<变量名1> <数据类型1> ;
<变量名2> <数据类型2> ;
......
<变量名n> <数据类型n> ;
B) Multiple constants and variables can be declared simultaneously in the DECLARE block. To declare a common constant or variable, you must specify the following information:
I. Name of a constant or variable
Ii. Data Types of constants or variables
2. Declare Constants
A) the basic format of declared constants is as follows:
B) <常量名> Constant <数据类型> : = <Value>;
I. The keyword constant indicates that a constant is declared. Once a constant is defined, its value will not change in future use.
Ii. Some fixed sizes to prevent some changes, it is best to define them as constants.
C) Example
Pass_Score constant INTEGER: = 60;
3. Declare Variables
A) the basic format of declared variables is as follows:
I. <变量名> <数据类型> [(Width): = <初始值> ];
Variable declaration has no keywords, but you must specify the data type. The width and initial values can be defined or not defined.
Ii. Example
Address VARCHAR2 (30): = 'address unknown ';
B) PL/SQL is assigned a NULL value to an uninitialized variable by default.
I. Example
Address VARCHAR2 (30 );
4. Case studies
A) For example:
Set serveroutput on; DECLARE Pass_Score constant INTEGER: = 60; Address VARCHAR2 (30): = 'haidian Beijing'; BEGIN DBMS_OUTPUT.PUT_LINE (Pass_Score); DBMS_OUTPUT.PUT_LINE (Address); END;
B) use the set serveroutput on command to SET the environment variable SERVEROUTPUT to the open state, so that PL/SQL programs can output results in SQL * Plus (SET SERVEROUTPUT OFF; no results are output afterwards)
C) use the DBMS_OUTPUT.PUT_LINE () function to output the parameter value.
5. Default database administrator user
A) when installing the Oracle database, the username SYS and SYSTEM are automatically created, the user password is set during installation, and the database administrator (DBA) is automatically assigned.
B) SYS user
This account has all database management functions. The basic tables and views in all database data dictionaries are stored in the user's SYS Schema, these basic tables and views are important for Oracle database operations.
C) SYSTEM user
This account can perform all other daily management tasks except for database backup and recovery and Database Upgrade.
Execution of PL/SQL programs
1. PL/SQL Program Execution
A) The execution of PL/SQL programs includes
I. Assignment Statement
Ii. Process Control statements
Iii. SQL statements
Iv. cursor statement
2. Use the value assignment statement
A) You can set an initial value when declaring or processing a variable. You can also assign values to variables in the execution part of the program.
SET ServerOutPut ON;DECLARETrainName VARCHAR2(30);BEGINTrainName := 'Oracle Administration';Dbms_output.put_line(TrainName);END;
B) shows the running result (variable names are case-insensitive)
3. Process Control statements
A) process control statements are the key to all procedural programming languages.
B) The main control statements of PL/SQL are as follows:
I. if... then... else (elsif)
If the condition is correct, then is executed; otherwise, else is executed (elsif is nested)
Ii. case
Logical selection from values
Iii. loop... exit... end
Loop Control: Execute exit with a judgment statement.
Iv. loop... exit when... end
Same as above. Execute exit when is true.
V. while... loop... end
Loop when while is true
Vi. for... in... loop... end
Cycles with known cycles
C) conditional statement IF
I. IF statements are executed based on the value of the conditional expression. The syntax structure is as follows:
IF <条件表达式> THEN
<执行语句> ... <执行语句n>
[ELSIF <条件表达式> THEN
<执行语句> ... <执行语句n>
...
ELSE
<执行语句> ]
End if;
Ii. The ELSIF clause is optional.
Iii. The Program specifies an integer variable Number. The IF statement is used to determine whether the Number variable is positive, negative, or 0.
SET ServerOutPut ON; DECLARENumber INTEGER: =-10; Begin IF Number <0 THEN dbms_output.put_line ('negative number'); ELSIF Number> 0 THEN dbms_output.put_line ('positive number '); ELSE dbms_output.put_line ('0'); end if; End;
Iv. The execution result is shown in the right figure:
D) Branch statement CASE
I. The branch statement is used to determine the specified variable, select a row that meets the conditions from the specified list, and return the value of the row as the result of the CASE statement.
The syntax structure of the CASE statement is as follows:
CASE <变量>
WHEN <表达式1> THEN value 1
WHEN <表达式2> THEN value 2
......
WHEN <表达式n> THEN value n
ELSE value n + 1
Ii. Declare an integer variable varDAY and an integer variable Result. Use the CASE statement to determine the day of the week for varDAY. If the variable varDAY is 1 ~ The week information is displayed. Otherwise, the system returns the message "data out of bounds ";
SET ServerOutPut ON; DECLARE varDAY INTEGER: = 3; Result VARCHAR2 (20); BEGIN Result: = Case varDAY WHEN 1 THEN 'mon 'when 2 then' Tuesday 'when 3 then' Wednesday 'when 4 then' THEN 'then 5 then' Friday 'when 6 then' saturday' WHEN 7 THEN 'sunday' else' data out of bounds 'end; dbms_output.put_line (Result); END;
Iii. Running Effect
E) LOOP... EXIT... END
I. The function of this statement is to repeatedly execute the program block in the loop body until the EXIT statement is executed, the loop is exited.
The syntax structure of the LOOP... EXIT... END statement is as follows:
LOOP
<程序块 1>
IF <条件表达式> THEN
EXIT
END IF
<程序块 2>
End loop;
Ii. Computing 1 ~ 4. accumulate
SET ServerOutPut ON;DECLARE varNum INTEGER := 1; varSum INTEGER := 0;BEGIN LOOP varSum := varSum + varNum; dbms_output.put_line(varNum); IF varNum = 4 THEN EXIT; END IF; dbms_output.put_line('+'); varNum := varNum + 1; END LOOP; dbms_output.put_line('='); dbms_output.put_line(varSum);END;
F) LOOP... exit when... END
I. The function of this loop statement is to repeatedly execute the program block in the loop body and EXIT the loop until the judgment statement after EXIT WHEN is met.
Ii. LOOP... exit when... the syntax structure of the END statement is as follows:
LOOP
<程序块 1>
EXIT WHEN <条件表达式>
<程序块 2>
Iii. Re-implement 1 ~ 4. accumulate
SET ServerOutPut ON; DECLARE varNum INTEGER := 1; varSum INTEGER := 0; BEGIN LOOP varSum := varSum + varNum; dbms_output.put_line(varNum); EXIT WHEN varNum = 4; dbms_output.put_line('+'); varNum := varNum + 1; END LOOP; dbms_output.put_line('='); dbms_output.put_line(varSum); END;
Iv. Running Effect
G) LOOP statement WHILE... LOOP... END LOOP
I. The function of this statement is to repeatedly execute the program blocks in the loop body when the statement conditions after WHILE are true.
The syntax structure of the WHILE... LOOP... end loop statement is as follows:
WHILE <条件表达式>
LOOP
<程序块>
End loop;
Ii. Implement 1 ~ again ~ 4. accumulate
SET ServerOutPut ON;DECLARE varNum INTEGER := 1; varSum INTEGER := 0;BEGIN WHILE varNum <= 4 LOOP varSum := varSum + varNum; dbms_output.put_line(varNum); IF varNum < 4 THEN dbms_output.put_line('+'); END IF; varNum := varNum + 1; END LOOP; dbms_output.put_line('='); dbms_output.put_line(varSum);END;
H) LOOP statement FOR... IN... LOOP... END LOOP
I. This statement defines a loop variable and specifies the initial value and end value of the loop variable. The variable is automatically added once every cycle.
Ii. FOR... IN... LOOP... end loop statement Syntax:
FOR <循环变量> IN <初始值> .. <终止值>
LOOP
<程序块>
End loop;
Iii. Implement 1 ~ again ~ 4. accumulate
SET ServerOutPut ON;DECLARE varNum INTEGER := 1; varSum INTEGER := 0;BEGIN FOR varNum IN 1..4 LOOP varSum := varSum + varNum; dbms_output.put_line(varNum); IF varNum < 4 THEN dbms_output.put_line('+'); END IF; END LOOP; dbms_output.put_line('='); dbms_output.put_line(varSum);END;
4. Exception Handling
A) PL/SQL programs may encounter errors or exceptions during running.
B) For example, you cannot establish a connection to Oracle or use 0 as the divisor. A good program should handle possible exceptions. The EXCEPTION handling code is implemented in the EXCEPTION block.
C) You can use the WHEN statement to define exceptions. The WHEN statement is used as follows:
EXCEPTION
WHEN <异常情况名> THEN
<异常处理代码>
WHEN <异常情况名> THEN
<异常处理代码>
...
WHEN OTHERS THEN
<异常处理代码>
D) predefined exception types
E) Case studies
I. An exception occurs when a string is assigned to a variable of the NUMBER type.
SET ServerOutPut ON;DECLARE varNum NUMBER;BEGIN varNum := 'abc';EXCEPTION WHEN VALUE_ERROR THEN dbms_output.put_line('VALUE_ERROR');END;
Common PL/SQL Functions
1. PL/SQL common functions
A) In PL/SQL programming, Oracle database functions are often used to complete the functions required by users.
B) In addition to statistical functions, there are also numeric functions, numeric functions, date functions, and so on.
2. Balanced Functions
3. Numeric Functions
4. Date Functions
Summary
Basic PL/SQL Structure
Declaration of variables and constants
Process control statement
Common functions