jpmc26's questions

考虑函数内的以下（不完整的）PL/pgSQL 块：

CREATE OR REPLACE FUNCTION my_calc(myvar1 NUMERIC, myvar2 NUMERIC)
    RETURNS NUMERIC
    RETURNS NULL ON NULL INPUT
    IMMUTABLE
    LANGUAGE plpgsql
    AS $$
    BEGIN
        RETURN some_third_party_function(myvar1, myvar2);
    EXCEPTION WHEN internal_error THEN
        IF SQLERRM LIKE 'KnownErrorPrefix:%' THEN
            RETURN 0;
        ELSE
            -- Reraise the original exception here
            RAISE EXCEPTION '%', SQLERRM;
        END IF;
    END
    $$

当发生意外错误时，此代码将抛出具有相同消息的新异常。但是，它不会保留原始类型或上下文。

如何重新引发或重新抛出未修改的原始异常？

我正在为我们的应用程序测试 Oracle 12c。我在一个特定的模式中有类似于这个视图的东西，我们将要求MY_OWNER_SCHEMA这个问题：

CREATE OR REPLACE VIEW OBJECT_COMMENTS AS
SELECT OBJS.OWNER, OBJS.OBJECT_NAME, COMMENTS.COMMENTS
FROM ALL_OBJECTS OBJS
LEFT JOIN ALL_TAB_COMMENTS COMMENTS ON (
    COMMENTS.OWNER = OBJS.OWNER 
    AND COMMENTS.TABLE_NAME = OBJS.OBJECT_NAME
)
WHERE
    OBJS.OWNER = 'MY_OWNER_SCHEMA'
    AND OBJS.OBJECT_TYPE IN ('TABLE', 'VIEW')
WITH READ ONLY
;

该视图是一种“元数据”视图，用于（以编程方式）验证所有表和视图是否都有注释。由于MY_OWNER_SCHEMA在部署期间被锁定，因此我们授予对其MY_LOGIN_USER的权限SELECT：

GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER;

（这GRANT是在以 . 身份登录时在部署期间执行的MY_OWNER_SCHEMA。）

这在 Oracle 11g 中工作得很好，在 12c 中也可以创建它。但是，仅在 12c 中，GRANT失败并显示以下消息：

SQL Error: ORA-01720: grant option does not exist for 'SYS.ALL_OBJECTS'
01720. 00000 -  "grant option does not exist for '%s.%s'"
*Cause:    A grant was being performed on a view or a view was being replaced
           and the grant option was not present for an underlying object.
*Action:   Obtain the grant option on all underlying objects of the view or
           revoke existing grants on the view.

这是相当奇怪的，因为ALL_OBJECTS所有用户都可以访问公共接口，并且它只显示用户已经有一些访问权限的对象。Oracle 12c 中的权限模型或默认权限设置是否发生了变化？数据库是否可能以不同的方式安装？有没有办法绕过这个错误而不给这个全局系统视图提供MY_OWNER_SCHEMA明确的权限？GRANT

我需要查看用户的所有角色，包括从某个角色继承的角色。

例如，考虑这种安排：

CREATE USER TEMPUSER1 IDENTIFIED BY "TEMPUSER1" ACCOUNT LOCK;
CREATE USER TEMPUSER2 IDENTIFIED BY "TEMPUSER2" ACCOUNT LOCK;

CREATE ROLE TEMP_ROLE1;
CREATE ROLE TEMP_ROLE2;
CREATE ROLE TEMP_ROLE3;
CREATE ROLE TEMP_ROLE4;
CREATE ROLE TEMP_ROLE5;

GRANT TEMP_ROLE1 TO TEMP_ROLE3;
GRANT TEMP_ROLE2 TO TEMP_ROLE3;
GRANT TEMP_ROLE3 TO TEMP_ROLE4;
GRANT TEMP_ROLE3 TO TEMP_ROLE5;
GRANT TEMP_ROLE4 TO TEMP_ROLE6;
GRANT TEMP_ROLE5 TO TEMP_ROLE6;

GRANT TEMP_ROLE2 TO TEMPUSER1;
GRANT TEMP_ROLE4 TO TEMPUSER1;
GRANT TEMP_ROLE6 TO TEMPUSER1;
GRANT TEMP_ROLE2 TO TEMPUSER2;

那我要结果

GRANTED_USER   | GRANTED_ROLE
---------------+---------------
TEMPUSER1      | TEMP_ROLE1
TEMPUSER1      | TEMP_ROLE2
TEMPUSER1      | TEMP_ROLE3
TEMPUSER1      | TEMP_ROLE4
TEMPUSER1      | TEMP_ROLE5
TEMPUSER2      | TEMP_ROLE1
TEMPUSER2      | TEMP_ROLE2

