语法结构
PL/SQL可以控制程序执行流程，实现比较复杂的业务逻辑。块结构如下：
[DECLARE] --声明部分，可选 BEGIN --执行部分，必须 [EXCEPTION] --异常处理部分，可选 END
块中每条语句以;结束
declare a int:=100; b int:=200; c number; begin c:=(a+b)/(a-b); dbms_output.put_line(c); exception when zero_divide then dbms_output.put_line(‘除数不许为零!‘); end;
代码注释和标识符
1．单行注释
单行注释由连个连接字符“--”开始，后面紧跟着注释内容。
2．多行注释
多行注释由/*开头，由*/结尾，这个大多数编程语言是相同的。
3．PL/SQL字符集
所有的PL/SQL程序元素（比如，关键字、变量名、常量名等）都是由一些字符序列组合而成的，而这些字符序列中的字符都必须取自PL/SQL语言所允许使用的字符集，那么这些合法的字符集主要包括以下内容：
大写和小写字母：A-Z或a-z。
数字：0-9。
非显示的字符：制表符、空格和回车。
数学符号：+，-，*，/，>，<，=等。
间隔符：包括()，{}，[]，?，!，;，:，@，#，%，$，&等。
数据类型
基本数据类型
1．数值类型
数值类型主要包括NUMBER、PLS_INTEGER和BINARY_INTEGER三种基本类型
2．字符类型
字符类型主要包括VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2等。这些类型的变量用来存储字符串或字符数据。
3．日期类型
日期类型只有一个种——即DATE类型，用来存储日期和时间信息，DATE类型的存储空间是7个字节，分别使用一个字节存储世纪、年、月、天、小时、分钟和秒。
4．布尔类型
布尔类型也只有一种——即BOOLEAN，主要用于程序的流程控制和业务逻辑判断，其变量值可以是TRUE、FALSE或NULL中的一种。
特殊数据类型
1．%TYPE类型
使用%TYPE关键字可以声明一个与指定列名称相同的数据类型，它通常紧跟在指定列名的后面。
-- 使用%TYPE类型变量输出emp表中编号为7369的员工的姓名和职务信息 DECLARE v_name emp.ename%type; v_job emp.job%type; BEGIN SELECT ENAME,JOB INTO v_name,v_job FROM EMP WHERE EMPNO=7369; DBMS_OUTPUT.put_line(v_name || ‘的职务信息‘ || v_job); END;
2．RECORD类型，需要先声明，语法如下：
type record_type is record ( var_member1 data_type [not null] [:=default_value], … var_membern data_type [not null] [:=default_value])
-- 声明一个记录类型emp_type,然后使用该了日常存储emp表中的一条记录信息，并输出这条记录信息 DECLARE TYPE emp_type IS RECORD( v_empno EMP.EMPNO%TYPE, v_ename EMP.ENAME%TYPE, v_job EMP.JOB%TYPE, v_sal EMP.SAL%TYPE ); emp_info emp_type; BEGIN SELECT EMPNO,ENAME,JOB,SAL INTO emp_info FROM EMP WHERE EMPNO=7369; DBMS_OUTPUT.put_line(emp_info.v_ename || ‘的员工编号:‘ || emp_info.v_empno || ‘,职务是：‘||emp_info.v_job || ‘，薪水：‘ || emp_info.v_sal); END;
3．%ROWTYPE类型，语法如下：
rowVar_name table_name%rowtype;
-- 声明一个%ROWTYPE数据类型，用于存储从数据表中加载到的一条记录信息 DECLARE emp_info EMP%ROWTYPE; BEGIN SELECT * INTO emp_info FROM EMP WHERE EMPNO = 7788; DBMS_OUTPUT.put_line(emp_info.ename||‘(‘||emp_info.empno||‘,职务：‘||emp_info.job||‘,薪水:‘||emp_info.sal||‘)‘); END;
定义变量和常量
1．定义变量
<变量名> <数据类型> [(长度):=<初始值>];
2．定义常量
<常量名> constant <数据类型>:=<常量值>;
-- 定义一个常量，用于存储圆周率PI: PI CONSTANAT NUMBER:= 3.14;
流程控制语句
选择语句/条件语句
1．if…then语句
if condtion_express then plsql_statment end if; -- condtion_express：条件表达式 -- plsql_statment：条件满足执行的plsql代码块，如果不满足就忽略该块。 -- 定义两个字符串变量，接着使用if...then语句比较两个字符串的长度，并输出结果 DECLARE v_name1 varchar2(20); v_name2 varchar2(20); BEGIN v_name1:= ‘PEPPA‘; SELECT ENAME INTO v_name2 FROM EMP WHERE EMPNO=7839; IF LENGTH(v_name1) > LENGTH(v_name2) THEN dbms_output.put_line(‘字符串‘ || v_name1 || ‘的长度大于字符串‘ || v_name2 ||‘的长度‘); END IF; END;
2．If…then…else语句
IF condtion_express THEN plsql_statment1; -- 条件为true时，要执行的pl/sql语句 ELSE plsql_statment2; -- 条件不满足（false）,要执行的pl/sql语句 END IF; -- 实现判断年龄大于等于55岁，才可以申请退休，否则不能申请退休 DECLARE v_age int:=25; BEGIN IF v_age >= 55 THEN dbms_output.put_line(‘你为祖国做出了贡献，可以申请退休‘); ELSE dbms_output.put_line(‘你好，打工人‘); END IF; END;
3．if…then…elsif语句
IF condtion_express1 THEN plsql_statement1; ELSIF condtion_express2 THEN plsql_statement2; ..... [ELSE plsql_statement_DEFAULT;] -- 所有的分支条件都不满足，可以指定一个默认执行语句 END IF; -- 得到指定员工（7369)的工资信息，打印出对应的工资的等级 DECLARE v_sal emp.sal%type; BEGIN SELECT SAL INTO v_sal FROM EMP WHERE EMPNO = 7369; IF v_sal >= 100 AND v_sal < 2000 THEN dbms_output.put_line(‘一等级‘); ELSIF v_sal >= 2000 AND v_sal < 3000 THEN dbms_output.put_line(‘二等级‘); ELSIF v_sal >= 3000 AND v_sal < 4000 THEN dbms_output.put_line(‘三等级‘); ELSE dbms_output.put_line(‘四等级‘); END IF; END;
4．case语句
case selector when expression_1 then plsql_statement1； when expression_2 then plsql_statement2； ...... [else default_statement;] end case;
小练习：根据年份计算生肖；打印输出指定月份的季节
循环语句
1．loop语句
会先执行依次循环体，然后再判断exit then关键字后面的条件表达式的值，如果为true，退出循环体，否则将再次执行循环体，循环体中的内容至少执行一次
loop plsql_sentence; -- 循环体中的pl/sql语句，可能是一条，5可能是多条，至少执行一次 exit when end_condition_exp -- 结束循环条件 end loop;
-- 使用loop循环计算前100个自然数的和，并打印输出
DECLARE
v_sum int:=0;
v_i int:=0;
BEGIN
LOOP
v_i := v_i + 1;
v_sum := v_sum + v_i;
EXIT WHEN v_i = 100;
END LOOP;
dbms_output.put_line(‘前100个自然数之和为:‘ || v_sum);
END;
2．while语句
WHILE循环根据循环条件执行0次或者多次循环体，再每次执行循环体之前，首先判断条件表达式的值是否满足，若果是true，则执行循环体，否则退出while
语法：
WHILE condtion_expression LOOP plsql_statement; END LOOP; -- 使用WHILE循环计算前100个自然数的和，并打印输出 DECLARE v_num int := 0; v_sum int := 0; BEGIN WHILE v_num < 100 LOOP v_num := v_num + 1; v_sum := v_sum + v_num; END LOOP; dbms_output.put_line(‘前100个自然数之和为:‘ || v_sum); END;
3．for语句
FOR语句可预置循环次数的循环控制语句，有一个循环计算器（通常是一个变量，通常是一个整型变量，计算器可以从小到大，也可以从大到小，次数控制由上限值和下限值决定）
语法:
FOR variable_counter IN [REVERSE] lower_limit..upper_limit LOOP plsql_statement; END LOOP;
-- variable_counter:计数器变量，通常是一个整型变量，默认情况下该计数器的值会循环递增，当使用了REVERSE关键字，该计数器循环递减
-- lower_limit：计数器的下限值
-- upper_limit：计数器的上限值
-- 使用for循环计算前100个自然数的和，并打印输出 DECLARE v_sum INT := 0; BEGIN FOR v_num IN 1..100 LOOP v_sum := v_sum + v_num; END LOOP; dbms_output.put_line(‘前100个自然数之和为:‘ || v_sum); END; -- 使用for循环计算前100个自然数偶数的和，并打印输出 DECLARE v_sum INT := 0; BEGIN FOR v_num IN REVERSE 1..100 LOOP IF MOD(v_num,2) = 0 THEN v_sum := v_sum + v_num; END IF; END LOOP; dbms_output.put_line(‘前100个自然数偶数之和为:‘ || v_sum); END;
PL/SQL游标
提供了一种从表中检索数据并进行操作的灵活手段，游标主要用于服务器上，处理客户端发送给服务器的SQL语句，或是批处理、存储过程、触发器中的数据处理。
游标的作用相当于一个指针，通过PL/SQL游标，程序可以一次处理查询结果集，并可以对该行数据执行特定操作
显式游标
显式游标是由用户声明和操作的一种游标，通常用于操作查询结果集（即由SELECT语句返回的查询结果），使用它处理数据的步骤包括：声明游标、打开游标、读取游标和关闭游标4个步骤。
1、声明游标
主要包括游标名称和为游标提供结果集的select语句，语法：
CURSOR cursor_name[(input_parameter1[,....])] [RETURN ret_type] IS select_statement;
2、打开游标
声明游标后，必须要打开游标才能使用，语法：
OPEN cursor_name[(parameter_value1[,....])]
3、读取游标
打开一个游标后，就可以读取游标中的数据了，读取游标就是逐行将结果集中的数据保存到变量中。语法：
FETCH cursor_name INTO {variable}
4、关闭游标
游标使用完毕后需要关闭，以释放系统资源
CLOSE cursor_name
-- 声明一个游标，用来读取emp表中job为SALESMAN的员工信息 DECLARE -- 声明一个游标，并定义一个输入（IN）参数v_job，类型为varchar2，但不可以指定长度 CURSOR cur_emp(v_job IN varchar2 := ‘SALESMAN‘) IS SELECT ENAME,JOB,SAL,HIREDATE FROM EMP WHERE JOB = v_job; TYPE record_emp IS RECORD( v_name emp.ename%type, v_job emp.job%type, v_sal emp.sal%type, v_hiredate emp.hiredate%type ); row_emp record_emp; BEGIN OPEN cur_emp(‘CLERK‘); -- 打开游标，该游标可以接收一个输入参数，代表工作类型 --读取游标 FETCH cur_emp INTO row_emp; -- 先让指针指向结果集中的第一行，并将值保存到row_emp变量中 WHILE cur_emp%found LOOP dbms_output.put_line(‘(‘|| row_emp.v_name || ‘:‘ || row_emp.v_job|| ‘,‘ || row_emp.v_sal||‘,‘||row_emp.v_hiredate ||‘)‘); FETCH cur_emp INTO row_emp; -- 让指针指向下一条记录，，并将值保存到row_emp变量中 END LOOP; -- 关闭游标 CLOSE cur_emp; END;
隐式游标
在执行一个SQL语句时，Oracle会自动创建一个隐式游标。隐式游标主要时处理数据操纵语句（UPDATE，DELETE）的执行结果，由于隐式游标也具有属性，所以在使用隐式游标时，需要在属性前面加上隐式游标的默认名称——SQL
在实际的PL/SQL编程中，经常使用隐式游标判断更新数据会或删除数据行的情况
-- 把emp表中工作为SALESMAN的工资上调20%,然后统计出有多少员工工资受了影响 BEGIN UPDATE EMP SET SAL = SAL * 1.2 WHERE JOB = ‘SALESMAN‘; IF SQL%NOTFOUND THEN dbms_output.put_line(‘没有雇员需要上调工资‘); ELSE dbms_output.put_line(‘有‘|| SQL%ROWCOUNT ||‘雇员需要上调工资‘); END IF; END;
游标的属性
无论是显式游标还是隐式游标，都具有%found、%notfound、%isopen和%rowcount四个属性，通过这4个属性可以获知SQL语句的执行结果以及该游标的状态信息。
（1）%found：布尔型属性，如果SQL语句至少影响到一行数据，则该属性为true，否则为fasle。
（2）%notfound：布尔型属性，与%found属性的功能相反。
（3）%rowcount：数字型属性，返回受SQL语句影响的行数。
（4）%isopen：布尔型属性，当游标已经打开时返回true，游标关闭时则为false。
-- 声明一个游标，用于查询指定员工编号的雇员信息，然后使用游标的%found属性来判断是否查询到指定员工编号的员工信息 DECLARE CURSOR cur_emp IS SELECT ENAME,JOB FROM EMP WHERE EMPNO=7369; v_name varchar2(50); v_job varchar2(20); BEGIN OPEN cur_emp; FETCH cur_emp INTO v_name,v_job; IF cur_emp%found THEN dbms_output.put_line(‘编号为10010的员工姓名为:‘ || v_name ||‘,职位是：‘||v_job); ELSE dbms_output.put_line(‘编号为10010的员工不存在‘); END IF; CLOSE cur_emp; END;
通过for语句循环游标
1、在for语句中遍历隐式游标中的数据时，通常在关键字“in”的后面提供由SELECT语句检索的结果集，在检索结果集的过程中，Oracle系统会自动提供一个隐式的游标sql。
-- 通过for语句循环游标，检索职位为CLERK的雇员信息（计数器可以自动作为record类型的变量） BEGIN FOR record_emp IN (SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB=‘CLERK‘) LOOP-- 遍历隐式游标中的记录 dbms_output.put_line(‘EMPNO:‘ || record_emp.empno); dbms_output.put_line(‘ENAME:‘ || record_emp.ENAME); dbms_output.put_line(‘JOB:‘ || record_emp.JOB); dbms_output.put_line(‘SAL:‘ || record_emp.SAL); END LOOP; END;
2、在for语句中遍历显式游标中的数据时，通常在关键字“in”的后面提供游标的名称，其语法格式如下：
for var_auto_record in cur_name loop plsqlsentence; end loop; -- 通过for遍历显式游标，通常在关键字IN后提供显示游标的名称，查询出10号部门的员工信息 DECLARE CURSOR cur_emp IS SELECT * FROM EMP WHERE DEPTNO = 10; BEGIN FOR record_emp IN cur_emp LOOP dbms_output.put(‘EMPNO:‘ || record_emp.empno); dbms_output.put(‘,ENAME:‘ || record_emp.ENAME); dbms_output.put(‘,JOB:‘ || record_emp.JOB); dbms_output.put_line(‘,SAL:‘ || record_emp.SAL); END LOOP; END;
PL/SQL异常处理
1、系统预定义异常
当PL/SQL程序违反了Oracle系统内部规定的设计规范时，就会自动引发一个预定义的异常，例如，当除数为零时，就会引发“ZERO_DIVIED”异常。
| 系统预定义异常 | 说明 |
| ZERO_DIVIED | 除数为0 |
| ACCESS_INTO_NULL | 企图为某个未初始化对象的属性赋值 |
| COLLECTION_IS_NULL |
企图使用未初始化的集合元素 |
| CURSOR_ALREADY_OPEN | 企图再次打开一个已经打开过的游标，但在重新打开之前，游标未关闭 |
| INVALID_CURSOR |
执行一个非法的游标操作，例如，关闭一个未打开的游标 |
| INVALID_NUMBER |
企图将一个字符串转换成一个无效的数字而失败 |
| LOGIN_DENIED |
企图使用无效的用户名或密码连接数据库 |
| NO_DATA_FOUND |
SELECT INTO语句没有返回数据 |
| ROWTYPE_MISMATCH |
主游标变量与PL/SQL游标变量的返回类型不兼容 |
| SELF_IS_NULL | 使用对象类型时，使用空对象调用其方法 |
declare v_no number; v_name varchar2(20); v_job emp.job%type; begin v_no:=1000; select ename,job into v_name,v_job from emp where empno=v_no; if sql%found then dbms_output.put_line(‘(‘ || v_no || ‘,‘ || v_name || ‘,‘ || v_job || ‘)‘); end if; exception when NO_DATA_FOUND then dbms_output.put_line(v_no || ‘不存在‘); when too_many_rows then dbms_output.put_line(‘返回记录超过一行‘); end;
2、自定义异常
2.1、错误编号异常
错误编号异常是指在Oracle系统发生错误时，系统会显示错误编号和相关描述信息的异常，虽然直接使用错误编号也可以完成异常处理，但错误编号较为抽象，不易于用户理解和记忆，对于这种异常，首先在PL/SQL块的声明部分（DECLARE部分）使用EXCEPTION类型定义一个异常变量名，然后使用语句PRAGMA EXCEPTION_INIT为“错误编号”关联“这个异常变量名”，接下来就可以像对待系统预定义异常一样处理了。
declare primary_iterant exception;--定义一个异常变量 pragma exception_init(primary_iterant,-00001);--关联错误号和异常变量名 begin insert into dept values(10,‘软件开发部‘,‘深圳‘);--向dept表中插入一条与已有主键值重复的记录，以便引发异常 exception when primary_iterant then --若oracle捕获到的异常为-00001异常 dbms_output.put_line(‘主键不允许重复！‘);--输出异常描述信息 end; /
2.2、业务逻辑异常
在实际的应用中，程序开发人员可以根据具体的业务规则自定义一个异常。这样，当用户操作违反业务逻辑规则时，就引发一个自定义异常，从而中断程序的正常执行并转到自定义的异常处理部分。但无论是预定义异常，还是错误编号异常，都是由Oracle系统判断的错误，但业务逻辑异常是Oracle系统本身是无法知道的，这样就需要有一个引发异常的机制，引发业务逻辑异常通常使用RAISE语句来实现。当引发一个异常时，控制就会转到EXCEPTION异常处理部分执行异常处理语句。业务逻辑异常首先在DECLARE部分使用EXCEPTION类型声明一个异常变量，然后在BEGIN部分根据一定的义务逻辑规则执行RAISE语句（在RAISE关键字后面跟着异常变量名），最后在EXCEPTION部分编写异常处理语句。
declare null_exception exception;--声明一个exception类型的异常变量 dept_row dept%rowtype;--声明rowtype类型的变量dept_row,与dept表中一行的数据类型相同 begin dept_row.deptno := 66;--给部门编号变量赋值 dept_row.dname := ‘公关部‘;--给部门名称变量赋值 insert into dept values(dept_row.deptno,dept_row.dname,dept_row.loc);--向dept表中插入一条记录 if dept_row.loc is null then--如果判断“loc”变量的值为null raise null_exception;--引发null异常，程序转入exception部分 end if; exception when null_exception then--当raise引发的异常是null_exception时 dbms_output.put_line(‘loc字段的值不许为null‘);--输出异常提示信息 rollback;--回滚插入的数据记录 end;