存储过程学习笔记

--根据存储过程的参数不同，将存储过程分为四大类
--（1）不带参数的存储过程
--（2）带输入参数的存储过程（in）
--（3）带输出参数的存储过程（out）
--（4）带输入输出参数的存储过程

--（1）不带参数的存储过程

打印九九乘法表

create or replace procedure proc_jiujiu
as
begin
    --外层循环控制行
    for x in 1..9
    loop
        --内层循环控制每行显示多少个表达式
        for y in 1..x
        loop
            dbms_output.put(x || '*' || y || '='||x*y || ' ');
        end loop;  
        dbms_output.new_line;
    end loop;
end;

--执行

execute proc_jiujiu;--只能在命令窗口中执行

--在SQL窗口中
begin
   proc_jiujiu;  
end;

--带输入参数的存储过程（in）,默认的参数就是输出参数

--通过员工编号，查询员工姓名，打印出来 
create or replace procedure proc_emp(eno number) --特别要注意，参数类型不能指定长度
as
   --变量
   empname emp.ename%type;     --员工姓名
begin
   select ename into empname from emp where empno=eno;
   dbms_output.put_line(empname);
end;

--执行
begin
   proc_emp(7499);
end;

--带输出参数的存储过程 out 表示是一个输出参数
create or replace procedure proc_emp(eno number,empname out varchar2) --特别要注意，参数类型不能指定长度
as
begin
   select ename into empname from emp where empno=eno;
end;


--执行带输出参数的存储过程
declare
   empname emp.ename%type;
begin
   proc_emp(7566,empname);
   dbms_output.put_line(empname);
end;

create or replace procedure proc_add(a number,b number,c out number) 

as
begin
      c:=a+b; 
      -- select a+b into c from dual;
end proc_add; 


--执行带输出参数的存储过程
declare
    aa number;
    bb number;
    cc number;
begin
    aa:='&aa';
    bb:='&bb';
    proc_add(aa,bb,cc);
    dbms_output.put_line(cc);
end;      


--带输入输出参数的存储过程  in out
int add(int a,int b){
    b = a+b;
    return b;
}   


create or replace procedure proc_add(a number,b in out number)
as
begin
       b := a+b;
end;


--执行带输入输出参数的存储过程
declare
      bb number;
begin
      bb:='&bb';
      proc_add(2,bb);
      dbms_output.put_line(bb);
end;


--通过员工编号查询此员工的工资
create or replace procedure proc_emp(num in out number)
as
begin
       select sal into num from emp where empno=num;
end;


--执行
declare
       num number;
begin
       num:='&empno';
       proc_emp(num);
       dbms_output.put_line('员工工资'||num);
end;

--逐条逐条的显示每个员工的信息（姓名，工资），
--统计工资大于3000的人数以及工资低于1000的人数
--分析(排位)函数：
rank() 相同的值排位相同，序号跳跃
row_number() 相同的值排位不同，序号连续
dense_rank() 相同的值排位相同，序号连续


select ename,sal,row_number() over (order by sal desc) as rankNum FROM EMP e;




declare
       num1 number(10) default 0; -- 工资大于3000的人数
       num2 number(10) default 0; -- 工资低于1000的人数
       num3 number(10) default 0; -- 保存表中的总记录数
       empname emp.ename%type;--员工姓名
       empsal emp.sal%type;--员工的工资
begin 
       --查询总记录数
       select count(*) into num3 from emp;
       for x in 1..num3
       loop
           select ename,sal into empname,empsal 
           from (
                select ename,sal,row_number() over (order by sal desc) as rankNum FROM EMP e
           ) t
           where t.rankNum = x;
           dbms_output.put_line('员工姓名:'|| empname||'，员工薪水：'||empsal);--打印员工姓名和工资
           --统计工资大于3000的人数
           if empsal >= 3000 then
              num1 :=num1+1;
           end if;
            --统计工资小于1000的人数
           if empsal <= 1000 then
              num2 :=num2+1;
           end if;
       end loop;
       dbms_output.put_line('工资大于3000的人数为:'|| num1);
       dbms_output.put_line('工资小于1000的人数为:'|| num2);
end;