我使用的是 Oracle 11.2g，但我也想获得未来版本的 Oracle 的答案。如果 Oracle 引入了使这更容易的新特性（比如直接向我们提供此视图而无需编写我们自己的查询），我希望这个问题保持相关性。

考虑下表：

CREATE TABLE MY_DATA (
    MY_DATA_ID NUMBER(38,0) PRIMARY KEY,
    THE_DATA VARCHAR(10) NOT NULL,
    DATA_TIMESTAMP TIMESTAMP (6) WITH LOCAL TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);

（请注意，在TIMESTAMP没有进一步限定的情况下默认为上面使用的数据类型。）

如果我使用 Data Pump 导出此数据，然后将其导入另一个具有不同时区的数据库，会发生什么情况？存储的时间是否会针对新时区进行调整，还是会表现得好像偏移量已更改而不更改其余数据？

例如，如果

• 原始数据库是美国中部时间
• 一行包含2017-10-25 12:14:38
• 我们使用美国东部时间将数据导入数据库

时间会调整为2017-10-25 12:14:38 -04:00（如在不更改日期/时间的情况下替换偏移量）还是2017-10-25 13:14:38 -04:00（= 2017-10-25 12:14:38 -05:00）？

我很抱歉没有亲自测试这个。我无权访问具有不同时区的另一个数据库，但我想知道告知我选择的数据类型。

我有下表：

CREATE TABLE MY_DATA (
  MY_DATA_ID NUMBER(38,0) PRIMARY KEY,
  GEOM SDO_GEOMETRY
);

我已经设置了它的 SDO 元数据并创建了一个空间索引：

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, SRID, DIMINFO)
VALUES ('MY_DATA', 'GEOM', 4326, MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180.0000,180.0000,0.00000000001),MDSYS.SDO_DIM_ELEMENT('Y',-90.0000,90,0.00000000001)));

