Oracle——plsql代码编写

定义

PLSQL 是oracle对sql语言的过程化扩展，指在sql命令语言中增加了过程处理语句

在命令行打开DBMS输出结果 使用：set serveroutput on

结构

DECLARE
声明部分：在此声明PL/SQL用到的变量,类型及游标,以及局部的存储过程和函数
BEGIN
–执行部分:过程及sql语句,即程序的主要部分
Exception
–执行异常部分:错误处理
End;

游标

示例
每年入职员工的人数
游标:可以存储查询返回的多条数据
语法:
CURSOR 游标名 [(参数名 数据类型,参数名 数据类型,…)] is select 语句;
Eg:
Cursor c1 is select ename from emp;
游标的使用步骤:
1.打开游标: open c1; (打开游标执行查询)
2.取一行游标的值 :fetch c1 into pjob ; (取一行到变量)
3.关闭游标 :close c1;
游标的结束方式:exit when c1%notfound

创建过程
create or replace procedure getyear(cont out number,c80 out NUMBER ,c81 out NUMBER,c82 out NUMBER,
c87 out number)
AS
pyear VARCHAR2(10);
CURSOR c1 IS SELECT to_char(hiredate,‘yyyy’) FROM emp;
BEGIN
cont:=0;c80:=0;c81:=0;c82:=0;c87:=0;
OPEN c1;
loop
fetch c1 INTO pyear;
exit WHEN c1%notfound;
cont:=cont+1;
IF pyear=‘1980’ THEN c80:=c80+1;
elsif pyear=‘1981’ THEN c81:=c81+1;
elsif pyear=‘1982’ THEN c82:=c82+1;
elsif pyear=‘1987’ THEN c87:=c87+1;
END IF;
end loop;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE(‘查询完毕’);
END;

执行过程
DECLARE
TYPE se_num IS record(
cont NUMBER,
cont80 NUMBER,
cont81 NUMBER,
cont82 NUMBER,
cont87 NUMBER);
v_se se_num;
BEGIN
getyear(v_se.cont,v_se.cont80,v_se.cont81,v_se.cont82,v_se.cont87);
DBMS_OUTPUT.PUT_LINE(‘总人数:’||v_se.cont||’ 1980:’||v_se.cont80||’ 1981:’||v_se.cont81||
’ 1982:’||v_se.cont87);
END;
/

2.为员工涨工资.从最低工资起每人涨10%,但工资总额不能超过5万元,请计算涨工资的人数和长工资后的工资总额,并输出长工资人数及工资总额
代码：
CREATE OR REPLACE function upda(pempno IN NUMBER)return number
AS
counttol NUMBER:=0;
BEGIN
UPDATE emp SET sal=sal*1.1 WHERE empno=pempno;
SELECT sum(sal) INTO counttol FROM emp;
return counttol;
END;
/
DECLARE
psal emp.sal%TYPE;
pempno emp.empno%type;
counter number:=0;
counttol NUMBER:=0;
countbef NUMBER:=0;
CURSOR c1 IS SELECT empno,sal FROM emp ORDER BY sal;
exp exception;
BEGIN
OPEN c1;
loop
fetch c1 INTO pempno,psal;
exit WHEN c1%notfound;
select sum(sal) into countbef from emp;
counter:=counter+1;
counttol:=upda(pempno);
IF counttol>50000 THEN
raise exp;
END IF;
DBMS_OUTPUT.PUT_LINE(‘涨工资前总金额:’||countbef||‘涨工资人数:’||counter||’ 涨工资后总金额为:’||counttol);
COMMIT;
END loop;
CLOSE c1;
exception
WHEN exp THEN
DBMS_OUTPUT.PUT_LINE(‘涨工资前总金额:’||countbef||’ 涨工资人数:’||counter||’ 涨工资后总金额为:’||counttol);
dbms_output.put_line(‘涨工资后，总工资数大于50000,所以放弃本次涨工资’);
ROLLBACK;
END;
/