pl/sql 以及oracle存储过程简介

已于 2022-10-09 17:56:37 修改
阅读量2.4k 收藏 11
点赞数 1
文章标签： oracle sql 数据库
于 2022-10-09 15:24:41 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_42315935/article/details/127227541
版权

oracle存储过程简介(&pl/sql)

1.什么是PL/SQL?

PL/SQL : Procedure Language/SQL

PLSQL 是对 sql语言的过程化扩展，指在 SQL命令语言中添加了过程处理语句（如：分支、循环），使sql语言具有过程处理能力。把SQL语言的数据库操纵能力和过程语言的数据处理能力结合起来，使得SQL面向过程但比过程语言简单、高效、灵活和使用。

1.1 pl/sql语法

主要分为三个部分：声明部分、可执行部分、异常处理部分 (不区分大小写)

DECLARE
  -- 声明变量、游标。
  I INTEGER;
BEGIN
  -- 执行语句

  --[异常处理]
exception
END;

其中 DECLARE部分用来声明变量或游标（结果集类型变量），如果程序中无变量声明可以省略掉

示例：

BEGIN

  --打印hello world

  DBMS_OUTPUT.PUT_LINE('hello world');

END;

其中DBMS_OUTPUT 为oracle内置程序包，相当于Java中的System.out,而PUT_LINE（）是调用的方法,相当于println（）方法

环境准备：

scott 是给初学者学习的用户，学习者可以用 scott 登录系统， scott 用户登录后，就可以使用 Oracle 提供的数据库和数据表，这些都是 Oracle 提供的，学习者不需要自己创建数据库和数据表，直接使用这些数据库和数据表练习 SQL 。

或者从Scott中将emp表创建过来，方便下方示例练习：

create table emp as select * from scott.emp
1.2 常量和变量定义
1.2.1 基本变量
变量名  变量类型（变量长度）  例如： v_name  varchar2(20);

变量的基本类型就是 oracle 中的建表时字段的变量如 char, varchar2, date, number, boolean, long

变量赋值的方式有两种：

  1. 直接赋值语句 := 比如: v_name := ‘zhangsan’
  2. 语句赋值，使用select …into … 赋值：（语法 select 值 into 变量）

示例：

DECLARE
-- 名称 
v_name VARCHAR ( 20 ) := 'xy';
--薪水
v_SAL NUMBER;
v_addr VARCHAR ( 200 );
BEGIN--在程序中直接赋值
	V_SAL := 1111;
	SELECT
		'测试地址' INTO v_addr 
	FROM
		dual;
--打印变量
DBMS_OUTPUT.PUT_LINE ( '姓名：' || V_NAME || ',薪水：' || V_SAL || ',地址：' || V_ADDR );
END;
1.2.2 引用变量

通过表名.列名%TYPE指定变量的类型和长度，例如： v_name emp.ename%TYPE;

变量的类型和长度取决于表中字段的类型和长度

-- 查询emp表中7839号员工的个人信息，打印姓名和薪水
DECLARE
  -- 姓名
  V_NAME TBLUSER.USERNAME%TYPE; -- 声明变量直接赋值

BEGIN
  --查询表中的姓名和薪水并赋值给变量
  --注意查询的字段和赋值的变量的顺序、个数、类型要一致
  SELECT ENAME, SAL INTO V_NAME, V_SAL FROM EMP WHERE EMPNO = 7839;

  --打印变量
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ',薪水：' || V_SAL);

END;

引用型变量的好处：

使用普通变量定义方式，需要知道表中列的类型，而使用引用类型，不需要考虑列的类型，使用%TYPE是非常好的编程风格，因为它使得PL/SQL更加灵活，更加适应于对数据库定义的更新。

1.2.3 记录型变量

接收一整行记录，相当于一个对象

语法： 变量名称   表名%ROWTYPE， 例如： v_emp emp%rowtype;

【示例】查询emp表中7839号员工的个人信息，打印姓名和薪水

-- 查询emp表中7839号员工的个人信息，打印姓名和薪水
DECLARE
  -- 姓名
  V_NAME EMP.ENAME%TYPE; -- 声明变量直接赋值
  --薪水
  V_SAL EMP.SAL%TYPE;

