pl/sql(1)

sql运行环境session
alter session set
nls_date_format yyyy mm dd hh24:mi:ss
nls_language MARCH 三月
nls_territory $ ¥

锁不释放
DML锁(表级共享锁,行级排他锁)
DDL锁(DDL排他锁)
所占回滚段资源不释放(公共资源)
数据库默认事务隔离级别:read committed
除了自己,其他session看不见你的数据
同一个用户的不同session:并发问题
同一个数据库里不同用户的session

SELECT
单表查询
语法顺序 select from where group by having order by
执行顺序 from where group by having select order by
子查询 表连接 集合运算
子查询(非关联,关联,跟在from后面,select后面)
表连接(cross join inner join outer join)
集合(union,union all,intersect,minus)

数据类型

• 标量类型
  数字型,字符型,日期型,布尔型

数字类型

• NUMBER
• NUMBER的子类型dec,float,real
• binary_integer(只能用于pl/sql)

字符型

• varchar2 varchar
• string(只能用于pl/sql)
• char
• long

DATE

Boolean

• 用于存储逻辑值(true,false,null)
• 不能向数据库中插入boolean类型的数据
• 不能将列值保存到boolean变量中
• 只能对boolean变量执行逻辑操作

变量声明
语法

• var_name type [constant] [notnull] [:=value];
• PL/SQL规定没有初始化的变量为NULL

%TYPE方式

• 变量具有与数据库的表中某列或者其他变量相同的类型

declare v_d1 DATE :=sysdate;
    begin dbms_output.PUT_LINE(
        to_char(
                v_d1,'yyyy-mm-dd hh24:mi:ss'
            ));
    end;

打印当前时间.

打印boolean值

declare
    v_b1 boolean :=true;
    begin
if v_b1 then DBMS_OUTPUT.PUT_LINE('true') ;
elsif v_b1 is null then DBMS_OUTPUT.PUT_LINE('null');
else  DBMS_OUTPUT.PUT_LINE('false');
end if;
    end;

不能直接打印boolean,所以需要用到if表达式
if then elsif then else end if

DML和TCL

可以直接在pl/sql中使用的标准sql

1. dml语句
2. 事务控制语句
   insert update delete

begin
    insert into test values (1);
    commit ;
end;

begin
    execute immediate '
    insert into test values (1)';
    commit ;
end;

DDL

本地动态sql是使用execute immediate语句来实现的
ddl语句用本地动态sql实现

begin
    execute immediate 'drop table test';
    commit;
end;

编译和执行

pl/sql程序分为编译和执行阶段
sql语句分为编译和执行
sql语句的编译发生在pl/sql程序的编译阶段
sql语句的编译发生在pl/sql程序的执行阶段

LOOP循环语句

语法
exit when子句是必须的,否则循环无法停止

WHILE循环语句

• 循环语句执行的顺序是先判断<boolean expression>的真假,如果为true则循环执行,否则退出循环
• 在while循环语句中任然可以使用exit或exit when子句

FOR循环语句

• 每循环一次,循环变量自动+1;使用关键字reverse,循环变量自动减1
• 跟在in reverse后面的数字必须是从小到大的顺序,但不一定是整数,可以是能够转换为整数的变量或表达式
• 可以使用exit或者exit when子句退出循环

begin
    for i in 1..1000 loop
        insert into test values (i);
        end loop;
end;
begin
    for i in 1..1000 loop
        execute immediate 'insert into test values ('||i||')';
        end loop;
end;

打印1-1000

begin
    for i in 1..1000 loop
         DBMS_OUTPUT.PUT_LINE('i='||i);
        end loop;
end;

Select语句的实现

根据select语句返回的记录数,将select语句的实现分为两类:

• 当且仅当返回一条记录
  用select…into…语句实现
• 返回0条或多条记录
  用cursor实现

select…into

• 若查询结果是单行单列,into子句后用标量类型,与select子句后的目标数据类型一致即可
• 若查询结果是单行多列,into子句后的变量个数、顺序、每个变量的数据类型应该与select子句后的目标数据相匹配,也可以用记录类型的变量

返回一条记录时用:

declare
    v_realname account.REAL_NAME%type;
    begin
    select REAL_NAME into v_realname
    from ACCOUNT
        where id=1005;
    DBMS_OUTPUT.PUT_LINE(v_realname);
end;

返回多个:

declare
    v_realname account.REAL_NAME%type;
    v_age number(3);
    begin
    select REAL_NAME,trunc((sysdate-BIRTHDATE)/365)
    into v_realname,v_age
    from ACCOUNT
        where id=1005;
    DBMS_OUTPUT.PUT_LINE(v_realname||'''s age is '||v_age||'.');
end;

用一个变量概况多个:

declare
    type t_acc_rec is record (
    v_realname account.REAL_NAME%type,
    v_age number(3));
    t_acc t_acc_rec;
    begin
    select REAL_NAME,trunc((sysdate-BIRTHDATE)/365)
    into t_acc
    from ACCOUNT
        where id=1005;
    DBMS_OUTPUT.PUT_LINE(t_acc.v_realname||'''s age is '||t_acc.v_age||'.');
end;

%rowtype

• 用表结构或视图结构定义变量
• 当使用%rowtype定义记录变量时,record成员的名称和类型与表或视图的列名称和类型完全相同

这个是小练习:有对应id查询出对应的人

declare
    v_id number(10):=1005;
    type v_account is record (
       v1 account.REAL_NAME%type,v2 account.IDCARD_NO%type);
    v_acc v_account;
    v_cnt number;
    begin
    select count(*) into v_cnt from ACCOUNT
        where id=v_id;
    if v_cnt =0 then DBMS_OUTPUT.PUT_LINE('无人');
    else select REAL_NAME,IDCARD_NO into v_acc from ACCOUNT where
                                                       ID=v_id;
    DBMS_OUTPUT.PUT_LINE(v_acc.v1||v_acc.v2);
    end if;
end