oracle的使用sqlplush - 码农知识堂 - 文章详情页

oracle的使用sqlplush
目录

1.下载oracle 11g

2.忘记密码怎么办？

3.通过system用户登录oracle

4.创建表空间

5.创建用户，赋予表空间

6.授权，最大权限dba

7.DESC查看表结构

8.col 对表中的字段进行设置

9.length和lengthb区别

10.ltrim()—去左空格 Rtrim()—去右空格 trim()—去所有空格

11.PL/SQL

（1）语序语法

（2）常量和变量定义

12. IF... then .... Elsif ...then ... Else... End if end;

13.case

14.while

15.for

15.oracle异常

16.复合变量

17.游标

18.%type和%rowtype

19.trunc

20.日期函数

months_between

add_month(日期，数字)：获得几个月后的日期

next_date()：指定给出下次某日日期数

last_day：求出本月的最后一天

21.转换函数

22.nvl

23.Decode函数

24.oracle的分页rownum

25.日期函数

26.%TYPE说明  %ROWTYPE

27.视图

28.序列

30.循环loop for

31.存储过程

32.存储过程的调用

33.触发器

1.下载oracle 11g

2.忘记密码怎么办？

我们先通过不用登录的方式连接到oracle----sqlplus /nolog

然后通过 conn /as sysdba连接oracle数据库

（为什么可以直接不登录就连接，要是别人连了怎么办）

oracle和mysql不一样，oracle的连接是通过配置文件，我们在下载的时候，配置文件就已经绑定了我们的主机，只有我们才可以改密码。

然后通过alter改密码

alter user 用户名 identified by xxx

3.通过system用户登录oracle

conn system/root sysdba

4.创建表空间

create tablespace scott_tb_space datafile 'd:/tbspace/scott_tb_space.dbf' size 200m

5.创建用户，赋予表空间

create user scott identified by tiger default tablespace scott_tb_space;

6.授权，最大权限dba

7.DESC查看表结构

查看表的各字段及数据类型describ简写DESC

  SQL>DESC 表名                     SQL>DESC dept

8.col 对表中的字段进行设置

SQL>col deptno heading “编号”          //给字段列deptno取别名为“编号”

SQL>col dname format a10   //10个字符长度

这句设定指定列名显示宽度是每行10个字符

9.length和lengthb区别

区别：length求得是字符长度，lengthb求得是字节长度。

10.ltrim()—去左空格 Rtrim()—去右空格 trim()—去所有空格

11.PL/SQL

（1）语序语法

declare

说明部分（变量说明，游标申明，例外说明）

begin

语句序列（DML语句）

exception

例外处理语句

End；

（2）常量和变量定义

---------------常量-------------

declare

pname varchar2(10);

age number(3) :=20;

begin

pname :='张三';

dbms_output.put_line(pname);

dbms_output.put_line(age);

end;

---------------引用变量（引入表中的某一列的数据类型）--------------

Myname 表.列名%type

引用类型变量，即Myname的类型与表中所应用的类型一致

.

declare

pname myemp.ename%type;

begin

select t.ename into pname from myemp t where t.empno = 7369;

dbms_output.put_line(pname);

end;

---------------记录型变量变量（可以预留Java中的对象类型变量）--------------

declare

prec myemp%rowtype；

begin

select * into prec from myemp t where t.empno = 7369;

dbms_output.put_line(prec.ename || ' ' || prec.sal);

end;

prec就是代表整个表

12. IF... then .... Elsif ...then ... Else... End if end;
```
 
--打开屏幕输出开关（默认没有打开的）
set serveroutput on;
--接受键盘输入，变量num：是一个地址值，在该地址上保存了输入的值
accept num prompt '请输入一个数字';
 
declare 
  --定义变量保存输入的数字
  pnum number := &num;
begin
  if pnum = 1 then 
    dbms_output.put_line('我是数字1');
  end if;
end;
/
```
13.case

Case xxx

When…Then…

Else…

End Case;

14.while

while 条件

loop

      /*条件成立后执行*/

exit when a>10           --当a>10的时候退出循环

end loop;

15.for

15.oracle异常

no_data_found (没有找到数据)

too_many_rows (select……into 语句匹配多个行)

zero_zivide (被零除)

有三种类型的异常错误：

预定义 ( Predefined )错误
ORACLE 预定义的异常情况大约有 24 个。对这种异常情况的处理，无需在程序中定义，由 ORACLE 自动将其引发。

非预定义 ( Predefined )错误
即其他标准的 ORACLE 错误。对这种异常情况的处理，需要用户在程序中定义，然后由 ORACLE 自动将其引发

用户定义(User_define) 错误
程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发

