Simple use of Oracle

Add, delete, modify, and delete trigger information

Create or replace trigger hz_test_trigger
After insert or update or delete on hz_test
For each row
Declare
V_type hz_test_log.l_type % type;
Begin
If inserting then
V_type: = 'insert ';
Insert into hz_test_log values (user, v_type, to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss'),: New. t_id,: New. t_name,: New. t_age,: New. t_sex, sqe_hz_test_log.nextval );
Elsif updating then
V_type: = 'updateold ';
Insert into hz_test_log values (user, v_type, to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss'),: Old. t_id,: Old. t_name,: Old. t_age,: Old. t_sex, sqe_hz_test_log.nextval );
V_type: = 'updatenew ';
Insert into hz_test_log values (user, v_type, to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss'),: New. t_id,: New. t_name,: New. t_age,: New. t_sex, sqe_hz_test_log.nextval );
Elsif deleting then
V_type: = 'delete ';
Insert into hz_test_log values (user, v_type, to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss'),: Old. t_id,: Old. t_name,: Old. t_age,: Old. t_sex, sqe_hz_test_log.nextval );
End if;
End;

 

Merge query retains all records in Table 1 and Table 2. Only one record is retained.
Select * From DBO. meeting1
Union
Select * From DBO. meeting2
Merge query to keep all records in Table 1 and Table 2
Select * From DBO. meeting1
Union all
Select * From DBO. meeting2
Only the same item records of Table 1 and Table 2 are retained in the submit and query operations.
Select * From DBO. meeting1
Intersect
Select * From DBO. meeting2
The subtraction query only keeps records of one or more tables out of table 2.
Select * From DBO. meeting1
Except
Select * From DBO. meeting2

Data type conversion: to_date ('', 'yyyy-MM-DD hh24: MI: ss') to_number ('') to_char ('')

Sort in ascending or descending order
Select * from Table order by column ASC/DESC

Join Data Tables
Table1 inner join Table2 on table1.id = table2.id

Left join or left Outer Join.
The result set of the left Outer Join includes all rows in the left table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all selection list columns in the right table in the row of the associated result set are null.

Right join or right outer join.
The right outer join is the reverse join of the left Outer Join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left table.

Full join or full outer join.
The Complete External Join Operation returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table.

Rows are returned only when at least one row in the same two tables meets the join conditions. The inner join removes rows that do not match any row in the other table. The outer join will return all rows of at least one table or view mentioned in the from clause, as long as these rows meet any where or having search conditions. Searches all rows in the left table referenced by the left Outer Join and all rows in the right table referenced by the right outer join. All rows of the two tables in the complete external join will be returned.