pl/sql表+记录习题2+动态游标

最新推荐文章于 2022-05-16 08:58:36 发布

lifei2199

最新推荐文章于 2022-05-16 08:58:36 发布

阅读量1.1k

点赞数

文章标签： table integer insert sql 数据库 jobs

本文链接：https://blog.csdn.net/lifei2199/article/details/7375290

版权

1.使用表类型,结构如下：
  varchar2(10);
使用for循环插入1到10（除了6和8)的数据到表变量中
declare
TYPE r_table_type IS TABLE OF resu.resu%TYPE
       INDEX BY BINARY_INTEGER;
       --------
       r_tab r_table_type;
begin
FOR i IN 1..10 LOOP
IF i <> 6 AND i <> 8 THEN
INSERT INTO resu
VALUES(i);
END IF;
r_tab(i)=i;
-------------

END LOOP;

end;

2.检索emp表中所有数据（编号，姓名，部门，工资）
将数据存储到表变量中，并将其工资〉8000的员工打印出来
-- Created on 2012-3-20 by NEW
declare
TYPE e_table_type IS TABLE OF emp%ROWTYPE
       INDEX BY BINARY_INTEGER;
       e_tab e_table_type;
BEGIN
     SELECT *
    BULK collect INTO e_tab
     FROM emp;
FOR i IN e_tab.FIRST..e_tab.LAST LOOP
IF e_tab(i).sal >500 THEN
dbms_output.put_line(e_tab(i).empno
                            ||','
                            || e_tab(i).empno
                            || ','
                            ||e_tab(i).deptno
                            || ','
                            ||e_tab(i).sal);
END IF;
END LOOP;

end;
3.汇总每个部门每个职位员工的最高及平均工资，包括显示部门名称
和职位名称，将数据存储到Test表中。要求使用表变量
DECLARE


        TYPE table_type IS TABLE OF test%Rowtype
        INDEX BY BINARY_INTEGER;

        tablename table_type;

        TYPE type_name IS RECORD
        (MAXX NUMBER,
        AVGG NUMBER,
        DEPTNOO NUMBER,
        JOBB VARCHAR2(9));
        recordtype type_name;

BEGIN

   SELECT MAX(sal),AVG(sal),deptno,job
BULK collect INTO tablename
   FROM emp
   GROUP BY deptno,job;

FOR i IN tablename.FIRST..tablename.LAST LOOP
   INSERT INTO test
   VALUES(
    tablename(i).maxx,
    tablename(i).avgg,
    tablename(i).deptnoo,
    tablename(i).jobb
    );
      END LOOP;
END;
_____________________________________________________________________________
PL/SQL Developer Test script 3.0
52
declare
--定义表
TYPE emp_table IS TABLE OF emp%ROWTYPE
       INDEX BY BINARY_INTEGER;
r_emp emp_table;
--定义职位id
TYPE emp_jobid IS TABLE OF emp.job%TYPE
       INDEX BY BINARY_INTEGER;
e_jobid emp_jobid;           -----------职位的表数据

--定义变量
ms emp.sal%TYPE;    ---------最大工资
ass emp.sal%TYPE;   --------------最小工资
di emp.deptno%TYPE;             ------部门名称
begin
SELECT distinct(job)
BULK COLLECT INTO e_jobid--------这问信息存到表数据中
FROM emp
------------选择条件是不同部门之间
WHERE deptno IN (SELECT DISTINCT(deptno) FROM emp);

FOR i IN e_jobid.FIRST..e_jobid.LAST LOOP
      dbms_output.put(e_jobid(i));
       SELECT MAX(sal)
       INTO ms
       FROM emp-------不同部门，不同职位之间的最高工资
       WHERE job=e_jobid(i);
       dbms_output.put(' '||'最高工资是'||ms);

       SELECT AVG(sal)
       INTO ass-------不同部门，不同职位之间的平均工资
       FROM emp
       WHERE job=e_jobid(i);
       dbms_output.put(' '||'平均工资是'||asS);

     /*   SELECT distinct(deptno)
      INTO di
      FROM emp
       WHERE job=e_jobid(i);
       dbms_output.put_line(' '||'所属部门'||di);*/

     SELECT MAX(deptno)
       INTO di
       FROM emp
       WHERE job=e_jobid(i);
       dbms_output.put_line(' '||'所属部门'||di);
