PLSQL

最新推荐文章于 2024-07-26 13:52:53 发布

MM999666

最新推荐文章于 2024-07-26 13:52:53 发布

阅读量4.1k

点赞数 4

分类专栏：数据库文章标签：数据库 sql plsql

本文链接：https://blog.csdn.net/mm999666/article/details/111545957

版权

PL/SQL

一、PL/SQL 程序设计简介

1、概述

PL/SQL 是 Procedure Language & Structured Query Language 的缩写。
PL/SQL是对 SQL 语言存储过程语言的扩展。
它现在已经成为一种过程处理语言，简称 PL/SQL。

2、PL/SQL 可用的 SQL 语句

PL/SQL 是 ORACLE 系统的核心语言，现在 ORACLE 的许多部件都是由 PL/SQL 写成。
在 PL/SQL 中可以使用的 SQL 语句有：
- INSERT，UPDATE，DELETE，SELECT … INTO，COMMIT，ROLLBACK，SAVEPOINT。
提示：在 PL/SQL 中只能用 SQL 语句中的 DML 部分，不能用 DDL 部分，如果要在 PL/SQL 中使用 DDL(如CREATE table 等)的话，只能以动态的方式来使用。

补充：大纲

1、pl/sql基本的语法格式

2、记录类型

3、流程控制

3.1条件判断（两种）

方式一：if … then … elseif then … else … end if;

方式二：case … when … then … end;

3.2循环结构（三种）

方式一：loop … exit when … end loop;

方式二：while … loop … end loop;

方式三：for i in … loop … end loop;

3.3 goto、exit

4、游标的使用（类似于java中的iterator）

5、异常的处理（三种方式）

6、存储函数（有返回值）、存储过程（没有返回值）

7、触发器

二、PL/SQL 块结构和组成元素

SQL> set serveroutput on

1、PL/SQL 块

PL/SQL 程序由三个块组成，即声明部分、执行部分、异常处理部分

（1）PL/SQL 块的结构如下：

DECLARE 
/* 声明部分: 在此声明 PL/SQL 用到的变量,类型及游标，以及局部的存储过程和函数 */
BEGIN
 /* 执行部分: 过程及 SQL 语句 , 即程序的主要部分 */
EXCEPTION
 /* 执行异常部分: 错误处理 */
END;

其中执行部分是必须的。

输出helloworld

declare
  ----声明的变量、类型、游标
begin
  ----程序的执行部分（类似于java里的main（）方法）
  dbms_output.put_line('helloworld');
exception
  ----针对begin块中出现的异常，提供处理的机制
  ----when ... then ...
  ----when ... then ...
end;

SQL> set serveroutput on

begin
  dbms_output.put_line('helloworld');
end;


helloworld

PL/SQL procedure successfully completed

查询100号员工的工资

declare
  --声明变量
  v_sal number(20);  
begin
  --sql语句的操作：select ... into ... from ... where ...
  select salary into v_sal from employees where employee_id = 100;
  --打印
  dbms_output.put_line(v_sal);
end;

查询100号员工的工资，邮箱，入职日期

declare
  v_sal number(20);
  v_email varchar2(20);
  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_email||','||v_hire_date);
end;


24000,SKING,17-JUN-87

PL/SQL procedure successfully completed

注意：变量之间要对应。

声明变量也可以动态声明

declare

  v_sal employees.salary%type;
  v_email employees.email%type;
  v_hire_date employees.hire_date%type;  
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_email||','||v_hire_date);
end;

（2）PL/SQL 块可以分为三类：

无名块：动态构造，只能执行一次。
子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。
触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

2、标识符

PL/SQL 程序设计中的标识符定义与 SQL 的标识符定义的要求相同。要求和限制有：

标识符名不能超过 30 字符；
第一个字符必须为字母；
不分大小写；
不能用’-‘(减号);
不能是 SQL 保留字。

一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果。

例如：下面的例子将会删除所有的纪录，而不是 KING 的记录；

DECLARE
   Ename varchar2(20) := ’KING’;
BEGIN
   DELETE FROM emp WHERE ename=ename;
END;

变量命名在 PL/SQL 中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个

系统的文档在规范上达到要求。下面是建议的命名方法：

