526互联

PL/SQL编程

发布时间 2023-10-14 20:12:33作者: L0ngyc


【语法结构】

declare       --定义变量
　　字段 字段类型； 　#须用‘ ；’结束
begin
　　执行语句；
　　dbms_output.put_line('');　 #打印输出
exception when *** then 　--异常处理
　　执行语句；
end;

　　示例：计算两个整数之和

declare
　　num1 int := 120;
　　num2 int := 100;
　　ret int;
begin
　　ret := (num1+num2)/(num1-num2);
　　dbms_output.put_line('计算结果：'||ret);
exception --异常处理
when zero_divide then
　　dbms_output.put_line('除数为0');
end;

 

【数据类型】

　　1）整数型

number
int
number(6)
number(6,2) #4位整数，2位小数

　　2）字符型

char/nchar :定长
varchar2/nvarchar2 ：可变长度

char(6)   #以字节为单位
varchar2(6)
nchar(6)  #以字符为单位
nvarchar2(6)
long:最大

　　3）日期类型

date ：（固定7个字节：世纪、年、月、日、时、分、秒）

　　4）布尔类型

boolean   返回值：true / false / null

　　5）特殊数据类型

　　①%TYPE类型
　　%TYPE：可以声明一个与指定列名称相同的数据类型，它通常紧跟在指定列名的后面

　　②record类型:需要先声明

type record_type is record
(
var_member1 data_type [not null] [:=default_value],
…
var_membern data_type [not null] [:=default_value])

　　③%rowtype类型

rowVar_name table_name%rowtype;　

　　示例1：使用%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：声明一个记录类型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数据类型，用于存储从数据表中加载到的一条记录信息

DECLARE
　　emp_info EMP%ROWTYPE;
BEGIN
　　SELECT * INTO emp_info FROM EMP WHERE EMPNO = 7369;
　　DBMS_OUTPUT.put_line(emp_info.ename||'('||emp_info.empno||',职务：'||emp_info.job||',薪水:'||emp_info.sal||')');
END;

　　6）定义变量与常量

　　declare
　　　　r int; --变量
　　　　pi constant number := 3.14; --常量(不能改变)
　　begin
　　　　r := 6;
　　　　dbms_output.put_line('圆的面积为：'||pi*r*r);
　　end;

 

【流程控制语句】
　　1）选择/条件语句

　　①if...then
　　语法结构：
　　if 条件 then
　　　　plsql_statment  --条件满足，执行plsql代码块，不满足就忽略
　　end if;


　　②if...then...else
　　结构：
　　IF 条件 THEN
　　　　plsql_statment1; -- 条件为true时，要执行的pl/sql语句
　　ELSE
　　　　plsql_statment2; -- 条件不满足（false）,要执行的pl/sql语句
　　END IF;

　　③if...then...elsif
　　结构：
　　IF 条件1 THEN
　　　　plsql_statement1;
　　ELSIF 条件2 THEN
　　　　plsql_statement2;
　　.....
　　[ELSE
　　　　plsql_statement_DEFAULT;] -- 所有的分支条件都不满足，可以指定一个默认执行语句
　　END IF;　

　　示例1：得到指定员工（7369)的工资信息，打印出对应的工资的等级

DECLARE
　　v_sal emp.sal%type;
　　v_level nvarchar2(32);
BEGIN
　　SELECT SAL INTO v_sal FROM EMP WHERE EMPNO = 7369;
　　IF v_sal >= 100 AND v_sal < 2000 THEN
　　　　v_level:='一等级';
　　ELSIF v_sal >= 2000 AND v_sal < 3000 THEN
　　　　v_level:='二等级';
　　ELSIF v_sal >= 3000 AND v_sal < 4000 THEN
　　　　v_level:='三等级';
　　ELSE
　　　　v_level:='天花板极';
　　END IF;
　　dbms_output.put_line('当前工资：'||v_sal||'，对应的工资等级：'||v_level);
END;
④case
结构：
case selector
　　when expression_1 then plsql_statement1；
　　when expression_2 then plsql_statement2；
　　......
　　[else default_statement;]
end case;

　　示例2：根据年份计算生肖: 年份除以12，余数减3

