Oracle 学习笔记

select * from user_tables;
drop table t_user_role;
drop table t_user;
drop table t_role;
drop sequence user_seq;

create sequence user_seq--建立序列(自动增长列)
increment by 1 --自增系数
start with 1 --初始值
--maxValue 最大值,noMaxValue 无最大值
--minValue 最小值,nominValue 无最小值
--cycle 循环取值 ,nocycle 不循环取值
--cache 缓冲区
;

create table t_user
(
 userId number(10) primary key,
 userName varchar2(16) not null
);
insert into t_user values(user_seq.nextval,'good');
insert into t_user values(user_seq.nextval,'haha');
insert into t_user values(user_seq.nextval,'flyCat');
insert into t_user values(user_seq.nextval,'fullCat');
select * from t_user;
select user_seq.currval from dual;

create table t_role(
 roleId number(10) primary key,
 roleName varchar(16) not null
)
partition by hash(roleId) --散列分区
partitions 2 --分区数目
--分区方式2(自己指定分区名)
--partition by hash( roleId )
--(
--          partition role_p1,
--          partition role_p2
--);
;

insert into t_role values(user_seq.nextval,'一级');
insert into t_role values(user_seq.nextval,'二级');
insert into t_role values(user_seq.nextval,'三级');
insert into t_role values(user_seq.nextval,'四级');
select * from t_role;

create table t_user_role
(
 infoId number(10) primary key,
 userId number(10) not null,
 roleId number(10) not null,
 remark varchar2(16) not null,
 foreign key (userId) references t_user(userId),
 foreign key (roleId) references t_role(roleId) 
)
partition by range(infoId)--范围分区
subpartition by list(userId) --这里用到复合分区，首先必须是范围分区，然后是列表分区
(
 partition p1 values less than(100)
 (
  subpartition p1s1 values(1),
  subpartition p1s2 values(3),
  subpartition p1s3 values(default)--剩下的结果存放在这里
 ),
 partition p2 values less than(200)
 (
  subpartition p2s1 values(2),
  subpartition p2s2 values(4),
  subpartition p2s3 values(default)
 ),
 partition p3 values less than(maxvalue)
 (
  subpartition p3s1 values(default)
 )
);
insert into t_user_role values(user_seq.nextval,1,5,'手动输入');
insert into t_user_role values(user_seq.nextval,1,6,'手动输入');
insert into t_user_role values(user_seq.nextval,1,7,'手动输入');
insert into t_user_role values(user_seq.nextval,1,8,'手动输入');
insert into t_user_role values(user_seq.nextval,2,5,'手动输入');
insert into t_user_role values(user_seq.nextval,2,6,'手动输入');
insert into t_user_role values(user_seq.nextval,4,5,'手动输入');
select * from t_user_role;

create public t_u_r for t_user_role;--创建同义词
select * from t_u_r;
--联合查询
select tur.infoId as id, tu.userName as userName,tr.roleName as roleName from t_u_r tur,t_user tu,t_role tr where tur.userId = tu.userId and tur.roleId = tr.roleId;
--创建视图
create view v_info as select tur.infoId as id, tu.userName as userName,tr.roleName as roleName from t_u_r tur,t_user tu,t_role tr where tur.userId = tu.userId and tur.roleId = tr.roleId;
select * from v_info;
create view v_info(id ,userName,roleName) as select tur.infoId as id, tu.userName as userName,tr.roleName as roleName from t_u_r tur,t_user tu,t_role tr where tur.userId = tu.userId and tur.roleId = tr.roleId;
select * from v_info;

--创建索引
create index ind_info on t_u_r(remark);
create inde1，m v_now; ind_info2 on t_u_r(userId,roleId,remark);--组合索引
create unique index i1，m v_now;d_info3 on t_role(roleName);--唯一索引
create index ind_info4 on t_use1，m v_now;(userName) reverse;--反向索引
--create bitmap index ind_info5 on t_u_r(1，m v_now;emark);--位图索引
create table t_sex

(
 sexId number(2) primary key,
 sexName varchar(2) not null
) organization index;--索引组织表(查询快，修改慢)

