1,新建存储过程

create or replace procedure aop_app_sum_job as
begin

/*
*  your sql command
*/

end aop_app_sum_job

2，打印日志

    begin
        DBMS_OUTPUT.ENABLE(1000000);
	dbms_output.put_line('time:'||sysdate); 

    end 

3，变量定义，赋值

declare
    CITY_NAME varchar2(20);
    
    begin
        CITY_NAME := '广州';
        DBMS_OUTPUT.ENABLE(1000000);
	dbms_output.put_line('CITY_NAME:'||CITY_NAME); 
    end 

4，使用into获取select内容

declare
    CITY_ID number;
    CITY_NAME varchar2(20);

    
    begin
        CITY_ID := 1;
        select city.city_name into CITY_NAME from tb_uc_cfg_city city where city.city_id = '520';
        DBMS_OUTPUT.ENABLE(1000000);
  dbms_output.put_line('CITY_NAME:'||CITY_NAME); 
    end;

5,使用动态SQL传递变量

declare
    CITY_ID number;
    CITY_NAME varchar2(20);
    SQLSTRING varchar2(3000);

    
    begin
        CITY_ID := 1;
        SQLSTRING := 'select city.city_name from city where city.city_id = :CITY_ID';
        execute immediate SQLSTRING into CITY_NAME using CITY_NAME;
        DBMS_OUTPUT.ENABLE(1000000);
	dbms_output.put_line('CITY_NAME:'||CITY_NAME); 
    end ;

6，使用游标cursor

declare
    cursor cur_name is select city_name as CITY_NAME from city where city.id = 1;
    
    CITY_NAME varchar2(20);
    
    begin
        open cur_name;
        FETCH cur_name INTO CITY_NAME;
        DBMS_OUTPUT.ENABLE(1000000);
	dbms_output.put_line('city_name:'||CITY_NAME); 

	CLOSE cur_name;
	commit;
    end 

7,使用for循环遍历游标

declare
    cursor cur_name is select city_name as CITY_NAME from city ;
    
    CITY_NAME varchar2(20);
    
    begin
        open cur_name;
        DBMS_OUTPUT.ENABLE(1000000);
        select count(*) into MaxNum from city;
        for i in 1..MaxNum loop
            FETCH cur_name INTO CITY_NAME;
            
	    dbms_output.put_line('city_name:'||CITY_NAME); 
        end loop;
	CLOSE cur_name;
	commit;
    end