PLSQL

最新推荐文章于 2024-06-12 19:31:55 发布
最新推荐文章于 2024-06-12 19:31:55 发布
阅读量207 收藏 2
点赞数
文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45364220/article/details/132715897
版权

文章目录

命令行窗口运行plsql之前需要运行
SET SERVEROUTPUT ON 才会将 dbms_output.put_line 的打印结果打印出
在这里插入图片描述

在这里插入图片描述

基本pl/sql语法

申明变量

DECLARE
  --申明变量	%TYPE：获得对应字段的类型
  v_sal       employees.employee_id%TYPE;
  v_email     VARCHAR2(256);
  v_hire_date DATE;
BEGIN
  SELECT salary, email, hire_date
    INTO v_sal, v_email, v_hire_date
    FROM employees
   WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE('v_sal: ' || v_sal || ', ' || 'v_email: ' ||
                       v_email || ', ' || 'v_hire_date: ' ||
                       TO_CHAR(v_hire_date, 'YYYY-MM-DD'));
END;

申明记录

DECLARE
  --申明一个记录类型
  TYPE emp_record_type IS RECORD(
    v_sal       employees.employee_id%TYPE,
    v_email     VARCHAR2(256),
    v_hire_date DATE);
  emp_record emp_record_type;
BEGIN
  SELECT salary, email, hire_date
    INTO emp_record.v_sal, emp_record.v_email, emp_record.v_hire_date
    FROM employees
   WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE(emp_record.v_sal || ', ' || emp_record.v_email || ', ' ||
                       emp_record.v_hire_date);
END;

DECLARE
  --%ROWTYPE：使记录和表结构保持一致；&：运行时会在窗口中输入g_emp_id的值再赋给v_emp_id
  v_emp_id NUMBER := &g_emp_id;
  v_emp_record employees%ROWTYPE;
BEGIN
  SELECT * INTO v_emp_record FROM employees WHERE employee_id = v_emp_id;
  DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || ', ' || v_emp_record.salary || ', ' || v_emp_record.email);
END;

流程控制

条件判断（两种）

IF ... THEN ...
ELSIF ... THEN ...
ELSE
  ...
END IF;


CASE
  WHEN ... THEN ...
  WHEN ... THEN ...
END;

/*
查询出 150号 员工的工资,
若其工资大于或等于 10000 则打印 'salary >= 10000'
若在 5000 到 10000 之间, 则打印 '5000<= salary < 10000'
否则打印 'salary < 5000'
*/
DECLARE
  v_emp_sal employees.salary%TYPE;
  v_msg VARCHAR2(256);
BEGIN
  SELECT salary INTO v_emp_sal FROM employees WHERE employee_id = 100;
  --方法一
  IF v_emp_sal < 5000 THEN
    DBMS_OUTPUT.PUT_LINE('salary < 5000');
  ELSIF v_emp_sal >= 5000 AND v_emp_sal < 10000 THEN
    DBMS_OUTPUT.PUT_LINE('5000<= salary < 10000');
  ELSE
    DBMS_OUTPUT.PUT_LINE('salary >= 10000');
  END IF;
  --方法二
  v_msg := CASE
              WHEN v_emp_sal < 5000 THEN 'salary < 5000'
              WHEN v_emp_sal >= 5000 AND v_emp_sal < 10000 THEN '5000<= salary < 10000'
              ELSE 'salary >= 10000'
            END;
  DBMS_OUTPUT.PUT_LINE(v_msg);
END;

循环结构（三种）

LOOP ...
EXIT WHEN ...
END LOOP;

WHILE ... LOOP
END LOOP;

FOR i IN ... LOOP
END LOOP;

--循环方式打印1-100
DECLARE
  v_count NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_count);
  EXIT WHEN(v_count >= 100);
  v_count := v_count + 1;
  END LOOP;
  
  v_count := 1;
  WHILE v_count <= 100 LOOP
    DBMS_OUTPUT.PUT_LINE(v_count);
    v_count := v_count + 1;
  END LOOP;
  
  FOR i IN 1..100 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;

goto，exit关键字

--打印1-100，遇到50就结束，并打印结束
--经量少用 goto
BEGIN
  FOR i IN 1..100 LOOP
    IF i = 50 THEN
      GOTO label;
    END IF;
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
  <<label>>
  DBMS_OUTPUT.PUT_LINE('结束');
END;
--推荐这种
BEGIN
  FOR i IN 1..100 LOOP
    IF i = 50 THEN
      DBMS_OUTPUT.PUT_LINE('结束');
      EXIT;
    END IF;
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;

