sql 一些知识

--动态sql

declare

  sql_stmt varchar2(200);

  emp_id  number(4) := 1;

  emp_rec emp%rowtype;

begin

  emp_id  := &职工编号;

  sql_stmt := 'select * from emp whereempno=:id';

  execute immediate sql_stmt

    into emp_rec

    using emp_id;

  dbms_output.put_line('职工编号：' || emp_rec.empno || '职工姓名：' ||

                       emp_rec.ename || '薪水' || emp_rec.sal);

exception

  when others then

    dbms_output.put_line('该雇员不存在！');

end;

--ref 游标的用法

declare

  type refur_t is ref cursor;

  refur    refur_t;

  p_id     number;

  p_name   varchar2(100);

  selection varchar2(1) := upper(substr('&查询条件(E)或(D)', 1, 1));

begin

  if selection = 'E' then

    open refur for

      select empno, ename from emp;

    dbms_output.put_line('====员工信息======');

  elsif selection = 'D' then

    open refur for

      select deptno, dname from dept;

    dbms_output.put_line('=====部门信息======');

  else

    dbms_output.put_line('请输入员工信息(E)或部门信息(D)');

    return;

  end if;

  fetch refur

    into p_id, p_name;

  while refur%found loop

    dbms_output.put_line('#' || p_id || ':' ||p_name);

    fetch refur

      into p_id, p_name;

  end loop;

  close refur;

end;

 

--游标中的动态sql

declare

  r_emp emp%Rowtype;

  type c_type is ref cursor;

  cur     c_type;

  p_salary number;

begin

  p_salary := &查询工资标准;

  open cur for 'select * from emp wheresal>:1 order by sal desc'

    using p_salary;

  dbms_output.put_line('薪水大于' || p_salary || '的员工有：');

  loop

    fetch cur

      into r_emp;

    exit when cur%notfound;

    dbms_output.put_line('编号：' || r_emp.empno || '姓名:' ||r_emp.ename ||

                         '薪水:' || r_emp.sal);

  end loop;

  close cur;

end;

--

select * fromtoys;

 

declare

  v_price  toys.toyprice%type;

  v_toyname varchar2(200);

  cursor cur_toy is

    select toyname, toyprice from toys for update;

begin

  open cur_toy;

  loop

    fetch cur_toy

      into v_toyname, v_price;

    exit when cur_toy%notfound;

    if v_price >= 250 then

      update toys set toyprice = toyprice * 1.1where current of cur_toy;

      dbms_output.put_line('玩具名：' || v_toyname || '  修改后单价：' ||

                           v_price * 1.1);

    elsif v_price >= 100 then

      update toys set toyprice = toyprice * 1.2where current of cur_toy;

   

      dbms_output.put_line(v_toyname || '修改前的价格' || v_price);

    else

      delete toys where toyprice < 50;

    end if;

  end loop;

  commit;

exception

  when others then

    rollback;

 

end;

 

--引用型游标

declare

  type toytype is ref cursor returntoys%rowtype;

  cur_toy toytype;

  rowtoy toys%rowtype;

begin

  open cur_toy for

    select * from toys order by toyprice desc;

  loop

    fetch cur_toy

      into rowtoy;

    exit when cur_toy%notfound;

    dbms_output.put(rowtoy.tid);

    dbms_output.put(rowtoy.toyname);

    dbms_output.put_line(rowtoy.toyprice);

    --dbms_output.new_line(rowtoy.toyprice);

  end loop;

  close cur_toy;

end;

 

select object_name

  from user_objects

 where object_type = 'TABLE'

 

 declare type cur_table is ref cursor;

v_tablerowcur_table;

begin

  open v_tablerow for 'select object_name fromuser_objects where object_type=' TABLE '';

 

end;

drop table emp1;

 

declare

  --type cur_table is ref cursor;

  -- v_tablerow cur_table;

  deletable varchar2(100);

  cursor tablecur is

    select object_name from user_objects whereobject_type = 'TABLE';

begin

  open tablecur;

  loop

    fetch tablecur

      into deletable;

    if tablecur%found then

      execute immediate 'drop table :id'

        using deletable;

    end if;

  end loop;

  close tablecur;

exception

  when others then

    dbms_output.put_line('出现错误！');

 

end;

分区

  --添加分区

alter tableteacher add partition p5 values less than (50);

 

--删除分区结构

alter tableteacher drop partition p4;

--删除分区内容

alter tableteacher truncate partition p5;

 

--查询分区

select *fromteacher partition(p4);

--拆分分区

alter table teachersplit partition p5 at(40) into (partition p4,partition p5)

 

--合并分区(不能将上界作为合并分区名)

alter tableteacher merge partitions p4,p5 into partition p5

--随机数

declate

   getid integer:=1;

begin

getid:=abs(mod(dbms_random.random,3))+1;

dbms_output.put_line(getid);

end;

 

declare

tt number;

begin

selectabs(mod(dbms_random.random,18))+1 into tt from dual;

dbms_output.put_line(tt);

end;

-----------------------------需要一张学员信息表的点名程序-------------------------------

--创建一个由号得到姓名的函数

create or replacefunction getNameById(v_id number) return varchar2 as

studentNamevarchar2(50);

begin

select name intostudentName from newstudent where id=v_id;

returnstudentName;

end;

 

--创建随机点名程序

create or replacefunction f_calling return varchar2

as

tt number;

st_namevarchar2(50);

begin

selectabs(mod(dbms_random.random,7))+1 into tt from dual;

