go执行sql的几种方式

1.db.Exec(query)

create_dblink_sql := "create table  test_oci ( name varchar2(10),age number, sex char(1 char),comments varchar2(255))"
res,err := db.Exec(create_dblink_sql)

2.db.Exec(queryTemplate, params)

res,err := db.Exec("insert into test_oci values(:1,:2,:3,:4)","lilei",26,"男","test for insert")
if err != nil {
   panic("Error execute sql: " + err.Error())
}
defer res.Close()
lastId, err := res.LastInsertId()
if err != nil {
   log.Println(err)
}
rowCnt, err := res.RowsAffected()
if err != nil {
   log.Println(err)
}
log.Printf("res is %v,LastInsertId:%v,RowsAffected:%v\n" ,res,lastId,rowCnt)

3.db.Query(query)

_,err = db.Query("update  test_oci set comments='test for update' where name= 'hanmeimei'")
if err != nil {
    panic("Error query sql: " + err.Error())
}

4.db.Query(queryTemplate, params)

rows,err := db.Query("select * from test_oci where name = :1","hanmeimei")
if err != nil {
    panic("Error query sql: " + err.Error())
}
defer rows.Close()
for rows.Next() { 
    var name,sex,comments string    
    var age int 
    rows.Scan(&name,&age,&sex,&comments)
    log.Printf("result is %v|%v|%v|%v\n",name,age,sex,comments)
}

5.db.Prepare

stmt,err := db.Prepare("insert into test_oci values(:1,:2,:3,:4)")
defer stmt.Close()
stmt.Exec("hanmeimei",11,"女","test for insert")
stmt.Exec("hanmeimei",12,"女","test for insert")
stmt.Exec("hanmeimei",13,"女","test for insert")
stmt.Exec("hanmeimei",14,"女","test for insert")
stmt.Exec("hanmeimei",15,"女","test for insert")
stmt.Exec("hanmeimei",16,"女","test for insert")
stmt.Exec("hanmeimei",17,"女","test for insert")

总结：

1.db.Query(queryTemplate, params)，db.Exec(queryTemplate, params)，db.Prepare的工作的原理一致，都是先请求prepare statement，再请求execute statement，需要注意的是前两者在调用结束后会自动释放链接回到池，而db.Prepare则需要我们显式调用stmt.Close()释放
2.db.Query和db.Exec都可以执行DML、DDL、DQL，二者的主要区别是在返回的结果类型，db.Query返回具体的输出结果，db.Exec返回LastInsertId(还没理解有啥用haha)和RowsAffected(命令影响的行数)
3.当我们不在意输出结果的时候，db.Exec是更好的选择，因为如果我们丢弃db.Query返回的*Rows，会因为*Rows没有被正常关闭而导致打开的连接数增加。
4.当我们需要事务时，我们需要调用db.Begin，因为上面的几种方法都是执行完即刻提交的。
5.db.Prepare适合的场景是批量进行sql操作时，prepare statement可以减少sql在库中的解析次数。它更像是db.Query(queryTemplate, params)和db.Exec(queryTemplate, params)的扩展版本

参考:

https://aloksinhanov.medium.com/query-vs-exec-vs-prepare-in-golang-e7c49212c36c
https://stackoverflow.com/questions/50664648/why-even-use-db-exec-or-prepared-statements-in-golang