Oracle PL/SQL language basics (2)

BR> less than Operator

<=
Operator less than or equal

>
Greater than Operator

> =
Operator greater than or equal

=
Equal to operator

! =
Not equal to operator

<>
Not equal to operator

: =
Value assignment operator


Table 6 displays comparison operators.

Operator
Operation

Is null
Returns true if the operand is null.

Like
Compare string values

Between
Whether the verification value is within the range

In
Verify that the operands are in the set series of values.


Table 7.8 displays logical operators.

Operator
Operation

And
Both conditions must be met

Or
If either of the two conditions is met

Not
Invert


Execution part

The execution part contains all statements and expressions. The execution part starts with the keyword begin and ends with the keyword exception. If the exception does not exist, it ends with the keyword end. Use a semicolon to separate each statement and assign values to each variable using the = or select into or fetch into operator. Errors in the execution part will be solved in the Exception Handling Section, you can use another PL/SQL block in the execution part. This block is called a nested block.

All SQL data operation statements can be used for execution. PL/SQL blocks cannot display the SELECT statement output on the screen. The SELECT statement must contain an into substring or a part of the cursor. the variables and constants used for execution must be declared in the declaration part first. The execution part must contain at least one executable statement, null is a legal executable statement. The transaction control statements commit and rollback can be used in the execution part. The data definition language cannot be used in the execution part, DDL statements are used together with execute immediate or dbms_ SQL calls.

Execute a PL/SQL Block

In SQL * Plus, the execution of anonymous PL/SQL blocks is input/after the PL/SQL blocks. The following example shows:

Declare
V_comm_percent constant number: = 10;
Begin
Update EMP
Set comm = Sal * v_comm_percent
Where deptno = 10;
End
SQL>/
PL/SQL procedure successfully completed.

SQL>


The name of the program is different from that of the anonymous program. The execute keyword must be used to execute the named program block:

Create or replace procedure update_commission
(V_dept in number, v_pervent in number default 10) is
Begin
Update EMP
Set comm = Sal * v_percent
Where deptno = v_dept;
End

SQL>/

Procedure created

SQL> execute update_commission (10, 15 );

PL/SQL procedure successfully completed.

SQL>


If you execute this program in another named block or anonymous block, you do not need to execute it.

Declare
V_dept number;
Begin
Select a. deptno
Into v_dept
From EMP
Where job = 'President'
Update_commission (v_dept );
End
SQL>/
PL/SQL procedure successfully completed
SQL>


Control Structure

The control structure controls the code lines of PL/SQL program flows. PL/SQL supports Conditional Control and cyclic control structures.

Syntax and usage

If... then

Syntax:

If condition then
Statements 1;
Statements 2;
....
End if


The IF statement determines whether the condition is true. If yes, the statement after then is executed. If the condition is false or null, the statement between then and end if is skipped, execute the statement after end if.

If... then... else

Syntax:

If condition then
Statements 1;
Statements 2;
....
Else
Statements 1;
Statements 2;
....
End if


If the condition is true, the statement between then and else is executed. Otherwise, the statement between else and end if is executed.

If can be nested, you can use if or if. Else statements in if or if. Else statements.

If (A> B) and (A> C) then
G: =;
Else
G: = B;
If C> G then
G: = C;
End if
End if


If... then... elsif

Syntax:

If condition1 then
Statement1;
Elsif condition2 then
Statement2;
Elsif condition3 then
Statement3;
Else
Statement4;
End if;
Statement5;


If the condition condition1 is true, execute statement1, and then execute statement5; otherwise, judge whether condition2 is true; if it is true, execute statement2, and then execute statement5. The Condition 3 is the same. If condition1, if neither condition2 nor condition3 is true, statement4 is executed and statement5 is executed.
Loop Control

The basic form of loop control is loop statements. The statements between loop and end loop are executed infinitely. The syntax of the loop statement is as follows:

Loop
Statements;
End Loop

The statement between the loop and the end loop cannot be executed infinitely. Therefore, when using the loop statement, you must use the exit statement to force the loop to end. For example:

X: = 100;
Loop
X: = x + 10;
If X> 1000 then
Exit;
End if
End loop;
Y: = X;


The value of Y is 1010.

