Stepan's questions

我正在运行需要密码的 Oracle 数据库更新脚本。我安装了 Oracle 钱包。如何将 Oracle 钱包传递给 sh 脚本，以便在运行脚本时无需输入密码？

我有我的 sqlnet.ora

sqlnet.expire_time=60
sqlnet.inbound_connect_timeout=300
sqlnet.allowed_logon_version_server=10
sqlnet.allowed_logon_version_client=10

WALLET_LOCATION =
(SOURCE =
   (METHOD = FILE)
      (METHOD_DATA =
          (DIRECTORY = /oracle/app/oracle/product/base19/19/network/admin/wallet)
   )
)

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = TRUE

凭据已正确安装：
$ mkstore -wrl "/oracle/app/oracle/product/base19/19/network/admin/wallet" -listCredential

Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   
List credential (index: connect_string username)
1: SID USER PASSWORD

我正在运行脚本 update_sql.sh：

#!/bin/bash
sqlplus cobrball @release.sql

发布.sql：

spool release.log;
exit;

我正在使用 ogr2ogr 从 Oracle 复制表到 PostGIS。当我运行尝试加载这些我得到的，

ERROR 1: ERROR: permission denied for relation spatial_ref_sys when copying <table>@AWS

我有两个名为的表my_nodes，restaurants其中包含名称和geoloc数据。我需要为每个 my_node 返回最近的餐厅和从节点到餐厅的距离的表。

    select n.node_id, r.r_name , sdo_nn_distance(1) as min_distance from 
        (select nodeid as node_id, geoloc  from my_nodes@my_server ) n,
        (select r_name, GEOLOC from restaurants) r 
        where sdo_nn   (n.geoloc, r.GEOLOC,   'Unit = MILE sdo_num_res = 1', 1) = 'TRUE'

上面的查询返回：

    ORA-13249: SDO_NN cannot be evaluated without using index
    ORA-06512: at "MDSYS.MD", line 1723
    ORA-06512: at "MDSYS.MDERR", line 17
    ORA-06512: at "MDSYS.PRVT_IDX", line 9

如何解决？

有两个包含对象及其坐标的表。一个包含酒店，另一个包含餐厅。如何建立一个表，为每家酒店列出最近的餐厅名称和距离？

WITH restaurant AS (
           SELECT 'A' r_name, 0.0 x, 0.0 y FROM dual UNION ALL
           SELECT 'B' r_name, 0.0 x, 6.0 y FROM dual UNION ALL
           SELECT 'C' r_name, 9.5 x, 0.5 y FROM dual UNION ALL
           SELECT 'D' r_name, 8.2 x, 4.7 y FROM dual -- etc           
           ),

     hotel AS ( 
           SELECT 'First_Hotel' h_name, 1.0 x, 6.0 y FROM dual UNION ALL  
           SELECT 'Second_Hotel' h_name, -4.0 x, 3.0 y FROM dual -- etc
           )               

     select h.h_name, r.r_name as nearest_restaurant, distance from restaurant r, hotel h  /* some query */

应该返回

 hotel          nearest_restaurant    distance
 First_Hotel       B                    1     -- B is the nearest restaurant to First_Hotel. Distance is sqrt((1.0-0.0)^2+(6.0-6.0)^2)=1
 Second_Hotel      A                    5     --if both A and B on the same distance choose based on r_name 

编辑：我也有geoloc两个带有 x 和 y 坐标的表。geoloc 可以帮助构建查询吗？

我有一个表，其中包含从 2 到 8my_link_t的列t.weight类型number和分类speedcat类型整数值。我想将数据从 min=0、max=0.6、step = 0.001 拆分到桶中，并构建一个 3D 图以查看每个类别的权重分布。

初始数据看起来像
weight speedcat
0.0234 2
0.8643 6 0.1854
7
（权重在 0 和 0.6 之间，speedcat 在 2 和 8 之间的还有一亿个条目）

这些查询返回正确的结果并在不到一分钟的时间内完成：

--repeat for each variable. Here we loook for speedcat =8
--It takes seconds to run this query
create table   histogram_tbl_8  as (
  select  ttt."Start" as bucket_index, ttt.hist_row as bin8  --here
  FROM ((
  SELECT  Bucket*1 "Start" , Bucket "End", Count(Bucket) hist_row
  FROM (SELECT WIDTH_BUCKET (weight, 0, 0.6, 601) Bucket FROM my_link_t  where speedcat=8)
      GROUP BY Bucket ORDER BY Bucket ) ttt   )   );

