test5

# 创建表空间:
create tablespace iha_t
datafile 'c:iha_t.dbf'
size 10m
autoextend on
next 10m;

# 创建用户:
create user iha_u
identified by iha_t
default tablespace iha_t;

# 给用户授权:
connect --连接角色,基本角色
resource --开发者角色,基本角色
dba--超级管理员角色
grant dba to iha_u;

切换用户:
conn iha_u/iha_t;
alter user iha_u account unlock;
grant dba to iha_u;
conn scott/oracle
conn sys as sysdba

建表:
create table person(
pid number(30),
pname varchar2(10)
);

修改表结构:
alter table person add gender varchar2(10);
desc person;
修改列类型:
alter table person modify gender char(1);
修改列名称:
alter table person rename column gender to sex;
删除一列:
alter table person drop column sex;

查询表中记录:
insert into person values(1,'小明');
commit;

序列:
create sequence s_person;
select s_person.nextval from dual;
select s_person.currval from dual;
--- 添加一条记录:
insert into person values(s_person.nextval,'小明');
commit;
select * from person;
# case when 表达式:
select e.ename,
case e.ename
when '曾阿牛' then '牛哥'
when '刘备' then '备哥'
when '张飞' then '飞哥'
else 'swage'
end
from (select * from emp) e
where prior e.empno!=prior 1113
start with e.mgr is null
connect by prior e.mgr=e.empno
and level <3;

select e.ename,
case e.ename
when '曾阿牛' then '牛哥'
when '刘备' then '备哥'
when '张飞' then '飞哥'
else 'swage'
end
from (select * from emp) e
where e.empno!= 1113
start with e.mgr is null
connect by prior e.mgr=e.empno
and level <3;

select e.ename,
case e.ename
when '曾阿牛' then '牛哥'
when '刘备' then '备哥'
when '张飞' then '飞哥'
when '诸葛亮' then '亮哥'
when '庞统' then '统一'
when '韦一笑' then '小哥'
else 'swage'
end
from (select * from emp) e
where e.empno!=prior 1011
start with e.mgr is null
connect by prior e.mgr!=e.empno
and level <3
and prior e.mgr>1001;

专用表达式：
select e.ename,
decode(e.ename,
'曾阿牛', '牛哥',
'刘备', '备哥',
'张飞', '飞哥',
'诸葛亮', '亮哥'
) ename
from(select * from emp) e
where e.empno!=prior 1011
start with e.mgr is null
connect by prior e.mgr!=e.empno
and level <3
and prior e.mgr>1001;

select e.sal,
case
when e.sal>8000 then '高收入'
when e.sal>3500 then '中收入'
when e.sal>1300 then '低收入'
end
from (select * from emp) e
where e.empno!=prior 1011
start with e.mgr is null
connect by prior e.mgr!=e.empno
and level <3
and prior e.mgr>1001;

分页查询:
select empno,mgr from (
select rownum rn, e.* from(
select * from emp order by sal desc
) e where rownum<12
) tt where rn>5
start with mgr is null
connect by prior empno=mgr
order by empno;

select empno,mgr from (
select rownum rn, e.* from(
select * from emp order by sal desc
) e where rownum<12
) tt where rn>5 and prior mgr!=1111
start with mgr is null
connect by prior empno=mgr
and level <5
and prior sal!=0
order by empno;

创建视图:
create view v_emp as select ename,job from emp;
select * from v_emp;
修改视图 语句同修改表

索引:
-- 单列索引
create index idx_ename on emp(ename);
-- 复合索引 第一列为优先检索列
create index idx_ename on emp(empno,mgr);

定义变量:
-- 赋值操作可以使用 := 也可以使用select into 语句赋值
declare
i number(2) := 10;
s varchar2(10) := '小明';
ena emp.ename%type;
emprow emp%rowtype;
begin
dbms_output.put_line(i);
dbms_output.put_line(s);
select ename into ena from emp where empno=1003;
dbms_output.put_line(ena);
select * into emprow from emp where empno=1006;
dbms_output.put_line(emprow.ename || '的工作:' ||emprow.job);
end;

pl中的if判断:
declare
i number(3) := &i;
begin
if i<18 then
dbms_output.put_line('未成年');
elsif i<40 then
dbms_output.put_line('中年人');
else
dbms_output.put_line('老年人');
end if;
end;
/

pl中的循环: --while循环:
declare
i number(2) :=1;
begin
while i<11 loop
dbms_output.put_line(i);
i :=i+1;
end loop;
end;

---exit 退出循环:
declare
i number(2) :=1;
begin
loop
exit when i>10;
dbms_output.put_line(i);
i := i+1;
end loop;
end;

---for 循环:
declare

begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;

---pl中的游标:
declare
cursor c1 is select * from emp;
emprow emp%rowtype;
begin
open c1;
loop
fetch c1 into emprow;
exit when c1%notfound;
dbms_output.put_line(emprow.ename);
end loop;
close c1;
end;

---给指定部门员工涨薪:
declare
cursor c2(eno emp.deptno%type)
is select empno from emp where deptno = eno;
en emp.empno%type;
begin
open c2(10);
loop
fetch c2 into en;
exit when c2%notfound;
update emp set sal=sal+100 where empno=en;
commit;
end loop;
close c2;
end;

select * from emp where deptno=10;

---存储过程:
create or replace procedure p1(eno emp.empno%type)
is

begin
update emp set sal=sal+100 where empno=eno;
commit;
end;

--测试p1:
declare

begin
p1(1002);
end;

select * from emp where empno=1002;

---存储函数:
create or replace function f_yearsal(eno emp.empno%type) return number
is
s number(10);
begin
select sal*12 + nvl(comm,0) into s from emp where empno=eno;
return s;
end;

-- 测试:
declare
s number(10);
begin
s := f_yearsal(1003);
dbms_output.put_line(s);
end;

--out 类型参数使用:
create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number)
is
s number(10);
c emp.comm%type;
begin
select sal*12, nvl(comm,0) into s,c from emp where empno=eno;
yearsal := s+c;
end;

测试:
declare
yearsal number(10);
begin
p_yearsal(1005,yearsal);
dbms_output.put_line(yearsal);
end;

---使用存储函数来实现返回一个部门编号:
create or replace function fdna(dno dept.deptno%type) return dept.dname%type
is
dna dept.dname%type;
begin
select dname into dna from dept where deptno=dno;
return dna;
end;

---触发器:
create or replace trigger t1
after
insert
on person
declare

begin
dbms_output.put_line('一个新员工入职:');
end;

select * from person;
SQL> set serveroutput on
SQL> insert into person values(2,'小花');
一个新员工入职:

---行级触发:
create or replace trigger t2
before
update
on emp1
for each row
declare

begin
if :old.sal> :new.sal then
raise_application_error(-20001,'不能给员工降薪');
end if;
end;
-- 触发t2;
update emp1 set sal=sal-100 where empno=1002;
commit;

select * from emp1;

create sequence s_person;
---触发器实现主键自增:
create or replace trigger auid
before
insert
on person
for each row
declare

begin select s_person.nextval into :new.pid from dual;
end;

查询person表数据
select * from person;
insert into person(pname) values('a');
commit;