游标的使用

游标属性
%FOUND布尔类型，如果游标最近一次数据读取成功则返回TRUE
%NOTFOUND尔类型，与%FOUND相反
%ISOPEN尔类型，如果游标是打开状态，则返回TRUE
%ROWCOUNT数字类型，返回记录数
--打印80号部门的员工工资
DECLARE
  --申明变量
  v_sal employees.salary%TYPE;
  v_id employees.employee_id%TYPE;
  --定义游标
  CURSOR emp_cursor IS SELECT salary, employee_id FROM employees WHERE department_id = 80;
BEGIN
  --打开游标
  OPEN emp_cursor;
  --提取数据
  FETCH emp_cursor INTO v_sal, v_id;
  WHILE emp_cursor%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('id: ' || v_id || ', salary: ' || v_sal);
    FETCH emp_cursor INTO v_sal, v_id;
  END LOOP;
  --关闭游标
  CLOSE emp_cursor;
END;

DECLARE
  --申明一个记录
  TYPE emp_record_type IS RECORD(
       v_sal employees.salary%TYPE,
       v_id employees.employee_id%TYPE
  );
  --申明一个记录类型的变量
  emp_record emp_record_type;
  --定义游标
  CURSOR emp_cursor IS SELECT salary, employee_id FROM employees WHERE department_id = 80;
BEGIN
  --打开游标
  OPEN emp_cursor;
  --提取数据
  FETCH emp_cursor INTO emp_record;
  WHILE emp_cursor%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('id: ' || emp_record.v_id || ', salary: ' || emp_record.v_sal);
    FETCH emp_cursor INTO emp_record;
  END LOOP;
  --关闭游标
  CLOSE emp_cursor;
END;

WHILE循环读取游标数据会有很多步骤，FOR循环可以简化

--打印80号部门的员工工资
DECLARE
  --申明一个游标
  CURSOR emp_cur IS SELECT employee_id, salary FROM employees WHERE department_id = 80;
BEGIN
  FOR c IN emp_cur LOOP
    DBMS_OUTPUT.PUT_LINE('employee_id: ' || c.employee_id || ', sal: ' || c.salary);
  END LOOP;
END;

示例题1

/*
利用游标, 调整公司中员工的工资: 
    
    工资范围       调整基数
    0 - 5000       5%
    5000 - 10000   3%
    10000 - 15000  2%
    15000 -        1%
*/
DECLARE
  --申明变量
  v_empid   employees.employee_id%TYPE;
  v_empsal  employees.salary%TYPE;
  v_factory NUMBER(3, 2);
  --申明游标
  CURSOR emp_sal_cur IS
    SELECT employee_id, salary FROM employees;
BEGIN
  /*--打开游标
  OPEN emp_sal_cur;
  --提取数据
  FETCH emp_sal_cur INTO v_empid, v_empsal;
  WHILE emp_sal_cur%FOUND LOOP
    IF v_empsal >= 0 AND v_empsal < 5000 THEN v_factory := 0.05;
    ELSIF v_empsal >= 5000 AND v_empsal < 10000 THEN v_factory := 0.03;
    ELSIF v_empsal >= 10000 AND v_empsal < 15000 THEN v_factory := 0.02;
    ELSIF v_empsal >= 15000 THEN v_factory := 0.01;
    END IF;
    UPDATE employees SET salary = salary * (1+v_factory) WHERE employee_id = v_empid;
    FETCH emp_sal_cur INTO v_empid, v_empsal;
  END LOOP;*/

  FOR c IN emp_sal_cur LOOP
    IF c.salary >= 0 AND c.salary < 5000 THEN
      v_factory := 0.05;
    ELSIF c.salary >= 5000 AND c.salary < 10000 THEN
      v_factory := 0.03;
    ELSIF c.salary >= 10000 AND c.salary < 15000 THEN
      v_factory := 0.02;
    ELSIF c.salary >= 15000 THEN
      v_factory := 0.01;
    END IF;
    UPDATE employees
       SET salary = salary * (1 + v_factory)
     WHERE employee_id = c.employee_id;
  END LOOP;
END;

带参数的游标

--处理带参数的游标
DECLARE
  v_factory NUMBER(3, 2);
  v_id      employees.employee_id%TYPE;
  v_sal     employees.salary%TYPE;
  CURSOR emp_sal_cur(emp_id NUMBER, emp_sal NUMBER) IS
    SELECT employee_id, salary
      FROM employees
     WHERE employee_id = emp_id
       AND salary > emp_sal;
