oracle数据库开发技术

oracle数据库开发技术表与表结构操作(重点)表结构数据类型字符类型char(n)和varchar2(n)相同点:可以保存数字、字母不同点,char长度固定、时间效率,varchar2长度可变、空间效率数字类型number(m,n),num

修改表名?、??????????

创建用户,授予权限

  1. 用户名加c##
-- 使用超级管理员登录
conn sys/root as sysdba;
**可以不用超级管理员,system也可以,尽量**
-- 创建scott用户
create user c##scott identified by tiger;

-- 为用户授权
grant connect,resource,unlimited tablespace to c##scott container=all;
-- 把emp表中的select权限授权给用户wanghao
grant select on tableName(emp) to wanghao;
-- 设置用户使用的表空间
alter user c##scott default tablespace users;
alter user c##scott temporary tablespace temp;

-- 使用c##scott用户登录
connect c##scott/tiger

显示用户是谁

show user;

查看scott用户下的表信息

select * from all_tables where owner=‘C##SCOTT’;

Oracle用户锁定/解锁(DBA用户执行)

锁定用户:
alter user user_name account lock;
用户解锁:
alter user user_name account unlock;

查看用户状态:
#一个账号状态可以是打开的(open),也可以是锁定(locked)或过期的(expired)
select username,account_status from dba_users where username = upper(‘user_name’);

新建角色

角色就是权限的集合,可以把一个角色授权给用户

create role myrole;//创建角色
grant create session to myrole;//将创建session的权限授予myrole
grant myrole to username;//授予username用户myrole的角色
drop role myrole;删除角色

权限

查看权限

select * from user_sys_privs;//查看当前用户所有权限
select * from user_tab_privs;//查看所用用户对表的权限

授予权限

grant connect to username;//授予连接的权限,包含create session
grant create session to username;//授予username用户创建session的权限,即登陆权限,可以登陆
grant unlimited tablespace to username;//授予username用户使用表空间的权限
grant create table to username;//授予创建表的权限
grante drop table to username;//授予删除表的权限
grant insert table to username;//插入表的权限
grant update table to username;//修改表的权限
grant all to public;//这条比较重要,授予所有权限(all)给所有用户(public)

-- 把emp表中的select权限授权给用户wanghao
grant select on tableName(emp) to wanghao;

grant create session to myrole;//将创建session的权限授予myrole角色

grant select on tablename to username;//授予username用户查看指定表的权限
grant drop on tablename to username;//授予删除表的权限
grant insert on tablename to username;//授予插入的权限
grant update on tablename to username;//授予修改表的权限
grant insert(id) on tablename to username;
grant update(id) on tablename to username; //授予对指定表特定字段的插入和修改权限,注意,只能是insert和update
grant alert all table to username;//授予username用户alert任意表的权限
grant 角色 to 用户//把角色给用户

撤销权限

revoke create session from c##test_user;

表空间

  1. 查看数据字典dba_tablespaces的结构信息
    desc dba_tablespaces;
    查看oracle数据库默认创建的表空间通过数据字典dba_tablespaces
    select tablespace_name,contents,status from dba_tablespaces;
  2. 显示表空间所包含的数据文件
    select file_name,bytes from dba_data_files where tablespace_name=‘SYSTEM’;
  3. 永久性表空间为users,默认临时表空间为temp
  4. 查看oracle用户以及其相关数据表信息
    select * from dba_tables;
  5. 查询system用户的help表信息的语句通过dba_tables视图查看用户的所有数据表的信息
    select * from dba_tables where owner=‘SYSTEM’ and table_name=‘HELP’;

创建表空间

在SQL Plus中输入sys as sysdba命令以sys用户作为sysdba身份登录数据

create tablespace user_zhibao
datafile 'D:\user_zhibao.dbf'
size 100M
autoextend on next 50M
maxsize 1G;

查看表空间

select * from dba_free_space where tablespace_name='USER_ZHIBAO';文件名要大写

创建临时表空间

create temporary tablespace user_zzb
tempfile 'D\zzb.dbf'
size 50M

查看临时表空间

select * from dba_temp_files;

修改表空间

  1. 重命名
    alter tablespace 旧名 rename to 新名
  2. 设置读写状态,只读和读写
    alter tablespace 表名 read only 或 read write
  3. 表空间可用状态
    alter tablespace 表名 online或offline normal或temporary或immediate,联机(可用),脱机(不可用)
  4. 设置默认的表空间
    alter database default tablespace 永久性表空间名
    alter database default temporary tablespace 临时表空间名

