SQL以及PLSQL上课重点代码

1\
select cname , nvl(s.scid,0)
from clazz,(select scid,count(scid)
            from student
            group by scid;) s
where cid=scid(+);

2\
select sid,sname,age
from(select sid,sname,age,rownum r from (select sid,sname,age from ren_student order by age) where rownum<=6)
where r>=3;//非常重要

3\
//查找表，很有用
select table_name
from user_tables
where table_name like '%REN%';

4\
create sequence ren_stu;
alter sequence ren_stu increment by 2 start with 10;//不能修改start with；
insert into ren_student2 values(ren_stu.nextval,'ren',1);//从1开始 每次加1；
insert into ren_class2 values(ren_stu.nextval,'C++');

5\
rowid
通过rowid 删除指定字段的重复记录，重复的留一条
//老师写的,非常重要
delete from ren_student2 a
where rowid>
(select min(rowid) from ren_student2 b where a.sname=b.sname);

6\随时取几条记录
select * from(
select * from ren_student2
order by dbms_random.random
)where rownum<3;

7\
select ename
from ren_emp a
where sal>(
select avg(sal)
from ren_emp b
where a.deptno=b.deptno
);

8\
mySQL
create database mydb;
show databases;

9\

PL/SQL   sqlplus rensx/rensx
create table ren_student(
    sid number(3) ,
    sname varchar(10)
);

游标的使用
declare v_id ren_student.sid%TYPE :=2;
        v_student ren_student%rowtype;

    cursor cur_stu is
     select * from ren_student
     where sid=v_id;
begin
    open  cur_stu;
    fetch cur_stu into v_student;
    DBMS_OUTPUT.PUT_LINE(v_student.sname);

    close cur_stu;
END;

游标循环的使用
declare v_id ren_student.sid%TYPE :=2;
        v_student ren_student%rowtype;

    cursor cur_stu is
     select * from ren_student
     where sid=v_id;
begin
    open  cur_stu;

    fetch cur_stu into v_student;

    while cur_stu%found loop
    DBMS_OUTPUT.PUT_LINE(v_student.sname);
    fetch cur_stu into v_student;
    end loop;

    close cur_stu;

END;
游标for循环的使用
declare v_id ren_student.sid%TYPE :=2;
        v_student ren_student%rowtype;

    cursor cur_stu is
     select * from ren_student
     where sid=v_id;
begin
    for v_emp in cur_stu loop
     DBMS_OUTPUT.PUT_LINE(v_emp.sname);
     end loop;
END;

异常
declare
        v_stu ren_student%rowtype;
begin
        select * into v_stu这个灯是什么什么材料做的啊?黄
        from ren_student
        where sid=45;
        DBMS_OUTPUT.PUT_LINE(v_stu.sname);
exception
        when no_data_found then
          DBMS_OUTPUT.PUT_LINE('v_stu:no data found');
end;

自己定义异常
declare
    e_myexception exception;
    v_stu ren_student%rowtype;
begin
    select * into v_stu
    from ren_student
    where sid=5;
    DBMS_OUTPUT.PUT_LINE(v_stu.sname);

    if v_stu.sid=5 then
    raise e_myexception;
        end if;
exception
    when e_myexception then
      DBMS_OUTPUT.PUT_LINE('123');
end;

存储块(在新文件里调用)
create or replace procedure ren_pro(p_id ren_student.sid%TYPE)as
    v_emp ren_student%rowtype;
begin
    select * into v_emp
     from ren_student
      where sid=p_id;
    DBMS_OUTPUT.PUT_LINE(v_emp.sname);
end;

begin
   ren_pro(5);
end;@ren

create or replace procedure ren_pro(
p_id [in] ren_student.sid%TYPE,
p_name out ren_student.sname%type
)as
        v_emp ren_student%rowtype;
begin
        select * into v_emp
         from ren_student
         where sid=p_id;

        p_name:=v_emp.sname;
        DBMS_OUTPUT.PUT_LINE(v_emp.sname);
end;

declare
   v_name ren_student.sname%type;
begin
   ren_pro(5,v_name);
   DBMS_OUTPUT.PUT_LINE('diao_yong:'||v_name);
end;

函数
create or replace function ren_fun(
p_id  ren_student.sid%TYPE)
return ren_student.sname%type
as
        v_emp ren_student%rowtype;
begin
        select * into v_emp
         from ren_student
         where sid=p_id;

        return v_emp.sname;
end;

declare
   v_name ren_student.sname%type;
begin
   v_name:=ren_pro(5);
   DBMS_OUTPUT.PUT_LINE('diao_yong:'||v_name);
end;

包的建立
create or replace package ren_pack as
  v_emp ren_student%rowtype;
  procedure addEmp(p_emp ren_student%rowtype);
end ren_pack;

create or replace package body ren_pack as
  procedure addEmp(p_emp ren_student%rowtype)as
  begin
     insert into ren_student
        values(p_emp.sid,p_emp.sname);
     commit;
  end;
end ren_pack;

declare
   v_stu ren_student%ROWTYPE;
begin
    v_stu.sid:=101;
    v_stu.sname:='101ren';
   ren_pack.addEmp(v_stu);
end;//插入数据成功

作业 给个id 知道他的领导（存储块）
   函数 给定地区id 工资最高的员工所在部门 其名称
09.7.17*******************************
触发器
create or replace trigger ren_tr
  after insert or update or delete on ren_student
declare
   v_cnt number;
begin
   select count(*) into v_cnt
   from ren_student;
   DBMS_OUTPUT.PUT_LINE('v_cnt:'||v_cnt);
end  ren_tr;

不能删root 用户 触发器中:old :new表的使用
create or replace trigger ren_del
  before delete on ren_student
  for each row
begin
   if:old.sid=1 then
          raise_application_error(-20100,'root');//必须大于20000
   end if;
end  ren_del;

记录对表的操作
create table renlog(
    username varchar2(20),
        sysd date,
        opr varchar2(20)
);
create or replace trigger ren_dml
  after insert or update or delete on ren_student
begin
   if inserting then
        insert into renlog values(user,sysdate,'insert');
   elsif updating then
        insert into renlog values(user,sysdate,'update');
   elsif deteting then
        insert into renlog values(user,sysdate,'detete');
   else
      null;
   end if;
end  ren_dml;