--DBMS_OUTPUT.PUT_LINE 打印参数
--直接赋值语句 v_name := 'zhangsan'
--声明引用类型变量 DECLARE test GROUP_INFO_TAB%ROWTYPE;
--使用select …into … 赋值:(语法 select 值 into 变量)
BEGIN
DECLARE
test GROUP_INFO_TAB%ROWTYPE;
v_name varchar2(20);
BEGIN
SELECT * INTO test FROM GROUP_INFO_TAB git WHERE GROUPID = '1200000000';
DBMS_OUTPUT.PUT_LINE(test.GROUPID);
v_name := 'zhangsan';
DBMS_OUTPUT.PUT_LINE(v_name);
END;
END;
--游标遍历的3种方式
create or replace procedure myprocedure is
CURSOR CUR_TEST IS --声明显式游标
SELECT ECODE,ENAME
FROM EMP;
CUR CUR_TEST%ROWTYPE; --定义游标变量,该变量的类型为基于游标C_EMP的记录
BEGIN
--For 循环
FOR CUR IN CUR_TEST LOOP
--循环体
DBMS_OUTPUT.PUT_LINE('员工编号:'||CUR.ECODE ||'员工姓名:'|| CUR.ENAME);
END LOOP;
--Fetch 循环
OPEN CUR_TEST;
LOOP
FETCH CUR_TEST INTO CUR;
EXIT WHEN CUR_TEST%NOTFOUND;
--循环体
DBMS_OUTPUT.PUT_LINE('员工编号:'||CUR.ECODE ||'员工姓名:'|| CUR.ENAME);
END LOOP;
CLOSE CUR_TEST;
--While 循环
OPEN CUR_TEST;
FETCH CUR_TEST INTO CUR;
WHILE CUR_TEST%FOUND LOOP
--循环体
DBMS_OUTPUT.PUT_LINE('员工编号:'||CUR.ECODE ||'员工姓名:'|| CUR.ENAME);
FETCH CUR_TEST INTO CUR;
END LOOP;
CLOSE CUR_TEST;
END myprocedure;
-- 存储过程输出参数
drop procedure if exists proc_user_out;
create procedure proc_user_out(out out_param varchar(20))
begin
select username into out_param from user where userid=2;
end;
call proc_user_out(@result);
select @result;
--示例有数据更新,没收插入
CREATE OR REPLACE PROCEDURE update_insert_temp
AS
BEGIN
DECLARE
CNT NUMBER;
recordnum NUMBER;
CURSOR select_cursor IS
SELECT * FROM temp_table;
select_row select_cursor%rowtype;
BEGIN
CNT:=0;
FOR select_row IN select_cursor LOOP
CNT:=CNT+1;
select count(id) into recordnum from temp_table Where id=select_row.id;
if(recordnum=0) then
insert into AMS_INTERNET_USER_LEVEL(id,name) values (select_row.id,select_row.name);
ELSE
UPDATE temp_table SET name=select_row.name WHERE id=select_row.id;
End if;
IF(CNT>50000) THEN
CNT:=0;
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
END;
COMMIT;
CALL update_insert_temp()