删除表空间

把表放到表空间中去

alter table c##scott.zhibao move tablespace USER_ZHIBAO;

表与表结构操作(重点)

新建表

create table zhibao(
id number(4) primary key,
name varchar2(20) not null
);

删除表

drop table ZHIBAO;

查看表结构

  1. DESC :查询表结构
    DESC emp;

使用命令方式操作表(增删改查)

增加字段
alter table emp add image number(2);
修改数据类型
alter table emp modify job varchar(60);
删除列
alter table emp drop column sal;
修改表名
rename table emp to emppp;(旧to新)
删除表
drop table emp;
修改列名
alter table emp rename column sal to salll;

约束

约束(完整性约束条件)
作用:保证数据的准确性
内容:非空、唯一、主键、外键和检查
分类:列级约束、表级约束

约束的语法

  1. 修改约束名称
    alter table 表名 rename constraint 旧名 to 新名
  2. 删除约束
    alter table 表名 drop constraint 约束名

创建约束的语法

  1. 建表时,直接加在列名后面
    id number(4)
  2. 建表后,使用alter table 表名 + add constraint 约束名 约束类型(字段名)
  3. 修改的方式,alter table 表名 modify 字段名 约束类型

唯一约束 unique

含义:某一列的值不允许重复,允许值为空值

  1. 建表时创建
    create table student(
    sid number(4) constraint student_sid_nn not null,
    user_name varchar2(10),
    email varchar2(20),constraint student_email_uk unique(email,column1,,,columnn)
    );
  2. 建表后创建
    alter table test modify ssss unique;
    alter table student add constraint name_uk unique(user_name);
  3. 添加列时创建
    alter table student add salary number(5,2) unique;

主键约束 primary key

  1. 建表的时候
    create table student(
    –列级约束
    id number(2) primary key,

    sid number(4) constraint student_sid_nn not null,
    user_name varchar2(10),
    email varchar2(20),
    表级约束
    constraint student_email_uk unique(email,column1,,,columnn)

    );
  2. 建表后
    alter table test modify column1 primary key;
    alter table test add constraint c1_pk primary key(column1);

非空约束 not null

  1. 创建时
    create table book(bid number(4) not null,
    title varchar2(20)
    );
  2. 建表后
    alter table 表明 modify 字段名 约束类型
    alter table book modify title not null ;
    或者alter table book modify(title varchar(2) not null);
  3. 添加列的时候
    alter table book add author varchar2(10) not null;

默认值约束

  1. 创建时
    create table table_name(column1 data_type default value);
  2. 修改时
    alter table table_name modify 字段名 deafult value;

外键约束

https://blog.csdn.net/wy_0928/article/details/51153191?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163939685316780271979981%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=163939685316780271979981&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2allsobaiduend~default-1-51153191.pc_search_all_es&utm_term=oracle%E5%A4%96%E9%94%AE%E7%BA%A6%E6%9D%9F&spm=1018.2226.3001.4187

emp和dept 是子父关系
外键约束(即引用完整性约束)
子表上定义的外键的列值,必须从主表被参照的列值中选取,或者为null
当主表参照列的值被子表参照时,主表的该行记录不允许被删除

  1. 创建时的语法
    create table student (–学生表
    s_id number(5) primary key,
    s_name varchar2(20),
    t_id varchar2(5),
    constraint fk_stu foreign key (t_id) references team (t_id)

    );
    create table team (–班级表
    t_id varchar2(5) primary key,
    t_name varchar2(20)
    );

其中constraint fk_stu foreign key (t_id) references team (t_id)中,第一个t_id指的是学生表中的t_id,第二个t_id指的是班级表中的t_id
外键约束:是一种子父关系,且可以很好的保护两表之间的关系

  1. 修改时
    alter table dept modify id primary key;
    alter table emp add constraint emp_fk foreign key (deptno)
    references dept(deptno);

