PL SQL基础

以下是我学习过程中所做的笔记，整理出来希望可以帮助到刚入门的人

/*
PL/SQL程序的基本单元是块(BLOCK)，块就是实现一定功能的
逻辑模块。一个PL/SQL程序由一个或多个块组成。块有固定的结
构，也可以嵌套。一个块可以包括三个部分，每个部分由一个关键字
标识。

块中各部分的作用解释如下：
(1) DECLARE：声明部分标志。
(2) BEGIN：可执行部分标志。
(3) EXCEPTION：异常处理部分标志。
(4) END；：程序结束标志。
*/

--要输出结果需要在SQL*Plus环境下执行一次以下的环境设置命令
-- SET SERVEROUTPUT ON

/*
1. SELECT 列名1，列名2... INTO 变量1，变量2... FROM 表名
WHERE 条件；

查询雇员编号为7788的雇员姓名和工资
*/
DECLARE --声明部分标识
  v_name VARCHAR2(10); --定义字符串 
  v_sal  number(5); --定义数据变量
BEGIN
  --可执行部分标识
  SELECT emp.ename, emp.sal
    INTO v_name, v_sal
    FROM SCOTT.EMP emp
   WHERE emp.empno = 7788;
   --输出雇员名和工资
   DBMS_OUTPUT.put_line('7788号雇员是：'||v_name||'，工资为：'||to_char(v_sal));
END;--结束标识

--输出结果为：7788号雇员是：SCOTT，工资为：3000

--PL/SQL程序的可执行语句、SQL语句和END结束标识都要以分号结束


/*
  数据类型
  
  数据类型               子类型
  BINARY_INTEGER          NATURAL,POSITIVE
  NUMBER                  DEC,DECIMAL,DOUBLE,PRECISION,PLOAT,INTEGER,INT,NUMERIC,REAL,SMALLINT
  CHAR                    CHARACTER,STRING
  VARCHAR2                VARCHAR
  
  
  类型标识符                   说 明
  NUMBER                       数值型
  INT                          整数型
  BINARY_INTEGER               整数型，带符号
  CHAR                         定长字符型，最大32 767 个字符
  VARCHAR2                     变长字符型，最大32 767 个字符
  LONG                         变长字符型，最长2 GB
  DATE                         日期型，用于存储日期和时间
  BOOLEAN                      布尔型，用于存储逻辑值TRUE 和FALSE
  LOB                          大对象类型，用来存储非结构化数据，长度可达4 GB
*/

/*
2. 变量的定义和初始化
定义了6 个变量， 分别用“ :=” 赋值运算符或
DEFAULT 关键字对变量进行了初始化或赋值。其中：c_tax_rate为常
量，在数据类型前加了“CONSTANT” 关键字；v_valid变量在赋值运算
符前面加了关键字“NOT NULL”，强制不能为空。如果变量是布尔型，
它的值只能是“TRUE”、“FALSE”或“NULL”。变量v_valid
布尔变量的值只能取“TRUE”或“FALSE”
*/
DECLARE --声明部分标识
v_job varchar2(9);
v_count BINARY_INTEGER DEFAULT 0; --DEFAULT用来为变量赋初值
v_total_sal number(9,1) := 0; --∶=用来为变量赋初值
v_date date := sysdate + 7;
c_tax_rate constant number(3,2) := 8.25; --常量
v_valid boolean not null := true; --不能为空
BEGIN
  v_job := 'MANAGER';
  --在程序中赋值
  DBMS_OUTPUT.put_line(v_job);
  --输出变量v_job的值
  DBMS_OUTPUT.put_line(v_count);
  --输出变量v_count的值
  DBMS_OUTPUT.put_line(v_date);
  --输出变量v_date的值
  DBMS_OUTPUT.put_line(c_tax_rate);
  --输出变量c_tax_rate的值
  END;  
  /* 执行结果
  MANAGER
  0
  04-3月 -17
  8.25
  */
  