END LOOP;

--------------有漏洞---必须是job_id完全不一样
end;
_________________________________________________________________________________________
DECLARE


        TYPE table_type IS TABLE OF test%Rowtype
        INDEX BY BINARY_INTEGER;

        tablename table_type;

    --    TYPE type_name IS RECORD
     --   (MAXX NUMBER,
      -- AVGG NUMBER,
       -- DEPTNOO NUMBER,
       -- JOBB VARCHAR2(9));

       -- recordtype type_name;


       -- TYPE table_type IS TABLE OF type_name
      -- INDEX BY BINARY_INTEGER;

      -- tablename table_type;

BEGIN

   SELECT MAX(sal),AVG(sal),deptno,job
BULK collect INTO tablename
   FROM emp
   GROUP BY deptno,job;

FOR i IN tablename.FIRST..tablename.LAST LOOP
   INSERT INTO test
   VALUES(
    tablename(i).maxx,
    tablename(i).avgg,
    tablename(i).deptnoo,
    tablename(i).jobb
    );
      END LOOP;
END;
__________________________________________________________________________________
游标做法
-- Created on 2012-3-22 by NEW
declare
CURSOR cur_name IS-----这里要添加别名
   SELECT MAX(salary) mm,AVG(salary) aa,department_id,job_id
-- BULK collect INTO tablename
   FROM employees
   GROUP BY department_id,job_id
   ORDER BY department_id;

   re_name cur_name%ROWTYPE;
begin
-- OPEN cur_name;
FOR re_name IN cur_name LOOP
dbms_output.put_line(re_name.department_id|| ','
                       ||re_name.job_id || ','
                       ||re_name.mm|| ','
                       ||re_name.aa);

END LOOP;
-- CLOSE cur_name;

end;
______________________________________________________________________________________
declare
TYPE m_type IS RECORD(
         depid NUMBER(4),
         depnm varchar2(25),
         jobid varchar2(20),
         jobnm varchar2(35),
         maxsal NUMBER(8,2),
         avgsal NUMBER(8,2)
                        );
r_sum m_type;

TYPE m_table IS TABLE OF m_type
         INDEX BY BINARY_INTEGER;

t_sum m_table;
begin
SELECT emp.department_id,
         dep.department_name,
         emp.job_id,
         job.job_title,
         MAX(emp.salary),
         AVG(emp.salary)
BULK COLLECT INTO t_sum
FROM   employees emp,
         departments dep,
         jobs job
WHERE emp.department_id = dep.department_id
AND    emp.job_id = job.job_id
GROUP BY emp.department_id,
           emp.job_id,
           dep.department_name,
           job.job_title
ORDER BY emp.department_id,
           emp.job_id;

FOR i IN t_sum.FIRST..t_sum.LAST LOOP
      r_sum := t_sum(i);

      INSERT INTO test3
      VALUES(r_sum.depid,r_sum.depnm,
             r_sum.jobid,r_sum.jobnm,
             r_sum.maxsal,r_sum.avgsal);
END LOOP;

COMMIT;
end;
______________________________________________________________________________
利用表和记录就可以不用创建表查询

-- Created on 2012-3-22 by NEW
declare
TYPE m_type IS RECORD(
         depid NUMBER(4),
         depnm varchar2(25),
         jobid varchar2(20),
         jobnm varchar2(35),
         maxsal NUMBER(8,2),
         avgsal NUMBER(8,2)
                        );
r_sum m_type;

TYPE m_table IS TABLE OF m_type---m_type是记录
         INDEX BY BINARY_INTEGER;

