pl/sql基础语法

-- 数据定义
-- 创建表
-- drop table scores;
-- drop table student;

Create table student(
Name varchar2 ( 10 ) constraint nname not null ,
No varchar2 ( 3 ) constraint pk_no primary key ,
Sex varchar2 ( 4 ) default ' 男 '
);
create table scores(
id number constraint pk_ID primary key ,
no varchar2 ( 3 ) constraint fp_no references student(no) ,
name varchar2 ( 100 ),
num number
);
-- drop table scores;
-- drop table student;

-- 创建视图
create view view_student as select * from student;
drop view view_student
-- -创建索引
create index bh on student(no,name);
drop index bh;
-- -创建约束条件
-- --ALTER TABLE 表名 ADD(CONSTRAINT 约束名 约束类型(列名))
alter table STUDENT add constraint chk check (sex = ' 男 ' or sex = ' 女 ' );
-- --alter table 表名 drop(constraint)
alter table student drop constraint chk

ALTER TABLE scores DISABLE constraint pk_ID; -- --关闭 ALTER TABLE student DISABLE/enable CONSTRAINT nn_sname
ALTER TABLE student disABLE CONSTRAINT nname;
ALTER TABLE scores DISABLE constraint fp_no;
ALTER TABLE student DISABLE constraint nname;
ALTER TABLE student DISABLE constraint pk_no;
ALTER TABLE student DISABLE constraint nname;
-- -添加列 ALTER TABLE products ADD description text;
ALTER TABLE student ADD description varchar2 ( 100 );
-- --删除列 ALTER TABLE products drop description text;
ALTER TABLE student drop column description ;

alter table yw_cyzt_wyqytj add YWFSR number ;
comment on column yw_cyzt_wyqytj.ywfsr is ' 物业费
收入 ' ;
alter table yw_cyzt_wyqytj add YWJYSR number ;
comment on column yw_cyzt_wyqytj.YWJYSR is ' 物业经
营收入 ' ;
alter table yw_cyzt_wyqytj add QTYWSR number ;
comment on column yw_cyzt_wyqytj.QTYWSR is ' 其他业
务收入 ' ;


数据查询
Select * from student


数据库操纵
Update insert delete
Update table_name
Insert into table_name values ()
Delete table_name


添加数据
declare
icount integer ;
sSQL varchar ( 100 );
BEGIN
for icount in 1 .. 90 loop
sSQL: = ' insert into student(name,no)values( ' || icount || ' , ' || icount || ' ); ' ;
dbms_output.put_line(SSQL);
-- -execute immediate sSQL;
END LOOP;
END ;


Sql块
DECLARE
V1 nchar ( 10 );
v_no varchar ( 3 ): = ' 1 ' ;
BEGIN
SELECT t.name INTO V1 FROM student t WHERE no = v_no;
-- SELECT t.name INTO V1 FROM student t
DBMS_OUTPUT.PUT_LINE (v1);
exception
When TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE ( ' More than one manager ' );
end ;


自定义变量
Record类型
declare
type t_re is record
( name1 student.name % type,name2 student.no % type,name3 student.sex % type);
re t_re;
begin
select * into re from student where no = 40 ;
-- select * into re from student where no=40;
dbms_output.put_line(re.name1 || ' ; ' || re.name2 || ' ; ' || re.name3);
exception
when too_many_rows then
DBMS_OUTPUT.PUT_LINE ( ' More than one manager ' );
end ;

索引表
-- INDEX BY TABLES(不是非/聚簇索引-存在的物理表，它是一个虚拟表)
declare
cursor cur is select * from student;
type type_arr is table of student % rowtype index by binary_integer;
arr_tr type_arr;
iCount integer : = 1 ;
begin

for i in cur LOOP
arr_tr(icount): = i;
icount: = icount + 1 ;
END LOOP;

for icount in 1 ..arr_tr. count loop
dbms_output.put_line(arr_tr(icount).name || arr_tr(icount).no || arr_tr(icount).sex );
end loop;
if arr_tr. EXISTS ( 2 ) then
dbms_output.put_line( ' arr_tr(2)= ' || arr_tr( 2 ).name || arr_tr( 2 ).no || arr_tr( 2 ).sex);
end if ;
end ;


