Oracle学习

最新推荐文章于 2022-11-02 15:12:52 发布

cuckoo_杜鹃

最新推荐文章于 2022-11-02 15:12:52 发布

阅读量362

点赞数

本文链接：https://blog.csdn.net/A_Cute_boy/article/details/76554714

版权

java 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

知识点1

1.1用户登录

显示当前用户:
show user
普通用户登录:
conn system/abc123;
sys用户登录:
conn sys/abc123 as sysdba;
断开连接:
disconn
退出Oracle数据库:
exit

1.2oracle服务

doc命令下-启动Oracle服务：net start oracleserviceorcl
doc命令下-关闭Oracle服务: net stop oracleserviceorcl

1.3账户操作

创建用户(system用户可以创建其它用户):
create user test identified by abc123;

给用户授予权限(system用户可以给其它用户授权):
grant connect,resource to test;

锁账户(只有高一级账户可以操作):
alter user test account lock;

解锁账户(只有高一级账户可以操作)
alter user test account unlock;

修改用户密码(高级账户或账户本身可以修改):
alter user system identified by abc123

1.4远程登录数据库

conn system/abc123@10.1.59.5:1521/orcl
conn sys/abc123@10.1.59.5:1521/orcl as sysdba

知识点2

2.1表空间文件

说明
select name,bytes,status from v$datafile;

说明
select name from v$controlfile;

说明
select group# from v$logfile;

说明
select name from v$database;

2.2将sql语句进行记录

spool E:test1.txt
......
spool off

2.3表空间相关操作

创建表空间:
create tablespace testcuck datafile 'E:/testcuck01.dbf' size 10m autoextend on next 10m maxsize 1G;
更改表空间:
alter tablespace testcuck add datafile 'E:/testcuck03.dbf' size 10m;
删除表空间:
drop tablespace tptest including contents and datafiles;

2.4表空间应用

create table t_user(
uiid char(6) primary key,
uname varchar(20) not null,
ubirthday date,
usex char(1) check(usex in('男','女')),
uaddress varchar2(50),
utelephone varchar2(20)
)
TABLESPACE shoppingtp
pctfree 8 --注册用户表中，注册用户的信息修改的次数一般比较少，所以设置8%的数据块可用空间用于update。
pctused 40   --注册用户表中，注册用户的信息很多，所以设置40%，使得数据块的数据低于40%时可以插入新的数据。
STORAGE ( 
   INITIAL       100M  --初始值设定为100M
   NEXT 50M         --NEXT设置为50M
)
;

 PCTFREE：为一个块保留的空间百分比，表示数据块在什么情况下可以被insert，默认是10，表示当数据块的可用空间低于10%后，就不可以被insert了，只能被用于update；即：当使用一个block时，在达到pctfree之前，该block是一直可以被插入的，这个时候处在上升期。

 PCTUSED：是指当块里的数据低于多少百分比时，又可以重新被insert，一般默认是40,即40%，即：当数据低于40%时，又可以写入新的数据，这个时候处在下降期。

知识点3

知识点4(函数)

应用1

create or replace function f_tax(i_sal in number) return number
as
  salary number(10,2);
begin
  salary:=i_sal;
  salary:=salary-3500;
  if salary<=0 then        
			return (0);
  elsif salary<=1500 then  
			return (salary*0.03);
  elsif salary<=4500 then      
			return (salary*0.1-105);
  elsif salary<=9000 then       
			return (salary*0.2-555);
  else
			return (salary*0.45-13505);
  end if;
end;
/
show error;

select f_tax(5000) from dual;

知识点5

知识点6

6.1查询存储过程

查询当前用户下存储过程的名称
select object_name from user_objects where object_type='PROCEDURE';
查询存储过程源码
select text from user_source where type='PROCEDURE' and name='P_INSERTORDERITEMS';

6.2查询函数

查询当前用户下函数的名称
select object_name from user_objects where object_type='FUNCTION';

知识点7(存储过程)

应用1