/*
 3.根据表的字段定义变量
 变量的声明可以根据数据库表的字段进行或根据已经定义的变量进行定义。方式是在表的字段名或已经定义的
 变量后加%TYPE,将其当作数据类型。
 变量名 表名.字段名%TYPE
*/

DECLARE 
v_ename scott.emp.ename%TYPE;--根据字段定义变量
BEGIN
  SELECT emp.ename
  INTO v_ename
  FROM scott.emp emp 
  where emp.empno = 7788;
  DBMS_OUTPUT.put_line(v_ename);
  --输出变量的值
  END;
  --输出结果为：SCOTT

/*
4. 结合变量的使用
结合变量在整个SQL*Plus下有效，使用结合变量可以在不同的程序之间传递信息
使用VARIABLE定义，使用PRING输出
*/
--STEP 1:在SQL*Plus里面创建结合变量g_ename;
--VARIABLE g_ename VARCHAR2(100)

--STEP 2:输入和执行以下程序
BEGIN
  :g_ename:=:g_ename||'Hello~~';
  DBMS_OUTPUT.PUT_LINE(:g_ename);
  END;

/*
5. 记录变量的定义
根据表或视图的一个记录中的所有字段定义变量，称为记录变量。
记录变量包含若干个字段，在结构上同表的一个记录相同，定义的方法是在表名后面加上%ROWTYPE
定义的方法：
记录变量名 表名%ROWTYPE
获取记录变量的字段的方法：
记录变量名.字段名
*/

DECLARE 
emp_record scott.emp%ROWTYPE; --定义记录变量
BEGIN
  SELECT * INTO emp_record
  FROM scott.emp
  where scott.emp.empno = 7788;
  DBMS_OUTPUT.put_line(emp_record.empno||'-'||emp_record.ename||'-'||emp_record.sal);
  end;

--输出结果：7788-SCOTT-3000

/*
6. TABLE类型变量
TABLE类型变量存储可变长度的一维数组数据，即数组中的数据动态的增长。
要定义TABLE变量，需要先定义TABLE数据类型。通过使用下标来引用TABLE变量的元素。
定义方法：
 TYPE 类型名 IS TABLE OF 数据类型[NOT NULL] INDEX BY BINARY_INTEGER
*/

DECLARE
TYPE type_table is TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;--类型说明
v_t type_table; --定义TABLE变量
BEGIN 
  v_t(1) := 'MONDAY';
  v_t(2) := 'TUESDAY';
  v_t(3) := 'WEDNESDAY';
  v_t(4) := 'THURSDAY';
  v_t(5) := 'FRIDAY';
DBMS_OUTPUT.put_line(v_t(3));
END;
--数据结果：WEDNESDAY

/*
运算符和函数
算数运算符：+、-、*、/、**(指数)
关系运算符：<、<=、>、>=、!=/<>
字符运算符：||
逻辑运算符：AND OR NOT
特殊运算符： IS NOLL、IS NOT NULL、LIKE、BETWEEN AND、IN(...)

对空值的算术和比较运算的结果都是空，但对空值可以进行连接运
NULL|| 'ABC' 的结果为'ABC'。
*/

/*
结构控制语句
IF 条件1 THEN
语句序列1；
[ELSIF 条件2 THEN
语句序列2；
ELSE
语句序列n；]
END IF;
其中：
条件部分是一个逻辑表达式，值只能是真、否或者空
IF-THEN-END IF
IF-THEN-ELSE-END IF
IF-THEN-ELSIF-ELSE-END IF
*/

-- IF_THEN-END IF
-- 如果温度大于30，则显示温度偏高
DECLARE
v_temprature number(5):=32;
v_result boolean:=false;
BEGIN
  v_result:=v_temprature > 30;
  IF v_result THEN
    DBMS_OUTPUT.PUT_LINE('温度'||v_temprature||'度，偏高');
    END IF;
 END;
