SQL PL/SQL syntax manuals

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

SQL PL/SQL syntax manuals
























Directory


The first part SQL the grammar part 3

A TABLE statement, CREATE 3

Two, the CREATE SEQUENCE statement 5

Three, the CREATE VIEW statement 6

Four, the INSERT statement: 7

Five, the UPDATE statement: 9

Six, the DELETE statement: 10

Seven, the TRUNCATE statement: 11

Eight, all kinds of FUNCTIONS: 12

1 transfer functions: 12

2 date functions 16

3 character function 20

4 numerical functions 28

5 separate functions: 33

More than 6 lines of the function 35

The second part PL/SQL the grammar part 41

A brief introduction to PL/SQL, 41

Two, the 48 variables

Three, the PL/SQL control program flow 52

Four, the stored procedure 54

Five, storage functions 54

Six, PACKAGE 54

Seven, trigger 56

Eight, 58 examples







The first part of the SQL grammar



A table statement, Create


Statement: 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]

Table is the most important database objects in Oracle, list some similar data set, the data presented as simple forms the basic form of several columns or fields.Create TABLE statement used to create a row in the database table.Create in the table statement receiving table name, column name, data type and size. In addition to the column name and description outside, can also specify constraints, storage parameters and the table will be part of a cluster.

Schema is used to specify the owner table, if not specified for the current logged on user.

Table_name is used to specify the created table, for a maximum of 30 characters, but can't figure at the beginning (to underscore), reserved words conflict but not with other objects or Oracle.

Column is used to specify the columns in the table, up to 254.

Datatype is used to specify what type of data is stored in the column, and ensure that only valid data can be input.

Column_constraint is used to specify the column constraints, such as a column is not null, can be specified as not null.

Table_constraint is used to specify constraints, such as the table's primary key, foreign key.

Pctfree is used to specify the data in the table grows in a Oracle block reserved space. DEFAULT is 10%, that is to say each block can only use of the table 90%, use 10% to increase the data row.

Pctused is used to specify a horizontal line, when the block used in the space below the horizontal line can add new rows of data to the.

Parallel is used to specify the table to accelerate the full table scan can be used to process a number of parallel queries.

Cache is used to specify the form is the most should be cached in the SGA database buffer pool of candidates.

Cluster is used to specify the table stored cluster.

Tablespace is used to specify that a partitioned database to store the data in the table.

Recoverable|Unrecoverable is used to determine whether the data in this table are written to the Redo file changes. In order to restore operations on the data.

When As does not specify the columns of the table, to generate the database structure and data by using the As clause of the query results.

An example:

   1) create table mytab1e(mydec decimal,

                       myint inteter)

                 tablespace user_data

                 pctfree   5

                 pctused   30;

   2) create table mytable2

             as ( select * from mytable1);

Two,
The create sequence statement


Statement: CREATE SEQUENCE [schema.]sequence_name

[INCREMENT BY integer]

    [START WITH integer]

    [MAXVALUE integer | NOMAXVALUE]

    [MINVALUE integer | NOMINVALUE]

    [CYCLE | NOCYCLE]

    [CACHE integer | NOCACHE]

    [ORDER | NOORDER]

Sequence used to generate unique sequence of values for the primary key of the table.

Increment by specifies the sequence value of each growth value

The first value Start with specify the sequence

The maximum value of Maxvalue specifies the sequence

The minimum specified sequence generated by Minvalue

Cycle specifies when the sequence value of Kui to the maximum or the minimum value, the sequence is circular.

Cache specifies the sequence generator a cached values

Order specifies whether the numerical sequence in order to access.

An example:

    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’);






Three,
The create view statement


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

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

    AS subquery

    [WITH CHECK OPTION [CONSTRAINT constraint]]

The view is actually stored in the database of the select statement. Each time you use a view in a SQL statement, select statement of this view is used to get the data.

If the view has been in existence for Or replace when you create a view, with this option, the new view will overwrite the old

           View.

Force if this option, when the view is based on the table does not exist or the view was not created in the mode of access, also may establish the view.

As subquery view select query

With check option if the view is based on a single table and the table of non empty all columns are included in the view, the view can be used for insert and update statements, the                Option to ensure that each insert or update data, the data can be seen                  Figure to check

An example:

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;

Four,
The INSERT statement:

1 Grammar

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

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

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

    [WHERE condition]


2 insert row

Use the VALUES keyword for the new row of each column is assigned a value. If you don't know the value of a column, you can use the NULL keyword to the value is set to a null value (two consecutive commas can also indicate a null value, or use the NULL keyword)

To insert a row when trying to provide a NULL value of the NOT NULL column, will return an error message.


Give an example:

Insert a record into the DEPARTMENT table



INSERT INTO DEPARTMENT

       (DEPARTMENT_ID,NAME,LOCATION_ID)

VALUES (01,’COMPUTER’,167)


3 insert rows

