PL-SQL编程

--内连接
select stu.sid 学生编号, stu.sname 学生姓名 ,stu.sage 学生年龄 ,co.cname 学生所在国家 ,co.cinfo 国家信息  from student stu inner join contry co on stu.cid= co.cid;
--左连接
select * from student stu left join contry co on stu.cid=co.cid ;
--右连接
select * from student stu right join contry co on stu.sid=co.cid ;
--全连接
select * from student stu full join contry co on stu.cid=co.cid ;
--子查询
select stu.sname 学生姓名, co.cname 国家名称 from student stu ,(select cid,cname from contry) co where stu.cid=co.cid ;
select stu.sage AGE, stu.sid SID from student stu where stu.sid >(select avg(cid ) from contry);

SELECT ID , PID, NAME, ICON, isParent
FROM (select to_char (JZGID) AS ID,
to_char (BMID) as PID ,
to_char (j. XMVVV) as NAME ,
to_char ('false') as isParent ,
to_char (CASE j .XBVVV
WHEN '男' THEN
'/szhxy/widget/smartui/libs/icons/user.png'
WHEN '女' THEN
'/szhxy/widget/smartui/libs/icons/user_female.png'
ELSE
'/szhxy/widget/smartui/libs/icons/user-silhouette-question.png'
END) as ICON
from HR_BM_JZG h
join HR_JZGXX j on j.ID = h .JZGID)
UNION ( select to_char( s.dept_id ) as ID,
to_char ('1') as PID ,
to_char (s. dept_name) as NAME ,
to_char('/szhxy/widget/smartui/libs/icons/user_group.png') as ICON,
to_char ('true') as isParent
from SYS_DEPARTMENT s)
--相关子查询
select stu.sid 学生编号, stu.sname 学生姓名 from student stu ,(select avg(cid ) id from contry group by cid)co where stu.cid>co.id ;
select * from sys_dept;
--视图
create or replace view v_sel
as
   select stu.sid 学生编号 ,stu.sname 学生姓名 from student stu where stu.sid>4 ;
调用:select * from v_sel;

--PL-SQL编程
declare
   nums number(20 );
   begin
   nums:= 20;
   dbms_output.put_line( nums);
 end;

 declare
   sids number(3 ):=1;
   stuname varchar(20 );
   sage number(3 );
   begin
   select stu.sname,stu.sage into stuname,sage from student stu where stu.sid=sids ;

   dbms_output.put_line( stuname);
   dbms_output.put_line( '这里是数据库查询' );
   dbms_output.put_line( sage);
   end;
create tablespace pro datafile 'e:\OracleSpace\pro.dbf' size 100m autoextend on next 1 m maxsize 200m ;
select * from dba_users;

------------------------------------项目实战----------------------------
select * from sys_dept_level;
select extract (year from sysdate)年 from dual;
select last_day(to_date ('2014-8-3', 'yyyy-mm-dd'))from dual;
select translate ('welcome' ,'l', 'hh')from dual;
select replace ('welcome', 'l','hh' ) from dual;
select instr ('oipessde', 's') from dual ;
select * from student stu where stu.sid<>2 ;
select * from student;
--else-if
declare
score number (8, 2);
begin
score := 70 ;
if score >90 then
dbms_output.put_line ('优秀');
elsif score >=80 then
dbms_output.put_line ('良好');
elsif score >=60 then
dbms_output.put_line ('一般');
else
dbms_output.put_line ('差');
end if ;
end;

--case when
declare
score number (8, 2);
begin
score := '&输入分数' ;
case
when score >= 90 then
dbms_output.put_line ('优秀');
when score >=80 then
dbms_output.put_line ('良好');
when score <80 then
dbms_output.put_line ('差');
end case ;
end;
--loop循环
declare
num number (5, 2);
begin
num:='&请输入数字' ;
loop
dbms_output.put_line (num);
num:=num +2;
if num >100 then
exit;
end if ;
end loop ;
end;
--标记
declare
vi number (5, 2);
begin
vi:=3 ;
for vi in 1..100
loop
dbms_output.put_line (vi);
if vi =50 then
goto test ;
end if ;
end loop ;
<<test>>
dbms_out.put_line('结束.............');
end;
select * from student;
--存储过程(无返回值)
create or replace procedure proc_getStuName
(
p_stu_id  number
)
is
v_stu_name varchar (10);
begin
select SNAME into v_stu_name from student where sid=p_stu_id ;
dbms_output.put_line (v_stu_name);
end;

declare
stuId number (3):= 3;
begin
proc_getStuName(stuId );
end;
--存储过程(参数 in out)
create or replace procedure pro_getStuName2
(
p_stuId in number,
p_stuName out varchar
)
is
begin
select sname into p_stuName from student where sid=p_stuId ;
end;