--执行结果：温度32度，偏高

-- IF_THEN-ELSE-END IF
-- 根据性别，显示尊称。
DECLARE
v_sex varchar2(2);
v_titil varchar2(10);
BEGIN
  v_sex := '男';
  IF v_sex='男' THEN
    v_titil:='先生';
    ELSE
      v_titil:='女士';
      END IF;
      DBMS_OUTPUT.PUT_LINE(v_titil||'您好！');
END;
--输出结果：先生您好！

--IF-THEN-ELSIF-ELSE-END IF
--根据雇员工资分级显示税金
DECLARE
v_sal NUMBER(5);
v_tax NUMBER(5,2);
BEGIN
  SELECT emp.sal INTO v_sal
  FROM SCOTT.EMP
  WHERE SCOTT.EMP.EMPNO=7788;
  IF v_sal >= 3000 THEN
    v_tax:=v_sal*0.08;
    ELSIF v_sal >= 1500 THEN
     v_tax:=v_sal*0.06;
    ELSE
      v_tax:=v_sal*0.04;
      END IF;
      DBMS_OUTPUT.PUT_LINE('赢缴税金：'||v_tax);
      END;
--输出结果：赢缴税金：240

/*
选择结构
语法：
CASE 选择变量名
  WHEN 表达式 THEN
  语句序列1
  WHEN 表达式 THEN
  语句序列2
  WHEN 表达式 THEN
  语句序列n
  ELSE
    语句序列n+1
    END CASE;
*/

DECLARE
v_job varchar2(10);
BEGIN
  SELECT emp.job INTO v_job
  FROM SCOTT.EMP emp
  WHERE emp.empno=7839;
  CASE v_job
    WHEN 'PRESIDENT' THEN
    DBMS_OUTPUT.PUT_LINE('雇员职务:总裁');
     WHEN 'MANAGER' THEN
    DBMS_OUTPUT.PUT_LINE('雇员职务:总裁');
     WHEN 'SALESMAN' THEN
    DBMS_OUTPUT.PUT_LINE('雇员职务:总裁');
     WHEN 'ANALYST' THEN
    DBMS_OUTPUT.PUT_LINE('雇员职务:总裁');
     WHEN 'CLERK' THEN
    DBMS_OUTPUT.PUT_LINE('雇员职务:总裁');
    ELSE
      DBMS_OUTPUT.PUT_LINE('雇员职务:未知');
      END CASE;
      END;
 --输出结果：雇员职务:总裁
     
/*
表达式结构CASE语句
语法：
变量=CASE 选择变量名
WHEN 表达式1 THEN 值1
WHEN 表达式2 THEN 值2
WHEN 表达式n THEN 值n
ELSE值n+1
END;  
*/
DECLARE 
v_grade varchar2(10);
v_result varchar2(10);
BEGIN
  v_grade:='B';
  v_result:=CASE v_grade
  WHEN 'A' THEN '优' 
  WHEN 'B' THEN '良'
  WHEN 'C' THEN '中'     
  WHEN 'D' THEN '差'
    END;
    DBMS_OUTPUT.put_line('评价等级:'||V_result);        
    END;    
--输出结果：评价等级:良      
      
/*
搜索CASE结构
没有选择变量，直接判断条件表达式的值，根据条件表达式决定转向
CASE
  WHEN 表达式1  THEN
    语句序列1
  WHEN 表达式2 THEN
    语句序列2
*/      
 DECLARE 
  v_sal varchar2(5);
  begin
    select emp.sal into v_sal from scott.emp emp where emp.empno = 7788;
    case
      when v_sal >= 3000 then
         DBMS_OUTPUT.PUT_LINE('工资等级：高');
      when v_sal >= 1500 then
         DBMS_OUTPUT.PUT_LINE('工资等级：中');       
      else
        DBMS_OUTPUT.PUT_LINE('工资等级：低');
        end case;
        end;
 --输出结果：工资等级：高     
      

