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';