Oracle development skills in projects

I have been involved in the "digital bus" project for more than a month, and I have basically completed and passed the test,
This project is based on the Oracle C/S structure and involves many database operations. Some experience is not used in the past,
This is a special record for its daily reference. If you can see this article, I am more pleased.

1. users is the user table, and userid is the unique sequence number obtained from sequence. As the primary key, the following trigger
It is convenient to insert and allocate a unique sequence each time. Other tables can also refer to this.

Create or replace trigger users_trig
Before insert on users
For each row
Declare
Seq_val number;
Begin
Select s_userid.nextval
Into seq_val from dual;
: New. userid: = seq_val;
End;

2.org is a department table, where orgid is the current department ID and porgid is the parent directory ID, such as a table with parent-child relationship
The connect by statement of Oracle is used for querying from a certain department up or down:

Select * From org connect by prior orgid = porgid start with orgid = 1
// Search for all sub-departments from a department with the ID of 1
Select * From org connect by prior porgid = orgid start with orgid = 1
// Search for all parent departments from departments with the ID of 1

3. User, role, and permission relationship Processing
The role table has the role module permissions, which are expressed by 1 and 0, 1 indicates yes, and 0 indicates no. If the system has 10 modules, the permissions of each role are represented by a 10-bit 01 value. A user is assigned a role and can have multiple roles, relative to the user, the permission is the result of performing and operating on all the fields with permissions of the role.

Strsql = "select substr (power," & modid & ", 1) as rightbit from role where roleid in (select roleid from role_user where userid in (select userid from users where loginname = '"& strloginname &"'))"
// Lists the permissions of the role owned by the current user on the current module.
If one exists in the result set, the user has the permission. If all the values are 0, the user does not have the permission.

4. Divide fields A and B in the temp table and return the result set.

Select (case when B <> 0 then a/B else a end) from temp

5. Create a trigger to delete a cascading table: the role table deletes a role. In the role_user table, delete the corresponding role-user relationship column:

Create or replace trigger trg_role_user_delete
Before delete on role for each row
Begin
Delete from role_user
Where roleid =: Old. roleid;
End;