72 lines
1.3 KiB
Markdown
72 lines
1.3 KiB
Markdown
## oracle 常用命令
|
||
1. 开启监听 `lsnrctl start`,关闭监听`lsnrctl stop`;
|
||
2. 查看oracle版本：
|
||
```sql
|
||
select * from v$version;
|
||
```
|
||
3.
|
||
```sql
|
||
SPOOL report
|
||
@run_report -- 指定输出文件可使用SPOOL report.txt
|
||
SPOOL OFF
|
||
```
|
||
将输出的内容写道命名为`report.lst`的文件当中
|
||
|
||
4. 查看ORACLE相关参数
|
||
```
|
||
select * from v$NLS_PARAMETERS;
|
||
```
|
||
5. 在sqlplus中执行Linux命令:在Linux命令最前面添加! ，比如`!pics -a`
|
||
|
||
## oracle 常用函数
|
||
|
||
1. decode()
|
||
|
||
以下sql
|
||
```sql
|
||
SELECT supplier_name,
|
||
decode(supplier_id, 10000, 'IBM',
|
||
10001, 'Microsoft',
|
||
10002, 'Hewlett Packard',
|
||
'Gateway') result
|
||
FROM suppliers;
|
||
```
|
||
相当于
|
||
```sql
|
||
IF supplier_id = 10000 THEN
|
||
result := 'IBM';
|
||
ELSIF supplier_id = 10001 THEN
|
||
result := 'Microsoft';
|
||
ELSIF supplier_id = 10002 THEN
|
||
result := 'Hewlett Packard';
|
||
ELSE
|
||
result := 'Gateway';
|
||
END IF;
|
||
```
|
||
比如以下语句
|
||
```sql
|
||
select decode(a.count,1,3) from (select count(*) as count from dual) a;
|
||
```
|
||
|
||
2. mod 函数
|
||
|
||
mod(除数,被除数)
|
||
```sql
|
||
select mod(2,4) from dual;
|
||
```
|
||
|
||
3. nvl 函数
|
||
```sql
|
||
nvl(expre1,expre2)
|
||
```
|
||
当expre1 为空时返回expre2的值
|
||
|
||
4. multiset 函数
|
||
告诉oracle这个查询想返回多行数据
|
||
|
||
5. case函数
|
||
对oracle的对象进行强制转换，比如
|
||
```sql
|
||
select case( empno as varchar2(20) ) e from eoda.emp;
|
||
```
|