BEGIN
  --查询表中的姓名和薪水并赋值给变量
  --注意查询的字段和赋值的变量的顺序、个数、类型要一致
  SELECT ENAME, SAL INTO V_NAME, V_SAL FROM EMP WHERE EMPNO = 7839;

  --打印变量
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ',薪水：' || V_SAL);

END;
1.2.4 常量定义
变量名  constant 变量类型（变量长度）  例如： v_name constant varchar2(20):='xy';
1.3 流程控制
1.3.1 流程分支

语法：

BEGIN

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

【示例】判断emp表中记录是否超过20条，10-20之间，或者10条以下：

DECLARE
  --声明变量接受emp表中的记录数
  V_COUNT NUMBER;

BEGIN

  --查询emp表中的记录数赋值给变量

  SELECT COUNT(1) INTO V_COUNT FROM EMP;

  --判断打印

  IF V_COUNT > 20 THEN
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数超过了20条为：' || V_COUNT || '条。');

  ELSIF V_COUNT >= 10 THEN
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10~20条之间为：' || V_COUNT || '条。');

  ELSE
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10条以下为：' || V_COUNT || '条。');

  END IF;

END;
1.3.2 LOOP循环

  1. 语法1

    WHILE total <=2300 LOOP
total :+=total+1;
END LOOP;

    示例：

    DECLARE
	step number :=1;
begin
	while step <= 10 Loop
	DBMS_OUTPUT.PUT_LINE(step);
	step := step +1;
	end loop;
end;

  2. 语法2

    Loop 
EXIT [when 条件]; --退出循环的条件
End Loop;

    示例

    declare
step number :=1 ;
begin
	loop
	exit when step > 10;
	DBMS_OUTPUT.PUT_LINE(step);
	step := step +1;
	end loop;
end;

  3. 语法3

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

    示例

    declare
step number :=1;
begin
    for step in 1..10 loop
    DBMS_OUTPUT.PUT_LINE(step);
    end loop;
end;
1.4 游标 cursor

pl/sql 中多条记录使用游标临时存储，类似于java 中jdbc返回的ResultSet集合，通过遍历游标，可以逐行访问处理该结果集的数据。

游标使用方式： 声明—>打开—>读取—>关闭

语法：

CURSOR 游标名 [(参数名 数据类型,参数名 数据类型 ...)] IS SELECT 语句
-- 例如：
cursor cl is select ename from emp;
游标使用步骤：

1. 打开游标：`open cl;` (打开游标执行查询)
2. 取一行的游标的值： `fetch cl into pjob;` (取一行到变量中)
3. 关闭游标：`close cl;` (关闭游标释放资源)
4. 游标的结束方式：exit when c1%notfound 
5. 注意： 上面的 pjob 必须与 emp 表中的 job 列类型一致：
	定义：pjob emp.empjob%type;
1.4.1 游标的属性
游标的属性返回值类型说明
%ROWCOUNT整型获得FETCH语句返回的数据行数
%FOUND布尔型最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND布尔型与%FOUND属性返回值相反
%ISOPEN布尔型游标已经打开时值为真，否则为假

其中 %NOTFOUND是在游标中找不到元素的时候返回TRUE,通常用来判断退出循环

1.4.2 示例
范例 1：

使用游标方式输出 emp 表中的员工编号和姓名

declare 
	cursor ps is 
		select * from emp;
	pemp emp%rowtype;
begin
	open ps;
	loop
		fetch ps into pemp;
		exit when ps%notfound;
		dbms_output.put_line(pemp.empno || ' ' || pemp.ename);
	end loop;
	close ps;
end;
范例 2：

按员工的工种涨工资,总裁 1000 元，经理涨 800 元其，他人员涨 400 元。

-- 备份出一张新表为 myemp;
create table myemp as select * from emp; 
declare cursor pc is select * from myemp;
declare
 cursor pc is
 select * from myemp;
 addsal myemp.sal%type; 
 pemp myemp%rowtype; 
begin 
	open pc; 
	loop fetch pc into pemp; 
	exit when pc%notfound; 
	if pemp.job = 'PRESIDENT' then addsal := 1000; 
	elsif pemp.job = 'MANAGER' then addsal := 800; 
	else addsal := 400; 
    end if; 
    update myemp t set t.sal = t.sal + addsal where t.empno =  pemp.empno; 
    end loop; 
    close pc; 
end;
范例 3：(带参数的游标)

写一段 PL/SQL 程序，为部门号为 10 的员工涨工资。