The exit when statement ends the loop. If the condition is true, the loop ends.

X: = 100;
Loop
X: = x + 10;
Exit when x> 1000;
X: = x + 10;
End loop;
Y: = X;


While... loop

While... loop has a condition associated with the loop. If the condition is true, the statement in the loop body is executed. If the result is false, the loop ends.

X: = 100;
While x <= 1000 Loop
X: = x + 10;
End loop;
Y = X;


For... loop

Syntax:

For counter in [reverse] start_range... end_range Loop
Statements;
End loop;


The number of loops in a loop and while loop is uncertain. The number of loops in a for loop is fixed. Counter is an implicitly declared variable whose initial value is start_range, the second value is start_range + 1 until end_range. If start_range is equal to end _ range, the loop is executed once. If the reverse keyword is used, the range is a descending order.

X: = 100;
For v_counter in 1 .. 10 Loop
X: = x + 10;

End Loop
Y: = X;


To exit the for loop, use the exit statement.

Tag

You can use tags to make the program more readable. The block or loop can be marked. The label format is <>.

Tag Block

<>
[Declare]
.........
Begin
........
[Exception]
.......
End label_name


Mark Loop

<>
Loop
.........
<>
Loop
..........
<>
Loop
....

Exit outer_loop when v_condition = 0;
End loop innermost_loop;
..........
End loop inner_loop;
End loop outer_loop;


GOTO statement

Syntax:

Goto label;

When the GOTO statement is executed, the control will immediately switch to the statement marked by the tag. PL/SQL has some restrictions on the GOTO statement. For block, loop, and if statements, it is illegal to jump from the outer layer to the inner layer.

X: = 100;
For v_counter in 1 .. 10 Loop
If v_counter = 4 then
Goto end_of_loop
End if
X: = x + 10;
<>
Null
End Loop

Y: = X;


Note: NULL is a legal executable statement.

Nesting

A program block can have another program block. In this case, it is nested. Note that variables are nested. variables defined in the most external block can be used in all child blocks. If the child block defines the same variable name as the external block variable, when executing a sub-block, the variables defined in the sub-block are used. The variables defined in the Child block cannot be referenced by the parent block. Similarly, the GOTO statement cannot jump from the parent block to the sub-block. Otherwise, it is legal.

Outer Block
Declare
A_number integer;
B _number integer;
Begin
-- A_number and B _number are available here
<>
Declare
C_number integer
B _number number (20)
Begin
C_number: = a_number;
C_number = outer_block. B _number;
End sub_block;
End out_block;


Summary

This article introduces the basic PL/SQL syntax and how to use the PL/SQL language to design and run the PL/SQL block, and integrate the PL/SQL program into the Oracle server, although PL/SQL programs are embedded into Oracle databases as functional blocks, the close combination of PL/SQL and Oracle databases has enabled more and more Oracle Database administrators and developers to use PL/SQL.

Bytes ---------------------------------------------------------------------------------------------------
Common Oracle scripts
----------------

1. view all objects currently
-------------------
SQL> select * From tab;

2. Create an empty table with the same structure as Table
-----------------------------
SQL> Create Table B as select * from a where 1 = 2;

SQL> Create Table B (B1, B2, B3) as select A1, A2, A3 from a where 1 = 2;

3. Check the database size and space usage
--------------------------------
SQL> Col tablespace format A20
SQL> select B. file_id File ID,
B. tablespace_name tablespace,
B. file_name physical file name,
B. Total Bytes bytes,
(B. bytes-sum (nvl (A. bytes, 0) already in use,
Sum (nvl (A. bytes, 0) remaining,
Sum (nvl (A. bytes, 0)/(B. bytes) * 100 percentage remaining
From dba_free_space A, dba_data_files B
Where a. file_id = B. file_id
Group by B. tablespace_name, B. file_name, B. file_id, B. bytes
Order by B. tablespace_name
/
Dba_free_space -- the remaining space in the tablespace
Dba_data_files -- data file space usage

4. view existing rollback segments and their statuses
-----------------------------
SQL> Col segment format A30
SQL> select segment_name, owner, tablespace_name, segment_id, file_id, status from dba_rollback_segs;

5. view the data file placement path
---------------------------
SQL> Col file_name format A50