SQL>
SQL>
SQL>
SQL> CREATE TABLE Department (
2 DepartmentID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL,
4 Description VARCHAR(200) NULL);
Table created.
SQL>
SQL> CREATE SEQUENCE DepartmentIDSeq;
Sequence created.
SQL>
SQL> CREATE OR REPLACE TRIGGER DepartmentAutonumberTrigger
2 BEFORE INSERT ON Department
3 FOR EACH ROW
4 BEGIN
5 SELECT DepartmentIDSeq.NEXTVAL
6 INTO :NEW.DepartmentID FROM DUAL;
7 END;
8 /
Trigger created.
SQL>
SQL> INSERT INTO Department (Name, Description)
2 VALUES ('Software', 'Coding');
1 row created.
SQL> INSERT INTO Department (Name, Description)
2 VALUES ('Hardware', 'Building');
1 row created.
SQL> INSERT INTO Department (Name, Description)
2 VALUES ('QA', 'Testing');
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE or replace PROCEDURE UpdateDepartment
2 (DeptID IN integer,
3 DepartmentName IN varchar2,
4 DepartmentDescription IN varchar2)
5
6 AS
7 BEGIN
8 UPDATE Department
9 SET Name = DepartmentName,
10 Description = DepartmentDescription
11 WHERE DepartmentID = DeptID;
12 END;
13 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> EXECUTE UpdateDepartment (1, 'Strange new name', 'Strange new description');
PL/SQL procedure successfully completed.
SQL> SELECT * FROM Department;
DEPARTMENTID NAME
------------ --------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
1 Strange new name
Strange new description
2 Hardware
Building
3 QA
Testing
SQL>
SQL>
SQL> drop sequence DepartmentIDSeq;
Sequence dropped.
SQL> drop table Department;
Table dropped.
SQL>
SQL>
SQL>
SQL>