oracle-常用sql语句、游标、触发器、存储过程、函数、包......

一、常用sql语句

聚合函数
COUNT（*）计算元组个数
COUNT（列名）对一列中的值计算个数
SUM（列名）求某一列值的总合
AVG（列名）求某一列值的平均值
MAX（列名）
MIN（列名）

--按照本门号从高到底，工资从高到底查询员工表
select * from personnel order by section desc,sal desc;

--求各个部门工资的平均值
select s.name, avg(p.sal)
  from personnel p
 inner join section s
    on p.section = s.id
 group by s.name
 
 --求各个部门工资的和
select s.name, sum(p.sal)
  from personnel p
 inner join section s
    on p.section = s.id
 group by s.name

--求工资大于财务部平均工资的员工的信息
select *
  from personnel
 where sal > (select avg(sal)
                from personnel
               where section = (select id from section where name = '财务部'))
 
--查询寻所有部门4的工作岗位，在根据工作岗位查询所有在这些工作呢岗位工作呢的员工            
select p.name,p.sal,j.name, s.name
  from personnel p
 inner join job j
    on p.job = j.id
 inner join section s
    on p.section = s.id
 where p.job in (select job from personnel where section = 4 group by job)
 


--各部门工资大于部门平均工资的员工信息
select *
  from personnel p
 where sal > (select avg(sal) from personnel where p.section = section);

--各部门工资大于部门平均工资的员工数量和部门编号
select count(*),p.section
  from personnel p
 where sal > (select avg(sal) from personnel where p.section = section) group by p.section;

 --排序
 select * from personnel order by id desc
 
 --插入
 insert into section (id,name) values(5,'市场部');
 
 --删
 delete section where id = 5;
 
 --改
 update section set name = '尼玛' where id = 5;

 --分页
select *
  from (select e1.*, rownum r
          from (select * from employee) e1
         where rownum < 5)
 where r > 2

--别名
select s.name as "部门",avg(sal) as "平均工资"
  from personnel p
 inner join section s
    on p.section = s.id
 group by s.name


--建表
create table text(
   id number(6) primary key,
   name varchar2(20) not null,
   age number(6) not null,
   manager number(6)
)tablespace users;

--插入数据
insert into text (id,name,age,manager) values (106,'A',30,104);
insert into text (id,name,age,manager) values (109,'B',19,104);
insert into text (id,name,age,manager) values (104,'A',20,111);
insert into text (id,name,age,manager) values (107,'D',35,109);
insert into text (id,name,age,manager) values (112,'E',25,120);
insert into text (id,name,age,manager) values (119,'F',45,null);




//选修了java课程的学生的id和姓名
select s.studentid, s.name
  from student s
 inner join sc x
    on s.studentid = x.studentid
 where x.courseid = (select courseid from course where coursename = 'Java');

--查看熊敏选修了那几门课程
select c.coursename
  from course c
 inner join sc x
    on c.courseid = x.courseid
 where x.studentid = (select studentid from student where name = '熊敏');

--打印选课数大于3的学生的id和姓名
select stu.studentid, stu.name
  from student stu
 where (select count(*) from sc where studentid = stu.studentid) > 3;

--:列出所有年龄比所属主管年龄大的人的ID 和名字?
select em.id,em.name from text em where em.age > (select age from text where em.manager = id);

--删除相同数据
delete from text t where t.id > (select min(x.id) from text x where t.name = x.name);



--用exists代替in
select * from employee e where e.section in (select id from section) order by id;

select * from employee e where exists (select id from section s where e.section = s.id) order by id;


--exists与in比较
select * from 表A where exists(select * from 表B where 表B.id=表A.id)  
--这句相当于 
select * from 表A where id in (select id from 表B)

 

二、游标、触发器、存储过程、函数、包......

打开输出选项 set serveroutput on;
关闭输出选项 set serveroutput off;

一、游标的应用

实例1：
declare
  id number;--申明变量接受游标里查出来的值
  department varchar2(20);
  --申明一个游标:aaaa
  cursor aaaa is select studentid,name from student where name  = '熊敏';
begin
  --打开游标
  open aaaa;
  --判断是否返回记录
  if aaaa%notfound then
    dbms_output.put_line('没有找到相应的记录');
  else
    --从游标中读取数据
    fetch aaaa
      into id,department;
    dbms_output.put_line(id || ',' || department);
  end if;
  close aaaa;
