这篇文章主要介绍“在PL/SQL块中怎么执行select语句”，在日常操作中，相信很多人在在PL/SQL块中怎么执行select语句问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”在PL/SQL块中怎么执行select语句”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

在PL/SQL块中"END；"表示结束PL/SQL块，但并没有结束事务，一个块可以跨越多个事务，一个事务也可以跨越多个块；PL/SQL块中不支持DDL和DCL语句，例如建表，赋予用户权限等.

一：在PL/SQL块中执行select语句

select语句在PL/SQL块中的语法结构如下

SELECT select_list

INTO {variable_name[, variable_name]...

| record_name}

FROM table

[WHERE condition];

范例：

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

二.在PL/SQL块中执行DML语句和控制事务

可以在PL/SQL块中使用SQL的函数，自定义的序列号，字段的默认值插入数据

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

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

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

----------

1

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

三.使用隐式游标定义PL/SQL块中DML语句的输出

SQL游标，游标是私有的SQL工作区，有隐式和显式游标两个类型；oracle server使用隐式的游标来执行SQL语句

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.

4种游标的属性SQL%COUNT,SQL%FOUND,SQL%NOTFOUND,SQL%ISOPEN

SQL> VAR rownums varchar2(30)

SQL> set serveroutput on

SQL> DECLARE

2 v_a hr.t04310_a.a%TYPE := 50000;

3 BEGIN

4 delete from hr.t04310_a where a < v_a;

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

6 DBMS_OUTPUT.PUT_LINE(:rownums);

7* END;

8 /

40000 row deleted

PL/SQL procedure successfully completed.

到此，关于“在PL/SQL块中怎么执行select语句”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！