CHAPTER TWO

PL/SQL SYNTAX
PL/SQL stands for “Procedural Language extensions to the Structured Query
Language”. SQL is a popular language for both querying and updating data in
the relational database management systems (RDBMS).

PL/SQL adds many procedural constructs to SQL language to overcome some

limitations of SQL. Besides, PL/SQL provides a more comprehensive
programming language solution for building mission-critical applications on
Oracle Databases.

PL/SQL is a highly structured and readable language. Its constructs express

the intent of the code clearly. Also, PL/SQL is a straightforward language to
learn.

PL/SQL is a standard and portable language for Oracle Database development.

If you develop a program that executes on an Oracle Database, you can quickly
move it to another compatible Oracle Database without any changes.

PL/SQL is an embedded language. PL/SQL only can execute in an Oracle

Database. It was not designed to use as a standalone language like Java, C#,
and C++. In other words, you cannot develop a PL/SQL program that runs on a
system that does not have an Oracle Database.

PL/SQL is a high-performance and highly integrated database language.

Besides PL/SQL, you can use other programming languages such as Java, C#,
and C++. However, it is easier to write efficient code in PL/SQL than other
programming languages when it comes to interacting with the Oracle Database.

PL/SQL architecture

The following picture illustrates the PL/SQL architecture:

1
PL/SQL engine is in charge of compiling PL/SQL code into byte-code and
executes the executable code. The PL/SQL engine can only be installed in an
Oracle Database server or an application development tool such as Oracle
Forms.

Once you submit a PL/SQL block to the Oracle Database server, the PL/SQL
engine collaborates with the SQL engine to compile and execute the code.
PL/SQL engine runs the procedural elements while the SQL engine processes
the SQL statements.

Now you should have a basic understanding of PL/SQL programming language

and its architecture.

Let’s create the first working PL/SQL anonymous block.

PL/SQL Anonymous Block

You will learn about the PL/SQL anonymous block and how to execute it using
SQL*Plus and SQL Developer tools.

PL/SQL anonymous block overview

PL/SQL is a block-structured language whose code is organized into blocks. A

PL/SQL block consists of three sections: declaration, executable, and
exception-handling sections. In a block, the executable section is mandatory
while the declaration and exception-handling sections are optional.

2
A PL/SQL block has a name. Functions or Procedures is an example of a
named block. A named block is stored into the Oracle Database server and
can be reused later.

A block without a name is an anonymous block. An anonymous block is not

saved in the Oracle Database server, so it is just for one-time use. However,
PL/SQL anonymous blocks can be useful for testing purposes.

The following picture illustrates the structure of a PL/SQL block:

1) Declaration section

A PL/SQL block has a declaration section where you declare variables, allocate
memory for cursors, and define data types.

2) Executable section

A PL/SQL block has an executable section. An executable section starts with

the keyword BEGIN and ends with the keyword END. The executable section
must have a least one executable statement, even if it is
the NULL statement which does nothing.
3) Exception-handling section

3
A PL/SQL block has an exception-handling section that starts with the
keyword EXCEPTION. The exception-handling section is where you catch and
handle exceptions raised by the code in the execution section.
Note a block itself is an executable statement, therefore you can nest a block
within other blocks.

PL/SQL anonymous block example

The following example shows a simple PL/SQL anonymous block with one
executable section.

BEGIN
DBMS_OUTPUT.put_line ('Hello World!');
END;

The executable section calls the DMBS_OUTPUT.PUT_LINE procedure to

display the "Hello World" message on the screen.
Execute a PL/SQL anonymous block using SQL*Plus

Once you have the code of an anonymous block, you can execute it using
SQL*Plus, which is a command-line interface for executing SQL statement and
PL/SQL blocks provided by Oracle Database.

The following picture illustrates how to execute a PL/SQL block using

SQL*Plus:

First, connect to the Oracle Database server using a username and password.

Second, turn on the server output using the SET SERVEROUTPUT ON command so
that the DBMS_OUTPUT.PUT_LINE procedure will display text on the screen.
Third, type the code of the block and enter a forward slash ( /) to instruct
SQL*Plus to execute the block. Once you type the forward-slash (/), SQL*Plus

