PL/SQL 基操示例

最新推荐文章于 2024-04-28 16:03:45 发布

philpy_used

最新推荐文章于 2024-04-28 16:03:45 发布

阅读量643

点赞数 1

文章标签：触发器数据库 sql oracle plsql

本文链接：https://blog.csdn.net/qq_43613793/article/details/119463670

版权

1、PL/SQL

PL/SQL(Procedural Language/SQL)是一种过程化语言，是oracle对sql语言的过程化扩展，在sql命令语言中增加了过程处理语句（分支，循环等），使得SQL语言具有过程处理能力，把sql语言的数据操作能力与过程语言的数据处理能力结合起来，使得PL/SQL 面向过程但比面向过程语言更加简单、高效、灵活和实用。

2、新建一个测试用表

新建一个测试用用表EMP（存储员工信息）：

create table EMP
(
    EMPNO    NUMBER(4) not null
        constraint PK_EMP
            primary key,
    ENAME    VARCHAR2(10),
    JOB      VARCHAR2(15),
    MGR      NUMBER(4),
    HIREDATE DATE,
    SAL      NUMBER(7, 2),
    COMM     NUMBER(7, 2), -- 奖金
    DEPTNO   NUMBER(2)
);

INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('1980-12-17', 'YYYY-MM-DD HH24:MI:SS'), 800.00, null, 20);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('1981-02-20', 'YYYY-MM-DD HH24:MI:SS'), 1600.00, 300.00, 30);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('1981-02-22', 'YYYY-MM-DD HH24:MI:SS'), 1250.00, 500.00, 30);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('1981-04-02', 'YYYY-MM-DD HH24:MI:SS'), 2975.00, null, 20);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('1981-09-28', 'YYYY-MM-DD HH24:MI:SS'), 1250.00, 1400.00, 30);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1981-05-01', 'YYYY-MM-DD HH24:MI:SS'), 2850.00, null, 30);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1981-06-09', 'YYYY-MM-DD HH24:MI:SS'), 2450.00, null, 10);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('1987-04-19', 'YYYY-MM-DD HH24:MI:SS'), 3000.00, null, 20);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('1981-09-08', 'YYYY-MM-DD HH24:MI:SS'), 1500.00, 0.00, 30);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('1987-05-23', 'YYYY-MM-DD HH24:MI:SS'), 1100.00, null, 20);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('1981-12-03', 'YYYY-MM-DD HH24:MI:SS'), 950.00, null, 30);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('1981-12-03', 'YYYY-MM-DD HH24:MI:SS'), 3000.00, null, 20);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('1982-01-23', 'YYYY-MM-DD HH24:MI:SS'), 1300.00, null, 10);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7839, 'KING', 'PRESIDENT', null, TO_DATE('1981-11-17', 'YYYY-MM-DD HH24:MI:SS'), 5000.00, null, 10);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (9999, '张三', 'MANAGER', null, TO_DATE('2021-08-05 09:56:51', 'YYYY-MM-DD HH24:MI:SS'), 5000.00, 500.00, 10);
INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7999, 'ADMIN', 'MANAGER', 7566, TO_DATE('2021-08-04 15:16:59', 'YYYY-MM-DD HH24:MI:SS'), 2500.00, null, null);

在这里插入图片描述

3、基础语法

declare
  -- 程序的声明部分：变量、常量、游标
begin
  -- 程序主体语句
exception
  -- 异常处理语句
end;

3.1、基本输出语句

dbms_output.put_line(...);

begin
  dbms_output.put_line('Hello World');
end;

在这里插入图片描述

3.2、变量

3.2.1、常量

变量名数据类型 := 值

declare
  var number := 1;
begin
  dbms_output.put_line(var);
end;

在这里插入图片描述

3.2.1、引用类型变量

变量名表名.列名%type;，可以用来保存一个从数据库中查出来的结果（通过into关键字select * into 变量名 from 表名 ...）

declare
  name emp.ename%type;
begin
  select ename into name from emp where empno=7369;
  dbms_output.put_line(name);
end;

在这里插入图片描述

3.2.2、记录类型变量

变量名表名%rowtype;，可以用来保存一条查询记录（通过into关键字select * into 变量名 from 表名 ...）

declare
  employee emp%rowtype;
