后台开发时常有Oracle相关操作总结

最新推荐文章于 2022-07-20 17:01:56 发布

张斌的书房

最新推荐文章于 2022-07-20 17:01:56 发布

阅读量649

点赞数 1

文章标签：数据库

本文链接：https://blog.csdn.net/bryant0917/article/details/83451494

版权

最近工作兼着做java后台开发去了，第一次做，发现核心的逻辑都是数据库操作，开发时很多基本数据库操作语句都不会写了，基本都是去搜索，下面分享总结我常用到的数据库基本操作，一些高级的还没有用到，以后用到再补上。

1、增删改查操作

**
**

Oracle增加记录（向表中插入数据）-五种写法

insert into 表名 values(值1，值2，…);

insert into 表名(列1，列2，…)values(值1，值2，…);

insert into 表名2(列1，列2，…)select 值1，值2，… from 表名1; （表2必须存在，列1，列2，…必须存在）

insert into 表2 select * from 表1;

select 值1，值2，… into 表名2 from 表名1; （表2不存在，插入时会自动创建表名2）

Oracle删除记录（把表中数据删除）-五种写法

DELETE FROM <table/view> [WHERE ]

实例：
–删除部门编号为’2BSB’的部门信息
delete from gem_file where gem01 = ‘2BSB’; --where还可以用子查询作为condition

–刪除所有部門信息
delete from gem_file; 删除整个表所有数据，delete并不会释放表所占用的空间

–刪除所有部門信息
truncate table gem_file;

–刪除表数据包括表结构
drop table gem_file;

oracle中删除表中的一列语句
alter table 表名 drop colum 列名

truncate和 delete只删除数据不删除表的结构(定义)

drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.

Oracle更改记录（把表中数据更改）-七种写法

1）最简单的更新
　　update tablea a set a.price=1.00

2）带条件的数据更新
　　update tablea a set a.price = 2.00 where a.id=‘02’

3）两张表关联更新为固定值
　　update tablea a set a.price =3.00 where exits(select 1 from tableb b where a.id=b.id)
　　将a,b相同id的 a表的price 字段更新为 3.00

4）关联更新数据来源第二张表
　　update tablea a set a.price=(select price from tablec c ) where exits (select 1 from tablec c where a.id=c.id)
　　将a表price字段更新为 id和c表id相同的数据

5）关联更新多个字段
　　update tablea a set ( a.price,a.type)=(select c.price,c.type from tablec c ) where exits (select 1 from tablec c where a.id=c.id)
　　更新a表的price 和 type 字段　
　
6）使用视图方式更新
　　update (select a.price old,c.price as new from tablea a ,tablec c where a.id=c.id) set old=new

7）MERGE 在SQL Server、Oracle数据库中可用，MySQL、PostgreSQL中不可用。可以同时进行更新和插入操作。执行效率要高于INSERT+UPDATE。
　　
　　示例
　　merge into tablea a ----要更新或者操作的表
　　using tablec c 　　　　----源表 using (select * from tablec ) c
　　on a.id=c.id　　　　　　--匹配条件
　　when matched then set a.price=c.price --当匹配时进行更新操作
　　when not matched then 　　　　--不匹配进行插入操作
　　insert values values(c.id,c.price)

使用update更新100行数据都需要6-8S 使用merge 更新全部数据（13W+ 与13W+ 关联）只用了10S左右。更新效率可见要比update高很多。

Oracle查询记录（查询表中数据）-五种基本写法

**
查询所有数据
select * from person;

查询所有数据的总记录数量
SELECT COUNT(1) FROM person；
SELECT COUNT(*) FROM person；

带多个条件的查询
select id，name，sex，birthday from person
WHERE name= ’张三’ AND sex= ’男’;

带多个条件的查询按顺序排序
select id，name，sex，birthday from person
WHERE name= ’张三’ AND sex= ’男’ order by birthday ;

Oracle表的基本操作

**
–创建表
create table userinfo
(
id number(6,0),
usernam varchar2(20),
userpwd varchar2(20),
email varchar2(30),
regdate date
);

添加一个字段remark字段,是varchar2类型,长度为100
alter table userinfo
add remarks varchar2(100);

给字段添加注释
comment on column new_uesrinfo.remarks is ‘注释’;

–给字段改名
alter table table_name
rename column column_name To new_column_name;

–修改表的名字
rename table_name to new_table_name;

删除字段
alter table table_name
drop column column_name;

修改remarks字段修改字段的长度

alter table userinfo
modify remarks varchar2(150);

–删除表数据
truncate table table_name;

delete from table_name

–删除表的结构
drop table table_name;

存储过程

create or replace procedure delete_emp
(id scott.emp.empno%type)
is
begin
delete from scott.emp where empno=id;
exception
when others then
dbms_output.put_line(‘errors’);
end；

存储函数

–查询某个员工的年收入
create or replace function Fupmoney(tname in varchar2)
return number
as
–定义月薪参数
tmoney test_procedure.money%type;
begin
–得到月薪
select t.money
into tmoney
from test_procedure t
where t.name = tname;

dbms_output.put_line(tmoney*12);

return(tmoney*12);

end;

存储过程和存储函数相当于一个东西。
存储过程在Oracle里叫procedure。
存储过程没有返回值。
存储函数在Oracle里叫function。
存储函数有返回值。

游标的使用，遍历

et Serveroutput On

declare
v_empno emp.empno%type;
v_ename emp.ename%type;
v_salary emp.sal%type;
cursor emp_cursor is
select empno,ename,sal from emp where deptno = 20;
begin
open emp_cursor;
loop
fetch emp_cursor into v_empno,v_ename,v_salary;
exit when emp_cursor%notfound;
dbms_output.put_line(‘编号为’||v_empno||‘的员工名字为’||v_ename
||’，他的薪资为’
||v_salary);
end loop;
close emp_cursor;
end;

创建定时器

每天9点钟执行存储过程 proce_autofeedback
declare jobno number;
begin
dbms_job.submit(jobno,‘proce_autofeedback;’, sysdate, ‘TRUNC(SYSDATE + 1) + （960）/(2460)’);
commit;
end;

每分钟执行一次
declare jobno number;
begin
dbms_job.submit(jobno,‘proce_autofeedback;’, sysdate, ‘TRUNC(SYSDATE ，“mi”) + 1/(24*60)’);
commit;
end;

部分规则为

每天午夜12点 ‘‘TRUNC(SYSDATE + 1)’’

每天早上8点30分 ‘‘TRUNC(SYSDATE + 1) + （860+30）/(2460)’’

每星期二中午12点 ‘‘NEXT_DAY(TRUNC(SYSDATE ), ‘’’‘TUESDAY’’’’ ) +12/24’’

每个月第一天的午夜12点 ‘‘TRUNC(LAST_DAY(SYSDATE ) + 1)’’

每个季度最后一天的晚上11点 ‘‘TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ),’‘Q’’ ) -1/24’’

每星期六和日早上6点10分 ''TRUNC(LEAST(NEXT_DAY(SYSDATE,

‘’’'SATURDAY"), NEXT_DAY(SYSDATE, “SUNDAY”))) + （6×60+10）/（24×60

）’’

查看定时任务的job号
select job, next_date, next_sec, failures, broken from user_jobs;

停止一个已启动的定时任务：
begin
dbms_job.broken(25, true, sysdate);
commit;
end;