Alex Rivera | Logout

How to use local variables in stored procedures?

Asked 2010-05-27T12:31:19.720
12

if I want to select any id from a table and want to insert it's value in another table as foreign key then how i will do it through stored procedure?

Edit
Report

1 Answer

0

Oracle style variables:

DECLARE v_MyVariable1 NUMBER;
DECLARE v_MyVariable2 VARCHAR2(100);

BEGIN
    v_MyVariable1 := 0;
    SELECT INTO v_MyVariable2 CUSTOMER_NAME
        FROM CUSTOMERS;

    SELECT INTO v_MyVariable1 CUSTOMER_HISTORY_SEQ.NEXTVAL FROM DUAL;

    INSERT INTO CUSTOMERS_HISTORY (CUSTOMER_ID, CUSTOMER_NAME) VALUES (v_MyVariable1, v_MyVariable2);

    EXCEPTION
        WHEN OTHERS THEN
            NULL;
END;
answered 2010-05-27T13:37:57.767

Your Answer