数据库之【PL SQL块 与 控制结构举例】

*********************************************

数据库之【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;