基本语法 存储过程 触发器介绍

1、Oracle基本语法 存储过程 触发器介绍PL/SQL 语言介绍PL/SQL是Oracle数据库的编程语言。我们可以将PL/SQL看成是对标准SQL的扩展，使用它编写的代码通常是放在Oracle数据库中执行。概述PL/SQL是Oracle数据库专用的语言，具有第三代编程语言和第四代编程语言的特性，对大小写不敏感。基于程序块的开发PL/SQL代码使用了程序块（block），利用模块化的方式进行构建，每一个程序块都是由一组逻辑上的变量、可执行代码以及异常处理代码构成。其中，只有可执行代码部分是必须的。set serveroutput ondeclare l_text varchar2(100);-

2、请注意这里的分号begin l_text:='Hello,world!'-请注意这里的冒号 dbms_output.put_line(l_text);-请注意这里的点号 exception when others then dbms_output.put_line('出现问题啦.'); raise;end;/请注意观察上述代码，每一个执行语句都是使用分号作为结束标记。由declare部分引出的是程序块的声明部分，通常情况下声明部分是定义所有变量和常量的地方，该部分是可选的。由begin、end引出的是程序块的执行部分，又称为执行体，这里通常是处理执行逻辑的地方，

3、该部分是必须的。（注意：可以直接写null，代表什么操作都不做）由exception部分引出的是程序块的异常处理部分，这里是我们检查和控制可能会在程序块中遇到的错误的地方，Oracle会在错误发生时，自动的跳转到这里。声明通过上面的代码，我们可以看到在声明中可以使用变量和常量。请注意，变量赋初值是可选的，而常量则是必须的。变量和常量在使用之前必须在程序块的声明部分进行声明（或定义）。declare l_number_variable number:=50;begin null;end;/declare l_number_constant constant number:=20;begin nu

4、ll;end;/现在我们尝试下述做法，请注意Oracle的出错提示declare l_number_constant constant number;begin null;end;/Oracle提示“常数 'L_NUMBER_CONSTANT' 的说明必须包含初始赋值”declare l_number_constant constant number :=20;begin l_number_constant:=50;end;/Oracle提示“表达式 'L_NUMBER_CONSTANT' 不能用作赋值目标”前一种异常会在程序块的编译期间被捕获，并且不能恢复，程

5、序块的异常处理部分也不能捕获这种错误。（请考虑如果在后一种情况下我们加上自己的异常处理代码是否能够捕获错误）为变量和常量赋值的时候均是采用“:=”操作符，存储过程存储过程（stored procedure）从本质上来讲就是命名的PL/SQL程序块，它可以被赋予参数。以下不经特别说明，过程均指的是存储过程。create procedure p_Test as begin null; end p_Test; / 请注意，在创建过程的时候，我们要在最后放置一个“/”（执行）符号。标准存储过程的语法如下CREATE OR REPLACE PROCEDURE procedure_name (parame

6、ter,parameter.) AUTHID DEFINER|CURRENT_USER IS|AS PRAGMA AUTONOMOUS_TRANSACTION; local declarations BEGIN executable statements EXCEPTION exception handlers END procedure_name;存储过程的语法和匿名PL/SQL程序块非常类似，只需要将PL/SQL程序块的declare部分替换成create or replace procedure procedure_name as即可（在begin-end之间可能有些小的改动）。decl

7、arebegin dbms_output.put_line('Hello,world');end;/-create procedure p_hello asbegin dbms_output.put_line('Hello,world');end p_hello;/执行存储过程的方式有两种，请参阅下述示例set serveroutput onbegin p_hello;end;/-execute p_hello;两种方式的效果完全一样。过程可以进行参数化的处理，过程的调用者传递一个值即可以使用它。参数可以是任何合法的PL/SQL类型，通常采用3种形式：IN、OU

