PLSql语句学习(一)

PL/SQL(PROCEDURAL LANGUAGE/SQL) 是标准的SQL的基础上增加了过程化处理的语言
ORACLE 客户端工具访问ORACLE服务器的操作语言，对SQL的扩充

还有其他的客户端编程软件，例如pro*c/c++，ODBC,OCI,JDBC,SQLJ
程序结构：
申明部分：declare
执行部分：begin
异常处理：exception
对大小写不敏感
注释：-- 或者 /* */


DECLARE
v_firstName varchar2(32);
v_firstName studengs.first_name%TYPE;
v_TempVar Number(7,3) NOT NULL :=12.3;
v_StuRec student%ROWTYPE;

TYPE record_name IS RECORD(
field1 type1 [NOT NULL] [:=EXPR1],
field2 type2 [NOT NULL] [:=EXPR1]);
限定NOT NULL，那么它必须拥有一个初始值。
TYPE tabletype IS TABLE OF type INDEX BY BINARY_INTEGER;

TABLE类型的例子
DECLARE
TYPE t_StuTable IS TABLE OF Student%ROWTYPE
INDEX BY BINARY_INTEGER;
v_Student t_StuTable;
BEGIN
SELECT * INTO v_Student（1001）
FROM Student
WHERE id = 1001;
END;

变量的作用域与可见性和PL/SQL控制语句（不作介绍，只显示例子）

DECLARE
v_str VARCHAR2(20);
v_num NUMBER:=199.01;
v_int BINARY_INTEGER:=220;
BEGIN
v_str:='well done leo!';
DBMS_OUTPUT.PUT_LINE(v_str);
DBMS_OUTPUT.PUT_LINE(v_int);
END；


1 DECLARE
2 v_str VARCHAR2(20);
3 v_num NUMBER:=199.01;
4 v_int BINARY_INTEGER:=220;
5 v_bool BOOLEAN;
6 BEGIN
7 v_str:='well done leo!';
8 DBMS_OUTPUT.PUT_LINE(v_str);
9 DBMS_OUTPUT.PUT_LINE(v_int);
10 v_bool:=false;
11 IF(v_bool) THEN
12 DBMS_OUTPUT.PUT_LINE('FALSE');
13 END IF;
14* END;


DECLARE
TYPE t_emp IS TABLE OF s_emp%ROWTYPE INDEX BY BINARY_INTEGER;
v_emp t_emp;
BEGIN
SELECT * INTO v_emp(100) FROM s_emp WHERE ID=12;
SELECT * INTO v_emp(200) FROM s_emp WHERE ID=15;
DBMS_OUTPUT.PUT_LINE(v_emp(100).id||':'||v_emp(100).last_name);
DBMS_OUTPUT.PUT_LINE(v_emp(200).id||':'||v_emp(200).last_name);
END;


1 DECLARE
2 TYPE t_emp_r IS RECORD(
3 v_id s_emp.id%TYPE,
4 v_last_name s_emp.last_name%TYPE);
5 TYPE t_emp_t IS TABLE OF t_emp_r INDEX BY BINARY_INTEGER;
6 v_emp_t t_emp_t;
7 BEGIN
8 FOR cnt in 1..25 LOOP
9 SELECT id,last_name
10 INTO v_emp_t(cnt).v_id,v_emp_t(cnt).v_last_name
11 FROM s_emp
12 WHERE id=cnt;
13 END LOOP;
14 FOR cnt IN 1..25 LOOP
15 DBMS_OUTPUT.PUT_LINE(v_emp_t(cnt).v_id||':'||v_emp_t(cnt).v_last_name);
16 END LOOP;
17 DBMS_OUTPUT.PUT_LINE('bye!');
18* END;



DECLARE
v1 NUMBER;
v2 VARCHAR2(10);
BEGIN
v1:=100;
v2:='hello';
DECLARE
v3 NUMBER;
v2 NUMBER;
BEGIN
v3:=300;
v2:=200;
END;
DBMS_OUTPUT.PUT_LINE(v1);
DBMS_OUTPUT.PUT_LINE(v2);
END;
结果
100
hello



