Oracle數據庫-PL/SQL整理(1)

　　獲取系統記錄行數
  go_block(T_WARITEMPRT);
  last_record;
  row_count:=:systemtrigger_record;  systemcursor_record
execute_trigger(POSTCHANGE);
設置鼠標狀態
set_application_property(cursor_styleBUSY);
raise form_trigger_failure;
顯示錯誤信息
  exception when others then
   display_note(sqlerrm||sqlcode:globallogon_user);
decode
decode(afreightmode企業自付供應商付費企業代付供應商代付null)
decode(qtyonhand qtyinitinvtranqtyinvtranqtynull)
導出路徑
select *  from t_parameter where f_name=LeanERPTempDir;
select f_c_value from t_parameter where f_name =WebServer;
合同附件
select f_c_value  from t_parameter where f_name=purorderaccesspath;
select f_c_value  from t_parameter where f_name=purorderaccessurl;
設置值列表    
SET_ITEM_PROPERTY(topORIGINALNOLOV_NAMELOV_MSO);
LIST_VALUES;
下拉列表
 n:=populate_group(rec_lb);
 populate_list(BLK_BUTAPPTYPErec_lb);
 :bLK_BUTAPPTYPE:=;
調用另一模塊
DECLARE
   pl_id   ParamList;
   v_string varchar();
   v_itemname varchar();
BEGIN
  pl_id := Get_Parameter_List(calendar);
  IF NOT Id_Null(pl_id) THEN
    Destroy_Parameter_List(pl_id);
  END IF;
  pl_id := Create_Parameter_List(calendar);
  v_itemname:=:systemcurrent_block||||:systemcurrent_item;
  v_string:=name_in(v_itemname);
  Add_Parameter(pl_id p_strTEXT_PARAMETERv_string);
  Call_Form(input_strno_hideno_replaceno_query_onlypl_id);
  copy(:globalstrv_itemname); Erase(:globalstr);
END;
批量處理
  set_application_property(cursor_styleBUSY);
  SYNCHRONIZE;
  first_record;
  loop
    null;
  exit when :systemlast_record = TRUE;
  next_record;
  end loop;
  set_application_property(cursor_styleDEFAULT);
  bell;
  display_note(SRMP:globallogon_user);
    采購計劃關閉完畢!
對話框
IF display_stop(INV :GLOBALlogon_user) <> alert_button THEN
  RETURN;
END IF;
取得貨幣名稱
begin
  select currencydesc
  into v_currencydesc
  from t_currency
  where currencycode = csplymoneyflag;
  exception when others then v_currencydesc := 人民幣（元）;
end;
詢問對話框
declare
  return_value number;
begin
  set_alert_property(stop_alertalert_message_text確認送審該合同嗎?);
  return_value := show_alert(stop_alert);
  if return_value != alert_button then
     return;
  end if;
end;
詢問對話框

　　DECLARE
 return_value number; 
BEGIN
  return_value := display_stop(mdm:globallogon_user);
  IF return_value = ALERT_BUTTON THEN
     COMMIT_FORM;
  END IF;
END;  

　　取下周一
 select trunc(sysdated)+ from dual;
message_level
oldmsg := :SystemMessage_Level;
  IF reldef = FALSE THEN
    Go_Block(detail);
    Check_Package_Failure;
    :SystemMessage_Level := ;
    Execute_Query;
    :SystemMessage_Level := oldmsg;
  ELSE
取當前年度期段號
begin
  select yearperiod
  into v_yearv_period
  from t_accperiod
  where sysdate >= startdate
  and to_char(sysdateyyyy/mm/dd)<=to_char(enddateyyyy/mm/dd);
exception when others then null; 
end;
設置畫布屬性
vCan:=FIND_VIEW(CAN_PRTNO);
SET_VIEW_PROPERTY(vCanVISIBLEproperty_true);
vCan:=FIND_VIEW(CAN_PRTNO);
SET_VIEW_PROPERTY(vCanVISIBLEproperty_false);
設備提示信息
elsif :topopsrc = X or :topopsrc=T then
   set_item_property(toptranobjprompt_text客戶代碼);
else
   set_item_property(toptranobjprompt_text部門代碼);
end if;
設置值列表KEYLISTVAL
ELSIF :topOPSRC=I THEN  零星出庫
  SET_ITEM_PROPERTY(topORIGINALNOLOV_NAMELOV_MSI);
LIST_VALUES;
  IF :topORIGINALNO IS NOT NULL THEN
     PRTNO_MSI;
  END IF;
END IF;
調用值列表show_lov
DECLARE
  a_value_chosen BOOLEAN;
