Ora-Accesul La Date PL-SQL

PL/SQL - Interactiunea cu serverul Oracle -1

Copyright Oracle Corporation, 1998. All rights reserved.

1818

Interacting with the

Oracle Server

Interacting with the

Oracle Server

18-2 Copyright Oracle Corporation, 1998. All rights reserved.

ObjectivesObjectives

After completing this lesson, you should be able to do the following:

• Write a successful SELECT statement in PL/SQL

• Declare the datatype and size of a PL/SQL variable dynamically

• Write DML statements in PL/SQL

• Control transactions in PL/SQL

• Determine the outcome of SQL DML statements

After completing this lesson, you should be After completing this lesson, you should be

able to do the following:able to do the following:

• Write a successful SELECT statement in PL/SQL

• Declare the datatype and size of a PL/SQL variable dynamically

• Write DML statements in PL/SQL

• Control transactions in PL/SQL

• Determine the outcome of SQL DML statements


SQL Statements in PL/SQLSQL Statements in PL/SQL

• Extract a row of data from the database by using the SELECT command. Only a single set of values can be returned.

• Make changes to rows in the database by using DML commands.

• Control a transaction with the COMMIT, ROLLBACK, or SAVEPOINT command.

• Determine DML outcome with implicit cursors.

• Extract a row of data from the database by using the SELECT command. Only a single set of values can be returned.

• Make changes to rows in the database by using DML commands.

• Control a transaction with the COMMIT, ROLLBACK, or SAVEPOINT command.

• Determine DML outcome with implicit cursors.


SELECT Statements in PL/SQLSELECT Statements in PL/SQL

Retrieve data from the database with SELECT.

Syntax

Retrieve data from the database with SELECT.Retrieve data from the database with SELECT.

SyntaxSyntax

SELECT select_list

INTO {variable_name[, variable_name]...

| record_name}

FROM table

WHERE condition;

SELECT select_list

INTO {variable_name[, variable_name]...

| record_name}

FROM table

WHERE condition;



SELECT Statements in PL/SQLSELECT Statements in PL/SQL

INTO clause is required.

Example

INTO clause is required.INTO clause is required.

ExampleExample

DECLAREv_deptno NUMBER(2);v_loc VARCHAR2(15);

BEGINSELECT deptno, loc

INTO v_deptno, v_locFROM deptWHERE dname = 'SALES';

...END;

DECLAREv_deptno NUMBER(2);v_loc VARCHAR2(15);

BEGINSELECT deptno, loc

INTO v_deptno, v_locFROM deptWHERE dname = 'SALES';

...END;


Retrieving Data in PL/SQLRetrieving Data in PL/SQL

Retrieve the order date and the ship date for the specified order.

Example

Retrieve the order date and the ship date for Retrieve the order date and the ship date for

the specified order.the specified order.

ExampleExample

DECLAREv_orderdate ord.orderdate%TYPE;v_shipdate ord.shipdate%TYPE;

BEGINSELECT orderdate, shipdate

INTO v_orderdate, v_shipdateFROM ordWHERE id = 157;

...END;

DECLAREv_orderdate ord.orderdate%TYPE;v_shipdate ord.shipdate%TYPE;

BEGINSELECT orderdate, shipdate

INTO v_orderdate, v_shipdateFROM ordWHERE id = 157;

...END;


Retrieving Data in PL/SQLRetrieving Data in PL/SQL

Return the sum of the salaries for all employees in the specified department.

Example

Return the sum of the salaries for all Return the sum of the salaries for all

employees in the specified department.employees in the specified department.

ExampleExample

DECLARE v_sum_sal emp.sal%TYPE; v_deptno NUMBER NOT NULL := 10;

BEGINSELECT SUM(sal) -- group function

INTO v_sum_salFROM empWHERE deptno = v_deptno;

END;

DECLARE v_sum_sal emp.sal%TYPE; v_deptno NUMBER NOT NULL := 10;

BEGINSELECT SUM(sal) -- group function

INTO v_sum_salFROM empWHERE deptno = v_deptno;

END;


INSERT

UPDATE

DELETE

Manipulating Data Using PL/SQLManipulating Data Using PL/SQL

Make changes to database tables by using DML commands:

• INSERT

• UPDATE

• DELETE

Make changes to database tables by using Make changes to database tables by using DML commands:DML commands:

• INSERT

• UPDATE

• DELETE



Inserting DataInserting Data

Add new employee information to the emptable.

Example

Add new employee information to the Add new employee information to the empemp

table.table.

ExampleExample

DECLAREv_empno emp.empno%TYPE;

BEGINSELECT empno_sequence.NEXTVAL

INTO v_empnoFROM dual;

INSERT INTO emp(empno, ename, job, deptno)VALUES(v_empno, 'HARDING', 'CLERK', 10);

END;

DECLAREv_empno emp.empno%TYPE;

BEGINSELECT empno_sequence.NEXTVAL

INTO v_empnoFROM dual;

INSERT INTO emp(empno, ename, job, deptno)VALUES(v_empno, 'HARDING', 'CLERK', 10);

END;


Updating DataUpdating Data

Increase the salary of all employees in the emp table who are Analysts.

Example

Increase the salary of all employees in the Increase the salary of all employees in the

empemp table who are Analysts.table who are Analysts.

ExampleExample

DECLAREv_sal_increase emp.sal%TYPE := 2000;

BEGINUPDATE emp

