PLSQL基础

PL/SQL
declare
声名部分;–声名变量，常量，类型等
begin
代码块 --sql语句，流程控制语句
exception
异常处理部分; --记录错误日志
end;

变量和常量：
变量和常量都是用来保存数据的
语法：
变量名 数据类型[:=初始值];
变量可以在程序中修改它存储的值

常量：不可以修改，在声名时必须包含常量名
语法：
常量名 constant 数据类型:=常量值;

标识符命名规范：
(1)对象名以字母开头，可以有数字，_,#,$等特殊字符
(2)长度不能超过30个英文字符
(3)不能以数字开头
(4)避免使用oracle的关键字

dbms_output.put(字符串); —在控制台输出一个字符串内容，输出完之后不换行，但是不换行不输出
dbms_output.put_line(字符串); --在控制台输出一个字符串内容，输出之后换行
“:=”:赋值符号
dbms_output.new_line(); --换行
declare
–声名一个变量,并给变量一个初始值 ‘PLSQL’
v varchar2(30):=‘PLSQL’;
–声名一个常量
p constant number(7,6):=3.141591;
begin
/*
–能过dbms_output.put_line，输出变量v的值
dbms_output.put_line(v);
–修改变量的值
v:=‘oracle’;
dbms_output.put_line(v);

dbms_output.put_line§;
–p:=3; 常量值不可修改
*/
–输出不换行
dbms_output.put(v);–输出变量v的值
dbms_output.put(‘smith’);
dbms_output.put(’ ni hao’);
–dbms_output.put_line(’’); --使用dbms_output.put_line()打印空字符串，让其换行
dbms_output.new_line(); --换行
end;
&：输入符号
注意：
数字类型: &变量名
字符串类型: ‘&变量名’
select * from emp where deptno=&部门编号;

select * from emp where job=’&job’; --输一个aa之后相当于 job=aa

declare
–声名一个变量，接收一个键盘输入的值
v varchar2(30):=’&变量值’;
begin
–打印变量v的值
dbms_output.put_line(v);
end;

plsql中的数据类型：
基础类型：
1.数字类型：
number(长度,精度):最大长度38,最小默认1

  integer:整数类型
  pls_integer:整数类型
  binary_integer:整数类型
  
  float:浮点型(小数类型)

2.字符串类型
varchar2(长度):变长字符串类型，最大长度4000个字符（英文）
char(长度):定长字符串类型

 long:可以存放2G的内容（oracle不建议使用的类型）
 CLOB:存放大文本内容（txt文件,word文件内容），可能会在代码块中出现
 
 BLOB:二进制方式方式存储大文件（音频，视频文件）

3.日期类型
date 日期类型
timestamp: 时间戳类型

4.布尔类型
boolean:只有三个值，true,false,null

