Oracle 初次相见
drop tablespace s_space including contents and datafiles; --删除表空间 |
create tablespace s_space --创建表空间 |
datafile 's_space' size 100m --指定表空间的大小 |
autoextend on ; --设置为自增长 |
|
create user root --创建名称为root的用户 |
identified by root --用户的密码是root |
default tablespace s_space --设置这个用户的默认表空间 |
temporary tablespace temp ; --设置这个用户的临时表空间 |
|
grant connect ,resource to root; --授予root用户的连接和资源使用权限 |
grant sysoper to root; --将系统操作权限授予此用户 |
select username, password from dba_users; --从系统表中查询出用户名和密码(加密) |
select username, password from dba_users where username= 'ROOT' ; |
alter user root identified by cmcc; --修改用户的密码 |
|
alter table tb_student rename column birthday to birth; -- 修改 tb_student 表的birthday字段 |
|
drop table tb_cmcc; --删除表 |
commit ; --提交事务 对数据进行增删的时候 注意要commit |
create table tb_cmcc( ---小括号 ------------------------- 创建表 |
stu_id char (12) primary key , |
stu_name varchar (50) not null , |
sex varchar (5), |
birthday date --最后一行无 逗号 |
) |
------------------------------游标:相当于指针,------------------------------------- |
-- 游标分为两类:静态游标和REF游标,前者分为显式游标和隐式游标,后者是一种引用类型 |
--隐式游标: %FOUND %NOTFOUND %ISOPEN $ROWCOUNT |
--如果执行select语句,则可以通过sql%rowcount检查受影响的行数; |
---通过检查sql%found是否为true来检查是否检索到数据 |
--当使用游标的属性时,需要在属性前加SQL ,因为oracle在创建隐式游标时,默认的游标名为SQL |
--------------------------隐式游标------------------------ |
insert into tb_cmcc (stu_id ) values ( '1' ); ---插入数据 |
|
begin |
update tb_cmcc set stu_name = 'lenovo游标' where stu_id = '1' ; |
--select * from tb_cmcc where stu_id='1'; |
if sql%notfound then |
dbms_output.put_line( '未更新记录' ); |
else |
dbms_output.put_line( '更新成功' ); |
end if; |
end ; |
|
select * from tb_cmcc |
---------------------------------- |
begin |
for h_cursor in ( select * from tb_cmcc) loop --对指针进行循环 |
dbms_output.put_line( 'id :' || h_cursor.stu_id); |
dbms_output.put_line( 'name :' || h_cursor.stu_name); |
dbms_output.put_line( 'sex:' || h_cursor.sex); |
dbms_output.put_line( 'birthday ' || h_cursor.birthday); |
dbms_output.put_line( '--------------------' ); |
end loop; |
end ; |
--------------------------显式游标------------------------ |
insert into tb_cmcc (stu_id,stu_name,sex,birthday) values ( '1' , 'lenovo' , '男' ,sysdate); |
|
declare |
cursor c_cursor(sex_param varchar2) is --声明一个游标 |
select stu_id, stu_name, sex, birthday |
from tb_cmcc |
where sex = sex_param; |
type cmcc is record( -----------------------------定义一个数据类型 |
s_id tb_cmcc.stu_id%type, --表明s_id的类型与tb_cmcc表的stu_id类型一样 |
s_name tb_cmcc.stu_name%type, |
s_sex tb_cmcc.sex%type, |
s_birthday tb_cmcc.birthday%type); |
cmcc_row cmcc; |
begin |
open c_cursor( '男' ); ------打开游标 |
fetch c_cursor into cmcc_row; ---抓取游标 |
while c_cursor%found loop --对游标进行循环 |
dbms_output.put_line( 'id :' || cmcc_row.s_id); |
dbms_output.put_line( 'name :' || cmcc_row.s_name); |
dbms_output.put_line( 'sex:' || cmcc_row.s_sex); |
dbms_output.put_line( 'birthday ' ||cmcc_row.s_birthday); |
dbms_output.put_line( '--------------------' ); |
fetch c_cursor into cmcc_row; |
end loop; |
end ; |
-------------------------------------------------------------------------- |
--------------------------------------- |
create user cmcc identified by admin; |
grant connect to cmcc; |
grant resource to cmcc; |
--------------------- |
create table tb_cmcc( ---小括号 |
stu_id char (12) primary key , |
stu_name varchar (50) not null , |
sex varchar (5), |
birthday date --最后一行无 逗号 |
) |
|
select * from tb_cmcc; |
|
insert into tb_cmcc(stu_id,stu_name,sex,birthday) values ( '12' , 'lenovo' , '男' ,to_date( '1989-09-19' , 'yyyy-mm-dd' )); |
commit ; --事务提交 |
rollback ; --事物回滚 |
|
insert into tb_cmcc (stu_id,stu_name) values ( '2' , 'IBM' ); |
commit ; |
update tb_cmcc set sex= '男' ,stu_name= 'Intel' ,birthday=to_date( '20091026' , 'yyyy-mm-dd' ) where stu_id= '2' ; |
commit ; |
insert into tb_cmcc values ( '3' , 'Windows' , '女' ,to_date( '01121995' , 'mm-dd-yyyy' )); |
commit ; |
-------------------- 简单的存储过程----------------- |
create or replace procedure printcmcc is ---创建或替换一个存储过程 |
begin |
dbms_output.put_line( '...begin...' ); |
for emp_cur in ( select c.stu_id,c.stu_name from tb_cmcc c) loop |
dbms_output.put(emp_cur.stu_id || '' ); |
--null; |
end loop; |
end ; |
-------------------- |
begin |
-- Call the procedure |
printcmcc; --刚才创建的存储过程 |
end ; |
-------------------- |
|
select * from session_privs; |
grant execute any procedure to cmcc; |
---------------------while------------------------------- |
create or replace procedure testP is |
num number :=1; |
begin |
while num<100 loop |
if mod(num,3)=0 then |
dbms_output.put(num || ' ' ); |
end if; |
num:=num+1; |
insert into tb_cmcc (stu_id,stu_name) values (num,to_char(sysdate, 'yyyy-mm-dd' )); |
end loop; |
end ; |
|
begin |
testP; |
end ; |
select * from tb_cmcc; |
------------------------------------------------------- |
select * from session_privs |
------------------- for ----------------------------- |
create or replace procedure ceshi is |
num number default 0; |
begin |
for i in 0..10000000 loop |
num:=i+1; |
insert into tb_cmcc (stu_id,stu_name) values (num,to_char(sysdate, 'yyyy-mm-dd' )); |
end loop; |
dbms_output.put_line(num); |
end ceshi; |
|
select * from tb_cmcc; |
delete from tb_cmcc; |
begin |
-- Call the procedure |
ceshi; |
end ; |
--------------------IN 参数----------------------- |
create or replace procedure testIn( |
tb_id in varchar2, |
tb_name in varchar2, |
tb_sex in varchar2, |
tb_bir in date ) is |
begin |
insert into tb_cmcc values (tb_id,tb_name,tb_sex,tb_bir); |
end testIn; |
--------------- |
declare |
tb_id varchar2(20):= '名称表示法 1' ; |
tb_name varchar2(20):= '12345' ; |
tb_sex varchar2(20):= '12345' ; |
tb_bir date :=sysdate; |
begin |
testIn(tb_id,tb_name,tb_sex,tb_bir); |
end ; |
------------------ |
begin |
testIn(tb_id => '名称表示法 2' ,tb_name=> '147' ,tb_sex=> '1' ,tb_bir =>sysdate); |
end ; |
-------------------------------------------------------- |
begin |
testIn( '位置法' , '147' , '1' ,sysdate); |
end ; |
-------------------------------------------------------- |
create or replace procedure inAndOut( |
tb_id in varchar2, |
id_o_param out tb_cmcc.stu_id%type, |
name_o_param out tb_cmcc.stu_name%type ) is |
begin |
dbms_output.put_line(tb_id || ' ' ); |
select stu_id ,stu_name into id_o_param,name_o_param from tb_cmcc where stu_id= '1' ; |
dbms_output.put_line(name_o_param || ' ' ); |
exception |
when no_data_found then |
id_o_param:= 'null' ; |
name_o_param:= 'null' ; |
dbms_output.put_line( 'can not found ' ); |
end ; |
|
insert into tb_cmcc(stu_id,stu_name) values ( 'outParam' , 'sql' ); |
insert into tb_cmcc(stu_id,stu_name) values ( '1' , 'sql' ); |
select * from tb_cmcc where stu_id= '1' ; |
|
declare |
tb_id varchar2(20):= '1' ; |
id_o_param varchar2(10); |
name_o_param varchar2(10); |
begin |
inAndOut(tb_id,id_o_param,name_o_param ); |
end ; |
-----------------------InOut------------------------ |
create or replace procedure swap( |
num1_param in out number, |
num2_param in out number |
) is |
var_tmp number; |
begin |
var_tmp:=num1_param; |
num1_param:=num2_param; |
num2_param:=var_tmp; |
end swap; |
|
declare |
var_max number:=23; |
var_min number:=46; |
begin |
if var_max<var_min then |
swap(var_max,var_min); |
end if; |
dbms_output.put_line(var_max || '>' || var_min); |
end ; |
-------------------------------------------------------- |
-----------------------------------------------函数---------------------------------------------------- |
return number is |
res number; |
begin |
res:=(num1+num2)/2; |
return (res); |
end average; |
----- |
declare |
avg_number number; |
begin |
avg_number:=average(45,789); |
dbms_output.put_line(avg_number); |
end ; |
-----------------------------存储过程分页---------------------------------- |
create or replace procedure sc(st_id in number,en_id in number,c_emp out sys_refcursor) is |
v_sid t_emp.sid%type; |
begin |
open c_emp FOR select sid from t_dmp where rownum between st_id and en_id ; |
end ; |
|
|
declare |
st_id number:=1; |
en_id number:=5; |
v_sid t_emp.sid%type; |
c_emp sys_refcursor ; |
begin |
sc(st_id,en_id,c_emp); |
loop |
fetch c_emp into v_sid; |
exit when c_emp%notfound; |
dbms_output.put_line(v_sid); |
end loop; |
end ; |
|
select rownum,sid from t_dmp where rownum between 1 and 5; |
|
---------------------------------------------------触发器------------------------------------------------- |
create table tb_cmcc_log( |
who varchar2(30), |
when date ); |
|
create or replace trigger cmcc_op |
before insert or update or delete |
on tb_cmcc --当对tb_cmcc进行 插入 更新 删除操作的时候,就会触发cmcc_op这个触发器 |
begin |
insert into tb_cmcc_log(who , when ) values ( user ,sysdate); |
end ; |
------ |
create or replace trigger cmcc_opp |
before insert or update or delete |
on tb_cmcc |
declare |
var_action varchar2(50); |
begin |
if inserting then |
var_action:= 'insert' ; |
elsif updating then ----- 是elsif 不是 elseif |
var_action:= 'update' ; |
elsif deleting then ----- 是deleting 不是 deleteing |
var_action:= 'deleteing' ; |
end if; |
insert into tb_cmcc_log(who, when , action ) values ( user ,sysdate,var_action); --log |
end ; |
------ |
|
----------------修改列长度------------------- |
alter table tb_cmcc modify ( stu_id char (50) ) |
--------------------增加字段------------------- |
alter table tb_cmcc_log add action char (20); |
--------------------删除字段------------------- |
alter table tb_cmcc drop column action |
insert into tb_cmcc(stu_id,stu_name) values (to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss' ), 'sql' ); |
update tb_cmcc set stu_name= 'sql!@#@#@' where stu_name= 'sql' ; |
delete tb_cmcc where stu_name= 'sql!@#@#@' |
select * from tb_cmcc_log; |
------------------行级触发器----------------- |
create table foo (sid number ,sname varchar2(20)); |
|
create sequence seq_foo; |
---------------- |
create or replace trigger foo_trigger |
before insert or update of sid |
on foo |
for each row --行级触发器 |
begin |
if inserting then |
select seq_foo.nextval into :new.sid from dual; --delete 没有前映像:old,只能使用new,delete 与此相反,而update则都有 |
else |
dbms_output.put_line( 'can not update the value of id' ); |
end if ; |
end ; |
---------------- |
insert into foo (sid ,sname) values (1, 'cmcc' ); |
insert into foo (sname) values ( 'admin' ); |
commit ; |
drop table foo; |
delete from foo; |
select * from foo; |
------------------------------------------------------------------------------ |
create or replace trigger foo_trigger |
before insert or update of sid |
on foo |
referencing new as new_value --???? |
old as old_value |
for each row --行级触发器 |
begin |
if inserting then |
select seq_foo.nextval into :new.sid from dual; --delete 没有前映像:old,只能使用new,delete 与此相反,而update则都有 |
else |
dbms_output.put_line( 'can not update the value of id' ); |
end if ; |
end ; |
--------------------------------instead of 触发器------------------------------ |
-- instead of 触发器定义在视图上 |
grant create view to cmcc; |
select * from session_privs; |
|
create table t_emp( |
sid number, |
ename varchar2(10) |
) |
create table t_dmp( |
sid number, |
dname varchar2(10) |
) |
alter table t_emp modify (ename varchar2(50)); |
alter table t_dmp modify (dname varchar2(50)); |
--------------------------- |
declare |
i number :=0; |
begin |
while i<100 loop |
insert into t_emp values (i,to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss' )); |
i:=i+1; |
end loop; |
end ; |
commit ; |
------------------------- |
select * from t_emp; |
select * from t_dmp; |
|
create view tb_d_e_view as select e.sid,ename,dname from t_emp e,t_dmp d where e.sid=d.sid; |
|
select * from tb_d_e_view order by sid desc ; |
|
create or replace trigger insert_emp_dmp_trigger |
instead of |
insert on tb_d_e_view |
for each row |
|
declare |
var_empsid t_emp.sid%type; |
begin |
insert into t_emp values (:new.sid,:new.ename); |
dbms_output.put_line(:new.dname); |
insert into t_dmp values (:new.sid,:new.dname); |
end ; |
|
select * from t_dmp order by sid desc ; |
insert into tb_d_e_view (sid,ename,dname) values (1000, 'ee' , 'dd' ); |
-----------------------------------------程序包------------------------------------------------- |
delete t_emp; |
insert into t_emp(sid_param ,ename_param ) values (33,89); |
select * from t_emp; |
---------- |
create or replace package ed_pkg is |
procedure update_t_emp(sid number,ename number) is |
begin |
|
update t_emp set ename=ename+ename_param where sid=sid_param; |
end ; |
function select_name(sid_param number ) return varchar2 is |
e_ename varchar2(20); |
begin |
select ename into e_ename from t_emp where sid =sid_param; |
return e_name; |
exception |
when no_data_found then |
dbms_output.put_line( 'sid 无效' ) ; |
end ; |
end ; |
|
|
-----------------------------------------修改表结构------------------------------------------------- |
---1)、你不得不告诉Oracle你准备alter什么表: |
Alter table table_name |
---2)、然后,你准备做什么?Adding 一个约束 |
ALTER TABLE table_name ADD CONSTRAINT |
---3)、强烈建议但不要求为约束定义个名字。约束名不需要放在引号里,但会以大写字母形式存储在数据字典里, |
ALTER TABLE temp ADD CONSTRAINT pk_temp |
---4)、表示约束类型将是Primary Key,Unique,Foreign Key,或Check约束 |
ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY |
---5)、跟在约束类型后面有几种特殊选项,Primary Key和Unique约束,需要指定约束的列;Check约束需要指定约束规则。 |
ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID); |
---6)、Primary Key和Unique约束应当指定索引的表空间名,使用USING INDEX TABLESPACE子句. |
ALTER TABLE temp ADD CONSTRAINT pk_temp PRIMARY KEY (ID) USING INDEX TABLESPACE student_index; |
|
Alter table table_name |
Add column_name type [ default expression] -- 增加新列 |
Modify datatype default expression -- 修改已有列和属性 |
Storage storage_clause -- 修改存储特征 |
Drop drop_clause -- 删除约束条件 |
----------------------------------------序列------------------------------------------------ |
--序列号生成器,可以为表中的行自动生成序列号,产生一组等间隔的数值(类型为数字)。其主要的用途是生成表的主键值, |
--可以在插入语句中引用,也可以通过查询检查当前值,或使序列增至下一个值。 |
--序列的创建语法如下: |
|
CREATE SEQUENCE 序列名 |
[INCREMENT BY n] --步长,默认为1,负值代表序列的值是按照此步长递减的。 |
[START WITH n] --初始值 默认为1。 |
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}] --MAXVALUE 定义序列生成器能产生的最大值。递增最大值是10的27次方;递减最大值是-1。 |
--MINVALUE 定义序列生成器能产生的最小值。递减最小值是10的26次方;递增最小值是1。 |
[{CYCLE|NOCYCLE}] --表示当序列生成器的值达到限制值后是否循环,如果循环,则当递增序列达到最大值时,循环到最小值; |
--对于递减序列达到最小值时,循环到最大值。如果不循环,达到限制值后,继续产生新值就会发生错误 |
[{CACHE n|NOCACHE}]; --默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲,可以改善序列的性能 |
--删除序列的语法是: |
DROP SEQUENCE 序列名; |
|