ORACLE PL、SQL编程

PL（Procedural Language）过程化的编程语言，是在SQL的基础上增加的部分，如：变量的使用、流程控制等，

重点学习Oracle和MySQL创建存储过程及流程控制的异同。

一、存储过程的创建、调用：

语法：
CREATE OR REPLACE PROCEDURE <存储过程名> [(参数名称 参数的传递方向 数据类型)]
{AS|IS}
BEGIN
  　DECLARE <变量名> 数据类型 := 初始值;
　　　　变量名 := 值;
　　BEGIN
　　　　--存储过程体。
　　END;
END;

说明：
 1、若没有参数就不能加小括号，MySQL都需要加
 2、参数的传递方向放再第二个位置，MySQL放在第一个位置。
3、存储过程定义部分结束后添加AS或IS，MySQL不需要。
4、当使用DECLARE定义完变量后需要再添加BEGIN END 包裹存储过程体的内容
5、调用存储过程使用“EXECUTE 存储过程名称();”或者将存储过程包裹在BEGIN END之间执行。


-- 实现加法运算的存储过程
CREATE OR REPLACE PROCEDURE proc_add(num1 NUMBER,num2 NUMBER)
AS 
BEGIN
  DECLARE 
  resault1 NUMBER(10,2) := 0;
  BEGIN
    resault1 := num1 + num2;
    dbms_output.put_line('结果为：' || resault1);
  END;
END; 


-- 若使用了dbms_output.put_line()输出，需要先打开显示开关才能查看结果
SET SERVEROUTPUT ON;
-- 调用
EXECUTE proc_add(10.5,20);
/*或者下面的方式*/
BEGIN
  proc_add(10.5,20);
END;

2.  流程控制之IF语句：

IF <条件表达式1> THEN
  #语句块1
[ELSIF <条件表达式2>THEN
  #语句块2
  END IF;
。。。。。。。]
END IF；

案例：
-- 案例描述：用户输入“课程编号”，查询对应编号的课程名称
CREATE OR REPLACE PROCEDURE proc_selectsubjec(id NUMBER)
AS 
BEGIN
  DECLARE
    subjectName VARCHAR2(20);
  BEGIN
      IF id = 1 THEN
        SELECT subject_name INTO subjectName FROM tb_subject WHERE subject_id = 1;
         dbms_output.put_line('结果为：' || subjectName);
      ELSIF id = 2 THEN
        SELECT subject_name INTO subjectName FROM tb_subject WHERE subject_id = 2;
        dbms_output.put_line('结果为：' || subjectName);
      ELSIF id = 3 THEN
        SELECT subject_name INTO subjectName FROM tb_subject WHERE subject_id = 3;
        dbms_output.put_line('结果为：' || subjectName);
      ELSE
         dbms_output.put_line( '您的输入有误！');
      END IF;
  END;
END;

-- 调用
EXECUTE proc_selectsubjec(3);

3.  流程控制之CASE语句： 

CASE <常量>
WHEN 值1 THEN
   #语句块1
WHEN 值2 THEN
  #语句块3