declare 
	cursor pc(dno myemp.deptno%type) is 
		select empno from myemp where deptno = dno; 
	pno myemp.empno%type; 
begin 
	open pc(20); 
	loop fetch pc into pno; 
	exit when pc%notfound; 
	update myemp t set t.sal = t.sal + 1000 where t.empno = pno; 
	end loop; 
	close pc; 
end;

2. 存储过程

2.1 什么是存储过程

pl/sql 可以进行表的操作，判断，循环逻辑处理，但是无法重复调用。

可以理解之前的代码全都编写在了main方法中,是匿名程序. JAVA可以通过封装对象和方法来解决复用问题

PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用,这些被存储起来的PLSQL程序称之为存储过程

存储过程作用：

  1. 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭是很耗费资源), 需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率.

  2. ORACLE官方给的建议：能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可能会存在错误.(如果在数据库中操作数据,可以有一定的日志恢复等功能.)

存储过程缺点：

  1. 开发调试不方便
  2. 可移植性差，存储过程绑定在数据库上，不同类型的数据库数据库存储过程语法各方面有所区别
  3. 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
  4. 如果一个项目中使用大量存储过程，随着用户需求的变动，导致数据结构的变化，这时候想要去维护该系统是非常麻烦的，代价比不使用存储过程大得多

2.2 存储过程语法

CREATE OR REPLACE PROCEDURE 过程名称[(参数列表)] IS
BEGIN

END [过程名称];

参数：可不带参数，输入参数和输出参数

2.2.1 无参

示例：创建存储过程打印helloworld

create or replace procedure HelloWorld is
begin
  DBMS_OUTPUT.PUT_LINE('hello world');
end;
-- 调用
BEGIN
HelloWorld;
END HelloWorld;
2.2.2 输入参数

示例：打印指定员工号的信息

create or replace procedure print_emp_info(I_EMPNO in EMP.EMPNO%type) is
 --声明变量接受查询结果
  V_ENAME EMP.ENAME%TYPE;
  V_SAL   EMP.SAL%TYPE;
begin
  select ename ,sal into V_ENAME, V_SAL from emp where empno=I_EMPNO;
  --打印结果
  DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
END print_emp_info;

-- 调用
BEGIN
print_emp_info(7839);
END;
2.2.3 输出参数

示例：返回指定员工号的员工的薪水

create or replace procedure out_emp_sal (I_EMPNO in EMP.EMPNO%type,EMPSAL out EMP.SAL%type) is
 --声明变量接受查询结果
  V_ENAME EMP.ENAME%TYPE;
begin
  select ename ,sal into V_ENAME, EMPSAL from emp where empno=I_EMPNO;
  --打印结果
  DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || EMPSAL);
END out_emp_sal;

-- 调用
declare
V_SAL EMP.SAL%TYPE;
BEGIN
out_emp_sal(7839,V_SAL);
DBMS_OUTPUT.PUT_LINE(V_SAL);
END;

注意:调用的时候,参数要与定义的参数的顺序和类型一致.

3.存储函数

3.1 语法

create or replace function 函数名(name in type,name in type,...) return 数据类型 is
var 数据类型;
begin
	return var;
end 函数名;

3.2存储过程和函数的区别

  1. 存储过程和函数区别在于函数可以有一个返回值，存储过程没有。但是过程和函数都可以通过out指定一个或者多个输出参数。我们可以用out参数，在过程和函数中返回多个值。
  2. function定义中只能有DDL（如select等）语句；procedure中主要是DML语句（对数据库进行复杂操作时,如对多个表进行Update、Insert、Query、Delete时）。
  3. 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
  4. 函数可结合各语句使用，存储过程则不行

3.3 存储函数示例

使用存储函数来查询指定员工的年薪

create or replace function empincome(eno in emp.empno%type) return
number is
 psal emp.sal%type;
 pcomm emp.comm%type;
begin
 select t.sal into psal from emp t where t.empno = eno;
 return psal * 12 + nvl(pcomm, 0);
end;

4.触发器

4.1 简介

数据库触发器是一个与表关联的、存储的 pl/sql 程序。每当一个特定的数据操作语句（insert、delete、update）在指定的表上发出时，oracle自动执行触发器中的语句序列。

触发器可用于 ：

  • 数据确认
  • 实施复杂的安全性检查
  • 做审计，跟踪表上所做的数据操作等
  • 数据的备份和同步

