Oracle学习笔记 4 PL/SQL

PL/SQL 块是构成 PL/SQL 程序的基本单元
将逻辑上相关的声明和语句组合在一起
PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分
[DECLARE
declarations]
BEGIN
executable statements
[EXCEPTION
handlers]
END;
以下示例演示了如何使用PL/SQL 语句：
declare
area integer;
width integer;
height integer;
currentdate date;
cnumber constant integer := 10;
begin
area :=10;
height :=2;
width := area/height;
currentdate :=sysdate;
DBMS_OUTPUT.put_line('宽 = '||width);
DBMS_OUTPUT.put_line('高 ='||height);
DBMS_OUTPUT.put_line('面积 ='||area);
DBMS_OUTPUT.put_line('常量的值为：'||cnumber);
DBMS_OUTPUT.put_line('当前时间为：'||currentdate);
exception
when zero_divide then
DBMS_OUTPUT.put_line('除数不能为零');
end;
给变量赋值有两种形式：
使用赋值语句 :=
使用 SELECT INTO 语句
使用常量赋值：
cnumber constant integer := 10;


以下代码演示了使用常量和SELECT INTO 语句：

declare
firstName varchar2(20);
lastName varchar2(20);
employeeid number;
consNumber constant integer :=1000;
begin
select employee_id, first_name,last_name into employeeid, firstName,lastname from employees2 where employee_id =1;
DBMS_OUTPUT.put_line('consNumber = '||consNumber);
DBMS_OUTPUT.put_line('employeeid='||firstName);
DBMS_OUTPUT.put_line('firstName='||firstName);
DBMS_OUTPUT.put_line('lastName='||lastname);
end;
CLOB数据类型的使用。
create table my_book_text(
chapter_id number(3),
chapter_descr varchar2(40),
chapter_text clob);
添加数据到clob字段 chapter_text ：
insert into my_book_text values(5,'第五章 PL/SQL 简介','PL/SQL 可用于创建存储过程，触发器和程序包等，用来处理业务规则，数据库时间或给SQL命令的执行添加程序逻辑。');
读取 CLOB 数据类型：
set serveroutput on
declare
clob_var clob;
amount integer;
offset integer;
output_var varchar2(100);
begin
select chapter_content into clob_var from my_book_text where chapterid=1;
amount :=20;
offset :=5;
DBMS_LOB.READ(clob_var,amount,offset,output_var);
DBMS_OUTPUT.put_line(output_var);
end;
/


PL/SQL 支持的流程控制结构：
条件控制
IF 语句
CASE 语句
循环控制
LOOP 循环
WHILE 循环
FOR 循环
顺序控制
GOTO 语句
NULL 语句
以下代码演示了条件控制（IF-THEN-ELSE语句）：
declare
age number(8);
begin
age := &age;
if age>20 and age<30 then
dbms_output.put_line('年龄在20 和30 之间 ');
elsif age < 20 then
dbms_output.put_line('年龄小于20');
else
dbms_output.put_line('年龄大于30');
end if;
end;
/
以下代码演示了从 employees2 表中检索employee_id 为 3 的记录 ,如果 salary 大于 15000 则减去 1000 ,否则salary 加 100
declare
firstName varchar(20);
lastName varchar2(20);
salarytemp number(10);
begin
select first_name,last_name,salary into firstName,lastName,salarytemp from employees2 where employee_id=3;
if salarytemp > 15000 then
update employees2 set salary = salary-1000 where employee_id = 3;
else
update employees2 set salary = salary+100 where employee_id=3;
end if;
dbms_output.put_line('firstName ='||firstName);
dbms_output.put_line('lastName='||lastName);
dbms_output.put_line('salarytemp = '||salarytemp);
end;


Case 语句：
以下代码演示了选择器。系统先计算选择器值。然后再依次选择 WHEN 子句。
set serveroutput on
begin
case '&grade'
when 'A' then dbms_output.put_line('优异');
when 'B' then dbms_output.put_line('优秀');
when 'C' then dbms_output.put_line('良好');
when 'D' then dbms_output.put_line('一般');
when 'E' then dbms_output.put_line('较差');
else dbms_output.put_line('没有此成绩');
end case;
end;
/
Loop 循环：以下代码演示了loop的使用
declare
x number;
begin
x :=0;
loop
x:=x+1;
if x>=3 then
exit;
end if;
dbms_output.put_line('循环体 x ='||x);
end loop;
dbms_output.put_line('循环体外 x ='||x);
end;
/
另外一种表现形式:
declare
x number;
begin
x :=0;
loop
x:=x+1;
exit when x>=3;
dbms_output.put_line('循环体内 x ='||x);
end loop;
dbms_output.put_line('循环体外 x ='||x);
end;
While循环：
declare
x number ;
begin
x:=0;
while x<=3 loop
x:=x+1;
dbms_output.put_line('循环内'||x);
end loop;
dbms_output.put_line('循环外'||x);
end;
/