declare
　　v_year int := 2023;
　　v_zodiac nvarchar2(10);
begin
　　case mod(v_year,12)-3
　　　　when 1 then
　　　　　　v_zodiac := '鼠';
　　　　when 2 then
　　　　　　v_zodiac := '牛';
　　　　when 3 then
　　　　　　v_zodiac := '虎';
　　　　when 4 then
　　　　　　v_zodiac := '兔';
　　　　when 5 then
　　　　　　v_zodiac := '龙';
　　　　when 6 then
　　　　　　v_zodiac := '蛇';
　　　　when 7 then
　　　　　　v_zodiac := '马';
　　　　when 8 then
　　　　　　v_zodiac := '羊';
　　　　when 9 then
　　　　　　v_zodiac := '猴';
　　　　when 10 then
　　　　　　v_zodiac := '鸡';
　　　　when 11 then
　　　　　　v_zodiac := '狗';
　　　　when 0 then
　　　　　　v_zodiac := '猪';
　　　　end case;
　　　　dbms_output.put_line(v_year||'年的生肖为：'||v_zodiac);
end;

　　2）循环语句
　　①loop语句：循环体中的内容至少执行一次
　　　结构：

loop
　　plsql_sentence; 　　-- 循环体中的pl/sql语句，可能是一条，5可能是多条，至少执行一次
exit when 结束条件 　　-- 结束循环条件
end loop;　

　　②while语句：根据循环条件执行0次或者多次循环体
　　　结构：

WHILE 条件 LOOP
plsql_statement;
END LOOP;

　　③for语句：可预置循环次数的循环控制语句
　　　结构：

FOR variable_counter IN [REVERSE] 下限..上限 LOOP
plsql_statement;
END LOOP;
-- variable_counter:计数器变量，通常是一个整型变量，默认情况下该计数器的值会循环递增，当使用了REVERSE关键字，该计数器循环递减

　　示例3：计算前100个自然数的和

--loop语句
declare
　　v_i int := 0;
　　v_sum 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('总和为：'||v_sum);
end;

--while语句
declare
　　v_i int := 0;
　　v_sum int :=0;
begin
　　while v_i<100 loop
　　　　v_i := v_i + 1;
　　　　v_sum := v_sum+v_i;
　　end loop;
　　dbms_output.put_line('总和为：'||v_sum);
end;

--for语句
declare
　　v_sum int :=0;
begin
　　for v_i in reverse 1..100 loop
　　　　v_sum := v_sum + v_i;
　　end loop;
　　dbms_output.put_line('总和为:' || v_sum);
end;

 

【PL/SQL游标】【*】

　1）流程
　　声明游标

　　CURSOR cursor_name [(input_parameter1[,....])] [RETURN ret_type] 
　　IS select_statement;

　　打开游标

　　OPEN cursor_name[(parameter_value1[,....])]；

　　读取游标

　　FETCH cursor_name INTO {variable}；

 

　　关闭游标

　　CLOSE cursor_name；

 

　2）游标属性
　　%found：布尔型属性，如果SQL语句至少影响到一行数据，则该属性为true，否则为fasle。
　　%notfound：布尔型属性，与%found属性的功能相反。
　　%rowcount：数字型属性，返回受SQL语句影响的行数。
　　%isopen：布尔型属性，当游标已经打开时返回true，游标关闭时则为false。

　　示例4：声明一个游标，用来读取emp表中job为SALESMAN的员工信息

DECLARE
　　CURSOR cur_emp(p_job varchar2 := 'MANAGER' )
　　IS SELECT * FROM EMP WHERE JOB=p_job;
　　V_EMP EMP%ROWTYPE;
BEGIN
　　OPEN cur_emp('CLERK');
　　FETCH cur_emp INTO V_EMP; --先让指针指向结果集中的第-一行，并将值保存到V EMP变量中
　　WHILE cur_emp%FOUND LOOP
　　　　dbms_output.put_line('ENAME:'|| V_EMP.ENAME||'，JOB:'|| V_EMP.JOB ||'，SAL:'||V_EMP.SAL);
　　　　FETCH cur_emp INTO V_EMP;
　　END LOOP;
　　close cur_emp;
END;

　3）通过for语句循环游标

--示例
begin
　　for v_dept in (select deptno,dname,loc from dept) loop
　　dbms_output.put_line('部门编号：'||v_dept.deptno||'，部门名称：'||v_dept.dname||'，地址：'||v_dept.loc);
　　end loop;
end;

 

　　--语法结构

在for语句中遍历显示游标中的数据时，通常在关键字‘in’的后面提供游标的名称
for var_auto_record in cur_name loop
    plsqlsentence;
end loop;

　　示例5：通过for遍历显式游标，通常在关键字IN后提供显示游标的名称，查询出10号部门的员工信息

DECLARE
　　CURSOR cur_emp(P_deptno in number := 20)
　　IS SELECT * FROM EMP WHERE DEPTNO = p_deptno;
BEGIN
　　--自动open和close
　　FOR record_emp IN cur_emp LOOP
　　　　dbms_output.put('员工编号:' || record_emp.empno);
　　　　dbms_output.put(',员工姓名:' || record_emp.ENAME);
　　　　dbms_output.put(',职位:' || record_emp.JOB);
　　　　dbms_output.put_line(',工资:' || record_emp.SAL);
　　END LOOP;
END;

 

