Shulou(Shulou.com)06/01 Report--

The main types of SQL statements that can be used in PL/SQL are as follows:

SELECT query statement, DML statement, Transaction transaction statement and cursor properties. This article will introduce the usage of these statements in PL/SQL one by one.

1. Query statement-SELECT

The SELECT statement is used to query one or more statements. Although SELECT statements are also DML statements, SELECT is read-only, so it is listed separately.

The format for using SELECT statements in PL/SQL is as follows:

SELECT select_list

INTO {variable_name [, variable_name]...

| | record_name} |

FROM table

[WHERE condition]

}

Here, INTO is a required option, which refers to importing the query results into variables, so the number, data type and order of variables followed by INTO must be the same as the number of options, data types and order followed by SELECT.

If you use the variable type record, you can import it all at once without having to specify it one by one (more on this later).

To use INTO, the query result can only have one (row) record, otherwise confusion will occur. Let's look at this example:

SQL > edit

DECLARE

-- v_fname VARCHAR2 (25); v_fname employees.first_name%TYPE;BEGIN SELECT first_name INTO v_fname FROM employees WHERE employee_id = 200; DBMS_OUTPUT.PUT_LINE ('First Name is:' | | v_fname); END;/

SQL > /

FirstName is: JenniferPL/SQLprocedure successfully completed.

What happens if the employee_id you want to query now is greater than or equal to 200?

SQL > edit

DECLARE v_fname employees.first_name%TYPE;BEGIN SELECT first_name INTO v_fname FROM employees WHERE employee_id > = 200; DBMS_OUTPUT.PUT_LINE ('First Name is:' | | v_fname); END;/

SQL > /

DECLARE*ERRORat line 1:ORA-01422:exact fetch returns more than requested number of rowsORA-06512:at line 5Mel-wrong report, indicating that there is more than 1 query result.

Now let's look at an example of a query with multiple columns (note that it is still a row of results):

SQL > edit

DECLARE vastly emptied employees. Hiredates% type; v_emp_salary employees.salary%TYPE;BEGIN SELECT hire_date, salary INTO v_emp_hiredate, v_emp_salary-two columns of records are queried. It should be noted that the number, type and order of data must correspond to FROM employees WHERE employee_id = 100 one by one. DBMS_OUTPUT.PUT_LINE ('Hire date is:' | | v_emp_hiredate); DBMS_OUTPUT.PUT_LINE ('Salary is:' | | v_emp_salary); END;/

SQL > /

Hiredate is: 17-JUN-03Salaryis: 24000PL/SQLprocedure successfully completed.

In addition to the basic query functions, some aggregate functions can also be used directly in PL/SQL, as long as the running result of these aggregate functions is still one-line record:

SQL > edit

DECLARE v_sum_sal NUMBER (10jue 2); v_deptno NUMBER NOT NULL: = 60 BEGIN SELECT SUM (salary)-- group function-- using the aggregate function SUM () in SQL-- the result of SUM () is still a line of INTO v_sum_sal-- imports the running result of the SUM () function into a variable FROM employees WHERE department_id = v_deptno in v_sum_sal DBMS_OUTPUT.PUT_LINE ('The sum of salaryis' | | v_sum_sal); END

SQL > /

Thesum of salary is 28800PL/SQLprocedure successfully completed.

If you want to query multiple records and import variables, you need to involve the concept of cursors, which will be explained in more detail later.

Second, use DML statements in PL/SQL

PL/SQL can also use DML statements to modify tables in the database. DML statements mainly refer to statements such as INSERT,UPDATE,DELETE and MERGE, in which INSERT,UPDATE,DELETE mostly involves only one table, while MERGE involves two tables. If the data of the two tables are inconsistent, use one table to update the other.

Let's take a look at the example used by INSERT in the DML statement:

SQL > edit

BEGIN INSERT INTO employees (employee_id, first_name,last_name,email, hire_date,job_id,salary) VALUES (employees_seq.NEXTVAL,'Ruth','Cores', 'RCORES',CURRENT_DATE,'AD_ASST',4000); END;/

SQL > /