8、T和IN OUT。IN-通过调用者传入，只能够由过程读取。 OUT-参数能够由过程写入。 IN OUT-过程能够读取或写入它们的值。SEQUENCE 对象介绍CREATE SEQUENCE seq_slof_apply INCREMENT BY 1 START WITH 2 MAXVALUE 999999999999999/SQL> select seq_slof_apply.nextval from dual; NEXTVAL- 2-我们要使用的存储过程create or replace procedure get_maxid( sign_str in varchar2, -这里的标志

9、前缀由每个小组自己控制,调用时从外部传入 new_id out varchar2 -使用OUT参数，准备用来获得新的id)asnew_count number;begin select count(*) into new_count from slof_apply; -请注意，在PL/SQL程序块中，此处必须加into if new_count=0 then -第一次调用时数据库没有数据,我们应该做初始化设置 new_id:=sign_str|'000000000000001' -请注意这里赋值的写法 else select seq_slof_apply.nextval int

10、o new_id from dual; while length(new_id)<15 loop new_id:='0'|new_id; end loop; new_id:=sign_str|new_id; end if;end get_maxid;/Create table slof_apply(Apply_id number(15)set serveroutput on declare v_num1 varchar2(20); begin get_maxid('LL_',v_num1); dbms_output.put_line('v_num1

11、='|v_num1); end;/触发器分为语句级触发器和行级触发器我们先看一个AFTER-INSERT-STATEMENT触发器：CREATE OR REPLACE TRIGGER temp_aisAFTER INSERT ON TEMPBEGIN dbms_output.put_line('executing temp_ais');END;看一下下面语句的结果：SQL> set feedback offSQL> INSERT INTO temp VALUES (1); - insert 1 rowexecuting temp_aisSQL> INS

12、ERT INTO temp VALUES (1); - insert 1 rowexecuting temp_aisSQL> INSERT INTO temp SELECT * FROM temp; - insert 2 rowsexecuting temp_ais每个SQL插入语句将触发一次，行级触发器最后一条语句要触发两次。一、事件顺序如果一个update SQL语句更新三行，那么行级触发器触发三次，语句级触发器触发一次。二、insert 语句级触发器定义语法语法如下：CREATE OR REPLACE TRIGGER trigger_nameAFTER | BEFORE INSER

13、T ON table_nameDECLARE Local declarationsBEGIN Body written PL/SQLEND;语句级和行级触发器在语法上关键的不同在于：FOR EACH ROW字句。在行级触发器中指定这个子句而语句级触发器中不需要指定。 在语句级触发器中： ·引用:NEW.COLUMN_NAME :OLD.COLUMN_NAME是不正确的。(1)第一种方法 ORACLE一般的做法是同时使用序列和触发器来生成一个自增字段.CREATE SEQUENCE SEQname INCREMENT BY 1 START WITH 1 MAXVALUE 9999999

14、9/CREATE TRIGGER TRGnameBEFORE INSERT ON table_nameFOR EACH ROWBegin SELECT SEQname.NEXTVAL INTO :NEW.FIELDname FROM DUAL;End;(2)第二种方法：CREATE OR REPLACE TRIGGER TR1 BEFORE INSERT ON temp_table FOR EACH ROWdeclare com_num NUMBER;BEGINSELECT MAX(ID) INTO COM_NUM FROM TEMP_TABLE; :NEW.ID:=COM_NUM+1;END

15、 TR1;package test;import java.sql.*;import connection.MyConnection;import oracle.jdbc.*;public class test_proc public static void main(String g)Connection conn;MyConnection myconn = new MyConnection();conn = myconn.getConnection();String sign_str = "LL_"String new_id = ""String s

16、trSQL = "call get_maxid(?,?)"tryCallableStatement callstmt = conn.prepareCall(strSQL);callstmt.setString(1,sign_str);callstmt.registerOutParameter(2,Types.VARCHAR);callstmt.execute();new_id = callstmt.getString(2);System.out.println("new_id = "+new_id);catch(Exception e)CREATE OR

17、 REPLACE TRIGGER delete_plcAFTER DELETE ON whl_plmFOR EACH ROWBEGINdelete from whl_plc where plc_pickinglistid=:old.plm_pickinglistid;END;create or replace procedure get_maxid( sign_str in varchar2, -这里的标志前缀由每个小组自己控制,调用时从外部传入 new_id out varchar2 -使用OUT参数，准备用来获得新的id)asnew_count number;begin select count(*) into new_count from whl_plm; -请注意，在PL/SQL程序块中，此处必须加into if to_