오라클 저장 프로시저 임시 테이블

Oracle 데이터베이스에서 저장 프로시저는 입력 매개변수를 수신하고 출력 값을 반환할 수 있는 미리 정의된 SQL 문 집합입니다. 이는 일반적으로 Oracle 데이터베이스의 성능과 유지 관리성을 향상시키고 비즈니스 논리와의 긴밀한 통합을 달성하기 위해 특정 작업을 수행하는 데 사용됩니다.

임시 테이블은 쿼리 과정에서 사용되는 임시 저장 공간입니다. Oracle 데이터베이스에서는 CREATE GLOBAL TEMPORARY TABLE 문을 사용하여 임시 테이블을 생성할 수 있습니다. 이러한 테이블은 세션이 끝나면 자동으로 삭제되며 단일 세션 내의 여러 쿼리에 대한 중간 결과로 사용될 수 있습니다.

지난 몇 년 동안 저장 프로시저와 임시 테이블의 조합은 효율적이고 복잡한 쿼리를 실행하기 위한 모범 사례 중 하나가 되었습니다. 예를 들어 대규모 데이터 웨어하우스에서는 저장 프로시저와 임시 테이블을 사용하여 복잡한 ETL(추출, 변환 및 로드) 작업을 구현할 수 있으며 성능 및 유지 관리 측면에서 좋은 성능을 갖습니다.

이 기사에서는 Oracle 데이터베이스에서 임시 테이블을 생성하고 저장 프로시저와 함께 사용하는 방법을 소개합니다.

임시 테이블 생성

Oracle 데이터베이스에서 임시 테이블을 생성하려면 다음 CREATE GLOBAL TEMPORARY TABLE 문을 사용할 수 있습니다.

CREATE GLOBAL TEMPORARY TABLE temp_table_name
(
  column_name1  datatype1,
  column_name2  datatype2,
  column_name3  datatype3,
  ...
)
ON COMMIT DELETE ROWS;

이 문에서:

• temp_table_name은 원하는 것입니다. create 임시 테이블의 이름입니다.
temp_table_name是你要创建的临时表的名称。
• column_name是表中的列名，可以定义多个列。
• datatype是每个列的数据类型。

注意，在这个语句中，ON COMMIT DELETE ROWS是指在提交事务或者会话关闭时删除临时表中的所有行。这可以确保每个会话使用的是空表，并且可以在会话结束时释放系统资源。

使用临时表

一旦你创建了临时表，你就可以在存储过程中使用它。例如，如果你需要在存储过程中定义一个中间结果集，你可以使用以下步骤：

1. 在存储过程中定义临时表：

CREATE OR REPLACE PROCEDURE my_procedure IS
BEGIN
  CREATE GLOBAL TEMPORARY TABLE temp_table
  (
    col1  NUMBER,
    col2  VARCHAR2(20)
  )
  ON COMMIT DELETE ROWS;
END my_procedure;

在这个例子中，我在存储过程中定义了一个名为temp_table的临时表，包括两个列col1和col2，并使用ON COMMIT DELETE ROWS选项来定义它。

2. 在存储过程中向临时表中插入数据：

CREATE OR REPLACE PROCEDURE my_procedure IS
BEGIN
  CREATE GLOBAL TEMPORARY TABLE temp_table
  (
    col1  NUMBER,
    col2  VARCHAR2(20)
  )
  ON COMMIT DELETE ROWS;
  
  INSERT INTO temp_table (col1, col2)
  VALUES (1, 'Hello');
  
  INSERT INTO temp_table (col1, col2)
  VALUES (2, 'World');
  
  COMMIT;
END my_procedure;

在这个例子中，我向临时表中插入了两行数据。注意，这里的COMMIT语句是必需的，因为Oracle数据库中的临时表只在事务提交时可用。

3. 在存储过程中查询临时表：

CREATE OR REPLACE PROCEDURE my_procedure IS
  cur1 SYS_REFCURSOR;
BEGIN
  CREATE GLOBAL TEMPORARY TABLE temp_table
  (
    col1  NUMBER,
    col2  VARCHAR2(20)
  )
  ON COMMIT DELETE ROWS;
  
  INSERT INTO temp_table (col1, col2)
  VALUES (1, 'Hello');
  
  INSERT INTO temp_table (col1, col2)
  VALUES (2, 'World');
  
  COMMIT;
  
  OPEN cur1 FOR
  SELECT *
  FROM temp_table;
  
  -- Use the result set
  -- ...
END my_procedure;

在这个例子中，我使用了SYS_REFCURSOR数据类型来声明一个结果集指针。然后，我在存储过程中打开了一个游标，使用SELECTcolumn_name은 테이블의 열 이름이며 여러 열을 정의할 수 있습니다.

datatype은 각 열의 데이터 유형입니다.

이 문에서 ON COMMIT DELETE ROWS는 트랜잭션이 커밋되거나 세션이 닫힐 때 임시 테이블의 모든 행을 삭제하는 것을 의미합니다. 이렇게 하면 각 세션이 빈 테이블을 사용하고 세션이 끝날 때 시스템 리소스가 해제됩니다.

임시 테이블 사용

🎜임시 테이블을 생성하면 저장 프로시저에서 사용할 수 있습니다. 예를 들어 저장 프로시저에서 중간 결과 집합을 정의해야 하는 경우 다음 단계를 사용할 수 있습니다. 🎜

🎜저장 프로시저에서 임시 테이블 정의:

rrreee🎜여기서 예, I temp_table이라는 임시 테이블이 두 개의 열 col1 및 col2를 포함하여 저장 프로시저에 정의되어 있으며 ON COMMIT를 사용합니다. DELETE ROWS 옵션을 사용하여 정의합니다. 🎜

🎜저장 프로시저의 임시 테이블에 데이터 삽입:

rrreee🎜이 예에서는 임시 테이블에 두 개의 데이터 행을 삽입했습니다. Oracle 데이터베이스의 임시 테이블은 트랜잭션이 커밋될 때만 사용할 수 있으므로 여기서 COMMIT 문이 필요합니다. 🎜

🎜저장 프로시저에서 임시 테이블 쿼리:

rrreee🎜이 예에서는 SYS_REFCURSOR 데이터 유형을 사용하여 결과를 선언했습니다. 포인터를 설정합니다. 그런 다음 저장 프로시저에서 커서를 열고 SELECT 문을 사용하여 임시 테이블에서 데이터를 검색한 다음 저장 프로시저를 호출한 프로그램에 결과 집합을 반환했습니다. 그런 다음 다른 커서처럼 결과 집합을 사용할 수 있습니다. 🎜🎜결론🎜🎜Oracle 데이터베이스에서는 저장 프로시저와 임시 테이블이 복잡한 쿼리 실행을 위한 모범 사례가 되었습니다. 저장 프로시저와 함께 임시 테이블을 사용하면 쿼리 성능과 유지 관리 가능성이 크게 향상되고 비즈니스 논리와 긴밀하게 통합될 수 있습니다. 실제 응용 프로그램에서는 임시 테이블과 저장 프로시저를 신중하게 디자인하고 이들이 사용자의 요구 사항을 충족할 수 있는지 확인하는 것이 좋습니다. 🎜

위 내용은 오라클 저장 프로시저 임시 테이블의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!