Oracle's basic syntax, storage functions, and triggers

1.pl/sql
PL/SQL is a procedural extension of the QL language by Oracle, which refers to the addition of process processing statements in the SQL command language, which enables the SQL language to have process processing power. The data manipulation of SQL language can
The combination of force and process language data processing ability makes PLSQL process-oriented and process language simple, efficient, flexible and practical.
BASIC syntax Structure:
[DECLARE]--declaring variables
Begin--code logic
[Exception]--Exception handling
End
Note: End of ";" It can't be canceled .
2.Oracle Common Exceptions
named system exceptioncause
Access_into_null undefined Object
If the corresponding when is not included in the Case_not_found case and no ELSE is set
Collection_is_null collection Element not initialized
Curser_already_open Cursor already open
Dup_val_on_index duplicate values on a column corresponding to a unique index
Invalid_cursor operation on an illegal cursor
Invalid_number Embedded SQL Statements cannot convert characters to numbers
No_data_found using SELECT INTO does not return rows
Too_many_rows when performing a select INTO, the result set exceeds one row
Zero_divide Divisor is 0
Subscript_beyond_count element subscript exceeds the maximum value of a nested table or Varray
Subscript_outside_limit the subscript is specified as a negative number when using a nested table or Varray
When Value_error is assigned, the variable length is not sufficient to accommodate the actual data
Login_denied PL/SQL application provides an incorrect user name or password when connecting to an Oracle database
not_logged_on PL/SQL applications access data without a connection to the Oralce database
Program_error PL/SQL Internal issues, you may need to reload the data Dictionary & Pl./sql System Package
Rowtype_mismatch host cursor variable is incompatible with the return type of the PL/SQL cursor variable
Self_is_null calling an object method on a NULL object when using an object type
Storage_Error when running PL/SQL, out of memory space
SYS_INVALID_ID Invalid ROWID string
Timeout_on_resource Oracle timed out while waiting for resources
basic syntax for 3.Oracle
(1). Condition judgment
Basic Syntax 1
If condition Then
Business logic
End If;
Basic Syntax 2
If condition Then
Business logic
Else
Business logic
End If;
Basic Syntax 3
If condition Then
Business logic
elsif condition Then
Business logic
Else
Business logic
End If;
(2). cycle
(1) Unconditional circulation
Loop
Looping statements
End Loop;
(2) Condition cycle
While condition
Loop
Looping statements
End Loop;
(3) For loop
For variable in start value: Terminating value
Loop
Looping statements
End Loop;
(3) Cursor use
Basic syntax: The cursor cursor name is a SQL statement;
Used in the loop process:
Open cursor Name
Loop
Fetch cursor name into variable
Exit when cursor name%notfound
End Loop;
Close cursor Name
4.Oracle Storage Functions
A storage function, also called a custom function, can receive one or more parameters and return a result, in which we can use PL/SQL for logical processing.
Basic syntax:
Create [OR REPLACE] Function name
(parameter name parameter type, parameter name parameter type, ... ）
Return result variable data type
Is
Variable declaration part;
Begin
Logical part;
return result variable;
[EXCEPTION
Exception handling Section]
End <Note: End of ";" It can't be canceled .>
Trigger for 5.Oracle
A database trigger is a PL/SQL program associated with a table that Oracle automatically executes whenever a particular DML (data manipulation statement) is emitted on a specified table.
A sequence of statements defined in the
Its syntax format is:
Create [or REPLACE] trigger trigger name
Before | After--determine whether to start the trigger first or after
[DELETE] [[or] INSERT] [[Or]update [of column name]]
On table name
[For each ROW] [When (condition)]
Declare
......
Begin
Lsql Block
end;<Note: End of ";" Can't cancel >

Basic syntax, storage functions, and triggers for Oracle