oracle数据库常用操作

1.连接登录

切换用户

su - oracle

以管理员模式登录到sqlplus：

sqlplus / as sysdba 

oracle登录身份有三种：

1.1Normal 普通身份；

1.2.sysdba 系统管理员身份；若以 ‘sysdba’ 方式认证，登录用户为 ‘SYS’，为 Oracle ‘最高权限用户’,可以建数据库

1.3 sysoper 系统操作员身份；若以 ‘sysoper’ 方式认证，登录用户为 ‘PUBLIC’，仅有 ‘PUBLIC 对象权限’,不能建数据库)

创建用户登录：

sqlplus /nolog
conn /as sysdba
create user zhangsan identified by 123456;
grant connect to zhangsan;
alter user zhangsan quota unlimited on users;  #为用户在users表空间上设置配额,分配无限制的空间
alter user zhangsan quota 10M on users;  #分配10M空间
conn zhangsan/123456;以用户zhangsan连接数据库

2.赋予角色

oracle提供三种标准角色（role）:connect、resource和dba.

2.1 connect是使用oracle简单权限，这种权限只对其他用户的表有访问权限，包括select/insert/update和delete等。拥有connect role 的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词(synonym)、回话（session）和其他 数据的链（link)。

2.2 resource role(资源角色)：提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。

2.3 dba role(数据库管理员角色)：拥有所有的系统权限

grant connect to zhangsan;

3.赋予权限

grant create view to zhangsan;
grant sysdba to zhangsan;
grant create materialized view to zhangsan;
grant create any materialized view to zhangsan;
grant global query rewrite to zhangsan;
grant on commit refresh to zhangsan;

4.查询该用户下所有的表

select * from tab;

5.表重命名后，前面需要加上模式名

ZHANGSAN.t1.name

6.插入多条数据

不支持insert into..values(),(),()插入多条数据，需使用insert all into语法或逐条插入

create table uxdbc_oracle_pivot_0001_table01(id int not null,name varchar2(15) not null, sex char(10), phone varchar2(15), salary float,depart varchar2(15),entime timestamp);
#插入数据:
insert into uxdbc_oracle_pivot_0001_table01 values(0001,'张三','man','0123-54589521',2500,'A3001','2015/12/2');
insert into uxdbc_oracle_pivot_0001_table01 values(0001,'张三','man','0123-54589521',2500,'A3001','02-Dec-15');

7.级联删除表

drop table uxdbc_oracle_pivot_0001_table01 cascade constraints;

8.listagg(XXX,',') within GROUP (order by XXX)

listagg第二个参数必须是特殊字符

select * from uxdbc_oracle_pivot_0001_table01 pivot(listagg(sex,',') within group(order by sex) for entime in(2,8)) order by id;

listagg 函数有两个参数：

1、 要合并的列名

2、 自定义连接符号

LISTAGG 函数既是分析函数，也是聚合函数

所以，它有两种用法：

1、分析函数，如： row_number()、rank()、dense_rank() 等，用法相似

listagg(合并字段, 连接符) within group(order by 合并的字段的排序) over(partition by 分组字段)

2、聚合函数，如：sum()、count()、avg()等，用法相似

listagg(合并字段, 连接符) within group(order by 合并字段排序)--后面跟 group by 语句

一部分聚合函数其实也可以写成分析函数的形式。

分析函数和聚合函数本质上都是对数据进行分组，二者最大的不同便是：

对数据进行分组分组之后，

聚合函数只会每组返回一条数据，

而分析函数会针对每条记录都返回，

一部分分析函数还会对同一组中的数据进行一些处理（比如：rank() 函数对每组中的数据进行编号）；

还有一部分分析函数不会对同一组中的数据进行处理（比如：sum()、listagg()），这种情况下，分析函数返回的数据会有重复的，distinct 处理之后的结果与对应的聚合函数返回的结果一致。

#LISTAGG 聚合函数用法
SELECT T.S_NO,
       LISTAGG(T.ITEM_NO, '/') WITHIN GROUP(ORDER BY T.S_NO, T.ITEM_NO) ITEM_NO,
       LISTAGG(T.CUS_NAME, '/') WITHIN GROUP(ORDER BY T.S_NO, T.ITEM_NO) CUS_NAME,
       LISTAGG(T.TEL, '/') WITHIN GROUP(ORDER BY T.S_NO, T.ITEM_NO) TEL,
       LISTAGG(T.ADDRESS, '/') WITHIN GROUP(ORDER BY T.S_NO, T.ITEM_NO) ADDRESS
  FROM test T
 GROUP BY T.S_NO;
