oracle_PLSQL_语法详细手册

具体:http://www.verydemo.com/demo_c134_i3305.html

第一部分  SQL语法部分

 

 

一、        Create table 语句

 

语句:   CREATE TABLE [schema.]table_name

     ( { column datatype [DEFAULT expr] [column_constraint] ...

       | table_constraint}

    [, { column datatype [DEFAULT expr] [column_constraint] ...

       | table_constraint} ]...)

    [ [PCTFREE  integer] [PCTUSED  integer]

      [INITRANS integer] [MAXTRANS integer]

      [TABLESPACE tablespace]

      [STORAGE storage_clause]

      [ RECOVERABLE | UNRECOVERABLE ]

    [  PARALLEL ( [ DEGREE { integer | DEFAULT } ]

                  [ INSTANCES { integer | DEFAULT } ]

                )

     | NOPARALLEL ]

    [  CACHE | NOCACHE  ]

    | [CLUSTER cluster (column [, column]...)] ]

    [ ENABLE   enable_clause

    | DISABLE disable_clause ] ...

[AS subquery]

表是Oracle中最重要的数据库对象,表存储一些相似的数据集合,这些数据描述成若干列或字段.create table 语句的基本形式用来在数据库中创建容纳数据行的表.create table 语句的简单形式接收表名,列名,列数据类型和大小.除了列名和描述外,还可以指定约束条件,存储参数和该表是否是个cluster的一部分.

Schema 用来指定所建表的owner,如不指定则为当前登录的用户.

Table_name 用来指定所创建的表名,最长为30个字符,但不可以数字开头(可为下划线),但不可同其它对象或Oracle的保留字冲突.

   Column 用来指定表中的列名,最多254个.

Datatype 用来指定列中存储什么类型的数据,并保证只有有效的数据才可以输入.

column_constraint 用来指定列约束,如某一列不可为空,则可指定为not null.

table_constraint 用来指定表约束,如表的主键,外键等.

Pctfree 用来指定表中数据增长而在Oracle块中预留的空间. DEFAULT为10%,也就是说该表的每个块只能使用90%,10%给数据行的增大时使用.

Pctused 用来指定一个水平线,当块中使用的空间低于该水平线时才可以向该中加入新数据行.

Parallel 用来指定为加速该表的全表扫描可以使用的并行查询进程个数.

Cache 用来指定该表为最应该缓存在SGA数据库缓冲池中的候选项.

Cluster 用来指定该表所存储的 cluster.

Tablespace 用来指定用数据库的那个分区来存储该表的数据.

Recoverable|Unrecoverable 用来决定是否把对本表数据所作的变动写入Redo 文件.以恢复对数据的操作.

As 当不指定表的各列时,可利用As子句的查询结果来产生数据库结构和数据.

例:

   1) create table mytab1e(mydec decimal,

                       myint inteter)

                 tablespace user_data

                 pctfree   5

                 pctused   30;

   2) create table mytable2

             as ( select * from mytable1);


二、        create sequence语句

 

语句:  CREATE SEQUENCE [schema.]sequence_name

[INCREMENT BY integer]

    [START WITH integer]

    [MAXVALUE integer | NOMAXVALUE]

    [MINVALUE integer | NOMINVALUE]

    [CYCLE | NOCYCLE]

    [CACHE integer | NOCACHE]

    [ORDER | NOORDER]

序列用来为表的主键生成唯一的序列值.

Increment by 指定序列值每次增长的值

Start with 指定序列的第一个值

Maxvalue 指定产生的序列的最大值

Minvalue 指定产生的序列的最小值

Cycle 指定当序列值逵到最大或最小值时,该序列是否循环.

Cache 指定序列生成器一次缓存的值的个数

Order 指定序列中的数值是否按访问顺序排序.

例:

    1) create sequence myseq

         increment by 4

         start with 50

         maxvalue 60

         minvalue 50

         cycle

         cache 3;

2)

   sql> create sequence new_s;

   sql>insert into new (new_id,last_name,first_name)

       values(new_s.nextval,’daur’,’permit’);

 

 

 

 

 


三、        create view语句

 

语句: CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [schema.]view_name

    [(alias [,alias]...)]

    AS subquery

    [WITH CHECK OPTION [CONSTRAINT constraint]]

视图实际上是存储在数据库上旳 select语句.每次在sql语句中使用视图时,表示该视图的select语句就用来得到需要的数据.

Or replace 创建视图时如果视图已存在,有此选项,新视图会覆盖旧的

           视图.

Force 如有此选项,当视图基于的表不存在或在该模式中没有创建视图的权限时,也可以建立视图.

As subquery 产生视图的select查询语句

With check option 如果视图是基于单表的且表中所有的非空列都包含在视图中时,该视图可用于insert和update语句中,本                选项保证在每次插入或更新数据后,该数据可以在视                  图中查到

例:

1)      create or place view new_v

as

   select substr(d.d_last_name,1,3),

      d.d_lastname,d.d_firstname,b.b_start_date,b.b_location

   from new1 d,

        new2 b

   where d.d_lastname=b.b_lastname;


四、        INSERT语句:

1.  语法

INSERT INTO [schema.]{table | view | subquery }[@dblink]

    [ (column [, column] ...) ]

    {VALUES (expr [, expr] ...) | subquery}

    [WHERE condition]

 

2.  插入单行

使用VALUES关键词为新行的每一列指定一个值.如果不知道某列的值,可以使用NULL关键词将其值设为空值(两个连续的逗号也可以表示空值,也可使用NULL关键词)

插入一行时试图为那些NOT NULL的列提供一个NULL值,会返回错误信息.

 

举例:

插入一条记录到DEPARTMENT表中

 

 

INSERT INTO DEPARTMENT

       (DEPARTMENT_ID,NAME,LOCATION_ID)

VALUES (01,’COMPUTER’,167)

 

3.  插入多行

将SELECT语句检索出来的所有数据行都插入到表中.这条语句通常在从一个表向另一个表快速复制数据行.

 

举例:

INSERT INTO ORDER_TEMP

SELECT A.ORDER_ID,B.ITEM_ID,C.NAME,E.FIRST_NAME||'.'||E.LAST_NAME,

       A.ORDER_DATE,A.SHIP_DATE,D.DESCRIPTION,B.ACTUAL_PRICE,

       B.QUANTITY,B.TOTAL

  FROM SALES_ORDER A,    ITEM B,  CUSTOMER C,

       PRODUCT D,        EMPLOYEE E

 WHERE MONTHS_BETWEEN(TO_DATE(A.ORDER_DATE),TO_DATE('01-7月-91'))>0

       AND A.CUSTOMER_ID=C.CUSTOMER_ID

       AND C.SALESPERSON_ID=E.EMPLOYEE_ID

       AND A.ORDER_ID=B.ORDER_ID

       AND B.PRODUCT_ID=D.PRODUCT_ID

 

4.  从其它表复制数据:

要快速地从一个表向另一个尚不存在的表复制数据,可以使用CREATE TABLE语句定义该表并同时将SELECT语句检索的结果复制到新表中.

CREATE TABLE EMPLOYEE_COPY

      AS

      SELECT *

        FROM EMPLOYEE


五、        UPDATE语句:

1.  语法

UPDATE [schema.]{table | view | subquery}[@dblink] [alias]

    SET { (column [, column] ...) = (subquery)

        |  column = { expr | (subquery) } }

     [,    { (column [, column] ...) = (subquery)

        |  column = { expr | (subquery) } } ] ...

    [WHERE condition]

 

UPDATE语句更新所有满足WHERE子句条件的数据行.同样,该语句可以用SELECT语句检索得到.但SELECT必须只检索到一行数据值.否则报错.而且每更新一行数据,均要执行一次SELECT语句.

 

举例:

UPDATE EMPLOYEE_COP

   SET SALARY=

       SALARY-400

 WHERE TO_NUMBER(TO_CHAR(HIRE_DATE,'YYMMDD'))<850101

 

 UPDATE ITEM_COP A

    SET A.ACTUAL_PRICE=

      (

      SELECT B.LIST_PRICE

        FROM PRICE B,SALES_ORDER C

       WHERE A.PRODUCT_ID=B.PRODUCT_ID AND

             A.ORDER_ID=C.ORDER_ID AND

             TO_NUMBER(TO_CHAR(C.ORDER_DATE,'YYYYMMDD')) BETWEEN

             TO_NUMBER(TO_CHAR(B.START_DATE,'YYYYMMDD')) AND

             NVL(TO_NUMBER(TO_CHAR(END_DATE,'YYYYMMDD')),29991231)

      )


六、        DELETE语句:

1.  语法

DELETE [FROM] [schema.]{table | view}[@dblink] [alias]

    [WHERE condition]

 

DELETE语句删除所有满足WHERE子句条件的数据行.

 

举例:

DELETE FROM item

WHERE ORDER_ID=510


七、        TRUNCATE语句:

1.  语法

TRUNCATE [schema.]table

 

 


八、        各类Functions:

 

1.    转换函数:

 

1.1.         函數:TO_CHAR

语法:

    TO_CHAR(number[,format])

 

用途:

将一个数值转换成与之等价的字符串.如果不指定格式,将转换成最简单的字符串形式.如果为负数就在前面加一个减号.

 

Oracle为数值提供了很多格式,下表列出了部分可接受的格式:

元素

描述

示例

结果

9

返回指定位数的数值,前面为0,显示为空格

99999

784

-578

1234567

45.895

‘   784’

‘  -578’

‘######’

‘    46’

插入小数点

9999.99

784

45.3482

‘784.00’

’45.35’

在结果串的指定位置插入逗号

9,999,999

784

1234567

0.44

‘       784’

‘ 1,234,567’

‘         0’

$

返回值前面加一个元符号

$99,999

784

-578

‘    $784’

‘   -$578’

B

结果的整数部分如果是0,就显示成空格

B9999.9

784

0.44

‘  784.0’

‘     .4’

S

返回 有正负号的数值

S9999

784

‘+784’

EEEE

以科 计数法表示数值

9.9EEEE

45

0.0666

‘  4.5E+01’

‘  6.7E-02’

 

 

 

 


1.2.         函數:TO_CHAR

 

语法:

    TO_CHAR(date[,format])

 

用途:

将按format参数指定的格式将日期值转换成相应的字符串形式.同样,Oracle提供许多的格式模型,用户可以用它们的组合来表示最终的输出格式.唯一限制就是最终的掩码不能超过22个字符.下表列出了部分日期格式化元素.

 

Oracle为数值提供了很多格式,下表列出了部分可接受的格式:

 

格式

意义

D

用数字表示星期几(1,2,3,4,5,6,7)

DY

用三个字符表示星期几的缩写形式(MON)

DAY

星期几的完整表示(MONDAY)

DD

用数字表示一月中的几号

DDD

用数字表示年份中的天数

W

一月中的星期数

WW

一年中的星期数

MM

用数字表示月数

MON

用三个字母表示月的缩写(APR)

MONTH

月的完整英文表示(FEBRUARY)

Y

年份中的最后一个数字(9)

YY

年份中的最后二个数字(99)

YYY

年份中的最后三个数字(999)

YYYY

年份用四个数字表示(1999)

YEAR