All rows will retrieve the SELECT statement into a table. This statement is usually from one table to another table to quickly copy the data rows.


Give an example:

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 month -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 from the other table to copy the data:

To copy data from one table to another is not existing table quickly, you can use the CREATE TABLE statement to define the form and also copy the SELECT statement search results to the new table.

CREATE TABLE EMPLOYEE_COPY

      AS

      SELECT *

        FROM EMPLOYEE

Five,
The UPDATE statement:

1 Grammar

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

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

        |  column = { expr | (subquery) } }

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

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

    [WHERE condition]


The UPDATE statement updates to meet all the conditions of a WHERE clause that row. Similarly, the statement can be obtained by using the SELECT statement to retrieve. But SELECT must be retrieved only one line of data values. Otherwise errors. And each update a row of data, to execute a SELECT statement.


Give an example:

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)

      )

Six,
The DELETE statement:

1 Grammar

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

    [WHERE condition]


The DELETE statement deletes all meet the conditions of a WHERE clause that row.


Give an example:

DELETE FROM item

WHERE ORDER_ID=510

Seven,
The TRUNCATE statement:

1 Grammar

TRUNCATE [schema.]table



Eight,
All kinds of Functions:


The 1 conversion function:


1.1. function: TO_CHAR

Grammar:

    TO_CHAR(number[,format])


Use:

A numerical value is converted to a string and the equivalent. If you do not specify the format will be converted to a string, the simplest form. If negative is preceded by a minus sign.


Oracle provides a format for the numerical, here is a partial list of acceptable format:

Elements

Description

Example

Value

Result

9

Returns the specified number of numerical, front 0, displayed as spaces

99999

784

-578

1234567

45.895

‘   784’

‘  -578’

‘######’

‘    46’

Insert a decimal point

9999.99

784

45.3482

‘784.00’

’45.35’

Insert a comma at the specified location result string

9,999,999

784

1234567

0.44

‘       784’

‘ 1,234,567’

‘         0’

$

The return value is preceded by a dollar sign

$99,999

784

-578

‘    $784’

‘   -$578’

B

The integer part results if it is 0, it shows the space

B9999.9

784

0.44

‘  784.0’

‘     .4’

S

Value returned a sign

S9999

784

‘+784’

EEEE

By a numerical notation

9.9EEEE

45

0.0666

‘  4.5E+01’

‘  6.7E-02’





1.2.
Function: TO_CHAR


Grammar:

    TO_CHAR(date[,format])


Use:

The format parameter specifies the format will date value into string form. Similarly, Oracle provides many format model, users can use their combined to represent the output format. The final only limit is the final mask cannot exceed 22 characters. Here is a partial list of date format elements.


Oracle provides a format for the numerical, here is a partial list of acceptable format:


Format

Significance

D

Numbers indicate a few weeks(1,2,3,4,5,6,7)

DY

Three characters that week abbreviations(MON)

DAY

A complete representation of the days of the week(MONDAY)

DD

In January, said several digital

DDD

Numbers indicate the days in the year

W

In January the number of weeks

WW

The week of the year number

MM

Numbers indicate the number of months

MON

With three letter abbreviation for months(APR)

MONTH

Complete English month said(FEBRUARY)

Y

Finally, a number of years.(9)

YY

The last two digits in the year(99)

YYY

The last three digits in the year(999)

YYYY

The year is represented by four digits(1999)

YEAR

Year full spelling alphabet(NINETEEN-NINETY-NINE)

AM or PM

Morning or afternoon identifier

HH

In hours date

MI

Minutes hours

SS

Second says minutes

SSSS

The number of seconds since midnight (the number of often between 0-86399)







1.3.
Function: TO_DATE


Grammar:

    TO_DATE(string,format)


Use:

According to the given format to convert a string into a Oracle date value.

The main purpose of the function is used to validate the input date value. In the application, the user must validate input date is valid, such as the number of days month is between 1~12 and date is in the specified number of days month.


1.4.
Function: TO_NUMBER


Grammar:

    TO_NUMBER(string[,format])


Use:

The function to convert a string into the corresponding value. For simple string values (e.g. several numbers with decimal point). The format is optional.



2.
The date function


2.1. function: ADD_MONTHS


Grammar:

    ADD_MONTHS(date,number)


Use:

Add months specified in the date date, returns a new date. If the given is negative, the return value is date months before the date of the date of.Number should be an integer, if the decimal, positive is truncated to the greatest integer less than the number of negative truncated, most small integer greater than this number.


