Oracle数据库开发——从小白到大神学习笔记

基于oracle数据库的PLSQL编程以及存储过程的创建和使用

test windows

declare 
  -- Local variables here变量和游标声明
  i integer;
begin
  -- Test statements here
  
end;

set serveroutput on
-- Created on 2021-03-24 by BJ 
declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here
  dbms_output.put_line('你好');
end;

变量

PLSQL编程中常见的变量分两大类:
1.普通数据类型(char,varchar2, date, number, boolean, long)
2.特殊变量类型(引用型变量、记录型变量)

声明变量的方式为
1变量名变量类型(变量长度)例如:v_name varchar2(20);

普通变量

变量赋值的方式有两种:
1.直接赋值语句 :=
2.语句赋值，使用select …into …赋值:(语法select值 into变量)
【示例】打印人员个人信息，包括:姓名、薪水、地址

--打印人员个人信息，包括:姓名、薪水、地址
DECLARE
--姓名
v_name VARCHAR2(20):='张三';
--新水
v_sal NUMBER;
--地址
v_addr VARCHAR2(200) ;
BEGIN
--直接赋值
v_sal :=1580;
--语句赋值
SELECT '上海传智播客' INTO v_addr FROM dual;
--打印输出
dbms_output.put_line('姓名:' ||v_name||'，薪水:'||v_sal||',地址:'||v_addr);
end;

引用变量

变量的类型和长度取决于表中字段的类型和长度
通过表名.列名%TYPE指定变量的类型和长度，例如: v_name emp.ename%TYPE;
【示例】查询emp表中7839号员工的个人信息，打印姓名和薪水

--打印人员个人信息，包括:姓名、薪水、地址
DECLARE
--姓名
v_name emp.ename%type;
--新水
v_sal emp.sal%type;

BEGIN
       SELECT ename,sal INTO v_name,v_sal FROM emp wHERE empno = 7839;
       --打印输出
       dbms_output.put_line('姓名:' ||v_name||'，薪水:'||v_sal);
end;

记录变量

接受表中的一整行记录，相当于Java中的一个对象
语法:变量名称表名%ROWTYPE，例如:v_emp emp%rowtype;
如果有一个表，有100个字段，那么你程序如果要使用这100字段话，如果你使用引用型变量一个个声明，会特别麻烦,记录型变量可以方便的解决这个问题

DECLARE
--记录型变量
v_emp emp%rowtype;


BEGIN
       SELECT * INTO v_emp FROM emp wHERE empno = 7839;
       --SELECT * INTO v_emp FROM emp  不加条件会报：返回行数超过请求行数，需要使用集合，循环，遍历
       --打印输出
       dbms_output.put_line('姓名:' ||v_emp.ename||'，薪水:'||v_emp.sal);
end;

流程控制

if条件分支

begin
if 条件1 then 执行1
elsif 条件2 then 执行2
else 执行3
end if；
end；

--判断emp表中记录是否超过20条，10-20之间，或者10条以下
DECLARE
--古明变量接受emp中的数量
v_COUNT NUMBER;
BEGIN
SELECT COUNT (1)INTO v_COUNT FROM EMP;
IF v_COUNT >20 THEN
   DBMS_OUTPUT.PUT_LINE (' emp表中的记录数超过了20条为:' || v_COUNT) ;
ELSIF v_COUNT >= 10 THEN
      DBMS_OUTPUT.PUT_LINE ('emp表中的记录数在10~20条为:'|| v_COUNT);
ELSE
      DBMS_OUTPUT.PUT_LINE ( ' emp表中的记录数10条以下为:' || v_COUNT);
END IF;
END;

LOOP循环

begin
loop
exit when 退出循环条件
end loop;
end;

set serveroutput on
DECLARE
    V_NUM NUMBER :=1;
BEGIN
    LOOP
          EXIT WHEN V_NUM >10;
         DBMS_OUTPUT.PUT_LINE(V_NUM);
         V_NUM := V_NUM + 1;
    END LOOP;
END;

游标

其他方式只能接受一行数据，游标可以接受多行数据

用于临时存储一个查询返回的多行数据(结果集,类似于Java的Jdbc连接返回的ResultSet集合)，通过遍历游标，可以逐行访问处理该结果集的数据。
游标的使用方式:声明—>打开—>读取—>关闭

语法
游标声明:
CURSOR 游标名[(参数列表)]IS查询语句;
游标的打开:
OPEN游标名;
游标的取值:
FETCH游标名INTO变量列表;
游标的关闭:
CLOSE游标名;

游标属性

其中 %NOTFOUND是在游标中找不到元素的时候返回TRUE,通常用来判断退出循环

--使用游标查询emp全表中所有员工的姓名和工资，并将其依次打印出来。
DECLARE
--声明游标
CURSOR c_emp is SELECT ename , sal FROM emp;
--声明变量接受游标中的数据
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
--打开游标
OPEN c_emp ;
--遍历游标
LOOP
--获取游标中的数据
FETCH c_emp INTO v_ename, v_sal;
--退出循环条件
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (v_ename ||' ,' || v_sal);
END LOOP;
end;

参数游标

--使用游标查询emp表中某部门员工的姓名和工资，并将其依次打印出来。
DECLARE
--声明游标
CURSOR c_emp(v_deptno emp.deptno%type) is SELECT ename , sal FROM emp where deptno=v_deptno ;
--声明变量接受游标中的数据
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
--打开游标  传入参数
OPEN c_emp(10) ;
--遍历游标
LOOP
--获取游标中的数据
FETCH c_emp INTO v_ename, v_sal;
--退出循环条件
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (v_ename ||' ,' || v_sal);
END LOOP;
end;

存储过程

之前我们编写的PLSQL程序可以进行表的操作,判断,循环逻辑处理的工作,但无法重复调用.
可以理解之前的代码全都编写在了main方法中,是匿名程序.JAVA可以通过封装对象和方法来解决复用问题
PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用,这些被存储起来的PLSQL程序称之为存储过程
存储过程作用:
1，在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭是很耗费资源),需要对数据库进行多次IO读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率.
2，ORACLE官方给的建议:能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可能会存在错误.(如果在数据库中操作数据,可以有一定的日志恢复等功能.)

1，不带参数
2，带参数
3，带输入输出参数

create or replace procedure p_hello is
begin
  dbms_output.put_line('三生三世')
end p_hello;

调用
begin  
p_hello 
  end;
  或者exec

存储过程带输入参数