年份的字母全部拼写(NINETEEN-NINETY-NINE)

AM或PM

午前或午后表示符

HH

用小时表示日期

MI

分钟表示小时

SS

秒钟表示分钟

SSSS

自午夜以来的秒数(这个数字经常在0-86399之间)

 

 

 

 

 

 


1.3.         函數:TO_DATE

 

语法:

    TO_DATE(string,format)

 

用途:

根据给定的格式将一个字符串转换成Oracle的日期值.

该函数的主要用途是用来验证输入的日期值.在应用程序中,用户必须验证输入日期是否有效,如月份是否在1~12之间和日期中的天数是否在指定月份的天数内.

 


1.4.         函數:TO_NUMBER

 

语法:

    TO_NUMBER(string[,format])

 

用途:

该函数将一个字符串转换成相应的数值.对于简单的字符串转换数值(例如几位数字加上小数点).格式是可选的.

 

 


2.   日期函数

 

2.1.         函數:ADD_MONTHS

 

语法:

    ADD_MONTHS(date,number)

 

用途:

在日期date上加指定的月数,返回一个新日期.如果给定为负数,返回值为日期date之前几个月的日期.number应当是个整数,如果是小数,正数被截为小于该数的最大整数,负数被截为大于该数的最小整数.

 

例如:

    SELECT TO_CHAR(ADD_MONTHS(sysdate,1),

               'DD-MON-YYYY') "Next month"

      FROM dual

 

Next month

-----------

19-FEB-2000

 

 

2.2.         函數:LAST_DAY

 

语法:

    LAST_DAY(date)

 

用途:

返回日期date所在月份的最后一天的日期.

 

例如:

    SELECT SYSDATE, LAST_DAY(SYSDATE) "Last",

           LAST_DAY(SYSDATE) - SYSDATE "Days Left"

      FROM DUAL

 

SYSDATE   Last       Days Left

--------- --------- ----------

19-JAN-00 31-JAN-00         12

 

2.3.         函數:MONTHS_BETWEEN

 

语法:

    MONTHS_BETWEEN(date1,date2)

 

用途:

返回两个日期之间的月份.如果两个日期月份内的天数相同(或者都是某个月的最后一天),返回值是整数.否则,返回值是小数,每于1/31月来计算月中剩余天数.如果第二个日期比第一个日期还早,则返回值是负数.

 

例如:

    SELECT MONTHS_BETWEEN(TO_DATE('02-02-1992', 'MM-DD-YYYY'),

                      TO_DATE('01-01-1992', 'MM-DD-YYYY'))

    "Months"

         FROM DUAL

 

        Months

    ----------

    1.03225806

 

    SELECT MONTHS_BETWEEN(TO_DATE('02-29-1992', 'MM-DD-YYYY'),

                        TO_DATE('01-31-1992', 'MM-DD-YYYY'))

    "Months"

    FROM DUAL

 

    Months

----------

         1

 

 

 

 

 

 

 

 

2.4.         函數:NEXT_DAY

 

语法:

    NEXT_DAY(date,day)

 

用途:

该函数返回日期date指定若天后的日期.注意:参数day必须为星期,可以星期几的英文完整拼写,或前三个字母缩写,或数字1,2,3,4,5,6,7分别表示星期日到星期六.例如,查询返回本月最后一个星期五的日期.

 

例如:

    SELECT NEXT_DAY((last_day(sysdate)-7),'FRIDAY')

      FROM dual

 

