Oracle之PLSQL语法
PL/SQL
PL：Procedure Language 过程式的语言。使用PL/SQL可以再Oracle中编写流程控制语句。
PL/SQL基本语法：
begin
-- 过程体
end;
例：向控制台输出 Hello Oracle
set serveroutput on -- 设置向控制台服务器输出语句
begin
-- dbms：database managment system
dbms_output.put_line('Hello Oracle');
end;
定义变量
Oracle定义的局部变量以 v_ 开始 v是变量variable简称
语法：
declare
变量名 数据类型 := 值;
begin
dbms_output.put_line(输出变量);
end;
例：定义变量并输出
declare
v_var number(3,2):= 3.14; -- := 等于
begin
dbms_output.put_line('变量：' || v_var); -- ||：拼接符
end;
定义常量
语法：
declare
常量名称 constant 数据类型 :=常量值;
begin
dbms_output.put_line(输出常量);
end;
例：
declare
v_con constant number(6):=12;
begin
dbms_output.put_line('常量：'||v_con);
end;
修改变量的值
例：
declare
v_var number(5):= 10;
begin
v_var := v_var + 2;
dbms_output.put_line('变量值修改后为：'||v_var);
end;
接受输入
语法：
declare
变量名 数据类型 := &n;
begin
dbms_output.put_line(变量名);
end;
例：输入年龄并且打印
declare
v_input number(5):= &n;
begin
dbms_output.put_line('输入参数为：'||v_input);
end;
输入字符串并打印
语法：
declare
变量名 数据类型 := '&n';
begin
dbms_output.put_line(变量名);
end;
例：
declare
v_input varchar2(5):= '&n';
begin
dbms_output.put_line('输入的字符串为：'||v_input);
end;
分支结构
简单分支
语法：
begin
if 条件 then
-- 条件成立执行分支语句
end if;
end
-- 注意： then相当于java的{ end if; 相当于java的}
-- end if; 分号不能少
例：
declare
v_input number(5):= &n;
begin
if v_input>80 then
dbms_output.put_line('奖励一根棒棒糖！');
end if;
end;
简单if...else分支
语法：
begin
if 条件 then
-- 条件语句
else
-- 条件语句
end if;
end ;
-- 注意：else没有条件不要加then
例：
declare
v_input number(5):= &n;
begin
if v_input>60 then
dbms_output.put_line('及格');
else
dbms_output.put_line('不及格');
end if;
end;
多条件if...else分支
语法：
begin
if 条件1 and 条件2 then
-- 条件语句
else
-- 条件语句
end if;
end;
例：
declare
v_java_score number(5):=&n;
v_music_score number(5):=&n;
begin
if v_java_score>90 and v_music_score>80 then
dbms_output.put_line('良好');
else
dbms_output.put_line('闭门思过');
end if;
end;
多重分支
begin
if 条件 then
elsif 条件 then
else
end if;
end;
例：
declare
v_input number(5):= &n;
begin
if v_input>=90 then
dbms_output.put_line('优秀：'||v_input);
elsif v_input<90 and v_input>=70 then
dbms_output.put_line('良好：'||v_input);
elsif v_input<70 and v_input>=60 then
dbms_output.put_line('及格：'||v_input);
else
dbms_output.put_line('不及格：'||v_input);
end if;
end;
多重等值判断
语法：
begin
case
when 条件 then
when 条件 then
when 条件 then
when 条件 then
else
end case;
end;
例：从控制台输入字母，判断等级 A优秀B良好C中等D及格E稀烂
declare
v_garde varchar2(20) := '&n';
begin
case
when v_garde='A' then
dbms_output.put_line('优秀');
when v_garde='B' then
dbms_output.put_line('良好');
when v_garde='C' then
dbms_output.put_line('中等');
when v_garde='D' then
dbms_output.put_line('及格');
else
dbms_output.put_line('稀烂');
end case;
end;
循环结构
for循环语法：
begin
for 变量 in 初始值..最大值 loop
-- 打印语句
end loop;
end;
-- 注意：初始值与最大值之间只有两个【..】
例：向控制台打印1到10之间的整数
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
例：计算1~100和并打印
declare
v_sum number(5):=0;
begin
for i in 1..100 loop
v_sum := v_sum + i;
end loop;
dbms_output.put_line(v_sum);
end;
while循环语法：
declare
初始化变量
begin
while 条件 loop
执行循环体;
更新循环变量;
end loop
end;
例：1到100的和
declare
v_index number(3):=0;
v_sum number(5):=0;
begin
while v_index<=100 loop
v_sum := v_sum + v_index;
v_index := v_index + 1;
end loop;
dbms_output.put_line('1到100累加之和：'||v_sum);
end;
loop循环，至少执行一次
begin
loop
循环体
exit when 条件; -- 注意：条件为true退出循环
end loop;
end;
例：
declare
v_index number(3):=6;
v_sum number(5):=6;
begin
loop
v_sum := v_sum + v_index;
v_index := v_index + 1;
exit when v_index<=100;
end loop;
dbms_output.put_line('至少执行一次，结果为：'||v_sum);
end;
PL与SQL结合
例：根据员工编号查询对应的员工信息(ename,sal,job)，将查询的结果使用变量输出。
步骤：1. 在declare块中定义变量，存储emp表对应的员工信息，
2. 在begin块中编写sql语句，将查询的结果使用into关键字赋给变量，
3. 输出对应编号的员工信息(ename,sal,job)。
declare
v_ename varchar2(30);
v_sal number(5);
v_job varchar2(30);
v_empno number(4) :=&n; -- 编号
begin
select ename 姓名,sal 薪资,job 职位 into v_ename,v_sal,v_job from emp where empno = v_empno;
dbms_output.put_line(v_ename||' '||v_sal||' '||v_job);
end;
以上例子存在风险，变量名不知道列名称的数据类型和长度，一下方法可以解决此问题。
%type
为了使一个变量的数据类型与另一个已经定义了的变量（尤其是表的某一列）的数据类型相一致。
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
v_job emp.job%type;
v_empno emp.empno%type := &n;
begin
select ename,sal,job into v_ename,v_sal,v_job from emp where empno = v_empno;
dbms_output.put_line(v_ename||' '||v_sal||' '||v_job);
end;
%rowtype
定义一个变量, 其数据类型和数据库表的数据结构相一致。定义一个变量的数据类型与一个表中记录的各个列的数据类型相对应、一致时，可以使用%rowtype来定义。
-- employee 是一个变量名（自己声明的），表示emp表的一行数据
-- 程序运行的时候根据where条件将emp表的一行赋给employee变量
declare
employee emp%rowtype;
v_empno emp.empno%type:=&n;
begin
SELECT ename,sal,job into employee.ename,employee.sal,employee.job FROM emp where empno = v_empno;
dbms_output.put_line(employee.ename||' '||employee.sal||' '||employee.job);
end;
例：根据员工编号获取员工的工资，对工资进行判断，小于1000加100，1000~2000之间的加50，大于2000的加30
步骤：
1. 定义变量，
2. 编写SQL语句根据编号查询工资，
3. 编写 case when then 语句判断工资，并对工资进行累加(100,50,30)，
4. 执行update语句更新工资，最后使用 commit 提交数据。
declare
v_sal emp.sal%type;
v_empno emp.empno%type:=&n;
begin
select sal into v_sal from emp where empno = v_empno;
case
when v_sal<=1000 then
v_sal := v_sal + 100;
when v_sal>1000 and v_sal<=2000 then
v_sal := v_sal + 50;
when v_sal>2000 then
v_sal := v_sal + 30;
end case;
update emp set sal=v_sal where empno=v_empno; -- 数据更新
commit; -- 提交数据
end;
例：使用PL/SQL向控制台输出所有员工信息(ename,sal,job)
declare
begin
for employee in (select ename,sal,job from emp) loop
dbms_output.put_line (employee.ename||' '||employee.sal||' '||employee.job);
end loop;
end;
Oracle存储过程
功能类似于java的方法，完成某个独立功能的一组指令(程序)集合。
特征：没有return关键字
编译一次，可以重复使用(调用)
署名的PL/SQL（根据名称可以重复调用），之前写的PL/SQL是匿名的（每次执行都要编译，不能重复调用）
存储过程语法：
-- create or replace 执行存储过程如果没有就创建，如果有覆盖(替换)之前的存储过程
create or replace procedure 存储过程名称(参数名称 参数类型 数据类型)
as
-- 定义变量
begin
-- 过程体
end;
-- 执行存储过程
exec 存储过程名称;
例：使用存储过程输出helloWorld
create or replace procedure pro_hw(v_word in varchar2)
as
begin
dbms_output.put_line(v_word);
end;
exec pro_hw('HelloWorld!'); -- 执行存储过程，打印HelloWorld!
例：根据员工编号获取员工信息，使用过程完成
步骤：
1. 定义存储过程，将员工编号作为参数
2. 定义变量存储emp表的员工信息
3. 定义sql语句根据员工编号获取员工信息
4. 打印员工信息
-- 定义存储过程
create or replace procedure getEmpById(v_empno in number)
as
employee emp%rowtype;
begin
select ename,sal,job,deptno into employee.ename,employee.sal,employee.job,employee.deptno from emp where empno=v_empno;
dbms_output.put_line(employee.ename||' '||employee.sal||' '||employee.job||' '||employee.deptno);
end;
-- 执行过程
exec getEmpById(7900);
游标
游标是指向此上下文区域的指针。PL/SQL通过游标控制上下文区域，游标保存SQL语句返回的行(一个或多个)。
可以命名一个游标，以便在程序中引用它来获取和处理SQL语句返回的行，一次处理一个(行)。PL/SQL中有两种类型的游标：
- 隐式游标
当执行SQL语句时，如果语句没有显式游标，则Oracle会自动创建隐式游标。
- 显式游标
是指在使用之前有明确的游标声明和定义，这样游标定义会关联数据查询语句，通常会返回一行或多行。打开游标后，用户可以利用游标的位置对结果集进行任何操作，显示游标有用户控制。
例：根据部门编号获取部门对应的员工信息，使用过程完成
使用过程 + 游标完成，在过程中定义游标，逐个打印。
-- 创建存储过程
create or replace procedure getEmpByDeptNo(v_deptno in number)
is
begin
for i in (select ename,sal,job from emp where deptno=v_deptno) loop
dbms_output.put_line(i.ename||' '||i.sal||' '||i.job);
end loop;
end;
-- 调用存储过程
exec getEmpByDeptNo(30);
场景：上面示例使用显示游标完成
显示游标：自己定义游标
显示游标语法:
create or replace procedure 过程名(参数名 参数类型 数据类型)
as
cursor 游标名称 is SQL语句
begin
for 变量 in 游标名称 loop
循环体
end loop;
end;
create or place procedure get_Emp_By_Dept_No2(v_deptno in number)
as
-- cursor 科索沃
cursor employee is select ename,sal,job from emp where deptno=v_deptno;
begin
for i in employee loop
dbms_output.put_line(i.ename||'----'||i.sal||'------'||i.job);
end loop;
end;
个人笔记，难免不足，可以参照下方链接
PL/SQL教程：https://www.yiibai.com/plsql