--查询并打印某个员工.(如7839号员工〉的姓名和薪水--存储过程:要求，调用的时候传入员工编号，自动控制台打印。
create or replace procedure p_guerynameandsal(i_empno IN emp.empno%TYPE)as
--声明变量  接受参数
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;

begin  
  --查询emp表中某个员工的姓名和薪水并赋值给变量
SELECT ENAME,SAL INTO V_NAME，V_SAL FROM EMP WHERE EMPNO = I_EMPNO;
DBMS_OUTPUT.PUT_LINE(V_NAME || '_' ||v_SAL);

  end p_guerynameandsal;

调用
-- Created on 2021-03-24 by BJ 
declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here
  p_guerynameandsal(7839);
end;

存储过程带输入输出参数

给其他变成语言使用

--输入员工号查询某个员工(7839号员工）信息，要求，将薪水作为返回值输出，给调用的程序使用。
create or replace procedure p_out(i_empno IN emp.empno%TYPE,o_sal out emp.sal%type)as


begin  
  --查询emp表中某个员工的姓名和薪水并赋值给变量
SELECT SAL INTO o_sal FROM EMP WHERE EMPNO = I_EMPNO;

  end p_out

**调用**
-- Created on 2021-03-24 by BJ 
declare 
--声明变量接受存储过程中的输出参数

 v_sal emp.sal%type;
begin
  -- Test statements here
  p_out(7839,v_sal);
  dbms_output.put_line(v_sal);
end;

动力节点(PLSQL+SQL优化)数据库入门必备教程

https://www.bilibili.com/video/BV1a5411h7mR?from=search&seid=15336009296702982505

使用PL/SQL可以编写具有很多高级功能的程序,虽然这些功能可以通过多个SQL语句来完成同样的功能,但是PLISQL具有如下的优点:
使一组语句功能形成模块化程序开发使用过程性语言控制程序结构
可以对程序中的错误进行处理具有较好的可移植姓
集成在数据库中,调用更快
减少了网络的交互,有助于提高程序性能

DECLARE
--在declare部分声明变量,常量等
--声明变量的规范:变量名苏变量类型[:-缺省值;
v_DEPTNO number;
BEGIN
--在BEGiN部分可以写soz语句,PL/sQL语命
--在BEGzN部分可以使用DECLARE部分声明的变量,常量
dbms_output.put_line('欢迎使用pL/sQL,执行查询语句之前,v_DEPTNO='|| V_DEPTNO);
--把查淘语句查淘的结果赋值给V_DEPTNO这个变量

SELECT DEPTNO INTO V_DEPTNO FROM EMP WHERE EMPNO = 7369;
dbms_output.put_line('执行查询语句后，V_DEPTNO='|| V_DEPTNO) ;
DELETE from emp where DEPTNO= V_DEPTNO;
DELETE from DEPT where DEPTNO = V_DEPTNO;
end;


SELECT deptno from EMP wHERE EMPNO = 7369;

在声明部分声明和初始化变量
在执行部分为变量赋新值，或在表达式中使用变量在异常处理部分也可以使用变量
通过参数把值传递到PLISQL块中
通过输出变量或者参数将值传出PL/SQL块

变量

DECLARE
V_TOTAL_SAL NUMBER (9,2) :=0;-- PL/SQL中赋值语命
C_TAX_RATE CONSTANT NUMBER (3,2) := 8.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_TOTAL_SAL='|| V_TOTAL_SAL) ;
--相当于JAVA中的==的功能执行逻辑比较操作
V_B :=(V_NUM1 = V_NUM2) ;
IF   (V_B =TRUE) THEN
DBMS_OUTPUT.PUT_LINE ( ' OK');
ELSE
DBMS_OUTPUT.PUT_LINE ( ' NOT OK' );
END IF;
END;

变量类型

简单变量

BINARY_INTEGER 整形数字
NUMBER [(precision, scale)] 数字类型
CHAR[(maximum_length)] 定长字符类型
VARCHAR2(maximum_length) 变长字符类型
DATE 日期类型
LONG 长字符类型
LONG RAW 长二进制类型
CLOB BLOB/BFILE 大对象类型(字符大对象.二进制大对象,操作系统文件大对象)
BOOLEAN 布尔类型,有效值为TRUE,FALSE,NULL

复杂变量 表和记录类型

DECLARE
--在声明部分声明表类型
TYPE NAMEED_TABLE_TYPE IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;
--使用声明的表类型来声明变垒
v_TABLE1 NAMEED_TABLE_TYPE;
BEGIn
--给表类型的变量赋值，可以通过索引来访问表类型的变量
--表类型的变量没有长度的限箭
v_TABLE1(1) := ' Hel1o1';
v_TABLE1(2) := ' He11o2 ' ;
v_TABLE1(3) := ' Hello3 ' ;
v_TABLE1(4) := ' Hel1o1' ;
v_TABLE1(5) := '工程师';
v_TABLE1(6):='工程师A' ;
DBMS_OUTPUT.put_line(v_TABLE1(1)||v_TABLE1(2) ) ;
END;

https://www.bilibili.com/video/BV1a5411h7mR?p=2&spm_id_from=pageDriver

PLSQL

过程化语言
1，过程，函数，触发器是PLSQL编写的
2，过程函数触发器是oracle中
3，plsql过程序言
4，可以在java中调用

块-----1，过程，2，函数，3触发器 ，4，包

注释 单行 – /…/

过程

p1/sql块由三个部分构成:定义部分、执行部分、例外处理部分.如下所示:
declea
/定义部分一—-—定义常量、变量、游标、例外、复杂数据类型/
begin
/执行部分–—--要执行的p1/sql语句和sq1语句/
exception
/例外处理部分----处理运行的各种错误/
end;

定义部分是从declare开始的,该部分是可选的
执行部分是从begin开始的,该部分是必须的
例外处理部分是从exception开始的,该部分是可选的

set serveroutput on --打开输出选项
begin
dbms_output.put_line(‘he11o’);
end;

declare
v_ename varchar2(5);–定义字符串变量
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line(‘雇员名:’||v_ename);
end;
/

declare–定义变量
v_ename varchar2 (5);
v_sal number (7,2);
begin
–执行部分
select ename, sal into v_ename,v_sal from emp where empno=&aa;–在控制台显示用户名
dbms_output.put_line (‘用户名是:’||v_ename ||‘工资:’||v_sal) ;–异常处理
exception
when no_data_found then
dbms_output.put_line(‘朋友，你的编号输入有误!’);
end;

create procedure sp_pro3(spName varchar2 , newsal number) is
begin
–执行部分,根据用户名去修改工资
update emp set sal=newsal where ename=spName ;
end ;
/

exec sp_pro3(‘张三’,4678);
commit；

函数

用于返回特定的数据,当建立函数时，在函数头部必须包含return子句,而在函数体内必须包含return语句返回的数据。我们可以使用create funct ion 来建立函数,实际案例:

案例
输入雇员姓名，返回年薪

create or replace function sp_fun2(spName varchar2) return
number is yearsal number (7,2);
begin
–执行部分
select sal*12+nvl(comm,0)*12 into yearsal from emp where ename=spName;
return yearsal;
end;

sqlplus调用函数
SQL> var abc number;
SQL> call sp_fun2(‘张三’) into:abc;
Method called
56136

包

包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成。
包头
create package sp_package is
procedure update_sal (name varchar2 , newsal number) ;
function annual_income(name varchar2) return number ;
end ;

包体
create package body sp_package is
procedure update_sal (name varchar2 , newsal number)
is
begin
update emp set sal=newsal where ename=name;
end;
function annual income ( name varchar2)return number
is
annual_salary number;
begin
select sal*12+nv1 (comm,0） into annual_salary from emp where ename=name;
return annual_salary;
end;
end;

使用包
当调用包的过程或是函数时,在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名.
exec sp_package.update_sal(‘张三’,‘8888’);

触发器

触发器是指隐含的执行的存储过程。当定义触发器时,必须要指定触发的事件和触发的操作，常用的触发事件包括insert,update, delete语句，而触发操作实际就是一个p1/sql块。可以使用create trigger来建立触发器。

变量和常量

标量

1定义一个变长字符串
v_ename varchar2 (10);
2定义一个小数范围-9999.99- 9999.99
v_sal number (6, 2);
3定义一个小数并给一个初始植为5.4:-是p1/sql的赋值号
v-sal2 number (6, 2):=5.4
4定义一个日期类型的数据
v-hiredate date;
5定义一个布尔变量,不能为空，初始植为false
v_valid boolean not null default false;

标量
–下面以输入员工号，显示雇员姓名、工资、个人所得税–
(税率为0.03)为例。说明变量的使用，看看如何编写.
declare
c_tax_rate number (3,2):=0.03 ;
–用户名
v_ename varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
–执行
select ename , sal into v_ename ,v_sal from emp where empno=&no;
–计算所得税
v_tax_sal:=v_sal*c_tax_rate ;–输出
dbms_output.put_line(‘姓名是:’||v_ename||‘工资:’||v_sal||‘交税’||v_tax_sal);
end ;

%type
declare
c_tax_rate number (3,2):=0.03 ;
–用户名
v_ename emp.ename%type;
v_sal number(7,2);
v_tax_sal number(7,2);
begin
–执行
select ename , sal into v_ename ,v_sal from emp where empno=&no;
–计算所得税
v_tax_sal:=v_sal*c_tax_rate ;–输出
dbms_output.put_line(‘姓名是:’||v_ename||‘工资:’||v_sal||‘交税’||v_tax_sal);
end

复合变量
用于存放多个值的变量。主要包括这几种:
pl/sql记录
p1/sql表
嵌套表
varray

复合类型pl/sql记录
–pl/sql记录类型
declare
–定义一个pls/sql记录类型，一个类型存储三个数据
type emp_record_type is record(name emp.ename%type ,salary emp.sal%type,title emp.job%type);
–定义了一个sp_record变量，这个电量的类型是 emp_record_type
sp_record emp_record_type;
– v_ename varchar2 (45);
begin
select ename , sal ,job into sp_record from emp where empno=7788;
dbms_output.put_line(‘员工名:’||sp_record.name||‘工资是’||sp_record.salary) ;
end;

pl/sql表
相当于高级语言中的数组.但是需要注意的是在高级语言中数组的下标不能为负数,而p1/sql是可以为负数的,并且表元素的下标没有限制
–pl/sql表实例
declare
–定义了一个pl/sql表类型sp_table_type ,该类型是用于存放emp.ename%type
–index by binary_integer 表示下标是整数
type sp_table_type is table of emp.ename%type index by binary_integer;
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line(‘员工名:’|| sp_table(0) ) ;
end;

参照变量
参照变量是指用于存放数值指针的变量.通过使用参照变量,可以使得应用程序共享相同对象,从而降低占用的空间。在编写p1/sq1程序时,可以使用游标变量(ref cursor)和对象类型变量(ref obj-type)两种参照变量类型
参照变量-ref cursor游标变量
使用游标时,当定义游标时不需要指定相应的select语句,但是当使用游标时(open时)需要指定select语句，这样一个游标就与一个select语句结合了。

–请使用pl/sql编写一个块，可以输入部门号，并显示该部门所
declare
–定义游标类型sp_emp_cursor
type sp_emp_cursor is ref cursor;
–定义一个游标变量
test_cursor sp_emp_cursor;
–定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
–执行
–把test_cursor和一个select结合
open test_cursor for select ename, sal from emp where deptno=&no;
–循环取出
loop
fetch test_cursor into v_ename ,v_sal;
–判断是否test_cursor为空
exit when test_cursor%notfound;
dbms_output.put_line( ‘名字:’||v_ename ||‘工资:’|| v_sal );
end loop;
–关闭游标
close test_cursor;
end;

控制结构

条件分支

if-then
if–then–else
if-then-elsif-else
预估工资低于2000，则涨薪

create or replace procedure sp_pro6 ( spName varchar2) is
–定义
v_sal emp.sal%type;
begin
–执行
select sal into v_sal from emp where ename=spName;.
–判断
if v_sa1<2000 then
update emp set sal=sal*1.1 where ename=spName;
end if;
end;

exec sp_pro6 (‘SCOTT’)

-编写一个过程，可以输入一个雇员名，如果该雇员的补助不是–0就在原来的基础上增加100;如果补助为0就把补助设为200;
create or replace procedure sp_pro7( spName varchar2) is
–定义
v_comm emp.comm%type;
begin
–执行
select comm into v_comm from emp where ename=spName;.
–判断
if v_comm<>0 then
update emp set comm=comm+100 where ename=spName;
else
update emp set comm=comm+200 where ename=spName;
end if;
end;

?编写一个过程,可以输入一个雇员编号,如果该雇员的职位是PRES IDENT 就给他的工资增加1000,如果该雇员的职位是 MANAGER就给他的工资增加500,其它职位的雇员工资增加200
create or replace procedure sp_pro8( spNo number) is
–定义
v_job emp.job%type;
begin
–执行
select job into v_job from emp where empno=spNo;
–判断
if v_job=‘PRESIDENT’ then
update emp set sal=sal+100 where empno=spNo;
elsif
v_job=‘MANAGER’ then
update emp set sal=sal+200 where empno=spNo;
else
update emp set sal=sal+200 where empno=spNo;
end if;
end;

loop循环语句-至少一次

是pl/sql中最简单的循环语句,这种循环语句以loop开头,以end loop结尾,这种循环至少会被执行一次.
请,编写一个过程,可输入用户名,并循环添加10个用户到users表中,用户编号从1开始增加.
create or replace procedure sp_pro9( spName varchar2) is
–定义
v_num number:=1;
begin
loop
insert into users1 values(v_num,spName);
–判断是否要退出循环
exit when v_num=10;
–自增
v_num :=v_num+1;
end loop;
end;
exec sp_pro9(‘你好’)

while循环语句

基本循环至少要执行循环体一次，而对于while循环来说,只有条件为true时,才会执行循环体语句, while循环以while… loop开始,以erAd loop结束

create or replace procedure sp_pro10( spName varchar2) is
v_num number:=11;
begin
while v_num<20 loop
insert into users1 values(v_num,spName);
v_num :=v_num+1;
exit when v_num=10;
end loop;
end;

for循环语句

begin
for i in reverse 1…10 loop
insert into users1 values(i,‘顺平’);
end loop;
end;

goto循环语句

goto语句用于跳转到特定标号去执行语句.注意由于使用goto语句会增加程序的复杂性，并使得应用程序可以读性变差，所以在做一般应用开发时,建议大家不要使用goto语句.

declare
i int :=1;
begin
loop
dbms_output.put_line(‘输出i=’||i);
if i=10 then
goto end_loop ;
end if ;
i:=i+1 ;
end loop ;
<<end_1oop>>
dbms_output.put_line(‘循环结束’);
end ;

null语句

null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用nul1语句的主要好处是可以提高pl/sql的可读性，,

分页过程

create table book
(bookId number , bookName varchar2 (50 ) , publishRouse varchar2(50))

create or replace procedure sp_pro77
(spBookId in number , spbookMame in varchar2 , sppublishHouse in varchar2) is
begin
insert into book values( spBookId,spbookMame , sppublishHouse);
end;

–有输入和输出的存储过程
create or replace procedure sp pro88
(spno in number , spMame out varchar2 , spsal out number, spJob out varchar2) is
begin
select ename ,sal,job into spMame, spsa1 , spJob from emp where empno=spno
end;

oracle分页方式

select * from
(select t1.*，rownum rn from (select * from emp) t1 where rownum<=10） where rn>=6;

分页存储过程

create or replace procedure fenye
( tableName in varchar2 ,
agesize in number,
pageNow in number,
myrows out number,--总记录数
myPagecount out number,--总页数
p_cursor out tespackage.test_cursor--返回的记录集
)is
--定义部分
--定义sql语句字符串
v_sql varchar2 ( 1000) ;--定义两个整数
v_begin number :=(pageNow-1)*Pagesize+1;
v_end number :=pageNow*Pagesize;
begin
--执行部分
v_sql :='select * from (select t1.*,rownum rn from (select * from '||tableMame
||' order by sal) t1 where rownum<='||v_end|| ') where rn>=' ||v beqin; 
--把游标和sgl关联
open p_cursor for v_sql;
--计算myrowsmyPageCount
--组织一个sql
v_sq1 :='select count(*) from '||tab1eName;
--执行sql，并把返回的值，赋给myrows ;
execute immediate v_sql into myrows;
--计算myPageCount
if mod ( myrows , Pagesize)=0 then
myPagecount:=myrows/Pagesize;
else
myPagecount:=myrows/Pagesize+1;
end if;
--关闭游标
close p_cursor;
end;

意外处理

oracle将例外分为预定义例外,非预定义例外和自定义例外三种
问题是，如果输入的雇员编号不存在,怎样去处理呢?

declare--定义
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=&gno;
dbms_output.put_line('名字:'||v_ename );
exception
when no_data_found then
dbms_output.put_line('编号没有!');
end;

mysql数据库emp示例数据

https://www.bilibili.com/video/BV1Gx411976C?from=search&seid=8085769202788008799

https://www.bilibili.com/video/BV1Gx411976C?p=17

语句的大小写不敏感查询的内容除外

order by 必须出现在最后

create table dept(
– 部门编号
deptno int unsigned auto_increment primary key,
– 部门名称
dname varchar(15) ,
– 部门所在位置
loc varchar(50)
)engine = InnoDB;

创建 scott 数据库中的 emp 表
create table emp(
– 雇员编号
empno int unsigned auto_increment primary key,
– 雇员姓名
ename varchar(15) ,
– 雇员职位
job varchar(10) ,
– 雇员对应的领导的编号
mgr int unsigned ,
– 雇员的雇佣日期
hiredate date ,
– 雇员的基本工资
sal decimal(7,2) ,
– 奖金
comm decimal(7,2) ,
– 所在部门
deptno int unsigned ,
foreign key(deptno) references dept(deptno)
)engine = innodb;

创建数据库 scott 中的 salgrade 表，工资等级表
create table salgrade(
– 工资等级
grade int unsigned ,
– 此等级的最低工资
losal int unsigned ,
– 此等级的最高工资
hisal int unsigned
)engine=innodb;

创建数据库 scott 的 bonus 表，工资表
create table bonus(
– 雇员姓名
ename varchar(10),
– 雇员职位
job varchar(9),
– 雇员工资
sal decimal(7,2),
– 雇员资金
comm decimal(7,2)
)engine=innodb;

dept表中的数据
INSERT INTO dept VALUES (10,‘ACCOUNTING’,‘NEW YORK’);
INSERT INTO dept VALUES (20,‘RESEARCH’,‘DALLAS’);
INSERT INTO dept VALUES (30,‘SALES’,‘CHICAGO’);
INSERT INTO dept VALUES (40,‘OPERATIONS’,‘BOSTON’);

salgrade表中的数据
select * from salgrade s2
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

emp表中的数据
INSERT INTO emp VALUES (7369,‘SMITH’,‘CLERK’,7902,to_date(‘1980-12-17’,‘yyyy-mm-dd’),800,NULL,20);
INSERT INTO emp VALUES (7499,‘ALLEN’,‘SALESMAN’,7698,to_date(‘1981-2-20’,‘yyyy-mm-dd’),1600,300,30);
INSERT INTO emp VALUES (7521,‘WARD’,‘SALESMAN’,7698,to_date(‘1981-2-22’,‘yyyy-mm-dd’),1250,500,30);
INSERT INTO emp VALUES (7566,‘JONES’,‘MANAGER’,7839,to_date(‘1981-4-2’,‘yyyy-mm-dd’),2975,NULL,20);
INSERT INTO emp VALUES (7654,‘MARTIN’,‘SALESMAN’,7698,to_date(‘1981-9-28’,‘yyyy-mm-dd’),1250,1400,30);
INSERT INTO emp VALUES (7698,‘BLAKE’,‘MANAGER’,7839,to_date(‘1981-5-1’,‘yyyy-mm-dd’),2850,NULL,30);
INSERT INTO emp VALUES (7782,‘CLARK’,‘MANAGER’,7839,to_date(‘1981-6-9’,‘yyyy-mm-dd’),2450,NULL,10);
INSERT INTO emp VALUES (7788,‘SCOTT’,‘ANALYST’,7566,to_date(‘87-7-13’,‘yyyy-mm-dd’),3000,NULL,20);
INSERT INTO emp VALUES (7839,‘KING’,‘PRESIDENT’,NULL,to_date(‘1981-11-17’,‘yyyy-mm-dd’),5000,NULL,10);
INSERT INTO emp VALUES (7844,‘TURNER’,‘SALESMAN’,7698,to_date(‘1981-9-8’,‘yyyy-mm-dd’),1500,0,30);
INSERT INTO emp VALUES (7876,‘ADAMS’,‘CLERK’,7788,to_date(‘87-7-13’,‘yyyy-mm-dd’),1100,NULL,20);
INSERT INTO emp VALUES (7900,‘JAMES’,‘CLERK’,7698,to_date(‘1981-12-3’,‘yyyy-mm-dd’),950,NULL,30);
INSERT INTO emp VALUES (7902,‘FORD’,‘ANALYST’,7566,to_date(‘1981-12-3’,‘yyyy-mm-dd’),3000,NULL,20);
INSERT INTO emp VALUES (7934,‘MILLER’,‘CLERK’,7782,to_date(‘1982-1-23’,‘yyyy-mm-dd’),1300,NULL,10);

oracle示例数据

CREATE TABLE “SCOTT”.“BONUS”
( “ENAME” VARCHAR2(10),
“JOB” VARCHAR2(9),
“SAL” NUMBER,
“COMM” NUMBER
)

CREATE TABLE “SCOTT”.“DEPT”
( “DEPTNO” NUMBER(2,0),
“DNAME” VARCHAR2(14),
“LOC” VARCHAR2(13),
CONSTRAINT “PK_DEPT” PRIMARY KEY (“DEPTNO”)

INSERT INTO SCOTT.DEPT (DEPTNO,DNAME,LOC) VALUES
(10,‘ACCOUNTING’,‘NEW YORK’),
(20,‘RESEARCH’,‘DALLAS’),
(30,‘SALES’,‘CHICAGO’),
(40,‘OPERATIONS’,‘BOSTON’);

CREATE TABLE “SCOTT”.“EMP”
( “EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10),
“JOB” VARCHAR2(9),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0),
CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”)

INSERT INTO SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES
(7369,‘SMITH’,‘CLERK’,7902,TIMESTAMP’1980-12-17 00:00:00.0’,800,NULL,20),
(7499,‘ALLEN’,‘SALESMAN’,7698,TIMESTAMP’1981-02-20 00:00:00.0’,1600,300,30),
(7521,‘WARD’,‘SALESMAN’,7698,TIMESTAMP’1981-02-22 00:00:00.0’,1250,500,30),
(7566,‘JONES’,‘MANAGER’,7839,TIMESTAMP’1981-04-02 00:00:00.0’,2975,NULL,20),
(7654,‘MARTIN’,‘SALESMAN’,7698,TIMESTAMP’1981-09-28 00:00:00.0’,1250,1400,30),
(7698,‘BLAKE’,‘MANAGER’,7839,TIMESTAMP’1981-05-01 00:00:00.0’,2850,NULL,30),
(7782,‘CLARK’,‘MANAGER’,7839,TIMESTAMP’1981-06-09 00:00:00.0’,2450,NULL,10),
(7788,‘SCOTT’,‘ANALYST’,7566,TIMESTAMP’1987-04-13 00:00:00.0’,3000,NULL,20),
(7839,‘KING’,‘PRESIDENT’,NULL,TIMESTAMP’1981-11-17 00:00:00.0’,5000,NULL,10),
(7844,‘TURNER’,‘SALESMAN’,7698,TIMESTAMP’1981-09-08 00:00:00.0’,1500,0,30);
INSERT INTO SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES
(7876,‘ADAMS’,‘CLERK’,7788,TIMESTAMP’1987-05-13 00:00:00.0’,1100,NULL,20),
(7900,‘JAMES’,‘CLERK’,7698,TIMESTAMP’1981-12-03 00:00:00.0’,950,NULL,30),
(7902,‘FORD’,‘ANALYST’,7566,TIMESTAMP’1981-12-03 00:00:00.0’,3000,NULL,20),
(7934,‘MILLER’,‘CLERK’,7782,TIMESTAMP’1982-01-23 00:00:00.0’,1300,NULL,10);

CREATE TABLE “SCOTT”.“SALGRADE”
( “GRADE” NUMBER,
“LOSAL” NUMBER,
“HISAL” NUMBER
)
INSERT INTO SCOTT.SALGRADE (GRADE,LOSAL,HISAL) VALUES
(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);
字符串只能是单引号 并注意大小写

基本查询

select sal*1.2 from emp;
别名特殊字符要加双引号
连接运算符||‘xxxx’||
order by 必须出现在最后 asc 升序 desc 排序可以使用别名

条件查询
比较运算符 > < <> !=
where hiredate > ‘1-1月-82’

复合条件 and or not
not job=‘MANAGER’ OR 优先级 and not

特殊运算符
between and in like is null ni not null

函数

数值型

abs 绝对值
sqrt 平方根
mod余数
round 四舍五入(45,923,1)
trunc

字符函数

ascii
lower
upper
initcap首字母大写
concat 拼接 ||
substr（‘asdfasdf’，1,5） 从第几个到第几个 以W开头的 substr(ename,1,1)=‘W’
length
instr 子字符串 出现的位置 包含s的 instr(ename,‘S’,1,1)>0
lpad rpad 用字符串填充(‘this’,10,’-’)
trim去除字符串 (‘asdfasdf’) 可以去掉空格
replcace

日期函数

select sysdate+1 from dual;
select sysdate - to_date(‘1-6月-82’) from dual;
add_months(sysdate,3)
next_day (sysdate,‘星期二’)
last_date
round四舍五入 seiect round(to_date( '15-6月-82 ') , ‘YEAR ’ ) fron dual
trunc

转换函数

数值，字符，日期

to_char

字符串自动转数值
字符串到数值
字符串到日期
数值到字符串
日期到字符串
SELECT TO_CHAR(123.45, ‘0000.0000’)，TO_CHAR(12345,'L9.9EEEE ')FRON dual

SQL> select ‘11’+22 from dual;

‘11’+22

    33

to_date

select to_date( '2019-01-01" , "YYYY-MM-DD ’ )from dual
select to char (sysdate,‘yyyy-mm-dd HH24:MI:SS’) from dual

to_number

https://www.bilibili.com/video/BV1Gx411976C?p=24&spm_id_from=pageDriver

NVL去空函数

​ nvl(comm,0)

decode分支函数

​ descode（列明）
userenv（‘lang’）

批处理启动ORACLE

net start OracleServicexxxx
net start listener

高级查询

多表关联查询
笛卡尔积=自然连接=两表乘积
1，相等连接
2，不等连接
3，自连接
​ 自己和自己连接 select * from emp e1,emp e2;
4，外连接
​ 缺啥补啥
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fINHWPHC-
左外连接
​ left outer join dept on 条件+

右外连接
+right outer join dept on 条件

全外连接
​ left outer join dept on 条件

统计函数（组函数）

全表搜索
avg
count * 多上号 count+列 统计非空列数
max
min
max

分组统计

组内统计group by
分组限定条件之后规则
1，在select后面的列，要么是分组的列，要么是分组函数/统计函数
2，分组过滤条件不能用where 要用having 出现在分组之后 where在分组之前

分组限定
对分组查询的结果进行过滤，要使用HAVING从句。HAVING从句过滤分组后的结果，它只能出现在GROUP BY从句之后，而WHERE从句要出现在GROUP BY从句之前。
where deptno is not null
group by 1,2
having max(sal)<2000;

order by 在最后
order by 别名排序 order by 数字

组函数的嵌套
select max( avg(sal))From empgroup by deptno

子查询=就是一个表

单行子查询，，只返回一行 =
select *from emp where
job=(select job from emp where lower (ename)=‘scott’)

多行子查询， sal < any 有一个成立即可 sal>all 大于所有的
SELECT empno, ename,job,sal FROM emp wHERE sal <ANY
( SELECT sal FROM emp wHERE job = ‘CLERK’) AND job <> ‘CLERK’

in 在集合中

多列子查询 where (job,deptno)=（select job,deptno）
select * from emp
where (deptno,job)=
( select deptno,job from emp where ename=‘SCOTT’)

子查询可以放在from
rownum只能等于1 和 《= n 不能大于
求第六行到第九行
错误：select rownum,emp.* From emp
where rownum>=6 and rownum<=9

正确：
select * from (select rownum r,e.ename from emp e where rownum<=9) e where e.r>=6;

集合操作

同一列类型相同就能连接
union all
selecte mpno,ename from emp union select deptno,job from emp;
intersect交集
miuns差集

insert into xxx(select * from xxx)
update xxx set 列明1=xx，列明2=xxx where 列明=xxxx
update xx set （mgr，name）=（select mgr,name from emp where name=scott）where no=777;
delete from xx where

数据操作

DML

insert into xxx value(全部列出)

insert into xxx(1,2，3) value（1.2.3）；

insert into xxx (select * from emp where xxx)

插入空值：
insert (null,’’)

update xxx set 列名 值 ， 列名=表达式 where

update emp1 set (ngr,hiredate)=(select ngr,hiredate fron enp where enane=‘SCOTT’)where empno=7777

delete from xxx where

完整性

数据完整性 主键 非空，唯一，检查，
约束完整性 外键

视图

视图可以修改，修改的是表的数据
可以创建只读视图

序列

同义词

聚簇

数据存在聚簇里
两个表的公共部分，提高查询速度
创建聚簇
CREATE CLUSTER COMM(STUNO NUMBER(5),STUNAME VARCHAR2(10),SEX VARCHAR2(2))SIZE 500
TABLESPACE USERS;
创建聚簇表1
CREATE TABLE STUDENT (STUNO NUMBER(5),
STUNAME VARCHAR2(10),SEX VARCHAR2(2),
ADDRESS VARCHAR2(20),E_MAIL VARCHAR2(20)
CLUSTER COMM(STUNO,STUNAME,SEX) ;
创建聚簇表2
CREATE TABLE SCORE(STUNO NUMBER(5)，
STUNAME VARCHAR2(10),SEX VARCHAR2(2),
CHINESE NUMBER(3),MATH NUMBER(3)，ENGLISH NUMBER(3))
CLUSTER COMM(STUNO,STUNAME,SEX) ;
创建聚簇索引
CREATE INDEX INX_COMM ON CLUSTER COMM;

数据库编程

PL/SQL 封装了SQL的过程语言
set serveroutput on

declare
begin
dbms_output.put_line(‘hello world’) ;
end;

变量的数据类型

变量

定义常量

declare
PI constant number :=3.1415926;
name varchar2(20) default ‘cy123’;
hiredate date := sysdate -7;
sex boolean := true;
begin
dbms_output.put_line(‘PI=’||PI);
dbms_output.put_line(‘name=’||name);
dbms_output.put_line(‘hiredate=’||hiredate);
if sex then
dbms_output.put_line(‘性别为男’);
else
dbms_output.put_line(‘性别为女’);
end if;
end;

show errors

根据表中字段定义变量%type

表名.字段名
declare
–定义变量
v_ename varchar2(2) ;
v_sal number;
begin
select ename, sal into v_ename,v_sal from emp where empno=‘7369’;
dbms_output.put_line(‘你好’||v_ename) ;
dbms_output.put_line(‘您的工资是:’||v_sal);
end;
–会提示太小放不下

declare
–定义变量
v_ename emp.ename%type ;
v_sal emp.sal%type;
begin
select ename, sal into v_ename,v_sal from emp where empno=‘7369’;
dbms_output.put_line(‘你好’||v_ename) ;
dbms_output.put_line(‘您的工资是:’||v_sal);
end;

–记录变量%rowtype

存的就是一个表里的所有的列类型
declare
–定义变量
v_recored emp%rowtype;
begin
select ename, sal into v_recored.ename,v_recored.sal from emp where empno=‘7369’;
dbms_output.put_line(‘你好’||v_recored.ename) ;
dbms_output.put_line(‘您的工资是:’||v_recored.sal);
end;

还可以用*
declare
–定义变量
v_recored emp%rowtype;
begin
select * into v_recored from emp where empno=‘7369’;
dbms_output.put_line(‘你好’||v_recored.ename) ;
dbms_output.put_line(‘您的工资是:’||v_recored.sal);
end;

table类型变量相当于集合变量

declare
–定义table型变量 类 数据类型 集合数据类型
TYPE aaaa IS TABLE OF varchar2(20) INDEX BY BINARY_INTEGER;
petList aaaa;
begin
petList(0):=‘tom’;
petList(-1):=‘yanli’;
dbms_output.put_line(‘狗狗的名字是:’||petList(0)) ;
dbms_output.put_line(‘狗狗的名字是:’||petList(-1));
end;

结合变量

VARIABLE g_ename VARCHAR2(100);

SET SERVEROUTPUT ON
BEGIN
:g_ename:=:g_ename||‘Hello~’;
–在程序中使用结合变量
DBMS_OUTPUT.PUT_LINE(:g_ename);
–输出结合变量的值
END;

SQL> select :g_ename from dual;

结构控制语句

顺序语句

从上到下一次执行――全部执行没有任何不执行的代码

判断语句if then end if

if(){
}else if(){
}else{}

declare
v_sex boolean default true;
begin
if v_sex then
dbms_output.put_line(‘狗狗的名字是:’) ;
end if;
end;

declare
v_sex boolean default true;
begin
if v_sex then
dbms_output.put_line(‘狗狗的名字是:’) ;
elsif v_sex=false then
dbms_output.put_line(‘狗狗的名字是:’) ;
end if;
end;

declare
–v_sex boolean default true;
v_sex varchar2(20) default ‘先生’;
begin
if v_sex=‘先生’ then
dbms_output.put_line(‘先生:’) ;
elsif v_sex=‘女生’ then
dbms_output.put_line(‘女生’) ;
else
dbms_output.put_line(‘人’) ;
end if;
end;

选择语句

case

case 选择变量名
when 表达式1 then
when 表达式2 then
else

1，相等条件switch
declare
v_job emp.job%type;
begin
select job into v_job from emp where empno=7788;
case v_job
when ‘CLERK’ then
dbms_output.put_line(‘雇员职位是:文员’);
when ‘MANAGER’ then
dbms_output.put_line(‘雇员职位是:经理’);
when ‘SALESMAN’ then
dbms_output.put_line(‘雇员职位是:销售’);
else
dbms_output.put_line(‘雇员职位是:吴志伟’);
end case;
end;

2，case赋值实现
declare
v_job emp.job%type;
v_job_name varchar2(10);
begin
select job into v_job from emp where empno=7788;
v_job_name:=case v_job
when ‘CLERK’ then
‘文员’
when ‘MANAGER’ then
‘经理’
when ‘SALESMAN’ then
‘销售’
else
‘其他’
end;
dbms_output.put_line(‘职位’||v_job_name) ;
end;

**3，搜索case结构，任意判断 大小小于 **

输出工资的等级
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7788;
case
when v_sal >5000 and v_sal < 10000 then
dbms_output.put_line(‘工资为:A’) ;
when v_sal >=3000 then
dbms_output.put_line(‘工资为:b’) ;
when v_sal >=2000 then
dbms_output.put_line(‘工资为:c’) ;
else
dbms_output.put_line(‘工资为:d’) ;
end case;
end;

case then 也可以使用在sql语句中
select empno,ename,(case job when ‘MANAGER’ then ‘经理’ when ‘SALESMANE’ then ‘销售’ else ‘其他’ end ) as job_name
from emp;

循环结构loop

1，基本loop
从1到10
declare
i number:=0;
begin
loop
dbms_output.put_line(‘数字是’||i) ;
i:=i+1;
exit when i>10;
end loop;
end;

求和
declare
i number:=1;
s number:=0;
begin
loop
s:=s+i;
i:=i+1;
exit when i>100;
end loop;
dbms_output.put_line(‘数字是’||s) ;
end;
2，for loop
输出1到10
declare
begin
for i in 0…10
loop
dbms_output.put_line(‘数字是’||i) ;
end loop;
end;

反向输出
declare
begin
for i in reverse 0…10
loop
dbms_output.put_line(‘数字是’||i) ;
end loop;
end;

计算数字和
declare
s number :=0;
begin
for i in reverse 0…10
loop
s:=s+i;
end loop;
dbms_output.put_line(‘数字是’||s) ;
end;

3，while loop
declare
i number :=0;
begin
while i<=10
loop
dbms_output.put_line(‘数字是’||i) ;
i:=i+1;
end loop;
end;

while求和
declare
i number :=1;
s number :=0;
begin
while i<=100
loop
s:=s+i;
i:=i+1;
end loop;
dbms_output.put_line(‘数字是’||s) ;
end;

嵌套案例乘法表

declare
begin
for i in 1…9
loop
for j in 1…i
loop
dbms_output.put(j||’’||i||’=’||(ji)||’ ') ;
end loop;
dbms_output.new_line;
end loop;
end;

declare
j number:=1;
begin
for i in 1…9
loop
j:=1;–初始化
while j<=i
loop
dbms_output.put(j||’’||i||’=’||(ji)||’ ') ;
j:=j+1;
end loop;
dbms_output.new_line;
end loop;
end;

游标–一个内存区

游标属性

declare
begin
insert into emp(empno,ename) values(770,‘张三’);
if sql%found then
dbms_output.put_line(‘更新成功’||sql%rowcount||‘行’) ;
commit;
else
dbms_output.put_line(‘更新失败’||sql%rowcount||‘行’) ;
rollback;
end if;
end;

隐式游标

更新语句insert update deletew
单行查询语句

显示游标–需要声明

针对查询语句

提取一次游标案例
declare
–定义游标
cursor emp_1 is select ename,sal from emp where empno=7788;
–定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
–打开游标
open emp_1;
–提取数据
fetch emp_1 into v_ename,v_sal;
dbms_output.put_line(‘数据’||v_ename||’,’||v_sal) ;
–关闭游标
close emp_1;
end;

loop提示工资前三位 1，通过变量定义
declare
–定义游标
cursor emp_1 is select ename,sal from emp order by sal desc;
–定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
–打开游标
open emp_1;
–提取数据
for i in 1…3
loop
fetch emp_1 into v_ename,v_sal;
dbms_output.put_line(‘数据’||v_ename||’,’||v_sal) ;
end loop;
–关闭游标
close emp_1;
end;

2，通过游标变量
declare
–定义游标
cursor emp_1 is select ename,sal from emp order by sal desc;
–定义变量
c_emp emp_1%rowtype;
begin
–打开游标
open emp_1;
–提取数据
for i in 1…3
loop
fetch emp_1 into c_emp;
dbms_output.put_line(‘数据’||c_emp.ename||’,’||c_emp.sal) ;
end loop;
–关闭游标
close emp_1;
end;

3，通过表定义游标变量
declare
–定义游标
cursor emp_1 is select ename,sal from emp order by sal desc;
–定义变量
c_emp emp%rowtype;
begin
–打开游标
open emp_1;
–提取数据
for i in 1…3
loop
fetch emp_1 into c_emp.ename,c_emp.sal;
dbms_output.put_line(‘数据’||c_emp.ename||’,’||c_emp.sal) ;
end loop;
–关闭游标
close emp_1;
end;

显示游标属性

游标循环1 loop

declare
–定义游标
cursor emp_1 is select ename,sal from emp order by sal desc;
–定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
–打开游标,游标数据才被传入游标变量
open emp_1;
–提取数据
loop
fetch emp_1 into v_ename,v_sal;
exit when emp_1%notfound;
dbms_output.put_line(‘数据’||v_ename||’,’||v_sal) ;
end loop;
–关闭游标
close emp_1;
end;

游标循环2 for

declare
–定义游标
cursor emp_cursor is select ename,sal from emp order by sal desc;
begin
for emp_record in emp_cursor loop
dbms_output.put_line(emp_record.ename||’,’||emp_record.sal) ;
end loop;
end;

游标循环3 for

begin
for re in (select ename from emp) loop
dbms_output.put_line(re.ename) ;
end loop;
end;

带参游标

部门号是xxx 职位是xxx
declare
–定义游标
cursor emp_1(p_deptno emp.deptno%type,p_job varchar2) is
select ename,sal from emp where deptno=p_deptno and job=p_job order by sal desc;
–定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
–打开游标,游标数据才被传入游标变量
open emp_1(10,‘MANAGER’);
–提取数据
loop
fetch emp_1 into v_ename,v_sal;
exit when emp_1%notfound;
dbms_output.put_line(‘数据’||v_ename||’,’||v_sal) ;
end loop;
–关闭游标
close emp_1;
end;

通过变量进行传参
declare
p_deptno emp.deptno%type:=10;
p_job varchar2(20):=‘MANAGER’;
–定义游标
cursor emp_1 is
select ename,sal from emp where deptno=p_deptno and job=p_job order by sal desc;
–定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
–打开游标,游标数据才被传入游标变量
open emp_1;
–提取数据
loop
fetch emp_1 into v_ename,v_sal;
exit when emp_1%notfound;
dbms_output.put_line(‘数据’||v_ename||’,’||v_sal) ;
end loop;
–关闭游标
close emp_1;
end;

动态select传值 sql语句可以变化

一定返回是一行语句

declare
–定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
–定义动态sql语句
str varchar2(400);
begin
str:=‘select ename,sal from emp where empno=7788’;
str:=‘select ename,sal from emp where empno=770’;
–执行动态sql
execute immediate str into v_ename,v_sal;
dbms_output.put_line(‘数据’||v_ename||’,’||v_sal) ;
end;

动态游标

sql语句返回多条数据，
declare
–定义动态游标
type my_cursor is ref cursor;
–定义游标型变量m_c
m_c my_cursor;
–动态sql语句
str varchar2(400);
p_deptnp number:=20;
p_job varchar2(20):=‘MANAGER’;
–记录型变量
emp_record emp%rowtype;
begin
str:=‘select * from emp where deptno=’||p_deptnp||‘and job=’’’||p_job||’’’’;
dbms_output.put_line(str);
open m_c for str;
loop
–提取数据
fetch m_c into emp_record;
exit when m_c%notfound;
dbms_output.put_line(‘员工名’||emp_record.ename||’,工资：’||emp_record.sal) ;
end loop;
–关闭游标
end;

异常处理

希望程序继续执行下去
DECLARE
v_name VARCHAR2(10) ;
BEGIN
SELECT ename INTO v_name FROM
emp
WHERE empno = 1234;
DBMS_OUTPUT.PUT_LINE(‘该雇员名字为:’|| v_name) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘编号错误，没有找到相应雇员!’||SQLCODE||’,’||SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘发生其他错误!’);
END;

存储过程

无名快
declare
begin
end

起名字，以后直接使用名称

创建存储过程

declare
total number:=0;
begin
select count(*) into total from emp;
dbms_output. put_line(‘总人数:’||total) ;
end;

就是把上面的无名快，起个名字
create or replace procedure p_emp_total
as
total number:=0;
begin
select count(*) into total from emp;
dbms_output. put_line(‘总人数:’||total) ;
end;

使用存储过程

1，无名块中调用
begin
– Call the procedure
p_emp_total;
end;
2，execute
execute p_emp_total
3，在其他存储过程中相互调用
create or replace procedure printEmp
is
cursor emp_cursor is
select ename，job, sal from emp;
begin
dbms_output.put_line(‘姓名’||‘职位’||‘薪水’);
for emp_record in emp_cursor
loop
dbms_output.put_line(emp_record.ename||’ ‘||emp_record.job ||’ '|| emp_record.sal) ;
end loop;
–调用存储过程
p_emp_total ;
end;
execute printEmp

存储过程参数

in 定义一个输入参数变量，用于传递参姿给存诸程
out 定义一个输出参数变量，用于从存储程获郓瘘对居
in out 定义一个输入、输出参娄变量，兼有以上两者的能

in变量

编写给雇员增加工资的存储过程CHANGE_SALARY，通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。
create or replace procedure
change_salary(p_empno in emp.empno%type default 8888,p_raise number default 100)
as
v_ename varchar2(20) ;
begin
select ename into v_ename from emp where empno=p_empno;
update emp set sal=sal+p_raise where empno=p_empno;
dbms_output.put_line(‘员工号是:’||p_empno||‘姓名是:’||v_ename||‘涨薪水:’||p_raise) ;
commit;
exception
when others then
dbms_output.put_line(‘数据库提取数据失败…’);
rollback;
end;

调用指定参数
execute change_salary(770,2000);
execute change_salary(p_empno =>770,p_raise=>2000);

使用out变量获取值

create or replace procedure
change_salary(p_empno in emp.empno%type default 8888,p_raise number default 100,v_deptno out number)
as
v_ename varchar2(20) ;
begin
–v_deptno:=50;
select ename,v_ename into v_ename,v_deptno from emp where empno=p_empno;
update emp set sal=sal+p_raise where empno=p_empno;
dbms_output.put_line(‘员工号是:’||p_empno||‘姓名是:’||v_ename||‘涨薪水:’||p_raise) ;
exception
when others then
dbms_output.put_line(‘数据库提取数据失败…’);
end;

declare
v_deptno number;
begin
change_salary(p_empno=>777,p_raise=>2000, v_deptno=>v_deptno) ;
dbms_output.put_line(‘部门编号是:’||v_deptno) ;
end;

in out

create or replace procedure change_phone (phone in out varchar2)
is
begin
dbms_output.put_line(‘传入的电话号是:’||phone) ;
phone :=’+86’||phone ;
end;

create or replace procedure test_inout
is
phone varchar2(20):=‘13756305180’;
begin
change_phone (phone) ;
dbms_output.put_line(‘电话号被修改为:’||phone) ;
end;

存储函数

求两个数的和

create or replace function two_sum(x number, y number)
return number
is
begin
return (x+y);
exception when others then
dbms_output.put_line(‘数字相加时出现异常。。。。。’);
end two_sum;

select two_sum(300,400) from dual;
select upper(‘aaa’) from dual;

create or replace function get_emp_name(v_empno number)
return varchar2
is
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=v_empno ;
return v_ename ;
exception when others then
dbms_output.put_line(‘查询员工时时出现异常。。。。。’);
end;

declare
v_ename emp.ename%type;
begin
v_ename :=get_emp_name(7788) ;
dbms_output.put_line(‘员工姓名是:’||v_ename) ;
end;

包头+包体

包头
create or replace package emp_package
is
Pl constant number :=3.1415926;–声明常量
type my_table is table of varchar2(20) index by binary_integer;–声明table集合变量t
ype my_cursor is ref cursor ; --声明全局的动态游标
el exception;–声明异常
function two_sum(x number, y number) return number;–声明求和函数
procedure change_phone(phone in out varchar2) ; --声明过程
end emp_package;

包体
create or replace package body emp_package
is
–定义two_sum函数
function two_sum(x number, y number) return number
is
begin
return (x+y) ;
exception when others then
dbms_output.put_line(‘数字相加时出现异常。。。。。’) ;
end two_sum;
–定义存储过程
procedure change_phone (phone in out varchar2)
is
begin
dbms_output.put_line(‘传入的电话号是:’||phone) ;
phone :=’+86’||phone ;
end change_phone;
end emp_package ;

使用包 包名.对象
select emp_package.two_sum(8000,100) from dual ;