NEXT_DAY(

---------

28-JAN-00

 

 

2.5.         函數:ROUND

 

语法:

    NEXT_DAY(date[,format])

 

用途:

该函数把一个日期四舍五入到最接近格式元素指定的形式.如果省略format,只返回date的日期部分.例如,如果想把时间(24/01/00 14:58:41)四舍五入到最近的小时.下表显示了所有可用格式元素对日期的影响.

 

Format Element

Result

SS

24/01/00 14:58:41

MI

24/01/00 14:59:00

HH

24/01/00 15:00:00

DD

25/01/00 00:00:00

MM

01/02/01 00:00:00

YY

01/01/00 00:00:00

CC

01/01/00 00:00:00

 

例如:

    SELECT to_char(ROUND(sysdate,'HH'),'DD-MON-YY HH24:MI:SS')

      FROM dual

 

TO_CHAR(ROUND(SYSDATE,'HH'),'DD-MON-YYHH24:MI:SS')

-----------------------------------------------------------------

24-JAN-00 15:00:00

 

2.6.         函數:TRUNC

 

语法:

    TRUNC(date[,format])

 

用途:

TRUNC函数与ROUND很相似,它根据指定的格式掩码元素,只返回输入日期用户所关心的那部分,与ROUND有所不同,它删除更精确的时间部分,而不是将其四舍五入.

 

例如:

    SELECT TRUNC(sysdate)

      FROM dual

 

TRUNC(SYS

---------

24-JAN-00

 

FLOOR函数:求两个日期之间的天数用;

select floor(sysdate - to_date('20080805','yyyymmdd')) from dual;

 

 


3.   字符函数

3.1.         函數:ASCII

 

语法:

ASCII(character)

 

用途:

返回指定字符的ASCII码值.如果为字符串时,返回第一个字符的ASCII码值.

 

例如:

   SELECT ASCII('Z')

     FROM dual

 

ASCII('Z')

----------

        90

 

 

3.2.         函數:CHR

 

语法:

CHR(number)

 

用途:

该函数执行ASCII函数的反操作,返回其ASCII码值等于数值number的字符.该函数通常用于向字符串中添加不可打印字符.

 

例如:

    SELECT CHR(65)||'BCDEF'

      FROM dual

 

CHR(65

------

ABCDEF

 

 


3.3.         函數:CONCAT

 

语法:

CONCAT(string1,string2)

 

用途:

该函数用于连接两个字符串,将string2跟在string1后面返回,它等价于连接操作符(||).

 

例如:

    SELECT CONCAT(‘This is a’,’ computer’)

      FROM dual

 

CONCAT('THISISA','

------------------

This is a computer

它也可以写成这样:

    SELECT ‘This is a’||’ computer’

      FROM dual

 

'THISISA'||'COMPUT

------------------

This is a computer

这两个语句的结果是完全相同的,但应尽可能地使用||操作符.

 

 

3.4.         函數:INITCAP

 

语法:

INITCAP(string)

 

用途:

该函数将字符串string中每个单词的第1个字母变成大写字母,其它字符为小写字母.

 

 

 

 

 

例如:

    SELECT INITCAP(first_name||'.'||last_name)

      FROM employee

     WHERE department_id=12

 

INITCAP(FIRST_NAME||'.'||LAST_N

-------------------------------

Chris.Alberts

Matthew.Fisher

Grace.Roberts

Michael.Douglas

 

 

3.5.         函數:INSTR

 

语法:

INSTR(input_string,search_string[,n[,m]])

 

用途:

该函数是从字符串input_string的第n个字符开始查找搜索字符串的第m次出现,如果没有找到搜索的字符串,函数将返回0.如果找到,函数将返回位置.

 

例如:

    SELECT INSTR('the quick sly fox jumped over the

           lazy brown dog','the',2,1)

      FROM dual

 

INSTR('THEQUICKSLYFOXJUMPEDOVERTHELAZYBROWNDOG','THE',2,1)

----------------------------------------------------------

                                                        31

 

 

 

 

 

 

 

 

3.6.         函數:INSTRB

 

语法:

INSTRB(input_string,search_string[,n[,m]])

 

用途:

该函数类似于INSTR函数,不同之处在于INSTRB函数返回搜索字符串出现的字节数,而不是字符数.在NLS字符集中仅包含单字符时,INSTRB函数和INSTR函数是完全相同的.

 

 

3.7.         函數:LENGTH

 

语法:

LENGTH(string)

 

用途:

该函数用于返回输入字符串的字符数.返回的长度并非字段所定义的长度,而只是字段中占满字符的部分.以列实例中,字段first_name定义为varchar2(15).

 

语法:

    SELECT first_name,LENGTH(first_name)

      FROM employee

 

FIRST_NAME      LENGTH(FIRST_NAME)

--------------- ------------------

JOHN                             4

KEVIN                            5

 

3.8.         函數:LENGTHB

 

语法:

LENGTHB(string)

 

用途:

该函数用于返回输入字符串的字节数.对于只包含单字节字符的字符集来说LENGTHB函数和LENGTH函数完全一样.

 

 

3.9.         函數:LOWER

 

语法:

LOWER(string)

 

用途:

该函数将字符串string全部转换为小写字母,对于数字和其它非字母字符,不执行任何转换.

 

 

3.10.      函數:UPPER

 

语法:

UPPER(string)

 

用途:

该函数将字符串string全部转换为大写字母,对于数字和其它非字母字符,不执行任何转换.

 

 

3.11.      函數:LPAD

 

语法:

LPAD(string,length[,’set’])

 

用途:

在字符串string的左边加上一个指定的字符集set,从而使串的长度达到指定的长度length.参数set可以是单个字符,也可以是字符串.如果string的长度小于length时,取string字符串的前length个字符.

 

语法:

    SELECT first_name,LPAD(first_name,20,' ')

      FROM employee

 

FIRST_NAME      LPAD(FIRST_NAME,20,'')

--------------- -----------------------------------------

JOHN                            JOHN

KEVIN                          KEVIN

3.12.      函數:RPAD

 

语法:

RPAD(string,length[,’set’])

 

用途:

在字符串string的右边加上一个指定的字符集set,从而使串的长度达到指定的长度length.参数set可以是单个字符,也可以是字符串.如果string的长度小于length时,取string字符串的前length个字符.

 

例如:

    SELECT first_name,rpad(first_name,20,'-')

      FROM employee

 

FIRST_NAME      RPAD(FIRST_NAME,20,'-')

--------------- -----------------------------------------

JOHN            JOHN----------------

KEVIN           KEVIN---------------

 

 

3.13.      函數:LTRIM

 

语法:

LTRIM(string[,’set’])

 

用途:

该函数从字符串的左边开始,去掉字符串set中的字符,直到看到第一个不在字符串set中的字符为止.

 

例如:

    SELECT first_name,ltrim(first_name,'BA')

      FROM employee

     WHERE first_name='BARBARA'

 

FIRST_NAME      LTRIM(FIRST_NAM

--------------- ---------------

BARBARA         RBARA

 

3.14.      函數:RTRIM

 

语法:

RTRIM(string[,’set’])

 

用途:

该函数从字符串的右边开始,去掉字符串set中的字符,直到看到第一个不在字符串set中的字符为止.具有NULL值的字段不能与具有空白字符的字段相比较.

这是因为空白字符与NULL字符是完全不同的两种字符.该函数的另外一个用途是当进行字段连接时去掉不需要的字符.

 

3.15.      函數:SUBSTR

 

语法:

SUBSTR(string,start[,length])

 

用途:

该函数从输入字符串中取出一个子串,从start字符处开始取指定长度的字符串,如果不指定长度,返回从start字符处开始至字符串的末尾.

 

3.16.      函數:REPLACE

 

语法:

REPLACE(string,search_set[,replace_set])

 

用途:

该函数将字符串中所有出现的search_set都替换成replace_set字符串.可以使用该函将字符串中所有出现的符号都替换成某个有效的名字.如果不指定replace_set,则将从字符串string中删除所有的搜索字符串search_set.

 

例如:

SELECT REPLACE('abcdefbdcdabc,dsssdcdrd','abc','ABC')

  FROM dual

 

REPLACE('ABCDEFBDCDABC,

-----------------------

ABCdefbdcdABC,dsssdcdrd

3.17.      函數:TRANSLATE

 

语法:

TRANSLATE(string,search_set,replace_set)

 

用途:

该函数用于将所有出现在搜索字符集search_set中的字符转换成替换字符集replace_set中的相应字符.注意:如果字符串string中的某个字符没有出现在搜索字符集中.则它将原封不动地返回.如果替换字符集replace_set比搜索字符集search_set小,那么搜索字符集search_set中后面的字符串将从字符串string中删除.

 

例如:

SELECT TRANSLATE('GYK-87M','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',

                 9999999999xxxxxxxxxxxxxx')

  FROM dual

 

TRANSL

------

xx-99x

 

 

 

 

 

 

 

 

 

 

 


4.     数值函数

4.1.         函數:ABS

 

语法:

ABS(number)

 

用途:

该函数返回数值number的绝对值.绝对值就是一个数去掉符号的那部分.

 

 

4.2.         函數:SQRT

 

语法:

SQRT(number)

 

用途:

该函数返回数值number的平方根,输入值必须大于等于0,否则返回错误.

 

 

4.3.         函數:CEIL

 

语法:

CEIL(number)

 

用途:

该函数返回大于等于输入值的下一个整数.

 

 

4.4.         函數:FLOOR

 

语法:

FLOOR(number)

 

用途:

该函数返回小于等于number的最大整数.


4.5.         函數:MOD

 

语法:

MOD(n,m)

 

用途:

该函数返回n除m的模,结果是n除m的剩余部分.m,n可以是小数,负数.

 

 

4.6.         函數:POWER

 

语法:

POWER(x,y)

 

用途:

该函数执行LOG函数的反操作,返回x的y次方.

 

 

4.7.         函數:ROUND

 

语法:

ROUND(number,decimal_digits)

 

用途:

该函数将数值number四舍五入到指定的小数位.如果decimal_digits为0,则返回整数.decimal_digits可以为负数.

 

4.8.         函數:TRUNC

 

语法:

TRUNC(number[,decimal_pluces])

 

用途:

该函数在指定的小数字上把一个数值截掉.如果不指定精度,函数预设精度为0. decimal_pluces可以为负数.


4.9.         函數:SIGN

语法:

SIGN(number)

 

用途:

该函数返回number的符号,如果number为正数则返回1,为负数则返回-1,为0则返回0.

 

 

4.10.      函數:SIN

 

语法:

SIN(number)

 

用途:

该函数返回弧度number的正弦值.

 

 

4.11.      函數:SINH

 

语法:

SINH(number)

 

用途:

该函数返回number的返正弦值.

 

4.12.      函數:COS

 

语法:

COS(number)

 

用途:

该函数返回弧度number的三角余弦值.要用角度计算余弦,可以将输入值乘以0.01745转换成弧度后再计算.


4.13.      函數:COSH

 

语法:

COSH(number)

 

用途:

该函数返回输入值的反余弦值.

 

 

4.14.      函數:TAN

 

语法:

TAN(number)

 

用途:

该函数返回弧度number的正切值.

 

 

4.15.      函數:TANH

 

语法:

TANH(number)

 

用途:

该函数返回数值number的反正切值.

 

 

4.16.      函數:LN

 

语法:

LN(number)

 

用途:

该函数返回number自然对数.


4.17.      函數:EXP

 

语法:

EXP(number)

 

用途:

该函数返回e(2.71828183)的number次方.该函数执行自然对数的反过程.

 

 

4.18.      函數:LOG

 

语法:

LOG(base,number)

 

用途:

该函数返回base为底,输入值number的对数.

 

 


5.   单行函数:

单行函数中可以对任何数据类型的数据进行操作.

 

5.1.         函數:DUMP

语法:

DUMP(expression[,format[,start[,length]]])

 

用途:

该函数按指定的格式显示输入数据的内部表示.下表列出了有效的格式.

格式代码

描述

8

八进制

10

十进制

16

十六进制

17

单字符

 

例如:

    SELECT DUMP('FARRELL',16)

    FROM dual

 

DUMP('FARRELL',16)

----------------------------------

Typ=96 Len=7: 46,41,52,52,45,4c,4c

 

5.2.         函數:GREATEST

语法:

GREATEST(list of values)

 

用途:

该函数返回列表中项的最大值.对数值或日期来说,返回值是最大值或最晚日期,如果列表中包含字符串,返回值是按字母顺序列表中的最后一项.

 

例如:

    SELECT GREATEST(123,234,432,112)

      FROM dual

 

GREATEST(123,234,432,112)

-------------------------

                      432

5.3.         函數:LEAST

语法:

LEAST(list of values)

 

用途:

该函数返回列表中项的最小值.对数值或日期来说,返回值是最小值或最早日期,如果列表中包含字符串,返回值是按字母顺序列表中的第一项.

 

例如:

    SELECT LEAST(sysdate,sysdate-10)

      FROM dual

 

LEAST(SYS

---------

10-JAN-00

 

5.4.         函數:NVL

 

语法:

NVL(expression,replacement_value)

 

用途:

如果表达式不为空值,函数返回该表达式的值,如果是空值,就返回用来替换的值.

 

例如:

    SELECT last_name,

           NVL(TO_CHAR(COMMISSION),'NOT APPLICABLE')

      FROM employee

     WHERE department_id=30

LAST_NAME       NVL(TO_CHAR(COMMISSION),'NOTAPPLICABLE')

--------------- ----------------------------------------

ALLEN           300

WARD            500

MARTIN          1400

BLAKE           NOT APPLICABLE


6.   多行函数

组函数可以对表达式的所有值操作,也可以只对其中不同值进行操作,组函数的语法如下所示:

function[DISTINCT|ALL expression]

如果既不指定DISTINCT,也不指定ALL,函数将对查询返回的所有数据行进行操作.不能在同一个SELECT语句的选择列中同时使用组函数和单行函数.

 

6.1.         函數:AVG

语法:

AVG([DISTINCT|ALL] expression)

用途:

对查询返回的数据行求平均值.

 

例如:

    SELECT AVG(sal) "Average"

      FROM emp

       Average

    ----------

    2073.21429

 

6.2.         函數:COUNT

语法:

COUNT([DISTINCT|ALL] expression)

 

用途:

计算表达式的个数.要计算EMP表中雇员的个数.

例如:

    SELECT COUNT(deptno)

      FROM emp

COUNT(DEPTNO)

-------------

           14

    SELECT COUNT(distinct deptno)

      FROM emp

 

COUNT(DISTINCTDEPTNO)

---------------------

                    3

6.3.         函數:MAX

 

语法:

MAX([DISTINCT|ALL] expression)

 

用途:

对查询返回的行集求最大值.如果有多个最大值,将所有均返回.要检索公司中最高工资的雇员.

 

语法:

    SELECT ename,sal

      FROM emp

     WHERE sal=(select max(sal)

           FROM emp)

 

ENAME            SAL

---------- ---------

KING            5000

 

6.4.         函數:MIN

 

语法:

MIN([DISTINCT|ALL] expression)

 

用途:

对查询返回的行集求最小值.如果有多个最小值,将所有均返回.

 

例如:

    SELECT MIN(last_name)

      FROM employee

 

MIN(LAST_NAME)

---------------

ADAMS

 

 

 

 

6.5.         函數:SUM

 

语法:

SUM([DISTINCT|ALL] expression)

 

用途:

计算查询返回的所有非空数值的总和.如果返回的数据都是空值,则该函数也返回空值.

 

例如:

    SELECT SUM(salary)"Total"

      FROM employee

     WHERE department_id=10

 

    Total

---------

     8750

 

6.6.         函數:VARIANCE

 

语法:

VARIANCE([DISTINCT|ALL] expression)

 

用途:

该函数计算返回所有行的统计方差.

 

例如:

    SELECT VARIANCE(salary)

      FROM employee

 

VARIANCE(SALARY)

----------------

       973659.27

 

 

 

 

 

 


伪列

返回值

Sequence.CURRVAL

上一次由序列产生器产生的序列名值.只有在当前实例会话中从该序列选择过一次值,这个伪列才会有效.

LEVEL

查诣的深度,LEVEL适用于特殊的树查询.

Sequence.NEXTVAL

选择这个伪列将导致序列发生器返回该序列的下一个值.一旦选择了该值,它就不能被重用,因为以后的每次检索都将返回下一个值.

ROWID

这个伪列表示数据行确切的存储位置.ROWID的格式是一个三个16进制数的结构AAAAAAAA.BBBB.CCCC,这里AAAAAAAA表示数据库文件中包含该行的块号,BBBB是数据块内部的行号,而CCCC则是数据中的文件ID.

ROWNUM

被检索数据行的序列号.

SYSDATE

当前日期和时间.

UID

当前用户的标识ID.

USER

用户登录进数据库的名字.

 

 

 


 TABLE: LOCATION                                部门地址表

 -------------------- -------- ----

 LOCATION_ID          NOT NULL NUMBER(3)        地址ID

 REGIONAL_GROUP                VARCHAR2(20)     地址名

 

 TABLE: DEPARTMENT                              部门名称表

 -------------------- -------- ----

 DEPARTMENT_ID        NOT NULL NUMBER(2)        部门ID

 NAME                          VARCHAR2(14)     部门名称

 LOCATION_ID                   NUMBER(3)        地址ID(LOCATION.LOCATION_ID)

 

 TABLE: JOB                                     工种表

 -------------------- -------- ----

 JOB_ID               NOT NULL NUMBER(3)        工种ID

 FUNCTION                      VARCHAR2(30)     工种名称

 

 TABLE: EMPLOYEE                                雇员信息表

 -------------------- -------- ----

 EMPLOYEE_ID          NOT NULL NUMBER(4)        雇员ID

 LAST_NAME                     VARCHAR2(15)

 FIRST_NAME                    VARCHAR2(15)

 MIDDLE_INITIAL                VARCHAR2(1)     

 JOB_ID                        NUMBER(3)        工种ID(JOB.JOB_ID)

 MANAGER_ID                    NUMBER(4)        领导ID(EMPLOYEE.EMPLOYEE_ID)

 HIRE_DATE                     DATE             雇佣日期

 SALARY                        NUMBER(7,2)      薪水

 COMMISSION                    NUMBER(7,2)      佣金

 DEPARTMENT_ID                 NUMBER(2)        部门ID(DEPARTMENT.DEPARTMENT_ID)

 

 TABLE: SALARY_GRADE                            薪资等级表

 -------------------- -------- ----

 GRADE_ID             NOT NULL NUMBER(3)        等级ID

 LOWER_BOUND                   NUMBER(7,2)      最低工资

 UPPER_BOUND                   NUMBER(7,2)      最高工资

 

 TABLE: PRODUCT                                 产品信息表

 -------------------- -------- ----

 PRODUCT_ID           NOT NULL NUMBER(6)        品号

 DESCRIPTION                   VARCHAR2(30)     品名

 

 TABLE: PRICE                                   产品价格表

 -------------------- -------- ----

 PRODUCT_ID           NOT NULL NUMBER(6)        品号(PRODUCT.PRODUCT_ID)

 LIST_PRICE                    NUMBER(8,2)      价格

 MIN_PRICE                     NUMBER(8,2)      最低价格

 START_DATE           NOT NULL DATE             生效日期

 END_DATE                      DATE             失效日期

 

 TABLE: CUSTOMER                                客户信息表

 -------------------- -------- ----

 CUSTOMER_ID          NOT NULL NUMBER(6)        客户ID(CUSTOMER.CUSTOMER_ID)

 NAME                          VARCHAR2(45)     客户名

 ADDRESS                       VARCHAR2(40)     客户地址

 CITY                          VARCHAR2(30)     城市

 STATE                         VARCHAR2(2)      州

 ZIP_CODE                      VARCHAR2(9)      邮编

 AREA_CODE                     NUMBER(3)        区号

 PHONE_NUMBER                  NUMBER(7)        电话号码

 SALESPERSON_ID                NUMBER(4)        销售员ID(EMPLOYEE.EMPLOYEE_ID)

 CREDIT_LIMIT                  NUMBER(9,2)      信用限制

 COMMENTS                      LONG             备注

 

 TABLE: SALES_ORDER                             订单单头表

 -------------------- -------- ----

 ORDER_ID             NOT NULL NUMBER(4)        订单ID

 ORDER_DATE                    DATE             订单日期

 CUSTOMER_ID                   NUMBER(6)        客户ID(CUSTOMER.CUSTOMER_ID)

 SHIP_DATE                     DATE             出货日期

 TOTAL                         NUMBER(8,2)      总金额

 

 TABLE: ITEM                                    订单单身表

 -------------------- -------- ----

 ORDER_ID             NOT NULL NUMBER(4)        订单ID(SALES_ORDER.ORDER_ID)

 ITEM_ID              NOT NULL NUMBER(4)        订单行号

 PRODUCT_ID                    NUMBER(6)        品号(PRODUCT.PRODUCT_ID)

 ACTUAL_PRICE                  NUMBER(8,2)      实际价格

 QUANTITY                      NUMBER(8)        订单数量

 TOTAL                         NUMBER(8,2)      总金额

 

 

 

 


第二部分  PL/SQL语法部分

 

一、   PL/SQL语言简介

(本讲义之所有程序均调式通过)

首先我们看一个简单之例子,下面这个例子是统计从1至100的总和.

declare

i number:=0;          /*声明变量井给初值*/

t number:=1;

error_message exception;  /*声明一个出错处理*/

begin

  for t in 1..100 loop

    i:=i+t;

  end loop;

  if i>=5050 then

    raise error_message;  /*引发错误处理*/

  else

    insert into c_nt(c_t) values(i);

  end if;

exception

  when error_message then

  insert into c_nt(c_t) values(0);

end;

²  从上例中可以看出PL/SQL语法的一般规则.

­  PL/SQL中语句以分号(;)结尾.

­  开始程序块的PL/SQL语句(如IF…或BEGIN语句)没有分句.

­  文本值括在单引号(‘ ‘)内,而不是(“ “).

­  过程只允许最后有一个出口..

²  PL/SQL程序可以分为三个部分

­  DECLARE部分用于变量、常量、函数、过程、Cursor.

­   BEGIN部分包含PL/SQL块中要执行的代码 用于程序处理,其中可以调用函数、过程.

­ Exception 部分用于出错处理.

下面我们再看一个例子:

declare

i number :=1;

t number :=1;

p number :=1;

/*create table c_ny(c_t number,cou_t number);*/

function aa(xx number)return number is     /* define function*/

   tt number;

   ct number:=1;

    j number:=1;

begin

   while j<=xx loop

   ct:=ct+j;

   j:=j+1;

   end loop;

   return ct;

end aa;

begin

  /*create table c_nt(c_t number,cou_t number);*/

  commit;

  while i<=200 loop

    t:=t+i;

    i:=i+1;

    p:=aa(i);      /* calling function*/

    insert into c_nt values(t,p);

    commit;

  end loop;

end;

/

 

 

 

 

 

说明:

1.在定义变量可以赋初值,赋初值有两种方法,一为上程序所示,另一种为如下所示:

Declare

I number default 92;

T number default 0;

 

2.定义常量

Declare

I constant number:=1;

T constant number:=9;

 

3.定义函数

function function_name(parameter type)return type is

  …declare variant

begin

  …

  …

end function_name;

在上面的例子中我们定义了一个函数aa,在begin模块部分引用了此函数aa().

 

4.定义过程

procedure procedure_name(parameter IN type) is

…declare variant

begin

exception

end procedure_name;

 

 

 

 

见下例:

declare

/*t_emp  c_nt%rowtype;*/

i number:=1;

t number:=1;

procedure te_t(t_t number) is          /*定义一个函数*/

  begin

    insert into c_nt1(t_1) values(t_t);

end te_t;

begin

 

 

調用函數

  for i in 1..100 loop

    te_t(i);

  end loop;

end;

/

 

5.定义Cursor

declare

/*t_emp  c_nt%rowtype;*/

t_emp1 number;

t_emp2 number;

cursor tes_t1

is select * from c_nt;

begin

  open tes_t1;

  delete from c_nt1;

  commit;

  loop

    fetch tes_t1 into t_emp1,t_emp2;

    exit when tes_t1%notfound;

    insert into c_nt1 values(t_emp1,t_emp2);a

  end loop;

  close tes_t1;

  commit;

end;

/

 

 

我们在open 一个cursor时,可能会存在一种情况,即我们不需要cursor中所有之记录,我们该如何处理:

1.在定义一个cursor时,可以附带参数如下所示

declae

  cursor c1(p_emp_id) is

select emp_no,emp_name from dept_no  where emp_id = p_emp_id;

demp_pt c1%rowtype;

begin

      open c1(123);

 loop

 fetch c1(123) into demp_pt

 …

 

2.在将cursor中之记录项转到变量中时进行控制,如下所示:

declare

  cursor is

  select  empt_no,empt_name from dept_no;

  p_no    number;

  p_name  number;

begin

  loop

  fetch c1 into (p_no,p_name);

 

用於到cursor中變量進行控制

  if condition1  then

   …

   …

  end if

  

end;

 

注意:

²  因为PL/SQL不支持I/O,所以程序所有结果都是放在数据档中.

²  Delete from accts where status=’bad debt’

If sql%rowcount>10 then

   Raise out_of_bounds;

End if;

 

另:

在声明一个变量时,PL/SQL提供两种变量类型:%TYPE,%ROWTYPE.

1.%TYPE

使用%TYPE时,可以有种用法:

²  一用法见下例:

declare

Balance                          number(7,2);

Minimum_balance         balance%type:=10.00;

在上例中,minimum_balance数据类型为number(7,2)具默认值为10.00.

²  二用法见下例(将数据类型与table中一column datatype相对应起来,如果table中column datatype变更,则在运行时,上数据类型会自动的变换上):

declare

my_dname            empc.empto%type;

 

2.%rowtype

使用%rowtype数据类型用于将table和cursor中一数据行相对起来.

见下例:

 

 

 

 

 

 

 

 

²  Declare

 

定義一個%rowtype類型,與my_cursor中記錄行對應.

Cursor my_cursor is select sal+nvl(comm,0) salcomm,wages,ename from emp;

My_rec my_cursor%rowtype;

Begin

Open my_cursor;

 

將my_cursor中之記錄寫到my_rec變量中去.

loop

Fetch my_cursor into my_rec ;

Exit when my_cursor%notfound;

If my_rec.wages>200 then

    Insert into temp values (null,my_rec.wages,my_rec.ename);

End if;

End loop;

Close my_cursor;

End;

 

 

 

 

 

 


二、   变量说明

在PL/SQL中包括以下几种常见的变量类型:

CHAR-存储定长的ASCII字符串,允许存储数字,文本文符等,最长可255个字符.

VARCHAR2-存储变长的字符串,尽管伋按符串的最大长度来定义,但VARCHAR2和CHAR的区别在于如果达不到定义的长度,下的空间不会自动的填写为空格,VARCHAR2最大可以放入2000个字符.

DATE-实际上是存储时间信息的日期/时间戳,在使用日期时,应考虑怎样使用日期函数.有关日期函数的格式见函数说明部分.

 

127

NUMBER-存储数值数据,包括整数和浮点数、数据范围可以从1Ï10   到38Ï10   ,而且,你有很大的数据空间.

BOOLENA-存储布尔值.它表示是/否,真/假,1/0之类的东西.

 

LONG-这是一种文本字符串,其长度大于VARCHAR2字段的2000个字符.该类最多可储存2 GB个字符,与原始二进制数据相比,它只能存储字符信息.

RAW-用来存储操作系统使用的原始二进制数据,可用于存储像图像或声音记录这样的信息,但这种数据长度最长度只有255字节.

LONG RAW-与LONG类型等价,但存储二进制数据,最长可达2GB个字节.

 

31

31

BINARY_INTEGER-这个字段按计算器使用的二进制格式存储信息,从

       -2  到2  -1.

 

 

 

 

 

 

 

另:

PL/SQL提位两种复合类型:TABLE和RECORD

1.TABLE

²  要定义一个数组,你使用表类型定义语句,例如要定义Last_name数组,可以使用下述语句:

type last_name_list is table of varchar2(22)

index by binary_integer;

last_name last_name_list;

 

²  当定义一个长类型时,就涉及到一个删除表的问题,PL/SQL表不能用Delete语句来删除,但可以将每一行空值如下所示:

sal_tab(3):=null;

另一种法是定义两个相同类型的表类型,如果要将另一表清空,只需将空表给要清空的表即可.如下所示.

declare

type numtabtype is table of number

index by binary_integer;

sal_tab numbertype;

empty_tab numbertype;

begin

for I in 1..100 loop

  sal_tab(I):=I;

end loop;

….

End;

 

 

 

 

 

 

 

 

2.RECORD

Declare

Type deptrectype is record

(deptno                number(2),

 dname                char(14),

 loc                      char(14),);

dept_rec               deptrectype;

begin

       select deptno,dname,loc into dept_rec from dept where deptno=30;

       …

       …

end;

与所有的编程语言一样,定义一个变量时,同样存在变量作用范围问题:

如下所示:

 

變量x為 real類型.

declare

  x  real;

function  function_name(variant type)return type is

  declare

 

 

在此範圍之內變量x為char

    x char;

begin

  …

  end function_name;

 

變量x為real類型

begin

x:=expression1…

end;

 

 

 

如果想引用另一block之变量时,可以加上block label,如下所示:

 

Block label;

<<outer>>

declare

birthdate      date;

begin

   …

   declare

        birthdate   date;

 

 

引用outer block塊所定義之變量.

begin

….

If birthdate=outer.birthdate then

….

End if;

End;

End outer;

 

 

 

 

 

 

 

 

 

 

 

 


三、   PL/SQL控制程序流

1.IF …THEN…ELSIF…ELSE…….END IF

IF … THEN

 STATMENT1;

elseif…then…

 statment2

elseif…then…

 statment3

ELSE

 STATMENT4;

end if;

 

例:

<<outer>>

for ctr in 1..20 loop

  <<inner>>

  for ctr in 1..10 loop

   if outer.ctr>ctr then…

    …

  end loop inner;

end loop outer;

 

2.loop…exit…end loop

  loop

   sequence_of_statement;

   …

   exit;

end loop;

例:

 loop

  fetch c1 into …

exit when c1%notfound;

end loop;

close c1;

 

 

另:加loop label;

例:<<outer>>

   loop

    …

    loop

     …

     exit outer when…

    end loop;

     …

  end loop outer;

 

3.while…loop…end loop结构如下所示:

   while total<=25000 loop

     ….

     Select sa1 into salary from emp where…

       Total:=total+salary;

   End loop;

 

4.for…loop…

   例1.

      select count(empno) into emp_count from emp;

      for L in 1..emp_count loop

          …

      end loop;

 

例2.

   <<outer>>

   for step in 1..25 loop

       for step in 1..10 loop

          …

          if outer.step>15 then…

       end loop;

   end loop outer;

 

5.  cursor….loop

declare

sursor c1 is select ename from emp;

name varchar2(100);

begin

 

for p_c1_rec in c1 loop

       name := p_c1_rec.ename;

 

end loop;

 

 

 

 


四、   存储过程

要创建存储过程,可以使用下面的SQL和PL/SQL语句:

 

CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(parameter list)

AS

BEGIN

(SQL AND PL/SQL COMMANDS)

END;

 

五、   存储函数

create or replace function function_name (parameter list) return type is

….

Begin

 …

end;

 

 

 

六、   Package

 

Package分为两部分:Specific and Body

在包说明部分中,主要将此包中所含的过程和Function的调用参数说明清楚,如:

CREATE OR REPLACE PACKAGE ZDL_JOB_PKG

AS

PROCEDURE ZDL_INSERT_JOB(

    p_bkc_id in number,

    p_item_id in number,

    p_job_number in out varchar2,

    p_group_id in number,

    p_lead_day in number,

    p_load_type in number,

    P_STATUS_TYPE IN NUMBER,

    P_USER_ID IN NUMBER,

    P_JOB_TYPE IN VARCHAR2);

PROCEDURE ZDL_UPDATE_JOB(

           P_BKC_ID IN NUMBER,

           P_GROUP_ID IN NUMBER,

           P_STATUS_TYPE IN NUMBER,

           P_USER_ID IN NUMBER);

FUNCTION WIP_MASS_LOAD(P_GROUP_ID IN NUMBER,P_USER_ID IN NUMBER) RETURN NUMBER;

PROCEDURE ZDL_PRE_UPDATE;

FUNCTION ZDL_UPDATE_ORACLE_WIP(P_USER_ID IN NUMBER) RETURN NUMBER;

FUNCTION ZDL_JOB_STATUS (P_JOB_NUMBER IN VARCHAR2) RETURN NUMBER;

END ZDL_JOB_PKG;

在包体部分,主要将包说明部分之过程及Function之代码写出来,

如:

CREATE OR REPLACE PACKAGE BODY ZDL_JOB_PKG

AS

PROCEDURE ZDL_INSERT_JOB (

     p_bkc_id in number,    p_item_id in number,p_job_number in out varchar2,

     p_group_id in number,

     p_lead_day in number,

     P_LOAD_TYPE IN NUMBER,

     P_STATUS_TYPE IN NUMBER,

     P_USER_ID IN NUMBER,

     P_JOB_TYPE IN VARCHAR2)

is

…………

begin

…………

end ZDL_INSERT_JOB;

 

PROCEDURE ZDL_UPDATE_JOB(P_BKC_ID IN NUMBER,P_GROUP_ID IN NUMBER,P_STATUS_TYPE IN NUMBER,P_USER_ID IN NUMBER)

AS

…………

BEGIN

…………

END ZDL_UPDATE_JOB;

 

FUNCTION WIP_MASS_LOAD(P_GROUP_ID IN NUMBER,P_USER_ID IN NUMBER) RETURN NUMBER

as

…………

begin

…………

END WIP_MASS_LOAD;

 

PROCEDURE ZDL_PRE_UPDATE IS

…………

begin

…………

end ZDL_PRE_UPDATE;

 

FUNCTION ZDL_UPDATE_ORACLE_WIP(P_USER_ID IN NUMBER) RETURN NUMBER

IS

…………

begin

…………

end ZDL_UPDATE_ORACLE_WIP;

 

FUNCTION ZDL_JOB_STATUS (P_JOB_NUMBER IN VARCHAR2) RETURN NUMBER

AS

…………

BEGIN

…………

END ZDL_JOB_STATUS;

 

END ZDL_JOB_PKG;


七、   触发器

²  所需系统权限

要为某表创建触发器,必须能改变这个表,因此不仅要拥有表,并且要具有这个表的 alter权限,或者具有alter any table系统权限,除此之外,必须有create triger系统权限,若要在另一个用户账号(account)(也称之为模式(schema))上创建触发器,就必具有create any trigger系统权限.

 

²  所需表权限

触发器可以引用的表并不是初始化触发事件的表.

 

²  触发器

触发器有十二种类型.一个触发器的类型由执行触发器的层次位置和触发事务的类型定义.

 

²  行级触发器

在某个事务中,行级触发器行执行,对于上述ledger表中记例子而言,触发器.行级触发器是在create trigger命令中通过用for each row 子句创建的.

 

²  合法的触发器类型

当两种不同类型之触发动作相结合时,有十二种可能的配置:

Before insert              行级触发器

before insert              语句级触发器

after insert          行级触发器

after insert          语句级触发器

before update             行级触发器

before update             语句级触发器

after update               行级触发器

after update             语句级触发器

before delete              行级触发器

before delete              语句级触发器

after delete          行级触发器

after delete          语句级触发器

 

 

 

 

 

 

例:

CREATE OR REPLACE TRIGGER "APPS"."ZDL_BKC_JOB_BODY_AFI"

AFTER INSERT ON "APPS"."ZDL_BKC_JOB_BODY"

REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

BEGIN

  Insert into audit_tbl values(:new.id,:new……);

UPDATE ZDL_BKC_JOB_HEAD SET UPDATE_DATE = SYSDATE

        WHERE ZDL_BKC_JOB_HEAD_ID = :NEW.ZDL_BKC_JOB_BODY_ID;

END;

 

 

 

 

 

 

 

 

 


八、   应用实例

下面以电算部开发出之程序<<MDS展开及开工日维护程序>>之各类程序为例:

1.      Create table, index, sequence, table trigger

首先清除原先已有之重名table,Sequence等:

DROP TABLE ZDL_BKC_JOB_BODY;

DROP TABLE ZDL_BKC_JOB_HEAD;

DROP TABLE ZDL_BKC_JOB_UPDATE;

drop sequence zdl_bkc_job_s;

 

建立table, sequence以及Index

create table zdl_bkc_job_head

   (zdl_bkc_job_head_id number not null,

    assembly varchar2(9) not null,

    lot_no varchar2(240),

    job_no varchar2(240),

    OL_DATE date,

    quantity number,

    line_code varchar2(240),

    created_date date,

    update_date date,

    primary key(zdl_bkc_job_head_id)

);

 create table zdl_bkc_job_body

    (zdl_bkc_job_body_id number not null references zdl_bkc_job_head(zdl_bkc_job_head_id),

    level1 VARCHAR2(15),

    job1 varchar2(240),

    level2 VARCHAR2(15),

    job2 varchar2(240),

    level3 VARCHAR2(15),

    job3 varchar2(240),

    level4 VARCHAR2(15),

    job4 varchar2(240),

    level5 VARCHAR2(15),

    job5 varchar2(240));

create table ZDL_BKC_JOB_UPDATE

(    BKC_ID NUMBER NOT NULL,

     LOCATION_ID NUMBER NOT NULL,

     ACTION_ID NUMBER NOT NULL,

     JOB_NUMBER VARCHAR2(240),

     UPDATED_FLAG VARCHAR2(1),

     CREATION_DATE DATE,

     UPDATED_DATE DATE

);

create sequence zdl_bkc_job_s;

CREATE INDEX ZDL_BKC_JOB_HEAD_N1 ON ZDL_BKC_JOB_HEAD(ZDL_BKC_JOB_HEAD_ID,ASSEMBLY);

CREATE INDEX ZDL_BKC_JOB_BODY_N1 ON ZDL_BKC_jOB_BODY(ZDL_BKC_JOB_BODY_ID,LEVEL1);

CREATE INDEX ZDL_BKC_JOB_BODY_N2 ON ZDL_BKC_jOB_BODY(ZDL_BKC_JOB_BODY_ID,LEVEL2);

CREATE INDEX ZDL_BKC_JOB_BODY_N3 ON ZDL_BKC_jOB_BODY(ZDL_BKC_JOB_BODY_ID,LEVEL3);

CREATE INDEX ZDL_BKC_JOB_UPDATE_N1 ON ZDL_BKC_JOB_UPDATE(ACTION_ID,LOCATION_ID);

COMMIT;

 

建立table Trigger:

-- Trigger head after update

CREATE OR REPLACE TRIGGER "APPS".ZDL_BKC_JOB_HEAD_AFU

AFTER UPDATE OF "LINE_CODE", "LOT_NO", "OL_DATE", "QUANTITY" ON "APPS"."ZDL_BKC_JOB_HEAD"

REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

BEGIN

     INSERT INTO ZDL_BKC_JOB_UPDATE (

         BKC_ID,

         LOCATION_ID,

         ACTION_ID,

         JOB_NUMBER,

         UPDATED_FLAG,

         CREATION_DATE,

         UPDATED_DATE)

     VALUES(

         :OLD.ZDL_BKC_JOB_HEAD_ID,

         1,

         3,

         :OLD.JOB_NO,

         'N',

         SYSDATE,

         SYSDATE);

END;

 

-- Trigger body before delete

CREATE OR REPLACE TRIGGER "APPS"."ZDL_BKC_JOB_BODY_BRD"

BEFORE DELETE ON "APPS"."ZDL_BKC_JOB_BODY"

REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

BEGIN

     if :old.job1 is not null then

         INSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)

         VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB1,'N',SYSDATE,SYSDATE);

     END IF;

     if :old.job2 is not null then

         INSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)

         VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB2,'N',SYSDATE,SYSDATE);

     END IF;

     if :old.job3 is not null then

         INSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)

         VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB3,'N',SYSDATE,SYSDATE);

     END IF;

     if :old.job4 is not null then

         INSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)

         VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB4,'N',SYSDATE,SYSDATE);

     END IF;

     if :old.job5 is not null then

         INSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)

         VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB5,'N',SYSDATE,SYSDATE);

     END IF;

     UPDATE ZDL_BKC_JOB_HEAD SET UPDATE_DATE = SYSDATE

         WHERE ZDL_BKC_JOB_HEAD_ID = :OLD.ZDL_BKC_JOB_BODY_ID;