speedcat对于in range 重复上述查询七次2..8

--if a bin is empty populate it with zero, don't skip it.
create table histogram_output as (
select  tr.bucket_index,
    CASE
        WHEN 1 > (select count(*) from histogram_tbl_2   htm where htm.bucket_index = tr.bucket_index)   THEN 0
        ELSE (select htm.bin2 from histogram_tbl_2   htm where htm.bucket_index =  tr.bucket_index and rownum = 1)
     END
     as b2,
     --same for b3-b7
      CASE
        WHEN 1 > (select count(*) from histogram_tbl_8   htm where htm.bucket_index = tr.bucket_index)   THEN 0
        ELSE     (select htm.bin8    from histogram_tbl_8   htm where htm.bucket_index =  tr.bucket_index and rownum = 1)
     END
     as b8
     FROM  (SELECT LEVEL as bucket_index, 0 as b2, /* 0 as b3, 0 as b4, 0 as b5, 0 as b6, 0 as b7, */ 0 as b8  FROM DUAL CONNECT BY LEVEL < 600)  tr
     )

最后

      select sum(b2), sum(b3),sum(b4),sum(b5),sum(b6),sum(b7),sum(b8) from histogram_output
       select bucket_index,
              round(b2 * 1000000 / 12921) as  b2, --normalize so that total is 1000000 ppm
              -- repeat for b3-b7
              round(b8 * 1000000 / 6262) as  b8 --normalize so that total is 1000000 ppm
         from histogram_output

我得到一张桌子

bin_end speedcat_2 speedcat_3 speedcat_4 .. speedcat_8
0.001
0.002 .. 0.599 0.600

显示此类别和此 bin 中的对象的 ppm 现在，当我组合查询时

-- DONT USE THE EXAMPLE BELOW - it is ineefficient  (runs 2+ hours instead of seconds for the method above)
       SELECT  Bucket_2*1 "Start" , Bucket_2 "End",
       Count(Bucket_2) as b2,
       --same for b3 .. b7
       Count(Bucket_8) as b8

      FROM
      (
          SELECT WIDTH_BUCKET (t2.weight, 0, 0.6, 601) Bucket_2,
                 --same for t3,.. t7
                 WIDTH_BUCKET (t8.weight, 0, 0.6, 601) Bucket_8

           FROM (select weight from my_link_t where  speedcat = 2) t2,
           -- ..speedcat = 3) t3, .. speedcat = 4) t4, etc
            (select weight from my_link_t where  speedcat = 8 ) t8

      )
      GROUP BY Bucket_2 ORDER BY Bucket_2
      ------

查询运行几个小时（运行时间比单个查询长大约 500 倍），直到我终止它。书籍建议在 SQL 中进行所有数据切片。这个例子表明，在复杂查询的情况下，将数据加载到 Java 并将其切片可能会更好。

什么会导致差异？

我有一个查询，我想把它包装成一个函数。当前查询返回in_count, in_sum, out_count, out_sum. 我只知道如何返回“原始类型”。所以目前我有四个不同的函数，带select in_count from /* query 3*/,select in_sum from /* query 3*/等。即使查询 3 返回所有必要的字段。我结合在一个声明中。这是多余的，我应该能够从单个函数返回in_count, in_sum, out_count, 。out_sum

我有一个查询，它返回给定硬编码段的附近段数和权重（参见查询 3）。我想创建一个函数 f(id) 从参数执行查询 3 的 id 。到目前为止，我能够创建四个函数，每个函数都返回一个 FIELD（参见查询 1）。然后查询 2 可以调用这些我们的函数来获取包含每个段 ID 的统计数据行的表。我不喜欢这个解决方案中的几件事： (a) 我执行四次基本相同的代码以返回四个数字。我希望我可以执行一次代码并返回一个元组（int，int，int，int）。(b) 任何关于用户定义函数的讨论都是从“指针不好”开始的。在这里，我似乎使用了指针。问题：创建查询 2 生成的表的正确方法是什么？我怀疑 Oracle 允许返回类型 row(int,int,int,int), 但是语法是什么？在哪里可以找到用户定义函数的示例？Oracle 文档仅在其文档中显示如何返回数字或二进制整数（那是什么？）。 https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm#i2153260

  --(Query 1)  creates a function
CREATE OR REPLACE Function dummy_function   ( name_in IN number )
   RETURN number
IS
   cnumber number;
   cursor c1 is
       select rt.in_count from (   --also do rt.in_sum, rt.out_count, rt.out_sum as three additional functions
     --COMPLEX_QUERY
  ) rt ;
