Java Web Development Knowledge Summary (Oracle-Programming)

1. PL/SQL: Oracle-specific

1.1 PL/SQL Overview

PL/SQL (Procedure Language/SQL) is Oracle's procedural extension of SQL language. It refers to the addition of procedural statements (such as branches, loops, etc.) in the SQL command language, which makes the SQL language procedural. Combining the data manipulation ability of the SQL language with the data processing ability of the process language makes PLSQL process-oriented but simpler, more efficient, more flexible and practical than the process language.

Basic grammatical structure:

- Grammar: Variable declaration and exception handling can not exist, when begin..end block must exist
[declare
    Declare variables;
]
begin
    Logic code;
    [exception
        Exception handling logic;
    ]
end;

1.2 variable

Variable declaration:

- Grammar: Declare variables in declare
 - 1. Declare variables only, not assign values
 Variable name variable type (length); e.g. v_name varchar2(30);
2. Give the initial value when declaring
 Variable name variable type (length): = value; -- e.g. v_name varchar2(30):='somnus';

Assignment of variables:

Variable assignment:
- 1. Give the initial value at the time of declaration
 Variable name variable type (length): = value; -- e.g. v_name varchar2(30):='somnus';
- 2. Declare first and assign values in the begin..end block
 Variable name: = value; -- e.g.: v_name:='somnus';
- 3. First declare that in the begin... end block, the table name condition is assigned by select ing field name into variable name from; and
 select field name into variable name from table name condition;

Case:

--1.Variable declaration and assignment
declare
  v_price number(10,2); -- Water unit price
  v_usenum number;--Watermeter Number
  v_usenum2 number(10,2);--Tonnage of water
  v_money number(10,2); --Water fee amount
begin
  --assignment
  v_price:=2.45;
  v_usenum:=9527;
  v_usenum2:=round(v_usenum/1000);
  v_money:=v_price*v_usenum2;
  --Output value
  dbms_output.put_line('Water consumption/ton:'||v_usenum2);
  dbms_output.put_line('Amount of money/element:'||v_money);
end; --Have a semicolon
--2.Query data from tables to variables select Column names into Variable name from Table name [condition]
declare
  v_price number(10,2); -- Water unit price
  v_usenum number;--Watermeter Number
  v_usenum2 number(10,2);--Tonnage of water
  v_money number(10,2); --Water fee amount
begin
  --assignment
  v_price:=2.45;
  --Query data from water bills to variables
  select usenum into v_usenum from t_account where year='2016' and month='01' and id = 1;
  v_usenum2:=round(v_usenum/1000);
  v_money:=v_price*v_usenum2;
  --Output value
  dbms_output.put_line('Water consumption/ton:'||v_usenum2);
  dbms_output.put_line('Amount of money/element:'||v_money);
end; --Have a semicolon

1.3 Attribute Type

If the type of variable defined depends on a column (reference attribute) in a specific table or on the middle row (record type) in the query table result set, the variable can be defined in such a way that it depends on the specific column type or row type.

Reference type: Variable name table name. Column name% type;

--Case:
declare
  v_price number(10,2); -- Water unit price
  v_usenum t_account.usenum%type;--Watermeter Number
  v_usenum2 number(10,2);--Tonnage of water
  v_money number(10,2); --Water fee amount
begin
  --assignment
  v_price:=2.45;
  --Query data from tables to variables
  select usenum into v_usenum from t_account where year='2016' and month='01' and id = 1;
  v_usenum2:=round(v_usenum/1000);
  v_money:=v_price*v_usenum2;
  --Output value
  dbms_output.put_line('Water consumption/ton:'||v_usenum2);
  dbms_output.put_line('Amount of money/element:'||v_money);
end; --Have a semicolon

Record type: variable name table name% rowtype; encapsulate a row record in the result set into a variable

declare
  v_price number(10,2); -- Water unit price
  v_account t_account%rowtype;--Encapsulate a row record of a query into a variable
  v_usenum2 number(10,2);--Tonnage of water
  v_money number(10,2); --Water fee amount