4
will execute the block and display the Hello World message on the screen as
shown in the illustrations.
Note that you must execute SET SERVEROUTPUT ON command in every session
that you connect to the Oracle Database in order to show the message using
the DBMS_OUTPUT.PUT_LINE procedure.
To execute the block that you have entered again, you use / command instead
of typing everything from the scratch:

Execute a PL/SQL anonymous block using SQL Developer

First, connect to the Oracle Database server using Oracle SQL Developer.

Second, create a new SQL file named [Link] resided in

the C:\plsql directory that will store the PL/SQL code.

5
Third, enter the PL/SQL code and execute it by clicking the Execute button or
pressing the Ctrl-Enter keyboard shortcut.

6
More PL/SQL anonymous block examples

In this example, we first declare a variable l_message that holds the greeting
message. And then, in the execution section, we use
the DBMS_OUTPUT.PUTLINE procedure to show the content of this variable
instead of using a literal string.

DECLARE
l_message VARCHAR2( 255 ) := 'Hello World!';
BEGIN
DBMS_OUTPUT.PUT_LINE( l_message );
END;
Here is the output:

1 Hello World!
The next anonymous block example adds an exception-handling section which
catches ZERO_DIVIDE exception raised in the executable section and displays
an error message.
1 DECLARE
2 v_result NUMBER;
3 BEGIN
4 v_result := 1 / 0;
5 EXCEPTION

7
6 WHEN ZERO_DIVIDE THEN
7 DBMS_OUTPUT.PUT_LINE( SQLERRM );
8 END;
The error mesage is:

1 ORA-01476: divisor is equal to zero

Now, you should know how to create PL/SQL anonymous blocks and execute
them using SQL*Plus and Oracle SQL Developer tools.

The SQLERRM function returns the error message associated with the most
recently raised error exception. This function should only be used within the
Exception Handling section of your code.
Oracle Data Types

In this section, you will get a brief overview of the PL/SQL data types including
numbers, Boolean, character, and datetime.

Introduction to PL/SQL data types

Each value in PL/SQL such as a constant, variable and parameter has a data
type that determines the storage format, valid values and allowed operations.

PL/SQL has two kinds of data types: scalar and composite. The scalar types
are types that store single values such as number, Boolean, character, and
datetime whereas the composite types are types that store multiple values, for
example, record and collection.

This section explains the scalar data types that store values with no internal
components.

PL/SQL divides the scalar data types into four families:

 Number
 Boolean
 Character
 Datetime
A scalar data type may have subtypes. A subtype is a data type that is a subset
of another data type, which is its base type. A subtype further defines a base
type by restricting the value or size of the base data type.

8
Note that PL/SQL scalar data types include SQL data types and its own data
type such as Boolean.

Numeric data types

The numeric data types represent real numbers, integers, and floating-point
numbers. They are stored as NUMBER, IEEE floating-point storage types
(BINARY_FLOAT and BINARY_DOUBLE), and PLS_INTEGER.
The data types NUMBER, BINARY_FLOAT, and BINARY_DOUBLE are SQL data types.
The PLS_INTEGER datatype is specific to PL/SQL. It represents signed 32 bits
integers that range from -2,147,483,648 to 2,147,483,647.
Because PLS_INTEGER datatype uses hardware arithmetic, they are faster
than NUMBER operations, which uses software arithmetic.
In addition, PLS_INTEGER values require less storage than NUMBER. Hence, you
should always use PLS_INTEGER values for all calculation in its range to increase
the efficiency of programs.
The PLS_INTEGER datatype has the following predefined subtypes:

PLS_INTEGER Description
subtypes
NATURAL Represents nonnegative PLS_INTEGER values
NATURALN Represents nonnegative PLS_INTEGER values with NOT
NULL constraint

POSITIVE Represents positive PLS_INTEGER values

POSITIVEN Represents positive PLS_INTEGER value with NOT NULL constraint
SIGNTYPE Represents three values -1, 0, or 1, which are useful for tri-state
logic programming
SIMPLE_INTEGER Represents PLS_INTEGER values with NOT NULL constraint.
Note that PLS_INTEGER and BINARY_INTEGER data types are identical.
Boolean data type

