mysql存储过程与外文件

I have a mysql stored procedure where I want to do two things 1. query a table and return the result as normal result set. 2. iterate through the result set and create a formatted text file from the procedure itself.

我有一个mysql存储过程我想做两件事1。查询一个表并返回结果作为正常的结果集。遍历结果集，并从过程本身创建一个格式化的文本文件。

I looked at INTO OUTFILE, but it seems INTO OUTFILE writes the result raw to the specified file and also if we use INTO OUTFILE resultset will be empty. Seems we can't have both.

我查看了OUTFILE，但它似乎是OUTFILE将结果原始地写到指定的文件中，而且如果我们在OUTFILE resultset中使用，结果也是空的。看来我们不能两者兼得。

Here is my sample SP

这是我的样本SP

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getdeals`()
BEGIN

select * INTO OUTFILE '/Users/tuser/sql/out.txt' from deals;

END

Any thoughts ? Thanks Prem

任何想法吗?谢谢但是过犹不及

2 个解决方案

#1

Assuming (for the sake of the example) that your deals table looks like

假设(为了这个例子)您的交易表是这样的

---------------------------
| id | deal_date  | deal  |
---------------------------
| 1  | 2014-03-10 | Deal1 |
| 2  | 2014-03-11 | Deal2 |
| 3  | 2014-03-12 | Deal3 |
---------------------------

Now your procedure code might look

现在您的过程代码可能会看到

DELIMITER //
CREATE PROCEDURE get_deals()
BEGIN
    -- create a temporary table and fill it with the desired subset of data
    -- Apply WHERE and ORDER BY as needed
    DROP TEMPORARY TABLE IF EXISTS tmp_deals;
    CREATE TEMPORARY TABLE tmp_deals 
    SELECT id, deal_date, deal -- explicitly specify real column names here. Don't use SELECT *. It's a bad practice.
      FROM deals
     ORDER BY id DESC;

    -- write the resultset to the file
    SELECT * 
      INTO OUTFILE '/path/to/deals.txt'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
      FROM tmp_deals;

    -- return the resultset to the client
    SELECT * FROM tmp_deals; 
END//
DELIMITER ;

After executing it:

执行后:

CALL get_deals();

On the client you'll get:

在客户端你会得到:

---------------------------
| id | deal_date  | deal  |
---------------------------
| 3  | 2014-03-12 | Deal3 |
| 2  | 2014-03-11 | Deal2 |
| 1  | 2014-03-10 | Deal1 |
---------------------------

And the file contents will be:

文件内容为:

3,"2014-03-12","Deal3"
2,"2014-03-11","Deal2"
1,"2014-03-10","Deal1"

Note: when using OUTFILE MySQL requires that the file should be created afresh. If you leave the file in the output directory then on the subsequent procedure call you'll get the following error

注意:使用OUTFILE MySQL时，需要重新创建文件。如果您将文件保留在输出目录中，那么在随后的过程调用中，您将得到以下错误

File '/path/to/deals.txt' already exists

文件的路径/ /交易。txt”已经存在

One way to workaround it is by appending a timestamp to the filename either within the procedure itself or by passing a value through a parameter.

解决此问题的一种方法是在过程本身中向文件名添加时间戳，或者通过参数传递值。

#2

DELIMITER $$

SqlCeConnection connection = new SqlCeConnection(conSTR);

string sql = "SELECT * INTO OUTFILE '/Users/tuser/sql/out.txt' FROM deals";
connection.Open();
SqlCeCommand cmd = new SqlCeCommand(sql, connection);
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataSet ds=new DataSet();
da.Fill(ds);
//datagridview1 is name of datagridview in form:
datagridview1.DataSource=ds.Tables[0];
connection.Close();

Will that work for you?

这对你有用吗?

#1