Oracle Database 数据库笔记

云满笔记

已于 2023-11-22 14:00:54 修改

阅读量723

点赞数

分类专栏： bigdata 文章标签： oracle database sqlplus sys cdb

于 2023-03-13 14:30:48 首次发布

本文链接：https://blog.csdn.net/wan212000/article/details/129493481

版权

bigdata 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

1. Oracle Database 数据库笔记

1.1. 常用命令

查看表结构: desc 表名;
查看表权限: select * from dba_tab_privs where table_name='STUDENT2';

1.1.1. 查看相应用户下有哪些表

select table_name from user_tables; //当前用户的表        
  
select table_name from all_tables; //所有用户的表    
  
select table_name from dba_tables; //包括系统表  
  
select table_name from dba_tables where owner='xxx'

1.2. JetBrains DataGrip

1.2.1. 如何查找表

Navigate -> Table/Routine/Class… -> Search

1.3. 创建用户

SYSOPER 权限, 即数据库操作员权限, sysoper 主要用来启动、关闭数据库, sysoper 登陆后用户是 public。权限包括:

打开数据库 (STARTUP, ALTER DATABASE OPEN/MOUNT/OPEN), 服务器 (CREATE SPFILE,etc)
关闭数据库服务器
备份数据库
恢复数据库 RECOVERY
日志归档 ARCHIVELOG
会话限制 RESTRICTED SESSION

SYSDBA 权限, 即数据库管理员权限, 最高的系统权限。任何具有 sysdba 登录后用户是 SYS。权限包括: 管理功能, 创建数据库 (CREATE DATABASE) 以及 SYSOPER 的所有权限

其他用户需要手动 grant 权限, show user 为该用户的名称。

$ sqlplus / as sysdba

SQL>conn / as sysdba 
已连接。

SQL>grant sysoper to test; 
授权成功。

SQL>grant sysdba to test; 
授权成功。 

SQL>conn test/test as sysoper; 
已连接。 

SQL>show user 
USER 为 "PUBLIC" 

SQL>conn test/test as sysdba; 
已连接。 

SQL>show user; 
USER 为 "SYS" 

SQL>conn test/test; 
已连接。 

SQL>show user 
USER 为 "test"

1.4. 系统用户

只有合法的用户账号才能访问 Oracle 数据库

Oracle 默认的系统用户:

sys: 这个账户相当于 SQLserver 中的 sa 账户, 是个超级账户, 拥有的权限最大, 可以完成数据库的所有管理任务。
system: 没有 sys 的权限大, 通常用来创建一些用于查看管理信息的表和视图, 同样也不建议使用 system 架构来创建一些与管理无关的表或视图。
sys 和 system 在登录 Oracle 工具时, sys 只能以系统管理员 (sysdba) 或系统操作员 (sysoper) 的权限登录, 而 system 可以直接登录 (normal)。
sysman: 是 Oracle 数据库中用于 EM 管理的用户, 如果你不用该用户, 也可以删除。
scott: 是 Oracle 提供的示例用户, 里面有一些供初学者学习操作的数据表 (emp, dept, salgrade, bonus)。

1.4.1. 启用 scott 用户

给 scott 用户解锁【语法格式】(默认的 scott 用户是锁定的): alter user username account unlock

例: alter user scott account unlock

然后连接: conn scott/tiger

1.4.2. Oracle 之 sys, system 区别

最重要的区别, 存储的数据的重要性不同

sys: 所有 Oracle 的数据字典的基表和视图都存放在 sys 用户中, 这些基表和视图对于 Oracle 的运行是至关重要的, 由数据库自己维护, 任何用户都不能手动更改。sys 用户拥有 dba, sysdba, sysoper 等角色或权限, 是 Oracle 权限最高的用户。
system: 用户用于存放次一级的内部数据, 如 Oracle 的一些特性或工具的管理信息。system 用户拥有普通 dba 角色权限。

其次的区别, 权限的不同。

sys: 用户具有 “SYSDBA” 或者 “SYSOPER” 系统权限, 登陆也只能用这两个身份, 不能用 normal。
system: 用户只能用 normal 身份登陆, 除非你对它授予了 sysdba 的系统权限或者 syspoer 系统权限。