The BOOLEAN datatype has three data values: TRUE, FALSE, and NULL.
Boolean values are typically used in control flow structure such as IF-
THEN, CASE, and loop statements like LOOP, FOR LOOP, and WHILE LOOP.
SQL does not have the BOOLEAN data type, therefore, you cannot:

 Assign a BOOLEAN value to a table column.

 Select the value from a table column into a BOOLEAN variable.
 Use a BOOLEAN value in a SQL function.
 Use a BOOLEAN expression in a SQL statement.

9
  Use a BOOLEAN value in
the DBMS_OUTPUT.PUTLINE and DBMS_OUTPUT.PUT subprograms.
Character data types

The character data types represent alphanumeric text. PL/SQL uses the SQL
character data types such as CHAR, VARCHAR2, LONG, RAW, LONG RAW, ROWID,
and UROWID.

 CHAR(n) is a fixed-length character type whose length is from 1 to 32,767

bytes.
 VARCHAR2(n) is varying length character data from 1 to 32,767 bytes.

Datetime data types

The datetime data types represent dates, timestamp with or without time zone
and intervals. PL/SQL datetime data types
are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL
TIME ZONE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND.
Data type synonyms

Data types have synonyms for compatibility with non-Oracle data sources such
as IBM Db2, SQL Server. And it is not a good practice to use data type
synonym unless you are accessing a non-Oracle Database.

Data Type Synonyms

NUMBER DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, INT, NUMERIC, REAL
SMALLINT
CHAR CHARACTER, STRING
VARCHAR2 VARCHAR
Now, you should have a complete overview of PL/SQL data types for
manipulating data in PL/SQL program.

PL/SQL Comments

In this section, you will learn how to use PL/SQL comments including single-
line and multi-line comments that allow you to improve the readability of your
code.

Introduction to PL/SQL Comments

10
PL/SQL comments allow you to describe the purpose of a line or a block of
PL/SQL code.

When compiling the PL/SQL code, the Oracle pre-compiler ignores comments.
However, you should always use comments to make your code more readable
and to help you and other developers understand it better in the future.

PL/SQL has two comment styles: single-line and multi-line comments.

Single-line comments

A single-line comment starts with a double hyphen ( --) that can appear
anywhere on a line and extends to the end of the line.
For example, the following single-line comment explains the meaning of
the co_vat_rate constant:
1 -- valued added tax 10%
2 DECLARE co_vat_rate CONSTANT NUMBER := 0.1;

Sometimes, while testing a program, you may use a single-line comment to

disable a line of code. The following illustrates how to comment out a line of
code:

1 -- UPDATE products SET list_price = 0 WHERE product_id = l_id;

If you want to comment just potion of a line, you can also use the single-line
comment. The following example shows how to comment out
the WHERE clause of the UPDATE statement. All the code which follows the
double-hyphen -- to the rest of the line will be treated as a comment:
1 UPDATE products SET list_price = 0; -- WHERE product_id = l_id;

Note that we add the semicolon (;) before the (--) to make the statement valid.
Multi-line comments

A multi-line comment starts with a slash-asterisk ( /* ) and ends with an

asterisk-slash ( */ ), and can span multiple lines:
1 /*
2 This is a multi-line comment
3 that can span multiple lines
4 */

Note that it is possible to use a multi-line comment as a single-line comment:

11
1 /* A multi-line comment can be used as a single-line comment */

We often use a multi-line comment to describe the purpose of a block of code

like the following example:

1 /*
2 This code allow users to enter the customer id and
3 return the corresponding customer name and credit limit
4 */
5 DECLARE
6 l_customer_name [Link]%TYPE;
7 l_credit_limit customers.credit_limit%TYPE;
8 BEGIN
9 ...
10 END;
11 /

For the maintainability, it is not a good practice to mix comments as follows:

1 BEGIN
2 -- single-line comment /* another comment */
3 NULL;
4 /*
5 multi-line comment
6 -- that has another single-line comment
7 */
8 END;
9/

Instead, use the following:

1 BEGIN
2 -- single-line comment, another comment

12
3 NULL;
4 /*
5 multi-line comment
6 that has another single-line comment
7 */
8 END;
9/

PL/SQL comment usage notes

PL/SQL does not allow you to nest a multi-line comment within another multi-
line comment. The following code block is not valid:

