【数据库】PLSQL

转载出处：https://blog.csdn.net/weixin_43485671/article/details/102720068

目录

💎PLSQL是什么

💎PLSQL语法 

💎PLSQL变量

💎判断体

💎循环

💎例外、异常

💎存储过程和函数

 

---

💎PLSQL是什么

        是专用于Oracle服务器，在SQL基础之上，添加了一些过程化控制语句，叫PLSQL，过程化包括有：类型定义，判断，循环，游标，异常或例外处理。 

 

💎PLSQL语法 

       declare和exception都是可以省略的，begin和end;/是不能省略的。

       在PLSQL程序中：；号表示每条语句的结束，/表示整个PLSQL程序结束

 
     [declare]
          变量声明;
          变量声明;
     begin
          DML/TCL操作;
          DML/TCL操作;
     [exception]
          例外处理;
          例外处理;
     end;
     /

 

💎PLSQL变量

       PLSQL的变量有4种：number、varchar2、与列名类型相同、与整个表的列类型相同。

       

 
写一个PLSQL程序，输出"hello world"字符串，语法：dbms_output.put_line('需要输出的字符串');
begin
    --向SQLPLUS客户端工具输出字符串
    dbms_output.put_line('hello 你好');
end;
/
 
注意：
dbms_output是oracle中的一个输出对象
put_line是上述对象的一个方法，用于输出一个字符串自动换行 
 
设置显示PLSQL程序的执行结果，默认情况下，不显示PLSQL程序的执行结果，语法：set serveroutput on/off;
set serveroutput on;
 
使用基本类型变量,常量和注释，求10+100的和
declare
    --定义变量
    mysum number(3) := 0;
    tip varchar2(10) := '结果是';
begin
    /*业务算法*/   
    mysum := 10 + 100;
    /*输出到控制器*/
    dbms_output.put_line(tip || mysum);
end;
/
 
输出7369号员工姓名和工资，格式如下：7369号员工的姓名是SMITH,薪水是800，语法：使用表名.字段%type
declare
    --定义二个变量，分别装姓名和工资
    pename emp.ename%type;
    psal   emp.sal%type;
begin  
    --SQL语句
    --select ename,sal from emp where empno = 7369;
    --PLSQL语句，将ename的值放入pename变量中，sal的值放入psal变量中    
    select ename,sal into pename,psal from emp where empno = 7369;
    --输出
    dbms_output.put_line('7369号员工的姓名是'||pename||',薪水是'||psal);    
end;
/
 
输出7788号员工姓名和工资，格式如下：7788号员工的姓名是SMITH,薪水是3000，语法：使用表名%rowtype
declare
    emp_record emp%rowtype;
begin
    select * into emp_record from emp where empno = 7788;
    dbms_output.put_line('7788号员工的姓名是'||emp_record.ename||',薪水是'||emp_record.sal);
end;
/
 
 

 

💎判断体

⭐语法

--第一种
IF 条件 THEN 语句序列；
END IF;
 
--第二种
IF 条件 THEN 语句序列1；
ELSE 语句序列2;
END IF;
 
--第三种
IF 条件 THEN 语句序列1；
ELSIF 条件 THEN 语句序列2；
ELSE 语句序列3;
END IF;

⭐示例如下

--使用if-else-end if显示今天星期几，是"工作日"还是"休息日"
declare
    pday varchar2(10);
begin
    select to_char(sysdate,'day') into pday from dual;
    dbms_output.put_line('今天是'||pday);
    if pday in ('星期六','星期日') then
    dbms_output.put_line('休息日');
    else
    dbms_output.put_line('工作日');
    end if;
end;
/
 
--从键盘接收值，使用if-elsif-else-end if显示"age<16"，"age<30"，"age<60"，"age<80"
declare
    age number(3) := &age;
begin
    if age < 16 then
       dbms_output.put_line('你未成人');
    elsif age < 30 then
       dbms_output.put_line('你青年人');
    elsif age < 60 then
       dbms_output.put_line('你奋斗人');
    elsif age < 80 then 
       dbms_output.put_line('你享受人');
    else
       dbms_output.put_line('未完再继');
    end if;
end;
/

 

💎循环

在PLSQL中，循环的语法有三种：

⭐WHILE循环：while后面跟的是循环条件，与java的差不多，LOOP和END LOOP是关键字

