Oracle Simple Script Example

Oracle Simple Script Example

1. Add a table

-- Date Modified: 2014.09.21

-- modified by: Yixian

-- What to modify: New Purchase Payment status Table

DECLARE

vc_str VARCHAR2 (5000);

vn_count number;

BEGIN

-- See if the existing system has a bt_product_model table

SELECTCOUNT(*)

into vn_count

from user_tables

WHERE table_name =' bt_buy_pay ';

-- if not, add the table, if any, do not process

IF Vn_count < 1 Then

vc_str: = ' CREATE TABLE Bt_buy_pay

(

ID number is not NULL,

Buynumber VARCHAR2 (30),

Money Number (15,2),

Recevietime DATE,

Recordtime DATE,

currency VARCHAR2 (30),

foreigncurrency Number (15,2),

constraint pk_bt_buy_pay_id primary KEY (ID)

) ';

EXECUTE IMMEDIATE vc_str;

END IF;

END ;

2. Add fields

-- Modify People : Yixian

-- Modification Time : 2014-9-18

-- Modify Content : Purchase Contract table increase contract field ( Motobu and Group )

DECLARE

vn_count number;

vc_str VARCHAR2 (1000);

BEGIN

-- See if the field exists in the table

SELECTCOUNT(*)

into vn_count

from user_tab_columns

WHERE table_name =' bt_buy_record ' and column_name = ' Contractbelong ';

IF Vn_count < 1 Then

vc_str: = ' ALTER TABLE bt_buy_record ADD contractbelong VARCHAR2 (+) ';

EXECUTE IMMEDIATE vc_str;

END IF;

END ;

3. Create a view

Create or Replace View V_bt_buy_fprecord_ce as

Selectdistinct B.corp_code,

C.corp_name,-- unit name

D.buynumber,-- procurement contract number

B.buyname,-- name of purchase contract

D.sale_no,-- Sales Contract number

d.product_id,-- product ID

D.product_name,-- product name

D.money,- - detail Amount

B.htprice,-- contract Amount

D.out_money,- - paid amount

Vf.ymoney,-- invoiced Amount

B.status

from Bt_buy_record b

left join Bt_corp c

on b.corp_code = C.corp_code

left join bt_buy_detail D

on b.buynumber = D.buynumber

left join (Select f.detail_id, sum( F.money) as Ymoney

from Bt_buy_fprecord F

Group by f.detail_id) VF

on d.id = vf.detail_id

order by B.corp_code ;

4. Modify the table field to allow null

-- modified by: Yixian

-- Modification Time: the years 9 Month One Day

-- What to modify: Modify a table Bt_buy_fprecord Field Produce_name Allow to empty

-- reason for modification: Purchase contract import times Produce_name to be NULL cannot insert

DECLARE

vn_nullable VARCHAR2 (1);

BEGIN

SELECT Nullable

into vn_nullable

from user_tab_columns

WHERE table_name =' Bt_buy_fprecord '

and column_name =' produce_name ';

-- if null is not allowed

IF vn_nullable = ' N ' Then

-- change to allow empty

EXECUTE IMMEDIATE' ALTER TABLE bt_buy_fprecord MODIFY produce_name VARCHAR2 ($) NULL ' ;

END IF;

COMMIT;

END ;

5. Modify the field length

-- modified by: Yixian

-- Modification Time: the years 9 Month 3 Day

-- What to modify: Modify a table Bt_buy_detail Field Product_Name the length

-- Reason for modification: import times Its field length is not enough

Alter Table bt_buy_detail Modify (product_name varchar2 (500));

-- Modify the Product Name field , Invoice Sheet Import report the length of its field is not enough

Alter Table Bt_buy_fprecord Modify (product_name varchar2 (500));

Commit ;

Oracle Simple Script Example