--触发器
create trigger tri_user --sys 用户数据中不可建
before insert on t_user --before ,after ,instead of 
for each row--行级触发器,不加则为语句级触发器
begin
 dbms_output.put_line('trigger Result:'||:NEW.userId||'  '||:NEW.userName);--:NEW 代表新数据 :OLD 代表旧数据条件表达示如：while 中不需要':'
end;

--pl/sql 语句
begin 
 dbms_output.put_line('hello '||'world');
end;

declare
 uName t_user.userName %type;--当前列的数据类型
 uResult t_user %Rowtype;--%rowtype 代表一行，类似于面向对象中的对象
 type uType is record(mId t_user.userId %type,mName t_user.username %type);--自定义类型
 myResult uType;--使用自定义类型
begin
 uName := 'goodboy';
 dbms_output.put_line('my name is '||uName);
 select userName into uName from t_user where userId = 4;
 dbms_output.put_line('Result:userId = 4 ,userName = '||uName);
 select * into uResult from t_user where userId = 1;
 dbms_output.put_line('Result:userId = '||uResult.userId ||' ,userName = '|| uResult.userName);
 select * into myResult from t_user where userId = 1;
 dbms_output.put_line('Result:mId = '||myResult.mId ||' ,mName = '|| myResult.mName);
end;

declare 
 uNumber number(4) default 5;--默认值
begin
 dbms_output.put_line('Result:uNumber ='||uNumber);
 if uNumber > 30 then
  uNumber := uNumber - 10;
  dbms_output.put_line('Result:if uNumber >30, uNumber - 10='||uNumber);
 elsif uNumber < 10 then
  dbms_output.put_line('Result:if uNumber >10, uNumber + 10='||(uNumber + 10));
 else 
  dbms_output.put_line('Result:if uNumber, uNumber='||uNumber);
 end if;
 
 case uNumber--case 用法1
  when 30 then dbms_output.put_line('Result:case uNumber =30, uNumber = '||uNumber);
  when 10 then dbms_output.put_line('Result:case uNumber =10, uNumber = '||uNumber);
  else dbms_output.put_line('Result:case uNumber else, uNumber = '||uNumber);
 end case;
 case --case 用法2
  when uNumber > 30 then dbms_output.put_line('Result:case uNumber > 30, uNumber = '||uNumber);
  when uNumber < 10 then dbms_output.put_line('Result:case uNumber < 10, uNumber = '||uNumber);
  else dbms_output.put_line('Result:case uNumber else, uNumber = '||uNumber);
 end case;
 
 while uNumber < 20--while loop
 loop
  uNumber := uNumber + 5;
  dbms_output.put_line('Result:while loop, uNumber = '|| uNumber);
 end loop;
 loop --loop 
  exit when uNumber < 10;
  uNumber := uNumber - 5;
  dbms_output.put_line('Result:loop, uNumber = '|| uNumber);
 end loop;
 for resultNumber in 9..16--这里 resultNumber无需定义,每次会自+1,for loop
 loop
  dbms_output.put_line('Result:for loop, resultNumber = '|| resultNumber);
  if resultNumber = 12 then
   dbms_output.put_line('Result:for loop, resultNumber = '|| resultNumber||' exit loop');--exit 类似于break ,return 会从这里退出
   exit;
  end if;
 end loop;
 
 if uNumber < 40 then 
  dbms_output.put_line('use goto to goto1.');
  goto goto2;-- goto 的使用
 end if;
 <<goto1>>--goto 节点
  dbms_output.put_line('goto1');
 <<goto2>>
  dbms_output.put_line('goto2');

 dbms_output.put_line('over-------------');
end;

declare
 myException exception;--自定义异常
 uException exception;
 pragma exception_init(myException,-20001);--  -20000 ~-20999之间
begin
 --raise myException;
 raise_application_error(-20001,'哈哈，我是-20001！');
Exception--捕捉异常
 when uException then
  dbms_output.put_line('raise uException.');
 when others then--其它异常
  dbms_output.put_line('other 猜猜它是谁?');
  raise;--重新把当前异常抛出
end;

