精通pl/sql编程

1.什么是plsql,有什么作用
plsql全称Procedure Language+SQL,
属于Oracle数据库特有编程语言.
它是在SQL语句基础上引入了变量,
分支,循环逻辑。
plsql程序可以存储在oracle数据库中
主要适用于完成大批量基于数据库
数据的运算处理。
2.plsql基本语法
  1）基本语法
  //匿名块,不能存在oracle中
  declare
    ... ...//声明定义变量(可省略)
  begin
    ... ...//主处理
  exception
    ... ...//异常处理(可省略)
  end;
  /


2）打印输出命令
   //在Oracle控制台打印输出消息
   dbms_output.put_line('消息');
   //默认显示消息功能关闭,需要开启
   set serveroutput on;


begin
  dbms_output.put_line('Hello World');
end;
/


 3)plsql注释
  -- 单行注释
  /*
   多行注释
  */


 4)变量定义和赋值
  declare
     msg varchar2(50);--默认null
     age number(3) := 0;--赋值
  begin
     dbms_output.put_line(msg);
     dbms_output.put_line(age);
  end;
  /
  注意：:=为赋值语句;单个=为比较,
  判断是否相等.


 5)if分支
  if 条件1 then
    ....
  elsif 条件2 then
    ...
  else
    ...
  end if;


  declare
    sal number(7,2) := 0;
  begin
    if sal = 0 then
      dbms_output.put_line('待业');
    elsif sal < 3000 then
      dbms_output.put_line('低保户');
    elsif sal <8000 then
      dbms_output.put_line('屌丝');
    else
      dbms_output.put_line('高级屌丝');
    end if;
  end;
  /


 6)循环语句
  a.LOOP循环
  loop
    //循环执行的逻辑
    exit when 退出条件;
  end loop;
==============
  declare
    i number(2) := 1;
  begin
    loop
      dbms_output.put_line(i);
      i := i+1;
      exit when i>10;
    end loop;
  end;
  /
  
  b.while循环
   WHILE 满足条件 LOOP
     //循环逻辑
   END LOOP;


  declare
    i number(3) := 1;
  begin
    while i<=10 loop
      dbms_output.put_line(i);
      i := i+1;
    end loop;
  end;
  /


  3)for循环
  for 变量 in 数字集合 loop
    //循环执行逻辑
  end loop;
  提示：变量自动定义;集合仅限于
数字或游标
  ============
  begin
     for i in 1..10 loop
       dbms_output.put_line(i);
     end loop;
  end;
  /


  7)DML语句
 DML的SQL语句和以前学习的一致，
直接可以写在PL/SQL中
  create table p001(
  id number(5),
  name varchar2(20)
  );
  --循环插入10条记录
  begin
    for i in 1..10 loop
      insert into p001 (id,name)
       values (i,'scott'||i);
    end loop;
    commit;--提交事务
  end;
  /


  8)Select语句
  select 字段1,字段2
  into 变量1,变量2
  from 表
  where 条件
  提示：select语句仅适用于有且只有
一条记录的情况。如果查询多行结果
得需要使用游标技术。
  declare
    v_name varchar2(20);
  begin
    select name 
    into v_name
    from p001
    where id=5;
    dbms_output.put_line(v_name);
  end;
  /


  9)游标
  作用：查询多行结果的记录
  使用方法：
   declare声明定义游标
    CURSOR 游标名 IS select语句;
   begin使用方法
    //首先打开游标OPEN 游标名;
    //然后循环从游标取数据
     fetch 游标名 into 变量;
    //最后关闭游标CLOSE 游标名;
==================
declare
  cursor c_p001 is 
     select name from p001;
  v_name varchar2(20);
begin
  open c_p001;
  loop
    fetch c_p001 into v_name;
    exit when c_p001%NOTFOUND;
    dbms_output.put_line(v_name);
  end loop;
  close c_p001;
end;
/


 10)DDL语句
 createtable,drop table,truncate table
 alter table等命令。
 DDL必须采用动态编译执行方法执行。
begin
  execute immediate  
       'drop table p001';
end;
/ 


begin
   execute immediate 
    'create table p002 (id number(5))';
   execute immediate 
     'insert into p002 values (1)';
end;
/


 11)%TYPE和%ROWTYPE
  变量名 表名.字段%TYPE;
  v_name emp.name%TYPE;
  含义:定义一个v_name变量,类型与
emp表的name字段一致。


 declare
    v_name emp.name%type;
    v_sal emp.salary%type;
  begin
     select name,salary 
     into v_name,v_sal
     from emp
     where id=2;
     dbms_output.put_line(v_name);
     dbms_output.put_line(v_sal);
  end;
  /


  变量名 表名%ROWTYPE;
  含义：声明一个变量,变量类型为
记录类型。可以存储一行记录信息
  v_emp emp%rowtype;
 
  declare
    v_emp emp%rowtype;
  begin
    select *
    into v_emp
    from emp 
    where id=2;
    dbms_output.put_line(v_emp.name);
    dbms_output.put_line(v_emp.salary);
  end;
  /


====================
查询所有员工的name和sal
declare
   cursor c_emp is 
      select name,sal from emp;
begin
  for v_emp in c_emp loop
    dbms_output.put_line(
      v_emp.name||' '||v_emp.sal);
  end loop;
end;
/
===================
根据EMP表员工的工资信息,
计算个人应该缴纳多少税。
工资总额-3500
1档 <1500      超出部分*3%-0
2档 >=1500 && <4500  超出部分*10%-105
3档 >=4500 && <9000 超出部分*20%-555
其他        超出部分*25-1005


要求：编写一段PL/SQL显示EMP表中
员工名 工资 个税3部分值
declare
  cursor c_emp is 
      select * from emp;
  v_sal emp.salary%type;
  v_tax emp.salary%type;
begin
   --利用游标循环遍历每个EMP记录
   for v_emp in c_emp loop
   --循环中根据员工的salary计算个税
   v_sal := v_emp.salary-3500;
   if v_sal <= 0 then
      v_tax := 0;
   elsif v_sal < 1500 then
      v_tax := v_sal*0.03;
   elsif v_sal < 4500 then
      v_tax := v_sal*0.1-105;
   elsif v_sal < 9000 then
      v_tax := v_sal*0.2-555;
   else
      v_tax := v_sal*0.25-1005;
   end if;
   --循环中显示员工名 工资 个税3部分值
   dbms_output.put_line(
    v_emp.name||' '||v_emp.salary||' '||v_tax);
   end loop;
end;
/

需求：根据 EMP员工信息,
生成EMP_TAX纳税记录
ID
NAME
SALARY
TAX
TAX_DATE
create table emp_tax(
id number(10),
name varchar2(20),
salary number(7,2),
tax number(7,2),
tax_date date);


==============
declare
  cursor c_emp is 
      select * from emp;
  v_sal emp.salary%type;
  v_tax emp.salary%type;
begin
   --利用游标循环遍历每个EMP记录
   for v_emp in c_emp loop
   --循环中根据员工的salary计算个税
   v_sal := v_emp.salary-3500;
   if v_sal <= 0 then
      v_tax := 0;
   elsif v_sal < 1500 then
      v_tax := v_sal*0.03;
   elsif v_sal < 4500 then
      v_tax := v_sal*0.1-105;
   elsif v_sal < 9000 then
      v_tax := v_sal*0.2-555;
   else
      v_tax := v_sal*0.25-1005;
   end if;
   --写入纳税记录表中
   insert into emp_tax (id,name,salary,tax,tax_date)
     values (v_emp.id,v_emp.name,v_emp.salary,v_tax,sysdate);
   end loop;
   commit;
end;
/