异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:
```
EXCEPTION
	WHEN first_exception THEN	<code to handle first exception > 
	WHEN second_exception THEN	<code to handle second exception > 
	WHEN OTHERS THEN	<code to handle others exception >
END;
```
预定义的异常处理

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Yj4hSylT-1611209518990)(F:\Oracle.assets\image-20201123090845076.png)]$

非预定义的异常处理

对于这类异常情况的处理，首先必须对非定义的 ORACLE 错误进行定义。步骤如下：
1. 在 PL/SQL 块的定义部分定义异常情况：<异常情况> EXCEPTION;
2. 将其定义好的异常情况，与标准的 ORACLE 错误联系起来，使用 PRAGMA EXCEPTION_INIT 语句：PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>);
3. 在 PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。
删除指定部门的记录信息，以确保该部门没有员工

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lBusQWJU-1611209518994)(F:\Oracle.assets\image-20201123091339650.png)]$

16.复合变量
```
TYPE myrecord is RECORD(
    id varchar2(10),
    name varchar2(10)
);
real_record myrecord;
BEGIN
    select emp_id,emp_name INTO real_record from emp where emp_id = '001';
    dbms_output.put_line(real_record.id || real_record.name);
END;
```
17.游标

按照员工的工种涨工资，总裁1000，经理800，其他400

------------------待有参数的游标-------------------

18.%type和%rowtype

%type

解释：type的使用时为了使一个新定义的变量与另一个已经定义了的变量(通常是表的某一列)的数据类型保持一致,

当被参照的那个变量的数据类型发生改变时,那么这个新定义的变量的数据类型也会随之发生改变,这种定义方式类似与其他编程语言中的"var",其类型不由自己决定，而是由与之关联的对象决定。

优点：当参数类型发生变化的时候不用修改Sql语句了,当不能确切的知道那个变量的类型是,就采用这种方法来定义变量的数据类型。

%rowtype

为了使一个变量的数据类型与一个表中记录的各个列的数据类型相对应、一致，用%rowtype的定义方式。当表的某些列的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用每次修改PL/SQL语言了。当不能确切地知道被参照的那个表的结构及其数据类型时，就只能采用这种方法定义变量的数据类型。

19.trunc

（1）日期处理：
　　　　TRUNC(date,[fmt])
　　 date 为必要参数，是输入的一个date日期值
　　fmt 参数可忽略，是日期格式，缺省时表示指定日期的0点。
（2）数值处理：
　　　　TRUNC(number,[decimals])
　　number 为必要参数，是输入的一个number数值
　　decimals 参数可忽略，是要截取的位数，缺省时表示截掉小数点后边的值。

.select trunc(123.458) from dual --123
.select trunc(123.458,0) from dual --123
.select trunc(123.458,1) from dual --123.4
.select trunc(123.458,-1) from dual --120

20.日期函数

months_between

add_month(日期，数字)：获得几个月后的日期

next_date()：指定给出下次某日日期数

last_day：求出本月的最后一天

21.转换函数

22.nvl

1.NVL函数是一个空值转换函数。
该函数的目的是把一个空值（null）转换成一个实际的值。

2.两种用法
用法一
（1）格式：NVL（表达式1，表达式2）
（2）功能：如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。
（3）注意：其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。

对数字型： NVL(comm,0);

对字符型 NVL( TO_CHAR(comm), 'No Commission')

对日期型 NVL（hiredate,' 31-DEC-99')
其中comm为你需要判断是否为空的列名

用法二
（1）格式：NVL2(表达式1，表达式2，表达式3）
（2）功能：如果表达式1为空，返回值为表达式3的值。如果表达式1不为空，返回值为表达式2的值。
（3）样例： NVL2（comm,'sal+comm',sal)
如果comm为空，就返回sal 的值。如果 comm 不为空(null),就返回表达式 sal+comm的值。

23.Decode函数

该函数类似于if...else if...else

语法：DECODE（col/expression,[search1,result1],[search2,result2]……[default]）

Col/expression:列名或者表达式

Search1，Search2……: 用于比较的条件

Result1，Result2……：返回值

如果匹配就返回相对应的result，否则返回default

24.oracle的分页rownum

25.日期函数

months_between：两个时间段中的月数

add_month(日期，n)：求出n个月后的日期

