pl/sql程序语言的组成:程序由块结构组成，包含变量，各种不同的程序控制结构，异常处理模块，子程序（过程，函数，包）,触发器等pl/sql 注释：/* 和 */ 之间多行注释以--开始的单行注释基本结构：4个基本结构：声明， 执行体开始，异常处理，执行体结束 declare.......begin ..... exception ......end; (1） 定义，声明部分 declare开始，到begin结束。 定义程序所需要变量，常量，数据类型，游标，异常处理名称等定义，声明部分是可选的（2） 执行部分begin开始，end结束，（异常处理部分，那么从exception结束)实现功能部分，通过一系列语句和流程控制，实现数据查询，数据操作，事务控制，游标处理等 这部分是必须的 (3) 异常处理部分 以exception开始，到end结束 可选的部分 (4)执行体结束 PLSQL中的SQL语句有： select、dml、commit、rollback、savepoint、cursor 特殊强调：PLSQL不支持DCL，不要问为什么。(DBMS_SQL package allows you to issue DDL and DCL statements.) SELECT SELECT select_list INTO variable_name | record_name FROM table WHERE condition;编程pl/sql，输出empno为7800的记录姓名：SQL> grant create procedure to scott; -->授权SQL> set serveroutput on-->使结果显示SQL> declare 2 v_name varchar2(10); 3 begin 4 select ename into v_name from emp where empno=7900; 5 dbms_output.put_line('7900 is name is '||v_name); 6 end; 7 /-----------------------------------------SQL> set serveroutput ondeclarev_name varchar2(10);empno number;beginselect ename into v_name from emp where empno=&empno;dbms_output.put_line('employees name is '||v_name);exception when NO_DATA_FOUND then dbms_output.put_line('not found record,please try again :) ');end;/命名块： 指一次编辑多次执行的pl/sql程序，包括自定义函数，过程，函数，包，触发器语法要素： 包括字符集，标识符，文本，分隔符，注释等字符集： 大小写英文字母，a-z,A-Z 0~9 空白符，制表符，空格，回车 特殊字符，~,!@#$%^&*()_ +,=,;,./' 等 '数值型，字符型，日期型，布尔型文字（true,flase,null)分隔符 ---->算术运算，数值比较，给变量赋值+ - * / **(乘方)= <= >= < > != ^= <> ~=(这4个均为不等于):=(赋值) ` (列表分隔符) @(数据库连接分隔符); （语句结束)|| 字符串连接=> 给其他传递参数% 属性标识器 type rowtype 流程控制：1.分支结构 (1) 多分支的IFIF condition1 THENstatements1;ELSIF condition2 THENstatements2;......ELSEstatement;END IF;作用：判断condition1是否成立，如果成立，执行statements1 如果不成立，判断condition2是否成立，如果成立，执行statements2,... 如果都不成立，执行statement; (2)等值比较的CASE多分支语句CASE expressionWHEN result1 THENstatement1;WHEN result2 THENstatement2;......ELSE statement;END CASE; (3)条件比较的CASE多分支CASE<---这里没有表达式 WHEN condition1 THEN statement1; WHEN condition2 THEN statement2;....... ELSEstatement;END CASE; eg:CASE WHEN v_sal>2000 and v_sal<2501 THENeg:调薪水 job ---manager sal*1.1 job----salesman sal*1.2 sal*2 ly---if----------------------set serveroutput ondeclarevjob emp.job%type;vsal emp.sal%type;vip emp.empno%type;news emp.sal%type;beginvip:=&inp;select job,sal into vjob,vsal from emp where empno=vip;if vjob='NAMAGER' then update emp set sal=sal*1.1 where empno=vip; news:=vsal*1.1;elsif vjob='SALESMAN' then update emp set sal=sal*1.2 where empno=vip; news:=vsal*1.2;else update emp set sal=sal*2 where empno=vip; news:=vsal*2;end if;dbms_output.put_line('empno: '||vip||' |old sal: '||vsal||' |new sal:'||news);exception when no_data_found then dbms_output.put_line('not record');end;/---------------------------ly--------case----------------set serveroutput ondeclarevjob emp.job%type;vsal emp.sal%type;vip emp.empno%type;news emp.sal%type;beginvip:=&inp;select job,sal into vjob,vsal from emp where empno=vip;case vjob when 'MANAGER' then news:=vsal*1.1; when 'SALESMAN' then news:=vsal*1.2;else news:=vsal*2;end case;update emp set sal=news where empno=vip;dbms_output.put_line('empno: '||vip||' |old sal: '||vsal||' |new sal:'||news);exception when no_data_found then dbms_output.put_line('not record');end;/--------------------------2、循环结构 （1）LOOP循环 LOOPstatement;EXIT WHEN condition; END LOOP;作用： 一直执行statement直到condition成立set serveroutput ondeclare v_n int:=1;v_s int:=0;beginloopv_s:=v_s+v_n;exit when v_n>=100;v_n:=v_n+1;end loop; dbms_output.put_line('1+2+3+.....100='||v_s);end;/ （2）WHILE 循环WHILE condition LOOPstatement;END LOOP;作用： 判断condition是否成立，成立执行statementset serveroutput ondeclare v_n int:=1;v_s int:=0;begin while v_n<=100 loop v_s:=v_s+v_n;v_n:=v_n+1;end loop;dbms_output.put_line('1+2+3+.....100='||v_s);end;/ （3）FOR 循环FOR var_name IN [reverse] lowest_value ..higest_value LOOPstatement;END LOOP;作用： 让var_name 在IN 后面指定的范围内进行循环 每执行一次，var_name会自动的加1。set serveroutput ondeclare v_n int:=1;v_s int:=0;begin for v_n in 1..100 loopv_s:=v_s+v_n;end loop; dbms_output.put_line('1+2+3+.....100='||v_s);end;/set serveroutput ondeclarev_n int:=1;v_sum int:=0;beginFOR v_n IN reverse 1..10 LOOPv_sum:=v_sum+v_n;dbms_output.put_line(v_n);END LOOP;end;/ （4）GOTO 跳转语句格式：GOTO label_name使用GOTO语句注意：GOTO后的label_name必须存在标号后至少要有一条可执行语句不能从一个IF语句外，跳到其内部不能从一个循环体外跳到其内不能从一个子程序外跳到其内 （5）NULL语句NULL语句被称为空语句，不执行任何操作，有些场合有要求，必須有要执行语句，但此时还不知道要执行的语句是什么，可用NULL语句来替代，避免语法错误==================================================编程pl/sqleg:输出empno为7800的记录姓名:set serveroutput onset verify off -->赋值前后不比较，默认为ondeclarev_name varchar2(8); --> 声明变量pi constant number:=3.1415 --> 声明常量empno number;begin select ename into v_name from emp where empno=&empno; --> where后面读输入,&后面可接任意值 dbms_output.put_line('ename is '||v_name );exception when no_data_found then dbms_output.put_line('not founder record,please try again :)');end;/============================eg : 在dept表查询部分编号为10的部门，将部门名和工作地点显示出来declaredname dept.dname%type; --> %type变量声明loc dept.loc%type;begin select dname,loc into dname,loc from dept where deptno=&v_id; --> into后面跟多个值 dbms_output.put_line('10 name is '||dname||' and loc is '||loc);exception when no_data_found then dbms_output.put_line('not found record,please try again :)');end;/=============================eg : 查询7788记录的信息：7900 7834 7788 declarea emp%rowtype; --> %rowtype变量声明v_no emp.empno%type;begin v_no:=&emp_id; --> begin赋值时候读输入 select * into a from emp where empno=v_no; dbms_output.put_line(v_no||':'||a.ename||' sal is '||a.sal); --> 输出%rowtype变量exception when no_data_found then dbms_output.put_line('not found record,please try again :)');end;/=============================eg:插入记录declareempno emp.empno%type:=&empno; --> 声明的时候赋值读输入ename emp.ename%type:='&ename'; -->注意引号begin insert into emp(empno,ename) values(empno,ename); -->dml语句end;/=============================--------------------declare vtotal date:=sysdate +7; type va is record -->声明记录类型record (a number not null:=100, b emp.ename%type, c emp.job%type); emp_record va; -->声明为定义的类型....--------------------============================set serveroutput ondeclareab int:=1;vsum int:=0;begin loop exit when ab>100; vsum:=vsum+ab; ab:=ab+1; end loop; dbms_output.put_line(vsum);end;/============================set serveroutput ondeclareab int:=1;vsum int:=0;begin while ab<=100 loop vsum:=vsum+ab; ab:=ab+1; end loop; dbms_output.put_line(vsum);end;/============================set serveroutput ondeclareab int:=1;vsum int:=0;begin for ab in 1..100 loop -->ab 可以不声明直接用 vsum:=vsum+ab; end loop; dbms_output.put_line(vsum);end;/============================set serveroutput ondeclareab int:=1;begin for ab in reverse 1..5 loop -->reverse 是反向取值 dbms_output.put_line(ab); end loop;end;/============================declarevar emp.ename%type;cursor mycur is -->游标声明select ename from emp where deptno=10;beginif not mycur%isopenthenopen mycur;end if;loop fetch mycur into var; dbms_output.put_line(var||' '||mycur%rowcount); exit when mycur%notfound;end loop; close mycur;end;/============================declare vename emp.ename%type; vsal emp.sal%type; cursor mycur is select ename,sal from emp where sal>2500;begin if not mycur%isopen then open mycur; end if; loop fetch mycur into vename,vsal; dbms_output.put_line(mycur%rowcount||vename||' sal is '||vsal); exit when mycur%notfound; end loop; close mycur;end;/=======================游标FOR循环declare vename emp.ename%type; vsal emp.sal%type:=&sal; cursor mycur is select ename from emp where sal>vsal;begin for vename in mycur loop -->隐式进行了游标的open、fetch和close dbms_output.put_line(vename.ename||' '||mycur%rowcount); -->注意这里输出不是直接用vename end loop;end;/----------------declare vename emp.ename%type; vsal emp.sal%type:=&sal;begin for vename in (select ename from emp where sal>vsal) loop -->甚至可以不声明游标,称为显式游标的自动化 dbms_output.put_line(vename.ename); end loop;end;/======================set serveroutput onDeclareresult integer;beginresult:=10+3*4-20+5**2;dbms_output.put_line('运算结果是：'||result);end;/======================PLSQL异常处理*****************************************1、声明异常 异常名 EXCEPTION；2、抛出异常 RAISE 异常名3、处理异常 抛出异常后的逻辑代码不会被继续执行异常的定义使用 ――――――――――――――――――――――――――――――――――――― begin dbms_output.put_line(1/0); exception when others then dbms_output.put_line('error'); end; declare e_myException exception; begin dbms_output.put_line('hello'); raise e_myException; --raise 抛出异常,用此关键字，抛出后转到自定义的e_myException ，执行其里面的putline函数后，再跳到end处，结束PL/SQL块，raise接下面的2句不会继续执行。 dbms_output.put_line('world'); dbms_output.put_line(1/0); exception when e_myException then dbms_output.put_line(sqlcode); --当前会话执行状态，错误编码 dbms_output.put_line(sqlerrm); --当前错误信息 dbms_output.put_line('my error'); when others then dbms_output.put_line('error'); end; ―――――――――――――――――――――――――――――――――――――<触发器>特定的事件，启动数据库，登录数据库，关闭数据库等系统事件，执行dml和ddl等操作触发器分3类：dml触发器---依据于基本表或者简单视图建立的触发器instead of 触发器-- 依据于复杂视图建立的触发器系统事件触发器-- 系统事件建立的触发器组成：（1）触发事件： 引起触发代码执行的事件，这些事件可以启动和关闭例程，用户登录和断开会话，oracle错误信息特定表和视图的dml操作（2）触发条件：由when子句指定的逻辑表达式，可选，如指定when子句，当触发事件发生时候，when条件必须为true,触发器代码开始执行 (3) 触发时刻 ：操作前还是操作后触发after | before 单一触发事件的DML触发器DML触发器是基于表的触发器，当对某个表进行DML操作时会激活该类触发器。建立的语法格式：CREATE [OR REPLACE] TRIGGER trigger_nameBEFORE | AFTER trigger_event [OF column_name]ON table_name [FOR EACH ROW] [WHEN trigger_condition][DECLARE]BEGINtrigger_bodyEND [trigger_name]eg:change emp 9-18 create or replace trigger change_empbefore insert or update or delete on emp begin if (to_char(sysdate,'FMHH24') NOT between 9 and 18) then raise_application_error(-20000,'change only at 9 -18,please try again :)');end if; end change_emp;/SQL> alter trigger CHANGE_EMP disable;--->禁用triggerSQL> alter trigger CHANGE_EMP enable;--->开启triggerSQL> drop trigger CHANGE_EMP; --->删除trigger:SQL> select trigger_name,trigger_type,triggering_event,table_owner,base_object_type,status from user_triggers; --->查看trigger查看错误信息：SQL> show errors 命令建立一个emp中sal中修改记录的表，将修改前后的sal,修改时间，修改empno记录下来CREATE or replace trigger emp_gcafter update of sal on emp for each rowbegin insert into emp_c values(:old.empno,:old.sal,:new.sal,sysdate);end emp_gc;/多个触发事件的DML触发器：在3种DML操作中，指定一种以上的操作为触发事件，这样的DML触发器称为多个触发事件的DML触发器。当在触发器中同时包含多种触发事件（INSERT、UPDATE、DELETE），并且需要根据事件的不同进行不同的操作时，则可以在触发器代码中使用下面3个条件谓词加以区别：（1）INSERTING：当触发事件为INSERT时，条件谓词返回TRUE，否则返回FALSE。（2）UPDATING：当触发事件为UPDATE时，条件谓词返回TRUE，否则返回FALSE。（3）DELETING：当触发事件为DELETE时，条件谓词返回TRUE，否则返回FALSE。 例：建一触发器，禁止在非工作时段，增加、修改、删除emp中数据，根据不同操作，给出不同错误。CREATE OR REPLACE TRIGGER emp_tg_changeBEFORE INSERT OR UPDATE OR DELETE ON empBEGIN IF (TO_CHAR(SYSDATE,'FM HH24') NOT BETWEEN 10 AND 18) THEN CASEWHEN INSERTING THEN RAISE_APPLICATION_ERROR(-20001,'insert only at 9:00-18:00');WHEN UPDATING THEN RAISE_APPLICATION_ERROR(-20002,'update only at 9:00-18:00');WHEN DELETING THEN RAISE_APPLICATION_ERROR(-20003,'delete only at 9:00-18:00'); END CASE; END IF;END emp_tg_change;/系统事件触发器系统事件触发器基于数据库或模式。触发事件包括数据库事件（如STARTUP、SHUTDOWN等）、DDL事件（如CREATE、ALTER、DROP等）。建立系统触发器的语法格式如下：CREATE [OR REPLACE] TRIGGER trigger_nameBEFORE | AFTER trigger_eventON DATABASE | SCHEMA [WHEN trigger_condition]BEGINtrigger_bodyEND [trigger_name];例：建立系统事件触发器 sys_drop_tg,当在用户模式下执行DROP操作时，将删除的对象信息存入 event_drop表中。CREATE TABLE event_drop(user_name VARCHAR2(15),object_name VARCHAR2(15),object_type VARCHAR2(10),object_owner VARCHAR2(15),create_date DATE);CREATE OR REPLACE TRIGGER sys_drop_tgAFTER DROP ON SCHEMABEGININSERT INTO event_drop VALUES(USER,ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE,ORA_DICT_OBJ_OWNER,SYSDATE);END sys_drop_tg;/<procedure> 存储过程 create or replace procedure procedure_nameis |as begin body;end procedure_name;/过程调用：call｜execute procedure_name eg1:查看当前时间SQL> create or replace procedure display_time 2 as 3 begin 4 dbms_output.put_line('current time is '||sysdate); 5 end display_time; 6 /SQL> set serveroutput onSQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; --->当前会话生效SQL> execute display_time();current time is 2010-08-05 14:46:21我们发现，存储过程本质上就是对sql语句的封装。sql语句以程序块的形式被封装在了数据库中了。写好了以后，无论是 java，还是 .net ，抑或其他开发语言，可以像使用sql语句那样随意调用。这就是数据库开发。函数与过程很相似，但有差别：1、函数有返回值，过程没有。2、函数调用在一个表达式中，过程则是作为pl/sql程序的一个语句存储过程入参，不论类型，缺省 情况下值都为null ,入参和出参不能有长度,其中关键字as可以替换成is,存储过程中变量声明在as和begin之间.同时，存储过程中可以再调用其它的存储过程，如果要保证存储过程之间的事务处理不受影响，可以定义为自治事务。查看过程：dba_procedures、user_proceduresSQL> select owner,object_name,procedure_name from dba_procedures where owner='SCOTT'; -->查看过程SQL> select text from dba_source where name='DISPLAY_SAL'; -->查看过程内容SQL> drop procedure scott.display_sal;-->删除过程有参数过程：带有参数的过程，指定参数名字，模式，数据类型 调用需要给出对应参数有三种模式:in 只能将行参传递到程序内部out 行参在子程序内部被赋值，然后将其传递给实参in out 同时具有以上2种参数特性 输入参数in，输入参数不能进行:=赋值，但可以将它赋给as后面定义的变量； 输入参数in，可以作为变量进行条件判断； 默认不写就是in；＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ create or replace procedure say_hello(v_name in varchar2) as begin --v_name:='a'; --存储过程入参v_name不能做为赋值目标 dbms_output.put_line('hello '||v_name); end;＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿存储过程输入参数作为变量进行条件判断 create or replace procedure say_hello( i_name in varchar2 ) as v_name varchar2(100); begin if i_name is null then v_name :='0'; else v_name :=i_name;--将入赋值给定义变量 end if; dbms_output.put_line('hello '||v_name); end;调用：exec myp('dsag'); -->用exec调用或：exec myp('');＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿多个参数的存储过程 create or replace procedure say_hello( v_first_name in varchar2, v_last_name in varchar2) as begin dbms_output.put_line('hello '||v_first_name||'.'||v_last_name); end;＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿out输出参数，用于利用存储过程给一个或多个变量赋值，类似于返回值 create or replace procedure say_hello( v_name in varchar2, v_content out varchar2 ) as begin v_content:='hello'||v_name; end; 调用： declare v_con varchar2(200); v_in varchar2(20):='wang'; begin say_hello(v_in,v_con); -->注意这里调用时没用exec dbms_output.put_line(v_con); end;＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿in out参数，既赋值又取值 create or replace procedure say_hello(v_name in out varchar2) as begin v_name:='hi '||v_name; end; 调用： declare v_inout varchar2(20):='wangsu'; begin say_hello(v_inout); -->先将值赋给定义好的变量，调用时用变量作参数 dbms_output.put_line(v_inout); end;＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿对存储过程入参赋缺省值 create or replace procedure say_hello( v_name varchar2 default 'susu', v_content varchar2 default 'hello' ) as begin dbms_output.put_line(v_name||' '||v_content); end; 调用：(用指明形参名的方式调用更好) begin say_hello(); end; 或 begin say_hello('cheng'); end; 或 begin say_hello(v_name=>'cheng'); end;＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿eg：定义一个过程display_sal,以job为参数，查询并输出该job的平均工作和最高工资和最低工资create or replace procedure display_sal(v_job emp.job%type)as v_avg_sal emp.sal%type; v_min_sal emp.sal%type; v_max_sal emp.sal%type;begin select avg(sal) into v_avg_sal from emp where job=v_job; select max(sal) into v_max_sal from emp where job=v_job; select min(sal) into v_min_sal from emp where job=v_job; dbms_output.put_line(v_job||' avg sal is '||v_avg_sal); dbms_output.put_line(v_job||' max sal is '||v_max_sal); dbms_output.put_line(v_job||' min sal is '||v_min_sal);exception when no_data_found then dbms_output.put_line('not found ~~~~~');end display_sal;/调用过程：SQL> set serveroutput onSQL> EXECUTE display_sal('CLERK');CLERK avg sal is 3022CLERK max sal is 8888CLERK min sal is 800＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿eg:定义一个修改记录的过程，一个输入参数，2个输出参数，输入id,之后将修改结果返回create or replace procedure oo_emp(v_no in emp.empno%TYPE, V_NAME out emp.ename%TYPE, V_SAL OUT EMP.sal%TYPE)AS v_job emp.job%TYPE;begin select job into v_job from emp where empno=v_no; CASE WHEN v_job='CLERK' then update emp set sal=sal*2 where empno=v_no; when v_job='MANAGER' then update emp set sal=sal*3 where empno=v_no; END CASE;select ename,sal into v_name,v_sal from emp where empno=v_no;END oo_emp;/调用过程：SQL> variable v_name varchar2(30);SQL> variable v_sal number;SQL> execute oo_emp(7900,:v_name,:v_sal);SQL> print v_name V_NAME--------------JAMESSQL> print v_sal; V_SAL---------- 17776＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿SQL CURSOR 游标是一个独立SQL工作区，有两种性质的游标： 隐式游标： 当PARSE 和EXECUTE 时使用隐式游标。 显式游标： 是由程序员显式声明的。 游标的属性： SQL%ROWCOUNT：一个整数值，最近SQL语句影响的行数。 SQL%FOUND BOOLEAN属性，如果为TRUE，说明最近的SQL STATEMENT有返回值。 SQL%NOTFOUND 与SQL%FOUND相反 SQL%ISOPEN 在隐式游标中经常是FALSE，因为执行后立即自动关闭了。 SQL> variable row_de number SQL> r 1 declare 2 v_deptno number:=10; 3 begin 4 delete from emp where 5 deptno=v_deptno; 6 :row_de:=sql%rowcount; 7* end; PL/SQL 过程已成功完成。 SQL> print row_de --这是一个SQL PLUS变量 ROW_DE ---------- 4 这时其实并没有真正的删除，而是需要 COMMIT或ROLLBACK，来完成事务。