#LISTAGG 分析函数用法
SELECT T.S_NO,
       LISTAGG(T.ITEM_NO, '/') WITHIN GROUP(ORDER BY T.S_NO, T.ITEM_NO) OVER(PARTITION BY T.S_NO) ITEM_NO,
       LISTAGG(T.CUS_NAME, '/') WITHIN GROUP(ORDER BY T.S_NO, T.ITEM_NO) OVER(PARTITION BY T.S_NO) CUS_NAME,
       LISTAGG(T.TEL, '/') WITHIN GROUP(ORDER BY T.S_NO, T.ITEM_NO) OVER(PARTITION BY T.S_NO) TEL,
       LISTAGG(T.ADDRESS, '/') WITHIN GROUP(ORDER BY T.S_NO, T.ITEM_NO) OVER(PARTITION BY T.S_NO) ADDRESS
  FROM test T;

9.oracle不支持limit、offset

limit 3语法：where rownum<=3;

offset 2 语法：offset 2 rows;

select t1,t2,t2-t1 t3 from uxdbc_oracle_operator_timecharacters_1441_table01 where rownum <=10 order by t3 offset 1 rows;

10.Oracle不支持布尔类型

11.oracle数据库关闭后恢复

oracle数据库关闭了，SQL*Plus无法连接，显示以下错误：

ORA-01033 : ORACLE initialization or shutdown in progress

conn zhangsan/123456报错：

ORA-01090: shutdown in progress - connection is not permitted

解决办法：

以DBA用户登录，sqlplus / as sysdba

提示：已连接

如果提示Connected to an idle instance.执行SYS@orcl>startup

提示：ORACLE instance started.

SQL>shutdown normal

提示：数据库已经关闭已经卸载数据库ORACLE 例程已经关闭

SQL>startup mount

提示：ORACLE例程已经启动

SQL>alter database open;

提示：第1 行出现错误: ORA-01157: 无法标识/锁定数据文件 5 - 请参阅 DBWR 跟踪文件

继续输入，SQL>alter database datafile 5 offline drop;

提示：数据库已更改。

循环使用最后两步，直到alter database open;后不再提示错误，出现“数据库已更改”。

到这里可能会出现另外一种情况：

ORA-01172:线程1的恢复停止在块118368(在文件2中)

ORA-01151:如果需要，请使用介质恢复以恢复块和还原备份

此时可以进行介质恢复：

SQL>recover datafile 2

完成介质恢复。

12.把date类型的公元前年份前面的负号显示出来

alter session set nls_date_format='sYYYY-MM-DD HH24:MI:SS';

13.设置列宽和、行宽

set linesize number;      #线宽   
set linesize 150;
set pagesize number;      #页面大小
set pagesize 300;
col colname format size;  #列宽   
col ID format a20;        #设定字符列格式
col ID for 999,999,999.999 #设定数字列格式  
clear columns;            #清空当前所有列的设置，恢复成默认格式

14.时间输入

ALTER SESSION SET TIME_ZONE='+08:00'; #设置时区

公元前：

select to_date('2022-11-30 13:34:56','YYYY-MM-DD HH24:MI:SS') - to_timestamp('-2022-11-20 12:34:56','sYYYY-MM-DD HH24:MI:SS') from dual;

带时区：

select to_date('2022-10-23 13:34:56','YYYY-MM-DD HH24:MI:SS') -timestamp'2022-10-21 12:34:56-08:00'  from dual;

时间间隔：

select to_date('2020-01-01 12:34:56','YYYY-MM-DD HH24:MI:SS') - interval'1-2' year to month from dual;
select to_date('2020-01-01 12:34:56','YYYY-MM-DD HH24:MI:SS') - interval'0 00:00:00' day to second t3 from dual;
 
create table test(t1 date,t2 timestamp);
insert into test values(to_date('2022-11-30 13:34:56','YYYY-MM-DD HH24:MI:SS'),to_timestamp('2022-11-20 12:34:56','YYYY-MM-DD HH24:MI:SS'));
create table test(t1 timestamp with time zone,t2 interval day to second);
insert into test values(timestamp'2020-01-01 12:32:22-8:00',interval '14' day);

CREATE TABLE candidates ( candidate_id NUMBER, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, job_title VARCHAR2(255) NOT NULL, year_of_experience INTERVAL YEAR TO MONTH, PRIMARY KEY (candidate_id) );  
INSERT INTO candidates ( first_name, last_name, job_title, year_of_experience ) VALUES ( 'Camila', 'Kramer', 'SCM Manager', INTERVAL '10-2' YEAR TO MONTH ); 

