oracle学习笔记

Oracle学习笔记

说明

1. 教程来源于哔哩哔哩，已加入个人收藏

2. 笔记只记载本人所不熟悉的知识点

本笔记只用作学习用途

正文

乱码问题：

Sql>  select userenv('language') from dual; 查出编码

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

]$  export NLS_LANG="SIHIMPLIFIED CNESE_CHINA.ZHS16GBK"

Vim /etc/profile 配置永久export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

SQL> create user chendd identified by chendd;

create user chendd identified by chendd

            *

第

ORA-65096: 公用用户名或角色名无效

公共用户名必须以C## 开头。

创建用户

Sql> create user c##chen identified by chen;

Sql> grant dba,connect,resource to c##chen;

Sql> conn c##chen/chen；

常用命令

sqlplus sys/sys as sysdba         dba登录

sqlplus scott/tiger               普通用户

show user                      查看用户

conn sys/sys as sysdba           切换用户

conn scott/tiger 切换用户

select * from tab;         查询用户下的表

selec * from scott.dept 管理员跨用户访问表。

Create schema sc1 authorization chen;报错，Oracle不支持自定义模式，一个用户即是一个模式。

--  查看当前的所有数据库select name from v$database;

-- 查看当前库结构

desc v$database;

-- 查看SID select instance_name from v$instance;

-- 查看服务名select name from dba_services;

-- 查看当前库的所有数据表select TABLE_NAME from all_tables;

-- 查看用户select * from dba_users; --查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,systemselect * from all_users;  --查看你能管理的所有用户！select * from user_users; --查看当前用户信息 ！

-- 创建用户create user 用户名 identified by 密码;/*

common user:公共用户名必须以C##或c##开头。公共用户可以访问全部CDB

local user: 本地用户不能以C##或c##开头。本地用户只能访问特定的PDB

*/

-- 给用户修改密码，密码不能以数字开头alter user 用户名 identified by 新密码;

-- 给用户分配权限grant create 权限 to 用户名;

/*

create session(登录权限)

create table(创建表权限)

create index(创建索引权限)

create view(创建视图权限)

create sequence(创建序列权限)

create trriger(创建触发器权限)

insert: 增

delete: 删

update: 改

select: 查

*/

-- 按角色对用户分配权限grant 角色名 to 用户名;

/*

常见角色:

dba、connect、resource

-- connect角色

是授予最终用户的典型权利，最基本的权利，能够连接到Oracle数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作。

alter session --修改会话

create cluster --建立聚簇

create database link --建立数据库连接

create sequence --建立序列

create session --建立会话

create synonym --建立同义词

create view --建立视图

-- resoure角色

是授予开发人员的，能在自己的方案中创建表、序列、视图等。

create cluster --建立聚簇

create procedure --建立过程

create sequenc --建立序列

create table --建表

create trigger --建立促发器

create type --建立类型

DBA角色

是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限。

*/

-- 回收权限revoke 权限/角色 from 用户名;

-- 创建表CREATE TABLE STUINFO (

    id NUMBER(5) PRIMARY KEY,

    name VARCHAR2(7) NOT NULL,

    age NUMBER(2) NULL,

    sex CHAR(1) NULL,

    grade CHAR(4) NULL

)

-- 插入一条记录INSERT INTO STUINFO (ID, NAME, AGE, SEX, GRADE) VALUES

(1, 'lily', 12, 'f', 'c6');

-- 批量插入INSERT ALL

INTO STUINFO VALUES(2, 'kemen', 12, 'm', 'c6')

INTO STUINFO VALUES(3, 'kaka', 11, 'm', 'c5')

INTO STUINFO VALUES(4, 'daly', 13, 'f', 'c6')

select 1 from dual;

SELECT * from STUINFO;

设置oracle服务自启

已经提供一个服务控制脚本：/etc/init.d/oracledb_ORCLCDB-19c名字太长，可以改为：/etc/init.d/oracle

· 配置 /etc/oratab下面这行的 "N" 改为"Y"

ORCLCDB:/opt/oracle/product/19c/dbhome_1:Y

· 添加到系统服务

chkconfig --add oracle

# 可以看到生成的启动脚本

cat /run/systemd/generator.late/graphical.target.wants/oracle.service

# Automatically generated by systemd-sysv-generator

[Unit]

Documentation=man:systemd-sysv-generator(8)