t_sum m_table;
begin
SELECT emp.department_id,
         dep.department_name,
         emp.job_id,
         job.job_title,
         MAX(emp.salary),
         AVG(emp.salary)
BULK COLLECT INTO t_sum
FROM   employees emp,
         departments dep,
         jobs job
WHERE emp.department_id = dep.department_id
AND    emp.job_id = job.job_id
GROUP BY emp.department_id,
           emp.job_id,
           dep.department_name,
           job.job_title
ORDER BY emp.department_id,
           emp.job_id;

dbms_output.put_line(SQL%ROWCOUNT);

FOR i IN t_sum.FIRST..t_sum.LAST LOOP
      r_sum := t_sum(i);--把表记录结果赋给记录

      dbms_output.put_line(
              r_sum.depid||r_sum.depnm||
             r_sum.jobid||r_sum.jobnm||
             r_sum.maxsal||r_sum.avgsal);
END LOOP;

--dbms_output.put_line(SQL%ROWCOUNT);

-- COMMIT;

--dbms_output.put_line(SQL%ROWCOUNT);
end;

总结：

/*记录是存储在多个字段中的一组相关的数据项，每个字段
都有自己的名字和数据类型,把字段的集合当作一个整体的逻辑单元,
主要用于从表中取出查询到的行数据
record中，引入type类型-------------------
例：emp_record.Job:= ‘CLERK‘;
SELECT empno,ename,job
INTO emp_record
FROM emp
WHERE ename= ‘SMITH‘
record可以看作是行的集合
2、声明的变量对应于数据库表或视图中列的集合，
在%ROWTYPE 之前加上数据库表名，
记录内字段的名字和数据类型参照表或视图中的列;
优点：不必知道数据库中列的数量和类型，运行期间列的数量和类型可能
发生改变，在select语句中使用该属性可以有效的检索表中的行

3、table类型，结果类似于其他语言中的数组类型，有两部分组成
--主键：binary_integer
--列：标量和记录类型
table类型没有长度的限制，可以动态增长
可以把其他记录类型，运用到table类型中，所以table更加灵活

TYPE m_type IS RECORD(
         depid NUMBER(4),
         depnm varchar2(25),
         jobid varchar2(20),
         jobnm varchar2(35),
         maxsal NUMBER(8,2),
         avgsal NUMBER(8,2)
                        );
r_sum m_type;

TYPE m_table IS TABLE OF m_type---m_type是记录
         INDEX BY BINARY_INTEGER;

t_sum m_table;

__________________________________________________________________________________
动态游标
在变量声明部分定义的游标是静态的，不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据，但还是有很大的局限性。通过采用动态游标，可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型，然后声明一个游标变量，游标对应的查询语句可以在程序的执行过程中动态地说明。
定义游标类型的语句如下：
TYPE 游标类型名 REF CURSOR;
声明游标变量的语句如下：
游标变量名游标类型名;
在可执行部分可以如下形式打开一个动态游标：
OPEN 游标变量名 FOR 查询语句字符串;
【训练2】按名字中包含的字母顺序分组显示雇员信息。
输入并运行以下程序：

Sql代码
declare
type cur_type is ref cursor;
cur cur_type;
rec scott.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;

运行结果为：

Sql代码
包含字母A的名字：
ALLEN
WARD
MARTIN
BLAKE
CLARK
ADAMS
JAMES
包含字母B的名字：
BLAKE
包含字母C的名字：
CLARK
SCOTT

说明：使用了二重循环，在外循环体中，动态生成游标的SELECT语句，然后打开。通过语句letter:=chr(ascii(letter)+1)可获得字母表中的下一个字母。

lifei2199

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
pl/sql表+记录习题2+动态游标

1.使用表类型,结构如下： varchar2(10);使用for循环插入1到10（除了6和8)的数据到表变量中declare TYPE r_table_type IS TABLE OF resu.resu%TYPE INDEX BY BINARY_INTEGER; -------- r_tab r_table_type;be
复制链接

扫一扫