1 BEGIN
2 /*
3 a multi-line comment
4 /*
5 a nested multi-line comment
6 */
7 */ -- -> error
8 END;
9/

For a PL/SQL block that will be processed dynamically, you cannot use single-
line comments. Because Oracle precompiler will ignore the end-of-line
characters that cause the single-line comments extend to the end of the block.
In this case, you can use multi-line comments instead.

In this tutorial, you have learned about PL/SQL comments including single-
line and multi-line comments that allow you to document the purpose of your
code

PL/SQL | DDL, DQL, DML, DCL and TCL Commands

Structured Query Language (SQL) as we all know is the database language by
the use of which we can perform certain operations on the existing database

13
and also we can use this language to create a database. SQL uses certain
commands like Create, Drop, and Insert etc. to carry out the required tasks.
These SQL commands are mainly categorized into four categories as:
1. DDL – Data Definition Language
2. DQl – Data Query Language
3. DML – Data Manipulation Language
4. DCL – Data Control Language
5. TCL-Transaction Control Language

1. DDL (Data Definition Language): DDL or Data Definition Language

actually consists of the SQL commands that can be used to define the
database schema. It simply deals with descriptions of the database
schema and is used to create and modify the structure of database objects
in the database.
Examples of DDL commands:
 CREATE – is used to create the database or its objects (like table,
index, function, views, store procedure and triggers).
 DROP – is used to delete objects from the database.
 ALTER-is used to alter the structure of the database.
 TRUNCATE–is used to remove all records from a table, including all
spaces allocated for the records are removed.
 COMMENT –is used to add comments to the data dictionary.
 RENAME–is used to rename an object existing in the database.

2. DQL (Data Query Language) :

DML statements are used for performing queries on the data within
schema objects. The purpose of DQL Command is to get some schema
relation based on the query passed to it.
Example of DQL:
 SELECT – is used to retrieve data from the database.

3. DML (Data Manipulation Language): The SQL commands that deals with
the manipulation of data present in the database belong to DML or Data
Manipulation Language and this includes most of the SQL statements.
Examples of DML:
 INSERT – is used to insert data into a table.
 UPDATE – is used to update existing data within a table.
 DELETE – is used to delete records from a database table.
4. DCL (Data Control Language): DCL includes commands such as GRANT
and REVOKE which mainly deals with the rights, permissions and other
controls of the database system.
Examples of DCL commands:
 GRANT-gives user’s access privileges to database.

14
  REVOKE-withdraw user’s access privileges given by using the GRANT
command.

5. TCL (transaction Control Language): TCL commands deals with

the transaction within the database.
Examples of TCL commands:
 COMMIT– commits a Transaction.
 ROLLBACK– rollbacks a transaction in case of any error occurs.
 SAVEPOINT–sets a savepoint within a transaction.
 SET TRANSACTION–specify characteristics for the transaction.

ORACLE DDL

 Create Table

Introduction to Oracle CREATE TABLE statement

To create a new table in Oracle Database, you use the CREATE TABLE statement.
The following illustrates the basic syntax of the CREATE TABLE statement:

CREATE TABLE schema_name.table_name (

column_1 data_type column_constraint,
column_2 data_type column_constraint,
...
table_constraint
);

CREATE TABLE [Link](

person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
PRIMARY KEY(person_id)
);

The person_id is the identity column that identifies unique rows in the table. The
data type of the person_id column is NUMBER. The clause GENERATED BY DEFAULT
AS IDENTITY instructs Oracle to generate a new integer for the column whenever
a new row is inserted into the table.

 Oracle ALTER TABLE

To modify the structure of an existing table, you use the ALTER

TABLE statement. The following illustrates the syntax:

15
1 ALTER TABLE table_name action;
The ALTER TABLE statement allows you to:

 Add one or more columns

 Modify column definition
 Drop one or more columns
 Rename columns
 Rename table
Let’s see some examples to understand how each action works.

Oracle ALTER TABLE examples

We will use the persons table that we created in the previous tutorial for the
demonstration

 Oracle ALTER TABLE ADD column examples

To add a new column to a table, you use the following syntax:

1 ALTER TABLE table_name

2 ADD column_name type constraint;

For example, the following statement adds a new column named birthdate to
the persons table:

ALTER TABLE persons