begin
  --Assignment of variables
  v_price:=2.45;
  --Query a row of data from a table and encapsulate it in a variable
  select * into v_account from t_account where year='2016' and month='01' and id = 1;
  --By recording type variables.Column Name Gets the value of a column in a row record
  v_usenum2:=round(v_account.usenum/1000); -- Through variables.Column name calls the value of a column
  v_money:=v_price*v_usenum2;
  --Output value
  dbms_output.put_line('Water consumption/ton:'||v_usenum2);
  dbms_output.put_line('Amount of money/element:'||v_money);
end; --Have a semicolon

1.4 anomaly

Errors that occur while running a program are called exceptions/exceptions. When an exception occurs, the statement will stop executing and control will be transferred to the exception handling part of the PL/SQL block.
There are two types of anomalies:
1. Predefined exceptions: When PL/SQL programs violate Oracle rules or exceed system restrictions, they are implicitly triggered. There are 21 kinds of exceptions.
2. User-defined exceptions: Users can define exceptions in the declaration part of PL/SQL blocks, and custom exceptions are explicitly triggered by RAISE statements.

The grammar of exception handling:

Grammar: Defined in begin..end block
begin
    Processing logic;
    exception
        when exception type then
            Exception handling logic
end;

Case:

--System predefined exceptions are commonly used:
--When there may be multiple exceptions, there need to be multiple when..then Sentence
--abnormal/Exceptions predefined 21 exceptions and other user-defined exceptions
declare
  v_num number;
begin
  v_num:=1/0; --Divided by 0 anomaly
  exception
    when ZERO_DIVIDE --Exceptional keywords whose divisor cannot be 0
      then
        dbms_output.put_line('The divisor cannot be zero');
    when NO_DATA_FOUND --Use select into No rows returned
      then
        dbms_output.put_line('No data returned');
end;

1.5 Conditional Judgment: if Grammar

The grammar of the judgment statement if:

--stay begin..end Use judgment statements in blocks: Note elsif No. else if
declare --When there is no definition of variables, declare Can be omitted
begin
  --ifThe format of ____________1: ifSentence
  if 1<2 then
    dbms_output.put_line('1 Less than 2');
  end if;
  --if Format 2: if..else Sentence
  if 1>2 then
    dbms_output.put_line('1 More than 2');
  else
     dbms_output.put_line('1 Less than 2');
  end if;
  --if Format 3: if..elsif..elsif..else Sentence
  if 1>2 then
    dbms_output.put_line('1 More than 2');
  elsif 1=2 then
     dbms_output.put_line('1 Equivalent to 2');
  else
    dbms_output.put_line('1 Less than 2');
  end if;
end;

1.6 cycle:

Unconditional cycle:

--Syntax: Loop statements are defined in begin..end In block
loop
    //Cyclic body;
end loop;
--Case:
declare
  v_num number:=1;
begin
  loop
    dbms_output.put_line(v_num);
    v_num:=v_num+1;
    --Exit cycle mode 1
    if v_num >= 100 then
      exit;
    end if;
    --Exit cycle mode 2
    exit when v_num >= 100;
  end loop;
end;

Conditional cycle:

--Syntax: Loop statements are defined in begin..end In block
while condition;--Conditions are the conditions under which a loop can be executed
loop
    //Cyclic body;
end loop;
--Case:
declare
  v_num number:=1;
begin
  while v_num < 100 --Cyclic Conditions
  loop
    dbms_output.put_line(v_num);--Value of output variable
    v_num:=v_num+1; -- Variable plus 1
  end loop;
end;

for loop:

--Syntax: Loop statements are defined in begin..end In block
for variable in Initial value..Termination value
loop
    //Cyclic body;
end loop;
--Case:
--Use for loop
declare
begin
  --Here's the x Variables are equivalent to local variables, which need not be defined, and x Only in loop Circulating in vivo use
  for x in 1..100
  loop
    dbms_output.put_line(x);
  end loop;
end;
--for Loops, where variables can be used to control the data range of the loop
declare
 v_num number:=50;
begin
  --Here's the x Variables are equivalent to local variables, which need not be defined, and x Only in loop Circulating in vivo use
  for x in 1..v_num
  loop
    dbms_output.put_line(x);
  end loop;
end;

1.7 cursor

The cursor is a data buffer set up by the system for users to store the execution results of the SQL statements. We can understand cursors as result sets in PL/SQL. The query data is stored in the memory buffer, which reduces the performance of the server. When the cursor opens, it points to the top of the first line in the result set by default. When fetch data, the cursor points to the first data.

Define parametric cursor syntax:

Grammar:
Cursor cursor name is SQL statement;

Parametric cursor usage syntax:

--Grammar:
open Cursor name;
loop
  fetch Cursor name into variable;
  exit when Cursor name%notfound;
end loop;
close Cursor name;
--Case:
--Parametric cursor
declare
cursor cur_pricetable is 
select * from t_pricetable where ownertypeid=1;
--Get the value stored in the cursor
v_price t_pricetable%rowtype;
begin
  open cur_pricetable;
  loop
    fetch cur_pricetable into v_price; --Get the value in the cursor into the row variable
    exit when cur_pricetable%notfound; --Exit the loop after the cursor's value has been taken
    dbms_output.put_line(v_price.price||'--'||v_price.minnum||'--'||v_price.maxnum);
    --The exit statement is placed after the output statement and the last value is output twice.
    --The reason is: only fetch Statement will grab the next value, when it is the last value, the output statement for output.
    --The judgement condition is that the current result is the last value, the next cycle. fetch Grab empty values, but
    --The output statement will continue to output the last value until the exit statement terminates the loop.
    --exit when cur_pricetable%notfound; --Exit the loop after the cursor's value has been taken
  end loop;
  close cur_pricetable;
end;

Definition and use of parametric cursors:

--Define a cursor with parameters
declare
cursor cur_pricetable2(v_ownertypeid number) is  --Define cursor parameters
select * from t_pricetable where ownertypeid=v_ownertypeid;
--Get the value stored in the cursor
v_price t_pricetable%rowtype;
begin
  open cur_pricetable2(3); --Calling a parameterized cursor requires passing in parameters
  loop
    fetch cur_pricetable2 into v_price; --Get the value in the cursor into the row variable
    exit when cur_pricetable2%notfound; --Exit the loop after the cursor's value has been taken
    dbms_output.put_line(v_price.price||'--'||v_price.minnum||'--'||v_price.maxnum);
  end loop;
  close cur_pricetable2;
end;

Use cursors by for loop:

--for Loop extraction of cursor values:Implicitly open and close cursors
declare
cursor cur_pricetable2(v_ownertypeid number) is  --Define cursor parameters
select * from t_pricetable where ownertypeid=v_ownertypeid;
--Get the value stored in the cursor
v_price t_pricetable%rowtype;
begin
  for v_price in cur_pricetable2(1)
  loop
    dbms_output.put_line(v_price.price||'--'||v_price.minnum||'--'||v_price.maxnum);
  end loop;
end;

2. Storage Functions: Oracle and mysql Universal

Storage functions are also called custom functions. You can accept one or more parameters and return a result. In functions, we can use P/SQL for logical processing.

Create stored procedure syntax:

Grammar:
CREATE [OR REPLACE] FUNCTION 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;
- Notes:
The length of the parameter cannot be added when the parameter of the function is declared.
The length of the type cannot be added to the return value of the function.

Case:

--Storage functions can be embedded SQL In a statement, a value or object is returned
create or replace function fn_getAddress
(v_id number)
return varchar2
is
    --Variable declaration
    v_name varchar2(20);
begin
  select name into v_name from t_address where id = v_id;
  return v_name;
end;
--Calling storage functions
select fn_getAddress(1) from dual; --Simple calls to storage functions
--Call storage functions as select Statement field
select id,name,fn_getaddress(addressid) from t_owners where id=1;

3. Stored Procedures (Focus): Oracle and mysql Universal

Stored procedures are named PL/SQL blocks stored in databases and are one kind of database objects. Applications can call stored procedures and execute corresponding logic. The disadvantage of stored procedure: the high coupling between tables in database is not conducive to the change of functional requirements.

Stored Procedures: The ability to encapsulate business logic under development is contrary to the hibernate framework.

Stored procedures and stored functions can encapsulate certain business logic and return results. The differences are as follows:

1. There is a return value in the stored function and it must be returned; the stored procedure has no return value, but it can return multiple values through the outgoing parameters.
2. Storage functions can be used directly in select statements; stored procedures can not be used directly in select statements; stored procedures are mostly called by applications (java).
3. Storage functions generally encapsulate a query result; stored procedures generally encapsulate a transaction code to control transaction operations.

Stored procedure grammar structure:

Grammar:
CREATE [OR REPLACE] PROCEDURE stored procedure name
 (parameter name [in|out|in out] type, parameter name [in|out|in out] type, parameter name [in|out|in out] type...)
IS|AS -- Both, but there must be one
    Variable declaration part;
BEGIN
    Logical part;
    [EXCEPTION
    Exception handling section]
END;
- Notes:
The length of the type cannot be specified when the parameter is declared.
The in|out specification of the parameter: in represents that the parameter is an incoming parameter and out represents that the parameter is an outgoing parameter.
    Declare the parameter in: Declare that the parameter is an incoming parameter
    Declare the parameter as out: Declare that the parameter is an outgoing parameter
    Declare the parameter in out: Declare that the parameter is an incoming and outgoing parameter
 Stored procedure calls without outgoing parameters:
    Call mode call;
    Start... End mode call;
    Called in JDBC;
Stored procedure calls with outgoing parameters: call calls cannot be used and outgoing parameters cannot be retrieved
    Start... End mode call;
    Called in JDBC;

Case 1: Parametric Stored Procedure Creation

--Storage Procedures Without Outgoing Parameters
create sequence seq_owners start with 18;--Create an automatic growth sequence,For setting tables id Column primary key
--Create stored procedures
create or replace procedure pro_owners_add
(
v_name varchar2, --Default omission of incoming parametersinKeyword
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
v_ownertypeid number
)
is
--Declare variables
begin
  insert into t_owners values (seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter
                              ,sysdate,v_ownertypeid);
end;

Case 1: Parametric stored procedure calls - call and begin..end mode calls

--Call stored procedures without outgoing parameters
--Call mode 1:
call pro_owners_add('sunmos',1,'2-1','0729-0809',1);commit;
--Call mode 2:
begin
  pro_owners_add('sunmos',1,'2-1','0729-0809',1);
  commit;
end;

Case 1: Parametric Stored Procedure Call - jdbc Mode Call

/**
 * Note: Call method: {call pro_owners_add(?,???????)}, must add {}, pass in parameters.
 * A stored procedure that calls a database without outgoing parameters
 */
