Oracle PL/SQL

(1). PL/SQL is not case sensitive.
(2). Character Type:
CHAR: up to 32767 characters.
(3). Fast format:
Begin
Declare
Begin
Exception
End
End
Note: If you use the first bengin, you must use the second one, and vice versa.
(4) annotation method:
Single Row -- this is a one-line comment
Multiple rows/* this is
Is
One-line comment
*/
(5) PL/SQL can directly support DML queries, but does not support DDL commands. You usually use PL/SQL to maintain data in the database structure. However, these structures cannot be maintained.
(6) declare
Customer char (30 );
Fiscal_year number (2) not null: = '97 ';
The definition of each variable must end with a semicolon.
(7) Declare pointer
Declare
Cursor employee_cursor is
Select * from employees;
Pointers are similar to views by using loop in the procdure section ). You can read the pointer.
(8) % Type attribute
% Type can be replaced with the given variable attribute in the table.
Declare
Cursor employee_cursor is
Select emp_id, emp_name from employees;
Id_num employees. emp_id % type;
Name employees. emp_name % type;
(9) % rowtype attribute
A variable is not limited to a single value. If your defined variable is associated with a pointer, you can use
The % rowtype attribute declares a variable and ensures that it is of the same type as the row where the cursor is located. In the Oracle dictionary
% Rowtype

Declare
Cursor employee_cursor is
Select emp_id, emp_name from employees;
Employee_record employee_cursor % rowtype;

Analysis:
In the above example, a variable named employee_record is defined, and % rowtype defines this variable to make it
The variable defined by the % rowtype attribute is also called a set, which is the same as the data type of the row where the employee_cursor is located.
Variable.
% Rowcount attribute
In PL/SQL, the % rowcount attribute ensures the number of rows of the cursor in a specific SQL statement block.
Declare
Cursor employee_cursor is
Select emp_id, emp_name from employees;
Records_processed: = maid % rowcount;

In the preceding example, the variable records_processed returns the row of the employee_cursor accessed by the PL/SQL statement.
Quantity

Warning when defining a variable, be careful to check that it conflicts with the table name. For example, if the variable you define is
The names of the tables accessed in the PL/SQL statement block are the same, so the variable name takes precedence over the table name.
Procdure
The procdure part is the main part of a block, which includes conditional statements and SQL
Statement
Begin
Open a cursor;
Condition1;
Statement1;
Condition2;
Statement2;
Condition3;
Statement3;
...
Close the cursor;
End

Pointer control command
Now you will learn how to define a pointer in the PL/SQL statement block. You need to know how to access
The defined pointer part explains the basic pointer control commands declare open fetch and
Close

Condition Statement
If condition1 then
Statement1;
End if;

If condition1 then
Statement1;
Else
Statement2;
End if;

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

Loops Loop
Loop
While-Loop
For-Loop

Begin
Open employee_cursor;
Loop
Fetch employee_cursor into employee_record;
Exit when employee_cursor % notfound;
Statement1;
....
End loop;
Close employee_cursor;
End;

Declare
Cursor payment_cursor is
Select cust_id, payment, total_due from payment_table;
Cust_id payment_table.cust_id % type;
Payment payment_table.payment % type;
Total_due payment_table.total_due % type;
Begin
Open payment_cursor;
While payment <total_due Loop
Fetch payment_cursor into cust_id, payment, total_due;
Exit when payment_cursor % notfound;
Insert into underpay_table
Values (cust_id, 'Kill owes ');
End loop;
Close payment_cursor;

Declare
Cursor payment_cursor is
Select cust_id, payment, total_due from payment_table;
Cust_id payment_table.cust_id % type;
Payment payment_table.payment % Type
Total_due payment_table.total_due % type;
Begin
Open payment_cursor;
For pay_rec in payment_cursor Loop
If pay_rec.payment <pay_rec.total_due then
Insert into underpay_table
Values (pay_rec.cust_id, 'Kill owes ');
End if;
End loop;
Close payment_cursor;
End;

 

Exception Section

Activate exception (exception): riase

Begin
Declare
Exception_name exception;
Begin
If condition then
Raise prediction_name;
End if;
Exception
When exception_name then
Statement;
End;
End;
Exception Handling
Exception
When exception1 then
Statement1;
When exception2 then
Statement2;
When others then
Statement3;

SQL * Plus