END;

 

-- Trigger head after delete

CREATE OR REPLACE TRIGGER "APPS".ZDL_BKC_JOB_HEAD_BRD

BEFORE DELETE ON "APPS"."ZDL_BKC_JOB_HEAD"

REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

BEGIN

     INSERT INTO ZDL_BKC_JOB_UPDATE

         (BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)

     VALUES

         (:OLD.ZDL_BKC_JOB_HEAD_ID,1,7,:OLD.JOB_NO,'N',SYSDATE,SYSDATE);

END;

 

-- Trigger body after insert

CREATE OR REPLACE TRIGGER "APPS"."ZDL_BKC_JOB_BODY_AFI"

AFTER INSERT ON "APPS"."ZDL_BKC_JOB_BODY"

REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

BEGIN

     UPDATE ZDL_BKC_JOB_HEAD SET UPDATE_DATE = SYSDATE

         WHERE ZDL_BKC_JOB_HEAD_ID = :NEW.ZDL_BKC_JOB_BODY_ID;

END;

2.      建立两个主要之package:

A.   ZDL_BKC_APP_PKG

Package Specific

 

CREATE OR REPLACE PACKAGE ZDL_BKC_APP_PKG

AS

/*BOM之展开*/

PROCEDURE ZDL_BOM_EXPLOSION(

         P_ITEM_ID IN NUMBER,

         p_Organization_id IN NUMBER,

         P_BOM_GROUP_ID IN NUMBER,

         P_EXPL_QTY IN NUMBER,

         P_ERROR_CODE OUT NUMBER);

 

