ORACLE数据库 —— PL/SQL知识点1

Touw1n_

已于 2023-06-13 16:25:58 修改

阅读量1.7k

点赞数 1

分类专栏： ORACLE 文章标签：数据库 java 开发语言

于 2023-06-12 08:59:14 首次发布

本文链接：https://blog.csdn.net/Beatrice_lovely/article/details/131161724

版权

ORACLE 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

ORACLE数据库 PL/SQL语句基础知识点适合有SQL基础的人群。 禁止转载！

PL/SQL 是ORACLE对标准SQL的扩展，是一种过程化语言，属于第三代语言。

PL/SQL语言的组成：
   声明部分
   可执行部分
   异常处理部分

PL/SQL语言的结构：
   [DECLARE] --声明部分开始关键字
   BEGIN --执行部分开始关键字
   [EXCEPTION] --异常处理部分开始关键字
   END --执行部分结束关键字

SET SERVEROUTPUT ON; //设置环境变量serveroutput为打开状态，从而使得pl/sql程序能够在SQL*plus中输出结果要使用DBMS_OUTPUT.PUT_LINE 就要先开启serveroutput

只包含执行部分的例子
1. 输出''第一个PL/SQL程序，这里是执行部分"
BEGIN
DBMS_OUTPUT.PUT_LINE(''第一个PL/SQL程序，这里是执行部分");
END;
/

包含声明、执行和异常处理三个部分的示例
2.查找emp表中比SCOTT工资收入高的雇员信息。
DECLARE
   var_employ_name VARCHAR2(40);
BEGIN
   SELECT ename INTO var_employ_name FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SCOTT');
   DBMS_OUTPUT.PUT_LINE(var_employ_name || '的工资高于SCOTT');
EXCEPTION
   WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('没有员工的工资高于SCOTT!');
   WHEN TOO_MANY_ROWS THEN
       DBMS_OUTPUT.PUT_LINE('有太多员工的工资高于SCOTT了!');
END;
/

赋值表达式
<变量> := <表达式>
eg. var_salary := 100为赋值表达式

数值表达式
由数值类型的变量、常量、函数或表达式由算术运算符号连接而成。
eg. 4+9*(var_salary - 2) 为数值表达式

关系表达式
由关系运算符连接起来的表达式。
eg. var_salary<= 100为关系表达式

逻辑表达式
是由逻辑符号连接起来的表达式。
eg. (var_salary > 100)AND(var_salary < 150)为逻辑表达式

变量与常量的声明
声明变量的语法结构如下：
<变量名> <数据类型> [(宽度) := <初始值>]; //初值，可赋，可不赋。

3.声明变量举例
DECLARE
var_country_name VARCHAR2(40):='CHINA';
BEGIN
DBMS_OUTPUT.PUT_LINE('国家的名字为: ' || var_country_name);
END;
/

声明常量的语法结构如下：
<常量名> CONSTANT <数据类型> := 值; //常量一旦被声明，必须赋初值。并且不能对常量值进行修改，否则报错。

4.声明常量举例
DECLARE
con_db_name CONSTANT VARCHAR2(40) := 'Oracle';
BEGIN
DBMS_OUTPUT.PUT_LINE('使用的数据库为: ' || con_DB_name);
END;
/

数值类型
   最常用的数据类型有NUMBER、PLS_INTEGER和BINARY_INTEGER
字符类型
   最常用的字符类型有VARCHAR2、NVARCHAR2、CHAR、NCHAR和LONG类型
布尔类型
   它存在三种值，即TRUE、FALSE和NULL
日期类型
   常用的日期类型有两种：DATE和TIMESTAMP

引用类型
   当定义某个变量或常量时，我们可以根据前面使用过的数据类型，利用%TYPE或%ROWTYPE来定义。（灵活性高）
   与字段类型相同，利用%TYPE命令
   与行记录类型相同，利用%ROWTYPE命令

5.用%TYPE定义变量举例
DECLARE
   var_name scott.emp.ename%TYPE //定义变量var_name 其变量类型和scott.emp.ename的类型相同，ORACLE自己去找
   var_empid scott.emp.empno%TYPE
   var_empsal scott.emp.empno%TYPE
BEGIN
   SELECT ename,sal INTO var_name,var_empsal FROM emp WHERE empno=&var_employee_id; //&是输入符号
   DBMS_OUTPUT.PUT_LINE('员工的姓名和工资为： ' || var_name || ' '|| var_employee_sal);
END;
/

6.用%ROWTYPE定义变量举例
DECLARE

var_emp_rec scott.emp%ROWTYPE; //定义变量var_emp_rec 用%ROWTYPE一次性定义整行的类型

BEGIN
SELECT * INTO var_emp_rec FROM scott.emp WHERE empno = emp&no;
DBMS_OUTPUT.PUT_LINE('员工姓名为:'|| var_emp_rec.ename);
END;
/

PL/SQL的键盘输入通过在变量名前面加一个“&”符号实现。
(1)如果编程时是：&var_input，那么输入时要加单引号把字符串引起来；
(2)如果编程时是：‟&var_input‟,那么输入时不要加单引号，直接输入字符串即可。

7..在emp表中根据手动输入的姓名var_name查找雇员工作、薪水。
DECLARE
   var_name VARCHAR2(20);
   var_job VARCHAR2(9);
   var_sal VARCHAR2(7,2);
BEGIN
   -- 注释var_name:= &var_name;
   var_name := '&var_name'; // 这里加了单引号，输入时直接输入雇佣名称SCOTT就可以了，如果没有加，那么就需要输入 'SCOTT'
   SELECT job,sal INTO var_job,var_sal FROM emp WHERE ename=var_name;
   DBMS_OUTPUT.PUT_LINE(var_name ||'员工的工作为： ' || var_job ||' 薪水为：'|| var_sal);
EXCPTION
   WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('无此员工信息，请核对后重新输入 !');
END;
/

复合数据类型
PL/SQL有三种常见的复合类型：记录类型、索引类型和VARRAY数组类型。

记录类型，定义记录的语法如下：
TYPE <记录名称> IS RECORD (
   <变量名> <数据类型> [(宽度) := <初始值>]
    [,<变量名> <数据类型> [(宽度) := <初始值>] ]
    [,<变量名> <数据类型> [(宽度) := <初始值>]]
    …
);

声明一个记录类型的变量的方法为：
<变量名称><记录名称>

8.对记录的定义和使用进行举例
DECLARE
   TYPE emp_record_type IS RECORD(
       var_name scott.emp.ename%TYPE,
        var_job scott.emp.job%TYPE,
        var_hiredate scott.emp.hiredate%TYPE,
        var_jobloc scott.dept.loc%TYPE
   );
   var_emp EMP_RECORD_TYPE;
BEGIN
   SELECT e.ename,e.job,e.hiredate,d.loc INTO var_emp FROM emp e,dept d
   WHERE e.empno = &&empno AND e.deptno=d.deptno;
   DBMS_OUTPUT.PUT_LINE(var_emp.var_name ||'员工的雇佣信息和部门所在地为:'||var_emp.var_jobloc || var_emp.var_jobloc)
END;
/

定义索引的语法如下：
TYPE <索引名称> IS TABLE OF <数据类型>
[NOT NULL]
INDEX BY <数组下标数据类型>;

声明索引类型变量的方式为：
<变量名称><索引名称>

9.对索引的定义和使用进行举例
DECLARE
   TYPE emp_table_type IS TABLE OF scott.emp%ROWTYPE
   INDEX BY BINARY)INTEGER;
   var_emp_table EMP_TABLE_TYPE;
