10 PL/SQL控制语句和存储过程

1.背景

与SQL Server采用T-SQL语言类似，Oracle也拥有自己的控制语言PL/SQL。

2.应用场景及代码实现

（1）比较两个值的大小并输出(if else endif）,条件嵌套循环（多重if else endif）(case when then),

--比较值并输出
DECLARE
NUMBER1 INTEGER:=9;
NUMBER2 INTEGER:=10;
BEGIN
IF NUMBER1<NUMBER2 THEN
DBMS_OUTPUT.PUT_LINE('NUMBER1小于NUMBER2');
ELSE
DBMS_OUTPUT.PUT_LINE('NUMBER1:'||NUMBER1||',NUMBER1大于等于NUMBER2');
END IF;
END;
--条件嵌套循环（if then else endif）
DECLARE
SCORE INTEGER:=50;
BEGIN
IF SCORE>=80 THEN                
... THEN ...;
ELSE ...; END IF; ELSE                             
IF ... THEN ...;ELSE IF ... THEN ...; ELSE ... END IF;END IF;
END IF;END;
--条件嵌套循环（case when then）
DECLARE ...;
BEGIN
CASE WHEN(...) THEN ...;
WHEN(...) THEN ...;
ELSE ...; END CASE;
END;

第一条语句在PL/SQL中执行结果如下图：

（2）循环结构(LOOP,类似于其他语言的DO WHILE结构，先执行再判断) 四种循环结构详解

--loop 循环体 exit...end loop
LOOP ...
IF ... THEN ... EXIT;
ELSE ... ENDIF
END LOOP
--loop 循环体 exit...when...end loop
--while ... loop ... end loop
--for... in loop ... end loop

 

A：示例，计算100以内所有奇数的和 见下图，三种方法以及输出结果（在Toad中编写执行，plsql一样）

（3）存储过程是一个能执行某个特定操作的子程序，按照是否含参数分为不带参数和带参数存储过程两类，一般包含三部分：声明部分，执行部分，异常处理部分。

A:不带参数存储过程

--创建存储过程：得到表STU的数据行数，并输出
CREATE OR REPLACE PROCEDURE TEST AS
TNUMBER INT;
BEGIN
SELECT COUNT(*) INTO TNUMBER FROM STU;
DBMS_OUTPUT.PUT_LINE('该表共有数据'||TNUMBER||'行');
END TEST;
--查询上述存储过程内容
SELECT TEXT FROM USER_SOURCE WHERE TYPE='PROCEDURE' AND NAME='TEST';
--调用 （SET SERVEROUTPUT ON）(两种方式)
EXEC TEST;
BEGIN PRO_NAME; END;

B：带参数存储过程(1.只有输入参数，2.有输入和输出参数，3.带默认值)

--创建带参数的存储过程 （给出学号，得到该学生班级信息）
CREATE OR REPLACE PROCEDURE GETCLASS(SNUM NUMBER)
AS
SCLASS STU.SDEPT%TYPE;                 --定义记录数据类型
BEGIN
SELECT SDEPT INTO SCLASS FROM STU WHERE SNO=SNUM;
DBMS_OUTPUT.PUT_LINE(SNUM||'的学生所在班级为 '||SCLASS);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到该学号');
END;
--调用带参数存储过程GETCLASS (三种方法)
EXEC GETCLASS(001);
CALL GETCLASS(001);
BEGIN GETCLASS(001); END;
--带输出参数的存储过程
CREATE OR REPLACE PROCEDURE TESTPRO01301(CODE IN NUMBER,OPE OUT NUMBER)
AS
BEGIN
SELECT OPERATE INTO OPE FROM QMSCOMMONIMAP WHERE MAPCODE=CODE;
EXCEPTION
WHEN NO_DATA_FOUND THEN                     --未找到数据
DBMS_OUTPUT.PUT_LINE('没有找到该code');
END;
--调用存储过程TESTPRO01301
DECLARE OPE NUMBER;
BEGIN
TESTPRO01301(1，OPE);
DBMS_OUTPUT.PUT_LINE(OPE);
END;
--带默认值的存储过程
CREATE OR REPLACE PROCEDURE TEST02
(
SNO VARCHAR2,
SAGE NUMBER DEFAULT 0,
SBIRTH DATE DEFAULT SYSDATE
)
AS BEGIN
INSERT INTO STU VALUES(SNO,SAGE,SBIRTH);
END;
--调用TEST02该示例(可缺省参数)
EXEC TEST02('001');
EXEC TEST02('001'，18);

REMARK:如果带参数存储过程的参数是变量时，必须使用上面第三种方法调用。并使用DECLARE声明变量类型。

C：对存储过程的其他操作（删除，查看，重新编译，调试存储过程）

--查看存储过程详细信息
SELECT TEXT FROM USER_SOURCE WHERE TYPE='PROCEDURE' AND NAME='TEST';
--查看存储过程状态
SELECT OBJECT_NAME,STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE='PROCEDURE';
--查询数据字典获取存储过程错误信息
SHOW ERROR; --展示当前编译错误等错误信息
SELECT NAME,LINE,TEXT FROM USER_ERRORS WHERE NAME='TEST' ORDER BY LINE;
--重新编译存储过程(注意权限)
ALTER PROCEDURE TEST COMPILE;
--删除存储过程(注意权限)
DROP PROCEDURE TEST;

（4）动态sql执行

--动态创建DDL语句（创建表）
CREATE OR REPLACE PROCEDURE PRO_TEST(
TABLE_NAME IN VARCHAR2,
FIELD1 IN VARCHAR2,
DATATYPE1 IN VARCHAR2,
FIELD2 IN VARCHAR2,
DATATYPE2 IN VARCHAR2
) AS
STR_SQL VARCHAR2(500);
BEGIN
STR_SQL:='CREATE TABLE '||TABLE_NAME||'('||FIELD1||' '||DATATYPE1||', '||FIELD2||' '||DATATYPE2||')';
EXECUTE IMMEDIATE STR_SQL;
EXCEPTION WHEN OTHERS THEN NULL;
END;
--执行该存储过程，创建表TESTSQL
EXEC PRO_TEST('TESTSQL','ID','NUMBER(8) NOT NULL','NAME','VARCHAR2(100)');
--动态创建DML语句（向TESTSQL表插入数据）
CREATE OR REPLACE PROCEDURE PROC_INSERT(
ID IN NUMBER,                           --序号
NAME IN VARCHAR2                        --姓名
) AS
STR_SQL VARCHAR2(500);
BEGIN
STR_SQL:='INSERT INTO TESTSQL VALUES(:1,:2)';
EXECUTE IMMEDIATE STR_SQL USING ID,NAME;--动态执行插入操作
EXCEPTION WHEN OTHERS THEN NULL;
END ;
--执行该DML存储过程，向TESTSQL插入数据
EXECUTE PROC_INSERT(1，'LIMING');

REMARK:EXECUTE IMMEDIATE  动态语句字符串 [info变量列表] [using参数列表]  后面两个参数一般在动态执行DML语句出现