Maison > Article > base de données > Comment utiliser le curseur dans Oracle
Comment utiliser le curseur dans Oracle
- 小云云original
- 2017-12-11 14:51:201660parcourir
Cet article présente principalement l'utilisation de base du curseur dans Oracle. Il est relativement complet après avoir lu cet article, j'espère que vous pourrez maîtriser comment utiliser le curseur dans Oracle.
Requête
L'instruction SELECT est utilisée pour interroger les données de la base de données. Lors de l'utilisation de l'instruction SELECT en PL/SQL, vous devez Utilisé avec la clause INTO, la valeur de retour
de la requête est affectée à la variable dans la clause INTO et la déclaration de variable est dans DELCARE. La syntaxe SELECT INTO est la suivante :
SELECT [DISTICT|ALL]{*|column[,column,...]}
INTO (variable[,variable,...] |record)
FROM {table|(sub-query)}[alias]
WHERE............
L'instruction SELECT en PL/SQL ne renvoie qu'une seule ligne de données. S'il y a plus d'une ligne de données, alors un curseur explicite doit être utilisé (nous parlerons des curseurs plus tard) et la clause INTO doit avoir le même nombre de colonnes que la clause SELECT. Les variables d'enregistrement peuvent également être incluses dans la clause INTO.
Attribut %TYPE
type et taille. Cette méthode d'affectation dynamique est très utile. Par exemple, si le type de données et la taille de la colonne référencée par la variable changent, si %TYPE,
est utilisé, l'utilisateur n'a pas à modifier le code, sinon le code doit le faire. être modifié.
v_empno SCOTT.EMP.EMPNO%TYPE; v_salary EMP.SALARY%TYPE;Non seulement les noms de colonnes peuvent utiliser %TYPE, mais également les variables, curseurs, enregistrements ou déclarations Les constantes peuvent utiliser %TYPE. Ceci est utile pour définir des variables du même type de données
.
DELCARE V_A NUMBER(5):=10; V_B V_A%TYPE:=15; V_C V_A%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE ('V_A='||V_A||'V_B='||V_B||'V_C='||V_C); END SQL>/ V_A=10 V_B=15 V_C= PL/SQL procedure successfully completed. SQL>
Autres instructions DML
en SQL. Nous avons déjà discuté de l'utilisation des instructions DML
imbriqué, faites attention à la portée de la variable.
CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number) AS v_ename EMP.ENAME%TYPE; BEGIN SELECT ename INTO v_ename FROM emp WHERE empno=p_empno; INSERT INTO FORMER_EMP(EMPNO,ENAME) VALUES (p_empno,v_ename); DELETE FROM emp WHERE empno=p_empno; UPDATE former_emp SET date_deleted=SYSDATE WHERE empno=p_empno; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee Number Not Found!'); END
Résultats de la déclaration DML
. Lorsqu'une instruction DML est exécutée, PL/SQL ouvre un curseur intégré et traite les résultats. Un curseur est une zone en mémoire qui conserve les résultats de la requête
Un curseur est ouvert lorsqu'une instruction DML est exécutée et fermé une fois terminé. . Les curseurs implicites n'utilisent que trois attributs : SQL%FOUND,
SQL%FOUND et SQL%NOTFOUND
: DELETE et UPDATE, au moins une ligne est DELETE ou UPDATE.<.>. TRUE : SELECT INTO renvoie au moins une ligne
Lorsque SQL%FOUND est TRUE, SQL%NOTFOUND est FALSE.
SQL%ROWCOUNT
Avant d'exécuter une instruction DML, la valeur de SQL%ROWCOUNT est NULL Pour l'instruction SELECT INTO, si l'exécution réussit
SQL%ISOPEN
<.>SQL%ISOPEN est une valeur booléenne qui est VRAIE si le curseur est ouvert et FALSE si le curseur est fermé. SQL%
ISOPEN est toujours FAUX pour les curseurs implicites car les curseurs implicites
. La marque est ouverte lorsque l'instruction DML est exécutée et fermée immédiatement à la fin.
Déclaration de contrôle des transactions
Une transaction est une unité de travail logique qui peut inclure une ou plusieurs instructions DML d'aide au contrôle des transactions. utilisateurs Assurer la cohérence des données. Si une instruction DML dans l'unité logique de contrôle de transaction échoue, la transaction entière sera annulée. En PL/SQL, les utilisateurs peuvent explicitement utiliser les instructions COMMIT, ROLLBACK, SAVEPOINT et
SET TRANSACTION.
L'instruction COMMIT termine la transaction, enregistre définitivement les modifications dans la base de données et libère tous les LOCK. ROLLBACK met fin à la transaction en cours et libère tous les LOCK,
mais n'enregistre aucune modification dans le base de données, SAVEPOI
NT est utilisé pour définir le point intermédiaire Lorsque la transaction appelle trop d'opérations de base de données, le point intermédiaire est très utile pour définir les attributs de la transaction, tels que la lecture. -niveau d'écriture et d'isolement.
Curseur explicite
Lorsque la requête renvoie plus d'une ligne, un curseur explicite est requis à ce moment. , l'utilisateur L'instruction select into ne peut pas être utilisée. PL/SQL gère les curseurs implicites. Le curseur implicite est ouvert au démarrage de la requête et se ferme automatiquement à la fin de la requête. Les curseurs explicites sont déclarés dans la partie déclaration du bloc PL/SQL, ouverts dans la partie exécution ou dans la partie gestion des exceptions, récupèrent les données et sont fermés.
Utiliser des curseurs
这里要做一个声明，我们所说的游标通常是指显式游标，因此从现在起没有特别指明的情况，我们所说的 声明游标 语法： CURSOR cursor_name IS select_statement; 在PL/SQL中游标名是一个未声明变量，不能给游标名赋值或用于表达式中。 例： 在游标定义中SELECT语句中不一定非要表可以是视图，也可以从多个表或视图中选择的列，甚至可以使用* 打开游标 使用游标中的值之前应该首先打开游标，打开游标初始化查询处理。打开游标的语法是： cursor_name是在声明部分定义的游标名。 例： 关闭游标 语法： 例： 从游标提取数据 从游标得到一行数据使用FETCH命令。每一次提取数据后，游标都指向结果集的下一行。语法如下： FETCH cursor_name INTO variable[,variable,...] 对于SELECT定义的游标的每一列，FETCH变量列表都应该有一个变量与之相对应，变量的类型也要相同。 例： 这段代码无疑是非常麻烦的，如果有多行返回结果，可以使用循环并用游标属性为结束循环的条件，以这 记录变量 定义一个记录变量使用TYPE命令和%ROWTYPE，关于%ROWsTYPE的更多信息请参阅相关资料。 记录变量用于从游标中提取数据行，当游标选择很多列的时候，那么使用记录比为每列声明一个变量要方 当在表上使用%ROWTYPE并将从游标中取出的值放入记录中时，如果要选择表中所有列，那么在SELECT子句 例： %ROWTYPE也可以用游标名来定义，这样的话就必须要首先声明游标： 带参数的游标 与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情 CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement; 定义参数的语法如下： Parameter_name [IN] data_type[{:=|DEFAULT} value] 与存储过程不同的是，游标只能接受传递的值，而不能返回值。参数只定义数据类型，没有大小。 另外可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。游标中定义的参数只是一个占位符，在别处引用该参数不一定可靠。 在打开游标时给参数赋值，语法如下： OPEN cursor_name[value[,value]....]; 参数值可以是文字或变量。 例： 游标FOR循环 在大多数时候我们在设计程序的时候都遵循下面的步骤： 1、打开游标 可以简单的把这一类代码称为游标用于循环。但还有一种循环与这种类型不相同，这就是FOR循环，用于 游标FOR循环的语法如下： 下面我们用for循环重写上面的例子： 在游标FOR循环中使用查询 在游标FOR循环中可以定义查询，由于没有显式声明所以游标没有名字，记录名通过游标查询来定义。 游标中的子查询 语法如下： 可以看出与SQL中的子查询没有什么区别。 游标中的更新和删除 在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。显式游标只有在需要获得多行数据的情 语法： FOR UPDATE [OF [schema.]table.column[,[schema.]table.column].. 在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。 在UPDATE和DELETE中使用WHERE CURRENT OF子串的语法如下： WHERE{CURRENT OF cursor_name|search_condition} 例： 下面再分享一下另外一则游标使用方法的代码，具体如下： 相关推荐：
游标都是指显式游标。要在程序中使用游标，必须首先声明游标。 DELCARE
CURSOR C_EMP IS SELECT empno,ename,salary
FROM emp
WHERE salary>2000
ORDER BY ename;
........
BEGIN
来选择所有的列 。 OPEN cursor_name
OPEN C_EMP;
CLOSE cursor_name
CLOSE C_EMP;
SET SERVERIUTPUT ON
DECLARE
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
CURSOR c_emp IS SELECT ename,salary FROM emp;
BEGIN
OPEN c_emp;
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);
CLOSE c_emp;
END
种方式提取数据，程序的可读性和简洁性都大为提高，下面我们使用循环重新写上面的程序： SET SERVERIUTPUT ON
DECLARE
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
CURSOR c_emp IS SELECT ename,salary FROM emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_ename,v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);
END
便得多。
中使用*比将所有列名列出来要安全得多。 SET SERVERIUTPUT ON
DECLARE
R_emp EMP%ROWTYPE;
CURSOR c_emp IS SELECT * FROM emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE('Salary of Employee'||r_emp.ename||'is'|| r_emp.salary);
END LOOP;
CLOSE c_emp;
END;
SET SERVERIUTPUT ON
DECLARE
CURSOR c_emp IS SELECT ename,salary FROM emp;
R_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE('Salary of Employee'||r_emp.ename||'is'|| r_emp.salary);
END LOOP;
CLOSE c_emp;
END;
况非常有用。它的语法如下： DECALRE
CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename
r_dept DEPT%ROWTYPE;
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
v_tot_salary EMP.SALARY%TYPE;
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO r_dept;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
OPEN c_emp(r_dept.deptno);
LOOP
FETCH c_emp INTO v_ename,v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
CLOSE c_dept;
END;
2、开始循环
3、从游标中取值
4、检查那一行被返回
5、处理
6、关闭循环
7、关闭游标
FOR循环的游标按照正常的声明方式声明，它的优点在于不需要显式的打开、关闭、取数据，测试数据的存在、定义存放数据的变量等等。 FOR record_name IN
(corsor_name[(parameter[,parameter]...)]
| (query_difinition)
LOOP
statements
END LOOP;
DECALRE
CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename
v_tot_salary EMP.SALARY%TYPE;
BEGIN
FOR r_dept IN c_dept LOOP
DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
FOR r_emp IN c_emp(r_dept.deptno) LOOP
DBMS_OUTPUT.PUT_LINE('Name:' || v_ename || 'salary:' || v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
END;
DECALRE
v_tot_salary EMP.SALARY%TYPE;
BEGIN
FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP
DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
FOR r_emp IN (SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename) LOOP
DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
END;
CURSOR C1 IS SELECT * FROM emp
WHERE deptno NOT IN (SELECT deptno
FROM dept
WHERE dname!='ACCOUNTING');
况下使用。PL/SQL提供了仅仅使 用游标就可以执行删除或更新记录的方法。
UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的
数据。要使用这个方法，在声明游标 时必须使用FOR UPDATE子串，当对话使用FOR UPDATE子串打开一个游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其
他对象只能查询这些数据行，不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。
[nowait]
如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。 DELCARE
CURSOR c1 IS SELECT empno,salary
FROM emp
WHERE comm IS NULL
FOR UPDATE OF comm;
v_comm NUMBER(10,2);
BEGIN
FOR r1 IN c1 LOOP
IF r1.salary<500 THEN
v_comm:=r1.salary*0.25;
ELSEIF r1.salary<1000 THEN
v_comm:=r1.salary*0.20;
ELSEIF r1.salary<3000 THEN
v_comm:=r1.salary*0.15;
ELSE
v_comm:=r1.salary*0.12;
END IF;
UPDATE emp;
SET comm=v_comm
WHERE CURRENT OF c1l;
END LOOP;
END
-声明游标
--宗地表的调查日期LANDINFO_RESEARCHDATE
--复制到流程表的权属调查时间FLOW_REASEARCHTIME
DECLARE
cursor cur_sel_all is select LANDINFO_RESEARCHDATE,LANDINFO_LANDNO from t_leoa_landinfo; --定义游标
l_date t_leoa_landinfo.landinfo_researchdate%type; --声明变量分别保存t_leoa_landinfo的各列
l_landNo t_leoa_landinfo.landinfo_landno%type;
begin
open cur_sel_all;
loop --循环取数，并将游标数据填充到返回纪录集合中
fetch cur_sel_all into l_date,l_landNo;
exit when cur_sel_all%NOTFOUND; --循环退出条件
if cur_sel_all%FOUND then --获取数据
update T_LEOA_BOOKFLOW t2 set FLOW_REASEARCHTIME = l_date where l_landNo = t2.landinfo_landno;
end if;
end loop;
close cur_sel_all;
end;
-- 声明游标；CURSOR cursor_name IS select_statement
--For 循环游标
--（1）定义游标
--（2）定义游标变量
--（3）使用for循环来使用这个游标
declare
--类型定义
cursor c_job
is
select empno,ename,job,sal
from emp
where job='MANAGER';
--定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
c_row c_job%rowtype;
begin
for c_row in c_job loop
dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
end loop;
end;
--Fetch游标
--使用的时候必须要明确的打开和关闭
declare
--类型定义
cursor c_job
is
select empno,ename,job,sal
from emp
where job='MANAGER';
--定义一个游标变量
c_row c_job%rowtype;
begin
open c_job;
loop
--提取一行数据到c_row
fetch c_job into c_row;
--判读是否提取到值，没取到值就退出
--取到值c_job%notfound 是false
--取不到值c_job%notfound 是true
exit when c_job%notfound;
dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
end loop;
--关闭游标
close c_job;
end;
--1：任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。
begin
update emp set ENAME='ALEARK' WHERE EMPNO=7469;
if sql%isopen then
dbms_output.put_line('Openging');
else
dbms_output.put_line('closing');
end if;
if sql%found then
dbms_output.put_line('游标指向了有效行');--判断游标是否指向有效行
else
dbms_output.put_line('Sorry');
end if;
if sql%notfound then
dbms_output.put_line('Also Sorry');
else
dbms_output.put_line('Haha');
end if;
dbms_output.put_line(sql%rowcount);
exception
when no_data_found then
dbms_output.put_line('Sorry No data');
when too_many_rows then
dbms_output.put_line('Too Many rows');
end;
declare
empNumber emp.EMPNO%TYPE;
empName emp.ENAME%TYPE;
begin
if sql%isopen then
dbms_output.put_line('Cursor is opinging');
else
dbms_output.put_line('Cursor is Close');
end if;
if sql%notfound then
dbms_output.put_line('No Value');
else
dbms_output.put_line(empNumber);
end if;
dbms_output.put_line(sql%rowcount);
dbms_output.put_line('-------------');
select EMPNO,ENAME into empNumber,empName from emp where EMPNO=7499;
dbms_output.put_line(sql%rowcount);
if sql%isopen then
dbms_output.put_line('Cursor is opinging');
else
dbms_output.put_line('Cursor is Closing');
end if;
if sql%notfound then
dbms_output.put_line('No Value');
else
dbms_output.put_line(empNumber);
end if;
exception
when no_data_found then
dbms_output.put_line('No Value');
when too_many_rows then
dbms_output.put_line('too many rows');
end;
--2,使用游标和loop循环来显示所有部门的名称
--游标声明
declare
cursor csr_dept
is
--select语句
select DNAME
from Depth;
--指定行指针,这句话应该是指定和csr_dept行类型相同的变量
row_dept csr_dept%rowtype;
begin
--for循环
for row_dept in csr_dept loop
dbms_output.put_line('部门名称:'||row_dept.DNAME);
end loop;
end;
--3,使用游标和while循环来显示所有部门的的地理位置（用%found属性）
declare
--游标声明
cursor csr_TestWhile
is
--select语句
select LOC
from Depth;
--指定行指针
row_loc csr_TestWhile%rowtype;
begin
--打开游标
open csr_TestWhile;
--给第一行喂数据
fetch csr_TestWhile into row_loc;
--测试是否有数据，并执行循环
while csr_TestWhile%found loop
dbms_output.put_line('部门地点：'||row_loc.LOC);
--给下一行喂数据
fetch csr_TestWhile into row_loc;
end loop;
close csr_TestWhile;
end;
select * from emp
--4,接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)
--CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
--定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]
declare
CURSOR
c_dept(p_deptNo number)
is
select * from emp where emp.depno=p_deptNo;
r_emp emp%rowtype;
begin
for r_emp in c_dept(20) loop
dbms_output.put_line('员工号：'||r_emp.EMPNO||'员工名：'||r_emp.ENAME||'工资：'||r_emp.SAL);
end loop;
end;
select * from emp
--5：向游标传递一个工种，显示此工种的所有雇员的所有信息(使用参数游标)
declare
cursor
c_job(p_job nvarchar2)
is
select * from emp where JOB=p_job;
r_job emp%rowtype;
begin
for r_job in c_job('CLERK') loop
dbms_output.put_line('员工号'||r_job.EMPNO||' '||'员工姓名'||r_job.ENAME);
end loop;
end;
SELECT * FROM EMP
--6：用更新游标来为雇员加佣金：(用if实现,创建一个与emp表一摸一样的emp1表，对emp1表进行修改操作),并将更新前后的数据输出出来
--http://zheng12tian.iteye.com/blog/815770
create table emp1 as select * from emp;
declare
cursor
csr_Update
is
select * from emp1 for update OF SAL;
empInfo csr_Update%rowtype;
saleInfo emp1.SAL%TYPE;
begin
FOR empInfo IN csr_Update LOOP
IF empInfo.SAL<1500 THEN
saleInfo:=empInfo.SAL*1.2;
elsif empInfo.SAL<2000 THEN
saleInfo:=empInfo.SAL*1.5;
elsif empInfo.SAL<3000 THEN
saleInfo:=empInfo.SAL*2;
END IF;
UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;
END LOOP;
END;
--7:编写一个PL/SQL程序块，对名字以‘A'或‘S'开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作)
declare
cursor
csr_AddSal
is
select * from emp1 where ENAME LIKE 'A%' OR ENAME LIKE 'S%' for update OF SAL;
r_AddSal csr_AddSal%rowtype;
saleInfo emp1.SAL%TYPE;
begin
for r_AddSal in csr_AddSal loop
dbms_output.put_line(r_AddSal.ENAME||'原来的工资:'||r_AddSal.SAL);
saleInfo:=r_AddSal.SAL*1.1;
UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_AddSal;
end loop;
end;
--8：编写一个PL/SQL程序块，对所有的salesman增加佣金(comm)500
declare
cursor
csr_AddComm(p_job nvarchar2)
is
select * from emp1 where JOB=p_job FOR UPDATE OF COMM;
r_AddComm emp1%rowtype;
commInfo emp1.comm%type;
begin
for r_AddComm in csr_AddComm('SALESMAN') LOOP
commInfo:=r_AddComm.COMM+500;
UPDATE EMP1 SET COMM=commInfo where CURRENT OF csr_AddComm;
END LOOP;
END;
--9：编写一个PL/SQL程序块，以提升2个资格最老的职员为MANAGER（工作时间越长，资格越老）
--（提示：可以定义一个变量作为计数器控制游标只提取两条数据；也可以在声明游标的时候把雇员中资格最老的两个人查出来放到游标中。）
declare
cursor crs_testComput
is
select * from emp1 order by HIREDATE asc;
--计数器
top_two number:=2;
r_testComput crs_testComput%rowtype;
begin
open crs_testComput;
FETCH crs_testComput INTO r_testComput;
while top_two>0 loop
dbms_output.put_line('员工姓名：'||r_testComput.ENAME||' 工作时间：'||r_testComput.HIREDATE);
--计速器减一
top_two:=top_two-1;
FETCH crs_testComput INTO r_testComput;
end loop;
close crs_testComput;
end;
--10：编写一个PL/SQL程序块，对所有雇员按他们的基本薪水(sal)的20%为他们加薪，
--如果增加的薪水大于300就取消加薪(对emp1表进行修改操作，并将更新前后的数据输出出来)
declare
cursor
crs_UpadateSal
is
select * from emp1 for update of SAL;
r_UpdateSal crs_UpadateSal%rowtype;
salAdd emp1.sal%type;
salInfo emp1.sal%type;
begin
for r_UpdateSal in crs_UpadateSal loop
salAdd:= r_UpdateSal.SAL*0.2;
if salAdd>300 then
salInfo:=r_UpdateSal.SAL;
dbms_output.put_line(r_UpdateSal.ENAME||': 加薪失败。'||'薪水维持在：'||r_UpdateSal.SAL);
else
salInfo:=r_UpdateSal.SAL+salAdd;
dbms_output.put_line(r_UpdateSal.ENAME||': 加薪成功.'||'薪水变为：'||salInfo);
end if;
update emp1 set SAL=salInfo where current of crs_UpadateSal;
end loop;
end;
--11:将每位员工工作了多少年零多少月零多少天输出出来
--近似
--CEIL(n)函数：取大于等于数值n的最小整数
--FLOOR(n)函数：取小于等于数值n的最大整数
--truc的用法 http://publish.it168.com/2005/1028/20051028034101.shtml
declare
cursor
crs_WorkDay
is
select ENAME,HIREDATE, trunc(months_between(sysdate, hiredate) / 12) AS SPANDYEARS,
trunc(mod(months_between(sysdate, hiredate), 12)) AS months,
trunc(mod(mod(sysdate - hiredate, 365), 12)) as days
from emp1;
r_WorkDay crs_WorkDay%rowtype;
begin
for r_WorkDay in crs_WorkDay loop
dbms_output.put_line(r_WorkDay.ENAME||'已经工作了'||r_WorkDay.SPANDYEARS||'年,零'||r_WorkDay.months||'月,零'||r_WorkDay.days||'天');
end loop;
end;
--12:输入部门编号，按照下列加薪比例执行(用CASE实现，创建一个emp1表，修改emp1表的数据),并将更新前后的数据输出出来
-- deptno raise(%)
-- 10 5%
-- 20 10%
-- 30 15%
-- 40 20%
-- 加薪比例以现有的sal为标准
--CASE expr WHEN comparison_expr THEN return_expr
--[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END
declare
cursor
crs_caseTest
is
select * from emp1 for update of SAL;
r_caseTest crs_caseTest%rowtype;
salInfo emp1.sal%type;
begin
for r_caseTest in crs_caseTest loop
case
when r_caseTest.DEPNO=10
THEN salInfo:=r_caseTest.SAL*1.05;
when r_caseTest.DEPNO=20
THEN salInfo:=r_caseTest.SAL*1.1;
when r_caseTest.DEPNO=30
THEN salInfo:=r_caseTest.SAL*1.15;
when r_caseTest.DEPNO=40
THEN salInfo:=r_caseTest.SAL*1.2;
end case;
update emp1 set SAL=salInfo where current of crs_caseTest;
end loop;
end;
--13:对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号。
--AVG([distinct|all] expr) over (analytic_clause)
---作用：
--按照analytic_clause中的规则求分组平均值。
--分析函数语法:
--FUNCTION_NAME(<argument>,<argument>...)
--OVER
--(<Partition-Clause><Order-by-Clause><Windowing Clause>)
--PARTITION子句
--按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组
select * from emp1
DECLARE
CURSOR
crs_testAvg
IS
select EMPNO,ENAME,JOB,SAL,DEPNO,AVG(SAL) OVER (PARTITION BY DEPNO ) AS DEP_AVG
FROM EMP1 for update of SAL;
r_testAvg crs_testAvg%rowtype;
salInfo emp1.sal%type;
begin
for r_testAvg in crs_testAvg loop
if r_testAvg.SAL>r_testAvg.DEP_AVG then
salInfo:=r_testAvg.SAL-50;
end if;
update emp1 set SAL=salInfo where current of crs_testAvg;
end loop;
end;
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!