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)可获得字母表中的下一个字母。
扫一扫
435
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