检查约束

  1. 检查约束是什么
    检查约束的作用就是让表中的数据更有意义。检查约束就是为了检测和过滤不符合实际意义的数据,比如说年龄1000,工资-500等这些数据。
  2. 在创建表时设置检查约束
    1)列级检查约束
    语法:
    creat table table_name ( column_name datatype check(expressions)) ;
    check是检查关键字
    expressions 是约束的条件

    实例:
    将salary 设置检查约束,使其>0 。
    create table userinfo_c(
    id varchar2(10) primary key ,
    username varchar2(20) ,
    salary number(5,0) check(salary>0)) ;
    2)表级级检查约束
    语法
    constraint constraint_name check(expressions)

    实例
    create table userinfo_c1(
    id varchar2(10) primary key ,
    username varchar2(20),
    salary number(5,0),
    constraint ck_salary check(salary>0) );

7、对c_birth设置检查约束,出生日期必须是2000年以后
//alter table customers_info add constraint customers_c_birth_ck check (c_birth>2000);
//修改后
alter table customers_info add constraint customers_c_birth_ck check (c_birth>to_date(‘2000-01-01’,‘yyyy-mm-dd’)); (注意把表名修改为customers_info)
//删除检查约束
alter table customers_info drop constraint customers_c_birth_ck;

创建检查约束判断age在18到60岁之间的男性或者age在18到55岁之间的女性
alter table customer add constraint c_ck check((sex=‘男’) and (age between 18 and 60) or (sex=‘女’) and (age between 18 and 55));

禁用约束,启用约束

  1. 禁用,约束不起作用
  2. –禁用约束 cascade 可选 如有级联约束的话
    alter table demo_dept4 disable constraint 约束名字 cascade;
    –启用约束
    Alter table demo_dept4 enable constraint 约束名字
    注意:如果插入的数据违反唯一约束 即便执行也是无效

在数据字典中查询约束

索引

概念:

是一个单独的、物理的数据库对象
用于存放表中每一条记录的位置的对象。
在创建索引时,先要对索引字段进行排序。
索引由Oracle自动维护
优点:提高查询速度
缺点:创建和维护索引需要时间;索引需要物理空间,随着数据量的增大而增大;

创建方式

  1. 自动创建:通过约束,系统创建
    手工创建
    create [unique] [bitmap] index index_name on table_name(column_name)
    [reverse] [tablespace tablespace_name]

  2. 创建B树索引
    create index dname_dept on dept(dname);
    默认情况下,创建的索引是不唯一的索引

  3. 创建基于函数的索引
    create index ename_lower_index
    on emp(lower(ename));
    select * from emp
    where lower(ename)=‘smith’;

  4. 创建复合索引
    适合查询where job=’’ and ename=’’;
    create index emp_idx on emp(job,ename);

查看,修改,删除索引

  1. 查看索引
    select * from user_ indexes;是es不是s
  2. 修改索引
    alter index index_name rename to new index_name;
    alter index dept_deptno_pk rename to pk_deptno_dept;
  3. 删除索引
    删除索引与索引创建时采用的方式有关
    手动创建的索引,通过命令删除
    drop index index_name;
    自动创建的索引
    通过禁用约束或删除约束的方式来删除对应的索引。
    删除表,则会完全删除所有索引

序列

概念:

1)是数据库对象。
2)用于产生一系列唯一的数字值
3)序列号的生成独立于表
4)同一序列生成器可用于一个或多个表
5)经常用来产生主键值

创建序列,直接创建复制

create sequence sequence
increment by 10
start with 50
maxvalue 100
cycle
nocache;

删除序列

drop sequence 序列名;

使用序列

伪列的引入
NEXTVAL,nextval 返回下一个有效的序列值.每执行一次会返回一个新的数值。(类似于指针的用法)
CURRVAL,currval返回当前的序列值.

–使用伪列插入记录
insert into students values(sequence1.nextval,‘xiaobai’,‘男’,20);
insert into students values(sequence1.nextval,‘ruyan’,‘女’,19);
select * from students;

注意:在使用伪列时,首先要创建一个序列,具体创建该序列的oracle命令省略

修改序列

修改序列
可以修改增量值,最大值,最小值,循环选项,缓存选项,
不可以修改初始值
序列的最小整数值不能大于当前值
序列的最大整数值不能小于当前值

ALTER SEQUENCE dept_deptno
INCREMENT BY 1
MAXVALUE 999999
NOCACHE
NOCYCLE;

同义词 synonym

  1. 是数据库的别名,通过同义词可以简化对象的访问
  2. 创建
    CREATE [PUBLIC] SYNONYM 同义词名
    FOR 对象名;
    public 选项表示所有用户均可使用.
  3. 删除同义词:
    使用drop synonym

