学习小结-oracle

select distinct brands.*from product_prices
inner join products on products.id = product_prices.product_id
inner join brands on brands.id = products.brand_id
inner join product_batches on product_prices.batch_id = product_batches.id
where (quotation_batches.id =289859 OR quotation_batches.group_id =289859) 
and products.publish_stage IN (0,1)
 and brands.status NOT IN (1,2)

由于三张表都比较大，顺序的影响可以先不考虑；

select * from 
( select distinct * from products where products.publish_stage IN (0,1) )  t1
inner join  ( select distinct brands.*from brands where  brands.status NOT IN (1,2) ) t2 
on t1.batch_id  = t2.id
inner join product_prices t3 on  t1.id = t3.product_id
inner join product_batches t4 on t3.batch_id = t4.id

我的思路：尽量缩小联查的每个表的数据量；

下面在网上找到关于：Ruby通过OCI8操作Oracle存储的事例

1.读blob的存储过程

CREATE OR REPLACE PROCEDURE "P_READ_IMAGE"
(
V_IMG_ID IN NUMBER,
CUR_RESULT OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN CUR_RESULT FOR
    SELECT ID,IMG
    FROM T_IMAGE
    WHERE ID=V_IMG_ID ;
END;

5、ruby读图片操作

require 'oci8'
h_conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)
s_photo_target_path = "~/222.jpg"
photo_id = 1
begin
    cursor = h_conn.parse('begin P_READ_IMAGE(
          :img_id,
          :list
      ); end;')
    cursor.bind_param(':img_id', photo_id)
    cursor.bind_param(':list', nil, OCI8::Cursor)
    cursor.exec()
    ret_cursor = cursor[':list']
    puts ret_cursor.getColNames.join(",")
    while row = ret_cursor.fetch()
        puts row[0]
        File.open(s_photo_target_path, 'wb') do |f|
            f.write(row[1].read)
        end
        break;
    end
rescue OCIError
    puts '-'*80
    puts "Code: " + $!.code.to_s
    puts "Desc: " + $!.message
    puts '-'*80
end

相关链接：http://www.thinksaas.cn/topics/0/497/497546.html