PLSQL--基础概念

用户空间字典

查询当前用户下所有视图
select * from user_views;

查询当前用户下所有的表
select * from user_tables;

表操作

-- 添加表字段
alter table emp add addr varchar2(20);
-- 修改表字段
alter table emp modify addr number(5);
--删除字段
alter table emp drop column addr;

权限相关

sqlplus命令行连接数据库
sqlplus scott/tiger@orcl as sysdba; (管理员身份登录)
sqlplus scott/tiger@orcl; (普通用户身份登录)

命令行连接, 以管理员身份连接
SQL> conn scott/tiger as sysdba; 

以普通用户连接
SQL> conn scott/tiger@orcl

第一次创建视图需要授予权限(必须sysdba身份登录)
grant create view to scott;

在SQL命令行连接中，直接执行sql文件
SQL> @D:\视图.sql

视图相关

概念：
封装或存储一张表或者多张表的连接，子查询，分组等集合操作符等等的结果结合

作用：
方便查询，屏蔽基表数据，防止任意篡改，安全

特点： 
本身是虚拟的表，不占用内存，不存储数据，视图的查询结果来自于基表

只读视图：
在视图子查询后 + with read only

检查约束视图：
在视图子查询后 + with check option 
更新前提： 必须满足视图子查询where条件，才可以更新

物化视图:
待补充

内联视图：
in-line view
内联视图更新
update (
	select t1.f1, t2.f2
	from t1, t2
	where t1.f1=t2.f1
) set t1.f1 = t2.f2

内联视图添加数据
insert into 
(select f1, f2,... from table where ...) 
values(v1, v2, ...); 

例子：
-- 可以插入 
insert into 
(select empno, ename, sal, deptno
 from emp where sal > 2000 and deptno=20) 
values(1001， '张三', 1000, 10); 

-- 有with check option， 无法可以插入 
insert into 
(select empno, ename, sal, deptno
 from emp where sal > 2000 and deptno=20 with check option) 
values(1001， '张三', 1000, 10); 

视图的常用操作



创建视图
create or replace view viewName
as
select * from emp;

删除视图
drop view viewName;



更新视图
视图可以更新数据吗（insert, delete, update）？ 
1. 简单条件创建的视图可以 (基表也会变化)
2. 复杂sql创建的视图不可以更新（distinct, 分组函数，group by, rownum）
3. 表连接和关联查询可以更新部分（字段来自同一个表的时候）

--细节
1.创建视图时，当查询中出现重复列名时候，需要起别名进行区分，否则创建失败
2.分组函数必须取别名 count(*) c 

PLSQL开发

基本类型 || 简单变量

包括三个大类：
字符， 数值， 日期

数值
binary_integer 整型数字
number[(precision, scale)] 数值类型

字符
char[(maximum_length)]		定长字符类型
varchar2(maximum_length)	变长字符类型
long						长字符类型
long raw					长二进制类型

日期
date		日期类型

大对象类型large object 
clob		字符大对象						
blob		二进制大对象
bfile		文件大对象

布尔类型
boolean		布尔类型：true,false, null


简单变量声明
v_gender		char(1);
v_count			binary_integer			:=	0;
v_total_sal		number(9,2)				:= 	0;
v_order_date	date					:=	sysdate + 7;
c_tax_rate		constant number(3,2)	:=	8.25;
v_valid			boolean not null		:= true;

注意number类型的使用：
参考：https://blog.csdn.net/fsfsdfsdw/article/details/83823887
NUMBER(p,s)   P 和S 可选

    其中precision表示数字的总长度，scale代表可以有几位小数。
    precision也叫精度，是指数中的总数字个数，默认情况下，精度为38 位，取值范围是1～38 之间。
    scale是小数位数，即数中小数点右边的数字个数。其范围从-84到127，能够决定舍入规则。如果我们不指定scale的值，默认就为0。
    不可以用常量或变量指定NUMBER的长度和精度。NUMBER类型最大的长度是38位。
    如果不指定NUMBER类型的最大长度，就会采用默认长度或是使用系统所支持的最大长度。
    精度和小数位数不会影响数据在磁盘上如何存储，而只会影响允许有哪些值以及数值如何舍入（round）。

    例如，数 123.45 的精度是 5，小数位数是 2。
