How to execute select statements in PL/SQL blocks
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!
