Oracle simple script example, oracle example

Oracle simple script example, oracle example

Oracle simple script example

1. Add a table

-- Modified on: 2014.09.21

-- Modifier: Yi xiaoqun

-- Modified: added the purchase payment form.

DECLARE

VC_STR VARCHAR2 (5000 );

VN_COUNT NUMBER;

BEGIN

-- Check whether the existing system has a BT_PRODUCT_MODEL table

SELECTCOUNT (*)

INTOVN_COUNT

FROMUSER_TABLES

WHERETABLE_NAME = 'bt _ BUY_PAY ';

-- If no table exists, the table is added. If yes, the table is not processed.

IF VN_COUNT <1THEN

VC_STR: = 'create table BT_BUY_PAY

(

Id NUMBER 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)

)';

EXECUTEIMMEDIATEVC_STR;

ENDIF;

END;

2. Add Fields

-- Modifier: Yi xiaoqun

-- Modification time:

-- Modified: added the contract field (Headquarters and group) in the procurement contract table)

DECLARE

VN_COUNT NUMBER;

VC_STR VARCHAR2 (1000 );

BEGIN

-- Check whether this field exists in the table

SELECTCOUNT (*)

INTOVN_COUNT

FROMUSER_TAB_COLUMNS

WHERETABLE_NAME = 'bt _ BUY_RECORD'ANDCOLUMN_NAME = 'tractbelong ';

IF VN_COUNT <1THEN

VC_STR: = 'alter TABLE BT_BUY_RECORD add contractbelong VARCHAR2 (30 )';

EXECUTEIMMEDIATEVC_STR;

ENDIF;

END;

3. Create a view

CreateorReplaceViewV_bt_buy_fprecord_ceAs

SelectdistinctB. corp_code,

C. corp_name, -- unit name

D. buynumber, -- Procurement Contract No.

B. buyname, -- Procurement contract name

D. sale_no, -- Sales Contract No.

D. product_id, -- product id

D. product_name, -- product name

D. money, -- Detailed amount

B. htprice, -- contract amount

D. out_money, -- paid amount

Vf. ymoney, -- invoiced amount

B. status

FromBt_buy_record B

LeftjoinBt_corp c

OnB. corp_code = c. corp_code

LeftjoinBt_buy_detail d

OnB. buynumber = d. buynumber

Leftjoin(SelectF. detail_id, sum (f. money)AsYmoney

FromBt_buy_fprecord f

GroupbyF. detail_id) vf

OnD. id = vf. detail_id

OrderbyB. corp_code;

4. The field for modifying the table can be blank.

-- Modifier: Yi xiaoqun

-- Modification date: January 1, September 11, 2014

-- Modification content: the PRODUCE_NAME field of the BT_BUY_FPRECORD table can be blank.

-- Reason for modification: PRODUCE_NAME cannot be inserted when it is NULL when the procurement contract is imported

DECLARE

VN_NULLABLE VARCHAR2 (1 );

BEGIN

SELECTNullable

INTOVN_NULLABLE

FROMUser_tab_columns

WHERETable_name = 'bt _ BUY_FPRECORD'

ANDColumn_name = 'produce _ name ';

-- If it cannot be blank

IF VN_NULLABLE = 'n'THEN

-- Changed to allow null

EXECUTEIMMEDIATE'Alter TABLE BT_BUY_FPRECORD MODIFY PRODUCE_NAME VARCHAR2 (200) null ';

ENDIF;

COMMIT;

END;

5. Modify the Field Length

-- Modifier: Yi xiaoqun

-- Modification date: January 1, September 3, 2014

-- Modify: Modify the length of PRODUCT_NAME in the BT_BUY_DETAIL field of the table.

-- Reason for modification: the length of the field is insufficient during import.

AltertableBT_BUY_DETAIL modify (PRODUCT_NAME varchar2 (500 ));

-- Modify the product name field. When the invoice table is imported, the length of the field is insufficient.

AltertableBT_BUY_FPRECORD modify (PRODUCT_NAME varchar2 (500 ));

Commit;

Oracle script

I can only teach you the general idea, but the program still has to be written by yourself. Students!
It indicates that the two databases are accessible. Does DBLINK be used for Table query?
If yes, select count (*) from B @ dblink_name; in the C database.
As for matching and distributing data, you can use the process and function. Or SHELL scripts.

Supplement:
I will standardize your example:
Declare
N_count number (10); -- defines 10 digits of the variable type.
Begin
Select count (*) into n_count from table;
If n_count = 0 then
Insert ...;
End if;
End;

What do Oracle table creation scripts and tables and fields mean? Example?

Create table student // create a student table
(Stuid number (8) primary key, // student ID integer primary key
Stuname varchar2 (20) not null, // The Student name is changed to a string (up to 20) not empty
Classid number (8) foreign key references class (classid) // foreign key of the student class number
)