【PLSQL】PLSQL基础

一：记录类型

1.语法

TYPE record_type IS RECORD(column1 type,colunm2 type,… …
Variable_name record_type;

2.代码实例

declaretype test_rec is record(    --test_rec记录类型l_name varchar2(30),d_id number(4));v_emp test_rec;   --v_emp变量名
begin v_emp.l_name := '张三';v_emp.d_id := 1234;dbms_output.put_line(v_emp.l_name || ',' || v_emp.d_id);
end;

可以使用SELECT语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相匹配即可.

create table cux.employee
(last_name varchar2(20),
department_id number(4));insert into cux.employee values('李四',1235,234);declaretype test_rec is record(    --test_rec记录类型l_name varchar2(30),d_id number(4));v_emp test_rec;   --v_emp变量名
begin select last_name, department_id into v_empfrom cux.employeewhere employee_id = 234;dbms_output.put_line(v_emp.l_name || ', ' || v_emp.d_id);
end;

二：字符转换

三：%TYPE和%ROWTYPE

1.%TYPE

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

使用%TYPE的优点：

• 所引用的数据库列的数据类型不必知道;
• 所引用的数据库列的数据类型可以实时改变.

declaretype test_rec is record(l_name cux.employee.last_name%type,d_id cux.employee.department_id%type);v_emp test_rec;
beginselect last_name,department_id into v_empfrom cux.employee where employee_id = 234;dbms_output.put_line(v_emp.l_name || ', ' || v_emp.d_id);
end;

2.%ROWTYPE

四：循环

1.LOOP

LOOP要执行的语句;EXIT WHEN<条件语句>;  --条件满足,退出
END LOOPdeclareint NUMBER(2) := 0;
beginLOOPint := int + 1;dbms_output.put_line('int的当前值为:' || int);EXIT WHEN int = 10;END LOOP;
END;

2.WHILE（推荐）

WHILE<布尔表达式> LOOP要执行的语句;
END LOOP;DECLAREx NUMBER(2) := 0;
BEGINWHILE x < 10 LOOPx := x + 1;dbms_output.put_line('x的当前值为:' || x);END LOOP;
END;

3.数字式循环

FOR 循环计数器 IN[REVERSE] 下限 .. 上限 LOOP要执行的语句
END LOOP;

每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。跟在IN REVERSE后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或者表达式。可以使用EXIT退出循环。

beginFOR i in reverse 2 .. 10 LOOPDBMS_OUTPUT.PUT_LINE('i的值为' || i);END LOOP;
end;

五：游标

1.游标定义及读取

--游标FOR读取
declare cursor c_emp(dep_id number default 1236) is select last_name,employee_id epidfrom cux.employeewhere department_id = dep_id;
beginfor v_emp in c_emp loopDBMS_OUTPUT.PUT_LINE(v_emp.last_name || ', ' || v_emp.epid);end loop;
end;

2.游标属性

• %FOUND：布尔类型属性，当最近一次读记录时成功返回，则值为TRUE；
• %NOTFOUND：布尔类型属性，与%FOUND相反；
• %ISOPEN：布尔型属性，当游标已打开时返回TRUE；
• %ROWCOUNT：数字型属性，返回已从游标中读取的记录数。

3.NO_DATA_FOUND和%NOTFOUND的区别

SELECT … INTO 语句触发NO_DATA_FOUND；

当一个显示游标的WHERE子句未找到时触发%NOTFOUND；当UPATE或DELETE语句的WHERE子句未找到时触发SQL%NOTFOUND；在提取循环中要用%NOTFOUND或%FOUND来确定循环退出条件，不要用NO_DATA_FOUND。

六：异常错误

1.异常处理

EXCEPTION WHEN first_exception THEN <code to handle first exception>WHEN second_exception THEN <code to handle second exception>WHEN OTHERS THEN <code to handle others exception>
END;

异常处理可以按照任意次序排列，但OTHERS必须放在最后。

declare -- Local variables herev_empid cux.employee.employee_id%type := &v_empid;v_sal cux.employee.salary%type;
/* 预定义异常处理 */
begin-- Test statements hereselect salary into v_salfrom cux.employeewhere employee_id = v_empidfor update;if v_sal <= 3000 then update cux.employee set salary = salary+1000where employee_id = v_empid;DBMS_OUTPUT.PUT_LINE('编号为：'|| v_empid || '工资已更新');else DBMS_OUTPUT.PUT_LINE('编号为：'|| v_empid || '工资不需更新');end if;
exceptionWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('编号为：'|| v_empid || '员工不存在');WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE('数据行数太多，请使用游标');WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('其他错误');
end;

2.非预定义异常处理

1. 在PL/SQL块定义部分定义异常情况 <异常情况> EXCEPTION

2. 将其定义好的异常情况，与标准的ORACLE错误联系起来，使用PRAGMA EXCEPTION_INIT语句；

   PRAGMA EXCEPTION(<异常情况>,<错误代码>)；

3. 在PL/SQL异常情况处理部分对异常情况做出相应处理。

3.用户自定义的异常处理

用户定义的异常错误是通过显式使用RAISE语句来触发。当引发一个异常错误时，控制就转向到EXCEPTION块异常错误部分，执行错误处理代码。

步骤：

1. 在PL/SQL块的定义部分定义异常情况；

2. <异常情况> EXCEPTION

   RAISE<异常情况>;

在PL/SQL块的异常情况处理部分对异常情况做出相应处理。

七：存储过程或函数

把PL/SQL程序存储在数据库中，并可以在任何地方来运行它。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

1.函数

IN参数标记表示传递给函数的值在该函数执行中不改变；OUT标记表示一个值在函数中进行计算并通过该参数传递给调用语句；IN OUT标记表示传递给函数的值可以变化并传递给调用语句。若省略标记，则参数隐含为IN。因为函数需要一个返回值，所以RETURN包含返回结果的数据类型。

create or replace function get_salary(dep_id cux.employee.department_id%type (default 1235),emp_count out number)return numberisv_sum number;
beginselect sum(salary), count(*) into v_sum, emp_countfrom cux.employeewhere department_id = dep_id;return v_sum;
exceptionwhen no_data_found then DBMS_OUTPUT.PUT_LINE('查询的数据不存在');when others then DBMS_OUTPUT.PUT_LINE(sqlcode || '--' || sqlerrm);end;

函数调用过程：

1.位置表示法；

declarev_num number;v_sum number;
beginv_sum := get_salary(1237,  v_num);DBMS_OUTPUT.PUT_LINE('1237号部门的工资总和：' || v_sum || ' 人数：' || v_num);
end;

2.名称表示法

形式参数必须和函数定义时声明的形式参数名称相同，顺序可以任意排列。

 v_sum := get_salary(dep_id => 1237, emp_count => v_num);

3.混合表示法

使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法。

 v_sum := get_salary(1237, emp_count => v_num);

八：包

1.包的创建

create or replace package demo_pack is-- Author  : 11313321-- Created : 2023/8/22 8:45:06-- Purpose : 练习测试-- Public type declarationsEmpRec cux.employee%ROWTYPE;-- Public function and procedure declarationsfunction add_emp(last_name VARCHAR2, dept_id number, emp_id NUMBER, salary number)return number;function remove_emp(emp_id number)return number;procedure query_empl(emp_id number);end demo_pack;

包主体的创建方法，它实现上面所声明的包定义：

create or replace package body demo_pack isfunction add_emp(last_name VARCHAR2, dept_id number, emp_id NUMBER, salary number)return numberisempno_remaining exception;pragma exception_init(empno_remaining, -1);begininsert into cux.employee values(last_name, dept_id, emp_id, salary,TO_DATE('2023,5,20','yyyy-mm-dd'));if sql%found thenreturn 1;end if;exceptionwhen empno_remaining then return 0;when others then return -1;end add_emp;function remove_emp(emp_id number)return numberisbegindelete from cux.employee where employee_id = emp_id;if sql%found then return 1;elsereturn 0;end if;exceptionwhen others thenreturn -1;end remove_emp;procedure query_empl(emp_id number)isbeginselect * into EmpRec from cux.employee where employee_id = emp_id;exceptionwhen no_data_found thenDBMS_OUTPUT.PUT_LINE('数据库中没有该员工');when too_many_rows thenDBMS_OUTPUT.PUT_LINE('程序运行错误！请使用游标');when others thenDBMS_OUTPUT.PUT_LINE(sqlcode || '--' || sqlerrm);end query_empl;begin-- Initializationnull;
end demo_pack;

2.包的调用

对包内共有元素的调用格式为：报名.元素名称

declarevar number;
beginvar := demo_pack.add_emp('老马', 1476, 789, 3800);if var=-1 thenDBMS_OUTPUT.PUT_LINE(sqlcode || '--' || sqlerrm);elsif var=0 thenDBMS_OUTPUT.PUT_LINE('该记录已存在');else DBMS_OUTPUT.PUT_LINE('添加记录成功');demo_pack.query_empl(789);DBMS_OUTPUT.PUT_LINE(demo_pack.EmpRec.employee_id||'--'||demo_pack.EmpRec.last_name||'--'||demo_pack.EmpRec.department_id);var := demo_pack.remove_emp(788);if var=-1 thenDBMS_OUTPUT.PUT_LINE(sqlcode || '--' || sqlerrm);elsif var=0 thenDBMS_OUTPUT.PUT_LINE('该记录不存在');elseDBMS_OUTPUT.PUT_LINE('删除记录成功');end if;end if;
end;

九：触发器

1.触发器的组成

• 触发事件：在何种情况下触发TRIGGER，例如：INSERT，UPDATE，DELETE
• 触发时间：触发之前（BEFORE）、之后（AFTER）
• 触发器本身：触发之后的目的和意图
• 触发频率：语句级(STATEMENT)触发器和行级(ROW)触发器。
  • 语句级：当触发某事件时，该触发器只执行一次
  • 行级：当某事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。
  • 行触发器和语句触发器的区别表现在：行触发器要求当一 个 DML 语句操做影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激 活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。

2.语法

CREATE [OR REPLACE] TRIGGER trigger_name{BEFORE | AFTER}{INSERT|DELETE|UPDATE[OF column[,column...]]}ON [schema.] table_name[FOR EACH ROW][WHEN condition]trigger body;

FOR EACH ROW选项说明触发器为多行触发器。当省略FOR EACH ROW选项时，BEFORE和AFTER触发器为语句触发器，而INSTEAD OF触发器则为行触发器。

3.触发器的限制

• 触发器体内的SELECT语句只能为SELECT…INTO…结构，或者为定义游标所使用的SELECT语句。
• 触发器中不能使用数据库事务控制语句COMMIT；ROLLBACK；SAVEPOINT语句。
• 由触发器所调用的过程或函数也不能使用数据库事务控制语句。

当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值

• :NEW 修饰符访问操作完成后列的值
• :OLD 修饰符访问操作完成前列的值

4.实例

--创建表
create table cux.emp_his as
select * from cux.employee
where 1 = 2;--创建触发器
create or replace trigger del_emp_triggerbefore delete on cux.employee for each row
begininsert into cux.emp_his(last_name, department_id, employee_id, salary)values(:old.last_name, :old.department_id, :old.employee_id, :old.salary);
end;

5.创建替代（INSTEAD OF）触发器

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON [schema.] view_name
[FOR EACH ROW ]
[WHEN condition]
trigger_body;

INSTEAD OF用于对视图的DML触发。