数据库编程

PL SQL 块,基本写法

declare -- 声明
  a int;
  b int;
  res int;
begin -- 逻辑开始执行
  a := &a;
  b := &b;
  res := a + b;
  dbms_output.put_line(res); -- 输出到控制台 
end; -- 代码块的结束

--一个例子
declare  --变量声明

  --参照创建表，考虑变量声明和表中数据是否匹配
  v_ename varchar2(10);
  v_sal number(7,2);

begin    --开始执行

  v_ename := &v_ename;
  --into 将查询的结果输出到变量
  select sal into v_sal from emp where ename=v_ename;
  dbms_output.put_line(v_sal);   --输出
  
  --异常处理
  exception 
  when  no_data_found then
    dbms_output.put_line('没有' || v_ename || '这个用户~');

end;

上面的代码：1.定义变量时，我们要参考表的列类型

                      2. 用户输入需要按照指定使用大写

列类型   emp.ename%type  让程序自己去看列类型

行类型    % rowtype

declare  --变量声明

  --参照创建表，考虑变量声明和表中数据是否匹配
  v_ename varchar2(10);
  v_row emp%rowtype;   --自动匹配emp 表的行

begin    --开始执行

  v_ename := &v_ename;
  --into 将查询的结果输出到变量
  select * into v_row from emp where ename=upper(v_ename);
  dbms_output.put_line(v_row.ename || '---' || v_row.sal);   --输出
  
  --异常处理
  exception 
  when  no_data_found then
    dbms_output.put_line('没有' || v_ename || '这个用户~');

end;

循环loop

declare
  x int;
  y int;
begin
  x := &x;
  y := &y;
  loop 
    dbms_output.put_line(x);
    exit when x=y;    --退出循环
    x := x+1;
  end loop;
end;

for循环，主要用于变量集合

declare
  x int;
begin 
  for x in 1..10 
  loop
    dbms_output.put_line(x);
  end loop;
end;

while 循环

declare
  x int;
begin
  x := 1;
  while x <=10 loop
    dbms_output.put_line(x);
    x := x + 1;
  end loop;
end;

分支 if else

/* 分支  if else */
-- 输入员工的姓名, 判断员工的工资等级 
/*
>=2500  D
>=2000  C
>=1500  B
        A
*/

declare 
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  v_ename := &v_ename;
 select sal into v_sal from emp where ename = upper(v_ename);
  if v_sal >= 2500 then
    dbms_output.put_line('D');
  elsif  v_sal >= 2000 then
    dbms_output.put_line('C');
  elsif  v_sal >= 1500 then
    dbms_output.put_line('B');
  else
    dbms_output.put_line('A');
  end if;
end;

游标，遍历结果集

定义:CURSOR <游标名> IS <SELECT 语句> [FOR UPDATE | FOR UPDATE OF 字段];

declare
  --定义游标
  cursor mycur is select * from emp;
  v_row emp%rowtype;
begin
  --打开游标
  open mycur;
  loop  --循环
  fetch mycur into v_row;  --读取了一行数据
  --fetch失败退出循环，此时没有数据了
  exit when mycur%notfound;
  dbms_output.put_line(v_row.ename);
  end loop;
  --关闭游标
  close mycur;
end;

函数，提高代码的重复利用

有且仅有一个返回值

--fn1是函数名()里面是对应的参数，若无参数，则不用写
create function fn1(x int,y int) return int   --函数声明部分
is  --变量声明部分
  res int;
begin 
  res := x+y;
  return res;
end;
--从字典中查看此函数
select * from user_procedures;
--使用
select fn1(1,2) from dual;

存储过程

存储过程 和 函数 都是 过程 
-- 作用, 给第三方调用, 提供第三方调用的接口  Java 
-- 分页的结果 , 得到 总页数,没有的数据集. 提供的参数 页码, 每页的数据量

-- 定义一个游标类型保存到一个 package 中
create package pk_commons is type mycur is ref cursor;
end;

-- in 表示 入参 , out 表示返回值  inout 既可以当入参也可以当出参
create or replace procedure pro_page(
v_current in int, 
v_count in int,
v_page out int,
v_cur out pk_commons.mycur
)
as
  v_total int; -- 总条数
  v_sql varchar2(1000);
  v_s int;
  v_e int;
