PLSQL_plsqlcsdn-CSDN博客

本文链接：https://blog.csdn.net/gaoshayo/article/details/120522540

本文详细介绍了Oracle的PL/SQL语言，包括变量声明、条件控制、循环和游标操作。PL/SQL结合了SQL与过程控制，支持创建存储过程，具有复用性和可移植性。通过DBMS_OUTPUT.PUT_LINE函数进行输出，利用游标处理查询结果。文中给出了多个示例，演示如何使用PL/SQL进行数据处理，包括变量输出、条件判断、循环控制及游标应用，帮助读者掌握PL/SQL的基本编程技巧。

摘要由CSDN通过智能技术生成

PLSQL
Oracle PL/SQL语言（Procedural Language/SQL）是结合了结构化查询与Oracle自身过程控制为一体的强大语言，PL/SQL不但支持更多的数据类型，拥有自身的变量声明、赋值语句，而且还有条件、循环等流程控制语句。过程控制结构与SQL数据处理能力无缝的结合形成了强大的编程语言，可以创建过程和函数以及程序包。

它能够携程程序存储过程，导入导出表，具有复用性和可移植性

PLSQLDEVELOPER连接oracle库是在C:\app\EDZ\product\11.1.0\db_1\NETWORK\ADMIN内的tnsnames.ora用notepad++打开

在这里插入图片描述
这是连接库的代码，如果在工作中要连接新的库，赋值改内容就可以了

基本操作包括：声明和赋值（变量与常量），条件空值，循环控制，游标

一.变量的使用

语法

BEGIN --开始
–逻辑部分
EXCEPTION --异常处理
END; --结束

变量的命名规范：V_变量名

1.认识函数：

