PL/SQL of Oracle Database notes

PL/SQL

PL/SQL is a block-structured language that places a set of statements in a block and sends it to the server at once. It is an extended language combined with Oracle process language and Structured Query language

Composition of PL/SQL code blocks

Declare--The markup declaration section is used to declare some variables before begin

Begin-A sign of the beginning of the main program body

Main program body block;

[exception

When the exception name then exception processing method;

When the exception name then exception processing method;

...

When and then exception handling method;]

End --The mark of the end of the main program title

The constants and variables of PL/SQL

The definition of a variable:

Declare--Used to declare variables

Variable name variable type;--direct definition does not assign an initial value

Variable name variable type: = variable value;--Define variables and assign initial values

The value of the database in which the variable name matches; --Directly using the values in the database to match the example (name Person.name%type)

Definitions of constants:

constant name constant data type: = value;--note constant is required

(The host constant: defined as the normal constant definition, used as: The variable name code is as follows:)

var varchar (BEGINSELECTto fromWHERE empno= 7499  END;

operator in PL/SQL

Type symbol description

Assignment Operator: = assignment in PL/SQL

Special Characters | | String connector

--single-line comment in PL/SQL

/*,*/Multi-line comments in PL/SQL, multi-line annotations cannot be nested

<<,>> tag separator, only to mark special locations in the program

.. Range operators such as: 1. 5 marks from 1 to 5

Arithmetic Operators +-*/underlying arithmetic operators

* * Exponentiation Operation Example: 2**3=8

Relational operators >,<, >=, <=, = basic relational operators

<>,!= Not equal to operator

Logical operator And,or,not logical operator

The main execution part of the PL/SQL program

Assignment statement:

(1) Receiving input from the user:

DECLARE     int;--Define a variable to receive the value entered by the user begin N: =&    A;--pops up a message box to enter end;

(2) Assigning values from query results

 set  Serveroutput on   declare  n  number  (4     begin  select  empno into  n from  emp where  =   '  

PL/SQL Control statements

(1) If...then elsif ... then end if;

Execute then if correct, otherwise execute else (elsif is nested judgment)

Notice elsif, there's less e.

(2) Case Var when ... then when ... then end

Logically select from a numeric value

(3) Loop exit End Loop

loop control, execute exit with judgment statement

(4) Loop exit when ... end loop

Ditto, execute exit when the When is true

(5) While condition loop loop body End loop

Loop when while is true

(6) The For loop variable in [rerverse meaning is reversed i.e. from the upper bound to the lower limit loop]. Upper loop loop body End loop

Cycles of known cycles

                                    

PL/SQL of Oracle Database notes