postgresql存储过程语法 plsql存储过程怎么写

文章目录

• 1: Oracle 数据库下的存储过程

• 1.1 ： PLSQL编程
• 1.2： PLSQL 的结构
• 1.3：PLSQL 中常见的类型分类
• 1. : 流程控制

• 2: 游标

• 2.1：游标
• 2.2 游标的语法
• 2.3 游标的属性
• 2.4： 有参游标

• 3： 存储过程

• 3.1 存储过程的简介
• 3.2 : 存储过程的语法

1: Oracle 数据库下的存储过程

1.1 ： PLSQL编程

• PLSQL : producure language / SQL
• 是 Oracle 对 SQL 语言的扩展
• 在SQL 语句中增强了 过程处理语句， 使SQL 语言具备了过程处理能力。

1.2： PLSQL 的结构

• 通过 plSQL Developer 工具 test Window 创建程序模块 或者在 SQL windows 中编写
• 通过在 program windows 中创建 producure 程序
• PLSQL 中不区分大小写
• PLSQL 有三个部分组成

• 声明部分： declare
• 可执行部分： begin … end
• 异常处理部分：

declare --声明 begin -- 可执行部分 -- 异常处理 end

1.3：PLSQL 中常见的类型分类

1： 数据类型分类

• 普通的数据类型（char , varchar2, date, number, boolean, long）
• 特殊变量类型（引用类型， 记录型变量）

2： 声明变量常用的方式

• 变量名 变量类型（变量长度） ：v_name varchar(20);

3: 变量赋值的方式

• 变量赋值的两种方式

• 1： 直接赋值： := for example: v_name := ‘zhangwang’;
• 2: 语句赋值 ， 使用 select … to… : (语法： select 值 into 变量)

4: 引用类型变量

• 变量的类型和长度取决于表中字段的类型和长度
• 通过表名.列名%type 指定类型和长度， 例如： v_name emp.ename%type;

5: 记录型变量

• 接受表中的一整行记录， 相当于Java中的一个对象
• 语法：变量名 表名%ROWTYPE, 例如： v_emp emp%rowtype;
• 使用场景： 一个表中的字段有大量时， 不需要一个一个进行声明：可以使用记录型变量

-- PL_SQL 程序的学习 变量的使用和赋值
declare
  -- 定义普通变量类型
  v_name varchar2(20) := '王记勇';
  v_sal  number;
  v_addr varchar2(200);
  
  -- 定义为引用类型，
  v_name1 emp.ename%type;               
  v_sal1 emp.sal%type;
  
  -- 定义记录型数据类型
  v_emp emp%Rowtype;
begin
 
  -- 普通变量： 赋值
  v_sal  := 7500;

  -- 变量插入赋值
  select '河南省周口市淮阳县' into v_addr from dual;
  select ename, sal into v_name1, v_sal1 from emp where empno = 7369;     -- 从数据库表中查出数据进行赋值
  
  -- 记录型数据赋值
  Select * Into v_emp From emp Where empno = 7369;
  
  -- 进行数据的输出 set serveroutput on: 打开输出对象。
  dbms_output.put_line('姓名1：' || v_emp.ename || ',薪水1：' || v_emp.sal ||',地址：' || v_addr);
end;

---

1. : 流程控制

• 条件分支

begin

	if condition1 then execu1
		elsif condition2 then execu2
		else execu3
   end if;
end;

• 循环

• Oracle 中有三种循环方式： 这里：loop

declare
	-- 声明循环变量
	v_num number := 1;
begin
	loop 
		exit when  v_num > 10; -- 退出条件
	end loop;
	
	-- 循环自增
	v_num := v_num+1;
end;

2: 游标

2.1：游标

• 用于临时存储一个查询返回的多行数据（结果集， 类型与 Java 的Jdbc 连接返回的ResultSet 集合），通过遍历游标， 可以逐行访问处理该结果集的数据。
• 游标的使用方式：声明–>打开—>读取—>关闭。