next_data(日期，"星期一”) ：下次某日期数

26.%TYPE说明 %ROWTYPE

%TYPE说明

为了使一个变量的数据类型与另一个已经定义了的变量（尤其是表的某一列）的数据类型相一致，Oracle提供了%TYPE定义方式。当被参照的那个变量的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用修改PL/SQL程序了。当不能确切地知道被参照的那个变量的数据类型时，就只能采用这种方法定义变量的数据类型。

如果一个表有较多的列，使用%ROWTYPE来定义一个表示表中一行记录的变量，比分别使用%TYPE来定义表示表中各个列的变量要简洁得多，并且不容易遗漏、出错。这样会增加程序的可维护性。

%ROWTYPE

为了使一个变量的数据类型与一个表中记录的各个列的数据类型相对应、一致，Oracle提供%ROWTYPE定义方式。当表的某些列的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用修改PL/SQL程序了。当不能确切地知道被参照的那个表的结构及其数据类型时，就只能采用这种方法定义变量的数据类型。

一行记录可以保存从一个表或游标中查询到的整个数据行的各列数据。一行记录的各个列与表中一行的各个列有相同的名称和数据类型。

27.视图

28.序列
```
create sequence seq_test
increment by 1
start with 10
maxValue 300
minValue 2
cache 20；
```
创建一个seqpersonid的序列，完成自动增长的操作

create SEQUENCE seqpersionid；

序列创建完毕后，所有的用户增长应该用户自己处理
- NextVal：取得序列的下一个内容
- CurrVal：取得序列的当前内容
30.循环loop for

declare

total number(4) ：=0；

begin

while total <= 100 loop

total :=total + 1;

dbms_output.put_line(total);

end loop;

end;

declare

total number(4) :=0;

begin

loop

exit when total = 100;

total :=total + 1;

dbms_output.put_line(total);

end loop;

end;

declare

total number(4) :=0;

begin

for total in 1...100 loop

dbms.output.put_line(total);

end loop;

end;

31.存储过程

实例
```
create or replace prodecure 过程名 【（参数名 in/out 数据类型）】
as
begin
    PLSQL 子程序体
end；
 
 
create or replace prodecure helloword as
begin
    dbms_output.put_line('hello word');
end;
 
//然后可以直接调用
begin
    helloword;
end;
```
给指定员工涨100工资
```
create or replace procedure addsal(pno in myemp.empno%type) as
    ---定义变量---
    prec myemp&rowtype;
begin
    select * into prec from myemp t where t.empno = pno;
    update myemp tset t.sal = t.sal + 100 where t.empno = pno;
    dbms_output.put_line('涨工资前是：'||prec.sal || '  涨工资后是:'||(prec.sal + 100));
end;
 
 
---------------------------------------------------------------------------------
begin
    addsal(pno => :pno);
    commit;
end;
```
计算指定员工的年薪
```
create or replace producre countYSal(pno in emp.empno%type,ysal out number) is
    psal emp.sal%type;
    pcomm emp.comm%type;
begin
    select t.sal,t.comm into psal,pcomm from emp t where t.empno = pno;
    ysal := psal*12 + nvl(pcomm,0);
end countYSal;
 
 
begin
    countYSal(pno => :pno,
                ysal => :ysal);
end;
```
32.存储过程的调用
```
declare
    ysal number;
begin
    ycountysay(7369,ysal);
    dbms_output.put_line(ysal);
end'
```
33.触发器trigger

触发器是一个与表相关联的，存储的PL/SQL程序。每当一个特定的数据操作语句（insert，update，delete）在指定的表上发出时，oracle自动地执行触发器中定义的语句

触发器类型
- 语句触发器：在指定的语句操作之前或之后执行一次，不管这个语句影响了多少行
- 行级触发器：触发语句作用的每一条记录都被触发。用old和new伪记录变量，识别值的状态
当插入一条数据的时候，打印一条数据
```
create or replace trigger insertptrg
    before insert on person
begin
    dbms_output.put_line("一条记录被插入");
end insertptrg;
 
 
insert into person values(seqpersonnid.next.val,'王五',1,sysdata);
```
星期三不允许插入员工数据
```
create or replace tigger valid_insert_p
    before insert on person
declare
    cruday varchar2(10);
begin
    select tochar(sysdata,'day') nto cruday from dual
    if cruday in ('星期三') then
        raise_application_error(-20001,'星期三不允许插入数据');
    end if;
end;
```
触发语句和伪记录变量的值

涨之前的工资不能比涨之后的高
```
create or replace trigger vaild_addsal
    before update of sal on myemp
        for each row
begin
    if :new.sal <= :old.sal then
        raise_application(-20002,"涨后的工资不能比涨前的低")；
    end if；
end vaild_addsal;
```
相关阅读:
QTableWidget常用属性
 离子液体1-丁基-3-甲基咪唑六氟磷酸盐(BMI)改性氧化石墨烯(GO)文献摘要
 SpringCloudAlibaba 综合项目实战工业级PaaS云平台第六课微服务治理、paas和devops
扩散模型 - Diffusion Model【李宏毅2023】学习笔记数学原理篇
 朴素贝叶斯分类（下）：数据挖掘十大算法之一
 线性表的应用 —— 静态链表
 服务器漏洞利用
 云时代下，医药行业管理居然这么简单
 matlab数据处理
 0.在Linux Centos7下安装MySQL及其配置
原文地址：https://blog.csdn.net/weixin_47188125/article/details/127675037