下面对p和s进行分析
    p>0，对s分2种情况分析：
  
  a. s>0
    精确到小数点右边s位，并四舍五入。然后检验有效数位是否<=p；如果s>p，小数点右边至少有s-p个0填充。
  	0.123 number(5,6) 
  	
  b. s<0
    精确到小数点左边s位，并四舍五入。然后检验有效数位是否<=p+|s|
  
    (有效数位：从左边第一个不为0的数算起)
  
    对于浮点数则不考虑精度问题
  
  c、表示整数
    当s的值被省略时，即等同于s等于0，表示整数
  
    NUMBER(p) 等同于NUMBER(p,0)
  
  c、浮点型
    当p和s都被省略，则当前可表示的数据为浮点型，可以存储正负数、零值、浮点数等
    示例：
    Value       Datatype       Stored Value
    123.2564    NUMBER         123.2564
    1234.9876   NUMBER(6,2)    1234.99              
    12345.12345 NUMBER(6,2)    Error                
    1234.9876   NUMBER(6)      1235                 
    12345.345   NUMBER(5,-2)   12300                
    1234567     NUMBER(5,-2)   1234600              
    12345678    NUMBER(5,-2)   Error                
    123456789   NUMBER(5,-4)   123460000            
    1234567890  NUMBER(5,-4)   Error                
    12345.58    NUMBER(*, 1)   12345.6              
    0.1         NUMBER(4,5)    Error                
    0.01234567  NUMBER(4,5)    0.01235              
    0.0999      NUMBER(4,5)    0.09990 (待验证？)           
    0.09999     NUMBER(4,5)    0.09999              
    0.099996    NUMBER(4,5)    Error

基础用法

在Command Window中执行
在命令后面加斜杠 '/'

开启命令行输出
set serveroutput on;


declare
	v_total_sal number(9, 2) := 0; -- pl/sql赋值语句
	c_tax_rate constant number(3,2) := 1.25;  --常量只能被赋值一次
	v_gender char(1);
	v_valid boolean not null := true;
	v_b boolean;
	v_num1 number(2) := 10;
	v_num2 number(2) :=10;
begin
	dbms_output.put_line('v_tatal_sal='|| v_total_sal);
	
	if( v_num1 = v_num2 ) then 
		dbms_output.put_line('v_num1  == v_num2 ');
	end if;

	v_b := (v_num1 = v_num2);
	if(v_b) then 
		dbms_output.put_line('ok');
	else
		dbms_output.put_line('not ok');
	end if;
end; 

复合类型 || 复合变量

复合变量也叫做组合变量。在复合变量中包含多个内部的组件，每个组件都可以单独存放值。一个复合变量可以存放多个值。
与简单变量类型不同，复合变量类型不是数据库中已经存在的数据类型，所以复合变量在声明类型之前，首先要创建使用到的复合类型，然后将变量声明为复合变量。

复合数据类型：
PL/SQL TABLES 表类型 (相当于数组)
PL/SQL RECORDS 记录类型（相当于对象数组||表记录）
复合类型被创建后，可以被使用多次定义多个变量

声明一个PL/SQL TABLE类型
type type_name is table of scalar_datatype
	[not null] index by binary_integer;

identifier		type_name;	

声明一个PL/SQL RECORD类型
type type_name is RECORD
(
	field_name1 field_type [not null {:=|default} expr],
	field_name2 field_type [not null {:=|default} expr]
);
identifier		type_name;

使用%type来声明变量
除了可以使用已经确定的类型来声明变量之外，还可以使用%type，%rowtype来作为变量的类型
declare
	v_empno		number(4);
	v_deptno	v_empno%type;
	v_sal		emp.sal%type;
	v_hiredate	emp.hiredate%type;
	
	v_d1		date;
	v_d2		v_d1%type;
begin
	v_empno  	:= 1001;
	v_deptno	:= 1002;
	dbms_output.put_line(v_empno || v_deptno);

	v_d1		:= '12-8月-2000';
	v_d2		:= '14-8月-2001';
	dbms_output.put_line(v_d1 || v_d2);

end;

使用%rowtype来声明变量
declare
	v_emp		dept%rowtype;
	v_emp2		v_emp%rowtype;
begin
	v_emp.deptno		:=	30;
	v_emp.dname			:=	'开发部';
	v_emp.loc			:=	'北京';
	
	dbms_output.put_line(v_emp.deptno || v_emp.dname || v_emp.loc);

end;

--表类型的例子
declare
	type nameed_table_type is table of varchar2(6) index by binary_integer;
	v_table1 nameed_table_type;
begin
	-- 用下标表示， 没有长度限制
	v_table1(1)	:=	'Hello1';
	v_table1(2)	:=	'Hello2';
	v_table1(3)	:=	'工程师';
	dbms_output.put_line(v_table1(1) || v_table1(2) || v_table1(3));
end;
--记录类型的例子
declare
	type nameed_record_type is record (
		empno		number(4),
		ename		varchar2(20),
		job			varchar2(20),
		hgr			number(4),
		hiredate	date,
		sal			number(8,2),
		comm		number(8,2),
		deptno		number(4)
	);
	v_rec nameed_record_type;
begin
	v_rec.empno		:= 7936;
	v_rec.ename		:= '张三';
	v_rec.job		:= '软件工程师';
	v_rec.mgr		:= 7800;
	v_rec.hiredate	:= to_date('2000-1-12', 'YYYY-MM-DD');
	v_rec.sal		:= 10000;
	v_rec.comm		:= 1000;
	v_rec.deptno	:= 1001;
	
	dbms_output.put_line(v_rec.empno || to_char(v_rec.hiredate, 'YYYY-MM-DD'));
end;