PL/SQL 语言

最新推荐文章于 2022-12-06 21:26:59 发布

chengyu0726

最新推荐文章于 2022-12-06 21:26:59 发布

阅读量313

点赞数

分类专栏： DB 文章标签： sql 数据库

本文链接：https://blog.csdn.net/chengyu0726/article/details/115714588

版权

DB 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

PL/SQL （Procedure Language/SQL）：对SQL 语言过程化的一个扩展，比如，在SQL 语言中添加逻辑分支、循环等，使SQL 语言具有过程化处理能力；是面向过程的编程语言。

1. 结构

DECLARE
 -- 声明部分：变量、类型、游标、局部存储过程和函数
BEGIN
 -- 执行部分
EXCEPTION
 -- 异常处理部分 
End;

其中【 BEGIN ~ End; 】部分是必须的。

2. Hello World 输出

BEGIN
    -- 输出语句
    DBMS_OUTPUT.PUT_line('Hello world');
  
END PROCEDURE_HELLO;

执行后输出：Hello world

也可在sqlplus 中执行：

C:\Users\lenovo>sqlplus

请输入用户名:  system as sysdba
输入口令:

SQL> BEGIN
  2      -- 输出语句
  3      DBMS_OUTPUT.PUT_line('Hello world');
  4
  5  END PROCEDURE_HELLO;
  6  /

PL/SQL 过程已成功完成。

SQL> set serveroutput on
SQL> BEGIN
  2      -- 输出语句
  3      DBMS_OUTPUT.PUT_line('Hello world');
  4
  5  END PROCEDURE_HELLO;
  6  /
Hello world

PL/SQL 过程已成功完成。

注意：
在SQLPLUS 中执行时，结尾处放一个【/】，表示过程结束；
且默认输出是关闭状态，需要开启，命令：set serveroutput on

3. 命名规则

标识符	命名规则	例子
程序变量	V_name	V_name
程序常量	V_name	V_person_name
游标变量	Name_cursor	Per_cursor
异常标识	E_name	E_too_many
表类型	Name_table_type	Per_record_type
表	Name_table	Emp
记录类型	Name_record	Emp_record
SQL/Plus 替代变量	P_name	P_sal
绑定变量	G_name	G_year_sal

4. 基本类型

声明：变量名变量类型
v_name varchar2(20)

赋值：
直接赋值：v_name := ‘chengyu’
语句赋值： select … into := 变量

4.1 自定义变量类型

set serveroutput on;

DECLARE
    v_name VARCHAR2(20) := 'chengyu';
    v_sallary NUMBER;
    v_address VARCHAR2(200);
    v_birthday date:=to_date('2021-08-26','YYYY-MM-DD');

BEGIN
	-- 直接赋值
    v_sallary := 500;
    -- 语句赋值
    select '大连' into v_address from dual;
        
    DBMS_OUTPUT.PUT_LINE('v_name='||v_name||',v_sallary='||v_sallary||',v_address'||v_address);
End;

输出：v_name=chengyu,v_sallary=500,v_address大连

4.2 引用型变量类型

引用型变量的类型与长度，取决于表中字段的类型和长度；

语法：表名.列名%TYPE，如 v_name user_tab.username%TYPE

DECLARE
    v_name user_tab.username%TYPE;
    v_age user_tab.age%TYPE;

BEGIN
    select username,age into v_name,v_age from user_tab where uuid = '1000';
        
    DBMS_OUTPUT.PUT_LINE('v_name='||v_name||',v_age='||v_age);
End;

输出：v_name=chengyu,v_age=30

5. 复合类型

5.1 数组类型变量 varray

5.1.1 一维数组

declare
  type user_varray is varray(2) of varchar2(20);
  v_users user_varray:=user_varray('yongyan','yongbin');
begin
  for x in v_users.first .. v_users.last loop
      dbms_output.put_line(v_users(x));
  end loop;
end;

5.1.2 二维数组

declare
  type user_varray is varray(2) of varchar2(20);
  v_user_1 user_varray:=user_varray('yongyan','yongbin');
  v_user_2 user_varray:=user_varray('chengyu','chenglong');
  
  type users_varray is varray(2) of user_varray;
  v_users users_varray:=users_varray(v_user_1,v_user_2);
  
