Oracle 中临时表的替代方案

发布于 12-07 02:48 字数 212 浏览 0 评论 0原文

  1. 在存储过程中创建一个临时表,例如“#Temp”。
  2. 使用 select 语句将值插入“Temp”表,例如。插入#Temp 从员工中选择*。
  3. 现在从此临时表中提取数据,例如。从 #Temp 中选择 *,其中 #Temp.Id = @id &很快。

如何在 Oracle 存储过程中中执行此操作?

  1. Create a temporary table inside a stored procedure, say '#Temp'.
  2. Insert values into 'Temp' table using a select statement, eg. Insert Into #Temp Select * from Employees.
  3. Now extract data from this Temp table, eg. Select * from #Temp where #Temp.Id = @id & so on.

How to do this in Oracle inside a stored procedure?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

找回味觉2024-12-14 02:48:03

您想要解决的业务问题是什么?在 Oracle 中需要使用临时表的情况非常罕见。为什么不简单地

SELECT *
  FROM employees
 WHERE id = p_id_passed_in;

在其他数据库中,您经常创建临时表,因为读取器会阻止写入器,因此您希望创建数据的单独副本以避免阻塞任何其他会话。然而,在 Oracle 中,读取器永远不会阻止写入器,因此通常不需要保存数据的单独副本。

在其他数据库中,您创建临时表是因为您不想进行脏读。然而,Oracle 不允许脏读。多版本读取一致性意味着 Oracle 将始终向您显示查询启动时(或者如果您将事务隔离级别设置为可序列化,则事务启动时)时存在的数据。所以不需要创建临时表来避免脏读。

如果您确实想要在 Oracle 中使用临时表,则不会动态创建该表。在创建存储过程之前,您将创建一个全局临时表。表结构对所有会话都可见,但数据仅对插入它的会话可见。您将填充过程中的临时表,然后查询该表。就像我所说的那样

CREATE GLOBAL TEMPORARY TABLE temp_emp (
  empno number,
  ename varchar2(10),
  job   varchar2(9),
  mgr   number,
  sal   number(7,2)
)
ON COMMIT PRESERVE ROWS;

CREATE OR REPLACE PROCEDURE populate_temp_emp
AS
BEGIN
  INSERT INTO temp_emp( empno,
                        ename,
                        job,
                        mgr,
                        sal )
    SELECT empno, 
           ename,
           job,
           mgr,
           sal
      FROM emp;
END;
/

SQL> begin
  2    populate_temp_emp;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select *
  2    from temp_emp;

     EMPNO ENAME      JOB              MGR        SAL
---------- ---------- --------- ---------- ----------
      7623 PAV        Dev
      7369 smith      CLERK           7902        800
      7499 ALLEN      SALESMAN        7698       1600
      7521 WARD       SALESMAN        7698       1250
      7566 JONES      MANAGER         7839       2975
      7654 MARTIN     SALESMAN        7698       1250
      7698 BLAKE      MANAGER         7839       2850
      7782 CLARK      MANAGER         7839       2450
      7788 SCOTT      ANALYST         7566       3000
      7839 KING       PRESIDENT                  5000
      7844 TURNER     SALESMAN        7698       1500
      7876 ADAMS      CLERK           7788       1110
      7900 SM0        CLERK           7698        950
      7902 FORD       ANALYST         7566       3000
      7934 MILLER     CLERK           7782       1300
      1234 BAR

16 rows selected.

,但是,在 Oracle 中实际想要使用临时表是非常不寻常的。

What is the business problem you are trying to solve? It is exceptionally rare that you need to use temporary tables in Oracle. Why wouldn't you simply

SELECT *
  FROM employees
 WHERE id = p_id_passed_in;

In other databases, you often create temporary tables because readers block writers so you want to create a separate copy of the data in order to avoid blocking any other sessions. In Oracle, however, readers never block writers, so there is generally no need to save off a separate copy of the data.

In other databases, you create temporary tables because you don't want to do dirty reads. Oracle, however, does not allow dirty reads. Multi-version read consistency means that Oracle will always show you the data as it existed when the query was started (or when the transaction started if you've set a transaction isolation level of serializable). So there is no need to create a temporary table to avoid dirty reads.

If you really wanted to use temporary tables in Oracle, you would not create the table dynamically. You would create a global temporary table before you created the stored procedure. The table structure would be visible to all sessions but the data would be visible only to the session that inserted it. You would populate the temporary table in the procedure and then query the table. Something like

CREATE GLOBAL TEMPORARY TABLE temp_emp (
  empno number,
  ename varchar2(10),
  job   varchar2(9),
  mgr   number,
  sal   number(7,2)
)
ON COMMIT PRESERVE ROWS;

CREATE OR REPLACE PROCEDURE populate_temp_emp
AS
BEGIN
  INSERT INTO temp_emp( empno,
                        ename,
                        job,
                        mgr,
                        sal )
    SELECT empno, 
           ename,
           job,
           mgr,
           sal
      FROM emp;
END;
/

SQL> begin
  2    populate_temp_emp;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select *
  2    from temp_emp;

     EMPNO ENAME      JOB              MGR        SAL
---------- ---------- --------- ---------- ----------
      7623 PAV        Dev
      7369 smith      CLERK           7902        800
      7499 ALLEN      SALESMAN        7698       1600
      7521 WARD       SALESMAN        7698       1250
      7566 JONES      MANAGER         7839       2975
      7654 MARTIN     SALESMAN        7698       1250
      7698 BLAKE      MANAGER         7839       2850
      7782 CLARK      MANAGER         7839       2450
      7788 SCOTT      ANALYST         7566       3000
      7839 KING       PRESIDENT                  5000
      7844 TURNER     SALESMAN        7698       1500
      7876 ADAMS      CLERK           7788       1110
      7900 SM0        CLERK           7698        950
      7902 FORD       ANALYST         7566       3000
      7934 MILLER     CLERK           7782       1300
      1234 BAR

16 rows selected.

As I said, though, it would be very unusual in Oracle to actually want to use a temporary table.

情深如许2024-12-14 02:48:03

创建全局临时表。

CREATE GLOBAL TEMPORARY TABLE <your_table>
ON COMMIT PRESERVE ROWS   # If needed.  Depends on your needs.
AS SELECT <your_select_query>;

然后,您可以在手术期间根据需要从表中进行选择。

http://www.oracle-base.com/articles/8i/TemporaryTables.php < /一>

<一href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15826034070548" rel="nofollow">http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15826034070548

Create a global temporary table.

CREATE GLOBAL TEMPORARY TABLE <your_table>
ON COMMIT PRESERVE ROWS   # If needed.  Depends on your needs.
AS SELECT <your_select_query>;

You can then select from the table as needed for the duration of your procedure.

http://www.oracle-base.com/articles/8i/TemporaryTables.php

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15826034070548

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文