---参考:《Oracle 11g SQL和PLSQL从入门到精通》
一:PL/SQL块结构
二:PL/SQL块分类
三:子程序(过程、函数、包、触发器)
一:PL/SQL块结构
PL/SQL块结构
PL/SQL块由三个部分组成:定义部分、执行部分、异常处理部分。
定义部分:用于定义常量、变量、游标、异常、复杂数据类型等(可选)。
执行部分:用于实现应用模块功能,该部分包含了要执行PL/SQL语句和SQL语句(必选)。
异常处理部分:用于处理执行部分可能出现的运行错误(可选)。
PL/SQL块基本结构如下:
DECLARE /* * 定义部分 */ BEGIN /* *执行部分 */ EXCEPTION /* *异常处理部分 */ END; /*块结束标记*/
示例1:只包含执行部分的PL/SQL块
SQL> conn cjc/cjcConnected.SQL> set serveroutput onSQL> begin 2 dbms_output.put_line('Hello,everyone'); 3 end; 4 /Hello,everyonePL/SQL procedure successfully completed.
示例2:包含定义部分和执行部分的PL/SQL块
SQL> set serveroutput onSQL> set verify offdeclare v_ename varchar2(5);begin select ename into v_ename from scott.emp where empno = &no; dbms_output.put_line('ename is:' || v_ename);end;/Enter value for no: 7788ename is:SCOTT
示例3:包含定义部分、执行部分和异常处理部分的PL/SQL块
SQL> set serveroutput onSQL> set verify offdeclare v_ename varchar2(5);begin select ename into v_ename from scott.emp where empno = &no; dbms_output.put_line('ename is:' || v_ename);exception when no_data_found then dbms_output.put_line('Please enter the correct employee number!');end;/Enter value for no: 6666Please enter the correct employee number!
二:PL/SQL块分类
匿名块、命名块、子程序、触发器。
1 匿名块:没有名称的PL/SQL块。
示例:
SQL> set serveroutput onSQL> set verify offdeclare v_avgsal number(6, 2);begin select avg(sal) into v_avgsal from emp where deptno = &no; dbms_output.put_line('The average wage is:' || v_avgsal);end;/Enter value for no: 10The average wage is:2916.67
2.命名块:指具有特定名称标识的PL/SQL块。
SQL> set serveroutput onSQL> set verify off<>declare v_deptno number(2); v_dname varchar2(10);begin <> begin select deptno into v_deptno from emp where lower(ename) = lower('&name'); end; --<> select dname into v_dname from dept where deptno = v_deptno; dbms_output.put_line('New dname is:' || v_dname);end; --<>/Enter value for name: scottNew dname is:RESEARCH
三:子程序(过程、函数、包、触发器)
(1)过程:用于执行特定操作
示例:
create procedure update_sal(name varchar2, newsal number) isbegin update emp set sal = newsal where lower(ename) = lower(name);end;/SQL> exec update_sal('scott',50000)SQL> commit;SQL>select sal from emp as of timestamp (systimestamp - interval '5' minute) where ename='SCOTT'unionselect sal from emp where ename='SCOTT'; SAL---------- 3000 50000
(2)函数
函数用于返回特定数据。
当建立函数是,函数头部必须包含return子句,并且函数体必须包含return语句。
示例:取得雇员全年收入函数annual_income。
SQL>create function annual_income(name varchar2) return number is annual_salary number(7, 2);begin select sal * 12 + nvl(comm, 0) into annual_salary from emp where lower(ename) = lower(name); return annual_salary;end; /SQL> select annual_income('scott') as Annual_salary from dual;ANNUAL_SALARY-------------36000
(3)包
包用于逻辑组合相关的过程和函数,它由包规范和包体两部分组成。
包规范用于定义公用的常量、变量、过程和函数。
包体则用于实现包规范中的过程和函数。
下面建立包含过程update_sal和函数annual_income的包emp_pkg为例。
示例:
SQL>create package emp_pkg is procedure update_sal(name varchar2, newsal number); function annual_income(name varchar2) return number;END;/SQL>create package body emp_pkg is procedure update_sal(name varchar2, newsal number) is begin update emp set sal = newsal where lower(ename) = lower(name); end; function annual_income(name varchar2) return number is annual_salary number(7, 2); begin select sal * 12 + nvl(comm, 0) into annual_salary from emp where lower(ename) = lower(name); return annual_salary; end;end;/SQL> exec emp_pkg.update_sal('scott',5000);SQL> select emp_pkg.annual_income('scott') from dual;EMP_PKG.ANNUAL_INCOME('SCOTT')------------------------------ 60000SQL> rollback;
(4)触发器
触发器是指被隐含执行的PL/SQL块。
当触发了与触发器相关的事件之后,Oracle会隐含执行触发器的PL/SQL块。
SQL> create trigger update_cascade after update of deptno on dept for each rowbegin update emp set deptno = :new.deptno where deptno = :old.deptno;end;/SQL> update dept set deptno=50 where dname='ACCOUNTING';SQL> select ename from emp where deptno=50;ENAME----------CLARKKINGMILLERSQL> rollback;SQL> select ename from emp where deptno=50;no rows selected
更多数据库相关学习资料,可以查看我的ITPUB博客,网名chenoracle:
http://blog.itpub.net/29785807/