CREATE INDEX SPIX_MY_DATA ON MY_DATA (GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

该表有大约 150,000 行。

我需要找到几何与特定行相交的行。这似乎是一项足够简单的任务。只需要一个JOIN和一个WHERE子句：

SELECT /*+ INDEX(MD2 SPIX_MY_DATA)*/
    MD2.MY_DATA_ID
FROM MY_DATA MD1
JOIN MY_DATA MD2 ON SDO_RELATE(MD1.GEOM, MD2.GEOM, 'mask=ANYINTERACT') = 'TRUE'
WHERE MD1.MY_DATA_ID = 143668 AND MD2.MY_DATA_ID != 143668

但是这个查询非常慢。仅获取 8 个相关行需要超过 85 秒。我们可以从查询计划中看到它没有使用空间索引，尽管有索引提示：

Plan hash value: 4008712617

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   379 |  9854 |  1390   (1)| 00:00:17 |
|   1 |  NESTED LOOPS                |               |   379 |  9854 |  1390   (1)| 00:00:17 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MY_DATA       |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C00755898 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | MY_DATA       |   379 |  4927 |  1388   (1)| 00:00:17 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("MD1"."MY_DATA_ID"=143668)
   4 - filter("MD2"."MY_DATA_ID"<>143668 AND 
              "MDSYS"."SDO_RTREE_RELATE"("MD1"."GEOM","MD2"."GEOM",'mask=ANYINTERACT 
              querytype=window  ')='TRUE')

（第 2 行是 MD1，第 4 行是 MD2。）

我尝试将提示更改为/*+ INDEX(MY_DATA SPIX_MY_DATA)*/，但这也没有效果。我已确保我的统计数据是最新的。

我怎样才能加快这个查询？我怎样才能让它使用空间索引？

我目前正在使用 Oracle 11.2。

我有一个表，其中包含一些基于其他表的预先计算的数据。（考虑到我必须处理的数据量，即时计算的计算成本太高。）我将在添加源数据时逐步生成。（我在正常使用中永远不需要UPDATE它；部分可能会被删除并重新生成。）该表将相当大。目前大约有 5000 万行，并且每年都会增长。

大多数针对该表的查询将被外键 ID 列过滤掉。因此，如果该 ID 的所有行都分组到相同的页面中，它们的性能会更好。我可以通过创建索引并CLUSTER定期调用来保证磁盘上的这种排序，但这显然不太理想，因为它需要某种计划任务、协调使用情况和其他计划任务等。

但是，由于我生成的数据块与我想要的外键相关CLUSTER，所以我可以轻松地在命令中添加一个ORDER BY子句：INSERT

INSERT INTO big_table (source_table1_id,a,b,c)
SELECT
   source_table1_id,
   5 /* some formula */,
   /* ... */
FROM source_table1
JOIN source_table2 ON ...
...
WHERE ... /* some condition indicating what needs to be generated */
ORDER BY source_table1_id

这会影响磁盘存储顺序，将行分组到接近最小页数吗？如果是这样，是否还有其他进程可能会在以后弄乱磁盘上的顺序？

我目前正在使用 PostgreSQL 9.3，但我也想了解更新版本以及升级。

我需要对表中的所有行执行简单更新。该表有 40-50 百万行。在结果期间删除索引和约束会UPDATE导致性能大幅提升。

但是自动真空呢？autovacuum 可以启动 aVACUUM或ANALYZE在 a 中间启动UPDATE吗？如果是这样，那将是消耗机器资源的无用工作。我可以在之前在桌子上禁用它，然后在UPDATE之后重新启用它：

ALTER TABLE my_table
SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);
-- Drop constraints, drop indexes, and disable unnecessary triggers

UPDATE my_table SET ....;

-- Restore constraints, indexes, and triggers
ALTER TABLE my_table
SET (autovacuum_enabled = true, toast.autovacuum_enabled = true);

如果我在第一次之后不提交，这是否有效ALTER？

另外，如果我在 期间禁用它，它会在更新后UPDATE触发，还是会因为在更新期间被禁用而忽略这些更新？（我怀疑它会运行，但我宁愿确定。）

我现在正在使用 PG 9.3，但应该很快就会升级。因此，任何提及新版本中的更改都值得赞赏。

最后有一个脚本将完全创建模式并用示例数据填充它。

架构

考虑这两个表：

多边形表：

CREATE TABLE my_polygon (
    my_polygon_id SERIAL PRIMARY KEY,
    common_id INTEGER NOT NULL,
    value1 NUMERIC NOT NULL,
    value2 NUMERIC NOT NULL,
    value3 NUMERIC NOT NULL,
    geom GEOMETRY(Polygon) NOT NULL
)
;

CREATE INDEX ON my_polygon (common_id);
CREATE INDEX ON my_polygon USING GIST (common_id, geom);

多边形内包含的点表：

CREATE TABLE my_point (
    my_point_id SERIAL PRIMARY KEY,
    common_id INTEGER NOT NULL,
    pointvalue NUMERIC NOT NULL,
    geom GEOMETRY(Point) NOT NULL
);

CREATE INDEX ON my_point (common_id);
CREATE INDEX ON my_point USING GIST (common_id, geom);

我使用几何的事实与这里的问题并不严格相关。但是，我认为这使我尝试做的事情的动机更加清晰。

问题查询

问题是多边形之间有非常微小的、微不足道的重叠。（尝试清理它们确实不是一种选择。重叠来自生成它们时的某种浮点错误，尽可能接近。）但是有些点可能落在这些微小的重叠内，当我JOIN他们时会产生两行基于遏制。但实际上，每个点应该只与一个多边形相关联。当一个确实属于其中两个时，它最终与哪一个关联并不重要，因此可以让查询只选择一个，如下所示：

SELECT DISTINCT ON (my_point.my_point_id)
    my_polygon.*,
    my_point.my_point_id,
    my_point.pointvalue,
    my_point.geom AS pointgeom
FROM my_polygon
JOIN my_point ON my_point.common_id = my_polygon.common_id AND ST_Contains(my_polygon.geom, my_point.geom)
WHERE my_polygon.common_id = 1
ORDER BY my_point.my_point_id, my_polygon.my_polygon_id

和上面的查询一样，我通常想SELECT基于common_id. 此查询执行良好。它的查询计划如下所示：

但是，这是我在许多不同查询中需要的逻辑，所以我想把它放在一个视图中。结果是，就查询规划器而言，查询看起来像这样：

SELECT *
FROM (
    SELECT DISTINCT ON (my_point.my_point_id)
        my_polygon.*,
        my_point.my_point_id,
        my_point.pointvalue,
        my_point.geom AS pointgeom
    FROM my_polygon
    JOIN my_point ON my_point.common_id = my_polygon.common_id AND ST_Contains(my_polygon.geom, my_point.geom)
    ORDER BY my_point.my_point_id, my_polygon.my_polygon_id
) point_with_polygon
WHERE common_id = 1

结果是现在 PostgreSQL在执行common_id 之后进行过滤DISTINCT ON，这意味着它必须对JOIN两个表的整体进行过滤。这是它的查询计划：

如何允许 PostgreSQL 将过滤器下推到查询的早期部分，并且仍然将一般查询放在视图中？

我现在卡在 PG 9.3 上，但升级到 9.5 可能是一种选择。

架构和示例数据脚本

需要 PostGIS。（这就是为什么没有 SQL Fiddle 的原因。）

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- DROP FUNCTION ST_GeneratePoints(geometry, numeric);
DO $doblock$
BEGIN
    IF NOT EXISTS(SELECT * FROM pg_proc WHERE UPPER(proname) = UPPER('ST_GeneratePoints')) THEN
        -- Create naive ST_GeneratePoints if version of PostGIS is not new enough
        CREATE FUNCTION ST_GeneratePoints(g geometry, npoints numeric)
            RETURNS geometry
            VOLATILE
            RETURNS NULL ON NULL INPUT
            LANGUAGE plpgsql
            AS $$
            DECLARE
                num_to_generate INTEGER := npoints::INTEGER;
                adjustment CONSTANT FLOAT := 0.00000000001;
                x_min FLOAT := ST_XMin(g) + adjustment;
                x_max FLOAT := ST_XMax(g) - adjustment;
                y_min FLOAT := ST_YMin(g) + adjustment;
                y_max FLOAT := ST_YMax(g) - adjustment;
                temp_result GEOMETRY[];
                result_array GEOMETRY[] := ARRAY[]::GEOMETRY[];
            BEGIN
                IF ST_IsEmpty(g) THEN
                    RAISE EXCEPTION 'Cannot generate points inside an empty geometry';
                END IF;

                IF ST_Dimension(g) < 2 THEN
                    RAISE EXCEPTION 'Only polygons supported';
                END IF;

                -- Reduce number of loops to reduce slow array_cat calls
                WHILE num_to_generate > 0 LOOP
                    SELECT ARRAY_AGG(contained.point) INTO temp_result
                    FROM (
                        SELECT point
                        FROM (
                            SELECT ST_MakePoint(
                                x_min + random() * (x_max - x_min),
                                y_min + random() * (y_max - y_min)
                            ) point
                            -- Generate extras to reduce number of loops
                            --
                            -- Each point has a probability of  ST_Area(g) / ST_Area(ST_Envelope(g))  to fall within the polygon.
                            -- So on average, we expect ST_Area(g) / ST_Area(ST_Envelope(g)) of the points generated to fall within.
                            -- Generating  ST_Area(ST_Envelope(g)) / ST_Area(g) * num_to_generate  points means that on average, we'll
                            -- get
                            --
                            --     ST_Area(g) / ST_Area(ST_Envelope(g)) * ST_Area(ST_Envelope(g)) / ST_Area(g) * num_to_generate
                            --      = num_to_generate
                            --
                            -- points within the polygon. (Notice the numerators and denominators cancel out.) This means we'll 
                            -- only run one loop about half the time without generating an excessive number of points.
                            --
                            -- Generate at least 20 to avoid a lot of loops for small numbers, though.
                            FROM generate_series(1, GREATEST(20, CEIL(ST_Area(ST_Envelope(g)) / ST_Area(g) * num_to_generate)::INTEGER))
                        ) candidate
                        WHERE ST_Contains(g, candidate.point)
                        -- Filter out extras if we have too many matches
                        LIMIT num_to_generate
                    ) contained
                    ;
                    IF ARRAY_LENGTH(temp_result, 1) > 0 THEN
                        result_array := array_cat(result_array, temp_result);
                        num_to_generate := npoints - COALESCE(ARRAY_LENGTH(result_array, 1), 0);
                    END IF;
                END LOOP;
                RETURN (SELECT ST_Union(point) FROM UNNEST(result_array) result (point));
            END;
            $$;
        RAISE NOTICE 'Created ST_GeneratePoints';
    ELSE
        RAISE NOTICE 'ST_GeneratePoints exists';
    END IF;
END
$doblock$
;

DROP TABLE IF EXISTS my_polygon;

CREATE TABLE my_polygon (
    my_polygon_id SERIAL PRIMARY KEY,
    common_id INTEGER NOT NULL,
    value1 NUMERIC NOT NULL,
    value2 NUMERIC NOT NULL,
    value3 NUMERIC NOT NULL,
    geom GEOMETRY(Polygon) NOT NULL
)
;

CREATE INDEX ON my_polygon (common_id);
CREATE INDEX ON my_polygon USING GIST (common_id, geom);


WITH common AS (
    SELECT
        common_id,
        random() * 5000 AS common_x_translate,
        random() * 5000 AS common_y_translate
    FROM (
        SELECT TRUNC(random() * 1000) + 1 AS common_id
        FROM generate_series(1, 100)
        UNION 
        SELECT 1
    ) a
),
geom_set_with_small_overlaps AS (
    SELECT
        ST_MakeEnvelope(
            x.translate, 
            y.translate, 
            x.translate + 1.1, 
            y.translate + 1.1
        ) AS geom
    FROM
        generate_series(0, 9) x (translate),
        generate_series(0, 9) y (translate)
)
INSERT INTO my_polygon (common_id, value1, value2, value3, geom)
SELECT
    common_id,
    random() * 100,
    random() * 100,
    random() * 100,
    ST_Translate(geom, common_x_translate, common_y_translate)
FROM common, geom_set_with_small_overlaps
;

DROP TABLE IF EXISTS my_point;

CREATE TABLE my_point (
    my_point_id SERIAL PRIMARY KEY,
    common_id INTEGER NOT NULL,
    pointvalue NUMERIC NOT NULL,
    geom GEOMETRY(Point) NOT NULL
);

INSERT INTO my_point (common_id, pointvalue, geom)
SELECT
    common_id,
    random() * 100,
    (ST_Dump(ST_GeneratePoints(extent, FLOOR(5000 + random() * 15000)::NUMERIC))).geom
FROM (
    SELECT
        common_id,
        -- Small negative buffer prevents lying on the outer edge
        ST_Buffer(ST_Extent(geom), - 0.0001) AS extent
    FROM my_polygon
    GROUP BY common_id
) common
UNION ALL
SELECT
    common_id,
    random() * 100,
    (ST_Dump(ST_GeneratePoints(intersection, TRUNC(random() * 5)::NUMERIC))).geom
FROM (
    SELECT
        p1.common_id,
        p1.my_polygon_id AS id1,
        p2.my_polygon_id AS id2,
        ST_Intersection(p1.geom, p2.geom) AS intersection
    FROM my_polygon p1
    JOIN my_polygon p2 ON (
        p1.my_polygon_id < p2.my_polygon_id AND
        p1.common_id = p2.common_id AND
        ST_Intersects(p1.geom, p2.geom)
    )
) a
;

CREATE INDEX ON my_point (common_id);
CREATE INDEX ON my_point USING GIST (common_id, geom);

你可能想VACUUM ANALYZE在那之后。

以文本形式查询计划

WHERE子句内（表现良好）：

Unique  (cost=1195.74..1207.74 rows=2400 width=216)
  ->  Sort  (cost=1195.74..1201.74 rows=2400 width=216)
        Sort Key: my_point.my_point_id, my_polygon.my_polygon_id
        ->  Nested Loop  (cost=5.34..1060.99 rows=2400 width=216)
              ->  Bitmap Heap Scan on my_polygon  (cost=4.93..191.74 rows=100 width=164)
                    Recheck Cond: (common_id = 1)
                    ->  Bitmap Index Scan on my_polygon_common_id_geom_idx  (cost=0.00..4.90 rows=100 width=0)
                          Index Cond: (common_id = 1)
              ->  Index Scan using my_point_common_id_geom_idx on my_point  (cost=0.41..8.68 rows=1 width=52)
                    Index Cond: ((common_id = 1) AND (my_polygon.geom && geom))
                    Filter: _st_contains(my_polygon.geom, geom)

WHERE子句外（表现不佳）：

Subquery Scan on a  (cost=209447.85..215842.18 rows=1827 width=212)
  Filter: (a.common_id = 1)
  ->  Unique  (cost=209447.85..211274.80 rows=365390 width=212)
        ->  Sort  (cost=209447.85..210361.33 rows=365390 width=212)
              Sort Key: my_point.my_point_id, my_polygon.my_polygon_id
              ->  Nested Loop  (cost=0.41..63285.00 rows=365390 width=212)
                    ->  Seq Scan on my_polygon  (cost=0.00..338.00 rows=9800 width=164)
                    ->  Index Scan using my_point_common_id_geom_idx on my_point  (cost=0.41..6.41 rows=1 width=52)
                          Index Cond: ((common_id = my_polygon.common_id) AND (my_polygon.geom && geom))
                          Filter: _st_contains(my_polygon.geom, geom)

假设我像这样永久更改我的数据库search_path：

ALTER DATABASE my_db SET search_path TO "$user",public,other_schema;

如何将其重置为该集群上原始、未更改的数据库所具有的默认值？

这通常是"$user",public，但可以从配置文件中更改。有一种方法可以读取来自配置文件的参数值（因此我可以在最坏的情况下构建动态查询），但我没有任何运气找到任何类似的东西。

我正在尝试增加特定模式的空间配额。我正在使用以下查询来执行此操作：

ALTER USER MY_SCHEMA QUOTA "100 M" ON "USERS";

这会导致以下错误：

SQL Error: ORA-02187: invalid quota specification
02187. 00000 -  "invalid quota specification"
*Cause:    In a grant or revoke statement, the user attempted to grant
           a tablespace quota above the upper limit.
*Action:   Grant a smaller tablespace quota.

但这没有任何意义。用户已经有 50 MB 的配额，尝试将其设置为 25 MB 的查询失败并显示相同的错误消息：

ALTER USER MY_SCHEMA QUOTA "25 M" ON "USERS";

我已经确认我的表空间有足够的剩余空间。（它的数据文件可以多占用大约 2 GB 的磁盘空间。）导致此错误的原因是什么？

我在 Oracle 中有一个函数，我和我的团队都没有写过，而且被严重混淆了。但是，它有一个游标输出参数。如何找出游标包含的数字和类型？

有关我需要的示例，请考虑此游标：

DECLARE
  TEMP1 VARCHAR2(500);
  TEMP2 NUMBER;
  TEMP3 RAW(16);
  TEMP SYS_REFCURSOR;
  PROCEDURE MAKE_TEMP(TEMP_RETURN OUT SYS_REFCURSOR) IS
  BEGIN
    OPEN TEMP_RETURN FOR SELECT DUMMY, 5, HEXTORAW('111') FROM DUAL;
  END;
BEGIN
  MAKE_TEMP(TEMP);
  LOOP
    FETCH TEMP INTO TEMP1, TEMP2, TEMP3;
    EXIT WHEN (TEMP%NOTFOUND);
    DBMS_OUTPUT.PUT_LINE('1: '||TEMP1);
    DBMS_OUTPUT.PUT_LINE('2: '||TO_CHAR(TEMP2));
    DBMS_OUTPUT.PUT_LINE('3: '||RAWTOHEX(TEMP3));
  END LOOP;
  CLOSE TEMP;
END;
/

如果MAKE_TEMP是我尝试使用的函数，我需要知道它每行有 3 个值，并且这些值按顺序是 a VARCHAR2、 aNUMBER和 a RAW，这样我就可以定义变量来保存结果。

我正在使用 Oracle 11.2.0.1.0，但如果也存在更新版本的答案，我将不胜感激。

为什么我不能只看源码

如果您必须知道，该函数是 Oracle ASP.NET Membership Provider 的一部分，并且是由 Oracle 编写的。我正在尝试调试一个问题，想看看这个函数返回了什么。该函数的源代码是这样开始的：

create or replace 
FUNCTION  "ORA_ASPNET_MEM_GETALLUSERS" wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
8
9200000
1
4
0
33

如您所见，（充其量）提取有关该光标的任何信息将极其困难。

考虑下表：

ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL 
---+----------+-----------+-----------+-----
1  | 1        | 1         | (null)    | 3   
2  | 1        | 2         | (null)    | 2   
3  | 1        | 3         | (null)    | 1   
4  | 1        | 4         | 4         | 2   
5  | 1        | 5         | (null)    | 1   
6  | 2        | 1         | (null)    | 4   
7  | 2        | 2         | 2         | 3   
8  | 2        | 3         | (null)    | 4   
9  | 2        | 4         | (null)    | 2   
10 | 2        | 5         | (null)    | 2   
11 | 2        | 6         | (null)    | 4   
12 | 2        | 7         | 14        | 2   
13 | 2        | 8         | (null)    | 2   

对于每一行，我需要计算VAL所有先前行的累积总和（按 排序ORDER_VAL和分组GROUP_ID），但每次NULL RESET_VAL遇到非时，我需要使用该值作为总和。接下来的行也需要建立在RESET_VAL而不是使用实际总和之上。请注意，每个组可以有多个重置值。

这是我对上表的预期结果：

ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL | CUMSUM
---+----------+-----------+-----------+-----+-------
1  | 1        | 1         | (null)    | 3   | 0
2  | 1        | 2         | (null)    | 2   | 3
3  | 1        | 3         | (null)    | 1   | 5
4  | 1        | 4         | 4         | 2   | 4
5  | 1        | 5         | (null)    | 1   | 6
6  | 2        | 1         | (null)    | 4   | 0
7  | 2        | 2         | 2         | 3   | 2
8  | 2        | 3         | (null)    | 4   | 5
9  | 2        | 4         | (null)    | 2   | 9
10 | 2        | 5         | (null)    | 2   | 11
11 | 2        | 6         | (null)    | 4   | 13
12 | 2        | 7         | 14        | 2   | 14
13 | 2        | 8         | (null)    | 2   | 16

如果不是重置值，我可以使用窗口查询：

SELECT temp.*,
       COALESCE(SUM(val) OVER (PARTITION BY group_id ORDER BY order_val ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
                0) AS cumsum
FROM temp;

上面的 SQLFiddle

我最初错误地认为我可以放在RESET_VAL的开头COALESCE，但这不起作用，因为它不会重置后续行的值。

我也尝试了这个解决方案，但它只会重置为零，而不是列中的值。将其调整为这样做证明是不平凡的，因为该值必须传播到所有后续行。

递归查询似乎很自然，但我还没有弄清楚如何做到这一点。

我可能应该提一下，我实际上必须处理的表比上面的示例要大得多（数十万到几百万行），所以请说明是否存在任何答案的性能缺陷。

考虑下表：

T_ID | T_START | T_END
-----+---------+------
   1 | 0.25    | 0.5
   2 | 0.8     | 1
   3 | 0.4     | 0.6
   4 | 0.2     | 0.3
   5 | 0.7     | 0.8

T_ID是独特的。每行代表一个连续的数字范围，是 0 到 1 的子集。T_START小于T_END。

我需要确定不包含在 0 和 1 之间的任何范围。请注意，某些范围确实重叠。端点的排他性与我的用例无关；我只需要确定差距的端点是什么。（因此，不考虑单点差距。）

对于这个特定的数据集，我希望结果是

GAP_START | GAP_END
----------+--------
0         | 0.2
0.6       | 0.7

实际数据集很大，并且将聚合到其他一些数据（数十万行，每个聚合组可能有 100 行），因此性能很重要。（不过，欢迎提供性能不佳但可能会改进的答案。）

我考虑尝试首先确定覆盖范围是什么，然后尝试反转它，但我什至无法弄清楚如何计算覆盖范围。简单GROUP BY是不够的，因为我们有重叠范围的链，它们将合并到一个范围中，即使并非所有范围都相互重叠。我认为递归查询可能会有所帮助，但我还没有弄清楚它的逻辑。

我用这个示例数据集创建了一个 SQLFiddle 。

可悲的是（而且毫无成效），我不能随意修改底层表示。

Oracle 允许使用几种不同的语法来指定在哪里可以找到您的数据库服务。我最常看到的（在我专业开发软件的短短几年里）看起来像这样：

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservice)))