begin
  select count(ename) into v_total from emp;
  v_page := ceil(v_total/v_count);
  v_s := (v_current-1) * v_count; -- 0 5 10
  v_e := v_current * v_count; -- 5 10 15
  v_sql := 'select * from (select e.*,rownum rn from emp e where rownum <='||v_e||') t  where t.rn >'||v_s;
  open v_cur for v_sql;
end;

-- [授权]
/*
连接到数据库 scott。
执行 PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
执行 PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.100.1', '58011' )
ORA-01031: 权限不足
ORA-06512: 在 "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: 在 line 1
此会话需要 DEBUG CONNECT SESSION 和 DEBUG ANY PROCEDURE 用户权限。
进程已退出。
从数据库 scott 断开连接。
*/
grant DEBUG CONNECT SESSION,DEBUG ANY PROCEDURE to scott;

练习

--数据库编程练习
--员工编码 从1000开始 用序列
create sequence seq_emp_id
start with 990
increment by 1
nocycle;
drop sequence seq_emp_id;
select seq_emp_id.nextval from dual;
--dbms_random.string('U',dbms_random.value(3,5))生成一个长度在3到5之间的随机字符串，其中包含大写字母（'U'表示大写字母）
--员工姓名
--生成3,5位的随机姓名
select dbms_random.string('U',dbms_random.value(3,5)) from dual;
--若姓名相同，则重新生成

--员工工作,从emp表中的job随机赋值，同时job不能为MANAGER 用随机序列排列，然后是伪列的第一个
select job 
from (
  select job,rownum,dbms_random.value 
  from emp 
  where job!='MANAGER' order by dbms_random.value
) where rownum=1;

--员工领导和部门编码，根据job指定，随机一个即可
select job,mgr,deptno 
from (
  select job,mgr,deptno,rownum,dbms_random.value 
  from emp 
  where job!='MANAGER' order by dbms_random.value
) where rownum=1; 

--入职时间在10--20年之间
select trunc(sysdate)-dbms_random.value(3650,7300) from dual;

--薪资 为所在部门的最低和最高之间
select round(dbms_random.value
((select min(sal) from emp where deptno=
 (select deptno 
 from (
   select job,mgr,deptno,rownum,dbms_random.value 
   from emp 
   where job!='MANAGER' order by dbms_random.value
 ) where rownum=1))
,
 (select max(sal) from emp where deptno=
 (select deptno 
 from (
   select job,mgr,deptno,rownum,dbms_random.value 
   from emp 
   where job!='MANAGER' order by dbms_random.value
 ) where rownum=1))
)) from dual;

--奖金  若job为SALESMAN 则设置奖金，为公司最低和最高之间
select round(dbms_random.value
((select min(comm) from emp)
,
(select max(comm) from emp))) from dual;


--在以上基础开始编程,插入10个员工
declare
  x int;
  v_empno   emp.empno%type;
  v_ename   emp.ename%type;
  v_job     emp.job%type;
  v_mgr     emp.mgr%type;
  v_sal     emp.sal%type;
  v_comm    emp.comm%type;
  v_deptno  emp.deptno%type;
  v_hiredate emp.hiredate%type;
begin 
  for x in 1..10
  loop
  --设置编号
  v_empno := seq_emp_id.nextval;
  --设置名称
  v_ename := dbms_random.string('U',dbms_random.value(3,5));
  --设置入职时间
  v_hiredate := trunc(sysdate)-dbms_random.value(3650,7300);
  --设置工作，领导，部门编码
  select job,mgr,deptno into v_job,v_mgr,v_deptno 
  from (
    select job,mgr,deptno,rownum,dbms_random.value 
    from emp 
    where job!='MANAGER' order by dbms_random.value
  ) where rownum=1;
  --设置工资
  select round(dbms_random.value(
                  (select min(sal) from emp where deptno=v_deptno)
                  ,
                  (select max(sal) from emp where deptno=v_deptno))
              ) 
  into v_sal from dual;
  --设置奖金
  if v_job='SALESMAN' then
    select round(dbms_random.value
          ((select min(comm) from emp)
           ,
          (select max(comm) from emp))) 
    into v_comm from dual;
  else 
    v_comm := null;
  end if;
  
  --插入数据
  insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
  values(v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno);
  end loop;
end;