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 序列名;
-----------------------------------------------------------------------------------------------------------