输出（打印）函数
DBMS_OUTPUT.PUT_LINE(打印内容）；
（类似python的print）
打印函数只能打印具体值或者变量里面的值，不能打印查询结果如JOB
eg：打印HELLO WORLD
方法一：直接打印
BEGIN
DBMS_OUTPUT.PUT_LINE(‘HELLO WORLD’);
END;
方法二：在声明时赋值
DECLARE
V_X VARCHAR2(30) := ‘HELLO WORLD’;
BEGIN
DBMS_OUTPUT.PUT_LINE(V_X);
END;

打印方法三：在逻辑不分赋值（类似python先定义空列表再赋值）
DECLARE
V_X VARCHAR2(30) ；
BEGIN
V_X:=‘HELLO WORLD’;
DBMS_OUTPUT.PUT_LINE(V_X);
END

eg：输出8/3的余数，输出格式是‘8/3的余数是？’
方法一：

begin
	DBMS_OUTPUT.PUT_LINE(‘8/3的余数是’||mod(8,3));
end;

方法二：

declare
v_x number:= 8;
v_y number:= 3;
begin
  DBMS_OUTPUT.PUT_LINE(v_x||'/'||v_y||'的余数是'||MOD(V_X,V_Y));
end;

方法三

declare
v_x number ;
v_y number ;
BEGIN
	V_X:=8;
	V_Y:=3;
	DBMS_OUTPUT.PUT_LINE(v_x||'/'||v_y||'的余数是'||MOD(V_X,V_Y));
END;

2.使用变量输出查询内容

plsql无法直接输出查询内容，必须借助游标

游标：是指向查询结果的一个指针，通过游标可以将查询结果集中的记录逐一取出，并在PL/SQL程序块中处理。
简单理解：暂存数据用的，目的是赋值给变量。
两种类型：
隐式游标：是由系统自动创建并管理的游标
显示游标：

区别：隐式游标只能去一条记录（一行），对于多条数据，必须自己创建显式游标

隐式游标的写法：
SELECT 查询内容
INTO 变量
from 表
where …

eg：
--一般先写逻辑体
SELECT ENAME,JOB ,SAL
FROM EMP
WHERE SAL = (SELECT MAX(SAL) FROM EMP);

--代码执行部分：
DECLARE --声明
V_ENAME VARCHAR2(10);
V_JOB  VARCHAR2(10);
V_SAL NUMBER ;
BEGIN--逻辑体
SELECT ENAME,JOB ,SAL
INTO V_ENAME,V_JOB,V_SAL--赋值给变量
FROM EMP
WHERE SAL = (SELECT MAX(SAL) FROM EMP);
DBMS_OUTPUT.PUT_LINE( V_ENAME||' '||V_JOB||','||V_SAL);
END;

eg–1.查询工号为7788的员工的姓名，入职日期，工资

SELECT ENAME,HIREDATE ,SAL 
FROM EMP 
WHERE EMPNO = 7788;

DECLARE
V_ENAME VARCHAR2(10);
V_HIREDATE VARCHAR2(20) ;
V_SAL NUMBER;

BEGIN
SELECT ENAME,TO_CHAR(HIREDATE,'YYYY/MM/DD') ,SAL 
INTO V_ENAME,V_HIREDATE,V_SAL
FROM EMP 
WHERE EMPNO = 7788;
DBMS_OUTPUT.PUT_LINE(V_ENAME||','||V_HIREDATE||','||V_SAL);
END;
--------------------------------------------
--或者这样

DECLARE
V_ENAME VARCHAR2(10);
V_HIREDATE date ;
V_SAL NUMBER;

BEGIN
SELECT ENAME,HIREDATE ,SAL 
INTO V_ENAME,V_HIREDATE,V_SAL
FROM EMP 
WHERE EMPNO = 7788;
DBMS_OUTPUT.PUT_LINE(V_ENAME||','||to_char(V_HIREDATE,'yyyy/mm/dd')||','||V_SAL);
END;

显式游标

命名规范：C_游标名
语法：
DECLARE
CURSOR C_游标名 IS 查询语句;(CURSOR IS C_EMP IS SELECT EMPNO,ENAME FROM EMP, 把empno，ename的值放在游标内，)
定义一个游标变量
V_EMP EMP%ROWTYPE
begin
open游标名; --打开游标
FETCH 游标名 INTO 变量名;
EXIT WHEN C_EMP%NOTFOUND；
CLOSE 游标名; --关闭游标；
end；

游标属性：%FOUND %NOTFOUND

%FOUND
用于判断游标时候能从结果集中提取到数据，如果提取到数据，则返回TRUE,否则会FALSE.
%NOTFOUND
用于判断游标时候能从结果集中提取到数据，如果提取到数据，则返回FALSE.否则会TRUE.
在这里插入图片描述

FOR循环直接可以省略打开，关闭游标的过程
for x in c_emp loop
dmbs_output.put_line
在这里插入图片描述
例：输出员工表中的员工姓名
方法一：

declare
cursor c_emp is
select ename from emp;
v_emp varchar2(10);
begin
  open c_emp;
  loop
    fetch c_emp into v_emp; --fetch into 必须成对使用 每次提取一行值给V_EMP
    dbms_output.put_line(v_emp);
    exit when c_emp%notfound;
  end loop;
  close c_emp;
 end;

方法二
for循环

declare 
cursor c_emp is 
select ename from emp;
begin
  for i in c_emp loop
    dbms_output.put_line(i.ename); 
    end loop;
end;

这里的i.ename必须写上，就算表内只有一个字段，也必须声明要打印的是表内的这个字段。

练习：
–1.输出第4工资等级的员工姓名
–2.输出部门30的员工姓名，工作，工资。

--1.输出第4工资等级的员工姓名
select e.ename
from emp e
join salgrade s on e.sal between s.losal and s.hisal
where s.grade = 4

declare 
cursor c_emp is 
  select e.ename
  from emp e
  join salgrade s on e.sal between s.losal and s.hisal
  where s.grade = 4;

begin   
  for i in c_emp loop
    dbms_output.put_line(i.ename);
    end loop;
end;
--2.输出部门30的员工姓名，工作，工资。

select ename,job,sal
from emp
where deptno =30;

declare
cursor c_emp is 
  select ename,job,sal
  from emp
  where deptno =30;
begin 
  for i in c_emp loop
    dbms_output.put_line(i.ename||','||i.job||','||i.sal);
    end loop;
end;

3.引用表中的数据类型

1）引用列的数据类型：%TYPE
写法

declare
v_ename emp.ename%type;
v_job emp.job%tpye;
v_sal emp.sal%type;

2)引用表的数据类型：%ROWTYPE (相当于把整张表的结构拿过来了)

DECLARE
V_EMP EMP%ROWTYPE;
v_grade salgrade.grade%type;--不同的表要不同的变量
begin
    SELECT e.ENAME,e.HIREDATE ,e.SAL, s.grade
    INTO v_emp.ENAME,v_emp.HIREDATE,v_emp.SAL,v_grade--(表明用的是哪张表的哪个字段)
    FROM EMP e
    join salgrade s on e.sal between s.losal and s.hisal
    WHERE EMPNO = 7788;    DBMS_OUTPUT.PUT_LINE(V_emp.ENAME||','||to_char(V_emp.HIREDATE,'yyyy/mm/dd')||','||V_emp.SAL||','||v_grade);        
END;

