PL/SQL学习一

1、PL/SQL块基本组成：

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

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

-- ex:pl/sql_2DECLARE   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_3CREATE OR REPLACE PROCEDURE UPD_SHIPDATE(V_ORDID NUMBER, V_SHIPDATE DATE) IS  E_NO_ROW EXCEPTION;-- 创建过程,输入参数为V_ORDID，V_SHIPDATEBEGIN  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_4CREATE 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  begin2  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 ISPROCEDURE 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)  ISBEGINUPDATE 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_6CREATE OR REPLACE TRIGGER update_cascade  AFTER UPDATE of deptno on dept -- 在更新后触发  FOR each rowBEGIN UPDATE emp SET deptno=:new.deptno --new为deptno新值(更新后的值) WHERE deptno=:old.deptno;         --old为deptno旧值(更新前的值)END;

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