*********************************************
数据库之【PL SQL块 与 控制结构举例】
*********************************************
--1.块示例
DECLARE
empno NUMBER(5);
BEGIN
SELECT empno
INTO empno
FROM emp
WHERE ename = 'SMITH'
FOR UPDATE OF deptno;
IF empno > 0 THEN
UPDATE emp SET deptno = 40 WHERE ename = 'SMITH';
END IF;
COMMIT; --用到行级锁,一定要提交
EXCEPTION
/* 异常处理语句 */
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('出错:' || SQLERRM);
END;
--2.变量与常量
--常量与变量定义与赋值
num constant number default 30;
num constant number :=30;
selelct sal into num from emp where empno=7369;
DECLARE
icode VARCHAR2(6);
p_catg VARCHAR2(20);
p_rate NUMBER;
c_rate CONSTANT NUMBER := 0.10;
BEGIN
...
icode := 'i205';
SELECT p_category, itemrate * c_rate
INTO p_catg, p_rate
FROM itemfile WHERE itemcode = icode;
...
END;
--动态SQL
DECLARE
sql_stmt VARCHAR2(200);
emp_id NUMBER(4) := 7566;
emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)';
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt
INTO emp_rec
USING emp_id;
dbms_output.put_line(emp_rec.ename);
END;
--控制结构举例
--打印服务设置
set serveroutput on;
--条件IF
DECLARE
e_mpno NUMBER;
d_eptno NUMBER;
BEGIN
e_mpno := 7369;
SELECT deptno INTO d_eptno FROM emp WHERE empno = e_mpno;
IF d_eptno = 30 THEN
UPDATE emp SET sal = sal + 100 WHERE empno = e_mpno;
ELSE
UPDATE emp SET sal = sal + 50 WHERE empno = e_mpno;
END IF;
DBMS_OUTPUT.PUT_LINE('deptno=' || d_eptno);
END;
/
DECLARE
text varchar2(200);
begin
text := '&text';
if upper(text) = 'A' then
dbms_output.put_line('优秀');
elsif upper(text) = 'B' then
dbms_output.put_line('良好');
elsif upper(text) = 'C' then
dbms_output.put_line('一般');
elsif upper(text) = 'D' then
dbms_output.put_line('差');
else
dbms_output.put_line('请输入正确成绩');
end if;
end;
/
--选择条件子查询
select e.*,(case when e.sal<1000 then '低' else '高' end) "级别" from emp e;
--循环控件
--LOOP循环
declare
i number := 1;
j number := 1;
begin
loop
exit when i > 10;
loop
exit when j > i;
dbms_output.put('*');
j := j + 1;
end loop;
dbms_output.new_line();
j := 1;
i := i + 1;
end loop;
end;
--WHILE循环
declare
i number := 1;
j number := 1;
begin
while i < 10 loop
while j < i loop
dbms_output.put('*');
j := j + 1;
end loop;
dbms_output.new_line();
j := 1;
i := i + 1;
end loop;
end;
--FOR循环
declare
i number;
j number;
begin
--反转在IN后面写reverse关键字
for i in 1 .. 9 loop
for j in 1 .. i loop
dbms_output.put('*');
end loop;
dbms_output.new_line;
end loop;
end;
--顺序控件
DECLARE
qtyhand itemfile.qty_hand%type;
relevel itemfile.re_level%type;
BEGIN
SELECT qty_hand, re_level
INTO qtyhand, relevel
FROM itemfile
WHERE itemcode = 'i201';
IF qtyhand < relevel THEN
GOTO updation;
ELSE
GOTO quit;
END IF;
<<updation>>
UPDATE itemfile
SET qty_hand = qty_hand + re_level
WHERE itemcode = 'i201';
<<quit>>
NULL;
END;