begin
  for x in v_users.first .. v_users.last loop
      for y in v_users(x).first .. v_users(x).last loop
        dbms_output.put_line(v_users(x)(y));
      end loop;
  end loop;
end;

输出：
yongyan
yongbin
chengyu
chenglong

5.2 记录类型

5.2.1 自定义一行 record

declare 
  -- 声明记录类型
  type person_record is record(
	  p_name varchar(20) not null default 'chengyu',
	  p_age number(7),
	  birthday date:=to_date('2021-08-26','YYYY-MM-DD')
  );
  -- 定义记录类型变量
  v_dalian_persons person_record;
begin
  --赋值
  v_dalian_persons.p_name:='chenglong';
  v_dalian_persons.p_age:=30;
  v_dalian_persons.birthday:=to_date('1990-01-01','YYYY-MM-DD');
  -- select name,age,birthday into v_dalian_persons from person_t where id = '1001'
  
 --输出
 dbms_output.put_line('dalian_persons p_name : '|| v_dalian_persons.p_name);
 dbms_output.put_line('dalian_persons p_age : '|| v_dalian_persons.p_age);
 dbms_output.put_line('dalian_persons birthday : '|| v_dalian_persons.birthday);

end;

输出：
dalian_persons p_name : chenglong
dalian_persons p_age : 30
dalian_persons birthday : 01-1月 -90

5.2.2 表中一行 rowtype

语法：变量名称表名%ROWTYPE，如 v_user username%rowtype;

DECLARE
    -- 记录类型变量
    v_user user_tab%ROWTYPE;

BEGIN
    select * into v_user from user_tab where uuid = '1000';
        
    DBMS_OUTPUT.PUT_LINE('v_name='||v_user.USERNAME||',v_age='||v_user.AGE||',v_address='||v_user.ADDRESS);
End;

输出：v_name=chengyu,v_age=30,v_address=大连

5.3 表类型变量 table

相当于java中的Map容器！或一个可变长的数组！

语法如下：
type 表类型 is table of 类型 index by binary_integer;
表变量名表类型;

index by binary_integer 子句代表以符号整数为索引！
值为标量，也可以是record类型！

5.3.1 Table 存基本类型（带角标一维数组）

declare 
    type t_tb is table of varchar2(20) index by binary_integer; 
    v_tb t_tb; 
begin 
    v_tb(100):='hello'; 
    v_tb(98):='world'; 
    dbms_output.put_line(v_tb(100)); 
    dbms_output.put_line(v_tb(98)); 
end;

5.3.2 Table 存 Record（多维数组）

declare 
    type t_rd is record(id number,name varchar2(20)); 
    type t_tb is table of t_rd index by binary_integer; 
    v_tb2 t_tb; 
begin 
    v_tb2(100).id:=1; 
    v_tb2(100).name:='hello'; 
    dbms_output.put_line(v_tb2(100).id); 
    dbms_output.put_line(v_tb2(100).name); 
end;

DECLARE
    type person_record is record(
	  username varchar(20),
	  age number(7)
  );
    dalian_persons person_record;

  TYPE rec_table_type IS TABLE OF person_record INDEX BY BINARY_INTEGER;
  rec_table rec_table_type;

BEGIN
  FOR i IN 1000 .. 1003
  LOOP
    SELECT username,age INTO rec_table(i) FROM user_tab WHERE uuid = i;
    --dbms_output.put_line(i);
  END LOOP;

  FOR i IN rec_table.first .. rec_table.last
  LOOP
    dbms_output.put_line(rec_table(i).username ||' '||rec_table(i).age);
  END LOOP;
END;

user_tab：
1000 chengyu 30 26-8月 -21 大连
1001 chenglong 40 26-8月 -21 香港
1002 chengwei 20 26-8月 -21 大连
1003 chengyu 20 26-8月 -21 日本

输出：
chengyu 30
chenglong 40
chengwei 20
chengyu 20

5.3.3 Table 存 rowtype

相当于多维数组！

declare 
    type menutype is table of menu%rowtype index by binary_integer; 
    menus menutype; 
