alter user scott
identified by tiger
account unlock
/
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename='张三';
dbms_output.put_line('v_sal is'||v_sal);
end;
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7369;
dbms_output.put_line('v_sal is'||v_sal);
end;
declare
v_sal emp.sal%type;
v_count number;
begin
select count(*) into v_count from emp where ename='SMITH';
if v_count=1 then
select sal into v_sal from emp where ename='SMITH';
dbms_output.put_line('v_sal is'||v_sal);
end if;
end;
declare
v_sal emp.sal%type;
begin
update emp set sal=10003 where empno=7369;
commit;
select sal into v_sal from emp where empno=7369;
dbms_output.put_line('v_sal is'||v_sal);
end;
declare
v_sal emp.sal%type;
v_count number;
begin
select count(*) into v_count from emp where empno=2234;
if v_count=1 then
update emp set sal=10003 where empno=2234;
else
dbms_output.put_line('chawuciren');
end if;
end;
declare
begin
update emp set sal=10003 where empno=2234;
if sql%notfound then
dbms_output.put_line('chawuciren');
end if;
end;
declare
salaray number:=2000;
begin
if salaray<1000 then
dbms_output.put_line('no tax');
else if salaray between 1000 and 2000 then
dbms_output.put_line('10%');
else if salaray between 2001 and 3000 then
dbms_output.put_line('20%');
else
dbms_output.put_line('30%');
end if;
end if;
end if;
end;
declare
salaray number:=2000;
begin
if salaray<1000 then
dbms_output.put_line('no tax');
elsif salaray between 1000 and 2000 then
dbms_output.put_line('10%');
elsif salaray between 2001 and 3000 then
dbms_output.put_line('20%');
else
dbms_output.put_line('30%');
end if;
end;
declare
x number:=null;
y number:=null;
begin
if x=y then
dbms_output.put_line('no tax');
else
dbms_output.put_line('jibajibajbiabijabjia');
end if;
if x!=5 then
dbms_output.put_line('heldl');
else
dbms_output.put_line('dfdfafdfdfdf');
end if;
if x=5 then
dbms_output.put_line('heldl');
else
dbms_output.put_line('dfdgdfdfdfdtere');
end if;
end;
输出结果:
jibajibajbiabijabjia
dfdfafdfdfdf
dfdgdfdfdfdtere
declare
begin
update emp set sal=
case deptno
when 10 then sal*1.1
when 20 then sal*1.2
when 30 then sal*1.3
else sal*10
end
where empno=7369;
end;
declare
begin
update emp set sal=
case
when deptno=10 then sal*1.1
when deptno=20 then sal*1.2
when deptno=30 then sal*1.3
else sal*10
end
where empno=7369;
end;
declare
v_sal emp.sal%type;
v_deptno emp.deptno%type;
begin
select sal into v_sal from emp where empno=7369;
v_sal:=case
when v_deptno=10 then v_sal*1.1
when v_deptno=20 then v_sal*1.2
when v_deptno=30 then v_sal*1.3
else v_sal*1.5
end;
dbms_output.put_line(v_sal);
end;
create table item (id number);
declare
i item.id%type;
begin
i:=0;
loop
insert into item(id) values(i);
i:=i+1;
exit when i>10;
end loop;
--select * from item;
end;
declare
i item.id%type;
begin
i:=11;
while i<20 loop
insert into item(id) values(i);
i:=i+1;
end loop;
--select * from item;
end;
declare
i item.id%type;
begin
for i in 0..20 loop
insert into item(id) values(i);
end loop;
--select * from item;
end;
declare
type emp_temp is record
(name emp.ename%type,
job emp.job%type,
sal emp.sal%type);
user emp_temp;
begin
select ename,job,sal into user.name, user.job,user.sal from emp where empno=7369;
dbms_output.put_line(user.name);
dbms_output.put_line(user.job);
dbms_output.put_line(user.sal);
end;
declare
user emp%rowtype;
begin
select ename,job,sal into user.ename, user.job,user.sal from emp where empno=7369;
dbms_output.put_line(user.ename);
dbms_output.put_line(user.job);
dbms_output.put_line(user.sal);
end;
使用table
declare
user emp%rowtype;
type emp_temp is table of
user%type
index by pls_integer;
user_table emp_temp;
begin
select ename,job,sal into user_table(1).ename, user_table(1).job,user_table(1).sal from emp where empno=7369;
dbms_output.put_line(user_table(1).ename);
dbms_output.put_line(user_table(1).job);
dbms_output.put_line(user_table(1).sal);
end;
定义结构体数组,并用while and for 循环
declare
user emp%rowtype;
type emp_temp is table of
user%type
index by pls_integer;
user_table emp_temp;
i number:=0;
counta number:=0;
begin
while i<10000 loop
select count(*) into counta from emp where empno=i;
if counta =1 then
select ename,job,sal into user_table(i).ename, user_table(i).job,user_table(i).sal from emp where empno=i;
dbms_output.put_line(user_table(i).ename);
dbms_output.put_line(user_table(i).job);
dbms_output.put_line(user_table(i).sal);
end if;
i:=i+1;
end loop;
--print way 1
while i>0 loop
if user_table.exists(i) then
dbms_output.put_line(user_table(i).ename);
end if;
i:=i-1;
end loop;
--print way 2
for m in user_table.first..user_table.last
loop
if user_table.exists(m) then
dbms_output.put_line(user_table(m).sal);
end if;
end loop;
end;
--使用游标用loop循环
declare
cursor emps is select empno, ename,job,sal from emp;
user emps%rowtype;
begin
open emps;
loop
fetch emps into user;
exit when emps%notfound;
insert into empdest(empno, ename,job,sal)values(user.empno,user.ename,user.job,user.sal);
--insert into empdest values user;
dbms_output.put_line(user.ename);
dbms_output.put_line(user.job);
dbms_output.put_line(user.sal);
end loop;
end;
--使用游标用while循环
declare
cursor emps is select empno, ename,job,sal from emp;
user emps%rowtype;
begin
open emps;
fetch emps into user;
while emps%found loop
insert into empdest(empno, ename,job,sal)values(user.empno,user.ename,user.job,user.sal);
--insert into empdest values user;
dbms_output.put_line(user.ename);
dbms_output.put_line(user.job);
dbms_output.put_line(user.sal);
fetch emps into user;
end loop;
close emps;
end;
--使用游标用for循环
declare
cursor emps is select empno, ename,job,sal from emp;
user emps%rowtype;
begin
for user in emps loop
insert into empdest(empno, ename,job,sal)values(user.empno,user.ename,user.job,user.sal);
--insert into empdest values user;
dbms_output.put_line(user.ename);
dbms_output.put_line(user.job);
dbms_output.put_line(user.sal);
end loop;
end;
--使用隐含游标用for循环
declare
begin
for user in (select empno, ename,job,sal from emp )
loop
insert into empdest(empno, ename,job,sal)values(user.empno,user.ename,user.job,user.sal);
--insert into empdest values user;
dbms_output.put_line(user.ename);
dbms_output.put_line(user.job);
dbms_output.put_line(user.sal);
end loop;
end;
--参数传递
declare
cursor emps(p_deptno number) is select empno, ename,job,sal from emp where deptno =p_deptno;
user emps%rowtype;
begin
for user in emps(10) loop
insert into empdest(empno, ename,job,sal)values(user.empno,user.ename,user.job,user.sal);
--insert into empdest values user;
dbms_output.put_line(user.ename);
dbms_output.put_line(user.job);
dbms_output.put_line(user.sal);
end loop;
end;
--选择并更新(慎用 死锁)
declare
cursor emps is select empno, ename,job,sal from empdest for update;
user emps%rowtype;
begin
for user in emps
loop
insert into emp2(empno, ename,job,sal)values(user.empno,user.ename,user.job,user.sal);
--insert into empdest values user;
dbms_output.put_line(user
PLSQL.rar_ADO PLSQL
版权申诉
49 浏览量
2022-09-23
10:07:17
上传
评论
收藏 3KB RAR 举报
weixin_42651887
- 粉丝: 79
- 资源: 1万+
最新资源
- Screenshot_20240522_084328_com.tencent.mm.jpg
- 附件计算机专业课选课说明-1.xlsx
- 基于TypeScript的ahousepet-admin-web管理系统设计源码
- 《广东开放大学学习指引》期末考核要求0522.zip
- mkl-2021.1.1-py2.py3-none-macosx-10-15-x86-64.whl
- mkl-2018.0.3-py2.py3-none-manylinux1-x86-64.whl
- halcon实现两幅图像相减
- mkl-2021.3.0-py2.py3-none-manylinux1-x86-64.whl
- IAR9.3主题配置,包含字体颜色等
- 才从简来.apk
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