WHILE  total  <= 25000  
LOOP
    total : = total + salary;
END  LOOP;

⭐FOR循环：循环的递增只能是1，不能自定义步长

 

FOR   I   IN   1 . . 3  
LOOP
语句序列 ;
END    LOOP ; 
 

⭐LOOP循环：exit后面的条件成立了才退出循环

Loop
   exit [when 条件成立];
   total:=total+salary;
end loop;

⭐示例如下

--使用loop循环显示1-10
declare
    i number(2) := 1;
begin
    loop
        --当i>10时，退出循环
        exit when i>10;
        --输出i的值
        dbms_output.put_line(i);
        --变量自加
        i := i + 1;  
    end loop;
end;
/
 
--使用while循环显示1-10
declare
    i number(2) := 1;
begin
    while i<11 
    loop
        dbms_output.put_line(i);
        i := i + 1;
    end loop;
end;
/
 
--使用while循环，向emp表中插入999条记录
declare
    i number(4) := 1;
begin 
    while( i < 1000 )
    loop
        insert into emp(empno,ename) values(i,'哈哈');
        i := i + 1;
    end loop;   
end;
/
 
--使用while循环，从emp表中删除999条记录
declare
    i number(4) := 1;
begin 
    while i<1000
    loop
        delete from emp where empno = i;
        i := i + 1;
    end loop;
end;
/
 
--使用for循环显示20-30
declare
    i number(2) := 20;
begin
    for i in 20 .. 30
    loop
        dbms_output.put_line(i);
    end loop;
end;
/
 

 

💎例外、异常

⭐语法

 --在declare节中定义例外   
out_of   exception ;
 
--在begin节中可行语句中抛出例外  
raise out_of ；
 
--在exception节处理例外
when out_of then …

⭐示例

 --使用oracle系统内置例外，演示除0例外【zero_divide】
declare
    myresult number;
begin
    myresult := 1/0;
    dbms_output.put_line(myresult);
exception
    when zero_divide then 
     dbms_output.put_line('除数不能为0');
     delete from emp;  
end;
/
 
--使用oracle系统内置例外，查询100号部门的员工姓名，演示没有找到数据【no_data_found】
declare
    pename varchar2(20);
begin
    select ename into pename from emp where deptno = 100;
    dbms_output.put_line(pename);
exception
    when NO_DATA_FOUND then 
     dbms_output.put_line('查无该部门员工');
     insert into emp(empno,ename) values(1111,'ERROR');
end;
/

 

 

💎存储过程和函数

⭐语法

过程与函数的使用场景：

• 当返回值只有一个参数的时候，那么就使用存储函数！
• 当返回值没有参数或者多于一个参数的时候，那么就使用过程！

# 无论是过程还是函数，as关键字都代替了declare关键字。


# 存储过程语法
create [or replace] procedure 过程名[(参数列表)]  
as
        PLSQL程序体；【begin…end;/】


# 函数语法
CREATE [OR REPLACE] FUNCTION 函数名【(参数列表) 】
 RETURN  返回值类型
AS
PLSQL子程序体；【begin…end;/】

⭐示例

# 创建无参的存储过程

CREATE OR REPLACE PROCEDURE hello
AS
  BEGIN
    dbms_output.put_line('hello world');
  END;


# 调用：

BEGIN
  hello();
END;


# 创建有参存储过程

CREATE or REPLACE PROCEDURE bb(pempno in NUMBER)
  AS
  BEGIN
    UPDATE EMP
    SET sal = sal * 1.2
    WHERE empno = pempno;
 
  END;
 
# 调用：

  BEGIN
    bb(7369);
  END;


# 创建过程：在过程中的参数，默认值是IN，如果是输出的话，那么我们要指定为OUT
CREATE OR REPLACE PROCEDURE find(pempno IN NUMBER, psal OUT VARCHAR2, pename OUT VARCHAR2, pjob OUT VARCHAR2)
AS
  BEGIN
    SELECT
      ename,
      sal,
      job
    INTO pename, psal, pjob
    FROM emp
    WHERE empno = pempno;
  END;


# 调用：
DECLARE
  psal   emp.sal%TYPE;
  pename emp.ename%TYPE;
  pjob   emp.job%TYPE;
BEGIN
  find(7369, psal, pename, pjob);
  dbms_output.put_line(psal || pename || pjob);
 
END;/