oracle中将表中时间信息补充全

发布网友发布时间：2022-04-10 01:32

共2个回答

懂视网时间：2022-04-10 05:53

index teachername on teacher(name); create index teachername2 on teacher(tid,age); --可以索引多个字段

创建唯一索引的SQL

create unique index teacherhao on teacher(hao);
create unique index teacherhao2 on teacher(hao,hao2);

删除索引的SQL

drop index teachername;

序列

序列是Oracle提供的用于产生一系列唯一数字的数据库对象。在某些数据表的结构中，有些字段需要这种特征：比如，对于某个学生数据表的学号关键字段，用户可以希望在录入数据时，能够自动在上一个记录的学号字段上自动加1等。

创建序列要有序列权限

grant create sequence,select any sequence to 用户名
--dba权限包括以上权限，所以不用重新赋予

序列的创建

CREATE SEQUENCE sequence --创建序列名称
[INCREMENT BY n] --递增的序列值是n 步长为n
[START WITH n] --开始的值,递增默认是minvalue 递减是maxvalue
[{MAXVALUE n | NOMAXVALUE}] --最大值
[{MINVALUE n | NOMINVALUE}] --最小值
[{CYCLE | NOCYCLE}] --循环/不循环
[{CACHE n | NOCACHE}]; --分配并存入到内存中

SQL例子：

create sequence SEQ4 increment by 1 start with 1 minvalue 1 nomaxvalue nocycle nocache;

序列的使用

create table lvling6(
id number,
name varchar(20)
);

insert into lvling6 values(SEQ4.nextval,‘xiaomi2‘);
select * from lvling6;

删除序列

drop sequence SEQ4

视图

视图是查看数据表中数据的一种方，当一些用户需要经常访问和查询数据表中某些字段构成的数据，但管理员从安全角度考虑又不希望他们直接接触数据表时，可以利用Oracle数据库提供的视图这一数据对象。视图犹如数据表的窗户，用户只能查看他们可以看到的数据，视图不是数据表，它仅是一些SQL查询语句集合，作用是按照不同的要求从数据表中提取不同的数据。

视图优点

因为视图可以有选择性的选取数据库里的一部分，用户通过简单的查询可以从复杂查询中得到结果，维护数据的独立性，视图可从多个表检索数据，对于相同的数据可产生不同的视图

创建视图的SQL

create [or replace] view teacher_view(id,name,age,hao) as select tid,tname,age,hao 
from teacher;
--or replace 代表如果视图存在则替换，不存在则创建一张新的视图

create [or replace] view teacher_student_view(id,name,age,hao,sname,sid) as select t.tid,t.tname,t.age,t.hao,s.name,s.studentid
from teacher t,student s
where t.tid=s.studentid;

用SQL 查看表的视图

select * from teacher_student_view ;
--和查表一样，表名换成视图名即可

修改视图的SQL

create or replace view teacher_student_view(id,name,age,hao,studname,studid) 
as select t.tid,t.tname,t.age,t.hao,s.name,s.studentid
from teacher t,student s
where t.tid=s.studentid;

--通过or replace 重新创建同名视图即可,如果视图存在则替换

修改视图的数据SQL

create [or replace] view teacher2_view(id,name,age,hao) 
as select t.tid,t.tname,t.age,t.hao
from teacher t;

update teacher2_view
set age=20
where id=1;--更新视图表中的数据
--注：在实际开发中，很少对视图中数据进行修改

设置成只读视图

with read only :设置为只读视图

create [or replace] view t_view(id,name,age,hao) 
as select t.tid,t.tname,t.age,t.hao
from teacher t
where age>40
with read only;
--不能插入/更新数据

为视图加限制

WITH CHECK OPTION:即该数据必须满足视图定义中的子查询中的WHERE条件，否则不允许插入或更新

create [or replace] view tab_view(id,name,age,hao) 
as 
select t.tid,t.tname,t.age,t.hao
from teacher t
where age>40
WITH CHECK OPTION; 

--当更新时：
insert into tab_view
values(5,‘lao‘,10,10);
--10<40 报错

删除视图的SQL

drop view tab_view;

事务处理

为什么引入事务

在单用户的数据库系统中，无需对数据的一致性和完整性做过多的考虑。而在多用户并发系统中，多个用户对同一数据的并发访问要求以某种机制来实现数据的一致性和完整性

什么是事务

