Oracle_PL/SQL的基本语法操作

5c22081p02

declare
temp_name varchar2(20):='hello world';
temp_num int :=1;
begin
--set serveroutput on
dbms_output.put_line(temp_num||'==='||temp_name);
end;
---if
declare
temp_num int :=&请输入num的值;
begin
if temp_num>5 then
dbms_output.put_line('temp_num>5');
elsif temp_num=5 then
dbms_output.put_line('temp_num==5');
else
dbms_output.put_line('temp_num<5');
end if;
end;
---case
declare
score char:='&num';
begin
case score
when 'A' then
dbms_output.put_line('very good');
when 'B' then
dbms_output.put_line('is good');
when 'C' then
dbms_output.put_line('is normal');
when 'D' then
dbms_output.put_line('is bad');
else
dbms_output.put_line('input a value');
end case;
end;
--loop
declare
temp_num int :=1;
begin
loop
if(temp_num>=10) then--出循环条件
exit;
end if;
dbms_output.put_line(temp_num);
temp_num:=temp_num+1;
end loop;
end;
--while
declare
temp_num int :=1;
begin
while temp_num<10 loop--进入循环条件
dbms_output.put_line(temp_num);
temp_num:=temp_num+1;
end loop;
end;
--for
declare
temp_num int :=1;
begin
for temp_num in  reverse 1..10 loop
dbms_output.put_line(temp_num);
end loop;
end;
---动态sql--ddl
declare
tbName varchar2(10):='my_tab';
begin
execute immediate '
create table '||tbName||' (
id int,
name varchar2(10),
age int
)';
end;

---绑定参数
declare
id int:=1;
tbName varchar2(10):='my_tab';
age int:=11;
uname varchar2(10):='my_tab';
begin
execute immediate
'insert into my_tab values(:1,:abc,:2) returning name into :uname'
using id,tbName,age returning into uname
;
end;
--execute immediate 字符串 using 输入参数 returning into 返回值参数
/*
Excute immediate 动态SQL语句 using 绑定参数列表 returning into 输出参数列表;
DML = Data Manipulation Language 数据操作语句[m'nipjulbl]
DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言
DCL（Data Control Language）：.?是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL
TCL（Transaction Control Language）事务控制语言 .SAVEPOINT 设置保存点 .ROLLBACK? 回滚 .SET TRANSACTION .
使用动态SQL原因
1.业务：只能在运行时才能确定sql。
2.在开发动态拼装SQL，更灵活，更强大。
3.更容易维护。
*/
----系统exception
declare
uname varchar2(10);
begin
select name into uname from users where id=2;
case '22'
when '1' then uname:='1';
when '2' then uname:='2';
end case;
dbms_output.put_line(uname);
exception
when too_many_rows then
dbms_output.put_line('查询到多行数据');
when others then
dbms_output.put_line('异常');
end;
---自定义异常
declare
n int:=&5;
e exception;--定义
begin
if(n=5) then
raise e;--手动抛出
end if;
exception--处理
when e then
dbms_output.put_line('自定义异常');
end;
---隐式游标
declare
uname varchar2(10);
begin
select name into uname from users where id=2;
dbms_output.put_line('查询到'||sql%rowcount);
if(sql%found)then
dbms_output.put_line('found is true');
else
dbms_output.put_line('found is false');
end if;
if(sql%notfound)then
dbms_output.put_line('notfound is true');
else
dbms_output.put_line('notfound is false');
end if;
if(sql%isopen)then
dbms_output.put_line('isopen is true');
else
dbms_output.put_line('isopen is false');
end if;
end;
---%type(字段类型)
---%rowtype
declare
uname users.name%type;
myrow users%rowtype;--行类型
begin
select name into uname from users where id=2;
select * into myrow from users where id=2;
dbms_output.put_line(uname);
dbms_output.put_line(myrow.name||'==='||myrow.password);
end;
---显示游标
declare
cursor my_cursor  is
select * from users;--声明
myrow users%rowtype;
begin
open my_cursor;--打开
if(my_cursor%notfound) then
dbms_output.put_line('无数据');
else
fetch my_cursor into myrow;--取数据
dbms_output.put_line(myrow.id||'==='||myrow.name||'==='||myrow.password);
end if;
close my_cursor;--关闭
end;
--while循环
declare
cursor my_cursor  is
select * from users;--声明
myrow users%rowtype;
begin
open my_cursor;--打开
fetch my_cursor into myrow;--取数据
while (my_cursor%found) loop
dbms_output.put_line(myrow.id||'==='||myrow.name||'==='||myrow.password);
fetch my_cursor into myrow;
end loop;
close my_cursor;--关闭
end;
--for循环
declare
cursor my_cursor  is
select * from users;--声明
myrow users%rowtype;
begin
for myrow in my_cursor loop
dbms_output.put_line(myrow.id||'==='||myrow.name||'==='||myrow.password);
end loop;
end;
--loop循环
declare
cursor my_cursor is select * from info;
myrow info%rowtype;
begin
open my_cursor;
fetch my_cursor into myrow;
loop
if my_cursor%found then
dbms_output.put_line(myrow.id);
fetch my_cursor into myrow;
else
exit;
end if;
end loop;
close my_cursor;
end;