Day 4, PL/SQL Basics

2008/03/20

Basic PL/SQL

1. PL/SQL Basics

2. PL/SQL Block Structure
Declare
... Variable Declaration
Begin
....
Exception
....
End;
/

Variable declaration content
Assign a proper variable name, the correct data type of the variable, define the variable, and control the variable range.
Naming rules
It must start with a character and can contain numbers, underscores, $, #, and so on. It must be 1-30 characters in length and case insensitive. It cannot be a system keyword.
Example:
Declare
X varchar2 (10); defines the Variable Area
Begin
X: = 'this is... '; variable value assignment
Dbms_output.put_line ('x value: '| X); | it is a connection character and does not need to be converted.
End
/End and execute
The default value is "no". We can use set
Set serveroutput on size 10000 // enable output. The input volume is also 10000.
At this time, re-execute L to output the result.
Save c: \ plsql.txt to save the above block.
Dbms_output.put ('x value: '| X); meaning no output

-- Line comment content
/*. Block comment ....*/
X varchar2 (10): = 'abce '; Define and assign values

@ C: \ sqlplus.txt content in the execution File

3. Use of branch statements
I. If Branch
If... then
....
Else if... then
....
Else
.....
End if

Example:
Declare
A number;
B varchar2 (10 );
Begin
A: = 2;
If a = 1 then
B: = 'a ';
Elsif A = 2 then
B: = 'B ';
Else
B: = 'C ';
End if;
Dbms_output.put_line (the value of 'B is' | B );
End;
/

Ii. Case Branch
Case
When... then...
Else
End case

Example:
Declare
A number;
B varchar2 (10 );
Begin
A: = 2;
Case
When a = 1 then B: = 'a ';
When a = 2 then B: = 'B ';
....
Else
B: = 'C ';
End case;
Dbms_output.put_line (the value of 'B is' | B );
End;
/

4. Use of loop statements
1. Basic Loop
Loop
...
End Loop
Example:
Declae
X number;
Begin
X: = 0;
Loop
X: = x + 1;
If X> = 3 then
Exit;
End if;
Dbms_output.put_line ('body x values are: '| X );
End loop;
Dbms_output.put_line (the value of 'external X is '| X );
End;
/

2. While Loop
While expression Loop
...
End loop;
Example:
Declae
X number;
Begin
X: = 0;
Loop
X: = x + 1;
Exit when x> = 3
Dbms_output.put_line ('body x values are: '| X );
End loop;
Dbms_output.put_line (the value of 'external X is '| X );
End;
/

Declae
X number;
Begin
X: = 0;
While x <= 3 Loop
X: = x + 1;
Dbms_output.put_line ('body x values are: '| X );
End loop;
Dbms_output.put_line (the value of 'external X is '| X );
End;
/

3. For Loop
For counter in [reverse] start_value... end_value Loop
...
End loop;

Example:
Begin
For I in 1 .. 5 Loop
Dbms_output.put_line ('body I values are: '| I );
End loop;
Dbms_output.put_line ('end ');
End;
/

For I in reverse 1 .. 5 loop indicates an ascending

Use of the GOTO statement
Declare
X number;
Begin
X: = 0;
<Repeat_loop> define a tag
X: = x + 1;
Dbms_output.put_line (X)
If x <3 then
Goto repeat_loop; return to tag
End if;
End;
/

5. Use of Exception Handling
1. What is an exception?
A warning or error in PL/SQL is called an exception, including compile-time errors and runtime errors.
3. Exception Classification
System exception. custom exception
4. Exception Structure
Exception
When... then
...
Common system exceptions:
Dup_val_on_index: insert duplicate rows into tables with unique constraints
No_data_found: no return value in a select into statement
Too_many_rows: The select into statement returns multiple rows.
Values_error: OneAlgorithm, Conversion, truncation, or size constraints Error
Zero_divide: Division by zero
......

Example:
Declare
Test varchar2 (10 );
Begin
Select name into test from deptment where id = 'TT ';
Dbms_output.put_line (test );
End;
/
This will result in an error no data found

Let's set an exception compensation:
Declare
Test varchar2 (10 );
Begin
Select name into test from deptment where id = 'TT ';
Dbms_output.put_line (test );
Exception
When no_data_found then
Dbms_output.put_line ('no data found ');
End;
/

Custom exception:
Declare
Tname varchar2 (10 );
E exception; general exception defined
Begin
Select name into tname from deptment where id = '01;
If tname <> 'department B 'then
Raise E; Exception
End if;
Dbms_output.put_line (tname );
Exception Handling
When E then
Dbms_output.put_line ('error, not department B ');
End;
/

6. Use of records
What is record: record is a composite variable composed of several correlation values. It is often used to support the return values of select statements. A record can be used to process a row of data as a unit, instead of processing each column separately
Record statement
Type type_name is record (
Variable_name datatype [,
Variable_name datatype [,
....
);
Real_name type_name;

Example:
Declare
Type myrecord is record (
Id varchar2 (10 ),
Name varchar2 (10 );
);
Real_record myrecord;
Begin
Select e_id, e_name into real_record from EMP where e_id = '001 ';
Dbms_output.put_line (real_record.id | ',' | real_record.name );
End;
/

Oracle Study Notes-composite Data Types
By Rocky posted on 14:54:00

1. PL/SQL records
Suitable for processing single row and multiple columns of data
Eg:
Type emp_record_type is record (
Name EMP. ename % type,
Salary EMP. Salary % type,
Dno emp. detno % Type
);
Emp_record emp_record_type;

2. PL/SQL set
Suitable for processing data in a single column and multiple rows

2.1 index table
Eg1:
Type ename_table_type is table of EMP. ename % Type
Index by binary_integer; -- (or pls_integer)
Ename_table ename_table_type;
There are two release methods:
.................
Forall I in ename_table.first... ename_table.last
/* SQL statement */
...................

For I in 1 .. ename_table.count Loop
/* SQL statement */
End Loop
..............

Eg2:
Type ename_table_type is table of number
Index by varchar (20 );
Ename_table ename_table_type;
................

Ename_table ('shanghai') = 1;
Ename_table ('beijing') = 2;
..............

Eg3:
Ename_table.delete; -- clears the data in the index table.


2.2 nested tables
The subscript starts from 1. There is no limit on the number of elements.
Eg:
Type ename_table_type is table of EMP. ename % type;
Ename_table ename_table_type: = ename_table_type ('A', 'A'); -- Initialization is required first.
......
Dbms_output.put_line (ename_table (2 ));
......

2.3 Variable Length Array
The subscript starts from 1, and the maximum number of elements is limited.
Eg:
Type ename_table_type is varray (20) of EMP. ename % type;
Ename_table ename_table_type: = ename_table_type ('A', 'A'); -- Initialization is required first.


3. PL/SQL record table
Applicable to multiple rows and multiple columns of data
Eg:
Type ename_table_type is table of EMP % rowtype;
Index by binary_integer;
Ename_table ename_table_type;
------
Select * Bulk collect into ename_table from EMP;
------


4. Multi-Level collection
Similar usage
4.1 multi-level varray
4.2 multi-level nested tables
4.3 multi-level index tables