数据类型

  1. 字符类型char(n)和varchar2(n)
    相同点:可以保存数字、字母
    不同点,char长度固定、时间效率,varchar2长度可变、空间效率
  2. 数字类型 number(m,n), number(m)
    m:总的位数,n小数位数。
    整数位数=m-n
  3. 时间类型 date和timestamp
    相同点:可以记录时间
    不同点:date保存时间不含毫秒,后者含毫秒
  4. 大字段类型 clob 和 blob
    相同点:可以存储较大数据
    不同点:clob存放海量文字,blob存放图片,电影音乐的二进制文件
  5. 常用 varchar2 number date clob

体系结构

  1. 完整的oracle数据库是由实例和数据库组成
    实例是由一系列的后台进程和内存区组成的,就是内存和后台进程的集合
    数据库是一系列物理文件的集合

逻辑结构(介绍了一些表空间)

  1. 数据库>表空间>段>数据块
  2. 一个oracle通常会有7个表空间
    system:系统表空间,必须有的
    sysaux:是system的辅助表空间,许多数据库的工具和可选组件将其对象存储在此,
    users:存储用户数据
    undo:事务的回滚,撤销
    temp:oracle运行中需要临时存放的数据,如排序的中间结果

数据字典

  1. 是oracle数据库的核心组件,由基表和数据字典视图组成

数据库的查询和视图

查询语句等操作

select xxx_name, count(*) as counter
from table_y
where where_condition
group by xxx_name
having having_condition
order by zzz

下面列出其执行顺序:
1. 根据where子句选择行;
2. 根据group by 子句组合行;
3. 根据having子句筛选group by分出来的组;
4. 根据order by子句中的分组函数的结果对组进行排序,order by必须使用分组函数或者使用Group by子句中指定的列;

基本查询

查询工资在20003000之间的
<>不包含20003000的,between...and...是包含20003000
select * from emp where sal>2000 and sal<3000;
select * from emp where sal between 2000 and 3000;

日期在这之间的
select * from emp where hiredate between '1-1月-1980' and '31-12月-1980';

查询某列的值在某个列表中的数据行
select * from emp where empno in(1111,7369);

工资为800,1600,3000的信息
select * from emp where sal in(800,16003000);

like字符串匹配
以S开头的信息
select * from emp where ename like 'S%';

含有S的信息
select * from emp where ename like '%S%';

使用函数显示姓名字段的任何位置包含’A’的所有员工的姓名
select ename from emp where instr(ename,'A') !=0;

有五个字符的名字
select * from emp where ename like '_____';

空值查询
select * from emp where comm is null;

查询显示的方式

其中雇佣日期以“19801217日”这样的形式显示,薪水值以“rmb5000.00”这样的形式显示
select ename,to_char(hiredate,'yyyy"年"mm"月"dd"日"') chusheng,'rmb'||to_char(sal,'9999.99') from emp where sal>2000 order by sal desc;
构造sql语句,查询emp表的信息,格式为“员工编码empno,姓名ename,月收入(工资+奖金)”
select '员工编码'||empno||',姓名'||ename||',月收入'||nvl(sal+comm,sal) from emp;

连接查询

  1. 连接查询中on后面的连接条件一般是=,
    还有非等值连接,使用关系运算符>, <, >=, <>, between …and in;
    查找出工资等级不为 4 级的员工的员工名字,部门名字,部门地点。
    select ename,dname,loc,grade from emp join dept on emp.deptno=dept.deptno join salgrade on sal between losal and hisal where grade<>4;

增删改查语句

insert into emp(empno,ename) values(111,'AAA');
delete from emp where ename='AAA';
update emp set ename='bbb' where ename='AAA';
update emp set sal=10000 where ename='AAA';

排序

排序
//空值最大,
select * from emp order by sal asc;   asc可以省略
select * from emp order by sal desc;降序
按照别名排序
select sal*12 as salll from emp order by salll desc;as可以省略
多个列排序
select deptno,sal*12 salll from emp order by deptno, salll desc;,按照deptno正序,按照salll降序
使用表达式排序
select * from emp order by sal*12;sal的值不会变
使用列位置排序
select * from emp order by 1;根据第一列的值排序

聚合函数

count,(对null值不算),avg,sum,可以使用distinct以防数据重复,,,max,min
avg和sum是对数值型数据,min和max是对任何数据类型

有几个组别
select count(distinct(deptno)) from emp;
有几个人,不重复
select count(distinct(ename)) from emp;