[ELSE #语句块N]
END CASE;

-- 输入编号，查看课程名称
CREATE OR REPLACE PROCEDURE proc_selectsubjec_case(id NUMBER)
AS 
BEGIN
  DECLARE
    subjectName VARCHAR2(20);
  BEGIN
      CASE(id)
        WHEN 1 THEN
          SELECT subject_name INTO subjectName FROM tb_subject WHERE subject_id = 1;
           dbms_output.put_line('结果为：' || subjectName);
        WHEN  2 THEN
          SELECT subject_name INTO subjectName FROM tb_subject WHERE subject_id = 2;
          dbms_output.put_line('结果为：' || subjectName);
        WHEN 3 THEN
          SELECT subject_name INTO subjectName FROM tb_subject WHERE subject_id = 3;
          dbms_output.put_line('结果为：' || subjectName);
        ELSE
           dbms_output.put_line( '您的输入有误！');
        END CASE;
  END;
END;

-- 调用
EXECUTE proc_selectsubjec(3);

4.  流程控制之LOOP语句结合IF语句：

语法：
LOOP
 IF<条件表达式1> THEN
   EXIT;
 END IF;
  编写循环体中的代码块，需要手动更新条件。
END LOOP

举例： 
-- 循环输出指定次数的提示信息。
CREATE OR REPLACE PROCEDURE proc_loopif(maxNums IN  NUMBER)
AS
BEGIN
  DECLARE 
    nums NUMBER(20) := 1;
  BEGIN
    LOOP 
      IF nums > maxNums THEN
         EXIT;
      END IF;
       dbms_output.put_line( '第'||nums||'次输出');
       nums := nums + 1;
     END LOOP;
  END;
END;

-- 调用
EXECUTE proc_loopif(10);

 5. 流程控制之LOOP语句结合WHEN语句：

语法：
LOOP
  EXIT WHEN <条件表达式>;
   编写循环体中的代码块，需要手动更新条件。
END LOOP;
举例： 
-- 循环输出指定次数的提示信息。
CREATE OR REPLACE PROCEDURE proc_loopwhen(maxNums IN  NUMBER)
AS
BEGIN
  DECLARE 
    nums NUMBER(20) := 1;
  BEGIN
   LOOP 
      EXIT WHEN nums> maxNums;
       dbms_output.put_line( '第'||nums||'次输出');
       nums := nums + 1;
     END LOOP;
  END;
END;

-- 调用
EXECUTE proc_loopwhen(10);

6.  流程控制之LOOP语句结合WHILE语句： 

语法：
WHILE <条件表达式>
LOOP
   编写循环体中的代码块，需要手动更新条件。
END LOOP;
举例： 
-- 循环输出指定次数的提示信息。
CREATE OR REPLACE PROCEDURE proc_loopwhile(maxNums IN  NUMBER)
AS
BEGIN
  DECLARE 
    nums NUMBER(20) := 1;
  BEGIN
   WHILE nums <= maxNums
   LOOP 
     dbms_output.put_line( '第'||nums||'次输出');
       nums := nums + 1;
     END LOOP;
  END;
END;

-- 调用
EXECUTE proc_loopwhile(10);

7.  流程控制之LOOP语句结合FOR语句：

/*
语法：
FOR 循环变量 IN 循环起始值..循环的终止值
LOOP
   编写循环体中的代码块，需要手动更新条件。
END LOOP;
*/
-- 循环输出指定次数的提示信息。
CREATE OR REPLACE PROCEDURE proc_loopfor(maxNums IN  NUMBER)
AS
BEGIN
   FOR nums IN 1..maxNums
   LOOP 
     dbms_output.put_line( '第'||nums||'次输出');
   END LOOP;
END;

-- 调用
EXECUTE proc_loopfor(10);

二、   批量插入记录：字段值采用随机生成

以tb_student为例，完成批量插入记录

CREATE OR REPLACE PROCEDURE proc_batchinsertstudent(recordNums IN NUMBER)
AS
BEGIN
　　DECLARE 
　　　　nos NUMBER(10) := 11;
　　　　mobile CHAR(11);
　　BEGIN
　　　　FOR nums IN 1..recordNums
　　　　LOOP 
　　　　　　mobile := '1' || substr(dbms_random.value(),3,10);
　　　　　　INSERT INTO TB_STUDENT VALUES(
　　　　　　　　's' || nos, /*STUDENT_NO*/
　　　　　　　　dbms_random.string('A',6), /*LOGIN_PWD*/
　　　　　　　　sys_guid(), /*STUDENT_NAME*/
　　　　　　　　substr('男女',CEIL(dbms_random.value(0,2)),1), /*SEX*/
　　　　　　　　CEIL(dbms_random.value(0,5)), /*GRADE_ID*/
　　　　　　　　mobile , /*PHONE*/
　　　　　　　　substr('批量插入记录字段值这里面的值采用随机截取',CEIL(dbms_random.value(1,84)),ROUND(dbms_random.value(5,10))), /*ADDRESS*/ 
　　　　　　　　to_date(to_char(to_date('1990-01-01','yyyy-mm-dd'),'J') + TRUNC(dbms_random.value(0,366)),'J'), /*日期*/
　　　　　　　　mobile || '@qq.com' /*电子邮箱*/
　　　　　　);
　　　　　　nos := nos + 1;
　　　　END LOOP;
　　END;
END;


-- 调用
EXECUTE proc_batchinsertstudent(1000);

select * from tb_student ORDER BY STUDENT_NO;

DELETE FROM tb_student where STUDENT_NO > 's10';