游标变量
显示游标
declare
icount integer : = 0 ;
cursor cur1 is select * from student ; -- -不带参数的游标
cursor cur2(v_no number ) is select * from student t where to_number(t.no) > v_no; -- -带参数游标
type type_record is record (name student.name % type,no student.no % type,sex student.sex % type);
rec_stu type_record;
v_name student.name % type;
v_no student.no % type;
v_sex student.sex % type;
begin

dbms_output.put_line( ' 不带参数的手动打开的游标 ' );
open cur1; --
loop
exit when cur1 % notfound;
fetch cur1 into rec_stu;

dbms_output.put_line( ' 当前获取的值为： ' || rec_stu.name || rec_stu.no || rec_stu.sex);
end loop;
close cur1;

dbms_output.put_line( ' 带参数的用for打开的游标 ' );
for i in cur2( 20 )loop
dbms_output.put_line( ' 当前获取的值为： ' || i.name || i.no || i.sex);
end loop;
end ;


隐示游标
declare
v_no varchar2 ( 10 ): =& 学号;
-- v_no number:=&学号;---为什么在数字时能够正确判断
begin
delete scores t where t.no = trim(v_no);
-- delete scores t where t.no=v_no;
if sql % notfound then -- -found,rowcount,isopen
delete student t1 where t1.no = v_no;
dbms_output.put_line(sql % rowcount );
end if ;

end ;
游标修改 删除操作
select * from student;
declare
cursor cur is select * from student for update of name nowait;
begin
for i in cur loop
UPDATE student set name = ' 1 ' where current of cur;
end loop;
end ;
-- select * from student;
流程控制语句
条件语句
-- -条件语句
-- if
declare
v_score number ( 5 , 2 ): =& 分数;
v_Result varchar ( 100 ): = '' ;
begin
if v_score > 0 and v_score < 60 then
v_result: = ' 未及格 ' ;
elsif v_score < 80 then
v_result: = ' 中 ' ;
elsif v_score < 90 then
v_result: = ' 良 ' ;
else
v_result: = ' 优 ' ;
end if ;
dbms_output.put_line(v_result);
end ;

-- case
declare
v_score varchar2 ( 4 ): =& 级别;
v_Result varchar ( 100 ): = '' ;
begin
case upper (trim(v_score))
when ' D ' then
v_result: = ' 未及格 ' ;
when ' C ' then
v_result: = ' 中 ' ;
when ' B ' then
v_result: = ' 良 ' ;
when ' A ' then
v_result: = ' 优 ' ;
else
v_result: = ' 未知 ' ;
end case ;
dbms_output.put_line(v_result);
end ;


循环语句
-- for
declare
i number : = 0 ;
j number : = 0 ;
icount number : = 10 ;
str varchar ( 20 ): = '' ;
begin
for i in 1 .. icount loop
str : = '' ;
for j in 1 .. icount - i loop
str : = str || ' * ' ;
end loop;
dbms_output.put_line( str );
end loop;
end ;

-- while循环
declare
cursor cur is select * from student;
st student % rowtype;
begin
open cur;
dbms_output.put_line( ' 查询的条数: ' || cur % rowcount );
loop
exit when cur % notfound;
fetch cur into st;
dbms_output.put_line(st.name || st.no || st.sex );
end loop;
close cur;
end ;







函数
create or replace function fun_sum return number is
Result number ;
begin
select count ( * ) into Result from student ;
return (Result);
end fun_sum;
-- select fun_sum from dual


create or replace function fun_Sum_stu(v_name varCHAR )
return number is sun number : = 0 ;
begin
select sum (to_number(no)) into sun from student where name = v_name;
return sun;
end fun_Sum_stu;

存储过程
create or replace procedure pro_find is
result number ;
begin
select count ( * ) into result from student ;
end pro_find;

触发器
create or replace trigger tr_out
before update on student
for each row
declare
-- local variables here
begin
dbms_output.put_line( ' 修改前数据： ' || :old.name || :old.no || :old.sex);
dbms_output.put_line( ' 修改后数据： ' || :new.name || :new.no || :new.sex);
end tr_out; update student set name = ' 12 ' where no = ' 2 '