数据库小技能:PL/SQL中书写定义sql

简介: 触发器由数据库管理系统负责调用和执行,通过触发触发器所监听的事物来实现触发器的调用。表级别的触发器(对于整个数据库表做监听)行级别的触发器(对于表中的每一行做监听)

I 书写定义sql

1.1 DQL语言

  1. 必须写into关键字
  2. 查询语句只能有一条返回值
异常示例:

没有值 no_data_found;

值过多 too_many_rows

1.2 DML(insert/update/delete)

执行DML语句要处理事务

&+变量来实现动态传参
--执行DML语句
declare
begin
    --普通DML语句
    delete from emp where empno=&xx;
    --事务处理
    commit;
end;

1.3 DDL(create/drop/alter/truncate)

execute immediate('DDL')

        declare
          v_count number(3);
        begin
          select count(*) into v_count from user_tables where table_name='T_TEST';
          if v_count=1 then
          --DDL语句执行时execute immediate()
          execute immediate('drop table t_test');
          dbms_output.put_line('drop table success');
          end if;
          
          execute immediate('create table  t_test(id number(10),name varchar2(20))');
          dbms_output.put_line('create table success');
        end;

II 游标(cursor)

sql语句执行时会在内存中开辟一个区域,用来存放执行的sql语句以及返回的数据,我们把这个内存区域叫做上下文环境(context);游标就是指向这个上下文环境的指针

2.1 游标分类

  • 隐式游标:由数据库管理系统创建执行
  • 显示游标:有程序员负责创建执行和关闭的游标

2.2 游标的属性

%rowcount 存储的是游标执行时所影响的记录条数

2.3 操作属性

  • 显示游标:自定义游标名称%rowcount
  • 隐式游标:sql%rowcount
  • %found 判断当前数据有没有下一条 true/fase
  • %notfound 判断当前数据有没有下一条 true/fase
  • %isopen 判断游标是否开启
            declare
                   v_count binary_integer;
            begin
                   update emp set job='baobiao' where empno=1111;
                   --操作隐式游标的属性获得影响的记录数
                   v_count := sql%rowcount;
                   commit;
                   
                   dbms_output.put_line(v_count||' rows updated');
            end;

2.4 显示游标

  1. 创建游标

cursor 游标名称 is 查询语句

  1. 开启游标

open 游标名称

  1. 获取数据

fetch 游标名称 into 变量

  1. 关闭游标

close 游标名称

            declare
                   v_name varchar2(20);
                   v_job varchar2(20);
                   --定义游标
                   cursor my_cursor is select ename,job from emp;
            begin
                   --开启游标,执行sql语句,将结果存储在指定内存区域
                   open my_cursor;
                   
                   loop
                   --获取数据,给变量赋值
                   fetch my_cursor into  v_name,v_job;
                   dbms_output.put_line(v_name||'-----'||v_job);
                   
                   exit when my_cursor%notfound;
                   
                   end loop;
                   --关闭游标
                   close my_cursor;
            end;

循环游标

            --for循环
            declare
                   cursor my_cursor is select * from emp;
            begin
                   for v_c in my_cursor loop
                dbms_output.put_line(v_c.ename||'---'||v_c.job);
                   end loop;
            end;

定义游标时传递参数

            --定义含有参数的游标
            declare
                   v_emp_record emp%rowtype;
                   --定义含有参数的游标
                   cursor my_cursor(p_id number) is select * from emp where empno=p_id;
            begin
                   --通过动态方式传递参数
                   open my_cursor(&no);
                   loop
                   fetch my_cursor into v_emp_record;
                   exit when my_cursor%notfound;
                   dbms_output.put_line(v_emp_record.ename||v_emp_record.empno);
                   end loop;
                   close  my_cursor;
            end;

注意:

  1. 设置形参时不要写参数的长度,实参是在开启游标的时候传递。
  2. 游标不能重复开启和关闭

2.5 定义游标类型的变量

  1. 定义游标类型: type 游标类型名称 is ref cursor return 返回结果类型
  2. 定义游标类型的变量: 变量名称 游标类型名称
