关于oracle

关于Oracle的使用

授权：

切换到system用户 : conn system/123456

grant create view to scott;

 

1.创建视图

create view emp_view as

 select empno,ename,job from emp;

 

2.查询视图

 select * from emp_view;

 

3.向视图中插入一条记录

 insert into emp_view values(1234,'李白','诗人');

 

4.删除视图中的一条记录

  deletefrom emp_view where empno=1234;

 

5.修改视图中的一条记录

 update emp_view set ename='李太白' whereempno=1234;

 

6.删除视图

 drop view emp_view;

 

创建一个复杂的视图（封装了多个基本表）：

create or replace view emp_view as

 select ename,job,dname from emp inner joindept

   onemp.deptno=dept.deptno and emp.ename='SCOTT';

 

创建索引(B树索引)：

create index emp_sal_index on emp(sal);

 

创建student表：

create table student(

  stuid   int  primary key,

  stuname  varchar2(20),

  score  number

);

 

创建序列：

create sequence myseq;

 

create sequence good_sequence

start with 100

increment by 20

maxvalue 2000;

 

插入记录：

insert into student values(myseq.nextval,'杜甫',95);

 

insert into studentvalues(good_sequence.nextval,'白居易',85.5);

1.使用if...then语句比较两个字符串变量的长度，并输出比较结果。

 declare

    var_str1 varchar2(20);

    var_str2 varchar2(20);

 begin

    var_str1:='hello';

    var_str2:='Oracle is good';

    if length(var_str1)<length(var_str2) then

       dbms_output.put_line('字符串"'||var_str1||'"的长度小于"'||var_str2||'"');

    end if;

 end;

  /

 

2.通过if..else语句实现只有年龄大于等于65岁，才可以申请退休，否则程序会提示不可以

  申请退休。

 declare

     age number:=15;

 begin

     if age>=65 then

        dbms_output.put_line('您可以申请退休');

     else

        dbms_output.put_line('年龄不到，不能申请退休!');

     end if;

 end;

  /

 

 

3.指定一个月份数值，然后使用if...then..elsif语句判断它所属的季节，并

  输出季节信息，代码如下。

 declare

    month int:=4;

 begin

    if month>=1 and month<=3 then

       dbms_output.put_line('春天');

    elsif month>=4 and month<=6 then

       dbms_output.put_line('夏日炎炎');

    elsif month>=7 and month<=9 then

       dbms_output.put_line('秋天');

    else

       dbms_output.put_line('冬天很冷');

    end if;

 end;

  /

 

 

4.使用loop语句求前100个自然数的和，并输出到屏幕。

 declare

    iint:=0;

   sum_i int:=0;

 begin

   loop

       i:=i+1;

       sum_i:=sum_i+i;

       exit when i=100;

   end loop;

   dbms_output.put_line('前100个自然数的和是：'||sum_i);

 end;

  /

 

 

5.使用while语句求前100个自然数的和，并输出到屏幕。

 declare

    iint:=0;

   sum_i int:=0;

 begin

   while i<=99 loop

      i:=i+1;

     sum_i:=sum_i+i;

   end loop;

    dbms_output.put_line('前100个自然数的和是：'||sum_i);

 end;

  /

 

 

6.使用for语句求前100个自然数中偶数之和，并输出到屏幕。

 declare

   sum_i int:=0;

 begin

   for i in 1..100 loop

       if mod(i,2)=0 then

          sum_i:=i+sum_i;

       end if;

   end loop;   

   dbms_output.put_line('前100个自然数的和是：'||sum_i);

 end;

  /

 

 

7.指定一个季度数值，然后使用case语句判断它所包含的月份信息并输出。

 declare

    season varchar2(20):='夏天';

 begin

    case season

       when '春天' then

         dbms_output.put_line('包含1、2、3月份');

       when '夏天' then

         dbms_output.put_line('包含4、5、6月份');

       when '秋天' then

         dbms_output.put_line('包含7、8、9月份');

       when '冬天' then

         dbms_output.put_line('包含10、11、12月份');

       else

         dbms_output.put_line('未知季节!');

    end case;

 end;

  /

