Oracle：PL/SQL基础笔记分享

最新推荐文章于 2025-04-26 09:46:31 发布

Swwwwwg

最新推荐文章于 2025-04-26 09:46:31 发布

阅读量1.9k

点赞数 29

文章标签：数据库 oracle 笔记

本文链接：https://blog.youkuaiyun.com/weixin_73296216/article/details/137437201

版权

一、基本结构

[declare declaration_statements;]

begin

executable_statements;

[exception exception_handling_statements;]

end;

declare变量，begin…end主体（可嵌套其他块），exception处理可能出现的任何执行错误，PL/SQL语句需要使用’/‘结尾才能被执行

二、常量和变量

①数据类型

类型	说明
boolean	布尔型，true\false\null
binary_integer	带符号整数，-231~231
natural	binary_integer的子类型，非负整数
naturaln	binary_integer的子类型，不为null的非负整数
positive	binary_integer的子类型，正整数
positiven	binary_integer的子类型，不为null的正整数
signtype	binary_integer的子类型，-1\0\1
pls_integer	带符号整数，-231~231，与binary_integer相似。若溢出会发生异常
simple_integer	取值与binary_integer相同，不能存储null值，若溢出不会发生异常，只会简单截断结果
string	与varchar2相同
record	一组其他类型的组合
ref cursor	指向一个行集的指针

②常量

声明时必须赋值

constant_name constant data_type {:=|default} value;

:=为赋值符号，也可以使用default代替（初始化常量或变量时）

③变量

variable_name data_type [[not null] {:=|default} value];

④在PL/SQL中使用变量和常量

set serveroutput on       
 /*在SQL*Plus中显示DBMS_OUTPUT.put_line输出内容的必做事项，即打开服务器输出*/
declare
  emp_number constant number(4) := 7900;
  emp_name varchar2(10);
  emp_job varchar2(9);
  emp_sal number(7,2);
begin
select ename,job,sal 
into emp_name,emp_job,emp_sal
from emp where empno=emp_number;        /*Oracle中用||连接两个字符串*/
dbms_output.put_line('查询的员工的编号为：'||emp_number);
dbms_output.put_line('该员工的姓名为：'||emp_name);
dbms_output.put_line('该员工的职位为：'||emp_job);
dbms_output.put_line('该员工的工资为:'||emp_sal);
end;
/

输出：

查询的员工的编号为：7900

该员工的姓名为：JAMES

该员工的职位为：CLERK

该员工的工资为：950

三、%TYPE类型和%ROWTYPE类型

①%TYPE类型

可解决无法确定变量数据类型的问题：用于隐式地将变量的数据类型指定为对应列的数据类型

variable_name table_name.column_name%type

[[not null] {:=|default}value];

set serveroutput on
declare
  emp_number constant number:=7900;
  emp_name emp.ename%type;
  emp_job emp.job%type;
  emp_sal emp.sal%type;
begin
select ename,job,sal 
into emp_name,emp_job,emp_sal
from emp where empno=emp_number;
end;
/
dbms_output.put_line('工号：'||emp_number);
dbms_output.put_line('姓名：'||emp_name);
dbms_output.put_line('职位：'||emp_job);
dbms_output.put_line('工资：'||emp_sal);

②%ROWTYPE类型

相对于%TYPE列数据，%ROWTYPE针对于行数据，可以存储表中一行数据。

variable_name table_name%rowtype;

set serveroutput on
declare
  emp_number constant emp.empno%type:=7900;
  one_emp emp%rowtype;
begin
  select *
  into one_emp
  from emp where empno=emp_number;
  dbms_output.put_line('工号：'||emp_number);
  dbms_output.put_line('姓名：'||one_emp.ename);
  dbms_output.put_line('职位：'||one_emp.job);
  dbms_output.put_line('工资：'||one_emp.sal);
end;
/

四、记录类型和表类型

①记录类型

可存储由一个或多个字段组成的一行数据

type record_name is record (

field_name data_type [[not null] {:=|default}value]

[,…]

);

set serveroutput on
declare
  type emp_type is record (
      empno number(4),
      ename varchar2(10),
      job varchar2(9),
      sal number(7,2)
  );
  one_tmp emp_type;
  begin
    select empno,ename,job,sal
    into one_emp
    from emp where empno=7900;
    dbms_output.put_line('工号：'||one_emp.empno);
    dbms_output.put_line('姓名：'||one_emp.ename);
    dbms_output.put_line('职位：'||one_emp.job);
    dbms_output.put_line('工资：'||one_emp.sal);
end;
/

②表类型

相对于记录类型可以处理多行数据，类似表

type table_name is table of data_type [not null]

index by binary_integer;

index by binary_integer:指定系统创建一个主键索引，用于引用表类型变量中的特定行

set serveroutput on
declare
  type my_emp is table of emp%rowtype
  index by binary_integer;
  new_emp my_emp;
