Oracle 11g pl/sql学习笔记（一）

Oracle 11g pl/sql学习笔记

示例数据库

HR schema

重要命令

sqlplus /nolog

conn /as sysdba

desc dba_users

select username,account_status from dba_users order by username;

conn hr/hr

desc employees
desc departments

一、PL/SQL基础

1.sql没有过程性控制

过程控制 + SQL = PL/SQL

IF ... THEN ... ELSE ... END IF; LOOP ... END LOOP; + SQL = PL/SQL


2.pl/sql优点：含有存储过程的sql，提高了性能，节省了网络传输

缺点：代码与oracle绑定了

3.PL/SQL 块结构

DECLARE

BEGIN

EXCEPTION

END;


3.1 Anonymous,匿名块，即时销毁，适合测试

[DECLARE]

BEGIN

[EXCEPTION]

END;

3.2 Procedure

PROCEDURE name
IS

BEGIN

[EXCEPTION]

END;

3.3 Function

FUNCTION name
RETURN datatype
IS
BEGIN

RETURN value;
[EXCEPTION]

END;