declare 
sqlString varchar(60);
userName t_user.userName %type;
begin
sqlString :='insert into t_user values(user_seq.nextval,:1)';--注意这里 :1
userName := 'isw2';
execute immediate sqlString using userName;--动态SQL
execute immediate 'select * from t_user';
end;
select * from t_user;

--游标
declare 
 uId t_user.userId %type default 3;
begin
 select userid into uid from t_user where t_user.userid = uId;--隐示游标(返回结果只能一行)
 if sql%Isopen then dbms_output.put_line('游标打开');--DML 语句执行时游标打开执行完关闭
 elsif sql%found then dbms_output.put_line('游标有效');
 elsif sql%Notfound then dbms_output.put_line('游标无效');
 end if;
 dbms_output.put_line('影响行数' || sql%rowCount );
end;

declare 
 cursor ucur is select * from t_user;--显示游标
 uRow t_user %Rowtype;
begin
 open ucur;--打开游标
 if ucur%isopen then dbms_output.put_line('游标成功打开.');
 end if;
 loop 
  fetch ucur into uRow;
  exit when ucur%notfound;
   dbms_output.put_line('cursor result: userId = ' || uRow.userId ||' userName= ' || uRow.userName);
  end loop;
  dbms_output.put_line('影响行数:' || ucur%rowCount);
  close ucur;
end;--在这里销毁

declare 
 cursor ucur(maxVal t_user.userId %type) is select * from t_user where userid < maxVal;--带参游标
 uRow t_user %Rowtype;
 type nullCur is ref cursor;--引用游标(弱类型) type nullCur is ref cursor return t_user %Rowtype(强类型,不可改变);
 ncur nullCur;
begin
 for uRow in ucur(10)--循环游标
 loop 
   dbms_output.put_line('当前行数:' || ucur%rowCount);
   dbms_output.put_line('cursor result: userId = ' || uRow.userId ||' userName= ' || uRow.userName);
  end loop;
  
  open ncur for select * from t_user;--打开游标
  if ncur%isopen then dbms_output.put_line('引用游标成功打开.');
  end if;
  loop 
  fetch ncur into uRow;
  exit when ncur%notfound;
   dbms_output.put_line('cursor result: userId = ' || uRow.userId ||' userName= ' || uRow.userName);
  end loop;
  close ncur;
end;

--存储过程
create procedure pro_first
as
begin
 dbms_output.put_line('存储过程');
end;

call pro_first();--调用存储过程
begin
pro_first();
end;

create procedure pro_second (info in varchar2,uname out varchar2,uinfo in out varchar2)--这里varchar2 不能限制大小
as--in 输入参数,out 输出参数 in out 输入输出参数
 urole varchar2(20);
begin
 urole := 'good';
 uname := 'boy';
 dbms_output.put_line('pro_second Result:urole='||urole||' uname ='||uname||' uinfo='||uinfo||' info='||info);
 uinfo := 'over';
end;

declare
 info varchar2(20) default ' valueinfo ';
 uname varchar2(20)  default ' valueuname ';
 uinfo varchar2(20)  default ' valueuinfo ';
begin
 pro_second(info,uname,uinfo);
 dbms_output.put_line('Result: uname ='||uname||' uinfo='||uinfo||' info='||info);
end;

--函数 drop function fun_first;
create function fun_first return varchar2--返回类型
as
begin
 return 'good';--返回值
end;
select fun_first() from dual;--区别于存储过程，它可以做为表达式的一部分

--包
--drop package pac_study1;
create package pac_study1 
as 
procedure pro_study1 ;
function fun_study1 return number ;
userNo t_user.userid%type;
cursor cur_study1 is select * from t_user where userId = userNO;--先定义后使用
end pac_study1;
-- 包体
create package body  pac_study1
as 
procedure pro_study1
is
begin
 dbms_output.put_line('我是 pac_study1.pro_study1');
end pro_study1;
function fun_study1 return number
is
begin
 return 144;
end fun_study1;
end pac_study1;
--可以不完全声明, 这不会出错但没意义
begin
 pac_study1.pro_study1();
end;
select pac_study1.fun_study1() from dual;