前端之家收集整理的这篇文章主要介绍了
oracle的PLSQL基础,
前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
1. PL/sql
1.1 PL/sql中的类型
a)标量类型(数字,字符,日期,布尔)
类型名 sql中的范围 PL/sql中的范围
char 1...2000 1...32767
varchar2 1...4000 1...32767
raw 1...2000 1...32767
long 1...2G 1...32760
long raw 1...2G 1...32760
b)LOB(CLOB,BLOB,NCLOB,BFILE)
c) %type,%rowtype
2. PL/sql(块)语法
declare
-- 声明部分
begin
-- 可执行部分
exception
-- 异常部分
end;
declare
name varchar2(20) :=
age number;
begin
name :=
age := &inputage;
dbms_output.put_line(
end;
3. 给变量赋值
:=
select ... into ... from ... where ...
----------------------------------------------------------------------
declare
name varchar2(20);
job varchar2(20);
begin
select ename,job into name,job from emp where empno = 7788;
dbms_output.put_line(
end;
-- 属性类型
%type %rowtype
declare
name emp.ename%type; -- 和emp.ename的类型和长度一致
a_row emp%rowtype;
begin
select ename into name from emp where empno = 7369;
-- rowtype需要查询出*来赋值
select * into a_row from emp where empno = 7788;
dbms_output.put_line(name ||
dbms_output.put_line(
end;
4. 分支
4.1 if
declare
age number(8);
begin
age := &age;
if age < 18 then
dbms_output.put_line(
elsif age < 30 then
dbms_output.put_line(
else
dbms_output.put_line(
end if;
end;
4.2 case
-- 用法一: switch
declare
monthValue number;
begin
monthValue := &mv;
case monthValue
when 1 then
dbms_output.put_line(
when 2 then
dbms_output.put_line(
when 3 then
dbms_output.put_line(
when 4 then
dbms_output.put_line(
when 5 then
dbms_output.put_line(
when 6 then
dbms_output.put_line(
else
dbms_output.put_line(
end case;
end;
-- 用法二: if else
declare
age number(8);
begin
age := &age;
case
when age < 18 then
dbms_output.put_line(
when age < 30 then
dbms_output.put_line(
else
dbms_output.put_line(
end case;
end;
5. 循环
loop循环
declare
x number := 0;
begin
loop exit when x >= 100;
x := x + 1;
dbms_output.put_line(
/*
if x >= 100 then
exit;
end if;
*/
end loop;
end;
while循环
declare
x number := 0;
begin
while x < 100 loop
x := x + 1;
dbms_output.put_line(
end loop;
end;
for循环
begin
for i in 10..20 loop -- for的临时变量值可以不用在声明块中定义
dbms_output.put_line(
end loop;
end;
-- 九九乘法表
begin
for i in 1..9 loop
for j in 1..i loop
dbms_output.put(
end loop;
dbms_output.put_line(
end loop;
end;
6. 顺序控制
goto 无条件跳转
null 空语句
7. 动态sql
declare
table_name varchar2(20);
tn_ddl varchar2(200);
begin
table_name :=
tn_ddl :=
execute immediate tn_ddl;
end;
declare
tn varchar2(20); -- 查询的目标表
cl1 varchar2(20); -- 查询的目标列
cl2 varchar2(20); -- 查询的目标列
wcl varchar2(20); -- 条件列
wva varchar2(20); -- 条件值
rv1 varchar2(20); -- 查询结果
rv2 varchar2(20); -- 查询结果
query_sql varchar2(200);
begin
tn :=
cl1 :=
cl2 :=
wcl :=
wva :=
-- select ename,job from emp where empno = :wva
query_sql :=
dbms_output.put_line(query_sql);
execute immediate query_sql into rv1,rv2 using wva;
dbms_output.put_line(
end;
8. 异常处理
抛出异常
declare
-- 自定义异常一定要在declare块中先声明
my_exception exception;
day_flag varchar2(10);
i number;
begin
select to_char(sysdate,
-- 触发预定义异常
i := 10/0;
if day_flag < 15 then
-- 抛出自定义异常
raise my_exception;
end if;
-- 所有在程序中可能会发生的异常都可以在exception块中进行处理
exception
when my_exception then
dbms_output.put_line(
when zero_divide then
dbms_output.put_line(
end;
select * from user_tables;
select * from user_indexes;
select * from user_views;
select * from user_sequences;
select * from user_synonyms;
--游标eg
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||
end loop;
close c_emp;
end;
declare
cursor c_emp(dno number) is select ename,sal from emp where deptno=dno;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_count number;
v_dno number;
begin
v_dno:=&v_dno;
select count(*) into v_count from emp where deptno=v_dno;
dbms_output.put_line(
open c_emp(v_dno);
loop
fetch c_emp into v_ename,v_sal;
exit when c_emp%notfound;
dbms_output.put_line(v_ename||
end loop;
close c_emp;
end;
原文链接:https://www.f2er.com/oracle/212296.html