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

--动态SQLDECLARE  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;--条件IFDECLARE  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;