打印输出员工的信息(姓名，工资，入职时间)
create or replace procedure p_printemp(i_empid number)
as
v_name employees.first_name%type;
v_sal employees.salary%type;
v_hdate employees.hire_date%type;
begin
      select first_name,salary,hire_date
        into v_name,v_sal,v_hdate
          from employees where employee_id=i_empid;
          dbms_output.put_line(v_name||'.'||v_sal||'.'||v_hdate);
exception 
    when no_data_found then
            dbms_output.put_line('您输入的编号不存在');
end;
/
show error;

set serveroutput on;
exec p_printemp(123);

应用2

打印输出员工的信息(姓名，工资，入职时间)
create or replace procedure p_printemp(i_deptid number)
as
v_name employees.first_name%type;
v_sal employees.salary%type;
v_hdate employees.hire_date%type;
cursor cur_emp is
        select first_name,salary,hire_date
        into v_name,v_sal,v_hdate
        from employees where department_id=i_deptid;  --定义游标
begin
        open cur_emp;   --打开游标
        fetch cur_emp into v_name,v_sal,v_hdate;
        while (cur_emp%found) loop
        dbms_output.put_line(v_name||'.'||v_sal||'.'||v_hdate);
        fetch cur_emp into v_name,v_sal,v_hdate;
        end loop;
        close cur_emp;
exception 
    when no_data_found then
            dbms_output.put_line('您输入的编号不存在');
end;
/
show error;

set serveroutput on;
exec p_printemp(1);

应用3

打印输出员工的信息(*)
create or replace procedure p_printemp(i_deptid number)
as
cursor cur_emp is
        select *
        from employees where department_id=i_deptid;  --定义游标
rec_emp cur_emp%rowtype;--游标的行记录类型
begin
        open cur_emp;   --打开游标
        fetch cur_emp into rec_emp;
        while (cur_emp%found) loop
        dbms_output.put_line(rec_emp.last_name||'.'||rec_emp.salary||'.'||rec_emp.email);
         fetch cur_emp into rec_emp;
        end loop;
        close cur_emp;
exception 
    when others then
            dbms_output.put_line('error'||','||sqlerrm);
end;
/
show error;

set serveroutput on;
exec p_printemp(1);

应用4

create or replace procedure p_printemp(i_deptid number)
as
cursor cur_emp is
        select * from employees where department_id=i_deptid;  --定义游标
begin

        for rec_emp in cur_emp loop
        dbms_output.put_line(rec_emp.last_name||'.'||rec_emp.salary||'.'||rec_emp.email);
        end loop;
exception 
    when others then
            dbms_output.put_line('error'||','||sqlerrm);
end;
/
show error;

set serveroutput on;
exec p_printemp(100);

应用5

create or replace procedure p_printemp(i_deptid number)
as
cursor cur_emp(deptno number) is
        select *
        from employees where department_id=deptno;  --定义游标
rec_emp cur_emp%rowtype;--游标的行记录类型
begin
        open cur_emp(i_deptid);   --打开游标
        fetch cur_emp into rec_emp;
        while (cur_emp%found) loop
        dbms_output.put_line(rec_emp.last_name||'.'||rec_emp.salary||'.'||rec_emp.email);
         fetch cur_emp into rec_emp;
        end loop;
        close cur_emp;
exception 
    when others then
            dbms_output.put_line('error'||','||sqlerrm);
end;
/
show error;

set serveroutput on;
exec p_printemp(100);

应用6

create or replace procedure p_printdeptandemp
as
  cursor cur_dept is select * from departments;
  cursor cur_emp(deptno number) is
      select * from employees where department_id=deptno;
  rec_dept  cur_dept%rowtype;
  rec_emp  cur_emp%rowtype;
begin
    open cur_dept;
    fetch cur_dept into rec_dept;
    while (cur_dept%found) loop
          dbms_output.put_line('部门'||rec_dept.department_id||'.'||rec_dept.department_name||'.'||
rec_dept.location_id);
          for rec_emp in cur_emp(rec_dept.department_id) loop
          dbms_output.put_line('      员工'||rec_emp.employee_id||'.'||
          rec_emp.first_name||'.'||rec_emp.salary);
    end loop;
    fetch  cur_dept into rec_dept;
    end loop;
    close cur_dept;