我不认为这种语法有任何特定的名称，但如果您使用图形工具修改它，它就是 Oracle 在其 TNS 文件中生成的。但 Oracle 还允许使用以下更紧凑的语法，至少使用 SQL*Plus 和 application diver 之类的工具：

myserver:1521/myservice

这称为Easy Connect，我读到它是由 Oracle 10g 引入的。这使得它大约有 10 年的历史。我不确定这是否可以在 TNS 文件中使用，尽管它很简短，但我认为没有太多必要将它放在 TNS 文件中。在我看来，它的好处在于：您可以直接将它用于您的应用程序，而无需维护 TNS 文件。

然而，从我所做的工作和与我合作过的客户来看，它的使用似乎并不常见。我什至知道它的唯一原因是因为 ESRI 开始推荐使用它在较新版本的 ArcGIS 中建立连接。这让我想知道为什么它不常见，那么使用 Easy Connect 是否有某种我遗漏的缺点？

（如果有人可以添加更多标签，我将不胜感激。我想不出/找不到更多有意义的东西。）

我正在使用 ESRI 的 arcpy 模块，我需要指定一个WHERE子句作为此函数的参数。由于结果最终将用于从数据库中检索数据，WHERE子句的文本有时会直接传递到数据库，但遗憾的是，ESRI 没有给我任何参数化查询的选项。由于我想防止潜在的 SQL 注入，我需要找到一些替代方法来保护我的数据库。

