有输出函数的存储过程

 

CREATE OR new PROCEDURE ADD_LOCATION (

    p_location_id OUT NUMBER,

    p_street_address IN VARCHAR2,

    p_postal_code IN VARCHAR2,

    p_city IN VARCHAR2,

    p_state_province IN VARCHAR2,

    p_country_id IN CHAR

)

AS

BEGIN

    INSERT INTO LOCATIONS (

        LOCATION_ID,

        STREET_ADDRESS,

        POSTAL_CODE,

        CITY,

        STATE_PROVINCE,

        COUNTRY_ID)

    VALUES (

        LOCATIONS_SEQ.NEXTVAL,

        p_street_address,

        p_postal_code,

        p_city,

        p_state_province,

       p_country_id

    );

   

    SELECT LOCATIONS_SEQ.CURRVAL INTO p_location_id FROM DUAL;

END ADD_LOCATION;

C＃调用得到存储过程的输出参数值：

// create the connection

OracleConnection conn = new OracleConnection("Data Source=oracledb;

    User Id=UserID;Password=Password;");

// create the command for the stored procedure

OracleCommand cmd = new OracleCommand();

cmd.Connection = conn;

cmd.CommandText = "ADD_LOCATION";

cmd.CommandType = CommandType.StoredProcedure;

// add the parameters for the stored procedure including the LOCATION_ID

// sequence value that is returned in the output parameter p_location_id

cmd.Parameters.Add("p_location_id", OracleType.Number).Direction =

    ParameterDirection.Output;

cmd.Parameters.Add("p_street_address", OracleType.VarChar).Value =

    "123 Any Street";

cmd.Parameters.Add("p_postal_code", OracleType.VarChar).Value = "33040";

cmd.Parameters.Add("p_city", OracleType.VarChar).Value = "Key West";

cmd.Parameters.Add("p_state_province", OracleType.VarChar).Value = "FL";

cmd.Parameters.Add("p_country_id", OracleType.VarChar).Value = "US";

// execute the command to add the records      

OracleString rowId;

conn.Open();

int rowsAffected = cmd.ExecuteOracleNonQuery(out rowId);

conn.Close();

// output the results

Console.WriteLine("Rows affected: " + rowsAffected);

Console.WriteLine("Location ID: " +   

cmd.Parameters["p_location_id"].Value);