Orcale基础复习

用sqlplus登陆
cmd
sqlplus sys/1459355930 as sysdba


scott解锁
alter user scott account unlock;

更改scott用户密码
alter user scott identified by tiger

命令连接数据库
sqlplus scott/1459355930@orcl



创建角色
create role test_role;

为新建的角色赋予权限
grant select, update, insert, delete on scott.emp to test_role;


删除角色drop role test_role;


connect system/1459355930
创建新的用户
create user emp_user identified by 1459355930
为新的用户授权
grant test_role, create session to emp_user;


创建表
create table person(
id number(4),
name varchar(8),
sex char(2),
birthday date);

查看这个连接所有的表的表空间
select table_name, tablespace_name from user_tables;

为创建的表制定表空间
create table person(
id number(4),
name varchar(8),
sex char(2),
birthday date)tablespce users;

在表中增加email列
alter table person add email varchar2(20);


创建视图
create or replace view dept_sum_vw
(name,minsal,maxsal,avgsal)
as select d.dname,min(e.sal),max(e.sal),avg(e.sal)
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;

没有权限，要赋视图权限

用connect sys/145935590 as sysdba

grant create view to scott;



导出表
exp scott/1459355930@orcl file=f:\DBFile\student.dmp full=y
导入表
imp scott/1459355930@orcl file=f:\DBFile\student.dmp

导出指定的表
exp scott/1459355930@orcl file=f:\DBFile\student_table.dmp tables(student,score,course)
imp scott/1459355930@orcl file=f:\DBFile\student_table.dmp tables(student,course)
(f:\DBFile\该文件夹是已经存在的)


创建新的的用户
create tablespce myspace datafile 'f:/DBFile/myspace.dbf' size 100m;

创建用户
create user test identified by test default tablespace myspace;

删除用户
drop user test;

如果已经存在表等一些数据库对象，必需用级联
drop user test cascade;

修改用户密码
alter user test indentified by 1459355930



游标
隐式游标的列子
set serveroutput on
begin
   update student set sno= '0601020214' where sno= '0601020212';
   if SQL%FOUND THEN 
       DBMS_OUTPUT.PUT_LINE('表已经更新');
   end if;
end;

列子二
set serveroutput on

declare 
   v_sno student.sno%type:='&sno';
   v_sname student.sname%type:='&sname';
begin
   update student set sname=v_sname where sno=v_sno;
   if SQL%NOTFOUND THEN 
       DBMS_OUTPUT.PUT_LINE('编号未找到');
    else
       DBMS_OUTPUT.PUT_LINE('表已经更新'||SQL%ROWCOUNT||'行数');-- 返回 DML 语句影响的行数
   end if;
end;

显示游标

例子1

set serveroutput on

declare   

cursor c_job is select empno,ename,job,sal from emp where job='MANAGER';  
   c_row c_job%rowtype;  
begin  
   open c_job;  
   loop  
      fetch c_job into c_row;  --提取一行数据到c_row  
       --判读是否提取到值，没取到值就退出，取到值c_job%notfound 是false，取不到值c_job%notfound 是true  
      exit when c_job%notfound;  
         dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);  
   end loop;  
--关闭游标  
close c_job;  
end; 

for循环
set serveroutput on

declare  
   cursor c_job is select empno,ename,job,sal from emp where job='MANAGER';  
   --定义游标变量，该类型为游标中的一行数据类型  
  c_row c_job%rowtype;  
begin  
   for c_row in c_job
   loop  
     dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);  
   end loop;  
end;  


自定义异常

set serveroutput on

declare 
    e_no_employee exception;
begin
    update emp set deptno=11 where empno=1111;
    if SQL%NOTFOUND then
        raise e_no_employee;
    end if;
    exception 
        when e_no_employee then
     dbms_output.put_line('该雇员不存在');
end;

非预定义异常

set serveroutput on

declare 
    pk_check exception;
    pragma exception_init(pk_check,-02290);
begin
    insert into score values('AAAA','BBBB',23);
    exception 
        when pk_check then
        dbms_output.put_line('违反了外键约束');
end;



用函数查实现：
在emp表中华查某个人的年收入
create or replace function annual_incomc(f_name varchar2)
return number
is
    annual_salary number(7,2);
begin
        --如果你某个字段为空，但是你想让这个字段显示0，nvl(字段名,0)，
	--就是当你选出来的时候，这个字段虽然为空，
	--但是显示的是0，当然这个0也可以换成其他东西，如：1，2，3……
    select sal*12+nvl(comm,0) into annual_salary
	from emp where lower(ename)=lower(f_name);
	return annual_salary;
end;

select annual_incomc('scott')  年收入 from dual;
--dual一个虚拟表，但只能显示一个数据


用存储过程实现
更新某个人的工资
--存储过程
create or replace procedure update_sal(v_name varchar2,newsal number)
is
begin
    --lower(str) 以及所有根据最新的字符集映射表变为小写字母的字符
    update emp set sal=newsal where lower(ename)=lower(v_name);
end;

exec update_sal('scott',2000);



用包调用函数和存储过程
实现上面的两个

创建包
create package emp_pkg
is
procedure update_sal(v_name varchar, newsal number);
function annual_income(f_name varchar) return number;
end;

创建包体
create package body emp_pkg is
procedure update_sal(v_name varchar,newsal number)
is
begin
    update emp set sal=newsal
	where lower(ename)=lower(v_name);
end;
function annual_income(f_name varchar) return number
is
    annual_salary number(7,2);
begin
    select sal*12+nvl(comm,0) into annual_salary 
	from emp where lower(ename)=lower(f_name);
	return annual_salary;
end;
end;

调用包
exec emp_pkg.update_sal('scott',1500);
select emp_pkg.annual_income('scott') 年收入 from dual;


触发器的实现
实现级联更新部门编号

select ename from emp where deptno=20;

create or replace trigger update_cascade
after update of deptno on dept
for each row
begin
    update emp set deptno=:new.deptno where deptno=:old.deptno;
end;

更新dept中的deptno同时触发更新emp的deptno
update dept set deptno=21 where deptno=20;