BEGIN
  --while参数应该在open打开的时候添加【for循环直接添加】
  OPEN emp_sal_cur(emp_id => 102, emp_sal => 4000);
  FETCH emp_sal_cur
    INTO v_id, v_sal;
  WHILE emp_sal_cur%FOUND LOOP
    IF v_sal >= 0 AND v_sal < 5000 THEN
      v_factory := 0.05;
    ELSIF v_sal >= 5000 AND v_sal < 10000 THEN
      v_factory := 0.03;
    ELSIF v_sal >= 10000 AND v_sal < 15000 THEN
      v_factory := 0.02;
    ELSIF v_sal >= 15000 THEN
      v_factory := 0.01;
    END IF;
    UPDATE employees
       SET salary = salary * (1 + v_factory)
     WHERE employee_id = v_id;
    FETCH emp_sal_cur
      INTO v_id, v_sal;
  END LOOP;
END;

隐式游标

--隐式游标: 更新指定员工 salary(涨工资 10)，如果该员工没有找到，则打印”查无此人” 信息
BEGIN
  UPDATE employees SET salry = salary + 10 WHERE employee_id = 99999;
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('查无此人');
  END IF;
END;

异常的处理

预定义异常处理

--预定义异常处理
DECLARE
  v_sal employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_sal FROM employees WHERE employee_id > 80;
  DBMS_OUTPUT.PUT_LINE(v_sal);
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('输出行数太多');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('出现其它类型异常');
END;

非预定义异常处理

--非预定义异常处理
DECLARE
  e_delid_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_delid_exception, -2292); 
BEGIN
  DELETE FROM employees WHERE employee_id =100;
WHEN e_delid_exception THEN
  DBMS_OUTPUT.PUT_LINE('违反完整性约束');
END;

用户自定义异常

DECLARE 
  e_too_high_sal EXCEPTION;
  v_sal employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_sal FROM employees WHERE employee_id = 100;
  IF v_sal > 10000 THEN
    RAISE e_too_high_sal;
  END IF;
EXCEPTION
  WHEN e_too_high_sal THEN
    DBMS_OUTPUT.PUT_LINE('工资太高了');
END;

示例题

--通过 select ... into ... 查询某人的工资, 若没有查询到, 则输出 "未找到数据"
DECLARE
  v_sal employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_sal FROM employees WHERE employee_id = 1001;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT._PUT_LINE('未找到数据');
END;
--更新指定员工工资，如工资小于300，则加100；对 NO_DATA_FOUND 异常, TOO_MANY_ROWS 进行处理.
DECLARE
  v_sal employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_sal FROM employees WHERE employee_id = 1001;
  IF v_sal > 300 THEN
    UPDATE employees SET salary = salary + 100 WHERE employee_id = 1001;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT._PUT_LINE('未找到数据');
  WHEN TOO_MANG_ROWS THEN
    DBMS_OUTPUT._PUT_LINE('输出数据太多');
END;
--自定义异常: 更新指定员工工资，增加100；若该员工不存在则抛出用户自定义异常: no_result
DECLARE
  no_result EXCEPTION;
BEGIN
  UPDATE employees SET salary = salary + 100 WHERE employee_id = 1001;
  IF SQL%NOTFOUND THEN
    RAISE no_result;
  END IF;
EXCEPTION
  WHEN no_result THEN
    DBMS_OUTPUT._PUT_LINE('更新失败');
END;

存储过程（无返回值），存储函数（有返回值）

存储函数

--存储函数
CREATE OR REPLACE FUNCTION func_name(dpt_id NUMBER, salary NUMBER)
RETURN NUMBER
IS
--函数需要申明的变量，记录类型，CURSOR【DECLARE】
BEGIN
  --函数执行体
EXCEPTION
  --函数执行遇到的异常
END;

存储函数的使用

--函数的 helloworld: 返回一个 "helloworld" 的字符串
--创建函数
CREATE OR REPLACE FUNCTION say_world(v_word)
RETURN VARCHAR2
IS
BEGIN
  RETURN 'word is:' || v_word;
END;

--调用函数
BEGIN
  DBMS_OUTPUT.PUT_LINE(say_hello('你好, Word'));
END;

--SQL执行
SELECT say_hello('你好, Word') FROM dual;

存储函数的题目

--创建一个存储函数，打印当前的系统时间
CREATE OR REPLACE FUNCTION get_sysdate
RETURN DATE
IS
  v_date DATE;