Exception
    when others then
            dbms_output.put_line('error'||','||sqlerrm);
end;
/
show error;

exec p_printdeptandemp;

自定义异常

declare
e_datanotnull exception;
pragma  exception_init(e_datanotnull,-01400);
begin
    insert into departments
      values(301,null,null,null);
exception
    when e_datanotnull then
    dbms_output.put_line('数据不能为空');
  when others then
    dbms_output.put_line('error');
end;

用户自定义异常

declare
e_datanotnull exception;
pragma  exception_init(e_datanotnull,-01400);
v_location number;
v_dept number;
e_depttoohigh exception;
begin
    v_location:=&地址编号;
    v_dept:=&部门编号;
    --部门编号不能大于10000
    if v_dept>10000 then
    raise e_depttoohigh;
    end if;
    insert into departments
      values(v_dept,'neusoft',null,v_location);
exception
    when e_datanotnull then
        dbms_output.put_line('数据不能为空');
    when e_depttoohigh then
        dbms_output.put_line('编号太大');
  when others then
        dbms_output.put_line('error');
end;

知识点8(触发器)

插入

create or replace trigger tr_checkpitemsinsert
before insert on t_procure_items
for each row
declare 
v_state t_main_procure.pstate%type;
begin
  select pstate into v_state
  from t_main_procure
  where pmid=:new.pmid;
  if v_state<>'1' then
    raise_application_error('-20001','该单据已经审核，不能添加');
  end if;
end;
/
show error;

%rowtype：代表一行记录
/*触发器*/
:new:
1)insert操作中，添加的行记录
2)update操作中更新以后的行记录

:old:
1)delete操作中，删除的行记录
2)update操作中更新以前的行记录

删除

create or replace trigger tr_checkpitemsdelete
before delete on t_procure_items
for each row
declare 
v_state t_main_procure.pstate%type;
begin
  --判断所删除的明细数据的单据是否是待审核
  select pstate into v_state
  from t_main_procure
  where pmid=:old.pmid;
  if v_state<>'1' then
    raise_application_error('-20001','该单据已经审核，不能再删除');
  end if;
end;

delete from t_procure_items where pmid='P20170800001' and gid='g0003';

更新

create or replace trigger tr_checkpitemsupdate
before update on t_procure_items
for each row
declare 
v_state t_main_procure.pstate%type;
begin
  --判断所更新的明细数据的单据是否是待审核
  select pstate into v_state
  from t_main_procure
  where pmid=:old.pmid;
  if v_state<>'1' then
    raise_application_error('-20001','该单据已经审核，不能再更新');
  end if;
end;

update t_procure_items set piprice=20 where pmid='P20170800001';

多个触发器合并

create or replace trigger tr_checkpitems
before update or insert or update on t_procure_items
for each row
declare 
v_state t_main_procure.pstate%type;
begin
   if inserting then
      select pstate into v_state
      from t_main_procure
      where pmid=:new.pmid;
   elsif updating or deleting then
       select pstate into v_state
       from t_main_procure
       where pmid=:old.pmid;
   end if;
     if v_state<>'1' then
       raise_application_error('-20001','该单据已经审核，无法进行添加、删除和修改');
  end if;
end;

测试添加:
insert into t_procure_items values('P20170700001','g0002',10,3,30,null);
测试修改:
update t_procure_items set piprice=40 where pmid='P20170800001';
测试删除:
delete from t_procure_items where pmid='P20170800002';
commit;
rollback;

应用

create or replace trigger cr_addchangesal
after update of salary on employees 
for each row
begin
    --判断员工工资是否发生改变
    if :old.salary<>:new.salary then
    insert into re_changeSal values(sqp_emppid.nextval,:old.employee_id,:old.salary,:new.salary,default,user);--user代表当前登录用户
    end if;
end;
/
show error;

作业