1 <<outer>>
2 DECLARE
3 v1 NUMBER;
4 v2 VARCHAR2(10);
5 BEGIN
6 v1:=100;
7 v2:='hello';
8 DECLARE
9 v3 NUMBER;
10 v2 NUMBER;
11 BEGIN
12 v3:=300;
13 v2:=200;
14 DBMS_OUTPUT.PUT_LINE(outer.v2);
15 END;
16 DBMS_OUTPUT.PUT_LINE(v1);
17 DBMS_OUTPUT.PUT_LINE(v2);
18* END;
19 /
结果：hello
100
hello


1 DECLARE
2 bool BOOLEAN;
3 v_int BINARY_INTEGER;
4 BEGIN
5 bool := null;
6 IF(bool)THEN
7 DBMS_OUTPUT.PUT_LINE('WELL DONE');
8 END IF;
9 v_int:=3;
10 IF(v_int=1) then
11 DBMS_OUTPUT.PUT_LINE('1');
12 ELSIF(v_int=2) then
13 DBMS_OUTPUT.PUT_LINE('2');
14 ELSIF(v_int=3) then
15 DBMS_OUTPUT.PUT_LINE('3');
16 ELSE
17 DBMS_OUTPUT.PUT_LINE('5');
18 END IF;
19* END;



1 DECLARE
2 v_id s_emp.id%TYPE;
3 v_last_name s_emp.last_name%TYPE;
4 v_count NUMBER:=1;
5 BEGIN
6 LOOP
7 IF(v_count>25) THEN 通常loop之前一定要加上 IF语句来控制
8 EXIT;
9 END IF;
10 SELECT id,last_name
11 INTO v_id,v_last_name
12 FROM s_emp
13 WHERE id=v_count;
14 DBMS_OUTPUT.PUT_LINE(v_id||':'||v_last_name);
15 v_count :=v_count +1;
16 END LOOP;
17 DBMS_OUTPUT.PUT_LINE('END');
18* END;


1 DECLARE
2 v_id s_emp.id%TYPE;
3 v_last_name s_emp.last_name%TYPE;
4 v_salary s_emp.salary%TYPE;
5 v_cnt NUMBER:=1;
6 v_grade VARCHAR2(5);
7 BEGIN
8 LOOP
9 SELECT id,last_name,salary
10 INTO v_id,v_last_name,v_salary
11 FROM s_emp
12 where id=v_cnt;
13 IF (v_salary >=2000) THEN v_grade:='a';
14 ELSIF (v_salary >=1500) THEN v_grade:='b';
15 ELSIF (v_salary >=1000) THEN v_grade:='c';
16 ELSE v_grade:='d';
17 END IF;
18 DBMS_OUTPUT.PUT_LINE(v_last_name||':'||v_grade);
19 v_cnt:=v_cnt+1;
20 EXIT WHEN v_cnt>25; 当v_cnt大于25时就退出循环
21 END LOOP;
22* END;


1 DECLARE
2 v_id s_emp.id%TYPE;
3 v_last_name s_emp.last_name%TYPE;
4 v_salary s_emp.salary%TYPE;
5 v_cnt NUMBER:=1;
6 v_grade VARCHAR2(5);
7 BEGIN
8 WHILE(v_cnt<=25) LOOP 每一次循环都要执行一次 WHILE里面的条件判断句
9 SELECT id,last_name,salary
10 INTO v_id,v_last_name,v_salary
11 FROM s_emp
12 where id=v_cnt;
13 IF (v_salary >=2000) THEN v_grade:='a';
14 ELSIF (v_salary >=1500) THEN v_grade:='b';
15 ELSIF (v_salary >=1000) THEN v_grade:='c';
16 ELSE v_grade:='d';
17 END IF;
18 DBMS_OUTPUT.PUT_LINE(v_last_name||':'||v_grade);
19 v_cnt:=v_cnt+1;
20 END LOOP;
21* END;


