Oracle详细使用
-
学习Oracle的使用（和mysql使用规则一样用“略”代表）
-
select语句的使用：略。
-
create创建表的使用：略。但是可以在表名前可以添加模式名称，可以复制表作为一个新表
create table table_name as (
select * from other_name
); -
alter修改表结构的使用：
-
增加一列：略。
-
修改一个或者多列的属性或者约束：略。另外Oracle提供【不】可见属性[in]visible
-
删除一列或者多列（物理删除）
alter table table_name drop (col1);
alter table table_name drop(col1, col2) -
修改列名名称：
alter table table_name rename column old_name to new_name; -
修改表名名称：
alter table table_name rename to new_name
-
-
删除表数据drop：
-
可以将表数据删除放置回收站中，若在后面添加purge，可以完全删除
drop table table_name purge; -
oracle还提供了表级联约束条件。多个表如果有关联，则必须先删除子表，后删除父表。
-
Oracle提供删除多个表的功能
begin for rec in (select table_name from all_tables where table_name like 'test_%') loop execute immediate 'drop table ' || rec.table_name || 'cascade constrains'; end loop; end; /
-
-
删除列set unused column:（逻辑删除）
-
作用是将选中的列设置不可用，这样在数据操作时这列的数据是不可见的
alter table table_name set unused column column_name; -
执行完上述操作之后，可以以另一种方式物理删除列
alter table table_name drop unused columns;
-
-
-
学习Oracle的使用
-
modify修改列
-
在修改列的过程中，不能随意修改类型长度
alter table table_name modify column_name 类型(长度) -
若文本的长度大于设置的长度，执行时就会报错，可以使用替代函数裁剪数据（类型是varchar2）：
update tahle_name
set column_name=replace(column_name, "替代前str", "替代后str") -
可以结合表达式给列设置值
alter table table_name modify column_name 类型(长度) generated always as (生成值的表达式)
-
-
表名称重命名rename
rename table_name to new_namerename table table_name to new_name -
数据类型
-
数值类型NUMBER
-
number(pre[, next]) 第一个参数表示整数的位数，第二个参数表示小数的位数，即可以表示整数和小数， 若小数的位数超过自定义的位数，系统会四舍五入。number的表现范围mysql的int，smallint，number，decimal 另外FLOAT类型是数值类型的子类
-
-
字符串类型char、nchar、varchar2、nvarchar2其实和mysql的char、varchar的语义是一样的，只不过带n表示存储 当前国家的Unicode码
-
日期类型DATE
-
日期类型转换为字符串时间to_char(date类型)
-
字符串时间转换为日期类型to_date("字符串时间", "字符串格式")
-
在插入语句涉及日期时，可以DATE指明字符串时间，或者使用to_date
-
-
-
数据操作
-
插入数据
-
单条数据插入：略。
-
多条数据插入
-
在有条件时，all代表的意思是如果都满足条件，都要执行插入操作；first即只在第一次满足条件时插入数据
-
无条件：
insert all into table_name(col1, col2) values (val1, val2) into table_name(col1, col2) values (val1, val2) into table_name(col1, col2) values (val1, val2) 子查询语句;
-
有条件：
insert first when condition1 then into table_name(col1, col2) values (val1, val2) when condition2 then into table_name(col1, col2) values (val1, val2) else into table_name(col1, col2) values (val1, val2) 子查询语句;
-
-
-
更新语句update和删除语句delete： 略。 在删除数据时，若两表之间存在关联，必须先删除子表中的数据，后删除父表的数据； 若在创建子表的过程中指定on delete cascade表级约束，在删除父表时，会连同与子表关联的数据都会删除
-
-
数据合并 merge语句就是同时执行多个语句（操作对象就是目标表），并将得到的数据集合并在目标表中， merge语句的语法：
python """ merge into target_table using source_table on search_condition when matched then 数据操作1 when not matched then 数据操作2 """如果数据操作是更新，尽量不要操作同一行数据 如果数据操作是删除，则会删除on条件和where条件匹配的行记录 -
数据排序order by：略。另外Oracle提供了nulls last约束，可以将null的行记录放置在最后
-
数据去重distinct：略。
-
数据集记录数限制： 12版本以上使用fetch next 数量 rows [only / with ties], 12版本以下使用where rownum <= 数量 可以在前面添加偏移量offset 数量，和mysql操作相同
-
通配符%和：略。如果匹配%或者 则使用escape str格式化后面一个字符即可：
select * from table_name where column_name like '%25!%%' escape '!' -
group by分组语义和mysql一样，只不过group by语句是在where条件之前
-
having语句是和group by搭配使用，它们是连在一起的
-
-
学习Oracle的使用
-
数据操作符
-
exits的返回值是True or False，也是存在的意思，如果是针对性的查询数据建议使用in语句
-
any用法其实和JavaScript的some方法的语义一样
-
all用法其实和JavaScript的every方法的语义一样
-
union的用法和mysql一样。 两个数据集的列数和类型一定要相等，列名可以不同，
select name1, add_time from table_name1 t1 union select name2, add_time from table_name2 t2
-
intersect行记录交集 两个数据集的列数和类型一定要相等，列名可以不同 帅选的都是行记录和列名相同的行保存
-
minus行记录差集 两个数据集的列数和类型一定要相等，列名可以不同 帅选的都是行记录和列名相同的行保存
-
-
数据表连接 on条件执行完主表的数据不变，该连接的还是得连接 where条件执行对象是在多表连接完后的数据集
-
inner join内连接，using(column)等同于on后字段值相等，column必须存在于两个表中
select * from table_name1 t1 inner join table_name2 t2 on t1.id = t2.id select * from table_name1 t1 inner join table_name2 t2 using(id)
-
left join左连接：略
select * from table_name1 t1 left join table_name2 t2 on t1.id = t2.id and t1.name = "str" select * from table_name1 t1 left join table_name2 t2 on t1.id = t2.id where t1.name = "str"
-
right join右连接：略
-
cross join笛卡尔连接，这个可以没有条件
select * from table_name1 t1 cross join table_name2 t2
-
自连接：指的是两张相同的表某一列或者多列进行内连接
-
-
数据库约束
-
主键：
-
列级约束：略
-
表级约束：略。但是若指定主键约束名，表示方式：constraint primary_key_name primary key (col1, ...)
-
添加主键：
alter table table_name add constraint primary_key_name
primary key (col1) -
删除drop/启用enable/禁用disable主键：
alter table table_name drop constraint primary_key_namealter table table_name drop primary key
-
-
外键：
-
只能是表级约束：foreign key(column) references main_table(main_column) on delete [cascade/set null]
-
添加外键：
alter table table_name add constraint foreign_key_name
foreign key (col1) references table_name(col1) -
删除drop/启用enable/禁用disable外键：
alter table table_name drop constraint foreign_key_name
-
-
not null：略
-
unique：
-
列级约束：略
-
表级约束：略。但是若指定唯一约束名，表示方式：constraint unique_name unique (col1, ...)
-
添加唯一：
alter table table_name add constraint unique_name
unique (col1) -
删除drop/启用enable/禁用disable唯一：
alter table table_name drop constraint unique_name
-
-
检查约束check 只有符合check后的表达式的要求才能更新或者新增数据
-
列级约束：column_name data_type check (expression)
-
表级约束：若指定check约束名，表示方式：constraint check_name check (expression)
-
添加主键：
alter table table_name add constraint check_name check (expression)
-
删除drop/启用enable/禁用disable主键：
alter table table_name drop constraint check_name alter table table_name drop primary key
-
-
-
过程
-
创建过程 """ create [or replace] procedure procedure_name [(parameter[, parameter])] is declare [declaration_section] begin executable_section [exception exception_section] end
[procedure_name]; """ create or replace procedure insert_user (id in user.id%type, name in user.name%type, res out number, res_msg out varchar2(20)) is begin res:=1; res_msg:='插入数据成功'; insert into user values (id, name); exception when dup_val_on_index then res:=-2000; res_msg:='插入数据重复'; when others then res:=sqlcode; res_msg:=sqlerrm; end insert_user; /
-
调用过程
declare res number; res_msg varchar2(20); begin insert_user(101, '胡先森', res, res_msg); dbms_output.put_line('状态码' || res || ', 状态信息' || res_msg); end; /
-
删除过程
drop procedure insert_user
-
-
游标
-
游标声明
-
无参数游标：该游标的结果集是所有的course_name并存在c1中，其course_name与name_in匹配
cursor c1 is select course_number from course where course_name = name_in; for data in c1 loop: ... end loop;
-
带参数游标：该游标的结果集是所有的course_number，其subject与通过参数传递给 游标的subject_name相匹配
cursor c2(subject_name in varchar2) is select course_number from course where subject = subject_name
-
带return子句的游标：返回值是course表的科目是chinese的所有列
cursor c3 return course%rowtype is select * from course where subject = 'chinese'
-
-
游标打开
open c1; -
游标提取 fetch cursor_name into variable_list; cursor_name：游标名称 variable_list：游标所需要的参数
fetch c1 into output_number; -
游标关闭
close c1; -
游标属性
-
%ISOPEN
-
如果光标处于打开状态，则返回TRUE;如果光标处于关闭状态，则返回FALSE。
-
-
%FOUND
-
如果声明了游标，但不打开，则返回INVALID_CURSOR，或者游标已关闭。
-
如果游标处于打开状态，则返回NULL，但未执行提取。
-
如果执行成功，则返回TRUE。如果没有行被返回，则返回FALSE。
-
-
%NOTFOUND
-
如果声明了游标，但不打开，则返回INVALID_CURSOR，或者游标已关闭。
-
如果游标处于打开状态，则返回NULL，但未执行提取。
-
如果执行了成功的提取，则返回FALSE。 如果没有行被返回，则返回TRUE。
-
-
%ROWCOUNT
-
如果声明了游标，但不打开，则返回INVALID_CURSOR,或者光标已关闭。
-
返回获取的行数。
-
除非遍历整个游标，否则ROWCOUNT属性不会给出真正的行数。 换句话说，不应该依赖这个属性来告诉游标在打开后有多少行。
-
-
create or replace function select_all (name_in in varchar2) return number is output_number number; cursor c1 is select course_number from course where course_name = name_in; begin open c1; fetch c1 into output_number; if c1%notfound then output_number := 9999; end if; close c1; return output_number; end;
-
-
-
学习Oracle的使用
-
mysql触发器 语法： create trigger trigger_name after/before insert/update/delete on table_name for each row begin sql语句; end; 注：这里的new指的是order_table新增一行数据的对象,一般是after之后的操作对象；old一般指的是before之前的操作对象
create or replace trigger insert_trigger before update on goods_table for each row declare num number; begin insert into order_table values (:gid, num); end; /
-
oracle触发器 instead of 只能作用于视图中的行级触发器上 语法： create [or replace] trigger trigger_name after/before/instead of [insert [or update [or delete]]] on table_name [for each row] 行级触发器 [declare 变量声明] begin [可以夹带条件判断，如下] [if inserting then] 数据操作语句 [elsif updating then] 数据操作语句 [elsif deleting then] 数据操作语句 [end if;] exception 发生异常时执行的语句 end;
create or replace trigger insert_trigger before update on goods_table for each row declare num number; begin insert into order_table values (:gid, num); end; /
主键自增触发器
create or replace trigger auto_increment before insert on table_name for each row declare -- local variables here begin select seq_id.nextval into :new table_id from dual; end auto_increment; insert into table_name(col1, col2, col2);
表级触发器：和上述语法及操作一样，只不过没有for each row，而且是不能使用:old和:new
-
create or replace trigger insert_trigger before update on goods_table begin insert into order_table (id, num) select gid, num from goods_table; end;
/