declare
                   --定义游标类型
                   type cursor_type is  ref cursor return emp%rowtype;
                   --定义游标类型变量
                   my_cursor cursor_type;
                   my_record emp%rowtype;
            begin
                   --在开启游标的时候动态绑定sql
                   open my_cursor for select * from emp;
                   loop
                   --循环游标变量,把结果存放在记录类型的变量中
                   fetch my_cursor into my_record;
                   exit when my_cursor%notfound;
                   dbms_output.put_line(my_record.ename||my_record.empno);
                   end loop;
            end;

III 过程和函数

https://blog.csdn.net/z929118967/article/details/128313118

IV 触发器 (trigger)

类似java中的Listener
触发器由数据库管理系统负责调用和执行,通过触发触发器所监听的事物来实现触发器的调用。

  • 表级别的触发器(对于整个数据库表做监听)
  • 行级别的触发器(对于表中的每一行做监听)
/*表级触发器*/
create or replace trigger table_emp_trigger
before  delete or update or insert on emp 
begin
       if to_char(sysdate,'dy') in('星期二','星期三')then
          
          dbms_standard.raise_application_error(-20000,'u cant modify this table');
       end if;
 
end;
 
 
/*行级的触发器*/
create or replace trigger update_sal_emp_trigger
before update on emp for each row
begin
       --:old.字段  获取更新之前的旧值
       --:呢w.字段  获取更新之后的新值
       if :old.job not in('MANAGER','PRESIDENT') and :new.sal>10000 then
          dbms_standard.raise_application_error(-20001,'u have too many money');
       end if;
end;
 
 
/*同步my_emp和emp表的插入*/
 
 
 
create or replace trigger insert_emp_trigger
after insert on emp for each row
begin
       --:old.字段  获取更新之前的旧值
       --:new.字段  获取更新之后的新值
       insert into my_emp(empno) values(:new.empno);
         
end;
 
update emp set sal=10001 where empno=7389;
select * from my_emp;
drop trigger table_emp_trigger;
insert into emp(empno,ename) values(1119,'xx');
commit;

4.1 触发器的执行顺序

有表级别的触发器,行级别的触发器作用于同一个表

  1. before表级别触发器
  2. before 行级别触发器
  3. after 行级别触发器
  4. after 表级别触发器

4.2 系统触发器

DBA用来调试系统on database

注:触发器不带参数,没有放回值,不作事务处理。

V 预备知识

5.1 使用SQL*PLUS登陆远程数据库的配置

  • 手工编辑:$ORACLE_HOME/network/admin/tnsnames.ora文件
  • 工具编辑: 通过Net Configuration AssistantNet Manager进行本地网络服务名配置
DEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 12.11.11.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ios逆向11on11)
    )
  )

5.2 建立数据库

CREATE DATABASE database;

5.3 建立顺序号

CREATE SEQUENCE CINOSEQ MINVALUE 1 MAXVALUE 4000000000 START WITH 41 INCREMENT BY 1 NOCYCLE;

5.4 创建索引

CREATE [ UNIQUE ] INDEX index
ON table "("
     column [ ASC | DESC]
  [, column [ ASC | DESC]]... ")" ;

5.5 修改表

  • 在表的后面增加一个字段
ALTER TABLE 表名 ADD 字段名 字段名描述   [ DEFAULT expr ][ NOT NULL ][ ,字段名2 ……];
  • 修改表里字段的定义描述
ALTER TABLE 表名 MODIFY 字段名1 字段名1描述  [ DEFAULT expr ][ NOT NULL ][ ,字段名2 ……];
  • 删除表里的某个字段
ALTER TABLE 表名 DROP 字段名;
  • 给表里的字段加上/禁止/启用约束条件
ALTER TABLE 表名 ADD | DISABLE | ENABLE CONSTRAINT 约束名 PRIMARY KEY (字段名1[,字段名2 ……]);
ALTER TABLE 表名 ADD | DISABLE | ENABLE CONSTRAINT 约束名 UNIQUE (字段名1[,字段名2 ……]);
  • 删除表里的约束条件
ALTER TABLE 表名 DROP CONSTRAINTS 约束名 [CASCADE];
--会把约束相关的索引一起删除,CASCADE能同时删去外键的约束条件。
  • 删除索引
