yyfbaloot/database/statement.go

// Statement.go
package database

import (
	"fmt"

	"bet24.com/log"
)

const (
	AdParamInput = iota
	AdParamOutput
	AdParamInputOutput
)

const (
	AdTinyInt = iota
	AdSmallInt
	AdInteger
	AdBigint
	AdChar
	AdVarChar
	AdBinary
	AdFloat
	AdNVarChar
)

type Parameter struct {
	IO      int
	Name    string
	Type    int
	Data    interface{}
	DataLen int
}

type Statement struct {
	Params          []Parameter
	NeedReturnValue bool
	IsOpenRecordSet bool
	ProcName        string
}

func NewStatement() *Statement {
	this := new(Statement)
	return this
}

func (this *Statement) AddParamter(name string, io, t int, dataLen int, value interface{}) {
	p := Parameter{io, name, t, value, dataLen}
	this.Params = append(this.Params, p)
}

func (this *Statement) SetProcName(name string) {
	this.ProcName = name
}

func (this *Statement) SetNeedReturnValue(b bool) {
	this.NeedReturnValue = b
}

func (this *Statement) SetOpenRecordSet(b bool) {
	this.IsOpenRecordSet = b
}

func (this *Statement) GenSql() string {
	if len(this.ProcName) == 0 {
		log.Error("Statement.GenSql faile no procname")
		return ""
	}
	if len(this.Params) == 0 {
		return fmt.Sprintf("exec %s", this.ProcName)
	}

	ret := "declare "
	if this.NeedReturnValue {
		ret = fmt.Sprintf("%v @RETURN_VALUE int", ret)
		if len(this.Params) > 0 {
			ret = fmt.Sprintf("%v; declare ", ret)
		}
	}
	// 声明所有参数
	for k, v := range this.Params {
		if k != 0 {
			ret = fmt.Sprintf("%v; declare ", ret)
		}
		switch v.Type {
		case AdTinyInt:
			ret = fmt.Sprintf("%v%s TINYINT; set %s=%v", ret, v.Name, v.Name, v.Data)
		case AdSmallInt:
			ret = fmt.Sprintf("%v%s SMALLINT; set %s=%v", ret, v.Name, v.Name, v.Data)
		case AdInteger:
			ret = fmt.Sprintf("%v%s INT; set %s=%v", ret, v.Name, v.Name, v.Data)
		case AdFloat:
			ret = fmt.Sprintf("%v%s DECIMAL(18, 4); set %s=%v", ret, v.Name, v.Name, v.Data)
		case AdBigint:
			ret = fmt.Sprintf("%v%s BIGINT; set %s=%v", ret, v.Name, v.Name, v.Data)
		case AdChar:
			ret = fmt.Sprintf("%v%s CHAR(%d); set %s='%v'", ret, v.Name, v.DataLen, v.Name, v.Data)
		case AdVarChar:
			ret = fmt.Sprintf("%v%s VARCHAR(%d); set %s='%v'", ret, v.Name, v.DataLen, v.Name, v.Data)
		case AdBinary:
			ret = fmt.Sprintf("%v%s VARBINARY(%d); set %s =0x%s", ret, v.Name, v.DataLen, v.Name, v.Data)
		case AdNVarChar:
			ret = fmt.Sprintf("%v%s NVARCHAR(%d); set %s=N'%v'", ret, v.Name, v.DataLen, v.Name, v.Data)
		}
	}
	// 声明结束
	ret = fmt.Sprintf("%v;", ret)
	if !this.NeedReturnValue {
		ret = fmt.Sprintf("%v exec %s ", ret, this.ProcName)
	} else {
		ret = fmt.Sprintf("%v exec @RETURN_VALUE = %s ", ret, this.ProcName)
	}
	// 执行参数
	comma := ""
	for _, v := range this.Params {
		if v.IO == AdParamOutput || v.IO == AdParamInputOutput {
			ret = fmt.Sprintf("%v%s%v OUTPUT", ret, comma, v.Name)
		} else {
			ret = fmt.Sprintf("%v%s%v", ret, comma, v.Name)
		}
		comma = ","
	}
	// 如果返回结果集
	/*if this.IsOpenRecordSet {
		return ret
	}*/

	outStr := ""
	comma = ""
	for _, v := range this.Params {
		if v.IO == AdParamOutput || v.IO == AdParamInputOutput {
			outStr = fmt.Sprintf("%v%s%s", outStr, comma, v.Name)
			comma = ","
		}
	}
	if this.NeedReturnValue || len(outStr) > 0 {
		ret = fmt.Sprintf("%v ;select ", ret)
	}

	if this.NeedReturnValue {
		ret = fmt.Sprintf("%v @RETURN_VALUE%s%s", ret, comma, outStr)
	} else {
		ret = fmt.Sprintf("%v%v", ret, outStr)
	}
	return ret
}