1.4.3. normal、sysdba、sysoper 有什么区别

normal: 是普通权限, 登录后是 system
sysdba: 拥有最高的系统权限, 登陆后是 sys
sysoper: 主要用来启动、关闭数据库, sysoper 登陆后用户是 public

sysdba 和 sysoper 属于 system privilege, 也称为 administrative privilege, 拥有例如数据库开启关闭之类一些系统管理级别的权限 sysdba 和 sysoper 具体的权限可以看下表:

1.4.4. dba 和 sysdba 的区别

sysdba, 是管理 Oracle 实例的, 它的存在不依赖于整个数据库完全启动, 只要实例启动了, 他就已经存在, 以 sysdba 身份登陆, 装载数据库、打开数据库。

只有在数据库完全启动后, dba 角色才有了存在的基础。

1.4.5. 修改密码

SYS 用户是 Oracle 中权限最高的用户, 而 SYSTEM 是一个用于数据库管理的用户。在数据库安装完之后, 应立即修改 SYS, SYSTEM 这两个用户的密码, 以保证数据库的安全。

安装完之后修改密码方法

cmd 命令行下输入 sqlplus / as sysdba;

法 1.SQL> alter user sys identified by huozhe
法 2.SQL> grant connect to sys identified by 123456
法 3.SQL> password system (注: 法 3 只适用于 SYSTEM)

验证:

SQL> conn system/huozhe
已连接。

SQL> show user
USER 为 "SYSTEM"

SQL> exit

注: SYS 和 SYSTEM 用户之间可以相互修改口令

修改 SYS 用户口令后的登录

将 SYS 用户的口令修改成 123456 后, 可按以下几种方法登录:

法 1. sqlplus / as sysdba 【以操作系统认证的方式登录, 不需要用户名和口令】
法 2. sqlplus sys/abcde as sysdba;
法 3. sqlplus sys/ as sysdba

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing optionssqlplus sys as sysdba;

查看当前用户:

SQL> show user
USER 为 "SYS"

这是为什么呢, 为什么修改了口令没有效果, 不用口令或者随便用什么口令都可以进入呢。

答案是: 认证方法。

Oracle 的口令认证

SYS 口令认证分为操作系统认证和 Oracle 认证方法。

操作系统认证方式

对于如果是 Unix 操作系统, 只要是以 DBA 组中的用户登录的操作系统, 就可以以 SYSDBA 的身份登录数据库, 不会验证 SYS 的口令。

对于 Windows 操作系统, 在 Oracle 数据库安装后, 会自动在操作系统中安装一个名为 ORA_DBA 的用户组, 只要是该组中的用户, 即可以 SYSDBA 的身份登录数据库而不会验证 SYS 的口令。也可以创建名为 ORA_SID_DBA(SID 为实例名)的用户组, 属于该用户组的用户也具备以上特权。

如何修改认证方式

如何修改认证方式为操作系统认证或 Oracle 认证。(Windows, Unix 平台有大同小异)

要将认证方式设置为操作系统认证:

修改 sqlnet.ora 文件

….\ product\11.2.0\ dbhome_2\ NETWORK\ADMIN\sqlnet.ora
…\product\版本号、home 目录、NETWORK\ADMIN\sqlnet.ora

记事本打开该文件, 修改参数为:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

WINDOWS 下, 默认就是这样, 即使用 NT 认证

修改 init.ora 文件

….\ product\11.2.0\dbhome_2\dbs\init.ora

说明: …\product\版本号、home 目录、dbs\init.ora

记事本打开该文件, 修改参数为:

remote_login_passwordfile='NONE'

重新启动数据库。

SQL> shutdown immediate

SQL> startup open

SHUTDOWN [mode]

The mode is normal, immediate, or abort.

