Oracle personnel permissions, string-to-column conversion, statistical management details, oracle details

Oracle personnel permissions, string-to-column conversion, statistical management details, oracle details

Use table Valued Functions

1. Create a type

Create or replace type row_sys_user as object (

Userid int,

Roleid varchar2 (500)

)

2. Create a table

Create or replace type tabemp as table of row_sys_user

3. Create a table Value Function

Create or replace function strsplit1 (p_value1 varchar2,

P_split1 varchar2: = ',')

-- Usage: select * from table (strsplit ('1, 2, 3, 4, 5 '))

Return tabemp

Pipelined is

V_idx integer;

V_userid integer;

V_strs_last varchar2 (4000 );

V_str varchar2 (500 );

V row_sys_user;

Cursor temp is

Select id, roleids from sys_user;

Begin

Open temp;

Loop

Fetch temp into v_userid, v_strs_last;

Exit when temp % notfound;

Loop

V_idx: = instr (v_strs_last ,',');

Exit when v_idx = 0;

V_str: = substr (v_strs_last, 1, v_idx-1 );

V_strs_last: = substr (v_strs_last, v_idx + 1 );

V: = row_sys_user (v_userid, v_str );

Pipe row (v );

End loop;

V: = row_sys_user (v_userid, v_strs_last );

Pipe row (v );

End loop;

Close temp;

Return;

End strsplit1;

4. Obtain the permissions of the old version of the system

Select c. name orgname, d. name deptname, a. realname, B. name

From sys_user a, sys_role B, sys_org c, sys_org d, table (strsplit1 ('','') e

Where a. id = e. userid and B. id = e. roleid

And a. orgid = c. id and a. deptid = d. id

And c. ext4 in ('20140901', '20160901 ')

Order by c. name, d. name, a. realname, B. name