oracle的plsql
显示打印结果set serverout on;
1、
declare
v_ename scott.emp1.ename%type;
v_emp1 scott.emp1%rowtype;
begin
select ename into v_ename from emp1 where empno=7788;
select * into v_emp1 from emp1 where empno=7788;
dbms_output.put_line('姓名:'|| v_ename);
dbms_output.put_line('工号:'|| v_emp1.empno);
end;
2、
declare
type ename_table_type is table of emp1.ename%type index by binary_integer;
ename_table ename_table_type;
begin
select ename into ename_table(1) from emp1 where empno=7788;
dbms_output.put_line(ename_table(1));
end;
3、
declare
type aaa is record (name emp1.ename%type,salary emp1.sal%type);
my_record aaa;
begin
select ename,sal into my_record from emp1 where empno=7788;
dbms_output.put_line('姓名：'||my_record.name);
end;
4、
declare
v_ename emp1.ename%type;
begin
select ename into v_ename from emp1 where empno=&emp1_empno;
dbms_output.put_line('姓名：'|| v_ename);
exception
when no_data_found then
dbms_output.put_line('输入编号有误，查无此人');
end;
5、
declare
v_table char(4) := &table_name;
v_sal emp1.sal%type;
v_sql varchar2(100);
begin
update emp1 set sal=sal+10000 where empno=7788;
select sal into v_sal from emp1 where empno=7788;
dbms_output.put_line('7788的工资是'|| v_sal);
v_sql := 'truncate table ' || v_table;
execute immediate v_sql;
dbms_output.put_line('表数据已清除');
end;
6、
declare
my_sql varchar2(500);
v_sal emp1.sal%type;
begin
my_sql :='insert into emp1 select * from emp';
execute immediate my_sql;
select sal into v_sal from emp1 where empno=&input_empno;
if v_sal<1500 then
dbms_output.put_line('涨工资');
else
dbms_output.put_line('不涨工资');
end if;
exception
when no_data_found then
dbms_output.put_line('查无此人');
end;
7、
declare
j number :=0;
begin
j:=1;
loop
dbms_output.put_line(j || '---');
exit when j>7;
j :=j+1;
end loop;
dbms_output.put_line('结束');
end;
8、
declare
j number :=0;
begin
j:=1;
while j<=8 loop
dbms_output.put_line(j || '---');
j :=j+1;
end loop;
dbms_output.put_line('结束');
end;
9、
begin
for j in 1.. 8 loop
dbms_output.put_line(j || '---');
exit when j>4;
end loop;
dbms_output.put_line('结束');
end;
10、
begin
for i in (select * from emp1) loop
dbms_output.put_line(i.ename);
end loop;
dbms_output.put_line('结束');
end;
11、
create or replace procedure proc1(i in number) as
a varchar2(50);
begin
a:='';
for j in 1.. i loop
a:=a || '*';
dbms_output.put_line(a);
end loop;
end;
exec proc1(10);
12、
create or replace procedure proc2(j out int) as
begin
j :=100;
end;
declare
k number;
begin
proc2(k);
dbms_output.put_line(k);
end;
/
13、
create or replace function fun_hello return varchar2 is
begin
return '你好';
end;
select fun_hello from dual;
begin
dbms_output.put_line(fun_hello);
end;
/
drop function fun_hello;
14、
create or replace function func1(sno1 int) return int is
v_score number;
v_mingci number;
begin
select score into v_score from 分数表 where student_no=sno1;
select count(*) into v_mingci from 分数表 where score>v_score;
v_mingci :=v_mingci+1;
return v_mingci;
end;
select func1(1) from dual;
15、
隐式游标
begin
update emp1 set sal=8000 where deptno=&deptno_is;
if sql%found then
dbms_output.put_line('更新'|| sql%rowcount||'行');
else
dbms_output.put_line('编号有误，没有找到');
end if;
end;
16、显示游标
declare
v_sal emp1.sal%type;
cursor cursor1 is select sal from emp1 where sal>2500;
begin
open cursor1;
loop
fetch cursor1 into v_sal;
exit when cursor1%notfound;
dbms_output.put_line('工资是'||v_sal);
end loop;
close cursor1;
end;
17、
declare
type emp_record_type is record (name emp.ename%type,salary emp.sal%type);
v_text emp_record_type;
cursor mycursor is select ename,sal from emp where deptno=10;
begin
open mycursor;
fetch mycursor into v_text;
while mycursor%found loop
dbms_output.put_line(v_text.name||v_text.salary);
fetch mycursor into v_text;
end loop;
close mycursor;
end;
18、
declare
type ename_table_type is table of varchar2(10) index by binary_integer;
ename_table ename_table_type;
cursor mycursor is select ename from emp where deptno=10;
begin
open mycursor;
fetch mycursor bulk collect into ename_table;
for i in 1.. ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
close mycursor;
end;
19、
declare
type refcur is ref cursor;
cursor1 refcur;
v_table_name varchar2(50);
v_ename emp1.ename%type;
begin
v_table_name :='&table_name';
if v_table_name='emp1' then
open cursor1 for select ename from emp1;
fetch cursor1 into v_ename;
while cursor1%found loop
dbms_output.put_line(v_ename);
fetch cursor1 into v_ename;
end loop;
close cursor1;
else
dbms_output.put_line('表明错误');
end if;
end;
20、
declare
type table_type is table of dept1%rowtype index by binary_integer;
table1 table_type;
cursor1 sys_refcursor;
begin
open cursor1 for select * from dept1 where mod(deptno,10)=0;
fetch cursor1 bulk collect into table1;
for i in 1 .. table1.count loop
dbms_output.put_line(table1(i).deptno||table1(i).dname||table1(i).loc);
end loop;
close cursor1;
end;
21、
declare
xh1 student3.xh%type;
kc1 varchar2(50) :='';
kc2 varchar2(50) :='';
cursor cursor1 is select distinct xh from student3;
cursor2 sys_refcursor;
begin
dbms_output.put_line(kc1||kc2);
open cursor1 ;
fetch cursor1 into xh1;
while cursor1%found loop
dbms_output.put_line(xh1||kc1);
kc1 :='';
open cursor2 for select kc from student3 where xh=xh1;
fetch cursor2 into kc2;
while cursor2%found loop
kc1 :=kc1||kc2;
fetch cursor2 into kc2;
end loop;
close cursor2;
insert into student4 values(xh1,kc1);
dbms_output.put_line(xh1||kc1);
dbms_output.put_line(xh1||kc2);
commit;
fetch cursor1 into xh1;
end loop;
close cursor1;
end;
