농부지기

[ MS SQL.PLSQL - 특징 ]

1. SP는 반복적인 사용을 위해 서버 상에서 Transact-SQL 문장의 집합을 캡슐화 하는 데이터베이스 개체(Object)이다.  ( MS-Sql Server 는 T-SQL  문법을 사용)

** Java에서 TableType으로 Procedure 호출 2 **

1. 테이블

CREATE TYPE [dbo].[INITVALS_MSG] AS TABLE(
    [SDate] [decimal](8, 0) NOT NULL,
    [EDate] [decimal](8, 0) NOT NULL,
    [PlantCode] [nvarchar](10) NOT NULL,
    [LoadType] [nchar](8) NOT NULL,
    [Asset] [bigint] NOT NULL
)

2. Procedure

ALTER PROCEDURE [dbo].[RegisterInitAssets]
    @initmsg INITVALS_MSG ReadOnly
AS
BEGIN
    ...

3. java단에서 호출 예제1

   3.1 vo

public class DBInitialAsset {
    private Integer sDate;
    private Integer eDate;
    private String plantCode;
    private String loadType;
    private Integer asset;

    public DBInitialAsset() {

    }
        ...
}

   3.2 sql procedure 호출 예제1

SQLServerDataTable sourceDataTable = new SQLServerDataTable();   
sourceDataTable.addColumnMetadata("SDate", java.sql.Types.DECIMAL);
sourceDataTable.addColumnMetadata("EDate", java.sql.Types.DECIMAL);
sourceDataTable.addColumnMetadata("PlantCode", java.sql.Types.NVARCHAR);
sourceDataTable.addColumnMetadata("LoadType", java.sql.Types.NCHAR);
sourceDataTable.addColumnMetadata("Asset", java.sql.Types.BIGINT);

// sample data
sourceDataTable.addRow(123, 234, "Plant1", "Type1", 123234);   
sourceDataTable.addRow(456, 789, "Plant2", "Type2", 456789);   

try (CallableStatement cs = conn.prepareCall("{CALL dbo.RegisterInitAssets (?)}")) {
    ((SQLServerCallableStatement) cs).setStructured(1, "dbo.INITVALS_MSG", sourceDataTable);
    boolean resultSetReturned = cs.execute();
    if (resultSetReturned) {
        try (ResultSet rs = cs.getResultSet()) {
            rs.next();
            System.out.println(rs.getInt(1));
        }
    }
}

   3.2 sql procedure 호출 예제2

connection = dataSource.getConnection();
CallableStatement statement = connection.prepareCall("{? = call dbo.RegisterInitAssets(?)}");
statement.registerOutParameter(1, OracleTypes.CURSOR);//you can skip this if procedure won't return anything
statement.setObject(2, new InitvalsMsg()); //I hope you some kind of representation of this table in your project
statement.execute();

ResultSet set = (ResultSet) statement.getObject(1);//skip it too if its not returning anything

* Java에서 TableType으로 Procedure 호출 *

- Procedure

CREATE PROCEDURE [dbo].[table_sel]
@tbl INT32Table READONLY
AS
BEGIN
SET NOCOUNT ON;
SELECT value FROM @tbl
END

-아래는 호출이 안되는 script

SqlConnection sqlconn;
System.Data.DataTable tbl = new System.Data.DataTable("INT32Table", "dbo");
tbl.Columns.Add("value", typeof(int));
tbl.Rows.Add(2);
tbl.Rows.Add(3);
tbl.Rows.Add(5);
tbl.Rows.Add(7);
tbl.Rows.Add(11);
using (SqlCommand command = new SqlCommand("table_sel"))
{
    command.Connection = sqlconn;
    command.Parameters.AddWithValue("@tbl", tbl);
    command.CommandType = System.Data.CommandType.StoredProcedure;
    SqlDataReader reader = command.ExecuteReader();

    //do stuff
}

-아래는 될거 같은 script 1

java.sql.connection connection = //driver, url, database, credentials ...

try
{
    PreparedStatement ps =
        connection.prepareStatement("insert into tbl values (?)");
    ps.setInt(1, your 1st int);
    ps.addBatch();
    ps.setInt(1, your 2nd int);
    ps.addBatch();
    ps.setInt(1, your 3rd int);
    ps.addBatch();
    ps.executeBatch();
}
catch (SQLException e)
{
    // err handling goes here
}
finally
{
    // close your resources
}

-아래는 될거 같은 script 2

java.sql.connection connection = //driver, url, database, credentials ...

try
{
    CallableStatement cs =
        connection.prepareCall("{ call table_sel(?) }");
    cs.setInt(1, your int);
    cs.execute();
}
catch (SQLException e)
{
    // err handling goes here
}
finally
{
    // close your ressources
}