begin
  select * into employee from emp where empno=7369;
  dbms_output.put_line('员工编号：' || employee.empno || ' 员工姓名：' || employee.ename || ' 员工工资：' || employee.sal);
end;

在这里插入图片描述

3.3、分支语句

3.3.1、语法一：`if 条件 then 语句; end if;`

例如：判断成绩合格：

declare
  score number := &score;
begin
  if score > 60 then dbms_output.put_line('成绩合格'); end if;
end;

3.3.2、语法二：`if 条件 then 语句; else 语句; end if;`

例如：判断成绩是否合格：

declare
  score number := &score;
begin
  if score>60 then dbms_output.put_line('成绩合格');
  else dbms_output.put_line('成绩不合格');
  end if;
end;

3.3.3、语法三：`if 条件 then 语句; elsif 条件 then 语句; ... else 语句; end if;`

例如：细分成绩等级：

declare
  score number := &score;
begin
  if score<60 then dbms_output.put_line('成绩不及格');
  elsif score>=60 and score<70 then dbms_output.put_line('成绩及格');
  elsif score>=70 and score<80 then dbms_output.put_line('成绩合格');
  elsif score>=80 and score<90 then dbms_output.put_line('成绩良好');
  else dbms_output.put_line('成绩优秀');
  end if;
end;

3.4、循环语句

3.4.1、语法一：`while 条件 loop 语句; end loop;`

例如：打印1~10：

declare
  step number := 1;
begin
  while step<=10 loop dbms_output.put_line(step);step:=step+1; end loop;
end;

3.4.2、语法二：`loop (语句;) exit [when 终止条件;] (语句;)end loop;`

例如：打印10~20：

declare
  step number := 11;
begin 
  loop exit when step>20;dbms_output.put_line(step);step:=step+1; end loop; 
end;
-----------或-----------
declare
  step number := 11;
begin 
  loop dbms_output.put_line(step);step:=step+1;exit when step>20; end loop; 
end;

3.4.3、语法三：`for 起始值 in 范围（起始值..结束值）loop 语句; end loop;`

例如：打印21~30：

begin 
  for step in 21..30 loop dbms_output.put_line(step); end loop;
end;

4、游标

游标可以存储返回的多条数据

语法及使用步骤：

创建（声明）游标：cursor 游标名[参数名数据类型,参数名数据类型,...] is select 语句;
打开游标：open 游标名;
迭代游标：loop fetch 游标名 into 记录类型变量; exit when 游标名%notfound; fetch 游标名 into 行变量;
关闭游标：close 游标名;

例如：遍历员工表emp：

declare
  cursor c is select * from emp;
  employee emp%rowtype;
begin
  open c;
    loop fetch c into employee;exit when c%notfound;
      dbms_output.put_line('员工编号：' || employee.empno || ' 员工姓名：' || employee.ename || ' 员工工资：' || employee.sal);
    end loop;
  close c;
end;

在这里插入图片描述

例如：给职位为“PRESIDENT”的人涨2000元工资，给职位为“MANAGER”的人涨1000元工资，给其他人涨500元工资：

declare
  cursor c is select * from emp;
  employee emp%rowtype;
  add_sal number;
begin
  open c;
    loop 
      fetch c into employee; 
      exit when c%notfound;
      if employee.job='PRESIDENT' then add_sal:=2000;
      elsif employee.job='MANAGER' then add_sal:=1000;
      else add_sal:=500;
      end if;
      update emp set sal=sal+add_sal where emp.empno=employee.empno;
    end loop;
  close c;
end;

在这里插入图片描述

例如：给部门号为“10”的员工增加500元奖金：

declare
  cursor c is select * from emp where deptno=10;
  employee emp%rowtype;
begin
  open c;
    loop 
      fetch c into employee;
      exit when c%notfound;
      update emp set comm=nvl(comm,0)+500 where empno=employee.empno;
    end loop;
  close c;
end;

在这里插入图片描述

5、存储过程

存储过程（stored procedure）是在大型数据库系统中，一组为了完成特定功能的SQL的语句集，经过编译之后存储在数据库中，用户通过指定的存储过程的名字并给出参数（如果有参数）来执行它。存储过程是数据库中的一个重要的对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

语法

create [or replace] procedure 过程名 [(参数名 in/out/inout 参数类型,参数名 in/out/inout 参数类型,...)] as
begin
  plsql程序体;
