关于Struts2 + spring + hibernate进Q群: 130529143交流
1.PLSQL循环:
--使用循环语句打印;1--100:
--1.初始化条件;2.循环体;3.循环条件;4.迭代条件
declare
v_i number(5) := 1;--1.初始化条件
begin
loop
dbms_output.put_line(v_i);--2.循环体
exit when v_i >= 100; --3.循环条件
v_i := v_i + 1;--4.迭代条件
end loop;
end;
--使用循环语句打印;1--100;
--1.初始化条件;2.循环体;3.循环条件;4.迭代条件
declare
v_i number(5) := 1;--1.初始化条件
begin
while v_i <= 100 loop --3.循环条件
dbms_output.put_line(v_i);--2.循环体
v_i := v_i + 1;--4.迭代条件
end loop;
end;
--使用循环语句打印;1--100;
--1.初始化条件;2.循环体;3.循环条件;4.迭代条件
begin
for c in 1 .. 100 loop
dbms_output.put_line(c);
end loop;
end;
--使用循环语句打印;1--100;
--1.初始化条件;2.循环体;3.循环条件;4.迭代条件
begin
for c in reverse 1 .. 100 loop
dbms_output.put_line(c);
end loop;
end;
--输出2-100之间的质数
declare
v_i number(3) := 2;
v_j number(3) := 2;
v_flag number(1) := 1;
begin
while v_i <= 100 loop
while v_j <= sqrt(v_i) loop
if mod(v_i,v_j)= 0 then v_flag := 0;
end if;
v_j := v_j + 1;
end loop;
v_j := 2;--初始化
if v_flag = 1 then
dbms_output.put_line(v_i);
end if;
v_i := v_i + 1;
v_flag := 1;
end loop;
end;
--输出2-100之间的质数
declare
v_flag number(1) := 1;
begin
for i in 2 .. 100 loop
for j in 2 .. trunc(sqrt(i)) loop
if mod(i,j)= 0 then v_flag := 0;
end if;
end loop;
if v_flag = 1 then
dbms_output.put_line(i);
end if;
v_flag := 1;
end loop;
end;
--输出2-100之间的质数
declare
v_flag number(1) := 1;
begin
for i in 2 .. 100 loop
for j in 2 .. trunc(sqrt(i)) loop
if mod(i,j)= 0 then v_flag := 0;
goto label;--满足条件跳出当前层循环
end if;
end loop;
<<label>>
if v_flag = 1 then
dbms_output.put_line(i);
end if;
v_flag := 1;
end loop;
end;
begin
for i in 1 .. 100 loop
if i = 50 then
goto label;
end if;
dbms_output.put_line(i);
end loop;
<<label>>
dbms_output.put_line('打印结束');
end;
begin
for i in 1 .. 100 loop
if i = 51 then
dbms_output.put_line('打印结束');
exit;
end if;
dbms_output.put_line(i);
end loop;
end;
2.PLSQL游标:
--打印80号部门的所有员工的工资
declare
v_sal employees.salary%type;
--定义游标
cursor emp_sal_cursor is select salary from employees where department_id = 80;
begin
--打开游标
open emp_sal_cursor;
--提取游标
fetch emp_sal_cursor into v_sal;
while emp_sal_cursor%found loop
dbms_output.put_line('salary:'||v_sal);
fetch emp_sal_cursor into v_sal;
end loop;
--关闭游标
close emp_sal_cursor;
end;
--打印80号部门的所有员工的工资
declare
v_sal employees.salary%type;
v_empid employees.employee_id%type;
--定义游标
cursor emp_sal_cursor is select employee_id, salary from employees where department_id = 80;
begin
--打开游标
open emp_sal_cursor;
--提取游标
fetch emp_sal_cursor into v_empid,v_sal;
while emp_sal_cursor%found loop
dbms_output.put_line('id:'||v_empid || 'salary:'||v_sal);
fetch emp_sal_cursor into v_empid,v_sal;
end loop;
--关闭游标
close emp_sal_cursor;
end;
--打印80号部门的所有员工的工资
declare
--声明记录类型
type emp_record is record(
v_empid employees.employee_id%type,
v_sal employees.salary%type
);
--声明一个记录类型变量
v_emp_record emp_record;
--定义游标
cursor emp_sal_cursor is select employee_id, salary from employees where department_id = 80;
begin
--打开游标
open emp_sal_cursor;
--提取游标
fetch emp_sal_cursor into v_emp_record;
while emp_sal_cursor%found loop
dbms_output.put_line('id:'||v_emp_record.v_empid || 'salary:'||v_emp_record.v_sal);
fetch emp_sal_cursor into v_emp_record;
end loop;
--关闭游标
close emp_sal_cursor;
end;
--打印80号部门的所有员工的工资
declare
--声明记录类型
type emp_record is record(
v_empid employees.employee_id%type,--这个两个顺序与定义游标时的顺序对应
v_sal employees.salary%type --这个两个顺序与定义游标时的顺序对应
);
--声明一个记录类型变量
v_emp_record emp_record;
--定义游标
cursor emp_sal_cursor is select employee_id, salary from employees where department_id = 80;
begin
--打开游标
open emp_sal_cursor;
--提取游标
fetch emp_sal_cursor into v_emp_record;
while emp_sal_cursor%found loop
dbms_output.put_line('id:'||v_emp_record.v_empid || 'salary:'||v_emp_record.v_sal);
fetch emp_sal_cursor into v_emp_record;
end loop;
--关闭游标
close emp_sal_cursor;
end;
--打印80号部门的所有员工的工资
declare
--定义游标
cursor emp_sal_cursor is select employee_id, salary from employees where department_id = 80;
begin
for c in emp_sal_cursor loop
dbms_output.put_line('id:'||c.employee_id || 'salary:'||c.salary);
end loop;
end;
--使用游标调整公司中员工的工资
declare
cursor emp_sal_cursor is select employee_id, salary from employees;
v_temp number(4,2);
v_empid employees.employee_id%type;
v_sal employees.salary%type;
begin
open emp_sal_cursor;
fetch emp_sal_cursor into v_empid, v_sal;
while emp_sal_cursor%found loop
if v_sal < 5000 then v_temp := 0.05;
elsif v_sal < 10000 then v_temp := 0.03;
elsif v_sal < 15000 then v_temp := 0.02;
else v_temp := 0.01;
end if;
dbms_output.put_line(v_empid||','||v_sal);
update employees set salary = salary * (1 + v_temp) where employee_id = v_empid;
fetch emp_sal_cursor into v_empid, v_sal;
end loop;
close emp_sal_cursor;
end;
--使用游标调整公司中员工的工资
declare
cursor emp_sal_cursor is select employee_id, salary from employees;
v_temp number(4,2);
begin
for c in emp_sal_cursor loop
if c.salary < 5000 then v_temp := 0.05;
elsif c.salary < 10000 then v_temp := 0.03;
elsif c.salary < 15000 then v_temp := 0.02;
else v_temp := 0.01;
end if;
dbms_output.put_line(c.employee_id||','||c.salary);
update employees set salary = salary * (1 + v_temp) where employee_id = c.employee_id;
end loop;
end;
--取出游标中的多个属性
CREATE OR REPLACE PROCEDURE BRPMS.P_PREWORK_GZZB(V_REC_NUM IN NUMBER,
V_JOBTYPE_ID IN VARCHAR2,
V_DEV_TYPE IN VARCHAR2,
V_DEV_MODAL IN VARCHAR2,
V_SPEC_CODE IN VARCHAR2,
V_PERWORK_TYPE IN VARCHAR2) AS
TYPE R_TYPE IS RECORD(
V_REC_NUM BRPMS.TB_WIT_INSTRUCT_PREWORK.REC_NUM%TYPE,
V_JOBTYPE_ID BRPMS.TB_WIT_INSTRUCT_PREWORK.JOBTYPE_ID%TYPE,
V_DEV_TYPE BRPMS.TB_WIT_INSTRUCT_PREWORK.DEV_TYPE%TYPE,
V_DEV_MODAL BRPMS.TB_WIT_INSTRUCT_PREWORK.DEV_MODAL%TYPE,
V_SPEC_CODE BRPMS.TB_WIT_INSTRUCT_PREWORK.SPEC_CODE%TYPE,
V_PERWORK_TYPE BRPMS.TB_WIT_INSTRUCT_PREWORK.PERWORK_TYPE%TYPE);
V_R1 R_TYPE;
--定义游标
CURSOR MYCURSOR IS
SELECT MAX(P.REC_NUM),
P.JOBTYPE_ID,
P.DEV_TYPE,
P.DEV_MODAL,
P.SPEC_CODE,
P.PERWORK_TYPE
FROM BRPMS.TB_WIT_INSTRUCT_PREWORK P
WHERE P.PERWORK_TYPE = '0600301'
AND P.CONTENT != '工器具已准备完毕,材料、备品已落实。'
GROUP BY P.JOBTYPE_ID,
P.DEV_TYPE,
P.DEV_MODAL,
P.SPEC_CODE,
P.PERWORK_TYPE;
BEGIN
OPEN MYCURSOR;
LOOP
FETCH MYCURSOR
INTO V_R1;
EXIT WHEN MYCURSOR%NOTFOUND;
INSERT INTO BRPMS.TB_WIT_INSTRUCT_PREWORK
(GUID,
JOBTYPE_ID,
DEV_TYPE,
DEV_MODAL,
SPEC_CODE,
PERWORK_TYPE,
REC_NUM,
CONTENT)
VALUES
(SYS_GUID(),
--取出游标中的属性
V_R1.V_JOBTYPE_ID,
V_R1.V_DEV_TYPE,
V_R1.V_DEV_MODAL,
V_R1.V_SPEC_CODE,
'0600301',
V_R1.V_REC_NUM + 1,
'工器具已准备完毕,材料、备品已落实。');
COMMIT;
END LOOP;
CLOSE MYCURSOR;
END;
关于Struts2 + spring + hibernate进Q群: 130529143交流。
有偿技术支持Q群:398162181