begin 
    select * into menus(2) from menu where id=1; 
    dbms_output.put_line(menus(2).group_name||'='||menus(2).menu_name); 
end;

5.4 复合类型的属性和方法

first：取集合第一个元素的下标
last：取集合最后一个元素的下标
count：取集合中存放的元素个数（实际存放数据的个数）
limit：取集合能存放的数据个数（集合的长度，用于变长数组，索引表和嵌套表是无限长度）
next(下标)：取当前下标的下一个元素的下标
prior(下标)：取当前下标的上一个元素的下标
extend(n,index)：表示将集合扩展n个元素，index表示集合已有元素的下标，如果有第二个参数，表示扩展集合n个元素并且扩展后的对应值是index这个下标所对应的值
delete(n)：删除集合中的元素

6. 逻辑控制

6.1 条件分支

语法：
IF 条件1 THEN 执行1
ELSIF 条件2 THEN 执行2
ELSE 执行3
END IF;

DECLARE
    v_age user_tab.age%TYPE;

BEGIN

    select age into v_age from user_tab where uuid = '1000';
    
    IF v_age > 30 THEN
        DBMS_OUTPUT.PUT_LINE('不年轻啦！');
    ELSIF v_age > 10 THEN
        DBMS_OUTPUT.PUT_LINE('风华正茂！');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('小屁孩！');
    END IF;
End;

输出：风华正茂！

6.2 流程控制

循环：
语法：
LOOP

EXIT WHEN 退出循环
END LOOP;

DECLARE
    v_num number := 1;

BEGIN

    Loop
        EXIT WHEN v_num > 10;
        DBMS_OUTPUT.PUT_LINE(v_num);
        
        v_num := v_num + 1;
    END LOOP;
End;

输出1~10

7. 游标

用于临时存储一个查询返回的多行数据（类似于Java ResultSet集合），通过遍历游标，可以逐行访问处理结果；

7.1 使用方式

声明 ⇒ 打开 ⇒ 读取 ⇒ 关闭
声明：CURSOR 游标名(参数列表) IS 查询语句;
打开：OPEN 游标名;
读取：FETCH 游标名 INTO 变量列表;
关闭：CLOSE 游标名;

7.2 游标属性

属性	返回值类型	说明
%ROWCOUNT	整型	返回数据行数
%FOUND	布尔型	读取（FETCH ）数据，返回一行为真，否则为假
%NOTFOUND	布尔型	与%FOUND 相反，常用于退出循环
%ISOPEN	布尔型	游标已打开为真，否则为假

7.3 例子（无参）

遍历人员表中人员名称及年龄

DECLARE
    -- 声明游标并赋值
    CURSOR c_users IS SELECT username,age FROM user_tab;
    -- 声明接受游标元素的变量
    v_name user_tab.username%TYPE;
    v_age user_tab.age%TYPE;
BEGIN
    -- 打开游标
    OPEN c_users;
    -- 遍历游标
    Loop
        -- 获取游标中数据（注意顺序与表中一致）
        FETCH c_users INTO v_name,v_age;
        -- 退出循环条件
        EXIT WHEN c_users%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('v_name='||v_name||',v_age='||v_age);
    END LOOP;
    -- 关闭游标
    CLOSE c_users;
End;

输出：
v_name=chengyu,v_age=30
v_name=chenglong,v_age=40
v_name=chengwei,v_age=20

7.4 例子（有参）

遍历人员表中年龄大于25岁的人员名称及年龄

DECLARE
    -- 声明游标（设置形参）
    CURSOR c_users(v_age user_tab.age%TYPE) IS SELECT username,age FROM user_tab where age > v_age;
    
    -- 声明接受游标元素的变量
    v_name user_tab.username%TYPE;
    v_age user_tab.age%TYPE;
BEGIN
    -- 打开游标（传入实参）
    OPEN c_users(25);
    -- 遍历游标
    Loop
        -- 获取游标中数据（注意顺序与表中一致）
        FETCH c_users INTO v_name,v_age;
        -- 退出循环条件
        EXIT WHEN c_users%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('v_name='||v_name||',v_age='||v_age);
    END LOOP;
    -- 关闭游标
    CLOSE c_users;