BEGIN
  v_date := SYSDATE;
  RETURN v_date;
END;
--执行SQL
SELECT get_sysdate FROM dual;

--计算两数和
CREATE OR REPLACE FUNCTION add_param(v_x NUMBER, v_y NUMBER)
RETURN NUMBER
IS
BEGIN
  v_sum NUMBER := v_x+v_y;
  RETURN v_sum;
END;
--执行SQL
SELECT add_param(1,2) FROM dual;

--定义一个函数: 获取给定部门的工资总和, 要求:部门号定义为参数, 工资总额定义为返回值.
CREATE OR REPLACE FUNCTION get_dpt_sal_func1(dpt_id NUMBER)
RETURN NUMBER
IS
  v_sumsal NUMBER;
  CURSOR dpt_sal_cur IS SELECT SUM(salary) FROM employees WHERE department_id = dpt_id;
BEGIN
  FOR c IN dpt_sal_cur LOOP
    v_sumsal: = v_sumal + c.salary;
  END LOOP;
  RETURN v_dpt_sal;
END;

DECLARE
  v_dptid NUMBER := 80;
BEGIN
  DBMS_OUTPUT.PUT_LINE(get_dpt_sal_func2(v_dptid));
END;

/*
要求: 定义一个函数: 获取给定部门的工资总和 和 该部门的员工总数(定义为 OUT 类型的参数).
要求: 部门号定义为参数, 工资总额定义为返回值.
*/
CREATE OR REPLACE FUNCTION get_dpt_sal2(dpt_id IN NUMBER, emp_count OUT NUMBER)
RETURN NUMBER
IS
  v_empcount NUMBER := 0;
  v_dpt_sal NUMBER :=0;
  CURSOR dpt_sal_cur IS SELECT salary FROM employees WHERE department_id = dpt_id;
BEGIN
  FOR c IN dpt_sal_cur LOOP
    v_dpt_sal := c.salary + v_dpt_sal;
    v_empcount := v_empcount + 1;
  END LOOP;
  RETURN v_dpt_sal;
END;

DECLARE
  v_num NUMBER := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE(get_dpt_sal2(80, v_num));
  DBMS_OUTPUT.PUT_LINE(v_num);
END;

存储过程

--定义一个存储过程: 获取给定部门的工资总和(通过 out 参数), 要求:部门号和工资总额定义为参数
CREATE OR REPLACE PROCEDURE get_sal_proc(dpt_id IN NUMBER, OUT sumsal NUMBER)
IS
  CURSOR emp_sal_cur IS SELECT salary FROM employees WHERE department_id dpt_id;
BEGIN
  sumsal := 0;
  FOR c IN emp_sal_cur IN LOOP
    sumsal := sumsal + c.salary;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(sumsal);
END;

BEGIN
  get_sal_proc(80, 0);
END;

/*
自定义一个存储过程完成以下操作:
对给定部门(作为输入参数)的员工进行加薪操作, 若其到公司的时间在 (? , 95) 期间, 为其加薪 %5
                                                      [95 , 98)            %3
                                                      [98, ?)              %1
得到以下返回结果: 为此次加薪公司每月需要额外付出多少成本(定义一个 OUT 型的输出参数).
*/
CREATE OR REPLACE PROCEDURE add_sal_proc(dpt_id IN NUMBER, cost OUT NUMBER)
IS
  CURSOR emp_sal_cur IS SELECT employee_id, salary, hire_date FROM employees WHERE department_id = dpt_id;
  v_factory NUMBER(4,2);
BEGIN
  cost += 0;
  FOR c IN emp_sal_cur LOOP
    IF c.hire_date < TO_DATE('1995', 'YYYY') THEN
      v_factory := 0.05;
    ELSIF c.hire_date >= TO_DATE('1995', 'YYYY') AND c.hire_date < TO_DATE('1998', 'YYYY') THEN
      v_factory := 0.03;
    ELSE
      v_factory := 0.01;
    END IF;
    cost := cost + salary * v_factory;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(cost);
END;
BEGIN
  add_sal_proc(80, 0);
END;

触发器

CREATE OR REPLACE TRIGGER update_emp_trigger
  AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  dbms_output.put_line('更新前old salary: ' || :old.salary || ', 更新后new salary: ' || :new.salary);
END;

SELECT * FROM employees;
UPDATE employees SET salary = salary - 100 WHERE employee_id = 100;

在这里插入图片描述
示例题

