暂时自学部分——Orcale游标存储过程触发器

最新推荐文章于 2022-08-04 16:57:17 发布

从炸毛到秃顶

最新推荐文章于 2022-08-04 16:57:17 发布

阅读量213

点赞数

分类专栏： # Oracle

本文链接：https://blog.csdn.net/rosemaryyoudu/article/details/86503357

版权

Oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、游标（光标）

1.什么是游标：

a.学术性定义：游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的优点就是用于临时存储从数据库中提取的数据块。

b.理解性定义：1）.在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

简而言之就是，把数据放在内存中，操作内存，最后把结果丢回数据库。

2）.游标是处理结果集的一种机制吧，它可以定位到结果集中的某一行，多数据进行读写，也可以移动游标定位到你所需要的行中进行操作数据。结果集，结果集就是select查询之后返回的所有行数据的集合。

2.游标的作用：

a.定位到结果集中的某一行

b.对当前位置的数据进行读写

c.可以对结果集中的数据单独操作，而不是整行执行相同的操作

d.是面向集合的数据库管理系统和面向行的程序设计之间的桥梁

3.游标的生命周期

a.打开游标-- open cursor，此步骤在 UGA 里申请一块内存给游标使用，这个时候游标还没有与sql语句关联。
b.解析游标-- sql与游标关联起来，解析sql的内容（包括执行计划），解析后的内容会被加载到共享池中（share pool- - library cache）。在UGA申请的内存用来保存指向这个共享游标（share cursor）在library cache中的位置。
c.定义输出变量-- 如果sql语句返回数据，必须先定义接收数据的变量。这一点不仅对查询语句很重要，对于使用 returning 自居的delete、insert和update 语句也很重要。
d.绑定输入变量-- 如果sql语句使用了绑定变量，必须提供他们的值。绑定的过程是不做什么检查。如果指定了无效的数据，执行的过程中会爆出一个运行时错误。
e.执行游标-- 执行跟游标关联的sql。注意数据库并非总是在这一步做重要的事情。事实上，对于很多类型的查询语句来说，真正的处理过程通常会被推迟到fetch数据阶段。
f.获取游标-- 如果sql语句返回数据，这一步会接受这些数据。特别是在查询语句中，大部分的处理工作都是在这一步进行的。在查询语句中，可能只会读取部分记录，换句话讲，游标有可能在取到所有记录前被关闭。
g.关闭游标-- 释放UGA中与这个游标有关的资源，从而这些资源可供其他的游标使用。在library cache中的share cursor不会被清除，它会继续保留在library cache 中，等待被重用（软解析重用）。

4.游标的分类

a.隐式游标

理解性定义：1.在前述程序中用到的SELECT...INTO...(

                                       CREATE TABLE 新表
                                        AS
                                        SELECT * FROM 旧表 )这个语句是将从一个表复制数据，然后把数据插入到另一个新表中。

查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。

2.如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是：
* 插入操作：INSERT。
* 更新操作：UPDATE。
* 删除操作：DELETE。

* 单行查询操作：SELECT ... INTO ...。

知识点复习：sql语言分类：a.DQL：数据查询语言

b.DML：数据操作语言

c.DDL：数据定义语言。用来创建表视图等，这个是隐形提交，不能回滚。

d.DCL：数据控制语言。用来授予或者回收访问数据库的某种特权。比如：回滚，授权，提交。（提交又分为自动提交（AutoComment on），隐形提交(ALTER，AUDIT， COMMENT，CONNECT，CREATE， DISCONNECT,DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE， RENAME)，显式提交（comment））

3.游标的作用：当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过 SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。

4.代码的运用

                                    SQL运行语法
隐式游标的属性   返回值类型              意义   
SQL%ROWCOUNT    整型           代表DML语句成功执行的数据行数   
SQL%FOUND       布尔型         值为TRUE代表插入、删除、更新或单行查询操作成功   
SQL%NOTFOUND    布尔型         与SQL%FOUND属性返回值相反   
SQL%ISOPEN      布尔型         DML执行过程中为真，结束后为假

下面所用表就是Oracle自带的雇员表

一、使用隐形游标查询
BEGIN
   FOR empa IN (SELECT ename,sal  FROM emp) 
   LOOP
     dbms_output.put_line('ename:'||empa.ename||',sal:'||empa.sal);
   END LOOP;
END;

修改系列

输入代码 

BEGIN  
        UPDATE emp SET sal=sal+100 WHERE empno=1234;   
        IF SQL%FOUND THEN    
        DBMS_OUTPUT.PUT_LINE('1！');   （成功）
        COMMIT;    
        ELSE  
        DBMS_OUTPUT.PUT_LINE('2！');  （失败） 
         END IF;    
        END; 

