/*
回顾:
1.同义词:简化SQL语句
2.序列:生成连续的数字
3、视图:
CREATE OR REPLACE VIEW 视图名
as
查询语句
[with check option] 对视图进行修改时,不能使视图的查询结果集行数变少,一般针对视图的查询语句带条件
[with read only]; 只读
视图的操作:
查询视图
修改视图:当视图的查询语句是多表连接查询时,只能修改外键表的数据
4.索引:提高查询的速度
CREATE INDEX 索引名 ON 表名(列名);
目标:存储过程
PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言
PL/SQL 是对 SQL 的扩展
支持多种数据类型,如大对象和集合类型,可使用条件和循环等控制结构
可用于创建存储过程、触发器和程序包,给SQL语句的执行添加程序逻辑
与 Oracle 服务器和 Oracle 工具紧密集成,具备可移植性、灵活性和安全性
优点:
1、可以编写逻辑代码
2、PL/SQL中的代码自动形成一个事物,出现异常就会一起回滚
3、更佳的性能,减少客户端对ORACLE服务器端的访问量,PL/SQL 经过编译执行的
*/
/*
存储过程(procedure):命名的PL/SQL代码快,类似JAVA的方法
一个功能定义到一个存储过程中,需要使用时直接调用
条件结构
IF 条件 THEN
语句;
ELSIF 条件 THEN
语句;
ELSIF 条件 THEN
语句;
....
ELSE
语句N;
END IF;
*/
/*
转账:
1、修改 对方账户 余额增加
2、修改 本账户 余额减少
3、添加 一条 交易记录
*/
--1、创建存储过程:输出“下午好!”
CREATE OR REPLACE PROCEDURE PROC1
AS
BEGIN
DBMS_OUTPUT.put_line('下午好!');
END;
--编写SQL语句调用存储过程
CALL PROC1();
--2、创建一个存储过程,用来查询考试的平均分,如果平均分大于80,显示优秀,60-80良好 <60差
CREATE OR REPLACE PROCEDURE PROC1
AS
--AVGSCORE NUMBER; --定义局部变量,语法是:变量名 数据类型
--变量的数据类型与表的列进行映射 语法是: 变量名 表名.列名%TYPE
AVGSCORE SCORE.SCORE%TYPE; -- 变量AVGSCORE的类型同 score表的列score的类型
BEGIN
--为变量赋固定的值:AVGSCORE:=20;
--查询平均分并为变量赋值
SELECT AVG(SCORE) INTO AVGSCORE FROM SCORE;
DBMS_OUTPUT.put_line('平均分是:'||AVGSCORE);
IF AVGSCORE>80 THEN
DBMS_OUTPUT.put_line('优秀');
ELSIF AVGSCORE>=60 THEN
DBMS_OUTPUT.put_line('良好!');
ELSE
DBMS_OUTPUT.put_line('差');
END IF;
END PROC1;
--调用存储过程
CALL PROC1();
--3.根据指定的学号查询的学生的信息,如果存在输出学生信息,不存在提示对应的信息
--带参数的存储过程
CREATE OR REPLACE PROCEDURE PROC1(id student.stuid%type)
AS
--变量与表中的一行数据进行映射,语法:变量名 表名%rowtype;
stu student%rowtype;
num int; --存放根据学号查询的记录行数
begin
select count(*) into num from student where stuid=id;
--判断
if num=0 then
dbms_output.put_line('学号不存在!');
else
select * into stu from student where stuid=id;
dbms_output.put_line(stu.stuid||'-'||stu.stuname);
end if;
end proc1;
--调用存储过程并传参数
call proc1(0);
--循环:
--LOOP循环
/*
LOOP
EXIT WHEN 退出循环的条件表达式;
执行语句;
END LOOP;
*/
--循环输出1-10
create or replace procedure proc1
as
i int:=1; --定义变量并赋初值
begin
LOOP
EXIT WHEN I>10;
dbms_output.put_line(I);
I:=I+1;
END LOOP;
end proc1;
--
CALL PROC1();
--WHILE循环
/*
WHILE循环语法:
WHILE 进行循环条件
LOOP
执行语句;
END LOOP;
*/
create or replace procedure proc1
as
i int:=1; --定义变量并赋初值
begin
WHILE i<=10
LOOP
dbms_output.put_line(I);
I:=I+2;
END LOOP;
end proc1;
--
CALL PROC1();
--for循环
/*
FOR 变量 IN 起始值..结束值
LOOP
执行语句;
END LOOP;
*/
create or replace procedure proc1
as
begin
for i in 1..10
loop
dbms_output.put_line(I);
end loop;
end;
--
call proc1();
--定义存储过程向学生信息表添加20行测试数据
create or replace procedure proc1
as
begin
for i in 1..20
loop
insert into student values(i,'测试'||i,'男',20,1.68,sysdate);
end loop;
end;
--
call proc1();
--判断某张表是否存在,如果存在就删除
CREATE OR REPLACE PROCEDURE CHECKTABLE(TABLENAME VARCHAR)
AS
NUM INT;
BEGIN
SELECT COUNT(*) INTO NUM FROM USER_TABLES WHERE TABLE_NAME=TABLENAME;
IF NUM>0 THEN
--删除表
--DROP TABLE TABLENAME;
EXECUTE IMMEDIATE 'DROP TABLE '||TABLENAME;
END IF;
END;
--注意:PL/SQL中不支持DDL语句:create drop,需要使用命令 EXECUTE IMMEDIATE 'DDL语句' 执行
CALL CHECKTABLE('ABC');
CREATE TABLE ABC
(
EMPID INT
);
SELECT * FROM USER_TABLES WHERE TABLE_NAME='ABC';
SELECT * FROM STUDENT;
SELECT * FROM SCORE;
commit;
回顾:
1.同义词:简化SQL语句
2.序列:生成连续的数字
3、视图:
CREATE OR REPLACE VIEW 视图名
as
查询语句
[with check option] 对视图进行修改时,不能使视图的查询结果集行数变少,一般针对视图的查询语句带条件
[with read only]; 只读
视图的操作:
查询视图
修改视图:当视图的查询语句是多表连接查询时,只能修改外键表的数据
4.索引:提高查询的速度
CREATE INDEX 索引名 ON 表名(列名);
目标:存储过程
PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言
PL/SQL 是对 SQL 的扩展
支持多种数据类型,如大对象和集合类型,可使用条件和循环等控制结构
可用于创建存储过程、触发器和程序包,给SQL语句的执行添加程序逻辑
与 Oracle 服务器和 Oracle 工具紧密集成,具备可移植性、灵活性和安全性
优点:
1、可以编写逻辑代码
2、PL/SQL中的代码自动形成一个事物,出现异常就会一起回滚
3、更佳的性能,减少客户端对ORACLE服务器端的访问量,PL/SQL 经过编译执行的
*/
/*
存储过程(procedure):命名的PL/SQL代码快,类似JAVA的方法
一个功能定义到一个存储过程中,需要使用时直接调用
条件结构
IF 条件 THEN
语句;
ELSIF 条件 THEN
语句;
ELSIF 条件 THEN
语句;
....
ELSE
语句N;
END IF;
*/
/*
转账:
1、修改 对方账户 余额增加
2、修改 本账户 余额减少
3、添加 一条 交易记录
*/
--1、创建存储过程:输出“下午好!”
CREATE OR REPLACE PROCEDURE PROC1
AS
BEGIN
DBMS_OUTPUT.put_line('下午好!');
END;
--编写SQL语句调用存储过程
CALL PROC1();
--2、创建一个存储过程,用来查询考试的平均分,如果平均分大于80,显示优秀,60-80良好 <60差
CREATE OR REPLACE PROCEDURE PROC1
AS
--AVGSCORE NUMBER; --定义局部变量,语法是:变量名 数据类型
--变量的数据类型与表的列进行映射 语法是: 变量名 表名.列名%TYPE
AVGSCORE SCORE.SCORE%TYPE; -- 变量AVGSCORE的类型同 score表的列score的类型
BEGIN
--为变量赋固定的值:AVGSCORE:=20;
--查询平均分并为变量赋值
SELECT AVG(SCORE) INTO AVGSCORE FROM SCORE;
DBMS_OUTPUT.put_line('平均分是:'||AVGSCORE);
IF AVGSCORE>80 THEN
DBMS_OUTPUT.put_line('优秀');
ELSIF AVGSCORE>=60 THEN
DBMS_OUTPUT.put_line('良好!');
ELSE
DBMS_OUTPUT.put_line('差');
END IF;
END PROC1;
--调用存储过程
CALL PROC1();
--3.根据指定的学号查询的学生的信息,如果存在输出学生信息,不存在提示对应的信息
--带参数的存储过程
CREATE OR REPLACE PROCEDURE PROC1(id student.stuid%type)
AS
--变量与表中的一行数据进行映射,语法:变量名 表名%rowtype;
stu student%rowtype;
num int; --存放根据学号查询的记录行数
begin
select count(*) into num from student where stuid=id;
--判断
if num=0 then
dbms_output.put_line('学号不存在!');
else
select * into stu from student where stuid=id;
dbms_output.put_line(stu.stuid||'-'||stu.stuname);
end if;
end proc1;
--调用存储过程并传参数
call proc1(0);
--循环:
--LOOP循环
/*
LOOP
EXIT WHEN 退出循环的条件表达式;
执行语句;
END LOOP;
*/
--循环输出1-10
create or replace procedure proc1
as
i int:=1; --定义变量并赋初值
begin
LOOP
EXIT WHEN I>10;
dbms_output.put_line(I);
I:=I+1;
END LOOP;
end proc1;
--
CALL PROC1();
--WHILE循环
/*
WHILE循环语法:
WHILE 进行循环条件
LOOP
执行语句;
END LOOP;
*/
create or replace procedure proc1
as
i int:=1; --定义变量并赋初值
begin
WHILE i<=10
LOOP
dbms_output.put_line(I);
I:=I+2;
END LOOP;
end proc1;
--
CALL PROC1();
--for循环
/*
FOR 变量 IN 起始值..结束值
LOOP
执行语句;
END LOOP;
*/
create or replace procedure proc1
as
begin
for i in 1..10
loop
dbms_output.put_line(I);
end loop;
end;
--
call proc1();
--定义存储过程向学生信息表添加20行测试数据
create or replace procedure proc1
as
begin
for i in 1..20
loop
insert into student values(i,'测试'||i,'男',20,1.68,sysdate);
end loop;
end;
--
call proc1();
--判断某张表是否存在,如果存在就删除
CREATE OR REPLACE PROCEDURE CHECKTABLE(TABLENAME VARCHAR)
AS
NUM INT;
BEGIN
SELECT COUNT(*) INTO NUM FROM USER_TABLES WHERE TABLE_NAME=TABLENAME;
IF NUM>0 THEN
--删除表
--DROP TABLE TABLENAME;
EXECUTE IMMEDIATE 'DROP TABLE '||TABLENAME;
END IF;
END;
--注意:PL/SQL中不支持DDL语句:create drop,需要使用命令 EXECUTE IMMEDIATE 'DDL语句' 执行
CALL CHECKTABLE('ABC');
CREATE TABLE ABC
(
EMPID INT
);
SELECT * FROM USER_TABLES WHERE TABLE_NAME='ABC';
SELECT * FROM STUDENT;
SELECT * FROM SCORE;
commit;