1.在SCOTT模式下，使用%type类型的变量输出emp表中编号为7369的员工姓名和职务

  信息(考察%type类型)

  setserveroutput on

 declare

    var_ename  emp.ename%type;

    var_job    emp.job%type;

 begin

    select ename,job into var_ename,var_job from emp where empno=7369;

     dbms_output.put_line('编号为7369的员工姓名为：'||var_ename||',职位为'||var_job);

 end;

  /

    

   

 

2.声明一个记录类型emp_type,然后使用该类型的变量存储emp表中编号为7369

  员工的姓名、职务、工资信息，并输出这条记录信息。（考察record类型）

 declare

    type emp_type is record(

        var_ename emp.ename%type,

        var_job   emp.job%type,

        var_sal   emp.sal%type

    );

    emp_info emp_type;

  begin

    select ename,job,sal into emp_info from emp where empno=7369;

    dbms_output.put_line('编号为7369员工姓名：'

           ||emp_info.var_ename||'，职务：'||emp_info.var_job||

           '工资是：'||emp_info.var_sal);

  end;

   /

 

 

3.声明一个%rowtype类型的变量rowVar_emp,然后使用该变量存储emp表中的一行数据。

  (考察%rowtype类型)

 declare

    rowVar_emp emp%rowtype;

 begin

    select * into rowVar_emp from emp where empno=7369;

    dbms_output.put_line('编号为7369的员工姓名为：'||rowVar_emp.ename||

       ',雇佣日期为：'||rowVar_emp.hiredate||'工资为：'||rowVar_emp.sal);

 end;

  /

 

1.使用触发器监听对dept表的操作，并记录到日志表中。

建立日志表：

create table dept_log(

  opertime   date,

  operdesc   varchar2(50)

);

 

创建触发器：

create or replace trigger dept_trigger

 after insert or update or delete

  ondept for each row

 begin

    if inserting then

       insert into dept_log values(sysdate,'执行了insert操作');

    elsif updating then

       insert into dept_log values(sysdate,'执行了update操作');

    elsif deleting then

       insert into dept_log values(sysdate,'执行了delete操作');

    end if;

 end;

  /

 

2.在不指定主键名称的情况下，实现student表主键自增效果。

 

  先创建序列：

 create sequence my_sequ

 start with 1

 increment by 1;

 

 create or replace trigger stu_trigger

 before insert

  onstudent for each row

 begin

    select my_sequ.nextval into :new.stuid from dual;

 end;

  /

 

1.使用存储过程插入dept表一条记录(无参存储过程)。

 create or replace procedure dept_proc is

   var_dname dept.dname%type;

 begin

   var_dname:='总裁部';

   insert into dept values(95,var_dname,'中国');

   commit;

 end;

  /

 

2.使用存储过程查询指定编号的员工信息(使用了in参数)。

 create or replace procedure empno_proc(var_empno in int) is

 type emp_record is record(

     var_ename emp.ename%type,

     var_job   emp.job%type

  );

 empinfo emp_record;

 begin

    select ename,job into empinfo from emp where empno=var_empno;

    dbms_output.put_line(var_empno||'工号的员工姓名是：'||empinfo.var_ename||

       ',职位是：'||empinfo.var_job);

 end;

  /

 

3.根据员工编号查询其姓名(使用了out参数)

 create or replace procedure

       empno_proc(var_empno in int,var_ename out varchar2) is

 begin

    select ename into var_ename from emp where empno=var_empno;

 end;

  /

 

  在PL/SQL中调用存储过程：

 declare

     var_good emp.ename%type;

 begin   

    empno_proc(7369,var_good);

    dbms_output.put_line('查询到的姓名为：'||var_good);

 end;

  /

 

4.使用in out类型参数根据工号查询工资。

 create or replace procedure emp_proc(var_info in out number) is

 begin

   select sal into var_info from emp where empno=var_info;

 end;

  /

 

  在PL/SQL中调用该存储过程：

 declare

   var_emp_info number:=7369;

 begin

   emp_proc(var_emp_info);

   dbms_output.put_line('该工号的员工工资是：'||var_emp_info);

 end;

  /

 

  定义一个PL/SQL代码块，计算两个整数的和与这两个整数的

  差的商。

  setserveroutput on

 declare

    aint:=100;

    bint:=200;

    cnumber;

 begin

   c:=(a+b)/(a-b);

   dbms_output.put_line(c);

   exception

     when zero_divide then

       dbms_output.put_line('除数不许为零！');

 end;

  /