【PL/SQL异常处理（exception）】

　　1）系统预定义异常

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）自定义异常
　　　　①错误编号异常
　　　　定义异常变量---关联错误号和异常变量---使用异常处理

declare
　　primary_iterant exception; --定义
　　pragma exception_init(primary_iterant,-00001); --关联
begin
　　insert into dept values(10,'peppa','bj'); --向dept表中插入一条与已有主键值重复的记录，以便引发异常
exception
　　when primary_iterant then  --若oracle捕获到的异常为-00001异常
　　dbms_output.put_line('主键不允许重复！'); --输出异常描述信息
end;

 

　　　　②业务逻辑异常　　
　　　　引发业务逻辑异常通常使用RAISE语句来实现：
　　　　　　DECLARE部分定义异常变量
　　　　　　BEGIN部分根据义务逻辑规则执行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;

　　注意：无论是预定义异常，还是错误编号异常，都是由Oracle系统判断的错误，但业务逻辑异常是Oracle系统本身是无法知道的

 

【存储过程】【*】

　　1）创建存储过程

　　　　create [or replace] procedure pro_name [(参数1,参数2,参数3…)]
　　　　　　　　　　　　　　　　　　　　--参数模式：in / out / in out；注意：in模式可以设定默认值,但是必须放参数末尾，比如：v_sum in int,v_i in int default 1
　　　　is|as
　　　　begin
　　　　　　plsql语句;
　　　　[exception]
　　　　　　[异常处理;]
　　　　end [pro_name];

　　2）调用存储过程
　　　　①命令窗口调用

　　　　exec pro_name(值1,值2...)；

　　　　②pl/sql窗口

　　　　create [or replace] procedure pro_name [(参数1,参数2,参数3…)]
　　　　　　　　　　　　　　　　　　　　--参数模式：in / out / in out；注意：in模式可以设定默认值,但是必须放参数末尾，比如：v_sum in int,v_i in int default 1
　　　　is|as
　　　　begin
　　　　　　plsql语句;
　　　　[exception]
　　　　　　[异常处理;]
　　　　end [pro_name];

 

　　3）删除存储过程

　　　　exec pro_name(值1,值2...);

　　

　　示例1：创建存储过程，添加数据到salgrade表

　　select * from salgrade order by grade;
　　create or replace procedure pro_insert_salgrade(p_grade in number,p_losal in number,p_hisal in number)
　　is
　　begin
　　　　insert into salgrade values(p_grade,p_losal,P_hisal);
　　　　dbms_output.put_line('数据添加成功！');
　　　　commit;
　　end;

　　begin
　　　　pro_insert_salgrade(6,4001,4500);
　　　　pro_insert_salgrade(p_losal=>5001,p_grade=>7,p_hisal=>6000);
　　　　pro_insert_salgrade(8,p_hisal=>7000,p_losal=>6001);
　　end;

 

　　示例2：计算指定部门的平均工资

　　CREATE OR REPLACE PROCEDURE PRO_AVG_SAL(P_DEPTNO IN NUMBER,P_SAL OUT NUMBER)
　　AS
　　BEGIN
　　　　SELECT ROUND(AVG(SAL),2) INTO P_SAL FROM EMP WHERE DEPTNO = P_DEPTNO;
　　EXCEPTION
　　　　WHEN NO_DATA_FOUND THEN
　　　　P_SAL := 0;
　　END;

　　DECLARE
　　　　V_DEPTNO NUMBER := 10;
　　　　v_SAL NUMBER;
　　BEGIN
　　　　PRO_AVG_SAL(V_DEPTNO,V_SAL);
　　　　DBMS_OUTPUT.put_line(V_DEPTNO||'号部门平均工资:'|| v_SAL);
　　END;

 

　　示例3：计算low到high累加的和，使用存储过程进行改造[out模式]

　　create or replace procedure compute_sum(p_low in int,p_high in int,p_sum out int)
　　is
　　　　v_sum int := 0 ;
　　begin
　　　　for v_i in p_low..p_high loop
　　　　　　v_sum := v_sum + v_i;
　　　　end loop;
　　　　p_sum := v_sum;
　　end compute_sum;

　　declare
　　　　v_sum int := 0;
　　begin
　　　　compute_sum(20,50,v_sum);
　　　　dbms_output.put_line ( '20..50累加的和为:'|| v_sum);
　　　　v_sum:=0;
　　　　compute_sum(200,500,v_sum);
　　　　dbms_output.put_line('200..500累加的和为:'|| v_sum);
　　end;