2.2 游标的语法

• 游标的声明：

• CURSOR 游标名（参数列表） IS 查询语句；

• 游标的打开

• OPEN 游标名

• 游标的取值

• FETCH 游标名 INTO 变量列表

• 游标的关闭

• CLOSE 游标名

2.3 游标的属性

游标的属性	返回值的类型	说明
%Rowcount	整型	获得fetch语句返回的数据行数
%found	布尔型	最近的fetch 语句返回一行数据则为真， 否则为假
%notfound	布尔型	与%Found 属性返回值相反
%isopen	布尔型	游标已经打开时为真，否则为假

2.4： 有参游标

-- 在定义游标时， 有参和无参的区别
declare

cursor c_emp(v_deptno emp.deptno%type) is 
	select ename, sal emp where deptno = v_deptno;

begin
	open c_emp(10);		// 进行参数的传入
	close c_emp;
end

---

3： 存储过程

3.1 存储过程的简介

1： 概念：

• 编写的PLSQL 程序可以进行表的操作， 判断， 循环逻辑处理的工作，但无法实现程序的 重复调用。
• PLSQL 是将一个个PLSQL的业务处理过程存储起来进行复用， 这些被存储起来的 PLSQL 程序称之为存储过程
• 代码的复用

2： 存储过程的作用

• 程序开发过程中， 实现一个特定的业务功能， 会向数据库进行多次的连接和关闭（连接和关闭很是消耗资源）， 需要对数据库进行多次I/O 读写， 性能较低。

• 解决： 业务实现放到 PLSQL中， 在应用程序中调用 PLSQL 就可以连接关闭 一次数据库，提高效率。

• Oracle官方建议：能够让数据库操作的不要放到程序中， 在数据库中实现基本不会出现错误， 在程序中可能会存在错误，（因为： 在数据库中操作数据时， 可以有一定的日志恢复功能）

---

3.2 : 存储过程的语法

1: 语法

create or replace procedure 过程名称（参数列表） is
begin

end(存储过程名称)；

2： 存储过程的分类

• 不带参数

-- 不带参数的存储过程
create or replace procedure P_Prictise_test1 as           -- is 可以替换为 as
-- 可以直接进行类型的声明
v_addr Varchar(200) ;

Begin
       Select  '河南省周口市淮阳县'  into v_addr from dual;
      dbms_output.put_line('hello world ');
      dbms_output.put_line('v_addr: ' || v_addr);
end P_Prictise_test1;

• 带参数

-- 带参数的存储过程
create or replace procedure P_Prictise_test2(v_empno In emp.empno%Type) as

v_name emp.ename%Type;
v_sal emp.sal%Type;

-- 定义一个参数游标
Cursor c_emp(v_deptno emp.deptno%Type) Is 
       Select  ename, sal From emp Where deptno = v_deptno;
       
begin
  Select ename, sal Into v_name, v_sal From emp Where empno = v_empno;
  dbms_output.put_line('姓名:' || v_name || '薪水' || v_sal );
  
  -- 读取游标中的数据
  Open c_emp(10);
  Loop
    Fetch c_emp Into v_name, v_sal ;
     dbms_output.put_line('姓名:' || v_name || '薪水' || v_sal );
    Exit When c_emp%Notfound;
  End Loop;
  Close c_emp;
  
end p_prictise_test2;

• 带输入和输出参数

– 含有 输入和输出参数的存储过程
 create or replace procedure p_prictise_test3(v_empno In emp.empno%Type, v_sal Out emp.sal%Type) Asbegin
 Select sal Into v_sal From emp Where empno = v_empno;
 end p_prictise_test3;
 ```

3：调用 producure

• 通过程序调用

Declare
v_sal emp.sal%Type;

begin
  -- Test statements here
      P_prictise_test1;
      P_prictise_test2(7369);
      p_prictise_test3(7369,v_sal );
      dbms_output.put_line('传出参数值' || v_sal);
end;

• 通过窗口调用