oracle 使用UDT作为参数调用存储过程

xqnpmsa8  于 2023-10-16  发布在  Oracle
关注(0)|答案(1)|浏览(124)

我想知道是否有可能调用一个同时具有in和out UDT(用户定义类型)参数的Oracle存储过程?
这是我一直在做的例子:
我有5个结构对应于我的Oracle数据库中的5个UDT:

struct ASO_BOOKABLE {
    aso_id: String,
    forg_types: FORGTYPE,
}

struct FORGTYPE {
    values: Vec<i8>,
}

struct BOOKABLETYPE {
    bookables: BOOKABLETABTYPE,
    STATUS: String, 
}

struct BOOKABLETABTYPE {
    values: Vec<BOOKABLE>,
}

struct BOOKABLE {
    ifi_id: String,
    head_id: String,
    forgtet_id: String,
}

正如您所看到的,有很多UDT,甚至是嵌套表。我想调用我的存储过程,它接受一个ASO_BOOKABLE对象作为IN参数,并有一个OUT参数BOOKABLETYPE
我的直觉是我应该使用这样的东西:

let rows = connection.query_as(
    "CALL myStoredProcedure($1, NULL)",
    &[&ASO_BOOKABLE as &(dyn ToSql)],
)?;

我不确定这是否是正确的方法,我也有一点困难,为我的结构体编写FromSqlToSql trait实现。有人能给我指个方向吗?

8zzbczxx

8zzbczxx1#

下面是示例代码。

use oracle::sql_type::{Collection, FromSql, Object, OracleType};
use oracle::{Connection, Result, SqlValue};

// Implement to_oracle_object functions for IN parameters.
// The ToSql trait is available but it is complex for Object types.

impl ASO_BOOKABLE {
    fn to_oracle_object(&self, conn: &Connection) -> Result<Object> {
        // create a new object.
        let obj_type = conn.object_type("ASO_BOOKABLE")?;
        let mut obj = obj_type.new_object()?;
        // set Oracle object members.
        obj.set("ASO_ID", &self.aso_id)?;
        obj.set("FORG_TYPES", &self.forg_types.to_oracle_object(conn)?)?;
        Ok(obj)
    }
}

impl FORGTYPE {
    fn to_oracle_object(&self, conn: &Connection) -> Result<Collection> {
        // Create a new collection
        let coll_type = conn.object_type("FORGTYPE")?;
        let mut coll = coll_type.new_collection()?;
        // set collection elements
        for i in &self.values {
            coll.push(i)?;
        }
        Ok(coll)
    }
}

// Implement FromSql for OUT parameters

impl FromSql for BOOKABLETYPE {
    fn from_sql(val: &SqlValue) -> Result<BOOKABLETYPE> {
        let obj = val.get::<Object>()?;
        Ok(BOOKABLETYPE {
            bookables: obj.get("BOOKABLES")?,
            STATUS: obj.get("STATUS")?,
        })
    }
}

impl FromSql for BOOKABLETABTYPE {
    fn from_sql(val: &SqlValue) -> Result<BOOKABLETABTYPE> {
        let coll = val.get::<Collection>()?;
        Ok(BOOKABLETABTYPE {
            values: coll.values().collect::<Result<Vec<_>>>()?,
        })
    }
}

impl FromSql for BOOKABLE {
    fn from_sql(val: &SqlValue) -> Result<BOOKABLE> {
        let obj = val.get::<Object>()?;
        Ok(BOOKABLE {
            ifi_id: obj.get("IFI_ID")?,
            head_id: obj.get("HEAD_ID")?,
            forgtet_id: obj.get("FORGTET_ID")?,
        })
    }
}

// Call myStoredProcedure
fn call_my_stored_procedure(conn: Connection, aso_bookable: &ASO_BOOKABLE) -> Result<BOOKABLETYPE> {
    // Create an Object bound to the first parameter.
    let aso_bookable_obj = aso_bookable.to_oracle_object(&conn)?;
    // Create an OracleType to tell the type of the second parameter.
    // NULL is set to the parameter before the statement is executed.
    let bookabletype_oracle_type = OracleType::Object(conn.object_type("BOOKABLETYPE")?);
    // Execute
    let stmt = conn.execute(
        "BEGIN myStoredProcedure(:1, :2); END;",
        &[&aso_bookable_obj, &bookabletype_oracle_type],
    )?;
    // Get the OUT parameter
    Ok(stmt.bind_value(2)?)
}

相关问题