分组函数(group by)

分组函数(多行函数)
分组函数作用于一组数据,并对一组数据返回一个值
group by 里面的东西在select里面可以不用存在,但反过来不行。

如果出现嵌套组函数必须有group by 
select round(max(avg(sal))) from emp;错
select round(max(avg(sal))) from emp group by deptno;对

每个组的平均工资,大于2000的
select deptno,round(avg(sal),2) from emp group by deptno having avg(sal)>2000;

group by 多个列
select deptno,job,sum(sal) from emp group by (deptno,job);可以加括号,order by 不可以加

如果select语句中包含组函数,那么查询的不是组函数的列必须出现在group by语句中

having子句

必须在group by语句之后的,也就是分组是前提

连接

rownum获取数据表前若干行

前四行
select * from emp where rownum<5;
35行
select * from (select rownum r,empno from emp where rownum<=5) re where r>=3;

日期类

select * from emp where hiredate<to_date('17-12-1980','DD-MM-YYYY');
生产日期在19801217日之前的商品信息
select * from emp where extract(year from hiredate)>=1980;
select * from emp where to_char(hiredate,'YYYY')>=1980;
1980年之后的

select * from emp where extract(year from hiredate)<=1980;
1980年之前的

视图

创建视图

create view kan as select * from c##scott.dept where deptno=10;

重命名视图

rename 旧视图名 to 新名;

修改视图

alter view

删除视图

drop view 视图名;

函数

数值函数

  1. 1、nvl(value1,value2)
    这个函数的意思是如果value1的值为null,那么函数返回value2的值 ,如果value1不为空,那么就返回value1的值。
    需要注意的是value1和value2要保持字段类型相同。
    2、nvl2(value1,value2,value3)
    这个函数的意思是如果value1的值为null 函数返回value3 否则函数返回value2 也就是说函数永远不会返回value1
2. 分组统计每个部门下,每种职位的平均奖金(也要算没奖金的人)和总工资(包括奖金)
select deptno,job,avg(nvl(comm,0)),sum(nvl2(comm,sal+comm,sal)) from emp group by deptno,job;
  1. 生成随机数并四舍五入
4. 生成一个120之间的随机数,再对其进行四舍五入处理,保留一位小数点。
select round(dbms_random.value(1,20),1) from dual;
  1. 返回绝对值,abs函数
    select abs(-10) from dual;//10
  2. ceil函数,返回比他大的整数
    select ceil(10.4) from dual;//11
    select ceil(-10.4) from dual;//-10
  3. floor函数,返回比他小的整数
    select floor(10.4) from dual;//10
    select floor(-10.4) from dual;//-11
  4. mod函数
    select mod(12,5) from dual;//2
    select mod(12,-5) from dual;//2
    select mod(-12,5) from dual;// -2
    select mod(-12,-5) from dual;// -2
  5. trunk 函数

1.select trunc(sysdate) from dual  --2019-08-20 00:00:00              今天的日期为2019-08-20
2.select trunc(sysdate, 'mm') from dual  --2019-08-01 00:00:00        返回当月第一天.
3.select trunc(sysdate,'yy') from dual  --2019-01-01 00:00:00         返回当年第一天
4.select trunc(sysdate,'dd') from dual  --2019-08-20 00:00:00         返回当前年月日
5.select trunc(sysdate,'yyyy') from dual  --2019-01-01 00:00:00       返回当年第一天
6.select trunc(sysdate,'d') from dual  --2019-08-18 00:00:00          (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual   --2019-08-20 10:00:00       当前时间为10:00 
8.select trunc(sysdate, 'mi') from dual  --2019-08-20 10:05:00        TRUNC()函数没有秒的精确


/***************数字********************/
/* TRUNC(number,num_digits) Number 需要截尾取整的数字。 Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。 TRUNC()函数截取时不进行四舍五入 */
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.40
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual  --123.4580
15.select trunc(123) from dual  --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120

字符函数

  1. substr函数,从第几个位置开始,截取几个,而不是到第几个位置
substr(ename,1,3) :字符截取函数
select substr('abcd',1,2) from dual;//ab
select substr('123456',2,3) from dual;//234
  1. instr函数
instr函数为字符查找函数,其功能是查找一个字符串在另一个字符串中首次出现的位置。
没有找到,instr函数返回0。 
select instr('asd','c') from dual;//0
select instr('asd','s') from dual;//2
  1. translate函数
translate(m,n,s):把m中含有n的换成s
select translate('abccc','c','d') from dual;//abddd
  1. lower,大写变小写
    select lower(‘SDDaa’) from dual;//sddaa
  2. upper,小写变大写
    select upper(‘SDDaa’) from dual;//SDDAA
  3. trim
    a) trim(string); –去除字符串首尾的空格
    select trim(’ dfd fdd ‘) from dual;//dfdfdd
    b) rtrim(string); –去除字符串右侧空格
    select rtrim(’ dfd fdd ‘) from dual;// dfd fdd
    c) ltrim(string); –去除字符串左侧空格
    select ltrim(’ dfd fdd ‘) from dual;dfd fdd
  4. initcap,返回字符串并将字符串的第一个字母变为大写,其他的字母变成小写;
    select initcap(‘HELLO,WORLD’) from dual;//Hello,World
    select initcap(‘hello,world’) from dual;//Hello,World

