golang call procedure or golang execute procedure. 使用Golang调用Oracle存储过程(Procedure),使用的是goracle库,目前存储过程还不完善，CallProc方法还没成型，只好使用Execute方式实现。 例子中涉及到oracle存储过程的输入和输出参数调用。

callProc.go

package main

import (
  	"errors"
	"fmt"

	"github.com/tgulacsi/goracle/oracle"
)

type db struct {
	conn   *oracle.Connection
	cursor *oracle.Cursor
}

func New() (*db, error) {
	// conn
	user, pwd, dsn := oracle.SplitDSN("user/password@192.168.1.1:1521/sid")
	conn, err := oracle.NewConnection(user, pwd, dsn, true)
	if err != nil {
		fmt.Println("oracle.NewConnection: ", err)
		return nil, err
	} else {
		if err := conn.Connect(0, false); err != nil {
			fmt.Println("conn.Connect: ", err)
			return nil, err
		}
	}

	fmt.Println("IsConnected: ", conn.IsConnected())

	// cursor
	cursor := oracle.NewCursor(&conn)

	fmt.Println("count: ", cursor.GetRowCount())
	fmt.Println("DDL: ", cursor.IsDDL())
	fmt.Println("Open: ", cursor.IsOpen())
	fmt.Println("String: ", cursor.String())
	fmt.Println("-----------------------------------")

	return &db{&conn, cursor}, nil
}

func (db *db) Close() {
	if db.cursor != nil {
		db.cursor.Close()
	}
	if db.conn != nil {
		if err := db.conn.Close(); err != nil {
			fmt.Println("db.conn.Close: ", err)
		}
	}
}

func (db *db) FetchOne() error {
	if err := db.cursor.Execute(`SELECT 1+1 FROM DUAL`, nil, nil); err != nil {
		fmt.Println("db.cursor.Execute: ", err)
		return err
	}

	// fetch one
	row, err := db.cursor.FetchOne()
	if err != nil {
		fmt.Println("db.cursor.FetchOne: ", err)
		return err
	}

	if row != nil {
		switch row[0].(type) {
		case float64:
			fmt.Println("float64: ", row[0].(float64))
		default:
			fmt.Println(fmt.Sprintf("%T", row[0]))
		}
	}

	return nil
}

func (db *db) CallProc() error {
	const dropTable = `
        DROP TABLE tb_user CASCADE CONSTRAINTS
    `

	const createTable = `
        CREATE TABLE tb_user(
            id NUMBER(38) PRIMARY KEY NOT NULL,
            name VARCHAR2(20) NOT NULL,
            create_time TIMESTAMP DEFAULT SYSTIMESTAMP
        )
    `

	const dropProc = `
        DROP PROCEDURE proc_test
    `

	const createProc = `
        CREATE OR REPLACE PROCEDURE proc_test(
            id IN tb_user.id%TYPE,
            name IN tb_user.name%TYPE,
            result OUT VARCHAR2
        )
        AS
        BEGIN
            INSERT INTO tb_user(id, name) VALUES(id, name);
            COMMIT;
            result:='插入数据成功';
        EXCEPTION
            WHEN OTHERS THEN
                ROLLBACK;
                result:='插入数据失败';
        END;
    `

	// drop table
	if err := db.cursor.Execute(dropTable, nil, nil); err != nil {
		fmt.Println("drop table: ", err)
	}

	// create table
	if err := db.cursor.Execute(createTable, nil, nil); err != nil {
		fmt.Println("create table: ", err)
		return err
	}

	// drop procedure
	if err := db.cursor.Execute(dropProc, nil, nil); err != nil {
		fmt.Println("drop procedure: ", err)
	}

	// create procedure
	if err := db.cursor.Execute(createProc, nil, nil); err != nil {
		fmt.Println("create procedure: ", err)
		return err
	}

	// call procedure
	out, err := db.cursor.NewVar("")
	if err != nil {
		fmt.Println("db.cursor.NewVar: ", err)
		return err
	}

	if err := db.cursor.Execute("BEGIN proc_test(:1, :2, :3); END;", []interface{}{1, "viney", out}, nil); err != nil {
		fmt.Println("db.cursor.Execute: ", err)
		return err
	}

	val, err := out.GetValue(0)
	if err != nil {
		fmt.Println("out.GetValue: ", err)
		return err
	}

	outStr, ok := val.(string)
	if !ok {
		fmt.Println("val.(string): ", fmt.Sprintf("%T", val))
		return errors.New("val is not string type")
	}

	fmt.Println("out------------------------")
	fmt.Println(outStr)

	return nil
}

func main() {
	db, err := New()
	if err != nil {
		fmt.Println("New: ", err)
		return
	}
	defer db.Close()

	// FetchOne
	if err := db.FetchOne(); err != nil {
		return
	}

	// CallProc
	if err := db.CallProc(); err != nil {
		return
	}
}