标识符	命名规则	例子
程序变量	V_name	V_name
程序常量	C_Name	C_company_name
游标变量	Name_cursor	Emp_cursor
异常标识	E_name	E_too_many
表类型	Name_table_type	Emp_record_type
表	Name_table	Emp
记录类型	Name_record	Emp_record
SQL*Plus替代变量	P_name	P_sal
绑定变量	G_name	G_year_sal

3、变量类型

（1）变量类型

在这里插入图片描述

补充：number类型

NUMBER（ precision， scale）

precision表示数字中的有效位;如果没有指定precision的话，Oracle将使用38作为精度。
如果scale大于零，表示数字精确到小数点右边的位数；scale默认设置为0；如果scale小于零，Oracle将把该数字取舍到小数点左边的指定位数。
Precision的取值范围为【1—38】；Scale的取值范围为【-84—127】。
NUMBER整数部分允许的长度为（precision- scale），无论scale是正数还是负数。
如果precision小于scale，表示存储的是没有整数的小数。
Precision表示有效位数，有效数位：从左边第一个不为0的数算起，小数点和负号不计入有效位数；scale表示精确到多少位，指精确到小数点左边或右边多少位(±决定)。

Number值类型举例：

实际值	数据类型	存储值
1234567.89	Number	1234567.89
1234567.89	Number(8)	1234567
1234567.89	Number(6)	出错
1234567.89	Number(9,1)	1234567.9
1234567.89	Number(9,3)	出错
1234567.89	Number(7,2)	出错
1234567.89	Number(5,-2)	1234600
1234511.89	Number(5,-2)	1234500
1234567.89	Number(5,-4)	1230000
1234567.89	Number(*,1)	1234567.9
0.012	Number(2,3)	0.012
0.23	Number(2,3)	出错

关于precision， scale也可以作如下表述，定点数的精度§和刻度(s)遵循以下规则：

当一个数的整数部分的长度 > p-s 时，Oracle就会报错
当一个数的小数部分的长度 > s 时，Oracle就会舍入。
当s(scale)为负数时，Oracle就对小数点左边的s个数字进行舍入。
当s > p 时, p表示小数点后第s位向左最多可以有多少位数字，如果大于p则Oracle报错，小数点后s位向右的数字被舍入

与int的区别

1）oracle本来就没有int类型，为了与别的数据库兼容，新增了int类型作为number类型的子集。

2）int类型只能存储整数，int相当于number(22),存储总长度为22的整数。

3）number可以存储浮点数，也可以存储整数；

number(8,1)存储小数位为1位，总长度为8的浮点数，如果小数位数不足，则用0补全；

number(8)存储总长度为8的整数；

number类型的子类

a) oracle本来就没有int类型，为了与别的数据库兼容，新增了int类型作为number类型的子集。

b) 在oracle数据库建表的时候，decimal,numeric不带精度，oracle会自动把它处理成INTEGER；带精度，oracle

会自动把它处理成number。

c) Oracle只用NUMBER(m,n)就可以表示任何复杂的数字数据。

d) decimal,numeric,int等都为SQL、DB2等数据库的数据类型，ORACLE为了兼容才将其引入；但实际上在

ORACLE内部还是以NUMBER的形式将其存入。

declare
  v_sal number(8,2) :=0;
  v_emp_id number(10);
  v_email varchar2(20);
  v_hiredate date;
begin
  select salary,employee_id,email,hire_date into v_sal,v_emp_id,v_email,v_hiredate
  from employees
  where employee_id = 123;
  
  dbms_output.put_line('employee_id:'||v_emp_id||' salary:'||v_sal||' email:'||v_email||' hire_date:'||v_hiredate);

end;



employee_id:123 salary:6500 email:SVOLLMAN hire_date:10-OCT-97

PL/SQL procedure successfully completed

（2）复合类型 ------记录和表

记录类型

记录类型是把逻辑相关的数据作为一个单元存储起来，称作 PL/SQL RECORD 的域(FIELD)，其作用是存放互不相

同但逻辑相关的信息。

定义记录类型语法如下:

TYPE record_type IS RECORD(
 Field1 type1 [NOT NULL] [:= exp1 ],
 Field2 type2 [NOT NULL] [:= exp2 ],
 . . . . . .
 Fieldn typen [NOT NULL] [:= expn ] ) ;

declare
----声明一个记录类型
  type emp_record is record(
  v_sal employees.salary%type,
  v_email employees.email%type,
  v_hire_date employees.hire_date%type
  );
  ----定义一个记录类型的成员变量
  v_emp_record emp_record;
    
begin

  select salary,email, hire_date into v_emp_record from employees where employee_id = 100;

  dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','||v_emp_record.v_hire_date);
end;


SQL> /

24000,SKING,17-JUN-87

PL/SQL procedure successfully completed

注：相当于是一个类里面有三个成员变量，java里面不能通过类去调用成员变量，需要通过对象来调用。

declare
----声明一个记录类型
  type emp_record is record(
  v_sal number(8,2) :=0,
  v_emp_id number(10),
  v_email varchar2(20),
  v_hiredate date
  );
  ----声明一个记录类型的变量
  v_emp_record emp_record;
begin
  select salary,employee_id,email,hire_date into v_emp_record
  from employees
  where employee_id = 123;
  
  dbms_output.put_line('employee_id:'||v_emp_record.v_emp_id||' salary:'||v_emp_record.v_sal||' email:'||v_emp_record.v_email||' hire_date:'||v_emp_record.v_hiredate);

end;



employee_id:123 salary:6500 email:SVOLLMAN hire_date:10-OCT-97

PL/SQL procedure successfully completed

注：声明记录类型时一定要注意里面的标点

declare
  type salary_record is record(
       v_name varchar2(20),
       v_salary number(10,2)
  );
  
  v_sal_record salary_record;
  
begin
  v_sal_record.v_name :='刘德华';
  v_sal_record.v_salary :=12000;
  
  dbms_output.put_line('name:'||v_sal_record.v_name||'salary:'||v_sal_record.v_salary);
  
end;  


name:刘德华salary:12000

PL/SQL procedure successfully completed

使用%TYPE

定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。

优点：

所引用的数据库列的数据类型可以不必知道；

所引用的数据库列的数据类型可以实时改变。

（3）使用%ROWTYPE

PL/SQL 提供%ROWTYPE 操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。

优点：

所引用的数据库中列的个数和数据类型可以不必知道；

所引用的数据库中列的个数和数据类型可以实时改变。

declare
  v_emp_record employees%rowtype;
    
begin
  select * into v_emp_record
  from employees
  where employee_id = 123;
  
  dbms_output.put_line('employee_id:'||v_emp_record.employee_id||' salary:'||v_emp_record.salary||' email:'||v_emp_record.email||' hire_date:'||v_emp_record.hire_date);
  
end;

declare
  v_emp_record employees%rowtype;
    
begin
  select * into v_emp_record
  from employees
  where employee_id = 123;
  
  dbms_output.put_line('employee_id:'||v_emp_record.employee_id||' salary:'||v_emp_record.salary||' email:'||v_emp_record.email||' hire_date:'||v_emp_record.hire_date);
  
end;

（4）PL/SQL 表(嵌套表)

（emmmm，暂不掌握）

PL/SQL 程序可使用嵌套表类型创建具有一个或多个列和无限行的变量, 这很像数据库中的表。

声明嵌套表类型的一般语法如下:

TYPE type_name IS TABLE OF
{datatype | {variable | table.column} % type | table%rowtype};

4、运算符和表达式（数据定义）

（1）关系运算符

最低0.47元/天解锁文章

MM999666

关注

4
点赞
踩
28

收藏

觉得还不错? 一键收藏
0
评论
PLSQL

PL/SQL一、PL/SQL 程序设计简介1、概述PL/SQL 是 Procedure Language & Structured Query Language 的缩写。PL/SQL是对 SQL 语言存储过程语言的扩展。它现在已经成为一种过程处理语言，简称 PL/SQL。2、PL/SQL 可用的 SQL 语句PL/SQL 是 ORACLE 系统的核心语言，现在 ORACLE 的许多部件都是由 PL/SQL 写成。在 PL/SQL 中可以使用的 SQL 语句有：INSERT，
复制链接

扫一扫