End;

输出：
v_name=chengyu,v_age=30
v_name=chenglong,v_age=40

8. 异常处理

8.1 语法

Exception
	WHEN exception1 [Or exception2...] THEN
		statement1;
	WHEN exception1 [Or exception2...] THEN
		statement2;
	WHEN OTHERS THEN
		statement3;

8.2 预定义异常

Oracle 已经定义好的异常，直接通过异常名称进行捕获
    ① NO_DATA_FOUNT：执行的sql 语句没有查询到结果
    ② TOO_MANY_ROWS：数据太多
    ③ INVALID_CURSOR：失效的游标（游标没打开等情况）
    ④ ZERO_DIVIDE：除数为 0
    ⑤ DUP_VAL_ON_INDEX：违反主键约束
    ⑥ VALUE_ERROR：赋值异常（类型不一致）

DECLARE
    CURSOR c_users IS SELECT username,age FROM user_tab;
    v_name user_tab.username%TYPE;
    v_age user_tab.age%TYPE;
BEGIN
    --没数据异常
    --SELECT username INTO V_NAME from user_tab where uuid = '1005';
    --数据太多异常
    --SELECT username INTO V_NAME from user_tab where uuid = '1000' or uuid = '1001';
    --失效游标异常
    --FETCH c_users INTO v_name,v_age;
    --除数为 0
    --v_age := 1/0;
    --违反主键约束异常
    --INSERT INTO user_tab VALUES('1000','','','',''); 
    --类型不一致
    v_age := 'a';
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        dbms_output.put_line('没数据异常！');
    WHEN TOO_MANY_ROWS THEN
        dbms_output.put_line('数据太多异常！');
    WHEN INVALID_CURSOR THEN
        dbms_output.put_line('失效游标异常！');
    WHEN ZERO_DIVIDE THEN
        dbms_output.put_line('除数为 0异常！');
    WHEN DUP_VAL_ON_INDEX THEN
        dbms_output.put_line('违反主键约束异常！');
    WHEN VALUE_ERROR THEN
        dbms_output.put_line('类型不一致异常！');
    WHEN OTHERS THEN
        dbms_output.put_line('其他异常！');
END;

※自定义异常略

9. 存储过程

PL/SQL 语言做成的业务存储起来，便于重复调用。
开发过程中，某些特定的功能，会对数据库进行多次连接、关闭（耗资源），对数据进行多次的IO读写等操作，性能比较低，使用存储过程，可以做到连接关闭一次数据库就可以实现业务需求，可以大大提高效率。

9.1 语法

注意：DECLARE 关键字省略

CREATE OR REPLACE PROCEDURE 存储过程名 
AS 

BEGIN
  NULL;
END PROCEDURE1;

可带参数，可有返回值

9.2 无参

过程 ⇒ 新建过程

CREATE OR REPLACE PROCEDURE P_HELLO AS 
-- 声明变量
v_name varchar(20) := 'CHENGYU';
BEGIN
  DBMS_OUTPUT.PUT_LINE('HELLO '||v_name);
END P_HELLO;

输出：HELLO CHENGYU

① SQL工作表中调用：

BEGIN
    P_HELLO;
END;

② SQL PLUS 中调用

SQL> exec P_HELLO ;
HELLO CHENGYU

PL/SQL 过程已成功完成。

9.3 带输入参数

CREATE OR REPLACE PROCEDURE P_PARAMETER(i_id IN user_tab.uuid%TYPE) AS 

v_name user_tab.username%TYPE;
v_age user_tab.age%TYPE;

BEGIN
  select username,age into v_name,v_age from user_tab where uuid = i_id;
  
  DBMS_OUTPUT.PUT_LINE('v_name='||v_name||',v_age='||v_age);
  
END P_PARAMETER;

① SQL工作表中调用：

BEGIN
    P_PARAMETER(1001);
END;

输出：v_name=chenglong,v_age=40
② SQL PLUS 中调用

SQL> exec P_PARAMETER(1000);
v_name=chengyu,v_age=30

PL/SQL 过程已成功完成。

9.4 带输出参数（有返回值）

一般提供给第三方程序调用；

