PL/SQL的组成元素

嘿嘿,无聊,这里给讲讲有关PL/SQL的组成部分
1:首先PL/SQL的组成元素有
块,变量名,数据类型,数据,变量声明,赋值语句,表达式,注释
这其中块的定义是最重要的：块定义分为四种：
分别是：有名块，无名块，子程序，触发器；子程序中又有：函数，存储过程，包！
这里也做简单介绍：
[b](2):有名块的定义[/b]

DROP TABLE book_info;

CREATE TABLE book_info(type_code VARCHAR2(20),type_remark VARCHAR2(20));

SELECT * FROM book_info;

--有名块的定义
<<inserttype>>
DECLARE
       v_TypeCode1 VARCHAR2(6) := 'CC';
       v_TypeCode2 VARCHAR2(6) := 'DD';
       v_TypeRemark1 VARCHAR2(15) :='Computer';
       v_TypeRemark2 VARCHAR2(15) :='C++ Language';
       v_OutPut VARCHAR2(20);
BEGIN
       INSERT INTO book_info VALUES(v_TypeCode1,v_TypeRemark1);
       INSERT INTO book_info VALUES(v_TypeCode2,v_TypeRemark2);

       SELECT type_remark INTO v_OutPut FROM book_info WHERE type_code = 'CC' AND ROWNUM <= 1;
       dbms_output.put_line(v_OutPut);
       SELECT type_remark INTO v_OutPut FROM book_info WHERE type_code= 'DD' AND ROWNUM <=1;
       dbms_output.put_line(v_OutPut);
END;

[b](3):这里是子程序的事例[/b]

DROP TABLE book_info;

CREATE TABLE book_info(type_code VARCHAR2(20),type_remark VARCHAR2(20));

SELECT * FROM book_info;

下面是一个存储过程的例子:
create or replace procedure inserttype as
       v_TypeCode1 VARCHAR2(6) := 'CC';
       v_TypeCode2 VARCHAR2(6) := 'DD';
       v_TypeRemark1 VARCHAR2(15) :='Computer';
       v_TypeRemark2 VARCHAR2(15) :='C++ Language';
       v_OutPut VARCHAR2(20);
BEGIN
       INSERT INTO book_info VALUES(v_TypeCode1,v_TypeRemark1);
       INSERT INTO book_info VALUES(v_TypeCode2,v_TypeRemark2);

       SELECT type_remark INTO v_OutPut FROM book_info WHERE type_code = 'CC' AND ROWNUM <= 1;
       dbms_output.put_line(v_OutPut);
       SELECT type_remark INTO v_OutPut FROM book_info WHERE type_code= 'DD' AND ROWNUM <=1;
       dbms_output.put_line(v_OutPut);
END;

可以看出把declare改成create or replace procedure *** as时,有名块就变成存储过程了，确实是这样的！

下面是一个触发器的例子:

CREATE OR REPLACE TRIGGER salary_trigger
       --当向auths表中插入一行或修改一行后激活该触发器
       BEFORE INSERT OR UPDATE OF salary ON auths
       FOR EACH ROW
BEGIN
       IF :new.salary > 100 THEN
          raise_application_error(-200060,'插入工资值应小于10000');
       END IF;
END salary_trigger;

:idea: 有帮助记得美言呀！