I am trying to execute the below query.
I am trying to do this comparison using IF statement. I am expecting my result to like the values which are not in v_anwen_id should be selected from v_anwen2_id. so when v_anwen2_id is not equal to v_anwen_id then to execute the insert statement. But i am not getting the " v_anwen2_id is not equal to v_anwen_id " value. ( i wanted it to be like when a is not equal to b then execute insert statement. but still it is inserting the values which are in b as well). I am not sure if it is the problem with my if statement or with the cursor.
IF v_anwen2_id <> v_anwen_id
Below is my query.
SET serveroutput ON;
DECLARE
v_client_id NUMBER(38);
v_roles_id NUMBER(38);
v_old_roles NUMBER(38);
v_rights_id NUMBER(38);
v_user1_id NUMBER(38);
v_anwen_id NUMBER(38);
v_anwen2_id NUMBER(38);
v_client1_id NUMBER(38);
v_abtei NUMBER(38);
CURSOR c_abtei (var01 NUMBER) IS
SELECT abteil,client_id FROM ty_join_anwen_abteil WHERE anwen = var01;
CURSOR c_anwen (var02 NUMBER) IS
SELECT anwen FROM ty_join_anwen_abteil WHERE abteil = var02;
CURSOR c_roles (var03 NUMBER) IS
SELECT roles FROM ty_join_rolesn_righte WHERE right = var03;
CURSOR c_users (var04 NUMBER) IS
SELECT anwen FROM ty_join_anwen_rolesn WHERE roles = var04;
BEGIN
-- SELECT id INTO v_roles_id FROM ty_sd_roles WHERE description ='cod_Test';
SELECT id INTO v_rights_id FROM ty_sd_berightigung WHERE description ='failure_note';
-- INSERT INTO ty_join_rolesn_righte (role,right)
-- VALUES (v_roles_id,v_rights_id);
SELECT ID INTO v_user1_id FROM ty_sd_person WHERE email = 'jens@gmail.com';
OPEN c_abteil(v_user1_id);
LOOP
FETCH c_abteil INTO v_abteil,v_client1_id;
EXIT WHEN c_abteil%NOTFOUND;
OPEN c_anwen(v_abteil);
LOOP
FETCH c_anwen INTO v_anwen_id;
EXIT WHEN c_anwen%NOTFOUND;
-- INSERT INTO ty_join_anwen_rolesn (anwen,roles,client_id) VALUES (v_anwen_id,v_roles_id,v_client1_id);
END LOOP;
CLOSE c_anwen;
END LOOP;
CLOSE c_abteil;
OPEN c_roles(v_rights_id);
LOOP
FETCH c_roles INTO v_old_roles;
EXIT WHEN c_roles%NOTFOUND;
OPEN c_users(v_old_roles);
LOOP
FETCH c_users INTO v_anwen2_id;
EXIT WHEN c_users%NOTFOUND;
IF v_anwen2_id <> v_anwen_id
THEN
dbms_output.put_line(v_anwen2_id);
-- INSERT INTO ty_join_anwen_rolesn (anwen,roles,client_id) VALUES (v_anwen2_id,v_roles_id,v_client1_id);
END IF;
-- delete from ty_join_roles_right where roles = v_old_roles;
END LOOP;
CLOSE c_users;
END LOOP;
CLOSE c_roles;
END;
/
Aucun commentaire:
Enregistrer un commentaire