Up to now, I have written oralce stored procedures, functions, triggers, and more, and more than SQL server stored procedures. next I will post my first Oracle stored procedure to witness the process of growth.
-- Function: copy a user
-- Created by: applegreen
-- Updated on:
Create or replace procedure p_copy_user_rights
(Olduserid in number,
Newusername in char,
Newpassword in char,
Newuserdesc in char
)
Is
P_user_id number (38 );
P_user_name char (10 );
P_user_password char (100 );
P_user_desc varchar2 (40 );
Cursor C1 is select group_id from t_usr_group_ass where user_id = olduserid;
C1_group_id t_usr_group_ass.group_id % type;
Cursor C2 is select role_id, data_right from t_usr_role_ass where usr_id = olduserid;
C2_role_id t_usr_role_ass.role_id % type;
C2_data_right t_usr_role_ass.data_right % type;
Begin
-- *************** Step 1: update the user information table ****************
-- Variable p_user_id
Select sheet_no + 1 into p_user_id from t_bi_sheet_no where sheet_name = 'user _ id ';
-- Variable p_user_name = newusername;
P_user_name: = newusername;
-- Variable p_user_password
P_user_password: = newpassword;
-- Variable p_user_desc
-- Select user_desc into p_user_desc from t_usr_usr where user_id = olduserid;
P_user_desc: = newuserdesc;
-- Insert t_usr_usr
Insert into t_usr_usr (user_id, user_name, user_password, user_create_date, user_upd_date, user_desc)
Values (p_user_id, p_user_name, p_user_password, sysdate, sysdate, p_user_desc );
-- Update t_bi_sheet_no
Update t_bi_sheet_no set sheet_no = p_user_id where sheet_name = 'user _ id ';
-- *************** Step 2: update the user group table ****************
-- A temporary table temp is recommended:
-- Query SQL statement: Select * From t_usr_group_ass where user_id = @ olduserid to temp,
-- For each record in temp, it is executed cyclically.
--{
-- @ Group_id = temp. group_id;
-- @ User_id = (select (t_bi_sheet_no. user_id) from t_bi_sheet_no) + 1;
-- Insert new information into the database
--} To the end of the loop
Open C1;
Loop
Fetch C1 into c1_group_id;
Exit when C1 % notfound;
If c1_group_id is not null then
Begin
Insert into t_usr_group_ass (user_id, group_id, upd_date)
Values (p_user_id, c1_group_id, sysdate );
End;
End if;
End loop;
Close C1;
-- *************** Step 3: update the user permission table ****************
-- A temporary table temp is recommended.
-- Select usr_id, role_id, data_right, upd_date from the SQL statement
-- From t_usr_role_ass
-- Where t_usr_role_ass.usr_id = @ olduserid select to temp.
-- For each record in temp, it is executed cyclically.
-- @ Role_id = temp. role_id
-- @ User_id = (select (t_bi_sheet_no. user_id) from t_bi_sheet_no) + 1;
--} Until the loop ends
Open C2;
Loop
Fetch C2 into c2_role_id, c2_data_right;
Exit when C2 % notfound;
If c2_role_id is not null then
Begin
Insert into t_usr_role_ass (usr_id, role_id, data_right, upd_date)
Values (p_user_id, c2_role_id, c2_data_right, sysdate );
End;
End if;
End loop;
Close C2;
End;
/
