How to make a stored procedure so that I can throw all the data of users who have roles and those who do not have assigned roles

1

please can someone help me with the following problem, the procedure I have only throws me the names of users who only have roles, but I want to throw all users who have roles and those who do not have assigned roles.

these are the 3 tables

usuariosroles |usuarios| roles

this is my procedure

CREATE OR REPLACE PROCEDURE Consultar ( l_nombre usuarios.nombre%type)                        
AS
     CURSOR l_cursor IS 
  SELECT usuarios.IDUSUARIOS,
      usuariosroles.idusuarios,
      roles.idrol,
      usuariosroles.idrol,
      roles.nombre,
      usuarios.nombre
        FROM usuarios
             left JOIN usuariosroles ON usuarios.IDUSUARIOS=usuariosroles.IDUSUARIOS
             left JOIN roles
                ON usuariosroles.idrol=roles.idrol
                WHERE usuarios.nombre=l_nombre;
                x1 NUMBER;
                x2 NUMBER;
                x3 NUMBER;
                x4 NUMBER;
                x5 VARCHAR(400);
                x6 VARCHAR(400);
BEGIN
   OPEN l_cursor; 
    FETCH l_cursor INTO x1,x2,x3,x4,x5,x6;   
 if l_cursor%found then
dbms_output.put_line(x5||', '||x6);  
  else 
  dbms_output.put_line('dato no encontrado '); 
  END if;
  CLOSE  l_cursor;
END Consultar;
    
asked by CarlosQ 22.05.2018 в 18:25
source

0 answers