SourcePath=/etc/rc.d/init.d/oracle

Description=SYSV: This script is responsible for taking care of configuring the Oracle Database and its associated services.

Before=multi-user.target

Before=multi-user.target

Before=multi-user.target

Before=graphical.target

[Service]

Type=forking

Restart=no

TimeoutSec=5min

IgnoreSIGPIPE=no

KillMode=process

GuessMainPID=no

RemainAfterExit=yes

ExecStart=/etc/rc.d/init.d/oracle start

ExecStop=/etc/rc.d/init.d/oracle stop

其它

lsnrctl是oracle的监听器,

lsnrctl start

lsnrctl stop

lsnrctl satus

· 手动正常关闭oracle服务方法：

su oracle

sqlplus / as sysdba

shutdown immediate;

||连接查询

Select‘员工名字是’ || name || ‘员工id是’ || id from emp;

结果：

‘员工名字是’ || name || ‘员工id是’

员工名字是tom 员工id是1

员工名字是jerry 员工id是2

单行函数

伪表dual,select 1 from dual  查询1

Select upper(aa) from dual; 转大写

Select lower(aa) from dual；转小写

Concat或者|| 字符串连接，建议|| 可跨数据库

Initcap  首字母大写

Substr(‘hellolinux’,1,3)  从开头到第三个，注意这里下标是从1开始，而不是0

Length(sal) 字符串长度

Replace（‘hello’，‘o’，‘h’）替换hello的o为h

Trunc(11.2) 取整数部分

Mod(10,3)取余

日期：

To_char 字符串转换函数

年 yyyy

月 mm

日 dd

时 HH  12进制 HH24 24进制

分 mi

秒 ss

fm     ex:   to_char(hiredate,’fmyyyy-mm-dd’)   去掉月日前面无用的0    

 999     to_char(sal,’99,999’) 可以将数字每三位以‘,’ 分割

 to_char(sal,’$99,999’) 美元

 to_char(sal,’l99,999’)   l 是local缩写 ，这里可显示为￥

to_number() 转化为数值

to_date(‘1999/07/07 18:05:21’,’yyyy/mm/dd HH24:mi:ss’) 转化为日期

通用函数

空值处理 nvl (避免空值+任何数=空值)

nvl(sal,0) 把空值转化为0

decode(a,’1’,’我是1‘，’2‘，’我是2‘，’其它‘) 如果a=1为我是1，a=2为我是2，a=其它，则为其它，相当于if.. if..else..

case when .. then .. when .. then .. else .. end

连接查询

查询所属领导的工资等级和员工的工资等级要用不同的表，否则意味着员工工资和领导在同一等级的数据。如下图sql:

Select * from a,b where a.no=b.ano(+);左外连接 。

非全量表跟+就可以做外连接查询

 Sql1999对SQL的支持

交叉查询cross join 为笛卡尔积

自然连接 natural join 去笛卡尔积，关联条件字段显示两次

Select * from emp join dept using(deptno) 确定的关联字段，无笛卡尔积

Oracle  left join, right join, full join 三种

分组：下图计算各部门各种工作分别有几人

后面有的前面才能有，后面没有的前面绝对不能有。

Having作用于 group by 之上  ex: having  count(*)>5

Create table aa as select * from bb;

Select * from aa for update;带着事务的查询。

Gender number(1) check (gender in(1,2));

Create table aa(

Id number(4),

constrait id_fk foreign key(id) referens bb(id) on delete casecade           ---级联删除

);

视图

创建视图没有权限，管理员授权：

Grant connect ，resource，dba to scott；

Create or replace view view_a as select * from bb where … … with read only ;                    --with read only 视图不允许修改。

Update view_a a set a.name=’lin’ where a.id=11;

Select * from view_a;

创建序列

上图中：步长，开始，最大最小，循环序列，缓存

Create sequence  seq1; 创建序列

Select seq1.nextval  from dual; 序列的下一个值

Select seq1.currval from dual; 序列的当前值

Insert into aa values（seq1.nextval,’lin’,’女’，22）插入数据，序列作为id值。

索引

单例索引：Create  index index_a on emp(id);

复合索引：create index index_b on emp(name,sex);

复合索引是有顺序的，顺序不对，是不使用索引的

一般在查询where后边的字段设置索引

PL/SQL

过程处理

Declare

变量，游标，例外说明

Begin

