Return a data from a stored procedure in Oracle

Question

Return a data from a stored procedure in Oracle

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have this stored procedure:

create or replace procedure spu_modificarTrabajador ( tnombre varchar2, tapellido varchar2, tid_cargo number, tdireccion varchar2, trut varchar2,tsueldo number, tcorreo varchar2, tcomuna number)
is
begin
    update TRABAJADOR
       set NOMBRE_TRABAJADOR=tnombre
           ,APELLIDO_TRABAJADOR=tapellido
           ,ID_CARGO=tid_cargo
           ,DIRECCION_TRABAJADOR=tdireccion
           ,RUT_TRABAJADOR=trut
           ,SUELDO=tsueldo
           ,CORREO_TRABAJADOR=tcorreo
           ,ID_COMUNA=tcomuna
     where RUT_TRABAJADOR=trut;
end;

My problem is that I need you to return some data that validates the modification.

sql oracle oracle-11g stored-procedure

asked by Constanza osorio escudero 02.07.2017 в 08:52

source

2 answers

Clone a table and clone text fields within it Files activity_main.xml and content_main.xml

score 1 · Answer 1

You could do something like the following, so that you return variables with the keyword OUT and thus indicate that it is an output parameter (the keyword IN is optional and it only indicates that the parameter is input). In my example below, the procedure returns the number of records affected by the update, an error code and an error message in case of an unhandled exception. The EXCEPTION block will be executed when an exception occurs, whether intentionally thrown or not. In case of an unhandled exception, the OTHERS block will be executed and the Oracle error code and message will be assigned to the SQLCODE and SQLERRM variables . I hope it helps. Almost all of my stored procedures follow the same structure.

CREATE OR REPLACE PROCEDURE spu_modificarTrabajador (
    tnombre IN VARCHAR2,
    tapellido IN VARCHAR2,
    tid_cargo IN NUMBER,
    tdireccion IN VARCHAR2,
    trut IN VARCHAR2,
    tsueldo IN NUMBER,
    tcorreo IN VARCHAR2,
    tcomuna IN NUMBER,
    reg_afectados OUT NUMBER,
    cod_err OUT NUMBER,
    msg_err OUT VARCHAR2
) IS
BEGIN
    cod_err := 0;
    msg_err := 'OK';

    UPDATE trabajador
    SET nombre_trabajador = tnombre,
        apellido_trabajador = tapellido,
        id_cargo = tid_cargo,
        direccion_trabajador = tdireccion,
        rut_trabajador = trut,
        sueldo = tsueldo,
        correo_trabajador = tcorreo,
        id_comuna = tcomuna
    WHERE rut_trabajador = trut;

    reg_afectados := SQL%ROWCOUNT;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        reg_afectados := 0;
        cod_err := SQLCODE;
        msg_err := SQLERRM;
END;

score 0 · Answer 2

Hi, I would use the PLESQL BEGIN / EXCEPTION, which is similar to a Try / Catch block in other languages. (In the Begin you put what you want to try to do, and at the end of the Begin you return the value you want, and in the Exception you put what you want to be returned if there has been an error).

Here is the link to the official page with the documentation: Documentation

Still, I'll give you an example here:

BEGIN
   SELECT * FROM tabla_inventada
EXCEPTION
   WHEN NO_DATA_FOUND THEN  -- Si no encuentra ningún dato entraría aquí.

I hope I have been helpful. Greetings !!