1 create table student (
2 xh number(4), --学号
3 xm varchar2(20), --姓名
4 sex char(2), --性别
5 birthday date, --出生日期
6 sal number(7,2) --奖学金
7 );
8 /*
9 *第一种写法
10 */
11 declare
12 v_name varchar(100);
13 v_xh number :=0;
14 begin
15 select xm,xh into v_name,v_xh from student where xh=3;
16 dbms_output.put_line(v_name||'------------>'||v_xh);
17 end;
18 /*
19 *第二种写法
20 */
21 declare
22 type stu_record is record(
23 v_name varchar(100),
24 v_xh number :=0
25 );
26 v_interface stu_record;
27 begin
28 select xm,xh into v_interface from student where xh=6;
29 dbms_output.put_line(v_interface.v_name||'------------>'||v_interface.v_xh);
30 end;
31
32
33
34 /*
35 *if else
36 */
37
38 declare
39 v_xh student.xh%TYPE;
40 v_temp varchar(30);
41 begin
42 select xh into v_xh from student where xh=23;
43 if v_xh<6 then
44 v_temp:='xh小于6';
45 elsif v_xh=6 then
46 v_temp:='xh等于6';
47 else
48 v_temp:='xh大于6';
49 end if;
50 dbms_output.put_line(v_temp);
51 end;
52
53 /*
54 *case when then end
55 */
56
57 declare
58 v_xm varchar(30);
59 v_temp varchar(30);
60 begin
61 select xm into v_xm from student where xh=2;
62 v_temp:=''
63 case v_xm when 'john' then 'xh是偶数'
64 when 'martin' then 'xh是奇数'
65 else 'null'
66 end;
67 dbms_output.put_line(v_temp);
68 end;
69
70 /**
71 *loop 循环
72 */
73 declare
74 --①
75 v_i number(5) :=1;
76 begin
77 loop
78 dbms_output.put_line(v_i);
79 --③
80 exit when v_i>=100;
81 --②
82 v_i:=v_i+1;
83 end loop;
84 end;
85
86 /*
87 *while
88 */
89 declare
90 v_i number(5) :=1;
91 begin
92 while v_i <=100 loop
93 dbms_output.put_line('--------------->'||v_i);
94 v_i:=v_i+1;
95 end loop;
96 end;
97
98 /*
99 *for
100 */
101 begin
102 for c in 1..100 loop
103 dbms_output.put_line('c'||c);
104 end loop;
105 end;
106
107
108
109 /*
110 *游标
111 */
112 declare
113 v_name student.xm%Type;
114 --定游标
115 cursor stu is select xm from student;
116 begin
117 --打开游标
118 open stu;
119 --提取游标
120 fetch stu into v_name;
121 while stu%found loop
122 dbms_output.put_line(v_name);
123 fetch stu into v_name;
124 end loop;
125 end;
126 /*
127 *练习
128 */
129 --游标练习
130 declare
131 type stu_record is record(
132 v_xm varchar(100),
133 v_xh number :=0
134 );
135 v_student stu_record;
136 --定游标
137 cursor stu is select xm,xh from student;
138 begin
139 --打开游标
140 open stu;
141 --提取游标
142 fetch stu into v_student;
143 while stu%found loop
144 dbms_output.put_line(v_student.v_xh||'---------------->'||v_student.v_xm);
145 fetch stu into v_student;
146 end loop;
147 --关闭游标
148 close stu;
149 end;
150 /**
151 *for代替游标
152 */
153 declare
154 --定游标
155 cursor stu is select xm,xh from student;
156 begin
157 for c in stu loop
158 dbms_output.put_line(c.xh||'---------------->'||c.xm);
159 end loop;
160 end;
161
162
163 /*
164 *函数
165 */
166 create or replace function hello(v_xm varchar2)
167 return varchar2
168 is
169 begin
170 return '===========hello=============='||v_xm;
171 end;
172 select hello('王正和') from dual;
173 --练习
174 --1
175 create or replace function get_sysadte
176 return date
177 is
178 begin
179 return sysdate;
180 end;
181 select get_sysadte from dual;
182 --2
183 create or replace function add_parm(v_num1 number,v_num2 number)
184 return number
185 is
186 v_sum number(10);
187 begin
188 v_sum:=v_num1+v_num2;
189 return v_sum;
190 end;
191 select add_parm(100,300) from dual;
192 /*
193 *存过
194 */
195 create or replace procedure deal_hello
196 is
197 begin
198 dbms_output.put_line('---------------->hello');
199 end;
200 --1
201 create or replace procedure deal_sum_xh
202 is
203 v_sum number(20) :=0;
204 cursor stu is select xh,xm from student;
205 begin
206 for c in stu loop
207 v_sum:=c.xh+v_sum;
208 dbms_output.put_line('<-----数据----------->'||c.xh);
209 end loop;
210 dbms_output.put_line('-----最终数据----------->'||v_sum);
211 end;
