PL/SQL学习一

1、PL/SQL块基本组成：

DECLARE 
/*
 * 定义部分---定义常量、变量、复杂数据类型、游标等(可选的)
*/
BEGIN
/*
 * 执行部分---PL/SQL及SQL语句的执行(必须的)
*/
EXCEPTION
/*
 * 异常处理部分---处理运行错误(可选的)
 */
END; -- 块(block)结束标记

-- ex:pl/sql_1
BEGIN 
  dbms_output.put_line('Hello,PL/SQL'); -- dbms_output是oracle提供的系统包,put_line是它的一个过程
END;

-- ex:pl/sql_2
DECLARE 
  v_ename VARCHAR2(5);
BEGIN
  SELECT ename INTO v_ename FROM emp
  WHERE empno=&no;  -- &no为替代变量
  dbms_output.put_line('雇员名:'||v_ename);  
 EXCEPTION  --异常处理
   WHEN NO_DATA_FOUND THEN 
     dbms_output.put_line('请输入正确的雇员编号!');
END;

2、匿名块
没有名称的PL/SQL块成为匿名块，它可以内嵌到应用程序中使用，也可以在sqlplus中使用。
(ex:sql_1与ex:sql_2都属于匿名块)

3、命令块
具有特定名称标识的PL/SQL块,使用<<>>加以标记。

4、子程序
子程序包括过程、函数和包。使用PL/SQL开发程序的时候，既可以开发客户端的子程序也可以开发服务端的子程序，
客户端子程序主要用在Developer中，服务端子程序可应用在任何应用程序中。

一、过程(procedure)
可以指定输入(in)输出(out)参数。

-- ex:pl/sql_3
CREATE OR REPLACE PROCEDURE UPD_SHIPDATE(V_ORDID NUMBER, V_SHIPDATE DATE) 
IS
  E_NO_ROW EXCEPTION;-- 创建过程,输入参数为V_ORDID，V_SHIPDATE
BEGIN
  UPDATE SCOTT.EMP
     SET SCOTT.EMP.HIREDATE = V_SHIPDATE
   WHERE SCOTT.EMP.EMPNO = V_ORDID;
  IF SQL%NOTFOUND THEN  --如果没有对应的V_ORDID则抛出异常
    RAISE E_NO_ROW;     --抛出异常
  END IF;
EXCEPTION
  WHEN E_NO_ROW THEN
    RAISE_APPLICATION_ERROR(-20004, 'no this number');-- 输出错误信息
END UPD_SHIPDATE;

在sqlplus执行过程时候，可以使用execute 过程名(参数) 或者call 过程名(参数) 执行。

二、函数(function)
函数用于返回特定数据。函数头部必须包含return子句，在函数体内必须要包含return语句返回数据。

-- ex:pl/sql_4
CREATE OR REPLACE FUNCTION TEST(NAME VARCHAR2) RETURN NUMBER IS
  AN_TEST NUMBER(7, 2);-- 返回值
BEGIN
  SELECT SAL * 12 + NVL(COMM, 0)
    INTO AN_TEST
    FROM SCOTT.EMP
   WHERE LOWER(ENAME) = LOWER(NAME);
  RETURN AN_TEST;-- 返回
END;

在sqlplus执行函数:

方法1：

1  begin
2  dbms_output.put_line(TEST('SCOTT'));
3* end;

方法2(注意函数内有DML操作的不能用此方法)：

select TEST('SCOTT') from dual;

方法3：

var a number(7,2);
exec :a:=TEST('SCOTT');
print a

三、包(package)
包用于组合过程和函数，它由包头和包体组成。包头主要是声名各个过程与函数，包体实现(注意:包头、包体要分开编译)

-- ex:pl/sql_5
-- 包头
CREATE OR REPLACE PACKAGE EMP_TEST IS
PROCEDURE UPD_SHIPDATE(NAME VARCHAR2,newsal NUMBER);
FUNCTION TEST(NAME VARCHAR2) RETURN NUMBER;
END;
-- 包体
CREATE OR REPLACE PACKAGE BODY EMP_TEST IS
 PROCEDURE UPD_SHIPDATE(NAME VARCHAR2,newsal NUMBER) 
 IS
BEGIN
UPDATE SCOTT.EMP 
SET SCOTT.EMP.sal = newsal 
WHERE lower(SCOTT.EMP.ename) = NAME; 
END UPD_SHIPDATE; 

FUNCTION TEST(NAME VARCHAR2) RETURN NUMBER 
IS
  AN_TEST NUMBER(7, 2);
BEGIN
  SELECT SAL * 12 + NVL(COMM, 0)
    INTO AN_TEST
    FROM SCOTT.EMP
   WHERE LOWER(ENAME) = LOWER(NAME);
  RETURN AN_TEST;
END TEST;
END EMP_TEST;

调用 SELECT emp_test.test('scott') from dual; 进行测试

四、触发器(Trigger)
触发器是指隐含执行的过程。只有insert、update、delete这3类触发器。

-- ex:pl/sql_6
CREATE OR REPLACE TRIGGER update_cascade
  AFTER UPDATE of deptno on dept -- 在更新后触发
  FOR each row
BEGIN
 UPDATE emp SET deptno=:new.deptno --new为deptno新值(更新后的值)
 WHERE deptno=:old.deptno;         --old为deptno旧值(更新前的值)
END;

只要对表dept的deptno列有更新即触发该触发器，触发器自动执行。