/*展开之半制品放于ZDL_JOB_BKC_HEAD AND ZDL_JOB_BKC_BODY中,并调用ZDL_JOB_PKG中的

相閞过程实现JOB之产生及LOAD入Oracle MFG中*/

PROCEDURE ZDL_PROCESS_BOM(

         P_ITEM_ID in number,

         P_BKC_ID OUT NUMBER,

         P_BOM_GROUP_ID IN NUMBER,

         P_GROUP_ID IN NUMBER,

         P_SCHEDULE_COMMENTS IN VARCHAR2,

         P_SCHEDULE_QUANTITY IN NUMBER,

         P_SCHEDULE_DATE IN DATE,

         P_ORGANIZATION_ID IN NUMBER,

         P_USER_ID IN NUMBER,

         P_ERROR_CODE OUT NUMBER,

         P_JOB_TYPE IN VARCHAR2);

END ZDL_BKC_APP_PKG;

 

 

Package Body

 

CREATE OR REPLACE PACKAGE BODY ZDL_BKC_APP_PKG

AS

PROCEDURE ZDL_BOM_EXPLOSION(

         P_ITEM_ID IN NUMBER,

         p_Organization_id IN NUMBER,

         P_BOM_GROUP_ID IN NUMBER,

         P_EXPL_QTY IN NUMBER,

         P_ERROR_CODE OUT NUMBER)

