linux shell使用loop和cursor批量修改sql

USER="zhangsan"
PWD="123456"
DBNAME="JTP"

sqlplus ${USER}/${PWD}@${DBNAME}<<EOF

declare
    #定义游标并赋值cursor 与is一起使用
    cursor stu_cur is select * from tbstudents;
    #定义类型rowtype，stu_row用户获取表字段值
    stu_row tbstudent%rowtype
    #开始执行
    begin
    #打开游标
    open stu_cur
    #开始循环
    loop
    #退出循环结束条件
    exit when stu_cur%notfound
    #将游标的值赋值到rowtype
    fetch stu_cur into stu_row
    #更新表字段,将没有身份证的学生状态置位0（不正常）
    update set status='0' where name=stu_row.name and id_code=' ';
    #或者使用下面sql,，更高效；
    if stu_row.id_code = ' ' then
        update student set status='0' where name=stu_row.name;
    elsif stu_row.id_kind = ' ' then
        update student set status='0' where name=stu_row.name;
    elsif stu_row.name = ' ' or stu_row.sex = ' ' then
        update student set status='0' where name=stu_row.name;
    end if;
    #结束循环
    end loop;
    #关闭游标
    close stu_cur;
    end;

EOF

• 1.sqlplus
• 2.delcare
• 3.cursor
• 4.begin
• 5.open cursor
• 6.loop
• 7.exit
• 8.fetch
• 9.sql