to_date

select * from emp where hiredate between to_date('17-12-1980','DD-MM-YYYY') and to_date('18-12-1980','DD-MM-YYYY');
两者之间

日期函数

1. 显示满40年服务年限的员工的姓名和雇佣日期
months_between(m,n):用于计算m和n之间有几个月
select ename,hiredate from emp where (months_between(sysdate,hiredate)/12)>40;
select months_between(sysdate,'1-1月-2021') from dual;

2.add_mouths
SELECT ADD_MONTHS( TO_DATE( '2005-07-26', 'yyyy-MM-dd' ), 12) FROM dual;
结果:26-7-06
SELECT ADD_MONTHS( TO_DATE( '2005-07-26', 'yyyy-MM-dd' ), -1) FROM dual;
结果:26-6-05

3.last_day
查询当前月份的最后一天
select last_day(sysdate) from dual;
查询某天所在月份的最后一天:
select last_day(to_date('1992-10-09','yyyy-mm-dd')) from dual;
next_date
select next_day(sysdate,'星期五') from dual;
当前日期的下个星期五的日期

4.current_date
和sysdate一样是当前时间
如果修改当前会话的时区,比如将中国的时区为东八区,修改为东九区,则current_date显示的时间为东九区时间, 根据东加西减的原则,current_date应该比sysdate快一小时.
select current_date,sysdate from dual;

5.extract函数
select extract(day from date'2011-05-17') from dual;//17
select extract(month from date'2011-05-17') from dual;//5

oracle中如何判断某个日期是星期几
SELECT to_char(to_date('2011-03-13','YYYY-MM-DD'),'d') FROM DUAL 
其中 红色部分为传入日期 

to_char的其他用法 
Select to_char(sysdate,'ss') from dual取当前时间秒部分 
Select to_char(sysdate,'mi') from dual取当前时间分钟部分 
Select to_char(sysdate,'HH24') from dual取当前时间秒小时部分 
Select to_char(sysdate,'DD') from dual取当前时间日期部分 
Select to_char(sysdate,'MM') from dual取当前时间月部分 
Select to_char(sysdate,'YYYY') from dual取当前时间年部分 
Select to_char(sysdate,'w') from dual取当前时间是一个月中的第几周(1日开始算) 
Select to_char(sysdate,'ww') from dual取当前时间是一年中的第几周(从1.1开始算) 
Select to_char(sysdate,'iw') from dual取当前时间是一年中的第几周(按实际日历的) 
Select to_char(sysdate,'d') from dual取当前时间是一周的第几天,从星期天开始,周六结束 
Select to_char(sysdate,'day') from dual 取当前日是星期几,和数据库设置的字符集有关,会输出'Tuesday' 
Select to_char(sysdate,'ddd') from dual 当前日是一年中的第几天

PL SQL语句

set serveroutput on (不加分号)

数据类型

%type

v_name emp.ename%type; 表达新的变量名和emp表中的ename类型一样,记录的是一个字段(每一列)

v_name emp.ename%type:='JAMES';
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=v_name;
if(v_sal>1500) then
update emp set comm=comm+100 where ename=v_name;
elsif v_sal>900 then
update emp set comm=800 where ename=v_name;
else
update emp set comm=comm+400 where ename=v_name;
end if;
end;
select * from emp;

%rowtype

针对表中的某一行
这个类型中select语句只能查询所有的东西*,

自定义复合数据类型

