Oracle学习

知识点1

1.1用户登录

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

1.2oracle服务

doc命令下-启动Oracle服务：net start oracleserviceorcldoc命令下-关闭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/orclconn 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 shoppingtppctfree 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 numberas  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);  elsereturn (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)asv_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)asv_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)ascursor 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)ascursor 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)ascursor 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_printdeptandempas  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;

自定义异常

declaree_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;

用户自定义异常

declaree_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_checkpitemsinsertbefore insert on t_procure_itemsfor each rowdeclare 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_checkpitemsdeletebefore delete on t_procure_itemsfor each rowdeclare 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_checkpitemsupdatebefore update on t_procure_itemsfor each rowdeclare 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_checkpitemsbefore update or insert or update on t_procure_itemsfor each rowdeclare 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_addchangesalafter update of salary on employees for each rowbegin    --判断员工工资是否发生改变    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/*调用存储过程*/declarev_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 asf_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 ondeclarev_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;