/*
 循环结构
 1. LOOP
 2. FOR LOOP
 3. WHILE LOOP
*/

/*
 LOOP  --循环起始标识
   语句1；
   语句2；
   EXIT[WHEN 条件]；
   END LOOP；--循环结束标识
   该循环的作用是反复执行LOOP与END LOOP之间的语句
   EXIT用于在循环过程中退出循环，WHEN用于定义EXIT的退出条件。如果没有WHEN条件，遇到EXIT语句
   则无条件退出循环
   
   1²+3²+5²+...+15²的值
*/
DECLARE
v_total number(5):=0;
v_count number(5):=1;
begin
  loop
  v_total:=v_total+v_count**2;
  exit when v_count=15;--条件退出
  v_count := v_count+2;
  end loop;
  DBMS_OUTPUT.PUT_LINE(v_total);
  end;
--输出结果：680

--1*2*3*4*5
declare 
v_result number(5):=1;
v_count number(5):=1;
begin
  loop
    v_result := v_result * v_count;
    exit when v_count = 5;
    v_count := v_count + 1;
    end loop;
     DBMS_OUTPUT.PUT_LINE(v_result);
    end;
 --输出结果：120
  

/*
FOR LOOP 循环
  FOR循环是固定次数循环，格式如下
  FOR 控制变量in [REVERSE] 下限..上限
    LOOP
      语句1;
      语句2;
    END LOOP;
*/
declare 
begin
  for i in 1..10
    loop
      dbms_output.put_line(to_char(i)||rpad('*', i, '*'));
      end loop;
      end;

/*
输出结果为：
1*
2**
3***
4****
5*****
6******
7*******
8********
9*********
10**********
*/

/*
为以上程序加REVERSE关键字，观察执行结果。
输出一个空心三角
*/
declare 
BEGIN
  FOR I IN 1..9
    LOOP
      IF I=1 OR I=9 THEN
        DBMS_OUTPUT.PUT_LINE(to_char(I)||rpad('          ',12-I,'')||rpad('*',2*I-1,'*'));
      ELSE
        DBMS_OUTPUT.PUT_LINE(to_char(I)||rpad(' ',12-I,' ')||'*'||rpad('',I*2-3,' ')||'*');
        END IF;
        END LOOP;
        END;
        
 /*
 WHILE LOOP 循环
   WHILE循环是有条件循环，格式如下：
   WHILE 条件
     LOOP
       语句1：
       语句2：
     END LOOP;
 */    
  DECLARE 
  v_count number(2):=1;
  begin
    WHILE v_count<6 LOOP
      INSERT INTO SCOTT.EMP(EMPNO, ENAME) values(5000+v_count, '临时');
      v_count := v_count +1;
      end loop;
      end;      

/*
--执行结果
select * from scott.emp
1	5001	临时						
2	5002	临时						
3	5003	临时						
4	5004	临时						
5	5005	临时						
*/

/*
多重循环
使用二重循环求1!+2!+...+10!的值
*/
DECLARE
v_total number(8):=0;
v_ni number(8):=0;
J number(5);
begin
  for i in 1..10
    loop
      J:=1;
      v_ni:=1;
      while J<=I
        loop
        v_ni := v_ni * J;
        J := J+1;
        end loop;--内循环求n!
        v_total := v_total + v_ni;
end loop;
DBMS_OUTPUT.put_line(v_total);
end;
--执行结果：4037913

DECLARE 
v_empno number(5) := 7188;
v_num number(10):=0;
begin
  select count(*) into v_num from scott.emp emp where emp.empno = v_empno; 
  if v_num > 0 then
    DBMS_OUTPUT.put_line('已存在');
    else
      insert into scott.emp(empno, ename) values(v_empno, 'xxxx');
      COMMIT;
      DBMS_OUTPUT.put_line('插入成功');
    end if;
    end;
    
 select * from scott.emp