Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to execute select statements in PL/SQL blocks". In daily operation, I believe many people have doubts about how to execute select statements in PL/SQL blocks. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to execute select statements in PL/SQL blocks". Next, please follow the editor to study!

In PL/SQL block, "END;" means to end a PL/SQL block, but it does not end a transaction. A block can span multiple transactions, and a transaction can also span multiple blocks. DDL and DCL statements are not supported in PL/SQL blocks, such as building tables, giving users permissions, and so on.

One: execute the select statement in the PL/SQL block

The syntax structure of the select statement in the PL/SQL block is as follows

SELECT select_list

INTO {variable_name [, variable_name]...

| | record_name} |

FROM table

[WHERE condition]

Example:

SQL > set serveroutput on

SQL > DECLARE

2 v_first_name hr.employees.first_name%TYPE

3 v_last_name hr.employees.last_name%TYPE

4 BEGIN

5 SELECT first_name,last_name INTO

6 v_first_name,v_last_name FROM hr.employees

7 WHERE employee_id=100

8 DBMS_OUTPUT.PUT_LINE (v_first_name | |''| | v_last_name)

9 * END

SQL > /

Steven King

PL/SQL procedure successfully completed

two。 Execute DML statements and control transactions in a PL/SQL block

You can insert data in PL/SQL blocks using SQL functions, custom sequence numbers, and field default values.

SQL > create table t14703a (id number (5), constraint pk_t14703a primary key (id))

2 location varchar2 (30) default 'fuzhou',salary number (8)

Table created.

SQL > create sequence user_seq

2 increment by 1

3 start with 1

4 maxvalue 99999

5 nocache

6 * nocycle

Sequence created.

SQL > select user_seq.nextval from dual

NEXTVAL

-

one

SQL > BEGIN

2 INSERT INTO hr.t14703a

3 (id,location,salary) VALUES

4 (user_seq.nextval,default,'6000')

5 COMMIT

6 * END

SQL > /

PL/SQL procedure successfully completed.

SQL > select * from hr.t14703a

ID LOCATION SALARY

3 fuzhou 6000

SQL > DECLARE

2 v_salary_increase hr.t14703a.salary%TYPE: = 500

3 BEGIN

4 UPDATE hr.t14703a SET salarysalary= salary + v_salary_increase

5 WHERE id=3

6 * END

7 /

PL/SQL procedure successfully completed.

SQL > select * from hr.t14703a

ID LOCATION SALARY

3 fuzhou 6500

three。 Use implicit cursors to define the output of DML statements in PL/SQL blocks

SQL cursors, which are private SQL workspaces, have implicit and explicit cursors; oracle server uses implicit cursors to execute SQL statements

A cursor is a private SQL work area.

There are two types of cursors:

-Implicit cursors

-Explicit cursors

The server uses implicit cursors to parse and execute your SQL statements.

Explicit cursors are explicitly declared by the programmer.

Property SQL%COUNT,SQL%FOUND,SQL%NOTFOUND,SQL%ISOPEN of four cursors

SQL > VAR rownums varchar2 (30)

SQL > set serveroutput on

SQL > DECLARE

2 Vetera hr.t04310_a.a%TYPE: = 50000

3 BEGIN

4 delete from hr.t04310_a where a < Ventra

5: rownums: = (SQL%ROWCOUNT | |'| | 'row deleted')

6 DBMS_OUTPUT.PUT_LINE (: rownums)

7 * END

8 /

40000 row deleted

PL/SQL procedure successfully completed.

At this point, the study on "how to execute select statements in a PL/SQL block" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.