事务（Transaction）用于保证数据的一致性，它由一组相关的dml语句(增删改语句)组成，该组的dml语句要么全部成功，要么全部失败。如：网上转帐就是用事务来处理，用以保证数据的一致性。

3种事务的处理命令

commit命令:commit是事务的提交命令，数据库为了保证事务的唯一性，一致性，在内存中将为每一个客户建立工作区，客户机对数据库进行操作处理的事务都在工作区内完成，只有在输入commit命令后，工作区内修改内容才写到数据库上，称为物理写入，这样可以保证在任意的客户机没有物理提交个修改以前，别的客户机读取的后台数据库中的数据是完整的，一致的。

set auto on; --在运行中输入sqlplus 后进入dos下，打开自动提交功能
set auto off; --取消自动提交功能

rollback命令:rollback是事务的回滚命令，在尚未提交commit命令之前，如果发现delete、insert、update等操作需要恢复的话，可以使用rollback命令回滚到上次commit时的状态

savepoint命令:savepoint 是保存点命令，事务通常由数条命令组成，可以将每个事务划分成若干个部分进行保存，这样每次可以回滚每个保存点，而不必回滚整个事务

创建保存点：savepoint 保存点名; 回滚保存点： rollback to 保存点名;

Oracle 数据备份与还原

exp命令用于把数据从远程数据库服务器导出至本地,生成dmp文件

imp命令用于把本地的数据库dmp文件从本地导入到远程的Oracle数据库中

将数据库中的表导出,生成dmp文件

exp hh/hh@orcl file=E:/lvling.dmp tables=(lvling)

exp xiaomi7/xiaomi7@192.168.2.57:1521/orcl file=E:/meetingroom.dmp tables=(meetingroom) --远程库

exp hh/hh@orcl file=E:/lvling.dmp tables=(lvling,lvling1)

将生成dmp文件,导入数据库中

imp hh/hh@orcl file=E:/lvling.dmp tables=(lvling)
--注意:导入导出时，用同一用户，同一表空间

将数据库中yy用户表导出,生成dmp文件

exp yy/yy@orcl file=E:/lvling.dmp owner=(yy)

将生成dmp文件,导入数据库中

imp yy/yy@orcl file=E:/lvling.dmp fromuser=yy

将数据库中yy，tt 用户表导出,生成dmp文件

exp system/orcl@XE file=E:/two.dmp owner=(yy,tt)  -- sys得给权限，sysdba权限，否则导不出来

将生成dmp文件,导入数据库中

imp system/orcl@XE file=E:/two.dmp fromuser=tt touser=tt
imp system/orcl@XE file=E:/two.dmp fromuser=yy touser=yy

将orcl整个数据库导出,生成dmp文件

exp yy/yy@orcl file=E:/lvling.dmp full=y

将生成dmp文件,导入orcl整个数据库中

imp yy/yy@orcl file=E:/lvling.dmp full=y ignore=y  --ignore=y忽略创建错误 因为有的表已经存在，然后它就报错，对该表就不进行导入

PL/SQL程序(过程化SQL语言)

需求：创建一个简单的PL/SQL程序向数据库中插入数据

create table lv(sname varchar2(10), spassword varchar2(20))

--创建过程：
create [or replace] procedure lv_one as
begin --执行部分
for i in 1..10 loop
 insert into lv values(‘a‘,‘bb‘);
 end loop;
end;

--调用过程：
exec lv_one;

需求：创建一个简单的PL/SQL程序删除数据库中数据

--创建过程：
create [or replace] procedure lv_two as
begin--执行部分
delete from lv where sname=‘lv1‘;
end;

--调用过程：
exec lv_two;

命名规范

当定义变量时，建议用v_作为前缀 v_sal

当定义常量时，建议用c_作为前缀 c_month

当定义游标时，建议用_cursor作为后缀 emp_cursor

当定义异常时，建议用e_作为前缀 e_error

什么是PL/SQL块

块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是在编写块，要完成简单的功能，可能需要一个块，复杂的功能，要一个块中嵌套另一个块

PL/SQL块由三个部分组成:定义部分、执行部分、异常处理部分

declare 
/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/ 
begin 
/*执行部分——要执行pl/sql语句和sql语句*/ 
exception 
/*异常处理部分——处理运行的各种错误*/ 
end;

实例1：只包括执行部分的pl/sql块

