The following walkthrough describes how to edit project and how to synchronize a schema and a project. A project from the walkthrough will be used there.
You should have the following privileges:
To complete this walkthrough, you must have completed the walkthrough.
In the previous walkthrough the project Demobase was created and deployed. Now you will add stored procedures to it. For this:
Type or paste the following code into the SQL document:
CREATE PROCEDURE demobase.addemp(IN EmpName CHAR(20), IN Salary INT) BEGIN DECLARE e_No INT; DECLARE e_Name CHAR (20) DEFAULT 'Unnamed'; DECLARE e_Sal INT DEFAULT 1100; IF EmpName IS NOT NULL THEN SET e_Name = EmpName; END IF; IF Salary IS NOT NULL THEN SET e_Sal = Salary; END IF; SELECT MAX(empno) INTO e_No FROM demobase.emp; INSERT INTO demobase.emp (empno, ename, sal, hiredate) VALUES (e_no + 10, e_name, e_sal, CURDATE()); END
Right-click the Procedures folder in Project Explorer and choose Add Existing File on the shortcut menu
Click on the Procedures folder in Project Explorer and choose Add Existing File on the Project menu.
Repeat steps 1-7 for the RaiseSalary procedure.
CREATE PROCEDURE demobase.RaiseSalary(IN department VARCHAR(14), IN rate FLOAT) COMMENT 'Raise salary of all employee working in this department' BEGIN DECLARE d_No INT DEFAULT 0; SELECT DeptNo INTO d_No FROM Dept WHERE Dname=Department; UPDATE Emp SET Sal=Sal*Rate WHERE DeptNo=d_No; END
Suppose that you don’t need to store sum of the employee salary for the departments. Delete salsum column in the dept table and all triggers, updating it.
Now, you’ll compare your project with the schema. Perform the following steps to create new schema comparison document.
Select New Schema Comparison on the Comparison menu.
Click Project in source or in target, then choose the project in the field below.
The schema comparison document will open. You can see comparison status of all schema and project options here. Table emp has Equal status. It has the synchronization operation Skip and will not be affected when synchronizing schema and project. Table dept has Different status. Click on it to see differences in the DDL. It has the Update synchronization operation and will be updated during synchronization.
View staff also has Different status. That’s because view select statement is stored in unformatted and single-line form. Views in project and in database will always have Different status, unless you use the same unformatted view form in the project. You don’t need to synchronize view now, click on its operation and choose Skip from the drop-down list.
Procedures have Only in Source status. They exist only in project and will be created during synchronization. Triggers exist only in target and will be dropped during synchronization.
Click the View Schema Update Script button on the Comparison toolbar to preview update script. To synchronize project, return back to the schema comparison document and click the Synchronize button on the Comparison toolbar. Select what you want to do with the synchronization script in the displayed Schema synchronization wizard. Click the Synchronize button to close the wizard and complete the synchronization. Review the synchronization script (if you selected to view it in the editor) and then execute it by clicking the Execute button on the toolbar or selecting SQL -> Execute on the top menu.