1 DECLARE
2 v_id s_emp.id%TYPE;
3 v_last_name s_emp.last_name%TYPE;
4 BEGIN
5 FOR cnt IN REVERSE 1..5 LOOP 加了REVERSE ，CNT由5开始减1
6 SELECT id,last_name
7 INTO v_id,v_last_name
8 FROM s_emp
9 WHERE id=cnt;
10 DBMS_OUTPUT.PUT_LINE(v_id||':'||v_last_name);
11 END LOOP;
12* END;


CURSOR游标
游标用于提取多行数据集
游标的使用：
（1）声明游标
（2）为查询打开游标
（3）将结果提取出来，存入PL/SQL变量中
（4）关闭游标

(1)CURSOR cursor_name IS SELECT * FROM...
(2)OPEN cursor_name;
(3)FETCH cursor_name INTO var1,var2...;
FETCH cursor_name INTO record_var;
(4)CLOSE cursor_name;

游标的属性：
%FOUND 前面FETCH返回一行数据，则为TRUE，未打开为false
%NOTFOUND 和上面的相反，未打开也为FALSE
%ISOPEN
%ROWCOUNT 指针位移量



1 DECLARE
2 CURSOR c_emp IS
3 SELECT * FROM s_emp;
4 v_emp s_emp%ROWTYPE;
5 BEGIN
6 OPEN c_emp;
7 FETCH c_emp INTO v_emp;
8 DBMS_OUTPUT.PUT_LINE(v_emp.id||':'||v_emp.last_name);
9* END;


1 DECLARE
2 CURSOR c_emp IS
3 SELECT * FROM s_emp;
4 v_emp s_emp%ROWTYPE;
5 BEGIN
6 OPEN c_emp;
7 LOOP
8 FETCH c_emp INTO v_emp;
9 DBMS_OUTPUT.PUT_LINE(v_emp.id||':'||v_emp.last_name);
10 END LOOP;
11* END;


1 DECLARE
2 CURSOR c_emp IS
3 SELECT * FROM s_emp;
4 v_emp s_emp%ROWTYPE;
5 BEGIN
6 OPEN c_emp;
7 LOOP
8 FETCH c_emp INTO v_emp;
9 EXIT WHEN c_emp%FOUND=false; 依赖于游标的属性
10 DBMS_OUTPUT.PUT_LINE(v_emp.id||':'||v_emp.last_name);
11 END LOOP;
12* END;


1 DECLARE
2 CURSOR c_emp IS
3 SELECT * FROM s_emp;
4 v_emp s_emp%ROWTYPE;
5 BEGIN
6 OPEN c_emp;
7 LOOP
8 FETCH c_emp INTO v_emp;
9 EXIT WHEN c_emp%NOTFOUND=true; 与c_emp%FOUND=false; 的结果是一样的
10 DBMS_OUTPUT.PUT_LINE(v_emp.id||':'||v_emp.last_name);
11 END LOOP;
12* END;


1 DECLARE
2 CURSOR r_em IS
3 SELECT last_name
4 FROM s_emp e,s_dept d
5 WHERE e.dept_id=d.id
6 AND d.region_id=4;
7 v_name s_emp.last_name%TYPE;
8 BEGIN
9 OPEN r_em;
10 LOOP
11 FETCH r_em INTO v_name;
12 EXIT WHEN r_em%NOTFOUND;
13 DBMS_OUTPUT.PUT_LINE(v_name);
14 END LOOP;
15 CLOSE r_em;
16* END;