ADD birthdate DATE NOT NULL;

If you view the persons table, you will see that the birthdate column is
appended at the end of the column list:

DESC persons;

Name Null Type

---------- -------- ------------
PERSON_ID NOT NULL NUMBER
FIRST_NAME NOT NULL VARCHAR2(50)
LAST_NAME NOT NULL VARCHAR2(50)
BIRTHDATE NOT NULL DATE

16
To add multiple columns to a table at the same time, you place the new
columns inside the parenthesis as follows:
ALTER TABLE table_name
ADD (
column_name type constraint,
column_name type constraint,
...
);

ALTER TABLE persons

ADD (
phone VARCHAR(20),
email VARCHAR(100)
);

In this example, the statement added two new columns

named phone and email to the persons table.

DESC persons

Name Null Type

---------- -------- -------------
PERSON_ID NOT NULL NUMBER
FIRST_NAME NOT NULL VARCHAR2(50)
LAST_NAME NOT NULL VARCHAR2(50)
BIRTHDATE NOT NULL DATE
PHONE VARCHAR2(20)
EMAIL VARCHAR2(100)

 Oracle ALTER TABLE MODIFY column examples

To modify the attributes of a column, you use the following syntax:

ALTER TABLE table_name

MODIFY column_name type constraint;
For example, the following statement changes the birthdate column to a null-
able column:
ALTER TABLE persons MODIFY birthdate DATE NULL;

 To modify multiple columns, you use the following syntax:

ALTER TABLE table_name

MODIFY ( column_1 type constraint,

17
 column_1 type constraint,
...);
For example, the following statement changes the phone and email column
to NOT NULL columns and extends the length of the email column to 255
characters:
ALTER TABLE persons MODIFY(
phone VARCHAR2(20) NOT NULL,
email VARCHAR2(255) NOT NULL
);

 Oracle ALTER TABLE DROP COLUMN example

To remove an existing column from a table, you use the following syntax:

ALTER TABLE table_name

DROP COLUMN column_name;

This statement deletes the column from the table structure and also the data
stored in that column.

The following example removes the birthdate column from the persons table:

ALTER TABLE persons

DROP
COLUMN birthdate;

To drop multiple columns at the same time, you use the syntax below:

ALTER TABLE table_name

DROP (column_1,column_2,...);

For example, the following statement removes the phone and email columns
from the persons table:
ALTER TABLE persons
DROP
( email, phone );

 Oracle ALTER TABLE RENAME column example

Since version 9i, Oracle added a clause for rename a column as follows:

ALTER TABLE table_name

RENAME COLUMN column_name TO new_name;

18
For example, the following statement renames the first_name column
to forename column:
ALTER TABLE persons
RENAME COLUMN first_name TO forename;

 Oracle ALTER TABLE RENAME table example

To give a table a new name, you use the following syntax:

ALTER TABLE table_name

RENAME TO new_table_name;

For example, the statement below renames the persons table to people table:

ALTER TABLE persons RENAME TO people;

PL/SQL - SELECT Statement

Oracle SELECT statement is used to fetch the data from a database table
which returns data in the form of result table. These result tables are called
result-sets.

Syntax

The syntax for the SELECT statement in Oracle/PLSQL is:

SELECT expressions FROM tables [WHERE conditions];

SELECT * FROM homes WHERE bathrooms >= 2 ORDER BY home_type ASC;

Clauses used with Select

SQL DISTINCT Clause: produces a list of only those values that are different
from one another SELECT DISTINCT column1, column2....columnN FROM
table_name;

SQL WHERE Clause: You can select partial table contents by placing
restrictions on the rows to be included in the output.

19
This is done by using the WHERE clause to add conditional restrictions to the
SELECT statement. SELECT column1, column2....columnN FROM table_name
WHERE CONDITION;

SQL AND/OR Clause: SELECT column1, column2....columnN FROM

table_name WHERE CONDITION-1 {AND|OR} CONDITION-2;

SQL IN Clause: Used to check whether an attribute value matches any value
within a value list SELECT column1, column2....columnN FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);

SQL BETWEEN Clauses: Used to check whether an attribute value is within a

range SELECT column1, column2....columnN FROM table_name WHERE
column_name BETWEEN val-1 AND val-2;