BEGIN
  a_value_chosen := Show_Lov(lov_name);
  IF  a_value_chosen THEN
    set_block_property(t_efficiencydefault_whereusername = :controldisplay_name);
    go_block(t_efficiency);
    execute_query; 
    set_block_property(t_efficiencydefault_whereusername in (select username from t_hrmuser where department = :globaluser_dept));
  END IF;
END;
生成序號
declare
  vlpadnum number;
  maxno varchar();
  cursor cerpcode is select sequencelpadnum from t_erpcode
  where erpcode=PURAPPNO and prifix=to_char(sysdateyy)||to_char(sysdatemm) for update nowait;
begin
  if :t_purappappno is null then
    if :SYSTEMMODE = NORMAL THEN
     begin
      begin
     for c in cerpcode loop    
        update t_erpcode set sequence=sequence+ where erpcode=PURAPPNO and prifix=to_char(sysdateyy)||to_char(sysdatemm);
     end loop;
    exception
     when others then
     display_note(SYS:globallogon_user);
     raise form_trigger_failure;
    end;
      select sequencelpadnum into maxnovlpadnum from t_erpcode where erpcode=PURAPPNO and prifix=to_char(sysdateyy)||to_char(sysdatemm);
     exception
      when NO_DATA_FOUND then
           select count(*)+ into maxno from t_purapp where appno like %||to_char(sysdateyy)||to_char(sysdatemm)||%;
           select max()
      vlpadnum:=;
      insert into t_erpcode(erpcodeprifixsequencelpadnum)
      values (PURAPPNOto_char(sysdateyy)||to_char(sysdatemm)maxnovlpadnum);
     end;
      :t_purappappno := Q||:globallogon_user||to_char(sysdateyy)||to_char(sysdatemm)||lpad(maxnovlpadnum);
    end if;
   end if;
end;
遍歷樹
 select t_prtstrudefprtno
        t_prtbasdefprtdesc
        t_prtbasdefmtltmrk
        t_prtstrudefprtpqty
        t_prtbasdefprtum
        t_prtstrudeftranprtno
   from t_prtstrudeft_prtbasdef
  where t_prtstrudefprtno = t_prtbasdefprtno
connect by prior t_prtstrudefprtno=t_prtstrudefprntno
  start with t_prtstrudefprntno=:BLOCK_BUTTONPRTNO;
 
/* 

　　** Builtin:  FORMS_DDL
** Example:   The expression can be a string literal
*/ 
BEGIN
  Forms_DDL(create table temp(n NUMBER));
  IF NOT Form_Success THEN
    Message (Table Creation Failed);
  ELSE
    Message (Table Created);
  END IF;
END;
物料所屬大類判斷
and (exists (select a from t_prtinclass c where cclassid=||:blk_queryclassid|| and cprtno=v_totqryprtno ) or :blk_queryclassid is null)
給值列表賦初值
declare
  cursor v_cur is select machtype A machtype B from t_gkdept order by machtype;
  N_INDEX NUMBER :=;
begin
  DELETE_LIST_ELEMENT(T_TPMSCKMTNMACHTYPE);
  for c in v_cur loop
    Add_List_Element(T_TPMSCKMTNMACHTYPEN_INDEXCACB);
    N_INDEX := N_INDEX + ;
  end loop;
end;

查詢重復數據
select rpno from t_purtrace group by rpno having count(rpno) >;

生成單據號
PROCEDURE GET_PRTNO is
   v_sysdate      varchar();
   v_Svdictateno  varchar();
   v_DbodNo       varchar();     
   n_count        integer;
   v_count        varchar();
   n_count       integer;
   n_count       integer;
   v_count       varchar();
   n_num          number;
   v_num          varchar();
BEGIN

　　v_DbodNo:= ZS;
   v_sysdate:=to_char(sysdateYYMMDD);
   獲取滿足條件的記錄數
   select count(*)+ into n_num
    from bas_prtbasdef
   where prtno like v_DbodNo||v_sysdate||%;
   v_num := to_char(n_num);
   填充
   v_count:=lpad(v_num);
   合成為物料號
   v_Svdictateno:=v_DbodNo||v_sysdate||v_count;
   判斷是否存在該號碼
   select count(*) into n_count from bas_prtbasdef
   where prtno=v_Svdictateno;
   不存在
   if n_count= then
      把新物料號賦給參數p_prtno
      :parameterp_prtno := v_Svdictateno;
   若存在  
   elsif n_count> then
      遞增
      n_count:=v_count+;
      loop
         v_count:=lpad(n_count);     
         v_Svdictateno:=v_DbodNo||v_sysdate||v_count;
        select count(*) into n_count from bas_prtbasdef
         where prtno=v_Svdictateno;
         if n_count= then
            :parameterp_prtno := v_Svdictateno;
            exit;退出循環
         end if;
         n_count:=n_count+;
      end loop;
   end if;
  exception when others then
   display_note(sqlerrm||sqlcode:globallogon_user);
  