DROP INDEX 索引名;
  • 删除顺序号
DROP SEQUENCE 顺序名;
  • 删除数据库表
DROP TABLE 表名 [{CASCADE | CASCADE CONSTRAINTS | RESTRICT}] ;

5.6 查询

https://blog.csdn.net/z929118967/article/details/128456644

5.7 插入数据 insert

使用INSERT语句一次只能插入一行数据。

INSERT INTO { table | view } ["("column [, column]...")"]{ VALUES "(" expression[, expression]...")" | subquery };

5.8 update

在修改表中数据时,不能破环表的完整性约束。如果修改的数据与完整性约束有冲突,那么这种修改操作不能成功。

UPDATE { table | view } [ alias ] SET column = { expression | subquery } [, column = { expr | subquery }]...[WHERE condition] ;

5.9 delete

就像修改数据一样,删除数据时也不能破坏数据库的完整性约束。

DELETE FROM { table | view }[WHERE condition] ;

5.10 事务

https://blog.csdn.net/z929118967/article/details/128387690

事务是一个逻辑上的单元。要么全部成功,要么全部失败。
在下面的情况下系统自动地结束一个事务:

  • COMMIT或ROLLBACK命令;
  • DDL,如CREATE TABLE语句;
  • 系统失败。

    • 退出SQL*PLUS;

COMMIT [WORK] ;
ROLLBACK [{ WORK | TO savepoint_name }] ;
SAVEPOINT savepoint_name ;
REMOVE SAVEPOINT <savepoint_name>;

5.11 数据导入和导出

  1. 导出
--将数据库TEST完全导出,用户名system 密码manager 导出到`daochu.dmp`中
exp system/manager@TEST file=d:daochu.dmp full=y
--将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= datanewsmgnt.dmp tables= (inner_notify,notify_staff_relat)
  1. 导入
--将`daochu.dmp` 中的数据导入 TEST数据库中
imp system/manager@TEST file=daochu.dmp
--将daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:daochu.dmp tables=(table1)
目录
相关文章
|
16天前
|
SQL 人工智能 Linux
SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
172 5
SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
|
15天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
15天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
1月前
|
SQL Oracle 关系型数据库
Oracle数据库创建表空间和索引的SQL语法示例
以上SQL语法提供了一种标准方式去组织Oracle数据库内部结构,并且通过合理使用可以显著改善查询速度及整体性能。需要注意,在实际应用过程当中应该根据具体业务需求、系统资源状况以及预期目标去合理规划并调整参数设置以达到最佳效果。
111 8
|
21天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
21天前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
57 3
|
27天前
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。
|
2月前
|
存储 运维 关系型数据库
从MySQL到云数据库,数据库迁移真的有必要吗?
本文探讨了企业在业务增长背景下,是否应从 MySQL 迁移至云数据库的决策问题。分析了 MySQL 的优势与瓶颈,对比了云数据库在存储计算分离、自动化运维、多负载支持等方面的优势,并提出判断迁移必要性的五个关键问题及实施路径,帮助企业理性决策并落地迁移方案。
|
14天前
|
关系型数据库 MySQL 分布式数据库
阿里云PolarDB云原生数据库收费价格:MySQL和PostgreSQL详细介绍
阿里云PolarDB兼容MySQL、PostgreSQL及Oracle语法,支持集中式与分布式架构。标准版2核4G年费1116元起,企业版最高性能达4核16G,支持HTAP与多级高可用,广泛应用于金融、政务、互联网等领域,TCO成本降低50%。
|
15天前
|
SQL 关系型数据库 MySQL
Mysql数据恢复—Mysql数据库delete删除后数据恢复案例
本地服务器,操作系统为windows server。服务器上部署mysql单实例,innodb引擎,独立表空间。未进行数据库备份,未开启binlog。 人为误操作使用Delete命令删除数据时未添加where子句,导致全表数据被删除。删除后未对该表进行任何操作。需要恢复误删除的数据。 在本案例中的mysql数据库未进行备份,也未开启binlog日志,无法直接还原数据库。

热门文章

最新文章