验证中...
语言: SQL
分类: 数据库相关
最后更新于 2018-02-14 16:11
片段 1 片段 2 片段 3 片段 4
PLSQL的基础练习--游标的使用
原始数据 复制代码
--隐式游标的使用
--根据用户输入的员工号,更新指定员工的工资,比如工资涨100
BEGIN
UPDATE empnew SET sal = sal + 100 WHERE empno = &NO;
IF SQL%FOUND THEN
dbms_output.put_line('修改成功');
COMMIT;
ELSE
dbms_output.put_line('修改失败');
ROLLBACK;
END IF;
END;
--使用游标修改并删除数据使用 WHERE CURRENT OF empnew_cursor
--修改结果集当前行对应的数据库表中的数据行
--如果创建的游标需要执行更新或者删除的操作必须带有FOR UPDATE子句
--FOR UPDATE子句会将游标提取出来的数据进行行级锁定,这样在本会话期间
--其他用户的会话就不能对当前游标中的数据行进行更新操作。
DECLARE
--定义游标
CURSOR empnew_cursor IS SELECT empno,job FROM empnew FOR UPDATE;
BEGIN
FOR empnew_record IN empnew_cursor LOOP
dbms_output.put_line('工号'||empnew_record.empno||'的职位是:'||empnew_record.job);
IF empnew_record.job = 'PRESIDENT' THEN
UPDATE empnew SET sal = sal + 1000 WHERE CURRENT OF empnew_cursor;
ELSIF empnew_record.job = 'MANAGER' THEN
UPDATE empnew SET sal = sal + 500 WHERE CURRENT OF empnew_cursor;
ELSE
UPDATE empnew SET sal = sal + 300 WHERE CURRENT OF empnew_cursor;
END IF;
END LOOP;
COMMIT;
END;
--使用of子句在特定表上加行共享锁
--例:输入部门号,显示该部门的部门名称及员工的姓名,
--并删除该部门下的这些员工
DECLARE
CURSOR empnew_cursor IS
SELECT d.dname dname, e.ename ename
FROM empnew e JOIN dept d ON e.deptno = d.deptno
WHERE e.deptno = &deptno
FOR UPDATE OF e.deptno;
BEGIN
FOR empnew_record IN empnew_cursor LOOP
dbms_output.put_line('部门名称:'||empnew_record.dname||'员工名:'||empnew_record.ename);
DELETE FROM empnew WHERE CURRENT OF empnew_cursor;
END LOOP;
COMMIT;
END;
PLSQL基础练习--异常的定义
原始数据 复制代码
--预定义异常
DECLARE
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE:=&salary;
BEGIN
SELECT ename INTO v_name FROM emp WHERE sal = v_sal;
dbms_output.put_line(v_name||'的工资是:'||v_sal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('没有该工资的员工');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.put_line('多个员工具有该工资');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('其他异常');
END;
--非预定义异常
DECLARE
--1:定义非预定义异常的标识符
e_fk EXCEPTION;
--2:把oracle错误与异常建立关联
-- -2292 违反约束外键错误号
PRAGMA EXCEPTION_INIT(e_fk, -2292);
BEGIN
DELETE FROM dept WHERE deptno = &deptno;
EXCEPTION
WHEN e_fk THEN
--3:捕捉并处理异常
dbms_output.put_line('此部门下有员工,不能删除!');
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE||'####'||SQLERRM);
END;
--自定义异常
DECLARE
v_empno emp.empno%TYPE:=&empno;
e_no_result EXCEPTION;
BEGIN
UPDATE emp SET sal = sal + 100 WHERE empno = v_empno;
IF SQL%NOTFOUND THEN
RAISE e_no_result;
ELSE
COMMIT;
END IF;
EXCEPTION
WHEN e_no_result THEN
dbms_output.put_line('数据更新失败');
WHEN OTHERS THEN
dbms_output.put_line('其他错误');
END;
--使用SQLCODE、SQLERRM定义异常
--deptno 具有外键约束
DECLARE
v_empno emp.empno%TYPE:= &empno;
v_ename emp.ename%TYPE:= '&ename';
v_deptno emp.deptno%TYPE:= &deptno;
BEGIN
INSERT INTO emp(empno,ename,deptno)VALUES(v_empno,v_ename,v_deptno);
IF SQL%FOUND THEN
dbms_output.put_line('数据插入成功!');
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('错误号:'||SQLCODE);
dbms_output.put_line('错误信息:'||SQLERRM);
END;
PLSQL基础练习--存储过程和存储函数
原始数据 复制代码
--1:创建输入参数的存储过程
CREATE OR REPLACE PROCEDURE proc1
(v_empno IN empnew.empno%TYPE)
IS
BEGIN
--根据员工号删除指定的员工信息
DELETE FROM empnew WHERE empno = v_empno;
--判断是否删除成功
IF SQL%NOTFOUND THEN
-- -20000~ -20999之间
raise_application_error(-20008, '指定删除的员工不存在!');
ELSE
dbms_output.put_line('删除成功!');
END IF;
END;
--2:创建带有输出参数的存储过程
CREATE OR REPLACE PROCEDURE proc2
(v_deptno IN NUMBER, v_avgsal OUT NUMBER, v_cnt OUT NUMBER)
IS
BEGIN
SELECT AVG(sal),COUNT(*)
INTO v_avgsal, v_cnt
FROM emp
WHERE deptno = v_deptno;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('没有此部门!');
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
--3:创建带有输入输出参数的存储过程
CREATE OR REPLACE PROCEDURE proc3
(v_num1 IN OUT NUMBER, v_num2 IN OUT NUMBER)
AS
v_temp NUMBER := 0;
BEGIN
v_temp := v_num1;
v_num1 := v_num2;
v_num2 := v_temp;
END;
--1:创建带有输入参数的存储函数
--根据部门编号返回该部门的总工资
CREATE OR REPLACE FUNCTION func1
(v_deptno IN NUMBER)
RETURN NUMBER
IS
v_sumsal NUMBER;
BEGIN
SELECT SUM(sal) INTO v_sumsal FROM emp WHERE deptno = v_deptno;
RETURN v_sumsal;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('没有此部门!');
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
--2:创建带有输出参数的存储函数
--根据员工号输出员工的姓名和员工的工资,并且返回员工的年收入
CREATE OR REPLACE FUNCTION func2
(v_empno IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_sal OUT emp.sal%TYPE)
RETURN NUMBER
IS
v_salsum NUMBER;
BEGIN
SELECT ename, sal, (sal+nvl(comm,0))*12
INTO v_name, v_sal, v_salsum
FROM emp
WHERE empno = v_empno;
RETURN v_salsum;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('没有此员工!');
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
--3:创建带有输入输出参数的存储函数
--求两个数的平方和,并输出两个数的平方
CREATE OR REPLACE FUNCTION func3
(n1 IN OUT NUMBER, n2 IN OUT NUMBER)
RETURN NUMBER
AS
BEGIN
n1 := n1 * n1;
n2 := n2 * n2;
RETURN n1 + n2;
END;
/******************************************************************************************/
--调用无参的存储过程
BEGIN
first_proc;
END;
--调用带有输入参数的存储过程
BEGIN
proc1(123);
END;
--调用带有输出参数的存储过程
DECLARE
v_avgsalary NUMBER;
v_count NUMBER;
BEGIN
proc2(10, v_avgsalary, v_count);
dbms_output.put_line('平均工资:'||v_avgsalary);
dbms_output.put_line('总人数:'||v_count);
END;
--调用带有输入输出参数的存储过程
DECLARE
v_n1 NUMBER := 5;
v_n2 NUMBER := 10;
BEGIN
proc3(v_n1, v_n2);
dbms_output.put_line('N1:'||v_n1);
dbms_output.put_line('N2:'||v_n2);
END;
--删除存储过程
DROP PROCEDURE proc1;
/************************************************************************/
--调用无参的存储函数
BEGIN
dbms_output.put_line(first_func);
END;
--调用带有输入参数的存储函数
BEGIN
dbms_output.put_line('部门的工资总额'||func1(&NO));
END;
--调用带有输出参数的存储函数
DECLARE
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;
v_salsum NUMBER;
BEGIN
v_salsum := func2(&NO, v_name, v_sal);
dbms_output.put_line('姓名:'||v_name);
dbms_output.put_line('工资:'||v_sal);
dbms_output.put_line('年收入:'||v_salsum);
END;
--调用带有输入输出参数的存储函数
DECLARE
v_n1 NUMBER := 5;
v_n2 NUMBER := 6;
v_sum NUMBER;
BEGIN
v_sum := func3(v_n1, v_n2);
dbms_output.put_line('N1的平方:'||v_n1);
dbms_output.put_line('N2的平方:'||v_n1);
dbms_output.put_line('平方和:'||v_sum);
END;
--删除存储函数
DROP FUNCTION func1;
PLSQL的基础练习--包
原始数据 复制代码
--创建包规范
CREATE OR REPLACE PACKAGE emp_package
IS
--添加员工信息的存储过程
PROCEDURE add_emp_proc
(v_empno IN emp.empno%TYPE,
v_ename IN emp.ename%TYPE,
v_sal IN emp.sal%TYPE,
v_deptno IN emp.deptno%TYPE);
--删除员工信息的存储过程
PROCEDURE del_emp_proc
(v_empno IN emp.empno%TYPE);
END emp_package;
--创建包体
CREATE OR REPLACE PACKAGE BODY emp_package
IS
--实现添加员工信息的存储过程
PROCEDURE add_emp_proc
(v_empno IN emp.empno%TYPE,
v_ename IN emp.ename%TYPE,
v_sal IN emp.sal%TYPE,
v_deptno IN emp.deptno%TYPE)
IS
e_2291 EXCEPTION;
PRAGMA EXCEPTION_INIT(e_2291,-2291);
BEGIN
INSERT INTO emp(empno, ename, sal, deptno)VALUES(v_empno, v_ename, v_sal, v_deptno);
EXCEPTION
WHEN dup_val_on_index THEN
raise_application_error(-20001,'员工号不能重复');
WHEN e_2291 THEN
raise_application_error(-20008,'部门号不存在');
END;
--实现删除员工信息的存储过程
PROCEDURE del_emp_proc
(v_empno IN emp.empno%TYPE)
IS
BEGIN
--根据员工号删除指定的员工信息
DELETE FROM emp WHERE empno = v_empno;
--判断是否删除成功
IF SQL%NOTFOUND THEN
raise_application_error(-20009,'指定删除的员工不存在');
ELSE
dbms_output.put_line('删除成功');
END IF;
END;
END emp_package;
/********************************************************************/
--包的调用1
--调用emp_package中的add_emp_proc过程来增加员工
DECLARE
v_empno emp.empno%TYPE:=&empno;
v_ename emp.ename%TYPE:=&ename;
v_sal emp.sal%TYPE:=&sal;
v_deptno emp.deptno%TYPE:=&deptno;
e_dup_val EXCEPTION;
e_no_dept EXCEPTION;
PRAGMA EXCEPTION_INIT(e_dup_val, -20001);
PRAGMA EXCEPTION_INIT(e_no_dept, -20008);
BEGIN
--调用emp_package.add_emp_proc过程增加员工
emp_package.add_emp_proc(v_empno, v_ename, v_sal, v_deptno);
COMMIT;
EXCEPTION
WHEN e_dup_val THEN
dbms_output.put_line(SQLERRM);
WHEN e_no_dept THEN
dbms_output.put_line(SQLERRM);
ROLLBACK;
END;
--包的调用2
--调用emp_package中del_emp_proc过程增加员工
DECLARE
v_empno emp.empno%TYPE := &empno;
e_no_emp EXCEPTION;
PRAGMA EXCEPTION_INIT(e_no_emp,-20009);
BEGIN
--调用emp_package.del_emp_proc来删除员工信息
emp_package.del_emp_proc(v_empno);
COMMIT;
EXCEPTION
WHEN e_no_emp THEN
dbms_output.put_line(SQLERRM);
ROLLBACK;
END;
--调用emp_sal_pkg包下的过程和函数
DECLARE
v_empno emp.empno%TYPE := &empno;
v_salary emp.sal%TYPE;
e_no_emp EXCEPTION;
PRAGMA EXCEPTION_INIT(e_no_emp,-20008);
BEGIN
v_salary:= emp_sal_pkg.get_sal(v_empno);
emp_sal_pkg.upd_sal(v_empno, v_salary);
COMMIT;
EXCEPTION
WHEN e_no_emp THEN
dbms_output.put_line(SQLERRM);
END;
--删除包规范和包体
DROP PACKAGE emp_package;
--删除包体
DROP PACKAGE BODY emp_package;
/*********************************************************************************/
--子程序重载
--根据员工号或员工姓名获取员工的信息
--根据员工号或员工姓名删除员工的信息
--创建包规范
CREATE OR REPLACE PACKAGE overload_pkg
IS
FUNCTION get_info(eno NUMBER)RETURN emp%ROWTYPE;
FUNCTION get_info(NAME VARCHAR2)RETURN emp%ROWTYPE;
PROCEDURE del_emp(eno NUMBER);
PROCEDURE del_emp(NAME VARCHAR2);
END;
--创建包体
CREATE OR REPLACE PACKAGE BODY overload_pkg
IS
FUNCTION get_info(eno NUMBER) RETURN emp%ROWTYPE
IS
emp_record emp%ROWTYPE;
BEGIN
SELECT * INTO emp_record FROM emp WHERE empno = eno;
RETURN emp_record;
EXCEPTION
WHEN no_data_found THEN
raise_application_error(-20020.'不存在此员工!');
END;
FUNCTION get_info(name VARCHAR2)RETURN emp%ROWTYPE
IS
emp_record emp%ROWTYPE;
BEGIN
SELECT * INTO emp_record FROM emp WHERE empno = eno;
RETURN emp_record;
EXCEPTION
WHEN no_data_found THEN
raise_application_error(-20020.'不存在此员工!');
END;
PROCEDURE del_emp(eno NUMBER)
IS
BEGIN
DELETE FROM emp WHERE empno = eno;
IF SQL%NOTFOUND THEN
raise_application_error(-20020,'不存在此员工');
END IF;
END;
PROCEDURE del_emp(NAME VARCHAR2)
IS
BEGIN
DELETE FROM emp WHERE ename = NAME;
IF SQL%NOTFOUND THEN
raise_application_error(-20020,'不存在此员工');
END IF;
END;
END overload_pkg;
--调用函数overload_pkg.get_info
--根据员工号查询员工信息
DECLARE
emp_record emp%ROWTYPE;
e_no_emp EXCEPTION;
PRAGMA EXCEPTION_INIT(e_no_emp, -20020);
BEGIN
emp_record := overload_pkg.get_info(&NO);
dbms_output.put_line('员工号:'||emp_record.empno||',姓名:'||emp_record.ename||',工资:'||emp_record.sal);
EXCEPTION
WHEN e_no_emp THEN
dbms_output.put_line(SQLERRM);
END;
--调用函数overload_pkg.get_info
--根据员工姓名查询员工信息
DECLARE
emp_record emp%ROWTYPE;
e_no_emp EXCEPTION;
PRAGMA EXCEPTION_INIT(e_no_emp, -20020);
BEGIN
emp_record := overload_pkg.get_info('&name');
dbms_output.put_line('员工号:'||emp_record.empno||',姓名:'||emp_record.ename||',工资:'||emp_record.sal);
EXCEPTION
WHEN e_no_emp THEN
dbms_output.put_line(SQLERRM);
END;
--调用过程overload_pkg.del_emp
--根据员工号删除员工信息
DECLARE
e_no_emp EXCEPTION
PRAGMA EXCEPTION_INIT(e_no_emp, -20020);
BEGIN
overload_pkg.del_emp(&NO);
COMMIT;
EXCEPTION
WHEN e_no_emp THEN
dbms_output.put_line(SQLERRM);
ROLLBACK;
END;
--调用过程overload_pkg.del_emp
--根据员工姓名删除员工信息
DECLARE
e_no_emp EXCEPTION
PRAGMA EXCEPTION_INIT(e_no_emp, -20020);
BEGIN
overload_pkg.del_emp(&NO);
COMMIT;
EXCEPTION
WHEN e_no_emp THEN
dbms_output.put_line(SQLERRM);
ROLLBACK;
END;

评论列表( 0 )

你可以在登录后,发表评论

9_float_left_people 9_float_left_close