<!-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} @font-face {font-family:"/@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; mso-pagination:none; font-size:10.5pt; mso-bidi-font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:宋体; mso-font-kerning:1.0pt;} /* Page Definitions */ @page {mso-page-border-surround-header:no; mso-page-border-surround-footer:no;} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:42.55pt; mso-footer-margin:49.6pt; mso-paper-source:0; layout-grid:15.6pt;} div.Section1 {page:Section1;} -->
PL/SQL:访问数据库
注:在pl/sql中不能嵌入DCL及DDL语句
使用标量变量接收数据。
DECLARE
v_ename emp.ename%TYPE;
v_sal emp.empno%TYPE;
BEGIN
SELECT ename,sal INTO v_ename,v_sal
FROM emp
WHERE empno=7369;
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;
/
雇员名:SMITH
雇员薪水3000
嵌入注意事项
NO_DATA_FOUND例外
TOO_MANY_ROWS例外:当SELECT INTO 返回多条数据时会触发TOO_MANY_ROWS例外
WHERE子句使用变量:变量名不能和列名相同
PL/SQL使用VALUES子句插入数据
DECLARE
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
BEGIN
v_deptno:=1111;
v_dname:='anran';
INSERT INTO dept(deptno,dname)
VALUES(v_deptno,v_dname);
END;
使用子查询更新列值
DECLARE
v_ename emp.ename%TYPE:='';
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;
使用变量删除数据
DECLARE
v_ename emp.enameanran%TYPE:=&name;
BEGIN
DELETE FROM emp
WHERE deptno=(SELECT deptno FROM emp
WHEN ename=v_ename);
END;
SQL游标
当执行SELECT INSERT UPDATE DETELE oracle server会为这些SQL语句分配相应的上下文。Oracle使用上下文去解析并执行相应SQL语句。Oracle 游标包含显示游标,和隐含游标。
当使用UPDATE,INSERT,DELETE语句时,为了取得DML语句结果必须使用SQL游标属性
SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT,SQL%ISOPEN
SQL%ISOPEN:用于确定SQL游标是否打开。PL/SQL中执行SELECT INSERT UPDATE DETELE oracle 会隐式打开游标。并在执行完成后会隐式关闭游标。
SQL%FOUND:确定SQL语句是否执行成功。
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%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;
/
修改了6行
在PL/SQL中使用COMMIT RollBack
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;
/
条件分支语句:
多重条件分支:
DECLARE
2 v_job VARCHAR2(10);
3 v_sal NUMBER(6,2);
4 BEGIN
5 SELECT job,sal INTO v_job,v_sal
6 FROM emp WHERE empno=&no;
7 IF v_job='PRESIDENT' THEN
8 UPDATE emp SET sal=v_sal+1000 WHERE empno=&no;
9 ELSIF v_job='MANAGER' THEN
10 UPDATE emp SET sal=v_sal+500 WHERE empno=&no;
11 ELSE
12 UPDATE emp SET sal=v_sal+200 WHERE empno=&no;
13 END IF;
14 END;
15 /
一:单一选择符进行等值比较
DECLARE
v_deptno emp.deptno%TYPE;
BEGIN
v_deptno:=&no;
CASE v_deptno
WHEN 10 THEN
dbms_output.put_line('10');
WHEN 20 THEN
dbms_output.put_line('20');
WHEN 30 THEN
dbms_output.put_line('30');
END CASE;
END;
/
二:多条件选择
DECLARE
v_sal emp.sal%TYPE;
v_ename emp.ename%TYPE;
BEGIN
SELECT ename,sal INTO v_ename,v_sal
FROM emp WHERE empno=&no;
CASE
WHEN v_sal < 1000 THEN
UPDATE emp SET comm=100 WHERE ename=v_ename;
WHEN v_sal < 2000 THEN
UPDATE emp SET comm=80 WHERE ename=v_ename;
WHEN v_sal <6000 THEN
UPDATE emp SET comm=50 WHERE ename=v_ename;
END CASE;
END;
基本循环语句:基本循环语句无论条件是否复合必定执行一次循环
DECLARE
i INT:=1;
BEGIN
LOOP
dbms_output.put_line('number'||i);
EXIT WHEN i=10;
i:=i+1;
END LOOP;
END;
WHILE循环语句。
DECLARE
i number(10):=1;
BEGIN
WHILE i<10 LOOP
dbms_output.put_line('Number'||i);
i:=i+1;
END LOOP;
END;
FOR循环
FOR counter in [reverse]
Lower_bound..upper_bound LOOP
statement1;
statement2;
END LOOP;
BEGIN
for i in REVERSE 1..10 LOOP
dbms_output.put_line('number'||i);
END LOOP;
END;
/
number10
number9
number8
number7
number6
number5
number4
number3
number2
number1
嵌套循环:
用标号来标记嵌套块或嵌套循环,区分内存循环和外层循环,并可以在内层循环中直接退出外层循环。
DECLARE
result INT;
BEGIN
<<outer>>
FOR i IN 1..100 LOOP
<<inner>>
FOR j IN 1..100 LOOP
result:=i*j;
EXIT outer WHEN result=1000;
EXIT WHEN result=500;
END LOOP inner;
dbms_output.put_line(i);
dbms_output.put_line(result);
END LOOP outer;
dbms_output.put_line(result);
END;
/
顺序控制语句
NULL:不会执行任何操作将控制传递到下一条语句。