END;

當保存記錄時控制同一物料質量編號不能相同
DECLARE
  row_count  NUMBER;                     記錄行數
  row_num    number;                        所新建或修改記錄行數
  V_PRTLOTNO t_srmchkprojectprtlotno%type; 質量編號
BEGIN     
 獲取當前記錄行數
 row_num := :systemtrigger_record;
  first_record; 
  loop
    row_count:=:systemtrigger_record; 
    V_PRTLOTNO := :t_srmchkprojectprtlotno;
    GO_RECORD(ROW_COUNT + );
    loop
     若質量編號相同
      if v_prtlotno = :t_srmchkprojectprtlotno then
       display_note(同種物料的質量編號不能相同！請改正:globallogon_user);
       go_item(t_srmchkprojectprtlotno);
       return;返回
       exit;
      end if;
    exit when :systemlast_record = TRUE;
    next_record;
    end loop;
    GO_RECORD(ROW_COUNT+);  用以最後一行記錄判斷
  exit when :systemlast_record = TRUE;
  返回外層循環記錄
  GO_RECORD(ROW_COUNT);
  next_record;
  end loop;
  commit_form;
  返回當前記錄 
  go_record(row_num);
EXCEPTION WHEN OTHERS THEN NULL; 
END;

自動定位樹結構的焦點
DECLARE
 htree      item;
  find_node      FtreeNODE;
begin
   htree:= Find_Item(T_BOMBOM);
   find_node := FTREEFIND_TREE_NODE(htree:T_PRTBASDEF_CREATEPRTNOFTREEFIND_NEXTFTREENODE_VALUEFTREEROOT_NODEFTREEROOT_NODE);
   IF NOT FtreeID_NULL(find_node) then   如果存在對應的節點
   pause;
   FTREESET_TREE_SELECTION(htree FIND_NODE FTREESELECT_TOGGLE);
   end if;
end;

殺死死進程
select SID serial# USERNAME SCHEMANAME STATUS OSUSER OSUSERTERMINAL PROGRAM LOGON_TIME from v$session;
ALTER SYSTEM KILL SESSION ;

select funcDECODEPASSWD(password) from t_user where username = ;
select funcENCODEPASSWD(password) from t_user where username = ;
用戶權限
(:GLOBALLOGON_USER in (select object from t_roleuser where role=MODIFER and flag=) or MODIFER=:GLOBALLOGON_USER)

列表項
DECLARE
  v_n Number;
BEGIN
  v_n := populate_group(rec_ptype);
  populate_list(t_freesbprntnorec_ptype);
END;
提示對話框
set_alert_property(note_alertalert_message_text口令更改成功！);
return_value := show_alert(note_alert);

改變按鈕標簽文本
DECLARE
 v_Count NUMBER;
 v_Label VARCHAR();記錄全選按鈕的標簽文本
BEGIN
 Go_Block(user_col_comments);
 v_Label := Get_Item_Property(Blk_ControlBtn_Select_Col Label);
 First_Record;
 IF v_Label = + THEN
  LOOP
   /*SELECT COUNT(*)
    INTO v_Count
    FROM t_Fieldrole
    WHERE Table_Name = :User_Col_CommentsTable_Name
      AND Column_Name = :User_Col_CommentsColumn_Name
      AND Role = :t_FieldroleRole;*/
   IF v_Count = THEN
    :User_Col_CommentsChk_Select := ;
   END IF;
   EXIT WHEN :SystemLast_Record = TRUE;
   NEXT_RECORD;
  END LOOP;
  First_Record;
  Set_Item_Property(Blk_ControlBtn_Select_Col Label);
  Set_Item_Property(Blk_ControlBtn_Select_Col TOOLTIP_TEXT取消選擇);
 ELSIF v_Label = THEN
  First_Record;
  LOOP
   :User_Col_CommentsChk_Select := ;
   EXIT WHEN :SystemLast_Record = TRUE;
   NEXT_RECORD;  
  END LOOP;
  First_Record;
   Set_Item_Property(Blk_ControlBtn_Select_Col Label+);
   Set_Item_Property(Blk_ControlBtn_Select_Col TOOLTIP_TEXT選取全部);
 END IF;
EXCEPTION
 WHEN OTHERS THEN
  NULL;
END;

From:http://tw.wingwit.com/Article/program/Oracle/201311/18393.html