It has been more than a month to participate in the "Digital Bus" project, and the module I have to complete and pass the test.
This project is based on Oracle's C / S structure, involving many database operations, some experience is not used before,
In order to leave a day reference, the special record is recorded here. If the reader who sees this article can be used, then I am even more gratifying.
1.Users is the user table, the userid is the only order number obtained from Sequence, as the primary key, the following trigger
It is convenient for each insertion of the unique sequence, and other tables can also be referenced 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, PORGID is the superior directory ID, a table like this parent-child relationship
During the query from a department, the query of the upward or down is used to the CONNECT BY statement:
Select * from org connection by prior orgid = PORGID Start with OrgID = 1
// Looking for all sub-sectors from the department of the department ID 1
Select * from org connection by prior porgid = orgid start with orgid = 1
// Looking for all parent sectors from the department of department ID 1
3. User, role, permission relationship processing
There is a function of the character in the role table, indicated by 1 and 0, 1 indicates that there is no, if there is 10 modules, each character is indicated by 10 bits, and the user is given a role, one Users can have multiple characters, relative to the user's permissions is the result of all the permissions of the role to do and operate.
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 & "'))"
// list the privilege of the current user to the current module
The result is set if there is any one of the users, and if it is 0, there is no permission.
4. Doing Fields A and B in Temp Table, return result set
SELECT (Case WHEN B <> 0 THEN A / B ELSE A end) from Temp
5. Establish the deletion of the trigger processing stage: the role role table deletes a role, and the corresponding role user relationship is deleted in role_user:
Create Or Replace Trigger TRG_ROLE_USER_DELETE
Before delete on role for Each Row
Begin
Delete from role_user
WHERE ROLLEID =: Old.RoleId;
END;
