Use procedure to update table : Update Data « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. Update Data

Use procedure to update table

    
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>

   
    
    
    
  

Related examples in the same category