PL/SQL编程

【语法结构】

　　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;

【游标】【*】

　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;

【异常处理（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 function fun_name(参数1 参数类型) return 参数类型 is|as
　　变量 变量类型；
　　begin
　　　　plsql语句;
　　　　return(变量);
　　exception
　　　　when ... then
　　　　　　exception语句;
　　　　　　return(0);
　　end(fun_name);

　　2）调用
　　select fun_name(参数1),fun_name(参数2)... from dual;

　　3）删除
　　drop function fun_name;

　　--示例：计算emp表中指定某个部门的平均工资
　　　　create or replace function fun_emp_avgsal(f_deptno number )
　　　　return number is
　　　　f_avgsal number;
　　　　begin
　　　　　　select avg(sal) into f_avgsal from emp where deptno=f_deptno;
　　　　　　return(round(f_avgsal,2));
　　　　exception
　　　　　　when no_data_found then
　　　　　　return(0);
　　　　end;
　　　　
　　--调用
　　　　select fun_emp_avgsal(10),fun_emp_avgsal(30) from dual;

【同义词对象，序列对象】

　　1）同义词
　　概念：　同义词是表、索引、视图等模式对象的一个别名。通过模式对象创建同义词，可以隐藏对象的实际名称和所有者信息，
　　　　　　或者隐藏分布式数据库中远程对象的设置信息，由此为对象提供一定的安全性保证*/

--注意：如果数据库用户要建立公有同义词，则要求该用户必须具有CREATE PUBLIC SYNONYM系统权限　
　　　grant create public SYNONYM to scott;

　　-- 创建公有同义词
　　　　create public synonym public_dept for peppa.dept;
　　-- 创建私有同义词
　　　　create synonym private_dept for peppa.dept;
　　-- 删除同义词
　　　　drop synonym public_dept;

　　2）序列
　　概念：序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。不占用磁盘空间，占用内存
　　主要用途：生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。

　　--创建序列
　　　　create sequence seq_name
　　　　[increment by n] --按步长n递增，若为负，则递减，n默认为1
　　　　[start with n] --初始值，默认为1
　　　　[maxvalue|minvalue|nomaxvalue] --最大值、最小值、默认选项无最大值定义
　　　　[cycle|nocycle] --是否循环
　　　　[cache|nocache] --内存块大小，默认20

　　--使用序列：需要使用如下伪列
　　　　NEXTVAL --返回序列中下一个有效的值，任何用户都可以引用。
　　　　CURRVAL --中存放序列的当前值,NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效。

【触发器】

　　组成：
　　　　触发事件：DML语句、DDL语句、数据系统事件、用户事件
　　　　触发时间：before\after\
　　　　触发操作：plsql语句....
　　　　触发对象：表、视图、模式、数据库
　　　　触发条件：when 逻辑表达式
　　　　触发频率：说明触发器内定义的动作被执行的次数。分为语句级触发器、行级触发器

　　1）创建触发器
　　　create [or replace] trigger tri_name
　　　[before | after | instead of] tri_event
　　　on table_name | view_name | user_name | db_name
　　　[for each row [when tri_condition]
　　　begin
　　　　　plsql_sentences;
　　　end tri_name;

　　2)删除
　　　drop trigger tri_name;

　　--示例：使用触发器针对dept表进行监控，首先创建一个日志表，用于存储对dept表的各种操作信息
　　create table dept_log(
　　　　operate_tag varchar2(100), --存储操作种类信息
　　　　operate_time date --存储操作日期
　　);

　　
　　-- 创建一个关于dept表的语句级触发器，将用户对dept表的操作信息保存到dept_log表中
　　create or replace trigger tri_dept
　　before insert or update or delete
　　on dept --创建触发器，当dept表发生插入，修改，删除操作时引起该触发器执行
　　declare
　　　　var_tag varchar2(10);--声明一个变量，存储对dept表执行的操作类型
　　begin
　　　　if inserting then --当触发事件是INSERT时
　　　　　　var_tag := '插入';
　　　　elsif updating then --当触发事件是UPDATE时
　　　　　　var_tag := '修改';
　　　　elsif deleting then--当触发事件是DELETE时
　　　　　　var_tag := '删除';
　　　　end if;
　　　　　　insert into dept_log values(var_tag,sysdate);--向日志表中插入对dept表的操作信息
　　　　　　end tri_dept;

　　select * from dept;
　　select * from dept_log;

　　insert into dept values(50,'CODING','UESTC');
　　update dept set dname='YCL' where deptno=50;
　　delete from dept where deptno=50;

　　3）行级触发器

　　概念：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。必须在语法中使用for each row
　　列标识符：
　　　　:NEW 新值标识符（操作完成后列的值）
　　　　:OLD 原值标识符（操作完成前列的值）*/

　　示例1：给数据表生成主键值
　　--先创建一个序列
　　create sequence seq_deptno
　　increment by 10
　　start with 50;

　　-- 创建一个行级触发器，该触发器在数据表dept插入数据时被触发，并且在该触发器的主体中实现课程表的id列的值
　　create or replace trigger tri_insert_dept
　　before insert
　　on dept
　　for each row
　　begin
　　　　select seq_deptno.nextval into :new.deptno from dual;
　　end;

　　insert into dept(dname,loc) values('LHJ','MANAGER');

　　--示例2:建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。
　　create or replace trigger tri_emp_remove
　　after delete on emp for each row
　　begin
　　　　insert into dept_log values('remove:'||:old.empno ||','||:old.ename ||')',sysdate);
　　end tri_emp_remove;

　　select * from emp;
　　delete from emp where empno in(7844,7934);

【程序包】

　　组成：由pl/sql元素组成，变量、类型、匿名pl/sql、存储过程、函数等
　　程序包通常由规范和主体组成。
　　该“规范”用于规定在程序包中可以使用哪些变量、类型、游标和子程序（指各种命名的PL/SQL块）
　　需要注意的是：程序包一定要在“包主体”之前被创建

　　create [or replace ] package pack_name is
　　[declare_variable]; --变量
　　[declare_type]; --类型
　　[declare_cursor]; --游标
　　[declare_function]; --函数
　　[declare_ procedure]; --存储过程
　　end [pack_name];

　　创建“程序包主体”使用CREATE PACKAGE BODY语句

　　create [or replace] package body pack_name is
　　[inner_variable]
　　[cursor_body]
　　[function_title]
　　　　{begin
　　　　　　fun_plsql;
　　　　[exception]
　　　　　　[dowith _ sentences;]
　　　　end [fun_name]}
　　[procedure_title]
　　　　{begin
　　　　　　pro_plsql;
　　　　[exception]
　　　　　　[dowith _ sentences;]
　　　　end [pro_name]}
　　…
　　end [pack_name];