Oracle basics Chapter 3 OraclePL/SQL Basics

Source: Internet
Author: User
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

@ Wentasy

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: info-contact@alibabacloud.com 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.