end;

实例2：
--游标循环读取数据
declare --申明  
    id   number; --变量
    name varchar2(20);
 cursor user_table is select studentid,name from student; --申明游标：user_table
begin  --开始
  for user_tables in user_table loop  --循环读取，user_tables为临时变量
    id   := user_tables.studentid;
    name := user_tables.name;
    dbms_output.put_line('学员编号：' || id || '学员姓名：' || name);
  end loop;
end; --结束

二、创建和调用过程
	--无参过程pro_01
	create or replace procedure pro_01 is
begin
  insert into job (id,name) values (4,'总经理');
  end;
 
--调用过程（在命令行中调）
	exec pro_01;

--有参过程pro_02
create  or replace procedure pro_02 
 ----申明五个个输入参数，
----输入字符串不让加长度
 (
       student_id in number,
       student_name in varchar2,
       student_sex in varchar2,
       student_age in number,
       student_department in varchar2
 ) as 
 begin
   insert into student values(student_id,student_name,student_sex,student_age,student_department);
   end;

--调用
exec pro_02(7,'林文伟','男',23,'学习部');
三、函数
--创建一个返回插入条数信息的函数
--申明5个参数的函数
--函数名function_insert_student
--返回值run_number
create or replace function function_insert_student
(
    student in number,
    stu_name in varchar2,
    sex in varchar2,
    age in number,
    department in varchar2
)return number as ret_number number :=0;
begin
  insert into student values(student,stu_name,sex,age,department);
  ret_number := ret_number + 1;
  commit;
  return ret_number;
  end;
--调用函数
  declare 
  --申明一个变量接受函数返回值
  receive_number number;
  begin
    receive_number :=function_insert_student(9,'王正国','男',23,'学习部');
    dbms_output.put_line('一共插入--' || receive_number || '条数据。');
end;

四、包

--创建包头
    create or replace package pack_age as
      --申明过程
      procedure my_procedure(studentid	number,stu_name varchar2,sex varchar2,age number,department varchar2);
      --申明函数
      function my_function(studentid number,stu_name varchar2,sex varchar2,age number,department varchar2) return number;
    end pack_age;
    
    --创建包体
    create or replace package body pack_age as
      --实现过程
      procedure my_procedure(studentid number,stu_name varchar2,sex varchar2,age number,department varchar2) is
        begin
          insert into student values (studentid,stu_name,sex,age,department);
          commit;
        end my_procedure;
     --函数的实现
     function my_function(studentid in number,stu_name in varchar2,sex in varchar2,age in number,department in varchar2)
       return number as return_number number := 0;
       begin
         insert into student values (studentid,stu_name,sex,age,department);
         return_number := return_number + 1;
         commit;
         return return_number;
         end my_function;
    end pack_age;
    
    --包的调用
    declare 
      --申明变量接收函数的返回值
      num_number number;
      begin
          pack_age.my_procedure(10,'黄广','男',23,'学习部');
          num_number := pack_age.my_function(11,'黄云生','男',23,'学习部');
        end;

五、视图

--创建视图
     create or replace view my_view as
     select * from student;
    
     --调用视图
     select * from my_view;

六、触发器

1、测试表结构
  create table a(
    id number(6),
    s_name varchar2(30)
)

create table b(
   id number(6),
s_name varchar2(30)
)

2、--创建出发器-- bck_trg
--单向表a插入数据的时候同时向表b里插入数据
--insert into b values (1,'熊敏')

  create or replace trigger bck_trg
  after insert on a for each row
begin
       insert into b values (1,'熊敏');
end;

--测试
insert into a values(1,'熊敏');



--复制表数据的写法
create or replace trigger tig_test
  after insert on a  
declare
begin
   insert into b (select * from a where a.id=(select max(a.id) from a ));
end tig_test;


--将插入的信息插入到目标表
create or replace trigger tig_a
  after insert on a  
  for each row
declare
  -- 在这里申明变量
begin
   insert into b(id,s_name) values(:NEW.ID,:NEW.S_NAME);
end tig_a;

--将A表删除的数据插入到B表
create or replace trigger tig_a
  after delete on a  
  for each row
declare
  -- local variables here
begin
   insert into b(id,s_name) values(:old.ID,:old.S_NAME);
end tig_a;