PL/SQLprocedure successfully completed.

SQL > select * from employees wherefirst_name like 'Ruth'

EMPLOYEE_ID FIRST_NAME LAST_NAME---EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY- -COMMISSION_PCTMANAGER_ID DEPARTMENT_ID-----207Ruth CoresRCORES 14-AUG-14 AD_ASST 4000

Let's look at another example of UPDATE:

SQL > edit

DECLARE sal_increase employees.salary%TYPE: = 800th begin UPDATE employees SET salary = salary + sal_increase WHERE job_id = 'ST_CLERK';END;/

SQL > /

PL/SQLprocedure successfully completed.

The INTO keyword can also be used in the UPDATE statement, as shown in the following example:

SQL > edit

DECLARE myname emp.last_name%TYPE; mysal emp.salary%TYPE;BEGIN FOR rec IN (SELECT * FROM emp) LOOP-- does not need to understand the meaning of loop loop here. Later, we will explain the use of loop in detail: UPDATE emp SET salary = salary * 1.5 WHERE employee_id = rec.employee_id RETURNING salary, last_name INTO mysal, myname. -- use INTO in the UPDATE statement to import the updated values into variables DBMS_OUTPUT.PUT_LINE ('New salaryfor' | | myname | |'='| | mysal); END LOOP;END;/

SQL > /

Newsalary for OConnell = 3900Newsalary for Dellinger = 5100Newsalary for Cabrio = 4500Newsalary for McCain = 4800Newsalary for Jones = 4200Newsalary for Walsh = 4650Newsalary for Feeney = 4500PL/SQLprocedure successfully completed.

Using DELETE in PL/SQL is also simple:

SQL > select * from tt

-- first use SQL statements to query all the information ID- 0 1

SQL > edit

DECLARE v_id tt.id%TYPE: = 1 ~ begin DELETE FROM tt WHERE id = vested _

SQL > /

PL/SQL procedure successfully completed.

SQL > select * from tt

ID- 0Mel-one record has been deleted

INSERT, UPDATE,DELETE statements can basically be used directly in PL/SQL, no different from SQL language. MERGE is rarely used, so let's give an example to briefly introduce it:

1. First create the table ttt and insert three records

