Oracle 基础笔记 3

Oracle 基础笔记3 (PL/SQL 1)

基本结构

PL/SQL结构

declare

   声明部分

begin

    执行部分

   exception

   异常处理部分

end;

变量声明

<变量名> 类型[:=初始值];

特殊类型

字段%type

表%rowtype

常量声明

<变量名> CONSTANT 类型:=初始值;

全局变量声明

VARIABLE <变量名> 类型;

使用全局变量

：<变量名>

查看全局变量

print <变量名>

赋值运算赋：=

使用select <列名> into <变量名> from <表名> where <条件>

（select into 语句的返回结果只能为一行）

用户交互输入

<变量> ：= ‘&变量’

注意oracle的用户互输入是先接受用户输入的所有值后在执行语句；所以不能使用循环进行用户交互输入；

条件控制语句

if <条件1> then

       语句

[elseif <条件2> then

       语句

          .

          .

elseif <条件n> then

        语句]

[else

     语句]

end if;

循环控制语句

1.loop

loop

     语句;

      exit when <条件>

end loop;

2.while loop

while <条件>

loop

    语句;

end loop;

3.for

for <循环变量> in 下限..上限

loop

    语句;

end loop;

eg:

for v_index in 1..5
loop
    dbms_output.put_line(v_index);
end loop;

输出结果为12345。

则相当于for(int i =1; i<=5;i++)

 

null语句

null;

表示没有操作

注释使用

单行注释：--

多行注释

/*

…

*/

异常处理

exception

    when <异常类型> then

            语句;

     when others then

           语句;

 

游标

定义：cursor <游标名> is <select 语句> [for update | for update of 字段名];

[for update | for update of 字段]--给游标加锁，既是在程序中有”update”,”insert”,”delete”语句对数据库操作时，游标自动给指定的表或者字段加锁，防止同时有别的程序对指定的表或字段进行”update”,”insert”,”delete”操作

在使用“delete”，“update”后还可以在程序中使用cursor of <游标名>子句引用当前行

操作: open <游标名> --打开游标

fetch <游标名> into 变量1，变量2，变量3，…，变量n;

fetch<游标名> into 行对象;--取出游标当前位置的值

close <游标名> --  关闭游标

属性：

%notfound --如果fetch语句失败，则该属性为”true”,否则为”false”;

%found --如果fetch语句成功，则该属性为”true”,否则为“false”;

%rowcount --当前行的行数;

%isopen --如果游标是开则返回”true”,否则返回为”false”;

loop循环

eg:

DECLARE 
     cursor c_1 is select * from emp;     --定义游标
     r c_1%rowtype;      --定义一个行对象，用于获得游标的值
BEGIN
     if c_1%isopen then
          CLOSE c_1;
     end if;               
     OPEN c_1;          --判断游标是否打开.如果开了将其关闭，然后在打开
     dbms_output.put_line('行号 姓名 薪水');
     LOOP
     FETCH c_1 INTO r;     --取值
     EXIT WHEN c_1%NOTFOUND;     --如果游标没有取到值，退出循环.
     dbms_output.put_line(c_1%rowcount||' '||r.ename||' '||r.sal);    --输出结果,需要 set serverout on 才能显示.
     END LOOP;
END;

for循环

eg:

 

DECLARE 
     cursor c_1 is select ename,sal from emp;     --定义游标     
BEGIN
     dbms_output.put_line('行号 姓名 薪水');
     FOR i IN c_1         --for循环中的循环变量i为c_1%rowtype类型;
     LOOP
     dbms_output.put_line(c_1%rowcount||' '||i.ename||' '||i.sal);    --输出结果,需要 set serverout on 才能显示.
     END LOOP;
END; 

for循环使用游标是在循环开始前自动打开游标，并且自动取值到循环结束后，自动关闭游标.

游标加锁

eg:

 

DECLARE 
     cursor c_1 is select ename,sal from emp for update of sal;     --定义游标对emp表的sal字段加锁.     
BEGIN
     dbms_output.put_line('行号 姓名 薪水');
     FOR i IN c_1         --for循环中的循环变量i为c_1%rowtype类型;
     LOOP
     UPDATE EMP set sal=sal+100 WHERE CURRENT OF c_1; --表示对当前行的sal进行跟新.
     END LOOP;
     FOR i IN c_1        
     LOOP
     dbms_output.put_line(c_1%rowcount||' '||i.ename||' '||i.sal);    --输出结果,需要 set serverout on 才能显示.
     END LOOP;
END; 

带参数游标

定义:CURSOR <游标名>(参数列表) IS <SELECT 语句> [FOR UPDATE | FOR UPDATE OF 字段];

eg:

DECLARE 
     cursor c_1(name emp.ename%type) is select ename,sal from emp where ename=name;     --定义游标     
BEGIN
     dbms_output.put_line('行号 姓名 薪水');
     FOR i IN c_1('&name')         --for循环中的循环变量i为c_1%rowtype类型;
     LOOP
     dbms_output.put_line(c_1%rowcount||' '||i.ename||' '||i.sal);    --输出结果,需要 set serverout on 才能显示.
     END LOOP;
END; 

隐式游标

隐试游标游标是系统自动生成的。每执行一个DML语句就会产生一个隐试游标，起名字为SQL;

隐试游标不能进行"OPEN" ,"CLOSE","FETCH"这些操作;

 

属性:
    %NOTFOUND --如果DML语句没有影响到任何一行时，则该属性为"TRUE"，否则为"FALSE";
    %FOUND --如果DML语句影响到一行或一行以上时，则该属性为"TRUE"，否则为"FALSE";
    %ROWCOUNT --返回游标当最后一行的行数;

个人认为隐试游标的作用是判断一个DML语句；
eg:

BEGIN
    DELETE FROM EMP WHERE empno=&a;
    IF SQL%NOTFOUND THEN
        dbms_output.put_line('empno不存在');
    END IF;
   IF SQL%ROWCOUNT>0 THEN
        dbms_output.put_line('删除成功');
    END IF;
END;