15.修改日期显示格式

可以通过设置 NLS_DATE_FORMAT 来让日期显示更人性化，可以有如下几种方式：

①　 在会话级别运行命令：“ ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; ”，只在会话级别起作用。

②　 在文件 $ORACLE_HOME/sqlplus/admin/glogin.sql 中加入：“ ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; ”，这样每个会话都会起作用。

③　 修改初始化参数：“ ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; ”，每个会话都起作用。

④　 设置环境变量 NLS_DATE_FORMAT ，但是必须和 NLS_LANG 一起设置，否则不会生效，可以直接在会话窗口使用 export 或 .bash_profile 配置文件（全局应用）设置，如下所示：

退出数据库在控制台执行

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK # 或： export NLS_LANG=AMERICAN

若是 Windows 环境，则可以使用如下命令：

SETX "NLS_DATE_FORMAT" "YYYY-MM-DD HH24:MI:SS"

SETX "NLS_LANG" "AMERICAN_AMERICA.ZHS16GBK"

16.存储过程

创建存储过程语法：

create or replace procedure 存储过程名
as
begin
  ----------------------------
end;

注：

在存储过程(PROCEDURE)和函数(FUNCTION)中没有区别；

在视图(VIEW)中只能用AS不能用IS；

在游标(CURSOR)中只能用IS不能用AS。

create or replace procedure myDemo01
as
begin
  dbms_output.put_line('hello word, my name is stored procedure');
end;
call myDemo01();    --call 存储过程名可完成调用，注意括号不能少

#带in out 参数：
create or replace procedure myDemo04(name out varchar,age in int)
as
begin
    dbms_output.put_line('age='||age);
    select 'ex_sunqi' into name from dual;
end;
 
declare
   name varchar(10);
   age int;
begin
   myDemo04(name=>name,age=>25);
   dbms_output.put_line('name='||name);
end;

create procedure uxdbc_oracle_extension_plsql_goto_0017_procedure01 
is i int := 2; 
begin 
    loop 
        <<next_step>> 
        i := i * 2; 
        if i > 100 then 
            exit;
        end if; 
        if i > 50 then 
            dbms_output.put_line(i); 
            goto next_step;
        end if; 
        dbms_output.put_line(i);
    end loop; 
end;
/
call uxdbc_oracle_extension_plsql_goto_0017_procedure01();
#注意：创建存储过程时没有参数不需要加括号()，但调用的时候要加

17.函数

创建函数语法：

create [or replace] function 函数名 
([p1,p2...pn])
return datatype
is|as
--声明部分 
begin
--PL/SQL程序块
end

create or replace function uxdbc_oracle_extension_plsql_goto_0002_function01(t1 in int) return varchar
    is p varchar(30);
    begin 
        if t1 mod 2 =0 then
            goto even_number;
        else
            goto odd_number;
        end if;
        <<even_number>>
        p := t1 || ' is a even number';
        goto end_lb;
        <<odd_number>>
        p := t1 || ' is a odd number';
        <<end_lb>> 
        return p; 
   end;
   /

如果函数创建成功但有报警 Warning: Function created with compilation errors.

执行show errors function uxdbc_oracle_extension_plsql_goto_0002_function01;查看具体的错误信息，然后修改

错误1：text类型不存在，return text改为return varchar

错误2：t1%2=0 改成t1 mod 2 = 0

错误3：p:=cast($1 as text) || ' is a odd number'改成p:=t1 || ' is a odd number'

函数无入参时，函数名后面没有括号 create or replace function function01 return int...

18.匿名块

创建匿名块语法：

declare
　　--声明变量
begin
　　--执行业务逻辑
exception
　　--异常处理
end; 
/

执行oracle匿名块，最前面先执行最前面加上下面这句，使控制台显示输出

set serveroutput on;

declare
  s  int := 0;
  i  int := 0;
  j  int;
begin
  <<outer_loop>>
  loop
    i := i + 1;
    j := 0;
    <<inner_loop>>
    loop
      j := j + 1;
      s := s + i * j; 
      if j<=5 then
        goto inner_loop;
      elsif (i * j) <= 15 then
        goto outer_loop;
      else
        goto end_loop;
      end if;   
    end loop inner_loop;
  end loop outer_loop;
  <<end_loop>> 
  dbms_output.put_line('end_loop');
  dbms_output.put_line('The sum of products equals: '||s);
end;
/