显示为： 2锛?
这个出现乱码，没转换字符

b.显式游标

1.显式游标的定义

1．声明游标 
在DECLEAR部分按以下格式声明游标：
 详细声明
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

            这里的参数对应是：
                cursor_name：游标名称。
                Local：作用域为局部，只在定义它的批处理，存储过程或触发器中有效。
                Global：作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标。
                [Local | Global]:默认为local。

                Forward_Only:指定游标智能从第一行滚到最后一行。Fetch Next是唯一支持的提取选                    
                项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字，默认为 
                Dynamic游标。如果Forward_Only和Scroll没有指定，Static、KeySet、Dynamic游标默 
                认为Scroll，Fast_Forward默认为Forward_Only

                Static:静态游标
                KeySet：键集游标
                Dynamic：动态游标，不支持Absolute提取选项

                Fast_Forward：指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦 
                Scroll或For_Update，就不能指定他啦。

                Read_Only:不能通过游标对数据进行删改。
                
                Scroll_Locks：将行读入游标是，锁定这些行，确保删除或更新一定会成功。如果指定啦    
                Fast_Forward或Static，就不能指定他啦。

                Optimistic：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位 
                删除不成功。当将行读入游标时，sqlserver不锁定行，它改用timestamp列值的比较结果                                
                来确定行读入游标后是否发生了修改，如果表不行timestamp列，它改用校验和值进行确 
                定。如果已修改改行，则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则 
                不能指定他。

                Type_Warning:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信 
                息。
                For Update[of column_name ,....] :定义游标中可更新的列。


大致声明
CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] 
IS SELECT语句; （本人常用）




参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。 
SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。

2．打开游标 
在可执行部分，按以下格式打开游标：
 
OPEN 游标名[(实际参数1[，实际参数2...])]; 
ex: open [ Global ] cursor_name | cursor_variable_name
    说明：
    cursor_name:游标名
    cursor_variable_name：游标变量名称，该变量引用了一个游标。
打开游标时，SELECT语句的查询结果就被传送到了游标工作区。 

3．提取数据 
在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。 

Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from ]
[Global] cursor_name
[into @variable_name[,....]]
或 
FETCH 游标名 INTO 记录变量; 
        参数说明：
            Frist：结果集的第一行
            Prior：当前位置的上一行
            Next：当前位置的下一行
            Last：最后一行
            Absoute n：从游标的第一行开始数，第n行。
            Relative n：从当前位置数，第n行。
            Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。


游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。 
下面对这两种格式进行说明： 
第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。 
第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。 
定义记录变量的方法如下： 
变量名 表名|游标名%ROWTYPE； 
其中的表必须存在，游标名也必须先定义。 
4．关闭游标 

CLOSE 游标名; 

显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。 
以下是使用显式游标的一个简单练习。

2.显式游标的运用例子

1.查询emp表里工资最低那个人（不记得名字了）
  declare   
         v_ename VARCHAR2(10);   
         v_job VARCHAR2(10);   
         CURSOR emp_cursor IS    
         SELECT ename,job FROM emp WHERE empno=7788;   
         BEGIN  
     OPEN emp_cursor;   
    FETCH emp_cursor INTO v_ename,v_job;   
        DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);   
        CLOSE emp_cursor;   
        END;  

2.循环查找
     DECLARE  
      CURSOR emp_cursor IS    
          SELECT empno, ename FROM emp;   
            BEGIN  
                FOR Emp_record IN emp_cursor LOOP    （循环次数和游标获取的行数一致）  
                    DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);   
                END LOOP;   
            END;  

这个循环查找忽略了游标打开，提取和关闭这三点，而emp_record是隐含定义的记录变量

3.按名字中包含的字母顺序分组显示雇员信息。 
declare    
 type cur_type is ref cursor;   
 cur cur_type;   （经试验，这个需要要，难怪手动试着添加的时候凉了好久）
 rec emp%rowtype;   
 str varchar2(50);   
 letter char:= 'A';   
begin  
        loop           
         str:= 'select ename from emp where ename like ''%'||letter||'%''';   
         open cur for str;   
         dbms_output.put_line('包含字母'||letter||'的名字：');   
          loop   
         fetch cur into rec.ename;   
         exit when cur%notfound;   
        dbms_output.put_line(rec.ename);   
end loop;   
  exit when letter='Z';   
  letter:=chr(ascii(letter)+1);   
 end loop;   
end;

游标学习来着两个博主的博客

https://blog.csdn.net/liyong199012/article/details/8948952这个是主要参考对象

https://blog.csdn.net/zhh1072773034/article/details/53185033这个博主的就很详细了，还有视图啥的，有耐心的可以看这位博主的。