第六章 PL/SQL和服务器之间的交互
<p>[TOC]</p>
<h2>6.2 在L/SQL中使用select 语句提取数据的实例</h2>
<pre><code>--使用select语句提取数据的实例
declare
v_deptno dept.deptno%TYPE;
v_loc varchar2(38);
begin
select deptno,loc
into v_deptno,v_loc
from dept
where dname='ACCOUNTING';
end;
--只能查询出一行数据，多或者少都会报错
declare
v_deptno dept.deptno%TYPE;
v_loc varchar2(38);
begin
select deptno,loc
into v_deptno,v_loc
from dept
where dname='accounting';
end;
--返回多行也会报错
declare
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
begin
select ename,sal
into v_ename,v_sal
from emp
where job='CLERK';
end;
/
--输出变量两种方法，与表值对比，可以知道程序是否正确
--1
SQL> variable g_deptno number
SQL> variable g_loc varchar2(38)
declare
v_deptno dept.deptno%TYPE;
v_loc varchar2(38);
begin
select deptno,loc
into v_deptno,v_loc
from dept
where dname='ACCOUNTING';
:g_deptno := v_deptno;
:g_loc := v_loc;
end;
/
print g_deptno
print g_loc
--2
SQL> set serveroutput on
declare
v_deptno dept.deptno%TYPE;
v_loc varchar2(38);
begin
select deptno,loc
into v_deptno,v_loc
from dept
where dname='ACCOUNTING';
dbms_output.put_line(v_deptno);
dbms_output.put_line(v_loc);
end;
--表值
select deptno,loc from dept where dname='ACCOUNTING';</code></pre>
<h2>6.3 利用分组函数从表中提取数据</h2>
<pre><code>--允许输出
SQL> set serveroutput on
--关闭替换变量新旧显示
SQL> set verify off
declare
v_sum_sal emp.sal%type;
v_deptno number not null := &p_department_id;
begin
select sum(sal) --group function
into v_sum_sal
from emp
where deptno = v_deptno;
dbms_output.put_line(v_deptno||'号部门的工资总和为：'||v_sum_sal);
end;
/
--保存以上文件至/home/oracle/script/dept_sum_salary.sql
--再通过如下执行，注意两个sql*plus 命令也保存
SQL> @/home/oracle/script/dept_sum_salary.sql
Connected.
Enter value for p_department_id: 30
30号部门的工资总和为：9400
PL/SQL procedure successfully completed.
--最后通过查询验证数据，需要注意的是变量的精度
select deptno,sum(sal) from emp group by deptno order by deptno;</code></pre>
<h2>6.4 PL/SQL变量与列同名的问题及命名惯例</h2>
<pre><code>--命名相同引发的问题：不要用与表列名相同的标识符作为变量名
--只能是where 的变量有二义有错
declare
ename emp.ename%TYPE;
hiredate emp.hiredate%TYPE;
sal emp.sal%TYPE;
empno emp.empno%TYPE := 7369 ; --v_empno emp.empno%TYPE :=7369;
begin
select ename,hiredate,sal
into ename,hiredate,sal
from emp
where empno = empno; --where empno = v_empno;
end;
/
--正确
declare
ename emp.ename%TYPE;
hiredate emp.hiredate%TYPE;
sal emp.sal%TYPE;
v_empno emp.empno%TYPE :=7369;
begin
select ename,hiredate,sal
into ename,hiredate,sal
from emp
where empno = v_empno;
end;
/</code></pre>
<h2>6.5 数据库中数据维护概述和准备工作</h2>
<pre><code>--复制副表用于实验
CREATE TABLE emp_pl AS SELECT * FROM emp;
CREATE TABLE dept_pl AS SELECT * FROM dept;
SELECT * FROM emp_pl;
SELECT * FROM dept_pl;
SELECT * FROM user_tables;
--创建一个序列
CREATE SEQUENCE deptid_sequence
START WITH 50
INCREMENT BY 5
MAXVALUE 99
NOCACHE
NOCYCLE;
--查看序列创建的情况
SELECT sequence_name,min_value,max_value,increment_by,last_number FROM user_sequences;</code></pre>
<h2>6.6 插入数据、修改数据和删除数据</h2>
<pre><code>--插入数据，要commit;
BEGIN
INSERT INTO dept(deptno,dname,loc)
VALUES (deptid_sequence.nextval,'人力资源部','B1栋5楼C区');
COMMIT;
END;
--修改数据，要commit;
SELECT empno,ename,job,sal FROM emp WHERE job='CLERK';
SQL> SET VERIFY OFF
DECLARE
v_sal_increase emp.sal%TYPE := &p_salary_increase;
BEGIN
UPDATE emp
SET sal = sal + v_sal_increase
WHERE job = 'CLERK';
COMMIT;
END;
--删除数据，要commit;
SELECT * FROM emp_pl WHERE job = 'SALESMAN' ORDER BY sal;
SQL> SET VERIFY OFF
DECLARE
v_job emp_pl.job%TYPE := '&p_job';
v_sal emp_pl.sal%TYPE := &p_sal;
BEGIN
DELETE FROM emp_pl
WHERE job = v_job
AND sal > v_sal;
COMMIT;
END;</code></pre>
<h2>6.7 merge 语句</h2>
<pre><code>--merge 语句语法
MERGE INTO table_name table_alias
USING (TABLE|VIEW|sub_query) ALIAS
ON (JOIN condition)
WHEN MATCHED THEN
UPDATE SET
col1=col1_val,
col2=col2_val
WHEN NOT MATCHED THEN
INSERT (col_list)
VALUES (col_values);</code></pre>
<h2>6.8 合并数据库中的数据行</h2>
<pre><code>--准备数据
CREATE TABLE copy_emp AS SELECT * FROM emp WHERE deptno=20;
SELECT * FROM copy_emp;
--合并数据
--ORA-38104: Columns referenced in the ON Clause cannot be updated: "C"."EMPNO"
BEGIN
MERGE INTO copy_emp c
USING emp e
ON (c.empno=e.empno)
WHEN MATCHED THEN
UPDATE SET
--c.empno = e.empno, --匹配条件不能用于修改
c.ename = e.ename,
c.job = e.job,
c.mgr = e.mgr,
c.hiredate = e.hiredate,
c.sal = e.sal,
c.comm = e.comm,
c.deptno = e.deptno
WHEN NOT MATCHED THEN
INSERT (c.empno,c.ename,c.job,c.mgr,c.hiredate,c.sal,c.comm,c.deptno)
VALUES (e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno);
COMMIT;--也需要commit;
END;</code></pre>