dbms_output.put_line(tt);

selectgetNameById(tt) into st_name from dual;

return st_name;

end;

 

---------------------------------无需数据库表的点名程序-----------------------------------

create or replacefunction f_calling return varchar2

as

tt number;

st_namevarchar2(50);

begin

<<nextRom>>

selectabs(mod(dbms_random.random,100))+1 into tt from dual;

dbms_output.put_line(tt);

if tt<=18 then

 goto getName;

 else

 goto nextRom;

 end if;

 <<getName>>

case tt

when 1 thenst_name:='吴鸿飞';

when 2 thenst_name:='董克飞';

when 3 thenst_name:='邓芳林';

when 4 thenst_name:='方宇';

when 5 thenst_name:='郭绍云';

when 6 thenst_name:='何勇波';

when 7 thenst_name:='黄华';

when 8 thenst_name:='刘志';

when 9 thenst_name:='娄俊';

when 10 thenst_name:='吕兵兵';

end case;

return st_name;

end;

 

--调用点名函数

select f_callingfrom dual

select abs(mod(dbms_random.random,3))+1from dual

--触发器

update v_emp_dempve set ve.depname='222' where ve.empno=1

--before 触发器，即在取款之前进行的操作

create or replacetrigger trg_bu_testrigger

before update ofsal on t_testrigger

for each row

begin

if :new.sal>=0then

dbms_output.put_line('取款成功！');

else

 dbms_output.put_line('金额不足！');

 raise_application_error(-20101,'钱不够');

 end if;

end;

--after 触发器，即在某操作结束后的操作

create or replacetrigger trg_af_testrigger

after update ofsal on t_testrigger

for each row

declare

v_getmoey number;

-

begin

v_getmoey:=:old.sal-:new.sal;

 if v_getmoey>=1000 then

 insert into t_testrigger_log

   (record_id, tid, get_money,get_time)

 values

   (sequid.nextval, :new.tid, v_getmoey,sysdate);

   end if;

end;

 

--instead of 对试图修改，其实是修改表

create or replacetrigger trg_v_emp_demp_insert

  instead of insert on v_emp_demp

  for each row

  begin

       insert into dept1(did,depname)

       values(:new.did, :new.depname);

      

       insert into emp(empno,ename,deptno)

       values(:new.empno,:new.ename,:new.did);

      

       exception

       when others then      

         raise_application_error(-20331, '该编号已存在！');

  end;

 

--自建日志表dropped_obj

create tabledropped_obj

(

obj_namevarchar2(20),

obj_typevarchar2(20),

drop_date date

)

 

--创建删除表的信息添加到日志中（自建日志表dropped_obj）

create or replacetrigger trg_log_drop

  after drop on schema

begin

  insert into dropped_obj

    (obj_name, obj_type, drop_date)

  values

    (ora_dict_obj_name, ora_dict_obj_type,sysdate);

end;

 

--把查询结果转换成xml格式

 declare

 result clob;

 xmlstr varchar2(32767);

 line varchar2(2000);

 line_no integer:=1;

 begin

 result:=dbms_xmlquery.getXML('select  e.empno,e.ename,e.deptno from emp e');

 xmlstr:=dbms_lob.substr(result,32767);

 loop

 exit when xmlstr is null;

 line:=substr(xmlstr,1,instr(xmlstr,chr(10))-1);

 dbms_output.put_line(line_no||':'||line);

 xmlstr:=substr(xmlstr,instr(xmlstr,chr(10))+1);

 line_no:=line_no+1;

 end loop;

 end;

 

 

create directoryTEST_DIR AS 'D:\testxml ';

GRANT READ, WRITEON DIRECTORY TEST_DIR TO SCOTT;

 

select *from emp

 

--读取xml文件

DECLARE

  input_file  UTL_FILE.FILE_TYPE;

  input_buffer VARCHAR2(4000);

BEGIN

  input_file := UTL_FILE.FOPEN(

                'TEST_DIR', 'employees.xml', 'r');

  LOOP

    UTL_FILE.GET_LINE(input_file,input_buffer);

    DBMS_OUTPUT.PUT_LINE(input_buffer);

  END LOOP;

  UTL_FILE.FCLOSE(input_file);

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    DBMS_OUTPUT.PUT_LINE('------------------');

END;

--把clob数据写入文件中

declare

 src clob;

 xmlfile utl_file.file_type;

 length integer;

 buffer varchar2(16384);

 begin

 src:=dbms_xmlquery.getXML('select *from emp');

 length:=dbms_lob.getlength(src);

 dbms_lob.read(src,length,1,buffer);

 xmlfile:=utl_file.fopen('TEST_DIR','emp.xml','w');

 utl_file.put(xmlfile,buffer);

 utl_file.fclose(xmlfile);

  end;

 

 select e.empno,e.ename,e.deptno from emp e

 

 select *from emp;

 select *from dept1;

 select * from emp,dept1 whereemp.deptno=dept1.did;

 select *from v_emp_demp

 

 update v_emp_demp v_ed set v_ed.ename='dddd'where v_ed.empno=1;

 

 insert into v_emp_demp v_edvalues(4,'ddddddd',11,'rrrrr')

 

 insert into v_emp_demp v_edvalues(232,'11',66,'rrrrr')

创建序列

create sequencefood_id

minvalue 1

start  with 1

increment by 1

maxvalue 100

cycle

cache 10

--调用序列

insert into fruit

  (frid, frname, price)

values

  (food_id.nextval, '苹果',100.234);