想到的一个选项是限制输入，如果不符合，则在此函数调用之前抛出错误。在我的例子中，呼叫者只需要 ASCII 字母数字字符。列中不允许使用其他类型的字符进行过滤。将用户限制为仅字母数字字符是否足以防止 SQL 注入，特别是因为文本必须作为字符串引用？

这一次，我发现自己在想出一个好的问题标题方面完全不知所措。

考虑以下三个表。每个都有额外的列，但只提到与表之间的关系相关的列：

• 表a有一个主键a_id
• 表b有一个主键b_id和一个a_id外键列a（一对多关系）
• 表reference有一个主键ref_id

reference是被引用以证实其他 2 个表格中包含的信息的来源列表。因此，我需要表格将它们关联在一起。和都a与b具有多对多关系reference，但还有一个额外的复杂问题：脚注编号。a这个脚注编号对于each and pair必须是唯一的reference，但只有当ab和same之间reference存在关系时才需要它。（当然，那b必须与同一个有关a。）到目前为止，我想出的表示这种关系的最好方法是一对表：

bridge_a_reference:

• 列：a_id, ref_id, footnote_num（可为空）
• 主键：a_id,ref_id
• 外键：a_id到表a；ref_id表reference

bridge_b_reference:

• 列：b_id, a_id,ref_id
• 主键：b_id,ref_id
• 外键：b_id,a_id到表b; a_id,ref_id到表bridge_a_reference

