精通Oracle10编程SQL(6)访问ORACLE

fjln9eqx39

/*
*访问ORACLE
*/
--检索单行数据
--使用标量变量接收数据
DECLARE
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
select ename,sal into v_ename,v_sal
from emp where empno=&no;
dbms_output.put_line('雇员名：'||v_ename);
dbms_output.put_line('雇员薪水：'||v_sal);
END;
--使用记录变量接收数据
DECLARE
TYPE emp_record_type IS RECORD(
ename emp.ename%TYPE,sal emp.sal%TYPE);
emp_record emp_record_type;
BEGIN
select ename,sal INTO emp_record
from emp where empno=&no;
dbms_output.put_line('雇员名：'||emp_record.ename);
dbms_output.put_line('雇员薪水：'||emp_record.sal);
END;
--操纵数据
--插入数据
--在PL/SQL块中使用VALUES子句插入数据
DECLARE
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
BEGIN
v_deptno:=&no;
v_dname:='&name';
INSERT INTO dept(deptno,dname) values(v_deptno,v_dname);
END;
SELECT * FROM DEPT;
--在PL/SQL块中使用子查询插入数据
DECLARE
v_deptno emp.deptno%TYPE:=&no;
BEGIN
INSERT INTO employee
select * from emp where deptno=v_deptno;
end;
select * from employee;
select * from emp;
--更新数据
--使用表达式更新列值
DECLARE
v_deptno dept.deptno%TYPE:=&no;
v_loc dept.loc%TYPE:='&loc';
BEGIN
UPDATE dept set loc=v_loc where deptno=v_deptno;
END;
SELECT * FROM DEPT;
--使用子查询更新列值
--将与SCOTT岗位相同的所有雇员的工资和补助更新为与SCOTT完全相同
DECLARE
v_ename emp.ename%TYPE:='&name';
BEGIN
UPDATE emp set(sal,comm) =
(select sal,comm from emp where ename=v_ename)
where job=(select job from emp where ename=v_ename);
END;
select * from dept;
--删除数据
--使用变量删除数据
DECLARE
v_deptno dept.deptno%TYPE:=&no;
BEGIN
DELETE FROM dept WHERE deptno=v_deptno;
END;
--使用子查询删除数据
--根据输入的雇员名解雇雇员所在部门的所有雇员
--下例会删除SCOTT所在部门的所有雇员
DECLARE
v_ename emp.ename%TYPE:='&name';
BEGIN
delete from emp where deptno=(select deptno from emp where ename=v_ename);
END;
SELECT * FROM EMP;
--SQL游标
--SQL%ISOPEN:用于确定SQL游标是否已经打开。当在PL/SQL块中执行SELECT INTO，INSERT，UPDATE以及DELETE语句时，ORACLE会隐含地打开
--游标，并且在语句执行完成之后会隐含地关闭游标，所以对于开发人员来说该属性的值永远都是FALSE，并且在开发PL/SQL应用时不需要使用该游标属性
--SQL%FOUND:用于确定SQL语句执行是否成功。
--语句执行是否成功是根据是否有作用行来判断，当SQL语句有作用行时，其属性值为TRUE，当SQL语句没有作用行时，其属性值为FALSE
DECLARE
v_deptno emp.deptno%TYPE:=&no;
BEGIN
update emp set sal=sal*1.1
where deptno=v_deptno;
if SQL%FOUND THEN
DBMS_OUTPUT.put_line('语句执行成功');
else
DBMS_OUTPUT.put_line('该部门不存在雇员');
end if;
END;
--SQL%NOTFOUND:用于确定SQL语句执行是否成功
--语句执行是否成功是根据是否有作用行来判断，当SQL语句有作用行时，其属性值为FALSE，当SQL语句没有作用行时，其属性值为TRUE
DECLARE
v_deptno emp.deptno%TYPE:=&no;
BEGIN
update emp set sal=sal*1.1
where deptno=v_deptno;
if SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('该部门不存在雇员');
else
dbms_output.put_line('语句执行成功');
end if;
end;
--SQL%ROWCOUNT:游标属性SQL%ROWCOUNT用于返回SQL语句所作用的总计行数
DECLARE
v_deptno emp.deptno%TYPE:=&no;
BEGIN
update emp set sal=sal*1.1
where deptno=v_deptno;
dbms_output.put_line('修改了'||SQL%ROWCOUNT||'行');
END;

--事务控制语句
--在PL/SQL块中使用COMMIT和ROLLRACK语句
DECLARE
v_sal emp.sal%TYPE:=&salary;
v_ename emp.ename%TYPE:='&name';
BEGIN
update emp set sal=v_sal where ename=v_ename;
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
select * from emp;
--在PL/SQL块中使用ROLLBACK和SAVEPOINT语句
create table temp
(
tempid    NUMBER(10)
)
select * from dual;
select * from temp;
BEGIN
INSERT INTO temp VALUES(1);
SAVEPOINT a1;
INSERT INTO temp VALUES(2);
SAVEPOINT a2;
INSERT INTO temp VALUES(3);
SAVEPOINT a3;
ROLLBACK TO a2;
COMMIT;
END;
select * from temp;