begin
  new_emp (1).empno := 6800;
  new_emp (1).ename := 'TRACY';
  new_emp (1).job := 'CLERK';
  new_emp (1).sal := 2500;
  new_emp (2).empno := 6900;
  new_emp (2).ename := 'LUCY';
  new_emp (2).job := 'MANAGER';
  new_emp (2).sal := 4000;
  dbms_output.put_line(new_emp (1).empno||','||
                      new_emp (1).ename||','||
                      new_emp (1).job||','||
                      new_emp (1).sal);
  dbms_output.put_line(new_emp (2).empno||','||
                      new_emp (2).ename||','||
                      new_emp (2).job||','||
                      new_emp (2).sal);
  end;
  /

输出：

6800，TRACY，CLERK，2500

6900，LUCY，MANAGER，4000

删除（表类型变量中的）记录

variable_name delete [(index_number)];

其他操作（表类型变量）

count：返回表类型变量中的记录数

first：返回表类型变量的第一行索引

last：返回表类型变量的最后一行索引

next：返回表类型变量的下一行索引

五、程序注释

--：单行注释

/*…*/：多行注释

六、条件选择语句

①if条件语句

if <expression1> then PL/SQL_statement1;

end if; --最简单形式

set serveroutput on
declare
  num number := 8;
begin
  if mod(num,2)=0 then
    dbms_output.put_line(num||'为偶数');
  end if;
end;
/

if <expression1> then PL/SQL_statement1;

else PL/SQL_statement2;

end if; --与else结合

if <expression1> then PL/SQL_statement1;

elsif <expression2> then PL/SQL_statement2;

else PL/SQL_statement3;

end if; --与elsif结合

②case表达式

case search_expression

when expression1 then result1;

when expression2 then result2;

…

when expressionN then resultN;

[else default_result;]

end case; --第一种便于比较确定的一个值

set serveroutput on
declare
  day number := 3;
begin
  case day
  when 1 then dbms_output.put_line('Monday');
  when 2 then dbms_output.put_line('Tuesday');
  when 3 then dbms_output.put_line('Wednesday');
  ……
  when 7 then dbms_output.put_line('Sunday');
  else dbms_output.put_line('Error');
  end case;
end;
/

输出：Wednesday

case

when condition1 then result1;

when condition2 then result2;

…

when conditionN then resultN;

[else default_result];

end case; --第二种便于比较确定的一个范围

set serveroutput on
declare
  score binary_integer := 61;
begin
  case 
  when score >= 90 then dbms_output.put_line('优秀');
  when score >= 80 then dbms_output.put_line('良好');
  when score >= 60 then dbms_output.put_line('及格');
  else dbms_output.put_line('不及格');
  end case;
end;
/

输出：及格

七、循环语句

①loop循环语句

loop

statements;

exit [when condition]; --退出循环

end loop;

set serveroutput on
declare
  i binary_integer := 2;
  n binary_integer := 1;
begin
  loop
    n := n*i;
    i := i+1;
    exit when i>10;
  end loop;
  dbms_output.put_line('n!='||n);
end;
/

输出：n!=3628800

②while循环语句

while condition

loop

statements;

end loop;

set serveroutput on
declare 
  i binary_integer := 2;
  n binary_integer := 1;
begin
  while i<=10
  loop 
    n:=n*i;
    i:=i+1;
  end loop;
  dbms_output.put_line('n!='||n);
end;

输出：n!=3628800

③for循环语句

for loop_variable in [reverse] lower_bound..upper_bound

loop

statements;

end loop;

set serveroutput on
begin
  for i in 1..20 loop
    if mod(i,3)=0 then
      dbms_output.put_line(i);
    end if;
  end loop;
end;

八、游标

①显示游标

声明游标：cursor cursor_name[(parameter[,parameter]…)]

[return return_type] is select_statement;

parameter_name [in] datatype [{:=|default} expression]

cursor emp_info (dept_no number := 20)		--声明一个带参数的游标
  is
  select empno,ename,job,sal
  from emp where deptno=dept_no;

打开和关闭游标：open cursor_name [(value[,value]…)];

close emp_info;

open emp_info;
open emp_info(10);			--打开游标传入参数
close emp_info;

检索数据：fetch cursor_name into {variable_list|record_variable};

fetch emp_info into emp_record;

游标中包含一个指针，每执行一个fetch时，该指针将自动移动到结果集的下一行。如果在循环中使用fetch语句，这样每一次循环都会从表中读取一行数据，从而遍历游标中的每行数据。

set serveroutput on
declare
  cursor emp_info (dept_no number := 20)
  is
  select empno,ename,job,sal
  from emp where deptno=dept_no;
  type emp_type is record(
            empno number(4),
            ename varchar2(10),
            job varchar2(9),
            sal number(7,2)
  );
  emp_record emp_type;