这样可以保证数据的一致性。如果和之间存在对应关系并且与正确相关，则只有b和之间存在关系。它还确保脚注编号对于所有/对都是一致的。但是，它是多余的，第二次存储和之间的关系（尽管至少强制保持一致），并且不能确保/关系需要脚注编号。referenceareferencebaareferenceabbreference

我试过几次重新排列我脑海中的表格，但我能想到的其他一切都允许某种不一致（b对于reference没有对应a关系reference或/b对a的关系或不一致的脚注编号的关系。）我怎样才能更好构建我的桥接表以消除冗余并确保脚注要求？有可能吗？areference

PostgreSQL 有CLUSTER命令来对磁盘上的行进行物理分组。通过对经常一起访问的“相邻”行（没有更好的术语）的信息进行分组，性能得到提高，因为在给定查询中需要读取的磁盘块更少。Oracle 有类似的东西吗？如果有这样的选项，它甚至会有助于在几乎从不更新的大表上提高性能吗？

我有一个B带有外键的表 table A。我想DELETE在表中的一些行B，我也想DELETE在表中他们的父行A。但是，删除条件基于 table B。A不能先删除表中的行，因为表中的引用B限制了删除，但我还必须A从行中获取要删除的键B。

这是一个带有示例表结构的 SQLFiddle： http ://sqlfiddle.com/#!4/f156c/4/0 。