1)实现采购单明细数据的添加程序
/*
  存储过程名称:p_insertprocureitems
  输入参数: i_pmid(采购编号); i_gid(商品编号); i_piprice(采购单价); i_pinum(采购数量);
  输出参数: i_result: 1(添加成功); -1(数据不存在);
  创建人:cuckoo
  创建日期:2017.07.31
  修改:cuckoo
*/

/*存储过程*/
create or replace procedure p_insertprocureitems
(i_pmid in t_main_procure.pmid%type,  --采购编号
 i_gid in t_goods.gid%type,           --商品编号
  i_piprice in number default 0,      --采购单价
  i_pinum in number default 1,        --采购数量
  i_result out number                 --输出参数
)
 as
 v_count number:=0;
 begin
    --检查采购单编号是否正确
  select count(pmid) into v_count
     from t_main_procure where trim(pmid)=trim(i_pmid);
  if v_count=0 then
      i_result:=-1;
      return;  --不存在采购单编号，退出程序
  end if;
   --检查商品编号是否正确
  select count(gid) into v_count
     from t_goods where trim(gid)=trim(i_gid);
  if v_count=0 then
      i_result:=-1;
      return;  --不存在商品编号，退出程序
  end if;
  insert into t_procure_items   
     values(i_pmid,i_gid,i_piprice,i_pinum,i_piprice*i_pinum,null);
  i_result:=1;
  commit;
exception 
   when others then
       i_result:=sqlcode;
       rollback;
 end;
/
show error;

set serveroutput on
/*调用存储过程*/
declare
v_result number;
begin
   p_insertprocureitems('p20170700006','g0002',10,3,v_result);
      if v_result=1 then
       dbms_output.put_line('添加订单明细成功');
   elsif  v_result=-1 then
       dbms_output.put_line('数据错误或不存在'); 
   else
       dbms_output.put_line('error'||','||v_result);
   end if;  
end;



--------------------------------------------------------------
2）实现对订单商品的评价功能

/*评价表主键自增函数*/
create or replace function e_createueid
   return varchar as
f_createueid varchar2(10);
f_maxid  t_user_evaluation.ueid%type;
begin
   f_createueid:='P'||to_char(sysdate,'yyyymm');
   select max(ueid) 
      into f_maxid
      from t_user_evaluation
      where  to_char(uedate,'yyyymm')=to_char(sysdate,'yyyymm');
   if f_maxid is null then
     f_createueid:=f_createueid||'001';
   else
     f_createueid:=f_createueid||trim(to_char(to_number(substr(f_maxid,8,5))+1,'000'));
   end if;
   return f_createueid;
end;


/*
  存储过程名称:insertevaluation
  输入参数: e_omid(订单编号); e_gid(商品编号); 
  输出参数: e_result: 1(添加成功); -1(数据不存在);
  创建人:cuckoo
  创建日期:2017.07.31
  修改:cuckoo
*/

create or replace procedure insertevaluation
(
e_omid in t_order_items.omid%type,--订单编号
 e_gid in t_order_items.gid%type,--商品编号
  e_result out number
)
 as
 v_count number:=0;
 begin
    --检查订单编号是否正确
  select count(omid) into v_count
     from t_main_order where trim(omid)=trim(e_omid);
  if v_count=0 then
      e_result:=-1;
      return;  --订单编号不存在，退出程序
  end if;
  --检查商品编号是否正确
  select count(gid) into v_count
     from t_goods where trim(gid)=trim(e_gid);
  if v_count=0 then
      e_result:=-1;
      return;  --商品编号不存在，退出程序
  end if;
  insert into t_user_evaluation   
    values(e_createueid(),e_omid,e_gid,sysdate,'A','abc');
  e_result:=1;
  commit;
exception 
   when others then
       e_result:=sqlcode;
       rollback;
 end;
/
show error;



set serveroutput on

declare
v_result number;
begin
   insertevaluation('O20170700012','g0001',v_result);
      if v_result=1 then
       dbms_output.put_line('添加成功');
   elsif  v_result=-1 then
       dbms_output.put_line('数据不存在'); 
   else
       dbms_output.put_line('error'||','||v_result);
   end if;  
end;