SET sal = sal + v_sal_increaseWHERE job = 'ANALYST';

END;


Deleting DataDeleting Data

Delete rows that have belong to department 10 from the emp table.

Example

Delete rows that have belong to department Delete rows that have belong to department

10 from the 10 from the empemp table.table.

ExampleExample

DECLAREv_deptno emp.deptno%TYPE := 10;

BEGINDELETE FROM emp

WHERE deptno = v_deptno;END;

DECLAREv_deptno emp.deptno%TYPE := 10;

BEGINDELETE FROM emp

WHERE deptno = v_deptno;END;


Naming ConventionsNaming Conventions

• Use a naming convention to avoid ambiguity in the WHERE clause.

• Database columns and identifiers should have distinct names.

• Syntax errors can arise because PL/SQL checks the database first for a column in the table.

• Use a naming convention to avoid ambiguity in the WHERE clause.

• Database columns and identifiers should have distinct names.

• Syntax errors can arise because PL/SQL checks the database first for a column in the table.



Naming ConventionsNaming Conventions

DECLARE

order_date ord.orderdate%TYPE;ship_date ord.shipdate%TYPE;

v_date DATE := SYSDATE;BEGIN

SELECT orderdate, shipdateINTO order_date, ship_dateFROM ordWHERE shipdate = v_date;

END;SQL> /DECLARE

*

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 6

DECLARE

order_date ord.orderdate%TYPE;ship_date ord.shipdate%TYPE;

v_date DATE := SYSDATE;BEGIN

SELECT orderdate, shipdateINTO order_date, ship_dateFROM ordWHERE shipdate = v_date;

END;SQL> /DECLARE

*

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 6


COMMIT and ROLLBACK

Statements

COMMIT and ROLLBACK

Statements

• Initiate a transaction with the first DML command to follow a COMMIT or ROLLBACK.

• Use COMMIT and ROLLBACK SQL statements to terminate a transaction explicitly.

• Initiate a transaction with the first DML command to follow a COMMIT or ROLLBACK.

• Use COMMIT and ROLLBACK SQL statements to terminate a transaction explicitly.


SQL CursorSQL Cursor

• A cursor is a private SQL work area.

• There are two types of cursors:

– Implicit cursors

– Explicit cursors

• The Oracle Server uses implicit cursors to parse and execute your SQL statements.

• Explicit cursors are explicitly declared by the programmer.

• A cursor is a private SQL work area.

• There are two types of cursors:

– Implicit cursors

– Explicit cursors

• The Oracle Server uses implicit cursors to parse and execute your SQL statements.

• Explicit cursors are explicitly declared by the programmer.


SQL Cursor AttributesSQL Cursor Attributes

Using SQL cursor attributes, you can test the outcome of your SQL statements.

Using SQL cursor attributes, you can test the Using SQL cursor attributes, you can test the

outcome of your SQL statements. outcome of your SQL statements.

SQL%ROWCOUNT Number of rows affected by the most recent SQL statement (an

integer value).

SQL%FOUND Boolean attribute that evaluates to TRUE if the most recent SQL

statement affects one or more rows.

SQL%NOTFOUND Boolean attribute that evaluates to TRUE if the most recent SQL

statement does not affect any rows.

SQL%ISOPEN Always evaluates to FALSE because PL/SQL closes implicit cursors

immediately after they are executed.



SQL Cursor AttributesSQL Cursor Attributes

Delete rows that have the specified order number from the ITEM table. Print the number of rows deleted.

Example

Delete rows that have the specified order Delete rows that have the specified order

number from the ITEM table. Print the number number from the ITEM table. Print the number of rows deleted.of rows deleted.

ExampleExample

VARIABLE rows_deletedDECLARE

v_ordid NUMBER := 605;BEGIN

DELETE FROM itemWHERE ordid = v_ordid;

rows_deleted := SQL%ROWCOUNT||' rows deleted.');

END;PRINT rows_deleted


SummarySummary

• Embed SQL in the PL/SQL block:

– SELECT, INSERT, UPDATE, DELETE.

• Embed transaction control statements in a PL/SQL block:

– COMMIT, ROLLBACK, SAVEPOINT.

• Embed SQL in the PL/SQL block:

– SELECT, INSERT, UPDATE, DELETE.

• Embed transaction control statements in a PL/SQL block:

– COMMIT, ROLLBACK, SAVEPOINT.


SummarySummary

• There are two cursor types: implicit and explicit.

• Implicit cursor attributes verify the outcome of DML statements:

– SQL%ROWCOUNT

– SQL%FOUND

– SQL%NOTFOUND

– SQL%ISOPEN

• Explicit cursors are defined by the user.

• There are two cursor types: implicit and explicit.

• Implicit cursor attributes verify the outcome of DML statements:

– SQL%ROWCOUNT

– SQL%FOUND

– SQL%NOTFOUND

– SQL%ISOPEN

• Explicit cursors are defined by the user.


Practice OverviewPractice Overview

• Creating a PL/SQL block to select data from a table

• Creating a PL/SQL block to insert data into a table

• Creating a PL/SQL block to update data in a table

• Creating a PL/SQL block to delete a record from a table

• Creating a PL/SQL block to select data from a table

• Creating a PL/SQL block to insert data into a table

• Creating a PL/SQL block to update data in a table

• Creating a PL/SQL block to delete a record from a table

Ora-Accesul La Date PL-SQL

Documents

Transcript of Ora-Accesul La Date PL-SQL