BEGIN
   SELECT ename INTO var_emp_table(1).ename FROM emp WHERE empno = 7499; //将7499的员工名字插入到var_emp_table第一行
   SELECT ename INTO var_emp_table(2).ename FROM emp WHERE empno = 7566;
   DBMS_OUTPUT.PUT_LINE('7499号员工姓名为:'|| var_emp_table(1).ename);
   DBMS_OUTPUT.PUT_LINE('7566号员工姓名为:'|| var_emp_table(2).ename);
END;
/

控制语句
1)条件语句IF
条件语句有三种形式：
1.IF…
2.IF…ELSE…
3.IF…ELSIF…

IF…结构如下：
   IF <条件> THEN
   语句块;
   END IF;

10.IF…结构示例。输入员工编号，查询该员工是否收入较低（<3000）。
DECLARE
   var_salary scott.emp.sal%TYPE;
BEGIN
   SELECT sal INTO var_salary FROM scott.emp WHERE empno = &empno;
   IF var_salary < 3000 THEN
       DBMS_OUTPUT.PUT_LINE('该员工的收入较低: ' || var_salary || '元');
   END IF;
END;
/

IF…ELSE…结构如下：
    IF <条件> THEN
    语句块1;
    ELSE
   语句块2;
END IF;

11.IF…ELSE…结构示例。输入员工编号，获取对应的员工工资，并根据其工资判断是否需要交税。
DECLARE
   var_salary scott.emp.sal%TYPE;
BEGIN
   SELECT sal INTO var_salary FROM scott.emp WHERE ename = '&ename';
   IF var_salary <3000 THEN
       DBMS_OUTPUT.PUT_LINE('该员工不需交税, 工资:' ||var_salary|| '元');
   ELSE
       DBMS_OUTPUT.PUT_LINE('该员工需要交税, 工资:' ||var_salary|| '元');
   END IF;