begin
  open emp_info(10);  
  loop
    fetch emp_info into emp_record;
    exit when emp_info%notfound;     --当游标找不到直接退出循环
      dbms_output.put('行号'||emp_info%rowcount);
      dbms_output.put(','||emp_record.empno);
      dbms_output.put(','||emp_record.ename);
      dbms_output.put_line(','||emp_record.sal);
  end loop;
  close emp_info;
end;
/

输出：

行号1，7782，CLARK，MANAGER，2450

行号2，7839，KING，PRESIDENT，5000

行号3，7934，MILLER，CLERK，1300

游标的几个属性

%found：返回布尔类型的值。用于判断最近一次读取记录时是否有数据行返回，如果有则返回true，否则返回false

%notfound：返回布尔类型的值。与%found相反

%isopen：返回布尔类型的值。用于判断游标是否打开

%rowcount：返回数字类型的值。用于返回已经从游标中读取的记录数

②隐式游标

指非PL/SQL程序中定义的而且是在PL/SQL中使用update、delete语句时，Oracle系统自动分配的标。

因为修改和删除也得逐条进行，所以它们的操作与我们定义的显示游标类似

不需要打开，也不需要关闭，即在执行update、delete语句时自动打开和关闭。当使用隐式游标的属性时，需要在属性前加上SQL。Oracle创建隐式游标时，默认的游标名为SQL

set serveroutput on
declare
  var_rows number;
begin
  update emp
  set sal=sal+10
  where deptno=10;
  var_rows := SQL%rowcount;
  dbms_output.put_line('修改'||var_rows||'条记录');
end;
/

③游标for循环

不需要手动打开和关闭游标，也不需要手动判断游标是否还有返回记录，for语句中设置的循环变量本身久存储了当前检索记录的所有列值，不再需要定义变量接收记录值

for循环使用时，不能对游标进行open、fetch、close。如果游标有输入参数，则只能使用该参数的默认值

set serveroutput on
declare
  cursor emp_info(deptno:=20)
  is
  select empno,ename,job,sal
  from emp where deptno=dept_no;
  type emp_type is record
            (empno number(4),
            ename varchar2(10),
            job varchar2(9),
            sal number(7,2));
begin
  for current_row in emp_info
  loop
  dbms_output.put_line('行号'||emp_info%rowcount||current_row.ename);
  end loop;
end;
/

输出：

行号1XXX

行号2XXX

行号3XXX

行号4XXX

九、异常

①异常处理

产生异常时，若程序没有处理异常，则会被终止运行。

exception

when exception1 then

statements1;

when exception2 then

statements2;

[…]

when others then

statementsN; --放在异常语句最后(others)

②预定义异常

指Oracle系统为一些常见错误定义好的异常，如表中的主键值重复、除数为0等。如下表

sqlcode函数可以获取异常错误号，sqlerrm函数可以获取异常的具体描述信息

set serveroutput on
declare 
  var_num1 number;
  var_num2 number;
begin
  var_num1:=9;
  var_num2:=0;
  dbms_output.put_line('var_num1='||var_num1);
  dbms_output.put_line('var_num2='||var_num2);
  dbms_output.put_line(var_num1=||'/'||var_num2||'='||var_num1/var_num2);
end;
/

输出：

declare

第一行出现错误：

ORA-01476：除数为0

ORA-06512：在line9

zero_divide：“除数为0”异常。此时程序被终止，因为抛出异常后，程序中没有处理该异常的代码

添加对该异常处理后的代码如下

set serveroutput on
declare 
  var_num1 number;
  var_num2 number;
begin
  var_num1:=9;
  var_num2:=0;
  dbms_output.put_line('var_num1='||var_num1);
  dbms_output.put_line('var_num2='||var_num2);
  dbms_output.put_line(var_num1=||'/'||var_num2||'='||var_num1/var_num2);
exception
  when zero_divide then
    dbms_output.put_line('输入错误，除数不能为0');
end;
/

输出：

var_num1=9

var_num2=0

输入错误，除数不能为0

③非预定义异常

除了预定义异常，Oracle为其他一些异常只提供错误代码，这些同样需要处理，不过需要在PL/SQL块中使用pragma exception_int语句为其设置名称

pragma exception_init (exception_name,oracle_error_number);

set serveroutput on
declare
  foreignexception exception;
  pragma exception_init(foreignexception,-02291);
begin
  insert into emp(empno,ename,job,deptno)
  values(8900,'TOM','CLERK',70);
  exception
        when foreignexception then
        dbms_output.put_line('提供的部门编号不存在');
end;
/

输出：

提供的部门编号不存在

ORA-02291：违反完整性约束条件。该异常在Oracle预定义异常中不存在，故要用pragma为其设置名称才能处理

④自定义异常

通过使用raise触发

set serveroutput on
declare 
  no_record exception;
begin
  update emp
  set comm=comm+10
  where empno='8900';

  if sql%notfound then
    raise no_record;
  end if;

  exception
    when no_record then
    dbms_output.put_line('未更新任何记录');
end;
/

输出：

未更新任何记录