declare
type user_type is record(
vname varchar2(20),
vsal number
);
vempno emp.empno%type:=7499;
userinfo user_type;
begin
select ename,sal into userinfo from emp where empno=vempno;
dbms_output.put_line(userinfo.vname||' '||userinfo.vsal);
end;

条件控制语句(循环,判断)

if…then…end if

declare
result number:=50;
begin
if result<60 then
dbms_output.put_line('不及格,成绩为:'||result);
end if;
end;

if…then…else…end if

declare
result number:=50;
begin
if result<60 then
dbms_output.put_line('不及格,成绩为:'||result);
else
dbms_output.put_line('及格,成绩为:'||result);
end if;
end;

if…then…elsif…end if

declare
result number:=&no;
begin
if result>=90 then
dbms_output.put_line('优秀,成绩为:'||result);
elsif result>=60 then
dbms_output.put_line('及格,成绩为:'||result);
else
dbms_output.put_line('不及格,成绩为:'||result);
end if;
end;

case

declare
result number:=&no;
begin
case
when result>=90 then
dbms_output.put_line('优秀,成绩为:'||result);
when result>=60 then
dbms_output.put_line('及格,成绩为:'||result);
else
dbms_output.put_line('不及格,成绩为:'||result);
end if;
end;

loop… exit when… end loop;

declare
i number:=1;
s number:=0;
begin
loop
s:=s+i;
i:=i+1;
exit when i>10;
end loop;
dbms_output.put_line(s);
end;

while…loop…end loop;

declare
i number:=1;
s number:=0;
begin
while i<11
loop
s:=s+i;
i:=i+1;
end loop;
dbms_output.put_line(s);
end;

for…in…loop…end loop;

declare
i number:=1;
s number:=0;
begin
for i in 1..10
loop
s:=s+i;
i:=i+1;
end loop;
dbms_output.put_line(s);
end;

字符函数

字母转化为ASCII值

chr(n):把ASCII码值变成字母
ascii©:把字母变成ASCII码值

set serveroutput on
declare
c1 test.column1%type:=1;
c2 test.column2%type:='a';
c3 test.column3%type:=sysdate;
begin
for i in 1..10
loop
insert into test(column1,column2,column3) values(c1,c2,c3);
--delete from test where column1=i;
c1:=c1+1;
c2:=chr(ascii(c2)+1);
c3:=c3+1;
end loop;
end;
select * from test;
去除空格
declare
name_old varchar2(30):=' sdf dfd ff ';
name1 varchar2(30);
name_new varchar2(30):='';
len number(30);
i number:=1;
begin
name_old:=trim(name_old);//去除两边的空格,空格不占用长度
len:=length(name_old);
while i<len+1
loop
name1:=substr(name_old,i,1);//截取字符name_old,从i开始,截取1个字符
if(length(trim(name1))!=0) then
name_new:=name_new||name1;//连接
end if;
i:=i+1;
end loop;
dbms_output.put_line(name_new);
end;

异常

分为系统异常和自定义异常

异常语法

exception
  when exception_name then 
  statement1
  when exception_name then
  ...
  when others then 一般是最后一条子句,没有捕捉到的都被它捕捉

系统异常

预定义异常

  1. oracle已经预先定义好名称的异常,有对应的错误编号和名字
    在这里插入图片描述

非预定义异常

  1. 在数据库中没有定义异常名称的异常
  2. 通常是数据库错误,只有错误编号,没有错误的异常名称,所以不能直接捕捉到
  3. 使用时,必须先声明一个异常类型的名称,然后通过pragma exception_inin语句为该异常关联错误编号
  4. 步骤:
    定义一个异常名称
    使用伪过程将异常名称和错误编号关联
    pragma
    exception_init(exception_name,oracle_error_number)
    在异常处理部分捕获异常并对异常情况做出相应的处理

自定义异常

异常定义
  在声明部分采用exception关键字声明异常:
   myexception exception
异常引发
   使用raise关键字引发
   raise myexception

DECLARE
  myexception EXCEPTION;
  v_sal emp.sal% TYPE;
BEGIN
  select sal into v_sal from emp where ename='JAMES';
  IF v_sal<5000 THEN
    raise myexception;
  end if;
  dbms_output.put_line('JAMES的工资是:'||v_sal);
EXCEPTION
  when myexception then
    dbms_output.put_line('工资太少了');
  when no_data_found THEN
    dbms_output.put_line('没有这个员工');
