PL/SQL의 숨겨진 기능

PL/SQL의 숨겨진 기능

현재 상태로는 이 질문은 우리의 Q&A 형식에 적합하지 않습니다.우리는 답변이 사실, 참고 자료 또는 전문 지식에 의해 뒷받침되기를 기대하지만, 이 질문은 토론, 논쟁, 여론 조사 또는 연장된 토론을 요청할 가능성이 높습니다.만약 이 질문이 개선될 수 있고 다시 열릴 수 있다고 느낀다면,안내를 위해 헬프 센터를 방문합니다.

마감됨11년 전에.

일련의 질문에서 PL/SQL의 알려지지 않은 기능은 무엇입니까?

편집: Oracle SQL 구문의 기능보다 PL/SQL 관련 기능을 선호합니다.그러나 PL/SQL은 대부분의 Oracle SQL 구조를 사용할 수 있기 때문에 PL/SQL에서 프로그래밍을 쉽게 하는 경우 포함될 수 있습니다.

변수를 재정의할 수 있고 익명 블록의 이름을 지정할 수 있으며 재정의된 변수를 이름으로 참조할 수 있습니다.

PROCEDURE myproc IS
   n NUMBER;
BEGIN
   n := 1;
   <<anon>>
   DECLARE
      n NUMBER;
   BEGIN
      n := 2;
      dbms_output.put_line('n=' || n);
      dbms_output.put_line('anon.n=' || anon.n);
      dbms_output.put_line('myproc.n=' || myproc.n);
   END anon;
END myproc;

정수 이외의 다른 유형으로 pl/sql 테이블을 인덱싱할 수 있습니다.이렇게 하면 코드를 훨씬 쉽게 읽을 수 있는 구조와 같은 "사전"을 만들 수 있습니다.

예:

DECLARE
  TYPE dictionary IS TABLE OF VARCHAR2(200) INDEX BY VARCHAR2(100);
  dict dictionary;
BEGIN
  dict('NAME') := 'John Doe';
  dict('CITY') := 'New York';

  dbms_output.put_line('Name:' || dict('NAME'));
END;

진짜 숨겨진 오라클 함수는 OVERPS 함수이지만 지원되지 않는 기능을 사용하는 것은 그다지 현명하지 않습니다.

select 'yes' from dual where (sysdate-5,sysdate) overlaps (sysdate-2,sysdate-1);

제가 성공적으로 사용한 거의 알려지지 않은 기능 중 하나는 변수를 사용하여 표에 삽입할 수 있는 기능입니다.%ROWTYPE예:

CREATE TABLE CUSTOMERS (
    id NUMBER,
    name VARCHAR2(100),
    birth DATE,
    death DATE
)

PROCEDURE insert_customer IS
    customer CUSTOMERS%ROWTYPE;
BEGIN
    customer.id := 45;
    customer.name := 'John Smith';
    customer.birth := TO_DATE('1978/04/03', 'YYYY/MM/DD');

    INSERT INTO CUSTOMERS VALUES customer;
END;

redo 테이블 공간을 조금 더 많이 확보하기는 하지만 데이터(특히 더 큰 테이블)를 훨씬 더 명확하게 삽입할 수 있습니다.또한 삽입할 각 열의 값을 저장하는 데 필요한 변수의 수를 줄일 수 있습니다.

절차 및 기능은 다음과 같이 정의할 수 있습니다.DECLARE블록:

DECLARE

    PROCEDURE print(text VARCHAR2) IS
    BEGIN
        DBMS_OUTPUT.put_line(text);
    END;

BEGIN

    print('Yay!');
    print('Woo hoo!');

END;

이것은 독립 실행형 스크립트를 만드는 데 유용합니다.

SAMPLE (K) 옵션을 사용하면 Oracle 테이블의 최대 K%로 구성된 샘플만 선택할 수 있다는 것을 알고 계십니까?

SELECT *
  FROM MASSIVE_TABLE SAMPLE (5);

이전 문은 MAXASS_TABLE이라는 대용량 테이블에 저장된 레코드의 최대 5%로 구성된 랜덤 집합을 검색합니다.

충분히 숨겨지지 않았을 수도 있지만, 저는 메이크 업서트(삽입 또는 업데이트)를 허용하는 병합 문을 좋아합니다.

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