1 DECLARE
2 CURSOR r_em IS
3 SELECT e.id,e.last_name
4 FROM s_emp e,s_dept d
5 WHERE e.dept_id=d.id
6 AND d.region_id=4;
7 v_name s_emp.last_name%TYPE;
8 v_id s_emp.id%TYPE;
9 BEGIN
10 OPEN r_em;
11 FETCH r_em INTO v_id,v_name; 两次FETCH
12 WHILE r_em%FOUND LOOP
13 DBMS_OUTPUT.PUT_LINE(v_id||':'||v_name); 先打印出来，再FETCH一次
14 FETCH r_em INTO v_id,v_name;
15 END LOOP;
16 CLOSE r_em;
17* END;

FOR 循环
1 DECLARE
2 CURSOR r_em IS
3 SELECT e.id,e.last_name
4 FROM s_emp e,s_dept d
5 WHERE e.dept_id=d.id
6 AND d.region_id=4;
7 BEGIN
8 FOR v_emp IN r_em LOOP
9 DBMS_OUTPUT.PUT_LINE(v_emp.id||':'||v_emp.last_name);
10 END LOOP;
11* END;


1 DECLARE 不用定义语句
2 BEGIN
3 FOR v_emp IN ( 用SELECT语句直接跟在FOR的IN语句后当作游标
4 SELECT e.id,e.last_name
5 FROM s_emp e,s_dept d
6 WHERE e.dept_id=d.id
7 AND d.region_id=4 这里不用分号
8 )LOOP
9 DBMS_OUTPUT.PUT_LINE(v_emp.id||':'||v_emp.last_name);
10 END LOOP;
11* END;


DECLARE
CURSOR c_emp IS
SELECT * FROM s_emp
WHERE dept_id=32
FOR UPDATE OF salary; 不加上OF的话，整个表都会加锁。有OF的话，只为这一列加锁
BEGIN
FOR v_emp IN c_emp LOOP
UPDATE s_emp set salary=salary*1.1
WHERE id=v_emp.id;
END LOOP;
END;


EXCEPTION

1 DECLARE
2 my_exception EXCEPTION;
3 v_emp s_emp%ROWTYPE;
4 v_sal s_emp.salary%TYPE;
5 BEGIN
6 SELECT salary INTO v_sal FROM s_emp
7 WHERE id=20;
8 IF(v_sal<1000) THEN
9 RAISE my_exception;
10 END IF;
11 DBMS_OUTPUT.PUT_LINE('END');
12 EXCEPTION
13 WHEN my_exception THEN
14 UPDATE s_emp set salary=salary+500 where id=20;
15* END;




SUB-PROGRAM


(1) PROCEDURE过程
CREATE[OR REPLACE] PRODUCE proc_name
[(arg_name[{IN | OUT| IN OUT}]TYPE,
arg_name[{IN | OUT| IN OUT}]TYPE]
{ IS | AS }
procedure_body

(2)FUNCTION函数
CREATE [OR REPLACE] FUNCTION func_name
[(arg_name[{ IN | OUT | IN OUT }]TYPE,
(arg_name[{ IN | OUT | IN OUT}]TYPE)]
RETURN TYPE
{IS|AS}
Func_body



PACKAGE 包
包头规范
CREATE [OR REPLACE] PACKAGE pack_name
{ IS | AS }
procedure_specification|;
function_specification|
variable_declaration|
type_definition|
exception_declaration|
cursor_declaration
END pack_name;


PACKAGE BODY 包主体
CREATE OR REPLACE PACKAGE BODY pac_name
{ IS | AS }
....
BEGIN
.....initialization code
END pac_name;


TRIGGER触发器，必须存在数据库中
都是带有名字的执行块
都有声明，执行体和异常处理部分
1 CREATE OR REPLACE TRIGGER zh_trigger
2 AFTER INSERT OR UPDATE OR DELETE ON s_emp
3 DECLARE
4 v_cnt NUMBER;
5 BEGIN
6 SELECT COUNT(*) INTO v_cnt FROM s_emp;
7 DBMS_OUTPUT.PUT_LINE('something changed!');
8* END;
9 /

Trigger created.