END;

游标

游标使用步骤

declare
声明游标: cursor 游标名 is select 语句;
cursor nycursor is select * from emp;
 my_emp emp%rowtype;
begin
打开游标:声明的游标必须打开后才能使用 open 游标名称;--相当于执行查询语句
open mycursor;
使用游标:一般在循环语句中使用fetch语句提取游标中的记录来进行操作
fetch mycursor into my_emp;
  dbms_output.put_line(my_emp.ename||' '||my_emp.sal);
关闭游标:游标使用完毕后,一定要关闭 close 游标名称; 
close mycursor;
end;

创建存储过程

异常的那几个东西

创建:查询指定员工的姓名和薪水,并利用out模式参数值传给调用者。
create or replace procedure asd(eno emp.empno%type,pname out emp.ename%type,psal out emp.sal%type)
as
begin
select ename,sal into pname,psal from emp where empno=eno;
dbms_output.put_line('姓名'||pname||'工资'||psal);
exception
when no_data_found then
dbms_output.put_line('没有');
end;

declare
vno emp.empno%type:=7369;
vname emp.ename%type;
vsal emp.sal%type;
begin
asd(vno,vname,vsal);
asd(eno=>vno,pname=>vname,psal=>vsal);
end;
创建一个存储过程,根据提供的雇员姓名,查询该雇员的上级领导人的姓名,并返回。
create or replace procedure asd(eno emp.empno%type,pname out emp.ename%type)
as
pmgr emp.mgr%type;
begin
select mgr into pmgr from emp where empno=eno;
select ename into pname from emp where empno=pmgr;
end;
declare
vno emp.empno%type;
vname emp.ename%type;
begin
asd(7369,vname);
dbms_output.put_line(vname);
end;

in out

create or replace procedure asd(n1 in out number,n2 in out number)
as
t number;
begin
t:=n1;
n1:=n2;
n2:=t;
end;
set serveroutput on
declare
n1 number:=10;
n2 number:=20;
begin
asd(n1,n2);
dbms_output.put_line(n1||' '||n2);
end;

窗口命令变量绑定调用

var v_name varchar2(20);
var v_sal number;
exec asd(7369,:v_name,:v_sal);

调用

print v_name v_sal;
select :v_name,:v_sal from dual;

游标和存储空间操作

请使用pl/sql编写一个过程,可以输入部门号,并显示该部门所有员工姓名和他的工资.

用游标实现

create or replace procedure pro4(v_deptno number)
is
cursor emp_cursor is select ename,sal from emp where deptno=v_deptno;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
  open emp_cursor;
  loop
  fetch emp_cursor into v_ename,v_sal;
  exit when emp_cursor%notfound;
  dbms_output.put_line('姓名:'||v_ename||'工资:'||v_sal);
  end loop;
  close emp_cursor;
  end;
begin
pro4(10);
end;

写一个函数

create or replace function asdff(eno emp.empno%type)
return varchar2
as
vname emp.ename%type;
begin
select ename into vname from emp where empno=eno;
return vname;
end;
begin
dbms_output.put_line(asdff(7369));
end;

表空间及其操作

  1. 默认的临时表空间是temp
    默认的永久表空间是system
  2. 更改默认临时表空间
    alter database default temprory tablespace
    更改默认永久表空间
    alter datebase default tablespace

查看数据字典的结构信息

desc dba_tablespaces;

查看默认创建的表空间的信息,通过视图查看

select tablespace_name,contents,status from dba_tablespaces;

显示表空间所包含的数据文件

select file_name,bytes from dba_data_files where tablespace_name=‘SYSTEM’;

  1. 查询视图

表管理

CDB和PDB

  1. CDB:容器数据库
    由ROOT SEED PDBS组成
    ROOT存储oracle提供的metadata和common user
    CDB与ROOT之间的关系为一对一;
    SEED用来创建新的PDB的模板,一个CDB只能有一个SEED
    PDBS普通数据库
    一个CDB 只能有一个根
  2. PDB:可插拔数据库
  3. 默认的连接模式是CDB
  4. 进入PDB
    alter session set container=pdborcl;
    startup;
    显示连接容器
    show con_name
  5. 在PDB中查看相关信息
    1)看看有哪些数据文件

今天的文章oracle数据库开发技术分享到此就结束了,感谢您的阅读。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:http://bianchenghao.cn/63737.html

(0)
编程小号编程小号

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注