--编写一个触发器, 在对 employees 记录进行删除的时候, 在 my_emp_bak 表中备份对应的记录
CREATE TABLE my_emp_bak AS SELECT employee_id, salary  FROM employees WHERE 1=2;
CREATE OR REPLACE TRIGGER my_emp_bak
BEFORE
DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO my_emp_bak(employee_id, salary) VALUES(:old.employee_id, :old.salary);
END;
SELECT * FROM employees;
SELECT * FROM my_emp_bak;
DELETE FROM employees;
ROLLBACK;
你去把唐僧使徒除掉
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
plsql免安装
01-07
plsql免安装，下载后解压，配置tns,配置环境变量，即可使用。
plsql11安装包文件
08-25
plsql11安装包文件
plsql12.0.4
10-26
plsql12.0.4汉化以及安装包，运行msi的时候以管理员身份运行，安装完成之后打开Chinese.exe汉化
PLSQL安装包
06-06
PLSQL客户端安装包，可以成功安装，安装PLSQL客户端就可以用
plsql注册机
01-10
plsql注册机plsqldev_keygen，最新版可用 字数补丁。aaaaa
PLSQL Developer美化包
12-01
PLSQL Developer中自带的美化十分的粗糙，排版不够简洁，并且美化后所有的字母都变成大写，不便于阅读。这个美化包，美化的规则是关键字大写，其他字母小写，并且排版好看。
PLSQL-Developer15和OracleInstantClient21以及.ora文件
07-13
1、PL/SQL Developer15安装介质。（plsqldev1504x32.msi、plsqldev1504x64.msi） 2、instantclient-basic-windows.x64-21.10.0.0.0dbru.zip 3、.ora文件
plsql 最新Version15版本，64bit
07-01
plsql Version15 版本，对imp 和exp兼容更好，而且支持客户端64位，不过现在还没找到激活码，短期用30天没问题的
PLSQL Developer
11-24
这个是PLSQL Developer的安装包，下载即可直接使用，不需要重新安装，打开里面的exe文件即可
PLSQL客户端
06-28
Oracle客户端是提供你连接Oracle服务器用的，是sqlplus，是一中命令行的界面，你安装PLSQL的话，必须要安装Oracle客户端，因为你PLSQL中的那个Database用的是安装Oracle客户端配置文件中的tnsname.ora文件的，也就是...
PLSQL Developer V9
11-24
PLSQL Developer V9
PLSQL程序优化和性能分析方法
08-15
PLSQL程序优化和性能分析方法 PLSQL程序优化是指在编写PLSQL程序时，为了提高程序的执行效率和性能所采取的一系列措施和技术。这些措施和技术可以帮助开发者编写高效、可靠、可维护的PLSQL程序，从而提高系统的整体...
Oracle数据库连接工具PLSQL轻桌面工具包
08-23
Oracle数据库连接工具PLSQL轻桌面工具包，安装PLSQL必须下载轻桌面工具包
plsqldev15.0.0.2050x32多语言版+sn
05-06
32位版本的 PLSQL 正式版。 安装请查看说明。 APRIL 8, 2022 - VERSION 15.0 RELEASED Dark Mode styles and Light Mode styles Read-Only Connections Editor enhancements A macro can now be repeated by ...
plsqldeveloper12.0.1
06-22
PLSQL Developer 12.0.1 64位 含注册码 注册码 亲测可用。
plsql中文版
10-09
plsql中文版非常好用！
Oracle连接工具plsql11x64位
03-12
Oracle连接工具plsql11x64位Oracle连接工具plsql11x64位Oracle连接工具plsql11x64位Oracle连接工具plsql11x64位Oracle连接工具plsql11x64位Oracle连接工具plsql11x64位Oracle连接工具plsql11x64位Oracle连接工具...
plsql亲测简单好用.zip
11-10
不需要繁琐的配置，按照文档傻瓜式操作就好用
Spring Security实现用户认证四：使用JWT与Redis实现无状态认证
最新发布
不做菜虚困的博客
06-12 808
在基本的通信流程中，我们一般采用Session去存储用户的认证状态。在Spring Security实现用户认证三中讲过，在拿到前端传输过来的用户名和密码之后，会有专门的过滤器处理这部分的需求，并且对认证成功的用户生成Token且存储在Session中。在下次发起请求时，直接从Session中取出同用户名的token进行密码哈希的比较要认证用户。对于无状态认证，则我们的认证不依赖与服务器端存储的Session的状态。所以无状态认证需要我们每次从前端传输一个包含完整认证信息的Token到服务器端进行自定义的认

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值