For example:

    SELECT TO_CHAR(ADD_MONTHS(sysdate,1),

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

      FROM dual


Next month

-----------

19-FEB-2000



2.2. function: LAST_DAY


Grammar:

    LAST_DAY(date)


Use:

Returns the date date is the last day of the month to date.


For example:

    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. function: MONTHS_BETWEEN


Grammar:

    MONTHS_BETWEEN(date1,date2)


Use:

Returns a date between two months. If two days date month within the same (or is the last day of a month), the return value is an integer. Otherwise, the return value is a decimal, every 1/31 month months remaining days. If the second date is earlier than the first date, is the return value is negative.


For example:

    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. function: NEXT_DAY


Grammar:

    NEXT_DAY(date,day)


Use:

This function returns date if the specified date after date. Note: the parameter day must be a week, can a few weeks English full spelling, or three letter abbreviation, or digital 1,2,3,4,5,6,7 denote Sunday to Saturday. For example, the query returns the last Friday date.


For example:

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

      FROM dual


NEXT_DAY(

---------

28-JAN-00



2.5. function: ROUND


Grammar:

    NEXT_DAY(date[,format])


Use:

The function to a date four to five homes in closest to the specified format elements form. If format is omitted, the date of return only date. For example, if you want to take the time (24/01/00 14:58:41) four to five homes to the nearest hour. The following table shows the influence of all available to date format elements.


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


For example:

    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. function: TRUNC


Grammar:

    TRUNC(date[,format])


Use:

The function of TRUNC and ROUND are very similar, which according to the specified format mask elements, only returned to the input part of the date the user cares, unlike ROUND, it removes more precise time, rather than the fourth to five homes.


For example:

    SELECT TRUNC(sysdate)

      FROM dual


TRUNC(SYS

---------

24-JAN-00


The FLOOR function: the number of days between two dates used for,

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



3.
Character function

3.1. function: ASCII


Grammar:

ASCII(character)


Use:

Returns the specified character of the ASCII code value. If the string, returns the first characters of the ASCII value.


For example:

   SELECT ASCII('Z')

     FROM dual


ASCII('Z')

----------

        90



3.2. function: CHR


Grammar:

CHR(number)


Use:

This function performs the inverse ASCII operation, returns the ASCII code value equal to number characters. This function is often used to add non printable characters from the string.


For example:

    SELECT CHR(65)||'BCDEF'

      FROM dual


CHR(65

------

ABCDEF



3.3.
Function: CONCAT


Grammar:

CONCAT(string1,string2)


Use:

This function is used to connect the two string, the string2 behind string1 back, it is equivalent to the concatenation operator(||).


For example:

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

      FROM dual


CONCAT('THISISA','

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

This is a computer

It can also be written as:

    SELECT ‘This is a’||’ computer’

      FROM dual


'THISISA'||'COMPUT

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

This is a computer

The two statement of the results are the same, but should use || operator as much as possible.



3.4. function: INITCAP


Grammar:

INITCAP(string)


Use:

This function will each word strings in string first letters to uppercase letters, and other characters to lowercase letters.






For example:

    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. function: INSTR


Grammar:

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


Use:

This function from the string input_string the first n characters begin to search the search string in M, if the string to search for is not found, the function returns 0 if it is found, the function returns the position.


For example:

    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. function: INSTRB


Grammar:

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


Use:

This function is similar to the INSTR function, difference is that the INSTRB function returns the number of bytes the search string, rather than the number of characters. In the NLS character set contains only a single character, INSTRB function and INSTR function are identical.



3.7. function: LENGTH


Grammar:

LENGTH(string)


Use:

This function returns the number of characters for the input string. The definition and not the length field returns the length of the field, but full of character. In the example, the field first_name is defined as VARCHAR2(15).


Grammar:

    SELECT first_name,LENGTH(first_name)

      FROM employee


FIRST_NAME      LENGTH(FIRST_NAME)

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

JOHN                             4

KEVIN                            5


3.8. function: LENGTHB


Grammar:

LENGTHB(string)


Use:

This function returns the number of bytes for the input string contains only single byte characters. For the character set LENGTHB and LENGTH functions are exactly the same.



3.9. function: LOWER


Grammar:

LOWER(string)


Use:

This function will string string are converted to lowercase, for digital and other non alphabetic characters, no conversion is performed.



3.10. function: UPPER


Grammar:

UPPER(string)


Use:

This function will string string converted to uppercase letters, for digital and other non alphabetic characters, no conversion is performed.



3.11. function: LPAD


Grammar:

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


Use:

In the set of a specified character set string string left with, so that the string length length length. specified by the set argument can be either a single character, also can be a string. If the length of the string is less than length, the string string of length characters.


Grammar:

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

      FROM employee


FIRST_NAME      LPAD(FIRST_NAME,20,'')

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

JOHN                            JOHN

KEVIN                          KEVIN

3.12. function: RPAD


Grammar:

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


Use:

In the set of a specified character set string string right and so on, the length of length length. parameters specified in set can be either a single character, also can be a string. If the length of the string is less than length, the string string of length characters.


For example:

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

      FROM employee


FIRST_NAME      RPAD(FIRST_NAME,20,'-')

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

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

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



3.13. function: LTRIM


Grammar:

LTRIM(string[,’set’])


Use:

The function starts from string left, get rid of the characters in the string set, until you see the first not on the characters in the string set so far.


For example:

    SELECT first_name,ltrim(first_name,'BA')

      FROM employee

     WHERE first_name='BARBARA'


FIRST_NAME      LTRIM(FIRST_NAM

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

BARBARA         RBARA


3.14. function: RTRIM


Grammar:

RTRIM(string[,’set’])


Use:

The function starts from the right side of the string, get rid of the characters in the string set, until you see the first not on the characters in the string set. With field NULL value cannot be and field has a blank character comparison.

This is because the blank character and NULL character is two character completely different. Another use of this function is when the field is connected to remove undesirable characters.


3.15. function: SUBSTR


Grammar:

SUBSTR(string,start[,length])


Use:

The function of a substring is removed from the input string, string beginning from the specified length from start character, if you do not specify a length, back end to a string from a start character.


3.16. function: REPLACE


Grammar:

REPLACE(string,search_set[,replace_set])


Use:

This function will be a string of all occurrences of search_set are replaced with replace_set string. You can use this letter will string all symbols are replaced with a valid name. If you do not specify a replace_set, then from the string string to remove all of the search string search_set.


For example:

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

  FROM dual


REPLACE('ABCDEFBDCDABC,

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

ABCdefbdcdABC,dsssdcdrd

3.17. function: TRANSLATE


Grammar:

TRANSLATE(string,search_set,replace_set)


Use:

This function is used for all appear in the search character set in the search_set characters into alternate character set the corresponding character in replace_set. Note: if one of the characters in the string string does not appear in the search character. It will wholly intact to return. If the alternate character set replace_set than the search character set search_set, then the search string the character set search_set back will be removed from the string string.


For example:

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

                 9999999999xxxxxxxxxxxxxx')

  FROM dual


TRANSL

------

xx-99x












4.
Numerical function

4.1. function: ABS


Grammar:

ABS(number)


Use:

This function returns the absolute value of numerical number. Absolute value is a number from the part of the symbol.



4.2. function: SQRT


Grammar:

SQRT(number)


Use:

The square root function returns the number value must be greater than or equal to 0, the input value, otherwise an error is returned.



4.3. function: CEIL


Grammar:

CEIL(number)


Use:

This function returns a value greater than or equal to the input of the next integer.



4.4. function: FLOOR


Grammar:

FLOOR(number)


Use:

This function returns the greatest integer less than or equal to number.

4.5.
Function: MOD


Grammar:

MOD(n,m)


Use:

This function returns the N in addition to M mode, the N in the rest of M.M, n can be a decimal, negative.



4.6. function: POWER


Grammar:

POWER(x,y)


Use:

This function performs the inverse LOG operation, return to x y.



4.7. function: ROUND


Grammar:

ROUND(number,decimal_digits)


Use:

The function value number four to five homes in to a specified number of decimal places. If decimal_digits is 0, it returns the integer.Decimal_digits can be negative.


4.8. function: TRUNC


Grammar:

TRUNC(number[,decimal_pluces])


Use:

The function in small numbers assigned to a numerical cut off. If you do not specify the precision, accuracy of 0 decimal_pluces function presupposition can be negative.

4.9.
Function: SIGN

Grammar:

SIGN(number)


Use:

This function returns the number symbols, if number is positive returns 1, negative returns -1, 0 or 0



4.10. function: SIN


Grammar:

SIN(number)


Use:

This function returns the arc sine value of number.



4.11. function: SINH


Grammar:

SINH(number)


Use:

This function returns the number return sine value.


4.12. function: COS


Grammar:

COS(number)


Use:

This function returns the trigonometric cosine curve number value. To calculate the cosine angle, can divide the input value is multiplied by 0.01745 into radian after calculation.

4.13.
Function: COSH


Grammar:

COSH(number)


Use:

This function returns the arccosine of input values.



4.14. function: TAN


Grammar:

TAN(number)


Use:

This function returns the arc tangent value of number.



4.15. function: TANH


Grammar:

TANH(number)


Use:

I value the function returns a value of number.



4.16. function: LN


Grammar:

LN(number)


Use:

This function returns the natural logarithm of number.

4.17.
Function: EXP


Grammar:

EXP(number)


Use:

This function returns e (2.71828183) number. Anti process the function of natural logarithm.



4.18. function: LOG


Grammar:

LOG(base,number)


Use:

This function returns base for end, enter a value of number log.



5.
Single function:

A single function can operate on any type of data.


5.1. function: DUMP

Grammar:

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


Use:

The function according to the specified format to display the internal data representation. The following table lists the valid format.

Format code

Description

8

Octal number system

10

Decimal system

16

Hexadecimal

17

Single character


For example:

    SELECT DUMP('FARRELL',16)

    FROM dual


DUMP('FARRELL',16)

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

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


5.2. function: GREATEST

Grammar:

GREATEST(list of values)


Use:

This function returns the maximum value of the items in the list. The numeric or date, the return value is the maximum or the latest date, if the string contains the list in alphabetical order, the return value is the last item in the list.


For example:

    SELECT GREATEST(123,234,432,112)

      FROM dual


GREATEST(123,234,432,112)

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

                      432

5.3. function: LEAST

Grammar:

LEAST(list of values)


Use:

This function returns the minimum value of the items in the list. The numeric or date, the return value is the minimum or the earliest date, if the string contains the list, the return value is the first alphabetical list.


For example:

    SELECT LEAST(sysdate,sysdate-10)

      FROM dual


LEAST(SYS

---------

10-JAN-00


5.4. function: NVL


Grammar:

NVL(expression,replacement_value)


Use:

If the expression is null, the function returns the value of the expression, if it is a null value, return and replacement value.


For example:

    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.
Multi line function

Group function can all values to expression, also can only operate on the different values, group function syntax is as follows:

function[DISTINCT|ALL expression]

If neither a nor DISTINCT, designated ALL, all rows returned by the query function will operate. Cannot set of functions and special functions and used in a SELECT statement in a select column.


6.1. function: AVG

Grammar:

AVG([DISTINCT|ALL] expression)

Use:

The data for the query returns averaging.


For example:

    SELECT AVG(sal) "Average"

      FROM emp

       Average

    ----------

    2073.21429


6.2. function: COUNT

Grammar:

COUNT([DISTINCT|ALL] expression)


Use:

A number of expressions. To calculate the number of EMP table employees.

For example:

    SELECT COUNT(deptno)

      FROM emp

COUNT(DEPTNO)

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

           14

    SELECT COUNT(distinct deptno)

      FROM emp


COUNT(DISTINCTDEPTNO)

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

                    3

6.3. function: MAX


Grammar:

MAX([DISTINCT|ALL] expression)


Use:

For the maximum query set of rows returned. If there is more than one maximum value, will all return to employees the highest wage search companies.


Grammar:

    SELECT ename,sal

      FROM emp

     WHERE sal=(select max(sal)

           FROM emp)


ENAME            SAL

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

KING            5000


6.4. function: MIN


Grammar:

MIN([DISTINCT|ALL] expression)


Use:

The minimum value for the query returns the set of rows. If there is more than a minimum value, will all return.


For example:

    SELECT MIN(last_name)

      FROM employee


MIN(LAST_NAME)

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

ADAMS





6.5. function: SUM


Grammar:

SUM([DISTINCT|ALL] expression)


Use:

Calculate the sum of all non empty query returned. If the data returned is null, the function returns a null value.


For example:

    SELECT SUM(salary)"Total"

      FROM employee

     WHERE department_id=10


    Total

---------

     8750


6.6. function: VARIANCE


Grammar:

VARIANCE([DISTINCT|ALL] expression)


Use:

This function calculates the variance of return all rows.


For example:

    SELECT VARIANCE(salary)

      FROM employee


VARIANCE(SALARY)

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

       973659.27







The pseudo column

The return value

Sequence.CURRVAL

Sequences generated by the sequence generator last name value. Only in the current instance session from the sequence of select a value, the pseudo column will be effective.

LEVEL

Check the depth of it, LEVEL is suitable for special tree query.

Sequence.NEXTVAL

The pseudo column will cause the sequence generator returns the next value of a sequence. Once chosen this value, it cannot be reused, because after each search will return a value.

ROWID

This pseudo columns represent data for the exact location of.ROWID format is AAAAAAAA.BBBB.CCCC a three hexadecimal number 16, where AAAAAAAA represents the database file contains the block number of the line, BBBB is the data row block, and CCCC is the file in the ID. data.

ROWNUM

The retrieved sequence number of rows of data.

SYSDATE

The current date and time.

UID

Identification of ID. for the current user

USER

Database user log in name.





TABLE: LOCATION                                Sector address table

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

LOCATION_ID          NOT NULL NUMBER(3)        Address ID

REGIONAL_GROUP                VARCHAR2(20)     Address name


TABLE: DEPARTMENT                              The name of the Department table

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

DEPARTMENT_ID        NOT NULL NUMBER(2)        Department ID

NAME                          VARCHAR2(14)     The name of the Department

LOCATION_ID                   NUMBER(3)        Address ID(LOCATION.LOCATION_ID)


TABLE: JOB                                     Work table

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

JOB_ID               NOT NULL NUMBER(3)        Type ID

FUNCTION                      VARCHAR2(30)     Type name


TABLE: EMPLOYEE                                Employee information form

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

EMPLOYEE_ID          NOT NULL NUMBER(4)        Employee ID

LAST_NAME                     VARCHAR2(15)

FIRST_NAME                    VARCHAR2(15)

MIDDLE_INITIAL                VARCHAR2(1)     

JOB_ID                        NUMBER(3)        Type ID(JOB.JOB_ID)

MANAGER_ID                    NUMBER(4)        Leadership ID(EMPLOYEE.EMPLOYEE_ID)

HIRE_DATE                     DATE             Date of employment

SALARY                        NUMBER(7,2)      Salary

COMMISSION                    NUMBER(7,2)      Commission

DEPARTMENT_ID                 NUMBER(2)        Department ID(DEPARTMENT.DEPARTMENT_ID)


TABLE: SALARY_GRADE                            The salary scale

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

GRADE_ID             NOT NULL NUMBER(3)        Grade ID

LOWER_BOUND                   NUMBER(7,2)      The minimum wage

UPPER_BOUND                   NUMBER(7,2)      The highest wages


TABLE: PRODUCT                                 Product information sheet

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

PRODUCT_ID           NOT NULL NUMBER(6)        Article number

DESCRIPTION                   VARCHAR2(30)     Name of a commodity


TABLE: PRICE                                   Product price list

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

PRODUCT_ID           NOT NULL NUMBER(6)        Article number(PRODUCT.PRODUCT_ID)

LIST_PRICE                    NUMBER(8,2)      Price

MIN_PRICE                     NUMBER(8,2)      The lowest price

START_DATE           NOT NULL DATE             Effective date

END_DATE                      DATE             Expiration date


TABLE: CUSTOMER                                Customer information table

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

CUSTOMER_ID          NOT NULL NUMBER(6)        The client ID(CUSTOMER.CUSTOMER_ID)

NAME                          VARCHAR2(45)     Customer name

ADDRESS                       VARCHAR2(40)     Customer address

CITY                          VARCHAR2(30)     City

STATE                         VARCHAR2(2)      State

ZIP_CODE                      VARCHAR2(9)      Zip code

AREA_CODE                     NUMBER(3)        Area code

PHONE_NUMBER                  NUMBER(7)        Telephone number

SALESPERSON_ID                NUMBER(4)        Sales ID(EMPLOYEE.EMPLOYEE_ID)

CREDIT_LIMIT                  NUMBER(9,2)      Credit limit

COMMENTS                      LONG             Remarks


TABLE: SALES_ORDER                             Order header table

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

ORDER_ID             NOT NULL NUMBER(4)        Order ID

ORDER_DATE                    DATE             Order date

CUSTOMER_ID                   NUMBER(6)        The client ID(CUSTOMER.CUSTOMER_ID)

SHIP_DATE                     DATE             Date of shipment

TOTAL                         NUMBER(8,2)      The total amount of


TABLE: ITEM                                    Single table orders

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

ORDER_ID             NOT NULL NUMBER(4)        Order ID(SALES_ORDER.ORDER_ID)

ITEM_ID              NOT NULL NUMBER(4)        Order number

PRODUCT_ID                    NUMBER(6)        Article number(PRODUCT.PRODUCT_ID)

ACTUAL_PRICE                  NUMBER(8,2)      The actual price

QUANTITY                      NUMBER(8)        The order quantity

TOTAL                         NUMBER(8,2)      The total amount of






The second part PL/SQL grammar


First, a brief introduction to PL/SQL

(all programs in this lecture are adjustable through)

We first look at a simple example, the following example is the statistics from the sum of 1 to 100.

declare

i number:=0;          /*Declare variables well to initial value*/

t number:=1;

error_message exception; /*Declare an error handling*/

begin

  for t in 1..100 loop

    i:=i+t;

  end loop;

  if i>=5050 then

    raise error_message; /*Error processing*/

  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;

² From the above example can be seen in the general rule of PL/SQL syntax.

­ PL/SQL statements with a semicolon (;) at the end.

­ Start the PL/SQL statement block (such as IF… or BEGIN statement) no clauses.

­ The text value enclosed in single quotation marks (‘ ‘), rather than(“ “).

­ The process allows only finally has an outlet.

² PL/The SQL program can be divided into three parts

­ The DECLARE part is used for variables, constants, functions, process, Cursor.

­ The BEGIN section contains code to perform the PL/SQL block in the program for processing, which can call the function, process.

­ The Exception part is used for error handling.

Let's look at an example:

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;

/






Explain:

1 in the definition of variables can be the initial value of the initial value, there are two ways, one is shown by the program, another is as follows:

Declare

I number default 92;

T number default 0;


2 defining constants

Declare

I constant number:=1;

T constant number:=9;


3 defined function

function function_name(parameter type)return type is

  …declare variant

begin

  …

  …

end function_name;

In the example above we define a function AA, in the begin module reference this function AA().


The 4 definition process

procedure procedure_name(parameter IN type) is

…declare variant

begin

exception

end procedure_name;





See example below:

declare

/*t_emp  c_nt%rowtype;*/

i number:=1;

t number:=1;

procedure te_t(t_t number) is          /*Define a function*/

  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 the definition of 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;

/



We open a cursor, there may be a situation, that is we do not need to record all the cursor, how do we handle:

1 in the definition of a cursor, can be combined with parameters as shown below

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 the record in the cursor items to variables when control, as shown below:

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);

  if condition1  then

   …

   …

  end if


end;


Be careful:

² Because PL/SQL does not support I/O, so the program all the results are placed in the data file.

² Delete from accts where status=’bad debt’

If sql%rowcount>10 then

   Raise out_of_bounds;

End if;


The other:

When you declare a variable, PL/SQL provides two types of variables:%TYPE,%ROWTYPE.

1.%TYPE

When using%TYPE, can use:

² A usage see cases:

declare

Balance number(7,2);

Minimum_balance balance%type:=10.00;

In the example above, the minimum_balance data type is number (7,2) with a default value of 10.00.

² Two use cases (see the data types and table in a column corresponding to the datatype, if the column datatype change, table at run time, data type will automatically transform):

declare

my_dname            empc.empto%type;


2.%rowtype

Using the%rowtype data type for a data line table and cursor relatively.

See example below:









² Declare

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

My_rec my_cursor%rowtype;

Begin

Open my_cursor;

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;







Two,
Variable description

Include the following several common types of variables in PL/SQL:

The ASCII string CHAR- stores fixed length, allowing the storage of digital, text symbols, maximum of 255 characters.

The VARCHAR2- store variable length character strings, although'm defined according to the maximum length of the string, but the difference between VARCHAR2 and CHAR is that if not up to the definition of the length, the space will not be the automatic fill spaces, VARCHAR2 can be placed in 2000 characters.

DATE- is actually stored time information of the date / time stamp, in the use of the date, should consider how to use the date function. The relevant date format function see function description.

The NUMBER- store numeric data, including integer and floating point numbers, data can range from 1Ï to 38Ï 10; 10, but, you have a lot of data space.

BOOLENA- stores a Boolean value. It said yes / no, true / false, 1/0 things.


LONG- this is a text string, whose length is greater than 2000 characters in VARCHAR2 field. It can store up to 2 GB characters, compared with the original binary data, it can only store character information.

RAW- to the original binary data using memory operating system, can be used to store the image or sound recording this information, but the data length is the length of only 255 bytes.

LONG RAW- and LONG type equivalence, but store binary data, the length of up to 2GB bytes.

The BINARY_INTEGER- field in binary format information from the use of calculators,

       -2 to 2 -1.








The other:

PL/SQL two composite types: TABLE and RECORD

1.TABLE

² To define an array, you use the table type definition statement, for example, to define a Last_name array, you can use the following statement:

type last_name_list is table of varchar2(22)

index by binary_integer;

last_name last_name_list;


² When defining a long type, involves a delete table, PL/SQL table cannot use the Delete statement to delete, but each line of the values shown:

sal_tab(3):=null;

Another method is to define two the same type of table type, if another table empty, only the empty table to table emptied. As shown below.

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;

As with all programming languages, the definition of a variable, the same problem exists variable scope:

As shown below:

declare

  x  real;

function  function_name(variant type)return type is

  declare

    x char;

begin

  …

  end function_name;

begin

x:=expression1…

end;




If you want to reference variable to another block, can add block label, as shown below:

<<outer>>

declare

birthdate date;

begin

   …

   declare

birthdate   date;

begin

….

If birthdate=outer.birthdate then

….

End if;

End;

End outer;













Three,
PL/The SQL control program flow

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

IF … THEN

STATMENT1;

elseif…then…

statment2

elseif…then…

statment3

ELSE

STATMENT4;

end if;


An example:

<<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;

An example:

loop

  fetch c1 into …

exit when c1%notfound;

end loop;

close c1;



Other: loop label;

An example:<<outer>>

   loop

    …

    loop

     …

     exit outer when…

    end loop;

     …

  end loop outer;


3.while…loop…End loop structure is shown as follows:

An example

   while total<=25000 loop

     ….

     Select sa1 into salary from emp where…

       Total:=total+salary;

   End loop;


4.for…loop…

In 1 cases

      select count(empno) into emp_count from emp;

      for L in 1..emp_count loop

          …

      end loop;


In 2 cases

   <<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;





Four,
The stored procedure

To create a stored procedure, you can use the following SQL and PL/SQL statements:


CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(parameter list)

AS

BEGIN

(SQL AND PL/SQL COMMANDS)

END;


Five, storage function

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

….

Begin

 …

end;




Six, Package


Package is divided into two parts: Specific and Body

In the package description part, mainly will call parameters and Function contained in this package instructions clear, such as:

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;

In the bag body part, mainly the package description written code part of the process and Function,

Such as:

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;

Seven,
The trigger

² The required system.

To create a trigger for a table, this table must be able to change, therefore not only to have the table, and it should have the permission on the alter table, or a alter any table system privileges, in addition, must have create triger permission, to another user account (account) (also known as mode (schema)) create a trigger, you will have create any trigger system privileges.


² The desired table permissions

The trigger can be referenced tables not initialize the trigger event table.


² The trigger

There are twelve types of triggers. Type a trigger by hierarchical location triggers and trigger affairs type definition.


² A row level trigger

In a transaction, a row level trigger for execution, the ledger records in the list example, trigger. A row level trigger is by using the for each row clause to create in the create trigger command.


² Trigger type legal

When the two types of trigger action when combined, there are twelve possible configurations:

Before insert row level trigger

The before insert statement level trigger

After insert row level trigger

The after insert statement level trigger

Before update row level trigger

The before update statement level trigger

After update row level trigger

after update     A statement level trigger

Before delete row level trigger

The before delete statement level trigger

After delete row level trigger

The after delete statement level trigger







An example:

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;










Eight,
Application examples

The computer program developed by the Department of <<MDS expansion and commencement date maintenance program > > all kinds of program as an example:

1. Create table, index, sequence, table trigger

First clear the name originally has table, Sequence etc.:

DROP TABLE ZDL_BKC_JOB_BODY;

DROP TABLE ZDL_BKC_JOB_HEAD;

DROP TABLE ZDL_BKC_JOB_UPDATE;

drop sequence zdl_bkc_job_s;


The establishment of table, sequence and 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;


The establishment of 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 the establishment of two major package:

A. ZDL_BKC_APP_PKG

Package Specific


CREATE OR REPLACE PACKAGE ZDL_BKC_APP_PKG

AS

/*BOM expansion*/

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);


/*Expansion of semi products on the ZDL_JOB_BKC_HEAD AND ZDL_JOB_BKC_BODY, and call in ZDL_JOB_PKG

The process of realizing JOB phase bear generation and LOAD into 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


/*For each deployment of semi product is assigned an JOB number, which is stored in the 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_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);


/*WIP automatic update of Oracle MFG maintenance O/L date etc.*/

PROCEDURE ZDL_UPDATE_JOB(

P_BKC_ID IN NUMBER,

P_GROUP_ID IN NUMBER,

P_STATUS_TYPE IN NUMBER,

P_USER_ID IN NUMBER);


/*Trigger the WIP JOB MASS LOAD program*/

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


/*In the update using the ZDL_BKC_JOB_UPDATE file Oracle MFG, data will be the fabric.*/

PROCEDURE ZDL_PRE_UPDATE;


/*Correlation program calls the front, and the ZDL_BKC_JOB_UPDATE file to update the Oracle MFG according to the*/

FUNCTION ZDL_UPDATE_ORACLE_WIP(P_USER_ID IN NUMBER) RETURN NUMBER;


/*Check the Oracle JOB state, and return*/

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_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

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;





Self complementary:

The date function:

1, For the day is the day of the week
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual; 
Monday
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual; 
monday 
Set the date language
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'; 
So it can also be
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American') 

2, The number of days between two dates
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;


3, The time for null.
select id, active_date from table1 
UNION 
select 1, TO_DATE(null) from dual; 
Note that to use TO_DATE(null)


4, The number of days in no way processing
select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual 


5, Find the number of days this year
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual 

6, Processing method of leap year
to_char( last_day( to_date('02' || :year,'mmyyyy') ), 'dd' ) 
If 28 is not a leap year


7, An interval of 5 seconds
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 said 5 seconds


8, The year of the first few days
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual 
310 2002-11-6 10:03:51 


9, floor((date2-date1) /365) As the years
floor((date2-date1, 365) /30) As the month
mod(mod(date2-date1, 365), 30)As the day.
The 23.next_day function
next_day(sysdate,6)From the beginning the next Friday. The latter figure is from Sunday onwards.
1 2 3 4 5 6 7 
Day one two three four five six


10, extract()Find the date field or interval value
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()Returns the date and time in the conversation
timestamp_value:=localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual;

LOCALTIMESTAMP
----------------------------
13-11 -03 12.09.15.433000
The afternoon


12, decode()Function


We construct an example, suppose we want to raise the standard of IQ staff, are: wages in 8000 yuan of the following will add 20%; pay more than 8000 yuan in 15%, the usual practice is to choose the wages, the fields of a record value? Select salary into var-salary from employee, and then flow control statements such as using If-Then-Else or choose case to the variable var-salary to judge. If you use the DECODE function, then we can put these flow control statements are omitted, the SQL statement can be directly completed. As follows: select decode (sign (salary - 8000), 1, salary*1.15, -1, salary*1.2, salary from employee is very simple?

Method of use:

  1, Compare the size of

  Select decode (sign (1- variable, -1 1, 2) variables, variable from dual; 2) - smaller

  sign()Function according to a value of 0, positive or negative, respectively return 0, 1, -1


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Nick at November 30, 2013 - 10:19 AM