declare
sid number (8):= 4;
sname varchar (20);
begin
pro_getStuName2(sid ,sname);
dbms_output.put_line (sname);
end;

--调用的另一种方式
declare
sid number (3):= 3;
sname varchar (20);
begin
pro_getStuName2(p_stuName =>sname, p_stuId=>sid );
dbms_output.put_line (sname);
end;
select * from student;
create table student2 as select * from student where 1=2 ;
select * from student2;
insert into student2 select * from student;
--函数
create or replace function fun_getStuName
(
f_stuId number
)
return varchar
is
f_stuName varchar (20);
begin
select sname into f_stuName from student
where sid =f_stuid;
return f_stuName ;
end;

declare
stuname varchar (20);
Sid number (3);
begin
stuname := fun_getStuName(3 );
dbms_output.put_line (stuname);
end;

select fun_getStuName (4) 姓名 from dual ;

select * from sys_dept_new;
select * from student;
select sum (sage) from student;
select avg (sage) from student;
--触发器
create or replace trigger insert_student
after insert or update on contry for each row
declare
SID NUMBER (3):= 11;
stuname varchar (20);
begin
stuname := '&输入用户名:' ;
insert into student( sid,SNAME ,SAGE, STUCON,cid ) values ( SID,stuname ,8, 'russia',3 );
end insert_student ;
select * from contry;
select * from student;
insert into contry values(23 ,'parul', '秘鲁阿里郎' );
insert into contry( cid,cname ) values (87 ,'lded');
select cname from contry where cid>( select avg (sid) from student)

-一般游标测试
declare
   cursor c_per
 is
  select * from person p;

  c_row c_per%rowtype;

  begin
      for c_row in c_per
       loop
       dbms_output.put_line(c_row.personId||'-'||c_row.personName||'-'||c_row.personAge||'-'||c_row.personInfo);
       end loop;
  end;

--Fetch游标测试
 declare
    cursor c_per2
    is
 select * from person p
 where p.personid in (3,2,5);
 --定义一个游标变量
 c_row c_per2%Rowtype;

 begin
    open c_per2;
    loop
       fetch c_per2 into c_row;
       --如果找不到数据就退出
       exit when c_per2%notfound;
       dbms_output.put_line(c_row.personId||'-'||
                            c_row.personName||'-'||
                            c_row.personAge||'-'||
                            c_row.personInfo);
    end loop;
 end;


--更新数据
 begin
    update person p set p.personname='LinDa' 
    where p.personid in (2,3,4);
    if sql%isopen then
    dbms_output.put_line('Openging cursor');
     else
     dbms_output.put_line('closing cursor');
     end if;

     if sql%found then
    dbms_output.put_line('游标指向了有效行');
     else
     dbms_output.put_line('Sorry');
     end if;

     if sql%notfound then
    dbms_output.put_line('Also Sorry');
     else
     dbms_output.put_line('Hello');
     end if;

     dbms_output.put_line(sql%rowcount);

     exception
      when no_data_found then
                       dbms_output.put_line('Sorry No data');
                       when too_many_rows then
                         dbms_output.put_line('Too Many rows');
                         end;
    select * from person;

    --插入数据
    declare
    cursor c_whilePer
    is
    select p.personname 
    from person p;

    --指定行指针
    row_per c_whilePer%Rowtype;

    --测试游标
    begin
    --打开游标
    open c_whilePer;
    --获取数据
    fetch c_whilePer into row_per;
    while c_whilePer%found 
    loop
    dbms_output.put_line('PersonName:'||row_per.personName);
    --给下一行添加数据
    fetch c_whilePer into row_per;
    end loop;
    close c_whilePer;
    end;

    select * from person;

    --输入参数查询该用户的信息
    declare
    cursor
    c_selPer(p_perId number)
    is
    select * from person p 
    where p.personid=p_perId;

    --定义一个游标指针
    r_per person%rowtype;
    perId number(3);
    begin
    perId :='&请输入员工Id';
       for r_per in c_selPer(perId)
       loop
       dbms_output.put_line('personName:'||r_per.personName||'-->'||
                             'personAge:'||r_per.personAge);
       end loop;
       end;

    select * from person p where p.personname like '%Ti%' or p.personname like '%Ji%';

    --对名字模糊查询，更新数据
       declare
       cursor
        c_per
        is
        select * from person p where p.personname like '%Ji%'
          or p.personname like '%To%' for update of personAge;       

    r_AddPerson c_per%Rowtype;
    personInfo person.personAge%Type;
    personAge number(3);
    begin
    personAge :='&请输入年龄:';
       for r_AddPerson in c_per
       loop
     dbms_output.put_line(r_AddPerson.personName||'原来的年龄:'||r_AddPerson.personAge);
     personInfo :=r_AddPerson.personAge*2;
    update person p set p.personage = personAge where current of c_per;
    end loop;
    end;