BEGIN
   open c1;
   fetch c1 into cnumber;
   if c1%notfound then
      cnumber := 0;
   end if;
   close c1;
RETURN cnumber;
EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

-- (Query 2)
SELECT id, dummy_function(t.id) AS func_in , dummy_function1(t.id) AS func_in_sum, dummy_function2(t.id) AS func_out, dummy_function3(t.id) AS func_out_sum , t.* FROM dummy_links t;

-- (Query 3)   -- COMPLEX_QUERY:  -- note  START WITH ID = name_in: here name_in is an argument (id=in range 1..10 works)
     WITH
dummy_links as (
SELECT 1 ID, 'A' in_node, 'B' out_node, 17 weight FROM dual UNION ALL
           SELECT 2 ID, 'B' in_node, 'A' out_node, 4 weight FROM dual UNION ALL
           SELECT 3 ID, 'C' in_node, 'A' out_node, 5 weight FROM dual UNION ALL
           SELECT 4 ID, 'A' in_node, 'D' out_node, 6 weight FROM dual UNION ALL
           SELECT 5 ID, 'C' in_node, 'G' out_node, 33 weight FROM dual UNION ALL
           SELECT 6 ID, 'X' in_node, 'Z' out_node, 12 weight FROM dual UNION ALL
           SELECT 7 ID, 'Z' in_node, 'Y' out_node, 15 weight FROM dual UNION ALL
           SELECT 8 ID, 'X' in_node, 'Y' out_node, 42 weight FROM dual UNION ALL
           SELECT 9 ID, 'K' in_node, 'M' out_node, 66 weight FROM dual UNION ALL
           SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual),
     res AS (SELECT ID,
            in_node,
            out_node,
            weight,
            MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () orig_in_node,
            MAX(CASE WHEN ID = connect_by_root(ID) THEN out_node END) OVER () orig_out_node,
            MAX(CASE WHEN ID = connect_by_root(ID) THEN ID END) OVER () orig_id,
            CASE WHEN MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () IN (in_node, out_node) THEN 'in'
               ELSE 'out'
            END direction_from_orig_node,
            LEAST(in_node, out_node) node1,
            GREATEST(in_node, out_node) node2,
            row_number() OVER (PARTITION BY LEAST(in_node, out_node), GREATEST(in_node, out_node) ORDER BY weight) rn
         FROM   dummy_links
         START WITH ID = name_in
         CONNECT BY NOCYCLE (PRIOR out_node IN (in_node, out_node)
                  OR PRIOR in_node IN (in_node, out_node))
                  AND LEVEL <= 2)
  SELECT orig_id,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'in' THEN node1||'~'||node2 END) in_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'in' THEN weight END), 0) in_sum,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'out' THEN node1||'~'||node2 END) out_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'out' THEN weight END), 0) out_sum
  FROM   res
  WHERE  rn = 1
  AND    ID != orig_id
  GROUP BY orig_id

数据集如下所示：

该表还没有 sum(segLength)，但可以通过将sum(segLength) from road_table group by roadID A roadID(ei Lake Street) 属于 a roadType(ei St) 并由路段组成（segID对于给定的路障，ei 为 2005）来计算。

我想绘制按道路长度分组的道路平均速度，进一步按道路类型分组，如下面的示例输出所示。

如何为每个添加列roadType，在每个roadType组中将道路放入箱中，在每个箱中计算平均值maxSpeed？

我正在使用 PL/SQL。如有必要，我可以翻译来自 MySQL 或 PostgreSQL 的查询。

我有两个表：t1和t2。分为三个步骤：

1. 在t1select a set_of_stringswith id = 1 ("1a" and"3c")。
2. 从中t1选择对应于OPTIONID ='A'并包含子字符串的字符串set_of_strings。

t1

身份名称   
1个“1a”   
2“2b”   
1 "3c"   

t2

选项ID TXT   
  一个“7h 9t”   
  B“1a 8n”   
  一个“2b 4r”    
  一个“3c 6u”

查询

SELECT 
    NAME 
FROM t1 
WHERE ID = 1; -- 1a, 3c

SELECT
    TXT 
FROM t2 
WHERE OPTIONID = 'A' AND (TXT LIKE '%1a%' OR TXT LIKE '%3c%') --"3c 6u"

问题

如何使TXT LIKE '%1a%' OR TXT LIKE '%3c%'零件自动化？

LIKE'%1a%'是安全的，因为所有条目都具有相同的格式，即没有1a2inTXT列。