In a normal shutdown, Oracle Database waits for all currently connected users to disconnect and disallows any new connections before shutting down. This is the default mode.
In an immediate shutdown, Oracle Database terminates and rolls back active transactions, disconnects clients, and shuts down.
In an abort shutdown, Oracle Database terminates active transactions and disconnects users; it does not roll back transactions. The database performs automatic recovery and rollback the next time it is started. Use this mode only in emergencies.

将认证方式设置为 Oracle 认证(密码文件认证):

同上, 修改 sqlnet.ora

….\ product\11.2.0\ dbhome_2\ NETWORK\ADMIN\sqlnet.ora

记事本打开该文件, 修改参数为:

#SQLNET.AUTHENTICATION_SERVICES= (NTS) , #注释掉这句话, 即不使用 NT 认证

或者

SQLNET.AUTHENTICATION_SERVICES= (NONE)

同上, 修改 init.ora
记事本打开该文件, 修改参数为:

remote_login_passwordfile='EXCLUSIVE'

或者

remote_login_passwordfile='SHARED'

EXCLUSIVE 表示只有当前实例使用这个密码文件, 且允许有别的用户作为 SYSDBA 登录进入系统, 若选择了 SHARED, 则表示不止一个实例使用这个密码文件。

重新启动数据库。

SQL> shutdown immediate

SQL> startup open

如果发生 sys 密码丢失的情况, 怎么办?

步骤 1. 使用 system 用户进行密码更改

SQL> conn system/huozhe

已连接。

SQL> alter user sys identified by huozhe

说明:

默认情况下, 只要用户具有 alter user 的权限, 那么可以修改 Oracle 中任意用户, 包括 alter user 中的所有 optional
默认情况下, system 账户之所以能修改 sys 的密码, 是因为它属于 dba 角色, 而 dba 角色当然具有 alter user 权限

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
STUDY                          TRUE  FALSE FALSE

说明现在有 sys 及 STUDY 账户拥有 sysdba 与 sysoper 的权限 [STUDY 默认创建的]。

步骤 2. 创建密码文件

如果存在密码文件 (PWDsid.ora), 则删除它

路径

….\product\11.2.0\dbhome_2\database\PWDorcl.ora
….\product\版本、home 目录、database\PWDsid.ora

然后用 orapwd.exe 创建密码文件

orapwd 路径

…\product\11.2.0\dbhome_2\BIN\orapwd.exe

说明: …\product\版本号、home 目录、BIN\orapwd.exe

–cmd 下输入 cd 命令进入到 ….\product\版本号、home 目录、BIN 目录下, 然后键入命令

orapwd file=filepath\pwd.ora password=password_of_sys entries=N

其中 filepath 表示密码文件路径, pwd.ora 为密码文件名, sid 是数据库实例名

eg: E:\app\Administrator\product\11.2.0\dbhome_2\dbs\PWDorcl.ora

entries 表示允许最大的超级用户数。

当没有指定文件路径时, 密码文件默认存放在 …\product\版本号、dbs\ 目录下。

1.5. 赋予权限

CREATE USER super IDENTIFIED BY abcd1234;
GRANT ALL PRIVILEGES TO super;

SELECT * FROM session_privs
ORDER BY privilege;

1.6. Start up and Shut down the Oracle Database Instance

1.6.1. Start up and Shut down the Oracle Database Instance

You may need to shut down the database instance to perform certain administrative tasks. To shut down and restart the database instance, perform the following steps:

1.Open a terminal window as the Oracle user. Execute the oraenv command to set the environment variables.

$  . oraenv
ORACLE_SID = [Oracle] ? orcl
The Oracle base has been set to /scratch/u01/app/Oracle

2.Log in to SQL*Plus as the SYSDBA user.

# sqlplus / as sysdba
                   SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 15 01:08:39 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

3.Issue the SHUTDOWN command to close the database and shut down the instance.

SQL>  shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

4.Issue the STARTUP command to start the instance and open the database.

SQL> startup
ORACLE instance started.

Total System Global Area 3355443200 bytes
Fixed Size                  8791392 bytes
Variable Size            1929382560 bytes
Database Buffers         1409286144 bytes
Redo Buffers                7983104 bytes
Database mounted.
Database opened.

1.7. SQL 操作