public void pro_add(Owners owners) {
    // Declare database connection objects
    Connection conn = null;
    // Declare objects that execute stored procedures
    CallableStatement stmt = null; // CallableStatement objects used by stored procedures

    try {
        // Get the connection
        conn = BaseDao.getConnection();
        // Precompiled stored procedures
        stmt = conn.prepareCall("{call pro_owners_add(?,?,?,?,?)}");
        // Setting parameters
        stmt.setString(1, owners.getName());
        stmt.setLong(2, owners.getAddressid());
        stmt.setString(3, owners.getHousenumber());
        stmt.setString(4, owners.getWatermeter());
        stmt.setLong(5, owners.getOwnertypeid());
        // Execute sql statements
        stmt.execute();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally{
        // Releasing resources
        BaseDao.closeAll(conn, stmt, null);
    }
}

Case 2: Creation of stored procedures with outgoing parameters

--Create stored procedures with outgoing parameters
create or replace procedure pro_owners_add2
(
v_name varchar2,
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
v_ownertypeid number,
v_id out number --Outgoing parameters need to be defined out Keyword
)
is -- isandasAll are acceptable.
begin
  --The owner's will be added id Values are stored in outgoing variables
  select seq_owners.nextval into v_id from dual;
  insert into t_owners values (v_id,v_name,v_addressid,v_housenumber,v_watermeter
                              ,sysdate,v_ownertypeid);
  commit; --dml Operations need to be used commit Submission transaction
end;

Case 2: Invocation of stored procedures with outgoing parameters - begin..end mode call

--Call stored procedures with outgoing parameters
--Only through begin...end Statement is invoked and cannot be passed call call
declare
  v_id number; --Declare variables that receive outgoing parameters
begin
  --Take the outgoing parameter as the corresponding parameter of the stored procedure
  pro_owners_add2('sunmos',1,'2-1','0729-0809',1,v_id);
  dbms_output.put_line(v_id);--Output parameter values
end;

Case 2: Calls with Outgoing Parameter Stored Procedures - JDBC mode calls

/**
 * Matters needing attention:
 *  Call method: {call pro_owners_add(?,?,??,?)}, must add {}, pass in parameters;
 *  To obtain the outgoing parameters, it is necessary to execute the SQL statement before the outgoing parameters can be obtained.
 * A stored procedure with outgoing parameters that calls a database
 */
public Long pro_add2(Owners owners) {
    // Define the value of the outgoing data
    Long v_id = 0L;
    // Declare database connection objects
    Connection conn = null;
    // Declare objects that execute stored procedures
    CallableStatement stmt = null; // CallableStatement objects used by stored procedures

    try {
        // Get the connection
        conn = BaseDao.getConnection();
        // Precompiled stored procedures
        stmt = conn.prepareCall("{call pro_owners_add2(?,?,?,?,?,?)}");
        // Setting parameters
        stmt.setString(1, owners.getName());
        stmt.setLong(2, owners.getAddressid());
        stmt.setString(3, owners.getHousenumber());
        stmt.setString(4, owners.getWatermeter());
        stmt.setLong(5, owners.getOwnertypeid());
        // Set the type of the outgoing parameter, the last parameter is the outgoing parameter, the number type when the type is
        stmt.registerOutParameter(6, OracleTypes.NUMBER);//OracleTypes are classes in Oracle driver packages
        // Execute SQL statements
        stmt.execute();
        // Obtain the outgoing parameter value after the stored procedure has been executed
        v_id = stmt.getLong(6);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally{
        BaseDao.closeAll(conn, stmt, null);
    }
    return v_id;
}

4. Triggers: Oracle and mysql Universal

A database trigger is a stored PL/SQL program associated with a table. Whenever a specific data operation statement (Insert,update,delete) is issued on a specified table, Oracle automatically executes the sequence of statements defined in the trigger. Triggers are logic codes that listen to DML operations on the action table and execute predefined ones. Triggers are divided into pre-trigger and post-trigger. When a table is deleted, triggers that act on the table are automatically deleted.

Functions of triggers: data validation; implementation of complex security checks; auditing, tracking data operations on tables; data backup and synchronization.

Definition of trigger:

Grammar:
CREATE [or REPLACE] TRIGGER trigger name
    BEFORE | AFTER
    [DELETE] [[or] INSERT] [[or] UPDATE [OF column]]
    ON table name
    [FOR EACH ROW] [WHEN (Conditions)]
declare
    Variable declaration;
begin
    Logic code block;
End;
- Notes:
before: Represents a pre-trigger; execution statements are suspended first; if an exception is thrown during the execution of the trigger, the suspended sql statement cannot be executed;
After: Represents a post-trigger; after executing a statement, the trigger is executed.
[DELETE] [[or] INSERT] [[or] UPDATE [OF Column Name]: Indicates that the trigger is triggered when a DML operation occurs in the table. If multiple DML operations need to be monitored, the OR keyword connection is used. If the update operation is monitored, the specific columns of the table can also be specified to be monitored, and when multiple columns are monitored, the column names are separated by commas.
[FOR EACH ROW]: Indicates that the class of the trigger is a level trigger, and the default is a statement level trigger.
[WHEN (Conditions)]: Indicates the condition under which the trigger executes. By default, true is executed.
The dml statement in the trigger commit s automatically instead of manually.

The difference between row-level trigger and statement trigger:

Line-level flip-flop: for each row, one statement execution, execution of logic in one flip-flop;
Statement-level flip-flop: The logic of a trigger is executed only once without multiple executions of pseudo-recorded variables.

Pseudo-record variables in triggers: Each trigger has default old and new variables, old represents the row record before the change, and new represents the row record after the change.

Case:

--Define pre-triggers
--Automatic calculation of monthly water consumption
create trigger tri_account_usenum
before --Create a pre-trigger
update of num1 --The value of a trigger acting on a column changes
on t_account
for each row
declare
--Declare variables
begin
  :new.usenum:=:new.num1-:new.num0;
end;

--Define post-trigger
--When the owner's name is changed, the information before the modification is saved in the information record table.
--Create a log table to store user information modifications
create table t_owners_log(
v_updatetime date,
v_oldname varchar2(30),
v_newname varchar2(30),
v_ownersid number);
--Create a post-trigger
create trigger tri_owners_update
after
update of Name --On the Owner's List name Create triggers on columns
on t_owners
for each row
declare
--Declare variables
begin
  --In flip-flops dml Statements commit transactions automatically, not manually
  insert into t_owners_log values (sysdate,:old.name,:new.name,:old.id);
end;