SQL > create table ttt (t_id int

2 t_name varchar2 (20); Tablecreated.

SQL > insert into ttt values (0Ma'a')

1row created.

SQL > insert into ttt values (1)

1row created.

SQL > insert into ttt values (2)

1row created.

SQL > select * from ttt

T_ID name-0 a 1 b 2 c

two。 Create table T2 again and insert a record

SQL > create table T2 (t2_id int

2 t2_name varchar2 (20))

Table created.

SQL > insert into T2 values (0mcmpd')

1row created.

SQL > insert into T2 values (3meme')

1row created.

SQL > select * from T2

T2_ID T2NAMEML-0d 3e

3. Now use the MERGE method in PL/SQL to update the data of ttt with T2 data

SQL > edit

BEGINMERGEINTO ttt USING T2 ON (ttt.t_id = t2.t2_id) WHEN MATCHED THEN UPDATE SET ttt.t_name = t2.t2_name WHEN NOT MATCHED THEN INSERT VALUES (t2.t2_id, t2.t2_name); END;/

SQL > select * from ttt

-- Table ttt has been updated with T2 merge update T_ID titled name muri-0 d 1 b 2 c 3 e

When DML statements operate on a database, you can also use Records to insert or update a set of data at the same time, such as the following two examples:

DECLARE my_book books%ROWTYPE;BEGIN my_book.isbn: ='1-56592-335-9; my_book.title: = 'Oracle PL/SQLProgramming 5threading; my_book.summary: =' General userguide'; my_book.author: = 'Feusertein, Steven'; my_book.page_count: = 1000; INSERT INTO books VALUES my_book;END;/DECLARE my_book books%ROWTYPE BEGIN my_book.isbn: ='1-56592-335-9; my_book.title: = 'Oracle PL/SQLProgramming 5threading; my_book.summary: =' General userguide'; my_book.author: = 'Feusertein, Steven'; my_book.page_count: = 1000; UPDATE books SET ROW = my_book WHERE isbin = my_book.isbn;END;/

In both cases, each record is saved in a variable of type Record, my_book, which is then inserted or updated into the table as a whole.

The variable type of Records will be explained in more detail later, and here is only a brief introduction to its basic usage.

DDL statements such as INDEX cannot be used directly and must be used in the form of dynamic SQL statements.

Third, thing control sentence (Transaction control)

Object control statements mainly refer to commit, rollback and savepoint.

There is no one-to-one correspondence between Block of PL/SQL and Transaction, it is possible that a block of PL/SQL has ended (for example, if an exception occurs, exit the program), but Tansaction has not been committed or rolled back, it will lead to data loss. (more on this later) on the other hand, a Transaction may be over, while the block of PL/SQL is still going on. A Transaction can span multiple Block, as in the examples of INSERT and UPDATE above, although the table has been modified, it is not submitted using commit, so Transaction continues.

Now let's look at the following example:

1. First log in as the SYS user:

SQL > show user

USERis "SYS"

SQL > desc v$transaction

Name Null? Type-ADDR RAW (4) XIDUSN NUMBER XIDSLOT NUMBER XIDSQN NUMBER XID RAW (8)

SQL > select xid,xidusn from v$transaction

Norows selected-- currently has no record

two。 Now switch to hr user

SQL > show user

USERis "HR"

SQL > create table tt (id int)

Tablecreated.

SQL > insert into tt values (0)

1row created.

3. Now switch to the SYS user again to view the record of the xid line

SQL > show user

USER is "SYS"

SQL > select xid,xidusn fromv$transaction

XID XIDUSN---040001009B020000 4

4. Now switch back to the hr user and submit what you just did

SQL > show user

USERis "HR"

SQL > commit

Commitcomplete.

SQL > select * from tt

ID- 0

5. At this point, if you look at the dynamic performance attempt (v$transaction) as a SYS user, you will find that the previous record has disappeared:

SQL > show user

USERis "SYS"

SQL > select xid,xidusn fromv$transaction

No rows selected

Fifth, the properties of cursors

1. What is a cursor

If you want to have an in-depth understanding of cursor Cursor, you must have a deep understanding of the architecture of Oracle, and this chapter provides only a basic overview.

Cursor is a pointer to a piece of private memory allocated by the Oracle server to process SQL statements. Usually this private memory area is in the PGA of the user session. If PGA is Dedicated, then this PGA is independent of SGA; if it is shared server mode, then the cursor is placed in SGA (future articles will introduce the related concepts of PGA,SGA). By holding a cursor, you can access the area of memory that the cursor points to. For more knowledge about Cursor, please refer to the knowledge about OCI in Oracle.

As mentioned earlier, using the SELECT INTO statement in PL/SQL, you can put a query result into a variable, while using CURSOR you can store multiple records.

Cursors include implicit cursors and explicit cursors. Implicit cursors are created and managed by Oracle. Implicit cursors are created and managed by Oracle server, while explicit cursors need programmers to declare, open, execute and close cursors through a series of system calls. Explicit cursors will be explained in detail later, and only implicit cursors are involved at present.

two。 The role of cursor attributes

When we use UPDATE and DELETE (not needed by INSERT) data, we want to know how many records are affected

Use cursor properties to query relevant information. These mainly include the following three categories:

A. SQL%FOUND: if a Boolean variable TRUE is returned, at least one record is affected.

B. SQL%NOTFOUND: if a Boolean variable TRUE is returned, none of the records are affected.

C. SQL%ROWCOUNT: the number returned indicates how many records have been affected.

Let's look at an example of using cursors:

SQL > select * from ttt

T_ID name-0 a 1 b 2 c

SQL > edit

DECLARE test_id ttt.t_id%TYPE: = 1 * begin DELETE FROM ttt WHERE t_id = test_id; COMMIT; DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT | | 'rows deleted'); END;/

SQL > /

1 rows deleted! PL/SQL procedure successfullycompleted.

SQL > select * from ttt

T_ID name-0a 2c

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.