AS

  l_seq_id                NUMBER;

  l_bom_or_eng            NUMBER:=1;

  l_err_msg               VARCHAR2(80);

  l_err_code              NUMBER;

  exploder_error          EXCEPTION;

  loop_error              EXCEPTION;

  table_name        VARCHAR2(20);

  item_id_null           EXCEPTION;

  p_revision_date   varchar2(15);

  P_EXPLODE_OPTION_TYPE   varchar2(100);

BEGIN

  P_ERROR_CODE := 0;

  SELECT BOM_LISTS_S.NEXTVAL

  INTO  l_seq_id

  FROM  DUAL;

 

  TABLE_NAME := 'BUILD SQL';

  INSERT INTO BOM_LISTS (SEQUENCE_ID, ASSEMBLY_ITEM_ID,

                                ALTERNATE_DESIGNATOR)

        SELECT DISTINCT l_seq_id,P_ITEM_ID,

                bbom.alternate_bom_designator

         FROM   bom_bill_of_materials bbom

         WHERE  bbom.organization_id = 102

         AND    bbom.assembly_item_id = P_ITEM_ID

         AND    (bbom.alternate_bom_designator IS NULL)

         AND    (bbom.assembly_type = 1);

    commit;

    TABLE_NAME := 'EXECUTE SQL';

/* Call BOM exploder */

   TABLE_NAME := 'CALL EXPLODER';

   -- bug 519321

   P_REVISION_DATE := to_char(sysdate,'DD-MON-YY HH24:MI');

   bompexpl.explosion_report

        (

        org_id => p_Organization_id, 

        order_by => 2,  

        list_id => l_seq_id,   

        grp_id => P_BOM_GROUP_ID,       

        session_id => -1,

        levels_to_explode => 15,

        bom_or_eng => 1,       

        impl_flag => 1,       

        explode_option => 2,

        module => 2,

        cst_type_id => -1,

        std_comp_flag => -1,

        expl_qty => P_EXPL_QTY,           

        report_option => -1,

        req_id => 0,

        lock_flag => -1,

        rollup_option => -1,

        alt_rtg_desg => '',

        alt_desg => '',

        rev_date => P_REVISION_DATE,

        err_msg => l_err_msg,

        error_code => l_err_code,

        verify_flag =>0,

        cst_rlp_id => 0,

        plan_factor_flag => 2,

        incl_lt_flag => 2

        );

  commit;

  TABLE_NAME := 'EXPLODE COMPLETE';

  if l_err_code = 9999 then

    raise loop_error;

  end if;

  if l_err_code <0  then

    raise exploder_error;

  end if;

  commit;    --save

  DELETE FROM BOM_LISTS WHERE SEQUENCE_ID = L_SEQ_ID;

  COMMIT;

EXCEPTION

    WHEN exploder_error THEN

    P_ERROR_CODE := 1;

        dbms_output.put_line(l_err_msg);  

    WHEN loop_error THEN

    P_ERROR_CODE := 2;

        dbms_output.put_line('aaa');

    WHEN item_id_null THEN

    P_ERROR_CODE := 3;

        dbms_output.put_line('Item is is null');

    WHEN NO_DATA_FOUND THEN

    P_ERROR_CODE := 4;

        dbms_output.put_line(TABLE_NAME ||SQLERRM);

    WHEN OTHERS THEN

    P_ERROR_CODE := 5;

        dbms_output.put_line(TABLE_NAME || SQLERRM);

END ZDL_BOM_EXPLOSION;

 

/* Process data of bom_explosion_temp */

PROCEDURE ZDL_PROCESS_BOM(

         P_ITEM_ID in number,

         P_BKC_ID OUT NUMBER,

         P_BOM_GROUP_ID IN NUMBER,

         P_GROUP_ID IN NUMBER,

         P_SCHEDULE_COMMENTS IN VARCHAR2,

         P_SCHEDULE_QUANTITY IN NUMBER,

         P_SCHEDULE_DATE IN DATE,

         P_ORGANIZATION_ID IN NUMBER,

         P_USER_ID IN NUMBER,

         P_ERROR_CODE OUT NUMBER,

         P_JOB_TYPE IN VARCHAR2)

AS

CURSOR C1 IS

    SELECT

         BET.ASSEMBLY_ITEM_ID,

         MSI.SEGMENT1,

         BET.COMPONENT_ITEM_ID,

         BET.PLAN_LEVEL

    FROM     BOM.BOM_EXPLOSION_TEMP BET,

         INV.MTL_SYSTEM_ITEMS MSI

    WHERE

         BET.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID AND

         BET.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND

         MSI.ORGANIZATION_ID = P_ORGANIZATION_ID AND

         BET.GROUP_ID = P_BOM_GROUP_ID AND

         BET.TOP_ITEM_ID = P_ITEM_ID AND

         (MSI.ITEM_TYPE = 'SA' OR MSI.ITEM_TYPE = 'FG')

    ORDER BY BET.PLAN_LEVEL;

P_C1 C1%ROWTYPE;

R_ITEM VARCHAR2(15);

P_JOB_NUMBER NUMBER;

BEGIN

    P_ERROR_CODE := 0;

    OPEN C1;

    LOOP

         FETCH C1 INTO P_C1;

         EXIT WHEN C1%NOTFOUND;

         IF P_C1.PLAN_LEVEL = 0 THEN

             select zdl_bkc_job_s.nextval into P_BKC_ID from sys.dual;

             insert into zdl_bkc_job_head

             (    zdl_bkc_job_head_id,

                  assembly,

                  CREATED_DATE,

                  update_date,

                  QUANTITY,

                  LOT_NO,

                  LINE_CODE,

                  OL_DATE)

             values

             (    P_BKC_ID,

                  P_item_ID,

                  SYSDATE,

                  SYSDATE,

                  P_SCHEDULE_QUANTITY,

            SUBSTR(P_SCHEDULE_COMMENTS,1,INSTR(P_SCHEDULE_COMMENTS,'/')-1),

SUBSTR(P_SCHEDULE_COMMENTS,INSTR(P_SCHEDULE_COMMENTS,'/')+1,

LENGTH(P_SCHEDULE_COMMENTS)),

                  P_SCHEDULE_DATE);

             COMMIT;

             ZDL_JOB_PKG.ZDL_INSERT_JOB(P_BKC_ID,P_ITEM_ID,P_JOB_NUMBER,P_GROUP_ID,

0,1,0,P_USER_ID,P_JOB_TYPE);

             UPDATE ZDL_BKC_JOB_HEAD SET JOB_NO = P_JOB_NUMBER WHERE ZDL_BKC_JOB_HEAD_ID = P_BKC_ID;

         ELSIF P_C1.PLAN_LEVEL = 1 THEN

             ZDL_JOB_PKG.zdl_insert_job(P_BKC_ID,P_C1.COMPONENT_ITEM_ID,p_job_number,p_group_id,

-2,1,0,P_USER_ID,P_JOB_TYPE);

             insert into zdl_bkc_job_body

                  (zdl_bkc_job_body_id,level1,job1)

             values(P_BKC_ID,P_C1.SEGMENT1,p_job_number);

             COMMIT;

         ELSIF P_C1.PLAN_LEVEL = 2 THEN

             SELECT SEGMENT1 INTO R_ITEM

                  FROM MTL_SYSTEM_ITEMS

                  WHERE ORGANIZATION_ID = P_ORGANIZATION_ID

                       AND INVENTORY_ITEM_ID = P_C1.ASSEMBLY_ITEM_ID;

             ZDL_JOB_PKG.ZDL_INSERT_JOB(P_BKC_ID,P_C1.COMPONENT_ITEM_ID,P_JOB_NUMBER,P_GROUP_ID,

-3,1,0,P_USER_ID,P_JOB_TYPE);

             UPDATE ZDL_BKC_JOB_BODY SET

                  LEVEL2 = P_C1.SEGMENT1,

                  JOB2 = P_JOB_NUMBER

                  WHERE LEVEL1 = R_ITEM AND ZDL_BKC_JOB_BODY_ID = P_BKC_ID;

             COMMIT;

         ELSIF P_C1.PLAN_LEVEL = 3 THEN

             SELECT SEGMENT1 INTO R_ITEM

                  FROM MTL_SYSTEM_ITEMS

                  WHERE ORGANIZATION_ID = P_ORGANIZATION_ID

                       AND INVENTORY_ITEM_ID = P_C1.ASSEMBLY_ITEM_ID;

             ZDL_JOB_PKG.ZDL_INSERT_JOB(P_BKC_ID,P_C1.COMPONENT_ITEM_ID,P_JOB_NUMBER,P_GROUP_ID,

-4,1,0,P_USER_ID,P_JOB_TYPE);

             UPDATE ZDL_BKC_JOB_BODY SET

                  LEVEL3 = P_C1.SEGMENT1,

                  JOB3 = P_JOB_NUMBER

                  WHERE LEVEL2 = R_ITEM AND ZDL_BKC_JOB_BODY_ID = P_BKC_ID;

             COMMIT;

         ELSIF P_C1.PLAN_LEVEL = 4 THEN

             SELECT SEGMENT1 INTO R_ITEM

                  FROM MTL_SYSTEM_ITEMS

                  WHERE ORGANIZATION_ID = P_ORGANIZATION_ID

                       AND INVENTORY_ITEM_ID = P_C1.ASSEMBLY_ITEM_ID;

             ZDL_JOB_PKG.ZDL_INSERT_JOB(P_BKC_ID,P_C1.COMPONENT_ITEM_ID,P_JOB_NUMBER,P_GROUP_ID,

-6,1,0,P_USER_ID,P_JOB_TYPE);

             UPDATE ZDL_BKC_JOB_BODY SET

                  LEVEL4 = P_C1.SEGMENT1,

                  JOB3 = P_JOB_NUMBER

                  WHERE LEVEL3 = R_ITEM AND ZDL_BKC_JOB_BODY_ID = P_BKC_ID;

             COMMIT;

         ELSIF P_C1.PLAN_LEVEL = 5 THEN

             SELECT SEGMENT1 INTO R_ITEM

                  FROM MTL_SYSTEM_ITEMS

                  WHERE ORGANIZATION_ID = P_ORGANIZATION_ID

                       AND INVENTORY_ITEM_ID = P_C1.ASSEMBLY_ITEM_ID;

             ZDL_JOB_PKG.ZDL_INSERT_JOB(P_BKC_ID,P_C1.COMPONENT_ITEM_ID,P_JOB_NUMBER,P_GROUP_ID,

-6,1,0,P_USER_ID,P_JOB_TYPE);

             UPDATE ZDL_BKC_JOB_BODY SET

                  LEVEL5 = P_C1.SEGMENT1,

                  JOB3 = P_JOB_NUMBER

                  WHERE LEVEL4 = R_ITEM AND ZDL_BKC_JOB_BODY_ID = P_BKC_ID;

             COMMIT;

         END IF;

    END LOOP;

    CLOSE C1;

    DELETE FROM BOM_EXPLOSION_TEMP WHERE GROUP_ID = P_BOM_GROUP_ID;

    COMMIT;