语句序列

Exception

异常处理

End;

dbms_output.put_Line(‘hello linux!!’) 放入上边执行，不包含exception，否则报错。

引用变量：pname  emp.name%type pname引用emp表name字段的类型

Ex1: 变量

Declare

 pname vachar(12);   变量

 page number(3) :=24 常量

pname1 emp.name%type 引用变量

 Begin

pname :=’zhangsan’；

select t.name into pname1 from emp t where t.id=77;

dbms_output.put_line(‘名字：’ || pname ||‘；年龄：’ || page)；

end

ex2: 记录类型变量，含有多个类型

Declare

emrec   emp%rowtype

 Begin

select * into emrec from emp t where t.id=77;

dbms_output.put_line(emrec.name   ||  ’,‘  ||  emrec.id)；

end

ex3: if分支

Declare

  Pno number :=&num   运行会让输入num值

  Begin

If ...then

dbms_output.put_line( )

elsif .. then

else..

end if；

end

ex4: 循环

1.

Declare

  Sum number(3):=0

  Begin

while  sum<100 loop

Sum:=sum+1;

dbms_output.put_line(sum)；

End loop;

End;

2.

Declare

  Sum number(3):=0

  Begin

loop

exit when sum=100;

Sum:=sum+1;

dbms_output.put_line(sum)；

end loop;

End;

3.

Declare

  Sum number(3):=0

  Begin

For sum in 1 .. 100 loop

dbms_output.put_line(sum)；

end loop;

End;

游标 cursor

Ex:

1.

Declare

  Prec  emp%rownum;

Cursor c1 is select * from emp;

  Begin

Open c1;

Loop

Fetch c1 into prec;

Exit when c1%notfound;

dbms_output.put_line(prec.id)；

end loop;

close c1;

End;

2.为id为10的员工加888元工资

  Declare

Curson c1(dno number(3)) is

Select * from emp where emp.id = dno;

Prec emp%rownum

  Begin

Open c1(10);

Loop

Fetch c1 into prec;

Exit when c1%notfound;

Update emp t set t.sal=t.sal+888 where  t.id=prec.id;

End loop;

Close c1;

Commit;

End;

例外Exception 部分

  No_data_found 数据未找到

Too_many_rows select into 语句匹配多个行

zero_divide 除数为0

Value_error 算术或者转换异常

Timeout_on_resource 请求资源超时

Ex1:

Begin

Exception 

When zero_divide then

Dbms_output.put_line(‘除数不能为0’)

End  

此处没有退出exit，raise no_data抛出异常并退出；

When others  then 其它异常

存储过程

创建存储过程：

Create or replace procedure hello(id in number,sum out number) as

Begin

Dbms_output.put_line(‘hello linux’);

Sum :=id*8+100

End;

调用存储过程：

Declare

Sum  number

Begin

Hello( 18 , sum)

Dbms_output.put_linr(sum)

End;

存储函数

Create or replace function  county(pno in emp.id%type) return number is

result number;

psal  emp.sal%type;

pjiangjin  emp.jiangjin%type;

Begin

Select t.sal,t.jiangjin into psal,pjiangjin from emp t where t.empno=pno;

Result=psal*12+nvl(pcomm,0);

Return(result);

End county;

存储过程和存储函数对比

存储过程没返回值，存储函数有返回值；

存储过程可以通过输出定义多个返回值，存储函数只能是返回一个，鉴于此，存储过程优于存储函数；

触发器

Ex1:

Create or replace trigger valid_insert before insert on person

Declare

Curday varchar2(20);

Begin

Select to_char(sysdate,’day’) into curday from dual;

If  curday = ‘星期三’ then

Raise_application_error(-20001,’星期三不允许插入数据‘)；

End if;

End valid_insert;

Ex2:

Create or replace trigger valid_update before update of sal on person for each row

Begin

If  :new.sal <= ：old.sal then

Raise_application_error(-20002,’工资不能越加越少‘)；

End if;

End valid_update;

关于Oracle分页

Select * from t5 where rowum =1 ; 有数据

Select * from t5 where rowum =2 ; 无数据，没有1，2没有意义

可以这样：

Select * from (select rownum as rn,t5.* from t5)  where rn =2;有数据

Oracle中的rownum 和rowid

ROWNUM      ROWID

---------- ------------------

89        AAAVo7AAEAAAAIXABY