/*只要涉及输入就需要打开输出选项*/
set serveroutput on --打开输出选项 
--set serveroutput off 关闭输出选项

begin 
dbms_output.put_line(‘hello‘);
end;
/--在命令窗口上执行最后得加/

/*相关说明： dbms_output是oracle所提供的包(类似java的开发包)，该包包含一些过程，put_line就是dbms_output包的一个过程。*/

实例2:PL/SQL包含定义部分，执行部分和异常处理部分

declare
 v_ename varchar2(5); --定义字符串变量
 v_sal number(7,2);
begin
 select ename,sal into v_ename,v_sal from scott.emp where empno=&aa; --& 表示要接收从控制台输入的变量,赋值给aa;
 dbms_output.put_line(‘雇员名：‘ ||v_ename||v_sal); 
exception
 when no_data_found 
 /*no_data_found：找不到数据的异常，是Oracle定义好的异常*/
 then dbms_output.put_line(‘编号不对‘);
end;

实例3:需求：输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)

declare 
 c_shuilv number(3,2):=0.03;--税率常量用c
 v_sname varchar2(3); --姓名变量用v
 v_sal number(10); --工资
 v_shui number(10); --个人所得税
begin
 --执行
 select sname,sal into v_sname,v_sal from lv where id=&id;
 v_shui:=c_shuilv*v_sal; --个人所得税
 dbms_output.put_line(‘姓名是‘||v_sname||‘工资‘||v_sal||‘税‘||v_shui);
end;

-- ‘:=‘就是赋值的意思,相当于java中的‘=‘

%type

实例3会产生漏洞，当要查的人员姓名大于v_sname varchar2(3)时会报错

可以使用%type属性定义变量,这样它会按照数据库列来确定你定义的变量的类型和长度

标识符名表名.列名%type;

v_sname varchar2(3); 可以换成v_sname lv.sname%type;

%rowtyp

使用%rowtype,可以使变量获得整个记录的数据类型

declare 
 lvtable lv %rowtype; 
begin
 --执行
 select * into lvtable from lv where id=1;
 dbms_output.put_line(lvtable.sname);
 dbms_output.put_line(lvtable.sal);
end;

if..then..else..end if:相当于if else语句,then后的是如果条件成立执行的语句,语句结束使用end if,同样支持嵌套

loop..exit..end loop:相当于while,配合if..then exit;退出循环,语句结束使用end loop;

for..in..loop..end loop:相当于for语句,for 循环变量 in 循环上界..循环下界 loop 循环处理语句段; end loop;

goto labelOffLOOP(标签名):跳到标签处,标签定义语句：<<labelOffLOOP>>

Oracle PL/SQL过程

需求：创建一个PL/SQL过程修改员工的工资

create [or replace] procedure lv_three (lvName in varchar2,newSal number) as --默认不写时是in
begin
 update lv set sal=newSal where sname=lvName;
end;

--调用过程：
exec lv_three(‘jerry‘,4000);
--或
call lv_three(‘jerry‘,4000);

--注：当遇到error时，用show error;查看错误
--类似于函数调用，过程可以被另一个过程调用

需求：创建一个PL/SQL过程查询员工的姓名

create [or replace] procedure lv_four (sid in number,stname out varchar2) 
as --或is 
begin
 select sname into stname from lv where id=sid;
end;

--调用过程：
declare
 ming varchar(10);
 sid number(10);
begin
 sid:=1;
 lv_four (sid,ming);
 dbms_output.put_line(ming);
end;

需求：创建一个PL/SQL过程查询员工的年工资

create or replace procedure getsal(saly in out number) as
begin
 select sal *12 into saly from lv where sal=saly;
exception
 when no_data_found 
 then dbms_output.put_line(‘没有找相关数据‘);
end;

--调用过程：
declare
 ssal number(10);
begin
 ssal:=1;
 getsal(ssal);
 dbms_output.put_line(ssal);
end;

删除过程

drop procedure 过程名;

oracle补充

标签：type属性 sql 独立作用 index uniq cep while 角度

热心网友时间：2022-04-10 03:01

Select to_char(a+1-Id,'YYYYMMDD') From (
SELECT Rownum Id,to_date('20160715','YYYYMMDD') a FROM DUAL
CONNECT BY LEVEL <= to_date('20160715','YYYYMMDD') - to_date('20160115','YYYYMMDD') +1) Order By a

思路，先生成列，然后在递增，递增采用rownum的方式