以下代码演示了while 循环得使用。声明了销量的 monthly_value 和 daily_value,并将其初始化为0。While执行循环，直至每月销量的值大于等于4000
set serveroutput on
declare
monthly_value number :=0;
daily_value number :=0;
begin
while monthly_value <= 4000
loop
monthly_value := daily_value * 31;
daily_value := daily_value +10;
dbms_output.put_line('每日销量:' || daily_value);
end loop;
dbms_output.put_line('每月销量' || monthly_value);
end;
/
For循环语句：
begin
for i in 1..5 loop
dbms_output.put_line('循环 I 的值 = '||i);
end loop;
dbms_output.put_line('end loop');
end;
/
Reverse(递减) 的使用
begin
for i in reverse 1..5 loop
dbms_output.put_line('循环 I 的值 = '||i);
end loop;
dbms_output.put_line('end loop');
end;
/
以下代码显示了25个偶数
set serveroutput on
begin
for eve_number in 1..25
loop
dbms_output.put_line(eve_number*2);
end loop;
end;
/
Oracle 中异常的处理：
预定义异常:
返回多行异常：
declare
firstname varchar2(20);
begin
select first_name into firstname from employees2 where division_id ='SAL';
dbms_output.put_line('first_name=' || firstname);
exception
when too_many_rows then
dbms_output.put_line('不能返回多行数据');
end;
/
用户自定义异常：
以下代码演示了用户接受输入的类别。IF 语句将用户输入的类别与指定的类别相匹配。如果指定的类别中不存在将引发typeException 异常
declare
typeException exception;
temptype varchar2(20);
begin
temptype :='&type';
if temptype not in ('java','c++','c#') then
raise typeException;
else
dbms_output.put_line('temptype = '||temptype);
end if;
exception
when typeException then
--dbms_output.put_line('没有找到相应的类型');
raise_application_error(-20000,'没有找到相应的类型');
end;
存储过程的使用：
过程是执行某些操作的子程序，它是执行特定任务的模块，它可以被赋予参数，存储在数据库中。以下代码
1. 创建存储过程语法：
CREATE [OR REPLACE] PROCEDURE
<procedure name> [(<parameter list>)]
IS|AS
<local variable declaration>
BEGIN
<executable statements>
[EXCEPTION
<exception handlers>]
END;
以下代码演示了如何创建一个不带参数的存储过程：
create or replace procedure pro_emp
as
firstName varchar2(20);
lastName varchar2(20);
salary number(20);
begin
select first_name,last_name,salary into firstName,lastName,salary from employees2 where employee_id = 1;
dbms_output.put_line('firstName = '||firstName);
dbms_output.put_line('lastName = ' ||lastName);
dbms_output.put_line('salary = ' ||salary);
exception
when no_data_found then
dbms_output.put_line('数据没有找到');
end;
执行以上存储过程：
execute pro_emp ；

过程参数模式：参数传递的模式有三种IN , OUT , IN OUT
IN 是参数的默认模式，这种模式定义的参数在程序运行的时候已经具有值，在过程序体中这个值不会改变。
OUT 模式定义的参数只在过程内部赋值。
IN OUT 模式定义的参数当过程运行时可能已经具有值，但是在过程体中也可以修改


以下创建了带参数的过程：
create or replace procedure mypro(employeeid in number,divisionid in out va
rchar2,jobid out varchar2)
as
tempdivid varchar2(20);
tempjobid varchar2(20);
begin
select division_id,job_id into tempdivid,tempjobid from employees2 where
employee_id =employeeid;
divisionid :=tempdivid;
jobid :=tempjobid;
end;

执行以上过程：
declare
cdivisionid varchar2(20);
cjobid varchar2(20);
cempid number(10);
begin
cempid :=1;
mypro(cempid,cdivisionid,cjobid);
dbms_output.put_line('...... cdivisionid = '||cdivisionid);
dbms_output.put_line('...... cjobid = '||cjobid);
end;
/
Oracle中的函数：
以下代码创建了函数：
create or replace function myfun(empid number)
return varchar2 is
firstName varchar2(20);
lastName varchar2(20);
begin
select first_name,last_name into firstName,lastName from employees2 where
employee_id = empid;
return 'firstName = '||firstName ||' lastName = '||lastName;
end;
/
执行以上函数：
declare
fid number(8);
info varchar2(100);
begin
fid :=1;
info :=myfunction(1);
dbms_output.put_line('info ='||info);
end;
/

数据类型说明：
BINARY_INTEGER
用户存储带符号的整数,, BINARY_INTEGER值的大小范围介于：-2的31次方减1到2的31次方减1之间，PL/SQL预定义了一下BINARY_INTEGER
子类型：
1. NATURAL: 可以限制变量存储非负整数值，即自然数。
2. NATURALN: 可以限制变量存储然数，且非空。
3. POSITIVE: 可以限制变量存储正整数。
4. POSITIVEN: 可以限制变量存储正整数，且非空。
5. SIGNTYPE: 可以限制变量存储-1,0和1 三个值。