存储过程和函数
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
存储过程和函数
1：创建一个可以向dept表中插入一行的过程
CREATE OR REPLACE PROCEDURE pro_c
AS
BEGIN
INSERT INTO emp V ALUES(8902,'FORD','ANAL YST',7566,to_date('1981-12-3','yyyy-MM-dd'),3000.00,null,20); END;
2:创建一个过程，传入雇员名，返回该雇员的薪水（薪金+佣金）
CREATE OR REPLACE PROCEDURE pro_2(names V ARCHAR2,sals OUT NUMBER )
AS
BEGIN
SELECT sal+nvl(comm,0) INTO sals FROM emp WHERE ename=names;
END;
--调用存储过程
DECLARE
sa NUMBER;
BEGIN
pro_2('SCOTT',sa);
dbms_output.put_line('薪水为：'||pro_2('SCOTT',sa));
END;
3：创建一个过程，传入雇员号，和薪水及增长比例（10%=0.1）。其中薪水为in out参数！更新薪水为新薪水用一个PL/SQL程序块来调用此过程，其传入到过程中的参数都是用户输入得到的
CREATE OR REPLACE PROCEDURE pro_3(num NUMBER,sal IN OUT NUMBER,increase NUMBER)
AS
BEGIN
UPDATE emp SET sal=sal*(1+increase) WHERE empno=num;
COMMIT;
SELECT sal INTO sal FROM emp WHERE empno=num;
END;
--调用存储过程
DECLARE
num NUMBER;
incre NUMBER;
sal NUMBER;
BEGIN
pro_3(&num,sal,&incre);
dbms_output.put_line('薪水为：'||sal);
END;
4：编写一个函数，以deptno为标准，返回此部门所有雇员的整体薪水
CREATE OR REPLACE Function fff_4 RETURN NUMBER
IS
CURSOR cu IS SELECT deptno d,SUM(sal) co FROM emp GROUP BY deptno; BEGIN
FOR i IN cu LOOP
dbms_output.put_line(i.co||' 部门'||i.d);
END LOOP;
RETURN 0;
END;
--调用存储过程
DECLARE
num NUMBER;
BEGIN
num:=fff_4;
END;
5：编写一个函数，接收deptno，和name（out），返回此部门的名称和地址CREATE OR REPLACE Function f_5(num NUMBER) RETURN dept%ROWTYPE
IS
de dept%ROWTYPE;
BEGIN
SELECT * INTO de FROM dept WHERE deptno=num;
RETURN de;
END;
--调用存储过程
DECLARE
de dept%ROWTYPE;
BEGIN
de:=f_5(10);
dbms_output.put_line(de.DNAME||de.loc);
END;
6；编写一个过程以显示所指定雇员名的雇员部门名和位置
CREATE OR REPLACE PROCEDURE p_6(name Varchar2,de OUT dept%ROWTYPE) IS
BEGIN
select * INTO de from dept where deptno=(select deptno from emp where ename=name); END;
--调用存储过程
DECLARE
de dept%ROWTYPE;
BEGIN
p_6('SCOTT',de);
dbms_output.put_line(de.DNAME||' '||de.loc);
END;
7；编写一个给特殊雇员加薪10%的过程，这之后，检查如果已经雇佣该雇员超过了60个月，则给他额外加薪300。
CREATE OR REPLACE PROCEDURE p_7
IS
CURSOR cu IS SELECT * FROM emp FOR UPDATE;
BEGIN
FOR i IN cu LOOP
UPDA TE emp SET sal=sal*1.1 WHERE CURRENT OF cu;
IF months_between(sysdate,i.hiredate)>60 THEN
UPDA TE emp SET sal=sal+300 WHERE CURRENT OF cu;
END IF;
END LOOP;
END;
exec p_7;
8：编写一个函数一检查所指定雇员的薪水是否在有效范围内，不同职位的薪水范围为：clerk 1500-2500
salesman 2501-2500
analyst 3501-4500
others 4501-n
CREATE OR REPLACE PROCEDURE(NAME V ARCHAR2) p_8
IS
sa NUMBER;
job Varchar2(20);
BOOLEAN flag:=false;
BEGIN
SELECT sal,job INTO sa,job FROM emp WHERE ename=NAME;
IF job='CLERK'AND sal>=1500 AND sal<=2500 THEN flag=TRUE;
ELSIF job='SALESMAN'AND sal>=1500 AND sal<=2500 THEN flag=TRUE;
ELSIF job='ANALYST'AND sal>=1500 AND sal<=2500 THEN flag=TRUE;