DROP TABLEIF EXISTS' CUSTOMERS ';CREATE TABLECUSTOMERS (' id 'INT( One) not NULLAuto_increment,--ID' Cust_num 'INT( One),--Customer number' Company 'VARCHAR( -),--Company Name' Cust_rep 'INT( One),--Employees' Credit_limit 'DECIMAL( -,2),--Credit Limit PRIMARY KEY(' id '));DROP TABLEIF EXISTS' salesreps ';CREATE TABLE' salesreps ' (' ID ')INT( One) not NULLAuto_increment,--ID' Quota 'DECIMAL( -,2),--Sales Target' Empl_num 'INT( One ), PRIMARY KEY(' id '));INSERT into' Salesreps ' (ID, QUOTA, Empl_num)VALUES ( 1,20000.00,103 );DROP TABLEIF EXISTS' offices ';CREATE TABLE' Offices ' (' ID ')INT( One) not NULLauto_increment, ' target 'DECIMAL( -,2), ' City 'VARCHAR( - ), PRIMARY KEY(' id '));INSERT into' offices ' (ID, Target, city)VALUES ( 1,20000.00,'Shenzhen' );DROP PROCEDUREIF EXISTS' add_cust ';D elimiter//CREATE PROCEDURE' Add_cust ' (inchC_nameVARCHAR( - ), inchC_numINTEGER, inchCred_limDECIMAL( -,2 ), inchTgt_slsDECIMAL( -,2 ), inchC_repINTEGER, inchC_offcVARCHAR( - ) ) BEGIN INSERT intoCustomers (Cust_num, company, Cust_rep, Credit_limit)VALUES(C_num, C_name, C_rep, Cred_lim); UPDATEsalesrepsSETQuota=Quota+Quota+Tgt_slsWHEREEmpl_num=C_rep; UPDATEOfficesSETTarget=Target+Tgt_slsWHERE City=C_OFFC; COMMIT;END //Call ' Add_cust ' ('John Doe',504,200.00,500.00,309,'Guangzhou');
MySQL stored procedure (with input parameters) instance