Oracle의 숨겨진 기능에 대한 저의 답변은 다음과 같습니다.

이제 Apex는 모든 Oracle 데이터베이스의 일부이므로 Apex를 사용하지 않는 경우에도 다음과 같은 Apex 유틸리티 기능이 유용합니다.

SQL> declare
  2    v_array apex_application_global.vc_arr2;
  3    v_string varchar2(2000);
  4  begin
  5  
  6    -- Convert delimited string to array
  7    v_array := apex_util.string_to_table('alpha,beta,gamma,delta', ',');
  8    for i in 1..v_array.count
  9    loop
 10      dbms_output.put_line(v_array(i));
 11    end loop;
 12  
 13    -- Convert array to delimited string
 14    v_string := apex_util.table_to_string(v_array,'|');
 15    dbms_output.put_line(v_string);
 16  end;
 17  /
alpha
beta
gamma
delta
alpha|beta|gamma|delta

PL/SQL procedure successfully completed.

이 PL/SQL 절차 구성은 많이 사용합니다(Steven Feuerstein 및 Chen Shapira 덕분).캐싱에 사용되는 연관 배열이지만 모든 데이터를 미리 로드하지는 않지만 필요한 경우 데이터베이스에서 데이터를 가져와 연관 배열에 넣습니다.

create or replace
PACKAGE justonce
IS
  FUNCTION hair (code_in IN hairstyles.code%TYPE)
    RETURN hairstyles%ROWTYPE;
  TYPE hair_t IS TABLE OF hairstyles%ROWTYPE
    INDEX BY BINARY_INTEGER;
  hairs          hair_t;
END justonce;

create or replace 
PACKAGE BODY justonce
IS
  FUNCTION hair (code_in IN hairstyles.code%TYPE) RETURN hairstyles%ROWTYPE
  IS
    return_value   hairstyles%ROWTYPE;
    FUNCTION hair_from_database RETURN hairstyles%ROWTYPE
    IS
      CURSOR hair_cur IS
      SELECT * FROM hairstyles WHERE code = code_in;
    BEGIN
      OPEN hair_cur;
      FETCH hair_cur INTO return_value;
      CLOSE hair_cur;
      RETURN return_value;
    END hair_from_database;
  BEGIN
    IF NOT (hairs.exists(code_in))
    THEN
      dbms_output.put_line('Get record from database');
      hairs (code_in) := hair_from_database;
    END IF;
    RETURN hairs (code_in);
  END hair;
END justonce;

테스트:

declare
    h hairstyles%ROWTYPE;
begin
   for i in 1000..1004
   loop
      h := justonce.hair(i);
      dbms_output.put_line(h.description);
   end loop;
   for i in 1000..1004
   loop
      h := justonce.hair(i);
      dbms_output.put_line(h.description||' '||h.price);
   end loop;

end;
/

Get record from database
CREWCUT
Get record from database
BOB
Get record from database
SHAG
Get record from database
BOUFFANT
Get record from database
PAGEBOY
CREWCUT 10
BOB 20
SHAG 21
BOUFFANT 11
PAGEBOY 44

1. 문서화되지 않은 함수: dbms_system.ksdwrt(경고/추적 파일에 기록)
2. DBMS_SQL 패키지(사용 예로 이 질문 참조)
3. AUTHID Current_USER 절
4. 조건부 컴파일

동적 PL/SQL은 보기 흉하지만 몇 가지 흥미로운 작업을 수행할 수 있습니다.예를 들어, 이름을 변수로 취급할 수 있습니다. 이전에 배열과 같은 %rowtype 변수를 이동하고 주어진 테이블 이름에 대해 각 열의 기본값으로 단일 행을 선택하는 커서를 반환하는 함수를 만드는 데 사용했습니다.두 가지 모두 정규화되지 않은 테이블에 대한 유용한 해결 방법입니다.

반복에 레이블을 추가한 다음 이 레이블로 이동하여 계속을 시뮬레이션할 수 있습니다.

declare
   i integer;
begin
   i := 0;

   <<My_Small_Loop>>loop

      i := i + 1;
      if i <= 3 then goto My_Small_Loop; -- => means continue
      end if;

      exit;

   end loop;
end;

언급URL : https://stackoverflow.com/questions/1031485/hidden-features-of-pl-sql