根据提供的文件信息，我们可以整理出以下关于Oracle数据库中的SQL语句和函数的相关知识点：
### 经典SQL语句收集（ORACLE）
#### 文件概述
该文件包含了一系列经典的Oracle SQL语句及函数，主要目的是用于学习和交流。内容覆盖了常用的查询、函数编写等方面。
#### SQL查询示例
1. **查询非特定用户**
- **语法**:
```sql
SELECT username
FROM dba_users
WHERE username NOT IN
('TEXT', 'RMAN_USER', 'TEST', 'SCOTT', 'TSMSYS', 'MDDATA', 'DIP',
'DBSNMP', 'SYSMAN', 'MDSYS', 'ORDSYS', 'EXFSYS', 'DMSYS', 'WMSYS',
'CTXSYS', 'ANONYMOUS', 'XDB', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
'OLAPSYS', 'MGMT_VIEW', 'SYS', 'SYSTEM', 'OUTLN');
```
- **解释**:
- 这个查询用于获取`dba_users`表中除了列出的一些特殊用户之外的所有用户名称。
- `NOT IN`操作符用来排除指定的用户名列表。
2. **查询锁定信息**
- **语法**:
```sql
SELECT s.username,
DECODE(l.type, 'TM', 'TABLELOCK', 'TX', 'ROWLOCK', NULL) LOCK_LEVEL,
o.owner, o.object_name, o.object_type,
s.sid, s.serial#, s.terminal, s.machine, s.program, s.osuser
FROM v$sessions s,
v$lock l,
all_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username IS NOT NULL;
```
- **解释**:
- 该查询用于显示当前锁定的会话信息，包括锁定级别、锁定的对象以及会话的详细信息等。
- 使用`DECODE`函数来确定锁定类型是表锁还是行锁。
- `v$sessions`、`v$lock`和`all_objects`视图分别包含了会话、锁定和对象的信息。
- `AND`条件用于关联这些视图并过滤出有效的记录。
3. **查询阻塞会话**
- **语法**:
```sql
SELECT (SELECT username FROM v$session WHERE sid = a.sid) blocker,
a.sid,
'is blocking',
(SELECT username FROM v$session WHERE sid = b.sid) blockee,
b.sid
FROM v$lock a,
v$lock b
WHERE a.block = 1
AND b.request > 0
AND a.id1 = b.id1
AND a.id2 = b.id2;
```
- **解释**:
- 此查询用于找出哪些会话正在阻塞其他会话。
- `v$lock`视图包含了锁定信息。
- 通过比较两个`v$lock`记录中的`id1`和`id2`字段来判断是否有阻塞发生。
- 子查询用于获取对应会话的用户名。
#### 函数示例
1. **生成随机数序列的函数**
- **函数定义**:
```plsql
CREATE OR REPLACE FUNCTION f_ssq_random(prior_num IN INTEGER, end_num IN INTEGER)
RETURN VARCHAR2
IS
v_prior_rand INTEGER := 0;
v_end_rand INTEGER;
v_string STRING(32000) := 'ɫ';
BEGIN
-- 函数体实现...
END f_ssq_random;
```
- **解释**:
- 该函数名为`f_ssq_random`，接受两个整型参数`prior_num`和`end_num`，返回一个`VARCHAR2`类型的字符串。
- 内部使用`DBMS_RANDOM.VALUE`生成随机数，并通过循环结构将生成的随机数存储到`v_string`变量中。
- 函数主要用于生成指定范围内的随机数序列。
2. **自定义聚合函数**
- **类型定义**:
```plsql
CREATE OR REPLACE TYPE type_str_agg AS OBJECT
(
total VARCHAR2(4000),
STATIC FUNCTION odciaggregateinitialize(sctx IN OUT type_str_agg)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate
(
SELF IN OUT type_str_agg,
VALUE IN VARCHAR2
) RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate
(
SELF IN type_str_agg,
return_value OUT VARCHAR2,
flags IN NUMBER
) RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge
(
SELF IN OUT type_str_agg,
ctx2 IN type_str_agg
) RETURN NUMBER
);
```
- **类型体定义**:
```plsql
CREATE OR REPLACE TYPE BODY type_str_agg IS
STATIC FUNCTION odciaggregateinitialize(sctx IN OUT type_str_agg)
RETURN NUMBER IS
BEGIN
sctx := type_str_agg(NULL);
RETURN 0;
END odciaggregateinitialize;
END;
```
- **解释**:
- 定义了一个名为`type_str_agg`的复合数据类型，包含了一个`VARCHAR2`类型的字段`total`。
- 该类型还包含了一些静态和成员函数，用于支持Oracle中的ODCI聚合函数接口。
- `odciaggregateinitialize`函数用于初始化聚合过程。
以上内容为Oracle数据库中经典SQL语句及函数的部分示例，可以帮助初学者更好地理解和掌握Oracle数据库的使用方法。