4.2 触发器类型：

4.2.1 语句触发器

在指定语句操作之前或者之后执行一次，不管这条语句影响了多少行的数据。

4.2.2 行级触发器（FOR EACH ROW）

触发语句作用的每一条记录，在行级触发器中使用old和new伪记录变量，识别值的状态。

4.3 触发器语法

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

示例：插入员工成功之后打印一句话 ‘新增员工成功’

create or replace trigger testTrigger
 after insert on person 
declare
 -- local variables here
begin
 dbms_output.put_line('一个员工被插入');
end testTrigger;
-- 执行insert语句

INSERT INTO "MES"."EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") VALUES ('7777', 'MILLER', 'CLERK', '7782', TO_DATE('1982-01-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '1300', NULL, '10');
触发语句:old:new
Insert所有字段都是空(null)将要插入的数据
update更新以前该行的值更新后的值
delete删除以前该行的值所有字段都是空(null)

示例：判断员工涨工资之后的工资的值一定要大于涨工资之前的工资

create or replace trigger addsal4p 
	before update of sal on emp for each row 
	begin 
	if :old.sal >= :new.sal then raise_application_error(-20002, '涨前的工资不能大于涨后的工资'); 
	end if; 
end;

-- 调用
update emp t set t.sal = t.sal - 1;
没有目的飞行
关注
  • 1
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql与oracle存储过程_mysql与oracle存储过程对比
weixin_30015521的博客
02-07 1339
一、创建存储过程语句不同mysqlDROP PROCEDURE IF EXISTS `SD_USER_P_ADD_USR`;create procedure P_ADD_FAC(id_fac_cd varchar(100))oraclecreate or replace procedure P_ADD_FAC(id_fac_cd IN ES_FAC_UNIT.FAC_CD%TYPE) is注意...
Oracle/Mysql/SqlServer 存储过程
qq_41451794的博客
10-21 360
Oracle、MySQL、SqlServer存储过程基本语法
【每日一练：存储过程】打印变量
debimeng
03-06 5058
oracle存储过程之简单例子：打印变量值 create or replace procedure 存储过程名 is --声明区域 begin --逻辑代码区域 end 存储过程名; 栗子： create or replace procedure test01 --固定模式，此为创建不带参数的存储过程 is v_age integer; ...
PlSql存储过程基础
qq_31856061的博客
05-11 6000
PLSQL全称，是Oracle对SQL的一种过程化拓展，在SQL命令语言中增加了过程处理语句（如分支，循环等），是SQL语言具有过程处理的能力。把复杂的业务逻辑放到数据库中处理，能够减少数据库和服务之间的网络交互，提高执行效率。PLSQL是一种不区分大小写的语言（大小写不敏感）用于临时存储一个查询返回的多行数据（结果集，类似于Java的Jdbc连接返回的ResultSet集合），通过遍历游标，可以逐行访问处理该结果集的数据。游标的使用方式：声明 一> 打开 一> 读取 一> 关闭。
什么是 PL/SQL
最新发布
Hello World
05-13 381
10. **易于集成**：PL/SQL 可以很容易地与 Oracle 数据库集成，也可以通过 Oracle 的开放接口与外部应用程序集成。2. **触发器**：可以定义触发器来自动执行 PL/SQL 代码，响应数据库内的操作，如插入、更新或删除数据。7. **记录类型**：可以使用记录（Record）来表示数据库中的行，方便数据的存储和操作。6. **事务控制**：可以在 PL/SQL 块中控制事务，确保数据的一致性和完整性。8. **集合操作**：支持对数据库中的数据集合进行批量操作。
oracle 打印输出(触发器、存储过程)
weixin_44084713的博客
09-12 9210
******20210911 dbms_output.put_line(‘开始时间’||TO_CHAR(startDATE, ‘DD-MON-YYYY HH24:MI:SS’));
PL/SQL 数据类型与基本输出
weixin_45083214的博客
07-26 2655
PL/SQL是使用sql的数据库编程语言，也是面向程序的程序语言相比于SQLsql是声明语句而PL/SQL是程序语言（有执行体、循环体、结构体）同时PL/SQL不于数据库进行交互用于编写程序、触发器、函数、包2.PLSQL数据类型1.PL/SQL数据类型变量常量生命周期--变量数据类型分类6种numbervarchar2date--剩余三种是新类型引用类型--记录型自定义型--行记录型其次是常量constant。...
Oracle存储过程
猪之仆的博客
07-04 5923
使用普通变量定义方式，需要知道表中列的类型，而使用引用类型，不需要考虑列的类型，使用%TYPE是非常好的编程风格，因为引用型变量更加灵活。
Oracle如何使用PL/SQL调试存储过程
09-08
主要介绍了Oracle如何使用PL/SQL调试存储过程，文中通过图文介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
oracle_oracle_oraclepl/sql_
10-01
1. **PL/SQL简介**：PL/SQL是Oracle数据库的内置编程语言，用于创建存储过程、函数、触发器和包。它的语法基于SQL，但增加了流程控制、异常处理和数据类型等特性。 2. **变量和数据类型**：PL/SQL支持多种数据类型...
Oracle PL/SQL实战（待续）
07-27
Oracle PL/SQL是一种强大的编程语言，它结合了SQL的数据处理能力与PL的程序设计特性，是Oracle数据库系统中用于创建存储过程、函数、触发器和包的主要工具。在这个"Oracle PL/SQL实战（待续）"的主题中，我们将深入...
Oracle实验六 PL/SQL程序设计
12-14
总的来说，实验六涵盖了Oracle数据库中使用PL/SQL进行程序设计的基本概念，包括函数的创建和调用、存储过程的编写及触发器的应用，这些都是数据库管理系统中不可或缺的部分。通过这些实践，开发者可以更好地理解和...
PLSQL--Oracle的存储过程
m0_66717003的博客
11-25 2426
PLSQL--Oraclede 存储过程 以及基本语法
ORACLE 存储过程打印SQL
lovepyang的专栏
12-11 5137
DBMS_OUTPUT.put_line(v_sql);
plsql 存储过程控制台打印不显示的问题
于德晓的博客
04-09 4825
在存储过程中使用下面语句： dbms_output.put_line(输出内容'); 在命令窗口，可以使用exec 存储过程名;调用存储过程，将输出结果打印到命令窗口。 有时，存储过程执行成功后，未看到输出的结果，原因可能是没有打开输出开关，执行一下下面语句即可 setserveroutput on; ...
Oracle存储过程打印输出错误信息、行号，快速排查
CrazyFlyingBee的博客
12-14 1016
Oracle存储过程打印输出错误信息、行号，快速排查
Oracle 实验7 存储过程
悢七的CSDN博客
10-15 1661
一、实验目的 1．掌握存储过程的创建和调用。 2．掌握存储过程的查看、修改、删除。 二、实验内容 1．根据输入的学号修改年龄，使年龄加1，并调用存储过程。 2．创建存储过程，根据输入的学号,显示学生的信息，并调用存储过程。 3.利用输入参数向SCORE表插入选课记录。并在PL/SQL块中调用存储过程。 4．根据输入的学号、课程号,修改其成绩,若成绩超出100则抛出异常,不做修改，并调用存储过程。 5．创建一个存储过程，以所在系为参数，返回该系的人数和平均年龄。并调用存储过程。 ...
PLSQL手动输入一个字符串,倒序打印输出字符串 , 如输入的是 abcd 打印输出 dcba
wp117的博客
08-04 1516
PLSQL手动输入一个字符串,倒序打印输出字符串 , 如输入的是 abcd 打印输出 dcba
PLSQL调试oracle存储过程
热门推荐
java coding girl
03-30 1万+
step over: 在单步执行时，在函数内遇到子函数时不会进入子函数内单步执行，而时将子函数整个执行完再停止，也就是把子函数整个作为一步。step over: 在单步执行时，在函数内遇到子函数时不会进入子函数内单步执行，而时将子函数整个执行完再停止，也就是把子函数整个作为一步。2.再点击View，进入存储包体查看页面，点击想要调试的存储过程名称，右侧就会显示具体代码，在这个页面可以打断点，调试：点击【开始调试按钮】，即进入调试，调试存储过程要先在下方写入参。二、调试存储包里的存储过程。
Oracle PL/SQL 存储过程详解
"Oracle_存储过程.pdf" Oracle存储过程是Oracle数据库系统中的一个重要组成部分...理解并熟练掌握Oracle存储过程和PL/SQL是成为高效Oracle数据库开发者的关键技能，它可以帮助你构建更高效、更健壮的数据库应用程序。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值