EXCEPTION

    WHEN OTHERS THEN

         P_ERROR_CODE := 6;

END ZDL_PROCESS_BOM;

 

END ZDL_BKC_APP_PKG;

 

 

B.   ZDL_JOB_PKG

 

Package Specific

 

CREATE OR REPLACE PACKAGE ZDL_JOB_PKG

AS

 

/*给每个展开之半制品分配一个JOB号,并将其存储于WIP_JOB_SCHEDULE_INTERFACE中*/

PROCEDURE ZDL_INSERT_JOB(

    p_bkc_id in number,

    p_item_id in number,

    p_job_number in out varchar2,

....................................................................

    p_lead_day in number,

    p_load_type in number,

    P_STATUS_TYPE IN NUMBER,

    P_USER_ID IN NUMBER,

    P_JOB_TYPE IN VARCHAR2);

 

/*维护O/L日期等时自动更新Oracle MFG之WIP*/

PROCEDURE ZDL_UPDATE_JOB(

         P_BKC_ID IN NUMBER,

         P_GROUP_ID IN NUMBER,

         P_STATUS_TYPE IN NUMBER,

         P_USER_ID IN NUMBER);

 

/*触发WIP JOB MASS LOAD程序*/

FUNCTION WIP_MASS_LOAD(P_GROUP_ID IN NUMBER,P_USER_ID IN NUMBER) RETURN NUMBER;

 

/*在使用ZDL_BKC_JOB_UPDATE檔更新Oracle MFG时,将其中之相关数据织一下*/

PROCEDURE ZDL_PRE_UPDATE;

 

/*调用前面之相关程序,并根据ZDL_BKC_JOB_UPDATE文件更新Oracle MFG*/

FUNCTION ZDL_UPDATE_ORACLE_WIP(P_USER_ID IN NUMBER) RETURN NUMBER;

 

/*检查Oracle JOB之状态,并返回*/

FUNCTION ZDL_JOB_STATUS (P_JOB_NUMBER IN VARCHAR2) RETURN NUMBER;

END ZDL_JOB_PKG;

 

Package Body

 

CREATE OR REPLACE PACKAGE BODY ZDL_JOB_PKG

AS

PROCEDURE ZDL_INSERT_JOB (

    p_bkc_id in number,

    p_item_id in number,

    p_job_number in out varchar2,

....................................................................

    p_lead_day in number,

    P_LOAD_TYPE IN NUMBER,

    P_STATUS_TYPE IN NUMBER,

    P_USER_ID IN NUMBER,

    P_JOB_TYPE IN VARCHAR2)

is

p_completion_date date;

r_schedule_date date;

p_start_quantity number;

l_seq_num number;

l_next_seq_num number;

p_lot_number varchar2(240);

P_LINE_CODE VARCHAR2(240);

p_wip_entity_id number;

begin

/* P_BKC_ID = 0 MEAN THAT THE BKC ID HAS BEEN DELETE FROM TABLE */

IF NOT (P_BKC_ID = 0 AND P_STATUS_TYPE = 7) THEN

    select OL_DATE,quantity,lot_no,line_code into p_completion_date,p_start_quantity,p_lot_number,P_LINE_CODE

         from zdl_bkc_job_head where ZDL_BKC_JOB_HEAD_ID = p_bkc_id;

END IF;

/* P_STSTUS_TYPE = 7 MEAD THAT THIS JOB MUST BE CANCELLED */

IF P_STATUS_TYPE <> 7 THEN

    select seq_num,next_seq_num into l_seq_num,l_next_seq_num

         from bom_calendar_dates

         where trunc(calendar_date) = trunc(p_completion_date);

    if l_seq_num is null then

         l_seq_num:=l_next_seq_num-1;

    end if;

    l_seq_num:=l_seq_num+p_lead_day;

    select calendar_date into r_schedule_date from bom_calendar_dates where seq_num=l_seq_num;

END IF;

/* P_LOAD_TYPE = 1 : ADD A JOB INTO ORACLE WIP

   P_LOAD_TYPE = 3 : UPDATE A JOB OF ORACLE WIP*/

if p_load_type = 1 then

    select wip_job_number_s.nextval into p_job_number from sys.dual;

elsif p_load_type = 3 then

    select wip_entity_id into p_wip_entity_id from wip_entities where wip_entity_name = p_job_number;

end if;

insert into wip_job_schedule_interface

(

    last_update_date,

    creation_date,

    created_by,

    last_updated_by,

    group_id,

    process_phase,

    process_status,

    load_type,

    job_name,

    wip_entity_id,

    LAST_UNIT_COMPLETION_DATE,

    organization_id,

    primary_item_id,

    description,

    start_quantity,

    STATUS_TYPE,

    ATTRIBUTE_CATEGORY,

    ATTRIBUTE1,

    ATTRIBUTE2,

    ATTRIBUTE3)

values

(   sysdate,

    sysdate,

    P_USER_ID,

    P_USER_ID,

    p_group_id,

    2,

    1,

    P_LOAD_TYPE,

    p_job_number,

    decode(p_load_type,1,null,p_wip_entity_id),

    DECODE(P_STATUS_TYPE,0,to_date(to_char(r_schedule_date,'DD-MON-YYYY HH24:MI:SS'),

'DD-MON-YYYY HH24:MI:SS'),NULL),

    102,

    decode(p_load_type,1,p_item_id,null),

    decode(p_load_type,1,'Created By ZDL BKC Program',DECODE(P_STATUS_TYPE,0,

'Updated by ZDL BKC Program','Cancelled by ZDL BKC Program')),

    DECODE(P_STATUS_TYPE,0,p_start_quantity,NULL),

    DECODE(P_STATUS_TYPE,0,NULL,P_STATUS_TYPE),

    'JOB',

    DECODE(P_LOAD_TYPE,1,P_JOB_TYPE,NULL),

    p_lot_number,

    P_LINE_CODE

);

commit;

end ZDL_INSERT_JOB;

 

PROCEDURE ZDL_UPDATE_JOB(P_BKC_ID IN NUMBER,P_GROUP_ID IN NUMBER,

P_STATUS_TYPE IN NUMBER,P_USER_ID IN NUMBER)

AS

P_JOB_NO VARCHAR2(240);

P_LOT_NO VARCHAR2(240);

P_QUANTITY NUMBER;

P_LINE_CODE VARCHAR2(240);

P_OL_DATE DATE;

p_job1 zdl_bkc_job_body.job1%type;  

p_job2 zdl_bkc_job_body.job2%type;

p_job3 zdl_bkc_job_body.job3%type;

p_job4 zdl_bkc_job_body.job4%type;

p_job5 zdl_bkc_job_body.job5%type;

cursor l_bkc is

    select job1,job2,job3,job4,job5 from zdl_bkc_job_body where zdl_bkc_job_body_id=p_bkc_id;

BEGIN

    SELECT   JOB_NO,LOT_NO,QUANTITY,LINE_CODE,OL_DATE

INTO P_JOB_NO,P_LOT_NO,P_QUANTITY,P_LINE_CODE,P_OL_DATE

    FROM     ZDL_BKC_JOB_HEAD

    WHERE    ZDL_BKC_JOB_HEAD_ID = P_BKC_ID;

    zdl_job_pkg.ZDL_INSERT_JOB(P_BKC_ID,0,p_job_no,p_group_id,0,3,P_STATUS_TYPE,P_USER_ID,NULL);

    open l_bkc;

    loop

    fetch l_bkc into p_job1,p_job2,p_job3,p_job4,p_job5;

         exit when l_bkc%notfound;

         if P_job1 is not null then

             zdl_job_pkg.ZDL_INSERT_JOB(P_BKC_ID,0,P_JOB1,P_GROUP_ID,-2,3,P_STATUS_TYPE,P_USER_ID,NULL);

         end if;

         IF P_JOB2 IS NOT NULL THEN

             zdl_job_pkg.ZDL_INSERT_JOB(P_BKC_ID,0,P_JOB2,P_GROUP_ID,-3,3,P_STATUS_TYPE,P_USER_ID,NULL);

         END IF;

         IF P_JOB3 IS NOT NULL THEN

             zdl_job_pkg.ZDL_INSERT_JOB(P_BKC_ID,0,P_JOB3,P_GROUP_ID,-4,3,P_STATUS_TYPE,P_USER_ID,NULL);

         END IF;

         IF P_JOB4 IS NOT NULL THEN

             zdl_job_pkg.ZDL_INSERT_JOB(P_BKC_ID,0,P_JOB4,P_GROUP_ID,-6,3,P_STATUS_TYPE,P_USER_ID,NULL);

         END IF;

         IF P_JOB5 IS NOT NULL THEN

             zdl_job_pkg.ZDL_INSERT_JOB(P_BKC_ID,0,P_JOB5,P_GROUP_ID,-8,3,P_STATUS_TYPE,P_USER_ID,NULL);

         END IF; 

    end loop;

