Oracle Training Advanced Programming

Advanced Database Programming

1. PL/SQL Introduction:
PL/SQL stands for procedural language/sql (procedural Language: Process language)
It's an extension to SQL

Tight security
Separates the application logic between the client and server, and the client executes only stored procedures

2. PL/SQL engine
Processing PL/SQL blocks and separating them into statements and procedures:
A. Sending a procedure statement to a procedure statement executor for processing
B. Sending an SQL statement to the SQL statement executor for processing

Structure of the 3.pl/sql block

The various components of A.pl/sql:
Declarations section
Executable section
Exception Handling Section

Structure of the B.pl/sql block:
DECLARE
Declarations
BEGIN
Executable statements
EXCEPTION
Handlers
END;

Where declarations is declared, executable statements is an executable statement, handlers is the handler

4. Output: Dbms_output. Put_Line ();

5. How to define variables:
Format: Declare variable name 1 data type 1:= initial value 1;
DECLARE Mynum varchar2 (): = ' abc ';

6. There are two types of variable assignment, see the example below
A,: = To assign a value
b, select into variable name to assign value
The first type of assignment:
Declare
N number;str varchar2 (20);
Begin
n:=100*3;
str:= ' &ABC ';--Popup dialog to get input assignment to variable str
Dbms_output.put_line (n| | ', ' | | STR);--where | | Is the meaning of the connection string and prints to the interface
End

The second type of assignment:
Select Tname into Tnames from temp where tid=2;
--Print to interface
Dbms_output.put_line (' type name: ' | | tnames);

7. Conditional Control Statement:
If-then
If-then-else
If-then-elsif

IF < conditions > Then
Statement
END IF;

IF < conditions > Then
Statement
ELSE
Statement
END IF;

IF < conditions 1> Then
Statement
elsif < conditions 2> Then
Statement
elsif < conditions 3> Then
Statement
ELSE
Statement
END IF;

8. Loop Control statement:
Use loop, unconditional loop, add from 1 to 100, use exit mate if exit
Declare
I number:=1; --Define loop number variable I
Total number:=0; --Define cumulative result variable total
Begin
Loop
Total:=total+i;
i:=i+1;
If I>100 Then
Exit
End If;
End Loop;
Dbms_output.put_line (' final result: ' | | Total);
End

9. Abnormal
An exception is thrown when an error occurs in a PL/SQL program
When an error occurs, normal execution stops, and control shifts to the exception handling section
Declare
I number:=0; n number:=0;
Begin
i:=100/n;--here will give an error, the divisor is 0
Dbms_output.put_line (' Result: ' | | i);
Exception
-Write others when you don't know what's wrong
When others then
--SQLERRM This will print error messages
Dbms_output.put_line (' exception: ' | | SQLERRM);
End

10, the definition and use of stored procedures
In real-world development, complex business is often encapsulated in the process.
Syntax for creating a procedure:

CREATE [OR REPLACE] PROCEDURE
<procedure name> [(<parameter list>)]
Is|as
<local variable declaration> declaring variable declare keyword
BEGIN
<executable statements>
[EXCEPTION
<exception Handlers>]
END;

Note: By processing we generally want one or more results, how can we take one or more results from the stored procedure?
Three modes of process parameters:
Inch
Value used to accept the calling program
Default parameter mode
Out
Used to return a value to the calling program
In Out
Used to accept the value of the calling program and return the updated value to the calling program
Execute stored procedures can be invoked using the EXEC command or in other procedures.

11. Definition and use of storage functions
We already have a lot of oracle built-in functions, so can we define our own functions?
A function is a named PL/SQL subroutine that can return a value.
syntax for creating a function:
CREATE [OR REPLACE] FUNCTION
function name [(PARAM1,PARAM2)]
Return datatype Is|as--return value type
[Local declarations]
BEGIN
Executable statements;
RETURN result; --Remember to return the results
EXCEPTION
Exception handlers;
END;
Method definitions in the analogy Java language
Delete functions drop function fun_name;

12. Definition and use of triggers
A trigger is a stored procedure that executes automatically when a specific event occurs
Specific events can be DML statements and DDL statements that perform updates
Triggers cannot be explicitly called
Features of the trigger:
Automatically generate data
Customizing complex security permissions
Provide audit and log records
Enable complex business logic

CREATE [OR REPLACE] TRIGGER trigger_name
After | Before | INSTEAD of
[INSERT] [[OR] UPDATE [of column_list]]
[[OR] DELETE]
On Table_or_view_name
[Referencing {old [as] old/new [as] NEW}]
[For each ROW]
[When (condition)]
Pl/sql_block;

Attention:
: New and: Old is the row record for the row processing in the Oracle trigger and for the previous value.

is a fixed usage.

OK
UPDATE E1 SET DEPTNO = ' ABC ' | | : NEW. DEPTNO WHERE DEPTNO =:old. DEPTNO;

You can also define a variable v_aaa in the trigger, and then assign the value:

V_AAA: =: NEW. DEPTNO;

Oracle Training Advanced Programming