我的第一个倾向是尝试通过将键SELECT从 from中保存B到变量中，然后将其用于DELETEfrom A。

DECLARE
  A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
  SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
  FROM (SELECT A_ID
        FROM B
        WHERE LENGTH(B_DATA) > 4
       );

  DELETE FROM B
  WHERE LENGTH(B_DATA) > 4;

  DELETE FROM A
  WHERE A_ID IN A_ID_TO_DELETE;
END;
/

但这只会给出一个PLS-00382: expression is of wrong type错误。错误本身来自DELETEon A; 我知道这一点，因为如果我将其注释掉，该块就会运行。

我怎样才能绕过这个expression is of wrong type错误，或者有什么方法可以解决这个问题？

Oracle 版本：Oracle 数据库 10g 企业版版本 10.2.0.1.0 - 产品

（是的，我很清楚那有多大。客户选择的数据库，而不是我们的。）

我有一个表，其中行可以相互关联，从逻辑上讲，两行之间的关系是双向的（基本上是无方向的）。（如果您想知道，是的，这确实应该是一张表。这是完全相同的逻辑实体/类型的两件事。）我可以想到几种方法来表示这一点：

1. 存储关系及其反向
2. 以一种方式存储关系，限制数据库以另一种方式存储它，并且有两个 FK 顺序相反的索引（一个索引是 PK 索引）
3. 使用两个索引以一种方式存储关系并允许插入第二个索引（听起来有点恶心，但是嘿，完整性）
4. 创建某种分组表，并在原始表上对其进行 FK。（提出了很多问题。分组表只有一个数字；为什么还要有表？使 FK 为 NULLable 或有与单行关联的组？）

这些方式有哪些主要优点和缺点，当然，有没有我没有想到的方式？

这是一个可以使用的 SQLFiddle：http ://sqlfiddle.com/#!12/7ee1a/1/0 。（碰巧是 PostgreSQL，因为那是我正在使用的，但我认为这个问题不是 PostgreSQL 特有的。）它目前存储关系和它的反向只是作为一个例子。