END;
/

IF…ELSIF…结构如下：
   IF <条件1> THEN
   语句块1;
   ELSIF <条件2> THEN
    语句块2;
   …
   ELSIF <条件n> THEN
   语句块n;
   ELSE
   语句块n+1;
   END IF;

12.IF…ELSIF…结构示例。输入员工编号，获取员工的工资和职位，并根据其职位资计算奖金。
DECLARE
var_bonus NUMBER;
    var_salary scott.emp.sal%TYPE;
    var_job_id scott.emp.deptno %TYPE;
    var_employee_id scott.emp.empno %TYPE;
BEGIN
   SELECT sal, deptno INTO var_salary, var_job_id FROM scott.emp WHERE empno = &empno;
   IF var_job_id = 10 THEN
       var_bonus := var_salary*0.2*12; //赋值用:=
   ELSIF var_job_id = 20 THEN
       var_bonus := var_salary*0.15*12;
   ELSIF var_job_id = 30 THEN
       var_bonus := var_salary*0.1*12;
   ELSE
       var_bonus := var_salary*0.05*12;
   END IF;
   DBMS_OUTPUT.PUT_LINE(var_employee_id || '员工部门编号为' || var_job_id || '的年终奖为：'|| var_bonus || '元！');
END;
/

嵌套IF结构
以上三种条件语句IF…、IF…ELSE…和IF…ELSIF…可以嵌套使用。这里不再举例。

分支语句CASE
CASE语句的语法结构：
   CASE<变量>
       WHEN<条件1>THEN语句块1;
       WHEN<条件2>THEN语句块2;
       …
       WHEN<条件n>THEN语句块n;
       ELSE 语句块n+1;
   END CASE;

13.分支语句示例。用于输出员工的职务。
DECLARE
   var_job emp.job%TYPE;
   var_employee_id emp.empno%TYPE;
BEGIN
   SELECT job INTO var_job FROM emp WHERE empno = &var_employee_id;
   CASE var_job
       WHEN 'SALESMAN' THEN
           DBMS_OUTPUT.PUT_LINE('员工的职务为 Sales Man');
       WHEN 'CLERK' THEN
           DBMS_OUTPUT.PUT_LINE('员工的职务为 CLERK');
       WHEN 'MANAGER' THEN
           DBMS_OUTPUT.PUT_LINE('员工的职务为 MANAGER');
       WHEN 'ANALYST' THEN
           DBMS_OUTPUT.PUT_LINE('员工的职务为 ANALYST');
       ELSE
           DBMS_OUTPUT.PUT_LINE('此人不是公司员工');
   END CASE;
END;
/

循环语句有三种形式：
         LOOP…END LOOP
         WHILE…LOOP… END LOOP
         FOR…LOOP…END LOOP

(1)LOOP…END LOOP语句
不断循环执行LOOP和END LOOP之间的语句。
该语句结构本身不会终止LOOP循环，终止循环时需借助于EXIT语句。

语法结构如下：
   [<<循环标签>>]
   LOOP
       语句块1;
       EXIT [循环标签] WHEN<条件>;
       语句块2;
   END LOOP [循环标签];

14.LOOP…END LOOP语句示例。计算1~100的和。
DECLARE
   var_result NUMBER:=0
   var_number NUMBER:=1
BEGIN
   LOOP
       var_result:=var_result+var_number;
       var_number :=var_number+1;
       EXIT WHEN var_number>100;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('1~100的和为： '|| var_result);
END;
/

(2)WHILE…LOOP… END LOOP语句。该语句结构本身能够终止循环，不用借助于EXIT语句。
语法结构如下：
   [<<循环标签>>]
   WHILE <条件> LOOP
        语句块;
   END LOOP [循环标签];

15.WHILE…LOOP… END LOOP语句示例。计算1-100之间的整数和。
DECLARE
   var_result NUMBER :=0;
   var_number NUMBER:=1;
BEGIN
   WHILE var_number<101 LOOP
       var_result := var_result+var_number;
       var_number := var_number+1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('1~100的和为： '|| var_result);
END;
/

(3)FOR…LOOP…END LOOP语句。循环遍历指定范围内的整数。每循环一次，其循环变量将会自动加1。
语法结构如下
   [<<循环标签>>]
   FOR <循环变量> IN [REVERSE] <初始值>..<终止值>LOOP
        语句段;
   END LOOP [循环标签];

17.FOR…LOOP…END LOOP语句示例。计算1-100之间整数和。
DECLARE
   var_result NUMBER :=0;
BEGIN
   FOR var_number IN 1..100 LOOP
       var_result := var_result+var_number;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('1~100的和为： '|| var_result);
END;
/