oracle PL/SQL 的隐藏特性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1031485/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hidden features of PL/SQL
提问by Adam Paynter
In light of the "Hidden features of..."series of questions, what little-known features of PL/SQL have become useful to you?
根据“...的隐藏特性”系列问题，PL/SQL 哪些鲜为人知的特性对您有用？
Edit:Features specific to PL/SQL are preferred over features of Oracle's SQL syntax. However, because PL/SQL can use most of Oracle's SQL constructs, they may be included if they make programming in PL/SQL easier.
编辑：特定于 PL/SQL 的功能优于 Oracle 的 SQL 语法的功能。但是，因为 PL/SQL 可以使用大多数 Oracle 的 SQL 构造，如果它们使 PL/SQL 中的编程更容易，则可能会包含它们。
采纳答案by Jeffrey Kemp
You can override variables, you can name anonymous blocks, and you can still refer to the overridden variables by name:
您可以覆盖变量，可以命名匿名块，并且仍然可以通过名称引用被覆盖的变量：
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;
回答by diederikh
You can index pl/sql tables by other types besides integers. This way you can create "dictionary" like structures, which can make your code much easier to read:
除了整数，您还可以按其他类型索引 pl/sql 表。通过这种方式，您可以创建类似“字典”的结构，这可以使您的代码更易于阅读：
Example:
例子：
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;
回答by Arno Conradie
The truly hidden oracle function is the OVERLAPS function, but it is probably not very wise to use any unsupported features.
真正隐藏的 oracle 功能是 OVERLAPS 功能，但使用任何不受支持的功能可能不是很明智。
select 'yes' from dual where (sysdate-5,sysdate) overlaps (sysdate-2,sysdate-1);
回答by Adam Paynter
One little-known feature I have had great success with is the ability to insert into a table using a variable declared as its %ROWTYPE. For example:
我取得了巨大成功的一个鲜为人知的功能是能够使用声明为其%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;
Although it chews up a bit more redo tablespace, it certainly makes inserting data (especially into larger tables) much clearer. It also avoids the multitude of variables needed to store each column's value you wish to insert.
尽管它占用了更多的重做表空间，但它确实使插入数据（尤其是插入更大的表）更加清晰。它还避免了存储您希望插入的每列值所需的大量变量。
回答by Adam Paynter
Procedures and functions may be defined within DECLAREblocks:
程序和功能可以在DECLARE块内定义：
DECLARE
PROCEDURE print(text VARCHAR2) IS
BEGIN
DBMS_OUTPUT.put_line(text);
END;
BEGIN
print('Yay!');
print('Woo hoo!');
END;
This is handy for creating stand-alone scripts.
这对于创建独立脚本非常方便。
回答by UltraCommit
Do you know that with the option SAMPLE (K) you can SELECT only a sample composed by up to K percent of an Oracle table?
您是否知道使用选项 SAMPLE (K) 只能选择由最多 K% 的 Oracle 表组成的样本？
SELECT *
FROM MASSIVE_TABLE SAMPLE (5);
The previous statement retrieves a randomic set composed by up to 5% of the records stored in the massive table called MASSIVE_TABLE.
前面的语句检索由存储在名为 MASSIVE_TABLE 的海量表中最多 5% 的记录组成的随机集。
回答by Jonathan
Maybe not hidden enough , but I love the Mergestatement that allow make upserts (insert or update)
也许隐藏得不够，但我喜欢允许更新插入（插入或更新）的Merge语句
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>];
回答by Tony Andrews
My answer to Hidden Features in Oracleis relevant here:
我对Oracle 中的隐藏功能的回答与此处相关：
Since Apex is now part of every Oracle database, these Apex utility functions are useful even if you aren't using Apex:
由于 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.
回答by Rob van Laarhoven
This a PL/SQL procedural construct i use a lot (credits to Steven Feuerstein and Chen Shapira). An Associative array used for chaching, but it does not pre load all data but gets data from database if needed and puts it in the Associative array.
这是我经常使用的 PL/SQL 过程构造（感谢 Steven Feuerstein 和 Chen Shapira）。用于 chaching 的关联数组，但它不会预加载所有数据，而是根据需要从数据库中获取数据并将其放入关联数组。
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;
Test it :
测试一下：
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
回答by Andrew not the Saint
- An undocumented function: dbms_system.ksdwrt (writes to alert/trace files)
- DBMS_SQL package (as an example of its use see this question
- AUTHID CURRENT_USER clause
- Conditional compilation