4.常量
常量也需要赋值
必须在声明的时候赋值，不能在逻辑中赋值！！！
必须在声明的时候赋值，不能在逻辑中赋值！！！
必须在声明的时候赋值，不能在逻辑中赋值！！！

规范写法：V_常量名 CONSTANT 数据类型:= 值；

二.条件控制

4中判断方式：
IF THEN （次数较少）
END IF;

IF THEN ELSE（次数较少）
END IF;

IF THEN ELSIF（次数较多）
END IF;

CASE（次数较多）
END CASE;

–例1：输入一个日期，找出离他最近的一个闰年，并输出这个闰年。
–判断余数和2的关系，如果>2 则取下一个闰年，如果<2 则取上一个闰年，如果=2，两个闰年都要取。
–要有输入的日期，取输入的日期的年份，要计算年份与4的余数，上一个闰年，下一个闰年



declare
v_date date;
v_input number;
v_mod number;
v_last_rn number;
v_next_rn number;

begin
  v_date :=to_date(sysdate-10*365);
  v_input :=to_number(to_char(v_date,'yyyy'));
  v_mod := mod(v_input,4);
  v_last_rn := v_input-v_mod;
  v_next_rn := v_input-v_mod+4;
  if v_mod>2 then  dbms_output.put_line(v_next_rn);
  elsif v_mod<2 then dbms_output.put_line(v_last_rn);
  else dbms_output.put_line(v_next_rn||','||v_last_rn );
  end if ;
  end;

–例2：查询JAMES的工资，如果大于1500元，则发放奖金100元，
–如果工资大于900元，则发奖金800元，否则发奖金400元。

select sal
from emp
where ename = 'JAMES'；

declare
v_sal number;
v_comm number;
begin
  select sal
  into v_sal
  from emp
  where ename = 'JAMES';
  
  if v_sal >1500 then  v_comm:= 1000;
     
  elsif v_sal >900 then v_comm:=800;
     
  else  v_comm:= 400;
  end if ;
  dbms_output.put_line(v_sal||','||v_comm);
 end;

练习：

--2.输入一个变量员工编号，查出该员工的工资和部门平均工资
--如果工资大于部门平均工资，则输出该员工加薪30%后的工资
--否则输出该员工加薪50%后的工资

select sal , avg_sal
from(
select deptno,avg(sal) avg_sal
  from emp 
  group by deptno) a
join emp e on e.deptno = a.deptno
where empno = 7788;

declare
  v_empno number;
  v_sal number;
  v_avg_sal  number;
begin
    v_empno :=7521;
    select sal , avg_sal
    into v_sal,v_avg_sal
    from(
            select deptno,avg(sal) avg_sal
            from emp  
            group by deptno) a
    join emp e on e.deptno = a.deptno
    where empno = v_empno;
    if v_sal>v_avg_sal then dbms_output.put_line(v_sal*1.3);
    else dbms_output.put_line(v_sal*1.5);
    end if;
end;

三.循环控制

loop 循环，又称无限循环（类似while true）

格式
declare
–循环体
begin
loop
if 。。 then exit;
end if;
end loop;
end;

--1加到100 
declare
v_x number;
v_y number;
begin 
	v_x:=1;
	
	v_y :=2;
	loop
		v_x:=v_x+v_y;
		v_y:=v_y+1;
		if v_y=101 then exit;
		end if;
	end loop;
		dbms_output.put_line(v_x);
end;

while 循环 满足条件进循环

格式：
begin
while 条件 loop
–循环体
end loop;
end;

for 循环
格式：
declare
–声明变量
begin
for 循环变量 in (循环下限)…(循环上限) loop (完全等同于for x in range（））
–循环体（要加分号的）
end loop；
end；

封装过程

eg：输入一个部门编号，打印出改部门的员工姓名和工资，将这个过程封装到存储过程中。
sql
上述过程相同
加上以下过程

第一种方式：直接封装
将PLSQL拿过来，直接去掉declare，换成创建存储过程语句。

第二种方式：
create or replace procedure sp_emp3(v_emp （in） number) is
内部的声明v_emp部分删掉；
或者声明了变量，就不需要参数了。
调用的时候，在存储过程名后加括号，输入参数即可。
参数和变量的区别，参数后是不允许加参数大小的，但是变量可以这样varchar2(10)。

调用参考：
begin
sp_emp3(30);
end；

练习巩固：
–例：输入一个职业，输出员工姓名和工资

create or replace procedure sp_emp(v_job in varchar2  ) is

cursor c_emp is
select ename ,sal
from emp
where job = v_job;

begin
  for i in c_emp loop
    dbms_output.put_line(i.ename||' '||i.sal);
  end loop;

end;