SQL LIKE Clause: Used to check whether an attribute value matches a given
string pattern.

The LIKE special operator is used in conjunction with wildcards to find

patterns within string attributes.

Standard SQL allows you to use the percent sign (%) and underscore (_)
wildcard characters to make matches when the entire string is not known:

Oracle ORDER BY clause In Oracle, a table stores its rows in unspecified order
regardless of the order which rows were inserted into the database.

To query rows in either ascending or descending order by a column, you must

explicitly instruct Oracle Database that you want to do so.

SELECT name,address, credit_limit FROM customers ORDER BY name ASC

SELECT country_id,city, state FROM locations ORDER BY state ASC NULLS

FIRST;

SELECT country_id, city, state FROM locations ORDER BY state ASC NULLS
LAST;

20
You don’t need to specify the column names for sorting the data. If you prefer,
you can use the positions of the column in the ORDER BY clause. See the
following statement:

SELECT name, credit_limit FROM customers ORDER BY 2 DESC, 1;

DML: Data Manipulation Language

Oracle DELETE

To delete one or more rows from a table, you use the

Oracle DELETE statement as follows:

DELETE FROM table_name WHERE condition;

DELETE FROM sales WHERE order_id = 1 AND item_id = 1

Introduction to Oracle INSERT statement

To insert a new row into a table, you use the Oracle INSERT statement as
follows:

1
INSERT INTO table_name (column_list) VALUES( value_list);
2

Overview of Oracle INSERT INTO SELECT statement

Sometimes, you want to select data from a table and insert it into another
table. To do it, you use the Oracle INSERT INTO SELECT statement as follows:

INSERT INTO target_table (col1, col2, col3) SELECT col1, col2, col3 FROM
source_table WHERE condition;

The Oracle INSERT INTO SELECT statement requires the data type of the source
and target tables match.

CREATE TABLE sales (

4 customer_id NUMBER,
5 product_id NUMBER,
6 order_date DATE NOT NULL,
7 total NUMBER(9,2) DEFAULT 0 NOT NULL,

21
8 PRIMARY KEY(customer_id,
9 product_id,
order_date)
);

CREATE TABLE sales_2017

AS SELECT * FROM sales
WHERE 1 = 0;

Unconditional Oracle INSERT ALL statement

Insert multiple rows into a table

To insert multiple rows into a table, you use the following Oracle INSERT
ALL statement:
1 INSERT ALL
2 INTO table_name(col1,col2,col3) VALUES(val1,val2, val3)
3 INTO table_name(col1,col2,col3) VALUES(val4,val5, val6)
4 INTO table_name(col1,col2,col3) VALUES(val7,val8, val9)
5 Subquery;

INSERT ALL
INTO fruits(fruit_name, color)
VALUES ('Apple','Red')

INTO fruits(fruit_name, color)

VALUES ('Orange','Orange')

INTO fruits(fruit_name, color)

VALUES ('Banana','Yellow')
SELECT 1 FROM dual;

Conditional Oracle INSERT ALL Statement

The conditional multi table insert statement allows you to insert rows into
tables based on specified conditions.

The following shows the syntax of the conditional multi table insert statement:

1 INSERT [ ALL | FIRST ]

2 WHEN condition1 THEN
3 INTO table_1 (column_list ) VALUES (value_list)

22
4 WHEN condition2 THEN
5 INTO table_2(column_list ) VALUES (value_list)
6 ELSE
7 INTO table_3(column_list ) VALUES (value_list)
8 Subquery

Oracle INSERT ALL restrictions

The Oracle multi table insert statement is subject to the following main
restrictions:

 It can be used to insert data into tables only, not views or materialized
view.
 It cannot be used to insert data into remote tables.
 The number of columns in all the INSERT INTO clauses must not exceed
999.
 A table collection expression cannot be used in a multi table insert
statement.
 The subquery of the multitable insert statement cannot use a sequence.

Introduction to the Oracle UPDATE statement

To changes existing values in a table, you use the following

Oracle UPDATE statement:

UPDATE table_name
SET column1 = value1, column2 =value2, column3 =value3, WHERE condition;

UPDATE parts
SET lead_time = 30, cost = 120, status = 1 WHERE part_id = 5;

23