1）定义存储过程

create or replace PROCEDURE P_PARAMETER_OUT(i_id IN user_tab.uuid%TYPE,o_name OUT user_tab.username%TYPE,o_age OUT user_tab.age%TYPE) AS 

BEGIN
    select username,age into o_name,o_age from user_tab where uuid = i_id;
  
END P_PARAMETER_OUT;

① SQL工作表中调用：

DECLARE
--接受存储过程返回结果
    v_name user_tab.username%TYPE;
    v_age user_tab.username%TYPE;
BEGIN
     p_parameter_out(1001,v_name,v_age);
     DBMS_OUTPUT.PUT_LINE('v_name '||v_name||',v_age '||v_age);
END;

输出：v_name chenglong,v_age 40

9.5 Java 程序调用

创建java 项目
新建lib 文件夹，并将 ojdbc.jar 文件复制到该文件夹中，ojdbc.jar 右键Add as Library ⇒ OK

创建Class

public class test {
    @Test
    public void test() throws ClassNotFoundException, SQLException {
        // 1.加载驱动
        Class.forName("oracle.jdbc.OracleDriver");
        // 2.获取连接对象
        String url = "jdbc:oracle:thin:@localhost:1521:chengyu";
        String user = "ADMINUSER";
        String password = "Aa135007";
        Connection conn = DriverManager.getConnection(url,user,password);
        // 3. 获得语句对象
        String sql = "Call P_PARAMETER_OUT(?,?,?)";
        CallableStatement call = conn.prepareCall(sql);
        // 4.设置输入参数
        call.setInt(1,1001);
        // 5.注册输出参数
        call.registerOutParameter(2, OracleTypes.VARCHAR);
        call.registerOutParameter(3, OracleTypes.NUMBER);
        // 6. 执行存储过程
        call.execute();
        // 7.获取输出参数
        String u_name = call.getString(2);
        int u_age = call.getInt(3);

		System.out.println("u_name="+u_name+",u_age="+u_age);
        // 8.释放资源
        call.close();
        conn.close();
    }
}

输出：u_name=chenglong,u_age=40

10. 存储函数

相对于存储过程，存储函数可以向调用者返回数据。

10.1 语法

注意：DECLARE 关键字省略

CREATE OR REPLACE FUNCTION 存储函数名 
RETURN 返回类型
IS
 -- 声明变量、记录类型、游标等
BEGIN
  Exception
END PROCEDURE1;

10.2 无参

定义：

create or replace function hello_world
RETURN varchar2 
IS
    
BEGIN

    RETURN 'Hello World';
END;

调用：

begin
     DBMS_OUTPUT.PUT_LINE(hello_world);
end;

-- select hello_world from dual

10.3 带参数

定义：

create or replace function hello_world(v_logo varchar2)
RETURN varchar2 
IS
    
BEGIN

    RETURN 'Hello:'||v_logo;
END;

调用：

begin
     DBMS_OUTPUT.PUT_LINE(hello_world('chenglong'));
end;

select hello_world('chengcheng') from dual

11. 触发器

是许多关系型数据库系统都提供的一项技术，由一个事件来启动运行，不能接受参数。

11.1 触发器组成

触发事件：在什么情况下触发，如：INSERT、UPDATE、DELETE；
触发时间：触发事件发生前（before）还是发生后（after）；
触发器本身：该触发器要做什么；
触发频率：动作执行的次数，语句级（STATEMENT）触发器和行级（ROW）触发器。

11.2 创建

create or replace TRIGGER update_menu_triger
    after update on menu
    for each row -- 每行都执行，去掉之后只执行一次
begin
    DBMS_OUTPUT.PUT_LINE('============触发器执行==============');
END;

create or replace TRIGGER "ADMINUSER".trigcer_user_tab
    before insert on user_tab
begin
    DBMS_OUTPUT.PUT_LINE('============触发器执行==============');
    if(to_char(sysdate,'DY') in ('星期六','星期日')) or 
    (to_char(sysdate,'HH24:MI') not between '08:00' and '18:00') THEN
    
    raise_application_error(-20500,'只能在工作时间对该表进行操作！');
    End if;
END;