存储过程学习1

create or replace procedure proc_insert(series  in number, --树的层级数 一般小于3级
                                        amount1 in number, --第一级别多少个节点
                                        amount2 in number, --第二级别多少个节点
                                        name    in varchar2 --输入姓名
                                        ) as
  str_sql  varchar2(500);
  i        number;
  j        number;
  pkId     char(6);
  pId      char(10);
  nodeName varchar2(500);
  remark   varchar2(500);
  CURSOR cur IS
    SELECT id FROM z_tree_node t where t.pid is null;

begin
  --1,先清空表中的数据
  delete from z_tree_node;
  commit;

  --2,建立第一级部门
  i      := 1;
  remark := 'leve1';
  while i < amount1 LOOP
    begin
      pkId     :=i;
      nodeName := name || to_char(i);
      -- DBMS_OUTPUT.PUT_LINE('pkId: ' || pkId || 'nodeName: ' || nodeName);
      str_sql := 'insert into z_tree_node (id,name,pid,remark) values(:1,:2,null,:3)';
      execute immediate str_sql