复杂类型：
5.记录类型：
record:可以存放多个值
定义类型语法：
type 类型名称 is record (
属性名 数据类型,
属性名 数据类型,
…

 );
 变量声名：
 变量名 类型名;
 记录类型的使用：
 变量名.属性名  --取一个记录类型中的属性值
 变量名.属性名:=值   --给记录类型变量的属性赋值
 declare
    --定义一个记录类型
    type  amtype is record(
       name varchar2(30),
       job varchar2(50),
       sal number(5)
    );
    --声名一个记录类型变量
    v amtype;
 begin
    v.name:='张三';
    v.job:='CLERK';
    v.sal:=1234;
    --记录类型变量不能直接打印
    dbms_output.put_line(v.name||','||v.job||','||v.sal);
 end;
 
 select into语句：
 select 列名,列名,列名,... into 变量，变量,.... from 表名 where ... group by .. order by ...
 注意：select into语句每次只能查询一条数据，不能多不能少
 declare
    --声名一个变量保存，员工的姓名
    v_ename varchar2(20);
 begin
    select ename into v_ename from emp where empno=7369; --将7369的员工编号查询到变量v_ename中
    --打印员工姓名
    dbms_output.put_line(v_ename);
 end; 
 
 declare
   v_ename varchar2(20);
 begin
   select ename into v_ename from emp; --查询出了多条数据，会报错
 end;
 declare
   v_ename varchar2(20);
 begin
   select ename into v_ename from emp where 1=0;--当查询不出数据时，依然会报错
 end;  
 
 --写一个代码查询出7369的姓名，职位和工资
 select ename,job,sal from emp where empno=7369; 
 declare
    --声名三个变量分别保存员工姓名，职位，工资
    v_ename varchar2(20);
    v_job varchar2(30);
    v_sal number(5);
 begin
    select ename,job,sal into v_ename,v_job,v_sal from emp where empno=7369;
    dbms_output.put_line(v_ename);
    dbms_output.put_line(v_job);
    dbms_output.put_line(v_sal);
 end; 
 
 --使用记录类型完成以上代码
 declare
    --定义一个记录类型
    type mtype is record(
       name varchar2(20),
       job varchar2(30),
       sal varchar2(5)
    );
    --声名一个变量
    v mtype;
 begin
    --使用select into语句查询
    select ename,job,sal into v from emp where empno=7369;
    --打印结果
    dbms_output.put_line(v.name);
    dbms_output.put_line(v.job);
    dbms_output.put_line(v.sal);
 end;
 记录类型：可以存放多个值，但都属于一条数据的 
 
 6.%type类型
   %type:取其他对象的类型，作为变量的类型
   对象名%type(类型) 
   注意：只能取单一类型
   emp.job%type --取emp表中job列的数据类型，作为一个新的类型 varchar2(9)
   declare
      --声名一个变量
      v emp.job%type;  --相当于声名一个变量 v varchar2(9);
      
      --声名一个变量
      n1 number(10);
      --声名一个变量和n1的类型完全相同的类型
      n2 n1%type; --n1%type取变量n1的类型，作为变量n2的类型
   begin
     
   end; 
7.%rowtype类型
%rowtype类型：它是记录类型和%type类型的结合;取一个对象的类型作为变量的类型

表名%rowtype;
emp%rowtype;
v emp%rowtype;
type mtype is record(
   empno emp.empno%type,
   ename emp.ename%type,
   job emp.job%type,
   mgr emp.mgr%type,
   hiredate emp.hiredate%type,
   sal emp.sal%type,
   comm emp.comm%type,
   deptno emp.deptno%type
)

–写一个代码块，输入一个员工编号，查询并打印员工的信息
select * from emp where empno=&eno;
declare
–声名一个%rowtype类型
v emp%rowtype;
begin
–查询
select * into v from emp where empno=&eno;
–打印
dbms_output.put_line(v.ename||’,’||v.job||’,’||v.mgr||’,’||v.hiredate||’,’||v.sal||’,’||v.comm||’,’||v.deptno);

end;

代码块部分：SQL语句，流程控制语句
sql语句的扫行：
1.select into语句（select语句）
2.insert,update,delete语句（可以直接在代码块中扫行）

begin
insert into dept values(50,‘dept1’,‘loc’);
end;

declare
–声名三个变量分别保存部门编号，部门名称，部门位置
v_deptno number(5):=60;
v_dname varchar2(20):=‘DEPT2’;
v_loc varchar2(20):=‘LOC2’;
begin
insert into dept values(v_deptno,v_dname,v_loc);
end;

declare
v number(4):=30;
begin
delete from emp where deptno=v;
end;
3.使用 execute immediate 执行
语法：
execute immediate sql语句(字符串类型) [into 变量] [using 值,值];

into子句和select into中的一样：用于将select查询结果保存到变量中
using子句：用于给sql语句传递参数

declare

begin
execute immediate ‘update emp set sal=sal+500’;
end;

begin
execute immediate ‘delete from emp’;
end;

select * from emp;

declare
–声名一个变量保存员工的姓名
v_ename varchar2(20);
begin
execute immediate ‘select ename from emp where empno=7369’ into v_ename;
–打印查询结果，打印变量的值
dbms_output.put_line(v_ename);
end;