1.7.1. 自增键主键 auto_increment ID

Oracle 11g

here is no such thing as “auto_increment” or “identity” columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger:

Table definition:

CREATE TABLE departments (
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq START WITH 1;

Trigger definition:

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

Oracle 12c

IDENTITY column is now available on Oracle 12c:

create table t1 (
    c1 NUMBER GENERATED by default on null as IDENTITY,
    c2 VARCHAR2(10)
    );

or specify starting and increment values, also preventing any insert into the identity column (GENERATED ALWAYS) (again, Oracle 12c+ only)

create table t1 (
    c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
    c2 VARCHAR2(10)
    );

Alternatively, Oracle 12 also allows to use a sequence as a default value:

CREATE SEQUENCE dept_seq START WITH 1;

CREATE TABLE departments (
  ID           NUMBER(10)    DEFAULT dept_seq.nextval NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

1.7.2. create table

https://www.w3schools.blog/insert-query-Oracle

Example 1: Creating a table with NULL and NOT NULL table constraint.

CREATE TABLE students  
( id number(10) NOT NULL,  
  name varchar2(40) NOT NULL,  
  class varchar2(10)  
);

Example 2: Creating a table with a PRIMARY KEY table constraint.

CREATE TABLE students  
( id number(10) NOT NULL,  
  name varchar2(40) NOT NULL,  
  class varchar2(10)  
  PRIMARY KEY(id)
);

1.7.3. insert

CREATE TABLE students  
( student_id number(10) NOT NULL,  
  student_name varchar2(40) NOT NULL,  
  student_age varchar2(10)  
  PRIMARY KEY(student_id)
);

INSERT INTO students  
(student_id, student_name, student_age)  
VALUES  
(3, 'Happy', '11');

INSERT INTO students  
(student_id, student_name, student_age)  
SELECT id, name, age  
FROM children  
WHERE age > 5;

1.7.4. 创建慢查询: 不使用 dbms_lock.sleep(sec)

Solution

I have created VIEW in the database, which calls my func.

View:

create or replace VIEW TEST_VIEW (ID) as SELECT TEST_PROC() as ID from dual;

Func:

create or replace function TEST_PROC return number is
IS
start_time DATE := sysdate;
end_time DATE;
curr_time: DATE;
id number :=0;
begin
end_time := start_time + interval '30' minute; // here you may define time as you need.
loop id := id + 1;
curr_time := sysdate;
exit when curr_time > end_time;
end loop;
return id;
end TEST_PROC

Maybe that’s would be helpful for someone.

BR.

Perfect ! A little remark : double IS keyword in the function creation

1.7.5. 创建慢查询: 使用 dbms_lock.sleep(sec)

I’m not quite sure what you mean by “stopping agents” in this context. The only “agent” I can think of in this context would be an Enterprise Manager agent but I doubt that’s what you’re talking about and I don’t see why you’d need a long-running query for that.

The simplest way to force a query to run for a long time is to have it sleep for a bit using the dbms_lock.sleep procedure. Something like

CREATE OR REPLACE FUNCTION make_me_slow( p_seconds in number )
  RETURN number
IS
BEGIN
  dbms_lock.sleep( p_seconds );
  RETURN 1;
END;

which you can call in your query

SELECT st.*, make_me_slow( 0.01 )
  FROM some_table st

That will call make_me_slow once for every row in some_table. Each call to make_me_slow will take at least 0.01 seconds. If some_table has, say, 10,000 rows, that would take at least 100 seconds. If it has 100,000 rows, that would take 1,000 seconds (16.67 minutes).

If you don’t care about the results of the query, you can use the dual table to generate the rows so that you don’t need a table with materialized rows. Something like

 SELECT make_me_slow( 0.01 )
   FROM dual
CONNECT BY level <= 20000

will generate 20,000 rows of data and take at least 200 seconds.

If you want a pure SQL query (which gives you less ability to control exactly how long it’s going to run),

select count(*)
  from million_row_table a
       cross join million_row_table b

will generate a 1 million x 1 million = 1 trillion row result set. That’s likely to run long enough to blow out whatever TEMP tablespace you have defined.

Ref: https://stackoverflow.com/questions/23071771/how-to-create-a-slow-sql-query

1.8. 概念

1.8.1. CDB 和 PDB

Oracle 12C 以及更高版本中引入了 CDB 与 PDB 的新特性, 在 ORACLE 12C 数据库引入的多租用户环境 (Multitenant Environment) 中, 允许一个数据库容器 (CDB) 承载多个可插拔数据库 (PDB)。

CDB 全称为 Container Database, 中文翻译为数据库容器, PDB 全称为 Pluggable Database, 即可插拔数据库。

在 ORACLE 12C 之前, 实例与数据库是一对一或多对一关系 (RAC): 即一个实例只能与一个数据库相关联, 数据库可以被多个实例所加载。而实例与数据库不可能是一对多的关系。

当进入 ORACLE 12C 后, 实例与数据库可以是一对多的关系。下面是官方文档关于 CDB 与 PDB 的关系图。

CDB 相当于操作系统, 调用并管理各个 PDB。PDB 相当于真正提供业务需求的数据库实例。Oracle 12C 安装后只创建了 CDB, 需要自己生成相应的 PDB。 Oracle 12C 在安装时, 默认创建为容器数据库。

查看当前是否处于容器 CDB

show con_name;

如果结果为 CDB&ROOT, 则为容器状态 (CBD), 结果为 ORCLPDB(这个 PDB 名字根据自己的实际情况而定, 不是固定的), 则为可插拔数据库 (PDB)。

查看容器中的数据库名

show pdbs;

切换到 PDB

alter session set container=ORCLPDB(这个 pdb 名字根据自己实际情况而定)

切换到 CDB

alter session set container=CDB&ROOT

注: 处于容器 CDB 时, 新建的用户必须以 C## 开头

参考自: CDBs and PDBs

1.8.2. Difference and commonalities between V $SQ L an d V$ SQLAREA

v$sqltext 中有内存中完整的 sql 语句 (SQL 被分割为多行存储), 而其他两个视图都是部分 sql 语句。但 v$sqltext 中存储的比较简单, 没有该语句的统计信息, 比如执行次数等。

v$sql: 存储的是具体的 SQL 和执行计划相关信息。

v$sqlarea: 存储 SQL 语句的前 1000 个 bytes 和一些相关的统计信息, 比如累计的执行次数, 逻辑读, 物理读等统计信息
V$SQLAREA 在 Oracle9i, Oracle10.1 和 Oracle10.2 版本中字段定义都不太一样, Oracle10.2 中会多出 sql_fulltext 字段,
是一个 CLOB 字段。

实际上, v$sqlarea 可以看做 v$sql 根据 sqltext 等做了 group by 之后的信息。

v$sql stores statistics about each child version of an SQL statement that is stored in the Shared Pool while v$sqlarea summarizes these statistics per sqlid (or address, respectively).

v$sql selects from the underling x$ table x$kglcursor_child while v $s ql a re a se l ec t s f ro m ‘ x$ kglcursor_child_sqlid`.

v$sql_shared_cursor stores the reason why a particular child cursor is not shared with another version of the «same» SQL statement.

From: https://renenyffenegger.ch/notes/development/databases/Oracle/installed/dynamic-performance-views/sql/diff-sql_sqlarea#:~:text=Columns%20The%20following%20query%20selects%20the%20column%20names,where%20table_name%20%3D%20%27V_%24SQLAREA%27%20and%20owner%20%3D%20%27SYS%27%29

V$SQLAREA: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQLAREA.html#GUID-09D5169F-EE9E-4297-8E01-8D191D87BDF7
V$SQL: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQL.html#GUID-2B9340D7-4AA8-4894-94C0-D5990F67BE75

1.9. Oracle sample database

1.9.1. 简单: https://www.oracletutorial.com/getting-started/oracle-sample-database

Download Oracle Sample Database

1.9.2. 复杂: Oracle Database Sample Schemas

下载 https://github.com/oracle-samples/db-sample-schemas#oracle-database-sample-schemas 里面的 https://github.com/oracle-samples/db-sample-schemas/archive/refs/tags/v23.2.tar.gz 。

Example

To install the HR schema, complete the following steps:

cd into the human_resources folder.
Connect to the target database with a privileged user.
Run the hr_install.sql script and provide inputs for the prompts.

docker cp db-sample-schemas-12.1.0.2.tar.gz testing-oracle-972072000R3I8d:/tmp

cd human_resources
sqlplus / as sysdba
conn / as sysdba

@/tmp/db-sample-schemas-23.2/human_resources/hr_install.sql
#sql <system>@<connect_string>
#@hr_install.sql

Ref: https://docs.oracle.com/en/database/oracle/oracle-database/21/comsc/installing-sample-schemas.html#GUID-1E645D09-F91F-4BA6-A286-57C5EC66321D

1.10. 函数

1.10.1. TO_DATE

TO_DATE - Convert String to Datetime - Oracle to SQL Server Migration

Oracle:

  -- Specify a datetime string and its exact format
  SELECT TO_DATE('2012-06-05', 'YYYY-MM-DD') FROM dual;

SQL Server:

  -- Specify a datetime string and style 102 (ANSI format), raises an error if conversion fails
  SELECT CONVERT(DATETIME, '2012-06-05', 102);
 
  -- TRY_CONVERT available since SQL Server 2012 (returns NULL if conversion fails)
  SELECT TRY_CONVERT(DATETIME, '2012-06-05', 102);

1.11. 案例

1.11.1. Oracle sql 语句时间字段做前后比较的几种方式

PS: update 是时间字段, to_date(时间字符串, 格式化字符串)是 oracle 转义时间的函数

oracle sql 日期比较:

在今天之前:

select * from up_date where update < to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update <= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')

在今天只后:

select * from up_date where update > to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update >= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')

精确时间:

select * from up_date where update = to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')

在某段时间内:

select * from up_date where update between to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update < to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and update > to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update <= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and update >= to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss')

1.11.2. Oracle 中处理除数为 0 的技巧 (oracle 中除数为+0)

Oracle 中, 处理除数为 0 的情况, 可以采取如下三种技巧:

(1)NVL 函数

NVL 函数, 实际上是将空值变成指定值, 当表达式中出现除数为 0 的情况时, 可将其替换为 NVL 函数, 如:

select (A/NULLIF(B,0)) from dual;

可替换为:

select (A/NVL(B,1)) from dual; 
(2)CASE WHEN 语句

CASE WHEN 语句是 Oracle 数据管理中常用的技巧, 当表达式出现除数为 0 的情况时, 可以加入 CASE WHEN 语句, 如:

select (A/NULLIF(B,0)) from dual;

可替换为:

select 
    CASE 
        WHEN B = 0 THEN 0 
        ELSE A/B 
    END as result
from dual;

(3)ISNULL 函数

ISNULL 函数也可以用来处理除数为 0 的情况, 该函数和 NVL 函数有类似之处, 但二者最大的不同在于前者可以处理数字型和字符串型参数形式:

select (A/NULLIF(B,0)) from dual;

可替换为:

select (A/ISNULL(B,1)) from dual;

总之, 处理除数为 0 的情况, Oracle 有多种技巧可以使用, 以上所介绍的只是三种常用技巧, 初学者可以依据自身的实际需求进行选择, 以解决除数为 0 的问题。

1.11.3. Oracle 中保留两位小数

ROUND(A/B,2) 函数

ROUND() 函数是会将计算结果进行四舍五入的, 如果所需要的值需要进行四舍五入, 就可以选择这个函数, 可以有一个参数, 也可以有两个参数; 如果有两个 param, 第一个是你的计算表达式, 第二个是需要保留的小数位数。例子如下:

SELECT ROUND(2/3, 2) RESULT FROM DUAL

TRUNC(A/B,2) 函数

RUNC() 函数是不会将计算结果进行四舍五入的, 如果所需要的值不需要进行四舍五入, 就可以选择这个函数, 可以有一个参数, 也可以有两个参数; 如果有两个 param, 第一个是你的计算表达式, 第二个是需要保留的小数位数。例子如下:

SELECT TRUNC(2/3, 2) RESULT FROM DUAL

TO_CHAR(A/B,‘FM99990.99’)

TO_CHAR() 是一个格式化函数, 第一个参数是计算表达式, 第二个参数是指定格式化的格式, 如果保留两位小数则小数点后写两个 99, 这里的数字 9 代表的数字, 也是一个占位符, 表示该位置上以后会是一个数字, 为什么小数点前面会是一个 0, 而不是 9, 是因为如果计算结果小于 1, 那么只会显示小数点和小数点之后的部分, 前面的 0 会忽略掉。

如果计算结果小于 1, 只显示小数点和小数点后的部分

/*结果小于 1, 则只显示小数点和小数点后的内容*/
SELECT TO_CHAR(2/3, 'FM99.99') RESULT FROM DUAL

当计算结果小于 1, 也会显示小数点前面的 0

SELECT TO_CHAR(2/3, 'FM990.99') RESULT FROM DUAL

当结果为小数点后 1 位时, 需要显示 2 位小数

select to_char(1/10,'FM9990.00') AS result  from dual;

注意: 0 和 9 的区别, 具体我没找到相关的信息, 暂时说一下我的想法, 0 和 9 应该都是类似于占位符, 你实际除得的结果中的数字一般会替换掉 9, 但是当你整数部分只有一位且是 0 的时候, 那么你需要在格式化的时候用 0 来指定格式化的模型, 如上文那个。67 的样子, 小数点后面的值, 你可以用 0 也可以用 9 来指定模式, 用 9 指定的结果就是你的小数点本来后面几位它就显示几位, 如果用 0 来指代, 它在位数不够的情况下会用 0 去进行占位填充。

1.11.4. 计算除法——解决除数为零报错

--Oracle 中计算除法语法
SELECT case WHEN(NVL(除数, 0))!=0 THEN round(被除数 / 除数, 需要保留的小数位数) ELSE 0 END result FROM DUAL

1.12. 视图

1.12.1. 转换利用 Oracle 视图实现字段类型转换 (oracle 视图字段类型)

字段类型转换是 Oracle 用户经常碰到且需要解决的问题, 字段类型转换有时也叫做数据转换, 可以利用 Oracle 视图实现字段类型转换, 是一种灵活有效的方法。

Oracle 视图实现字段类型转换时, 可以以虚拟的表的形式从表中选择数据, 然后再以另一种类型的表重新建立表结构(比如: 从字符型变成日期型), 并且将转换所需的表达式包含在其中, 之后再利用要求的字符类型对指定的字段进行重新定义, 最终完成字段类型转换, 用户可以用虚拟表中的数据进行查询或分析。

以下是 Oracle 视图用于字段类型转换的基本语句:

CREATE VIEW view_name AS -- 定义一虚拟表
 SELECT 
     field1, -- 指定要转化的字段
       CAST(field1 AS new_type) AS new_field1 -- 将指定字段转换成定义的新类型
   FROM 
     table_name;  -- 从源表中选取数据

以上通过 CAST 函数将指定字段转换成定义的新类型, 其中的"new_type"表示要转换成的新类型, 用户可以根据需要进行调整, 以达到要求的目的。

此外, Oracle 还提供了其他数据转换的函数, 比如 NVL 函数, 用来转换空值数据到字符串等; TO_CHAR 函数用来将数值转换成字符串; TRIM 函数可以用来去掉字符串头尾的空格等, 用户可以根据实际情况进行选择使用。

通过以上方法利用 Oracle 视图可以实现字段类型转换, 更方便快捷的解决用户的问题, 提高工作效率。

1.13. 环境变量

1.13.1. NLS_LANG

NLS_LANG 是一个环境变量, 用于定义语言, 地域以及字符集属性。对于非英语的字符集, NLS_LANG 的设置就很是重要。

NLS: “National Language Support (NLS)” 当咱们设定一种 nls 的时候实际上咱们是为 oracle 在存放数据时指定了他的语种所特有的一些表达形式, 好比咱们选择 chinese, 那么它的中文字符如何存放, 按什么规则排序, 货币如何表示, 日期格式也就被设定了。

NLS_LANG 参数由如下部分组成: NLS_LANG=_.
NLS_Language 指定:

Oracle(错误)信息的语言
日和月份的名称
注意: NLS_LANGUAGE 与插入和查询的数据的语言无关。

NLS_Territory 指定:

货币和数字格式
计算星期和天数的范围和惯例

客户端字符集 (CLIENTS CHARACTERSET):

定义 Oracle 客户端, 客户应用使用的编码

或者它要符合您 Microsoft Windows 代码页 (GUI 工具的 ACP, 命令提示符的 CHCP 值)
或者为 Unicode WIN32 应用设置为 UTF8/AL32UTF8。

常见的值能够参见 Oracle Database Client Globalization Support

https://docs.oracle.com/en//database/oracle/oracle-database/23/odpnt/featGlobal.html#GUID-EE66C558-1C17-4CB5-A31F-72066B9B9FA1

1.14. 报错解决

1.14.1. `dpiStmt_execute: ORA-00942: table or view does not exist`

表或视图确实不存在或名称写错。
首先检查是否有权限。
表名大小写

Oracle 对大小写敏感, 通常在创建和查询时对名称数据库会自动转为大写, 但语句中有引号时会按引号中的内容保留。例如:

create table "TableName"  (
   "col1"        VARCHAR2(2),
   "col2"        DATE
);

这时查询语句也要相应改成

select * from "TableName";

平时所做的对其它用户下资源的操作权限有些是通过角色授权的, 但在存储过程、函数、触发器不能直接使用, 需要单独授权。授权语句举例:

grant select on sys.XXX TO XXX;

被查询的表名前是否添加了该表的所属用户名。

Oracle 有规定:

需要使用

owner.object_name 方式来访问别的用户的表, 即使你有权限

不然 oracle 会找不到你的对象, 这与 oracle 解析对象名称有关

查询表所属用户:

SELECT table_name, owner FROM all_tables WHERE table_name = 'STUDENT2';

需要在存储过程中使用 role 权限(这个我没太看懂, 以下代码部分为粘贴, 来自 http://www.cnblogs.com/jzbml/p/5991918.html)

SQL> select * from dba_role_privs where grantee='SUK';
 
  GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
  ------------ ------------ ------------ ------------
  SUK DBA NO YES
  SUK CONNECT NO YES
  SUK RESOURCE NO YES
 
  --用户 SUK 拥有 DBA 这个 role
 
  --再创建一个测试存储过程: 
  create or replace procedure p_create_table  
  is
  begin
  Execute Immediate 'create table create_table(id int)';
  end p_create_table;
 
  --然后测试
  SQL> exec p_create_table;
 
  begin p_create_table; end;
 
  ORA-01031: 权限不足
  ORA-06512: 在"SUK.P_CREATE_TABLE", line 3
  ORA-06512: 在 line 1
 
  --可以看到, 即使拥有 DBA role, 也不能创建表。role 在存储过程中不可用。
  --遇到这种情况, 我们一般需要显式进行系统权限, 如 grant create table to suk;
  --但这种方法太麻烦, 有时候可能需要进行非常多的授权才能执行存储过程
  --实际上, oracle 给我们提供了在存储过程中使用 role 权限的方法: 
  --修改存储过程, 加入 Authid Current_User 时存储过程可以使用 role 权限。
  create or replace procedure p_create_table  
  Authid Current_User is
  begin
  Execute Immediate 'create table create_table(id int)';
  end p_create_table;
 
  --再尝试执行: 
  SQL> exec p_create_table;
 
  PL/SQL procedure successfully completed
 
  --已经可以执行了。

我在我遇到的问题中试了这个方法, 得到以下结果:

Compilation errors for PACKAGE BODY XXX.XXXX

Error: PLS-00157: AUTHID 只允许在方案级程序中使用
Line: xxxx
Text: PRM_ERRORMSG OUT VARCHAR2) Authid Current_User IS