END ZDL_UPDATE_JOB;

 

FUNCTION WIP_MASS_LOAD(P_GROUP_ID IN NUMBER,P_USER_ID IN NUMBER) RETURN NUMBER

as

req_id number;

LOGINID NUMBER;

begin

SELECT FND_CONCURRENT_REQUESTS_S.NEXTVAL INTO REQ_ID FROM DUAL;

SELECT FND_LOGINS_S.NEXTVAL INTO LOGINID FROM DUAL;

insert into FND_CONCURRENT_REQUESTS (

    REQUEST_ID,

    LAST_UPDATE_DATE,

    LAST_UPDATED_BY,

    LAST_UPDATE_LOGIN,

    REQUEST_DATE,

    REQUESTED_BY,

    PHASE_CODE,

    STATUS_CODE,

    PRIORITY_REQUEST_ID,

    PRIORITY,

    REQUESTED_START_DATE,

    HOLD_FLAG,

    ENFORCE_SERIALITY_FLAG,

    SINGLE_THREAD_FLAG,

    HAS_SUB_REQUEST,

    IS_SUB_REQUEST,

    IMPLICIT_CODE,

    UPDATE_PROTECTED,

    QUEUE_METHOD_CODE,

    ARGUMENT_INPUT_METHOD_CODE,

    ORACLE_ID,

    PROGRAM_APPLICATION_ID,

    CONCURRENT_PROGRAM_ID,

    RESPONSIBILITY_APPLICATION_ID,

    RESPONSIBILITY_ID,

    NUMBER_OF_ARGUMENTS,

    NUMBER_OF_COPIES,

    SAVE_OUTPUT_FLAG,

    NLS_LANGUAGE,

    NLS_TERRITORY,

    PRINTER,

    PRINT_STYLE,

    PRINT_GROUP,

    REQUEST_CLASS_APPLICATION_ID,

    CONCURRENT_REQUEST_CLASS_ID,

    PARENT_REQUEST_ID,

    CONC_LOGIN_ID,

    LANGUAGE_ID,

    DESCRIPTION,

    REQ_INFORMATION,

    RESUBMIT_INTERVAL,

    RESUBMIT_INTERVAL_UNIT_CODE,

    RESUBMIT_INTERVAL_TYPE_CODE,

    RESUBMIT_TIME,

    RESUBMIT_END_DATE,

    RESUBMITTED,

    CONTROLLING_MANAGER,

    ACTUAL_START_DATE,

    ACTUAL_COMPLETION_DATE,

    COMPLETION_TEXT,

    OUTCOME_PRODUCT,

    OUTCOME_CODE,

    CPU_SECONDS,

    LOGICAL_IOS,

    PHYSICAL_IOS,

    LOGFILE_NAME,

    LOGFILE_NODE_NAME,

    OUTFILE_NAME,

    OUTFILE_NODE_NAME,

    ARGUMENT_TEXT,

    ARGUMENT1,

    ARGUMENT2,

    ARGUMENT3,

    ARGUMENT4,

    ARGUMENT5,

    ARGUMENT6,

    ARGUMENT7,

    ARGUMENT8,

    ARGUMENT9,

    ARGUMENT10,

    ARGUMENT11,

    ARGUMENT12,

    ARGUMENT13,

    ARGUMENT14,

    ARGUMENT15,

    ARGUMENT16,

    ARGUMENT17,

    ARGUMENT18,

    ARGUMENT19,

    ARGUMENT20,

    ARGUMENT21,

    ARGUMENT22,

    ARGUMENT23,

    ARGUMENT24,

    ARGUMENT25,

    CRM_THRSHLD,

    CRM_TSTMP

    )

VALUES

    (

    REQ_ID,

    SYSDATE,

    P_USER_ID,

    LOGINID,

    SYSDATE,

    P_USER_ID,

    'P',

    'I',

    REQ_ID,

    50,

    SYSDATE,

    'N',

    'Y',

    'N',

    'N',

    'N',

    'N',

    'N',

    'I',

    'S',

    900,

    706,

    34291,

    706,

    20560,

    3,

    0,

    'Y',

    'AMERICAN',

    'AMERICA',

    NULL,

    'LANDSCAPE',

    'N',

    NULL,

    NULL,

    -1,

    LOGINID,

    0,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    'N',

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    RTRIM(TO_CHAR(P_GROUP_ID))||', 0, 1',

    P_GROUP_ID,

    0,

    1,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    0,

    NULL

);

COMMIT;

return(req_id);

END WIP_MASS_LOAD;

 

PROCEDURE ZDL_PRE_UPDATE IS

    cursor c1 is

         select rowid,bkc_id,location_id,action_id,job_number

from zdl_bkc_job_update where updated_flag = 'N';

    p_c1_rec c1%rowtype;

    p_count number default 0;

begin

    delete

         from zdl_bkc_job_update z1

         where rowid !=

             (select

                  max(rowid)

             from

                  zdl_bkc_job_update z2

             where

                  z1.bkc_id = z2.bkc_id and

                  z1.location_id = z2.location_id and

                  z1.action_id = z2.action_id and

                  z1.job_number = z2.job_number AND

                  Z1.UPDATED_FLAG = z2.updated_flag)

         and z1.updated_flag = 'N';

    commit;

    open c1;

    loop

         fetch c1 into p_c1_rec;

         exit when c1%notfound;

         if p_c1_rec.action_id = 3 then

             select count(*) into p_count

                  from zdl_bkc_job_update

                  where bkc_id = p_c1_rec.bkc_id and

                       location_id = p_c1_rec.location_id and

                       action_id = 7 and

                       job_number = p_c1_rec.job_number AND UPDATED_FLAG = 'N';

             if p_count > 0 then

                  delete from zdl_bkc_job_update

                       where rowid = p_c1_rec.rowid;

                  commit;

             end if;      

         end if; 

    end loop;

end ZDL_PRE_UPDATE;

 

FUNCTION ZDL_UPDATE_ORACLE_WIP(P_USER_ID IN NUMBER) RETURN NUMBER

IS

P_GROUP_ID NUMBER;

P_REQ_ID NUMBER;

p_bkc_id number;

p_action_id number;

p_location_id number;

p_job_number varchar2(240);

p_count boolean default false;

p_count_item number;

cursor c1 is

    select   bkc_id,action_id,location_id,job_number

    from     zdl_bkc_job_update

    where    action_id = 3 and location_id = 1 AND UPDATED_FLAG = 'N';

cursor c2 is

    select   bkc_id,action_id,location_id,job_number

    from     zdl_bkc_job_update

    where    action_id = 7 AND UPDATED_FLAG = 'N';

begin

    ZDL_JOB_PKG.ZDL_PRE_UPDATE;

    select wip_job_schedule_interface_s.nextval into p_group_id from sys.dual;

    open c1;

    loop

         fetch c1 into p_bkc_id,p_action_id,p_location_id,p_job_number;

         exit when c1%notfound;

         zdl_job_pkg.zdl_update_job(p_bkc_id,p_group_id,0,P_USER_ID);

    end loop;

    if c1%rowcount > 0 then

         UPDATE zdl_bkc_job_update SET UPDATED_FLAG = 'Y',UPDATED_DATE = SYSDATE

             where action_id = 3 and location_id = 1 AND UPDATED_FLAG = 'N';

         p_count := true;

    end if;

    close c1;

    commit;

    open c2;

    loop

         fetch c2 into p_bkc_id,p_action_id,p_location_id,p_job_number;

         exit when c2%notfound;

         zdl_job_pkg.zdl_insert_job(0,0,p_job_number,p_group_id,0,3,7,P_USER_ID,NULL);

    end loop;

    if c2%rowcount > 0 then

         p_count := true;

         UPDATE zdl_bkc_job_update SET UPDATED_FLAG = 'Y',UPDATED_DATE = SYSDATE

             where action_id = 7 AND UPDATED_FLAG = 'N';

    end if;

    close c2;

    commit;

 

    if p_count then

         P_REQ_ID := ZDL_JOB_PKG.WIP_MASS_LOAD(P_GROUP_ID,P_USER_ID);

    else

         p_req_id := 0;

    end if;

    RETURN(P_REQ_ID);

end ZDL_UPDATE_ORACLE_WIP;

 

FUNCTION ZDL_JOB_STATUS (P_JOB_NUMBER IN VARCHAR2) RETURN NUMBER

AS

P_STATUS_TYPE NUMBER;

BEGIN

SELECT

    STATUS_TYPE INTO P_STATUS_TYPE

FROM

    WIP_DISCRETE_JOBS WDJ,

    WIP_ENTITIES WE

WHERE    WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID AND

    WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID AND

    WE.WIP_ENTITY_NAME = P_JOB_NUMBER;

RETURN (P_STATUS_TYPE);

END ZDL_JOB_STATUS;

 

END ZDL_JOB_PKG;

 

 

 

 

自我补充:

日期函数:

1、求某天是星期几  
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;  
星期一  
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;  
monday  
设置日期语言  
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';  
也可以这样  
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')  

2、两个日期间的天数  
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual; 

 

3、时间为null的用法  
select id, active_date from table1  
UNION  
select 1, TO_DATE(null) from dual;  
注意要用TO_DATE(null)  

 

4、处理月份天数不定的办法  
select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual  

 

5、 找出今年的天数  
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual  

6、闰年的处理方法  
to_char( last_day( to_date('02' || :year,'mmyyyy') ), 'dd' )  
如果是28就不是闰年  

 

7、5秒钟一个间隔  
Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')  
from dual  

2002-11-1 9:55:00 35786  
SSSSS表示5位秒数  

 

8、一年的第几天  
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual  
310 2002-11-6 10:03:51  

 

9、floor((date2-date1) /365) 作为年  
floor((date2-date1, 365) /30) 作为月  
mod(mod(date2-date1, 365), 30)作为日.  
23.next_day函数  
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。  
1 2 3 4 5 6 7  
日 一 二 三 四 五 六

 

10、          extract()找出日期或间隔值的字段值
date_value:=extract(date_field from [datetime_value|interval_value])
SQL> select extract(month from sysdate) "This Month" from dual;

This Month
----------
         11

SQL> select extract(year from add_months(sysdate,36)) "3 Years Out" from dual;

3 Years Out
-----------
        2006

 

11、          localtimestamp()返回会话中的日期和时间
timestamp_value:=localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual;

LOCALTIMESTAMP
----------------------------
13-11月-03 12.09.15.433000
下午

 

12、          decode()函数

 

先构造一个例子,假设我们想给智星职员加工资,其标准是:工资在8000元以下的将加20%;工资在8000元以上的加15%,通常的做法是,先选出记录中的工资字段值? select salary into var-salary from employee,然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。 如果用DECODE函数,那么我们就可以把这些流控制语句省略,通过SQL语句就可以直接完成。如下:select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee 是不是很简洁?

使用方法:

  1、比较大小

  select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

  sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1

 

转载来自 http://blog.csdn.net/happy__xiaoyu/article/details/6955472

展开阅读全文

没有更多推荐了,返回首页