oracle数据库常用操作

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

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;查看具体的错误信息然后修改

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

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

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

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;
/