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
)