ddl语句不能直接在plsql代码块中运行，但是可以放在execute immediate中运行

begin
execute immediate ‘create table ttt(id number(11),name varchar2(30))’;
end;

declare
v_empno number(11):=7369;
v_ename varchar2(30):=’&员式姓名’;
v_job varchar2(20):=’&职位’;
v_mgr emp.mgr%type:=&上级编号;
v_hiredate emp.hiredate%type:=to_date(’&入职日期’,‘yyyy-mm-dd’);
v_sal emp.sal%type:=&工资;
v_comm emp.comm%type:=&佣金;
v_deptno emp.deptno%type:=&部门编号;
begin
update emp set ename=v_ename,
job=v_job,
mgr=v_mgr,
hiredate=v_hiredate,
sal=v_sal,
comm=v_comm,
deptno=v_deptno
where empno=v_empno;
end;
select * from emp where empno=7369;
select ‘drop table ‘||object_name||’;’ from user_objects where object_type=‘TABLE’;

declare

begin
execute immediate ‘delete from emp where empno=:1’ using 7369;–delete from emp where empno=7369
end;
select * from emp;
execute immediate语句中sql点位符
:n 表示点位符 n一般情况下是一个正整数，也可以是其他字符串，
如果sql语句中包含点位符，那么必须使用using子包给点位符传值

declare

begin
–update emp set ename=‘Hello’,job=‘IT’,sal=1234 where empno=7499
execute immediate ‘update emp set ename=:1,job=:2,sal=:3 where empno=7499’ using ‘Hello’,‘IT’,1234;
end;
select * from emp where empno=7499;

declare
–声名一个变量保存sql语句
v_sql varchar2(200):=‘update emp set ename=’‘Hello’’,job=’‘IT’’,sal=1234 where empno=7499’;
begin
dbms_output.put_line(v_sql);
execute immediate v_sql;
end;

oracle字符串中包含字符串的情况，（1）使用两个单引号表示一个单引号
（2）使用chr()函数来获取一个单引号chr(39)
select chr(39) from dual;
declare
–声名一个变量保存sql语句
–v_sql varchar2(200):=‘update emp set ename=’‘Hello’’,job=’‘IT’’,sal=1234 where empno=7499’;
v_sql varchar2(200):=‘update emp set ename=’||chr(39)||‘Hello’||chr(39)||’,job=’||chr(39)||‘IT’||chr(39)||’,sal=1234 where empno=7499’;
begin
dbms_output.put_line(v_sql);
end;
update emp set ename=‘Hello’,job=‘IT’,sal=1234 where empno=7499

execute immediate和直接执行的sql语句的
(1)ddl语句不能直接在plsql代码块中运行，但是可以放在execute immediate中运行
(2)当sql语句操作的表，是使用execute immedate动态创建的那么，只能在execute immediate中运行
(3)当将表名放入变量中时，这时必须使用execute immediate语句来运行
begin
–创建一张ttt3的表
execute immediate ‘create table ttt3(id number(11),name varchar2(30))’;
–给表里添加数据
insert into ttt3 values(1,‘plsql’); —不允许运行，但是可以在execute immediate中运行
end;

–这个代码只能执行一次
begin
–创建一张ttt3的表
execute immediate ‘create table ttt3(id number(11),name varchar2(30))’;
–给表里添加数据
execute immediate ‘insert into ttt3 values(1,’‘plsql’’)’; —不允许运行，但是可以在execute immediate中运行
end;

select * from ttt3;

declare
–声名一个变量变量，保存数据库中的表名
v_tab varchar2(30):=‘EMP’;

–声名一个变量
v_sal number(5):=500;
–声名一个变量保存sql语句
v_sql varchar2(200);
begin
–update v_tab set sal=sal+v_sal; 不能运行
v_sql:=‘update ‘||v_tab||’ set sal=sal+’||v_sal;
dbms_output.put_line(v_sql);
execute immediate v_sql;
end;
update EMP set sal=sal+500