end;
----------或者----------
create [or replace] procedure 过程名 [(参数名 in/out/inout 参数类型,参数名 in/out/inout 参数类型,...)] is
begin
  plsql程序体;
end 过程名;

例如：创建一个“Hello World”存储过程：

create or replace procedure hello_world is
begin
  dbms_output.put_line('Hello World');
end hello_world;

执行“Hello World”存储过程：

begin
  hello_world;
end;

在这里插入图片描述

例如：创建存储过程，给指定的员工涨薪1000，并打印出涨薪前和涨薪后的工资：

create or replace procedure increase_salary(eno in number) is
employee emp%rowtype;
begin
  select * into employee from emp where empno = eno; 
  dbms_output.put_line(employee.ename || ' 涨薪前： ' || employee.sal);
  update emp set sal = sal + 1000 where empno = eno; 
  select * into employee from emp where empno = eno; 
  dbms_output.put_line(employee.ename || ' 涨薪后： ' || employee.sal);
end;

执行涨薪存储过程：

begin
  increase_salary(9999);
end;

在这里插入图片描述

6、存储函数

语法

create or replace function 函数名(参数名 in 数据类型,参数名 in 数据类型, ... ) return 类型 is 结果变量 数据类型;
begin 
  plsql程序体;
  return 结果变量;
end 函数名;

例子：创建存储函数，给指定的员工涨薪指定金额，并打印出涨薪前和涨薪后的工资：

create or replace function increase_salary(eno in number,add_sal in number) return varchar2 is
employee emp%rowtype;
before_ varchar2(32767);
begin
  select * into employee from emp where empno=eno;
  before_:='员工编号：' || employee.empno || ' 员工姓名：' || employee.ename || ' 涨薪前工资：' || employee.sal;
  update emp set sal=sal+add_sal where empno=eno;
  select * into employee from emp where empno=eno;
  return before_ || chr(10) || '员工编号：' || employee.empno || ' 员工姓名：' || employee.ename || ' 涨薪后工资：' || employee.sal;
end increase_salary;

调用涨薪存储函数：

begin
  dbms_output.put_line(increase_salary(9999,1000));
end;

在这里插入图片描述

7、触发器

语法：

create or replace trigger 触发器名
before | after
delete | insert | update [of 列名] on 表名
[for each row [when 条件]]
begin
  plsql语句;
end 触发器名;

例子：插入员工信息后输出成功添加的提示：

create or replace trigger after_insert_hint
after insert on emp
begin
   dbms_output.put_line('成功添加了一个新的员工信息');
end after_insert_hint;

测试触发器：

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (1000, '李四', 'MANAGER', null, sysdate, 500, null, null);

在这里插入图片描述

例子：更新员工工资时如果相差超过10000元时则报错工资相差太大：

create or replace trigger update_sal_error
begin update of sal on emp  
for each row
begin
  if :new.sal-:old.sal>10000 then
    raise_application_error(-20001,'工资更新前后相差过大，已超过10000元');
  end if;
end update_sal_error;

测试触发器：
在这里插入图片描述

例子：通过触发器将已删除的员工信息移入“回收站”：

--首先创建一个与emp表结构相同的表emp_recycle_bin充当“回收站”
create table emp_recycle_bin as select * from emp where 1=2;

在这里插入图片描述

create or replace trigger emp_recycle_bin_trigger
after delete on emp
for each row
begin
  insert to emp_recycle_bin(empno,ename,job,mgr,hiredate,sal,comm,deptno)
  values (:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
  dbms_output.put_line('删除的数据已移至emp_recycle_bin表中');
end emp_recycle_bin_trigger;

在这里插入图片描述

测试触发器：

delete from emp where empno=1000;

在这里插入图片描述

philpy_used

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
PL/SQL 基操示例

文章目录1、PL/SQL2、新建一个测试用表3、基础语法3.1、基本输出语句3.2、变量3.2.1、常量3.2.1、引用类型变量3.2.2、记录类型变量3.3、分支语句3.3.1、语法一：`if 条件 then 语句; end if;`3.3.2、语法二：`if 条件 then 语句; else 语句; end if;`3.3.3、语法三：`if 条件 then 语句; elsif 条件 then 语句; ... else 语句; end if;`3.4、循环语句3.4.1、语法一：`while 条件 lo
复制链接

扫一扫