Temos pavadinimas: WordPress, Shopify ir PHPFusion programuotojų bendruomenė :: Šauktinių generatoriaus skriptas

Parašė Istrinti000123· 2015 Geg. 14 21:05:25
#1

Labas, šiandiena nagrinėju šauktinių generatoriaus skript`ą, pagalvojau gal ir jums bus įdomu panagrinėti:

Forma Saukimo_sarasas.txt

Option Compare Database
Option Explicit

Private Sub pbNaujas_Sarasas_Click()
On Error GoTo HandleErr
    Dim qDef1 As QueryDef
    Dim qDef2 As QueryDef
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim txtSrasoID As String
   
    'Autorius: ArtuCer
    'Modifikavimo data: 2015-05-07
   
    'Patikriname ar einamaisiais metais jau yra sukurtas sąrašas
    Set rst1 = CurrentDb.OpenRecordset("SELECT count(*) As Kiekis " & _
        "FROM PRSK " & _
        "WHERE year(SarasoData)=year(Date())")
    If rst1("Kiekis") > 0 Then
        MsgBox Year(Date) & " metų šaukimo sąrašai jau yra registruoti!" & vbLf & "Sąrašų generavimo procedūra nutraukiama.", _
            vbInformation, "Informacija"
        Exit Sub
    End If
   
    Set qDef1 = CurrentDb.QueryDefs("qryfrm23_0")
    Set qDef2 = CurrentDb.QueryDefs("qryfrm23_0_exec")
   
    'surandame regionų kiekį
    qDef1.SQL = "SELECT count(*) As Kiekis " & _
        "FROM PerVIS.kdch_reg"
    Set rst1 = CurrentDb.OpenRecordset("SELECT * FROM qryfrm23_0")
    Me.prbProgressBar.Value = 0
    Me.prbProgressBar.Max = 4 + 2 * rst1("Kiekis")
   
    'surandame naujo sąrašo ID
    Set rst1 = CurrentDb.OpenRecordset("SELECT SarasoID " & _
        "FROM PRSK " & _
        "WHERE SarasoID Like 'A%' " & _
            "AND Len(SarasoID)=3 " & _
        "ORDER BY SarasoData DESC")
    If Not rst1.EOF Then
        txtSrasoID = "A" & Format(CInt(Mid(rst1("SarasoID"), 2)) + 1, "00")
    Else
        txtSrasoID = "A01"
    End If
   
   
    'Sąrašų generavimo inicijavimas
    Me.prbProgressBar.Value = Me.prbProgressBar.Value + 1
    Me.txtProgress = "Sąrašų generavimo inicijavimas"
    DoEvents
    Me.Repaint
    Me.Visible = True
   
    qDef2.SQL = "BEGIN PerVIS.SAUKIMO_SARASAS.Nauju_Sarasu_Inicijavimas('" & txtSrasoID & "'); END;"
    qDef2.Execute
       
    Set rst2 = CurrentDb.OpenRecordset("SELECT count(*) As Kiekis " & _
        "FROM PRSK " & _
        "WHERE SarasoID Like '" & txtSrasoID & "*'")
    If rst2("Kiekis") = 0 Then
        MsgBox "Klaida " & txtSrasoID & "* sarašų generavimo inicijavime. Procedūra nutraukiama.", vbCritical, "Klaida"
        Exit Sub
    End If


    'Generuojamas Axx sąrašas
    Me.prbProgressBar.Value = Me.prbProgressBar.Value + 1
    Me.txtProgress = "Generuojamas " & txtSrasoID & " sąrašas"
    DoEvents
    Me.Repaint
    Me.Visible = True
   
    qDef2.SQL = "BEGIN PerVIS.SAUKIMO_SARASAS.Naujas_Sarasas_A('" & txtSrasoID & "'); END;"
    qDef2.Execute
       
    Set rst2 = CurrentDb.OpenRecordset("SELECT count(*) As Kiekis " & _
        "FROM PRS_A " & _
        "WHERE SarasoID='" & txtSrasoID & "'")
    If rst2("Kiekis") = 0 Then
        MsgBox "Klaida generuojant " & txtSrasoID & " sąrašą. Procedūra nutraukiama.", vbCritical, "Klaida"
        Exit Sub
    End If


    'Generuojamas Axxyy sąrašas
    qDef1.SQL = "SELECT RegionoKodas, SkyriausOrgNo, PoskyrioOrgNo " & _
        "FROM PerVIS.kdch_reg " & _
        "ORDER BY 1"
    Set rst1 = CurrentDb.OpenRecordset("SELECT * FROM qryfrm23_0")
    Do While Not rst1.EOF
        Me.prbProgressBar.Value = Me.prbProgressBar.Value + 1
        Me.txtProgress = "Generuojamas " & txtSrasoID & rst1("RegionoKodas") & " sąrašas"
        DoEvents
        Me.Repaint
        Me.Visible = True

        qDef2.SQL = "BEGIN PerVIS.SAUKIMO_SARASAS.Naujas_Sarasas_B('" & txtSrasoID & "', '" & rst1("RegionoKodas") & "', " & rst1("SkyriausOrgNo") & ", " & rst1("PoskyrioOrgNo") & "); END;"
        qDef2.Execute
           
        Set rst2 = CurrentDb.OpenRecordset("SELECT count(*) As Kiekis " & _
            "FROM PRS_B " & _
            "WHERE SarasoID='" & txtSrasoID & rst1("RegionoKodas") & "'")
        If rst2("Kiekis") = 0 Then
            MsgBox "Klaida generuojant " & txtSrasoID & rst1("RegionoKodas") & " sąrašą. Procedūra nutraukiama.", vbCritical, "Klaida"
            Exit Sub
        End If

        rst1.MoveNext
    Loop 'rst1
   

    'Išsaugoma sarašų kontrolinės sumos
    qDef1.SQL = "SELECT SarasoID " & _
        "FROM PRSK " & _
        "WHERE SarasoID Like '" & txtSrasoID & "%' " & _
        "ORDER BY SarasoID"
    Set rst1 = CurrentDb.OpenRecordset("SELECT * FROM qryfrm23_0")
    Do While Not rst1.EOF
        Me.prbProgressBar.Value = Me.prbProgressBar.Value + 1
        Me.txtProgress = "Išsaugoma sarašo " & rst1("SarasoID") & " kontrolinė suma"
        DoEvents
        Me.Repaint
        Me.Visible = True

        qDef2.SQL = "BEGIN PerVIS.SAUKIMO_SARASAS.Issaugoma_Kontroline_Suma('" & rst1("SarasoID") & "'); END;"
        qDef2.Execute
           
        Set rst2 = CurrentDb.OpenRecordset("SELECT count(*) As Kiekis " & _
            "FROM PRSK " & _
            "WHERE SarasoID='" & rst1("SarasoID") & "' " & _
                "AND KontrolineSuma='0'")
        If rst2("Kiekis") > 0 Then
            MsgBox "Klaida išsaugant " & rst1("SarasoID") & " sąrašo kontrolinę sumą. Procedūra nutraukiama.", vbCritical, "Klaida"
            Exit Sub
        End If

        rst1.MoveNext
    Loop 'rst1
   

    'sarašų generavimo užbaigimas
    Me.prbProgressBar.Value = Me.prbProgressBar.Value + 1
    Me.txtProgress = "Sąrašų generavimo užbaigimas"
    DoEvents
    Me.Repaint
    Me.Visible = True
   
    qDef2.SQL = "BEGIN PerVIS.SAUKIMO_SARASAS.Nauju_Sarasu_Uzbaigimas; END;"
    qDef2.Execute
       
    'Clear ProgressBar
    Me.prbProgressBar.Value = 0
    Me.txtProgress = ""
    Me.Visible = True
   
    Forms!frm23_0!ufrm23_0_A.Requery
   
    MsgBox "Sąrašų generavimo procedūra užbaigta sėkmingai.", vbInformation, "Informacija"

ExitHere:
    Exit Sub

HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "frm23_0.pbNaujas_Sarasas_Click"
    End Select
End Sub

Private Sub pbQuit_Click()
On Error GoTo HandleErr
   
    DoCmd.Close acForm, "frm23_0"

ExitHere:
    Exit Sub

HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "frm23_0.pbQuit_Click"
    End Select
End Sub

Private Sub pbTikrinti_Click()
On Error GoTo HandleErr
On Error GoTo HandleErr
    Dim qDef1 As QueryDef
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim txtRezultatas As String
    Dim strData As Date

    'Autorius: ArtuCer
    'Modifikavimo data: 2015-05-07

    Set qDef1 = CurrentDb.QueryDefs("qryfrm23_0")
   
    'surandame regionų kiekį
    Set rst1 = CurrentDb.OpenRecordset("SELECT Count(*) AS Kiekis " & _
        "FROM PRSK " & _
        "WHERE SarasoID Like '" & [Forms]![frm23_0]![ufrm23_0_A].[Form]![SARASOID] & "*'")
    Me.prbProgressBar.Value = 0
    Me.prbProgressBar.Max = rst1("Kiekis")
   
    txtRezultatas = "Sarašo ID" & vbTab & Right("                     " & "Kontrolinė suma", 20) & vbTab & vbTab & "Trukmė" & vbLf
   
    'surandame sąrašo ID
    Set rst1 = CurrentDb.OpenRecordset("SELECT SarasoID " & _
        "FROM PRSK " & _
        "WHERE SarasoID Like '" & [Forms]![frm23_0]![ufrm23_0_A].[Form]![SARASOID] & "*' " & _
        "ORDER BY SarasoID")
    Do While Not rst1.EOF
        Me.prbProgressBar.Value = Me.prbProgressBar.Value + 1
        Me.txtProgress = "Skaičiuojama " & rst1("SarasoID") & " kontrolinė suma"
        DoEvents
        Me.Repaint
        Me.Visible = True
   
        strData = Now()
        qDef1.SQL = "SELECT PerVIS.SAUKIMO_SARASAS.Saraso_Kontroline_Suma('" & rst1("SarasoID") & "') As Kontroline_Suma " & _
            "FROM dual"
        Set rst2 = CurrentDb.OpenRecordset("SELECT Kontroline_Suma FROM qryfrm23_0")
   
        If Not rst2.EOF Then
            txtRezultatas = txtRezultatas & _
                Right("    " & rst1("SarasoID"), 8) & vbTab & Right("                     " & rst2("Kontroline_Suma"), 20) & vbTab & vbTab & "  " & Format(Now() - strData, "n") * 60 + Format(Now() - strData, "s") & " sek." & vbLf
        Else
            txtRezultatas = txtRezultatas & _
                Right("    " & rst1("SarasoID"), 8) & vbTab & Right("                     " & "Klaida!", 20) & vbTab & vbTab & "  " & Format(Now() - strData, "n") * 60 + Format(Now() - strData, "s") & " sek." & vbLf
        End If
   
        rst1.MoveNext
    Loop 'rst1
   
    'Clear ProgressBar
    Me.prbProgressBar.Value = 0
    Me.txtProgress = ""
    Me.Visible = True
   
    MsgBox txtRezultatas, vbInformation, "Informacija"

ExitHere:
    Exit Sub

HandleErr:
    Select Case Err.Number
        Case 2427
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "frm23_0.pbTikrinti_Click"
    End Select
End Sub





Ir
Package Saukimo_Sarasas.txt

CREATE OR REPLACE PACKAGE SAUKIMO_SARASAS AS
-- Autorius: ArtuCer
-- Modifikavimo data: 2015-04-30

  --Ðaukimo sàraðø generavimo inicijavimas
  PROCEDURE Nauju_Sarasu_Inicijavimas (in_SarasoID IN VARCHAR2);

  --Generuojamas A sàraðas
  PROCEDURE Naujas_Sarasas_A (in_SarasoID IN VARCHAR2);

  --Generuojamas regioninis A sàraðas
  PROCEDURE Naujas_Sarasas_B (in_SarasoID IN VARCHAR2, in_RegionoKodas IN VARCHAR2, in_SkyriausOrgNo IN INTEGER, in_PoskyrioOrgNo IN INTEGER);

  --Skaièiuojama sàraðo kontrolinë suma
  FUNCTION Saraso_Kontroline_Suma (in_SarasoID IN VARCHAR2) RETURN VARCHAR2;

  --Iðsaugoma sàraðo kontrolinë suma
  PROCEDURE Issaugoma_Kontroline_Suma (in_SarasoID IN VARCHAR2);

  --Ðaukimo sàraðø generavimo uþbaigimas
  PROCEDURE Nauju_Sarasu_Uzbaigimas;

  --Ðaukimo sàraðø generavimas nuo A iki Z
  PROCEDURE Sarasu_Generavimas_Nuo_A_Iki_Z (in_SarasoID IN VARCHAR2);

END SAUKIMO_SARASAS;
/


CREATE OR REPLACE PACKAGE BODY SAUKIMO_SARASAS AS
-- Autorius: ArtuCer
-- Modifikavimo data: 2015-05-07

  --Ðaukimo sàraðø generavimo inicijavimas
  PROCEDURE Nauju_Sarasu_Inicijavimas (in_SarasoID IN VARCHAR2) AS
    v_data DATE;
    v_filehandle UTL_FILE.FILE_TYPE;
  BEGIN
    --Atidaromas log failas
    v_filehandle := UTL_FILE.FOPEN ('ORACLE_LOG_DIR', 'PerVIS_Saukimo_sarasu_generavimas.log', 'a');

    UTL_FILE.PUTF (v_filehandle, 'Ðaukimo sàraðø generavimas pradëtas: %s\n\n', TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
 
    v_data:=sysdate;
    INSERT INTO PRSK (SarasoID, SarasoData, TeritOrgNo, KontrolineSuma, ObjektuSkaicius, RevOrgNo, UserID, RevisionDate)
      SELECT in_SarasoID, trunc(SysDate), Null, 0, 0, 0, User, SysDate
      FROM dual
      UNION
      SELECT in_SarasoID||RegionoKodas, trunc(SysDate), SkyriausOrgNo, 0, 0, 0, User, SysDate
      FROM kdch_reg;
 
    UTL_FILE.PUTF (v_filehandle, '     - sàraðø kontroliniø áraðø kiekis: %s     %s\n', SQL%ROWCOUNT, TO_CHAR(TO_DATE('00:00:00','HH24:MI:SS')+(SYSDATE-v_data),'HH24:MI:SS'));
 
    COMMIT;

    UTL_FILE.FCLOSE (v_filehandle);

  EXCEPTION
 
    WHEN UTL_FILE.INVALID_PATH THEN
      RAISE_APPLICATION_ERROR (-20001, 'Blogas log failo kelias.');
      UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      RAISE_APPLICATION_ERROR (-20002, 'Blogas log failas.');
      UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.WRITE_ERROR THEN
      RAISE_APPLICATION_ERROR (-20003, 'Negalima áraðyti á log failà.');
      UTL_FILE.FCLOSE_ALL;
    WHEN OTHERS THEN
      UTL_FILE.PUTF (v_filehandle, 'Klaida: %s\n', SQLERRM);
      UTL_FILE.PUTF (v_filehandle, 'Ðaukimo sàraðø generavimo inicijavimas nutrauktas: %s\n\n', TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
      UTL_FILE.PUT_LINE (v_filehandle, '------------------------------------------------------------------');
      UTL_FILE.NEW_LINE (v_filehandle);
      UTL_FILE.FCLOSE_ALL;
  END Nauju_Sarasu_Inicijavimas;


  --Generuojamas A sàraðas
  PROCEDURE Naujas_Sarasas_A (in_SarasoID IN VARCHAR2) AS
    v_data DATE;
    v_filehandle UTL_FILE.FILE_TYPE;
  BEGIN
    --Atidaromas log failas
    v_filehandle := UTL_FILE.FOPEN ('ORACLE_LOG_DIR', 'PerVIS_Saukimo_sarasu_generavimas.log', 'a');

    v_data:=sysdate;
    INSERT INTO PRS_A (PersonalCode, SarasoID, EilesNr, AtsitiktinisSkaicius, RevOrgNo, UserID, RevisionDate)
      SELECT PersonalCode, in_SarasoID, rownum as Eil_Nr, atsitiktinis, 0, User, SysDate
      FROM (SELECT dbms_random.value as atsitiktinis, PersonalCode
            FROM PRV
            WHERE PersonalCode Not In(SELECT PersonalCode FROM PRA WHERE KKPAKodas In('V','Z','X'))
            ORDER BY 1) a1;
   
    UTL_FILE.PUTF (v_filehandle, '     - %s sàraðo áraðø kiekis: %s     %s\n', in_SarasoID, SQL%ROWCOUNT, TO_CHAR(TO_DATE('00:00:00','HH24:MI:SS')+(SYSDATE-v_data),'HH24:MI:SS'));

    COMMIT;

    UTL_FILE.FCLOSE (v_filehandle);

  EXCEPTION
   
    WHEN UTL_FILE.INVALID_PATH THEN
      RAISE_APPLICATION_ERROR (-20001, 'Blogas log failo kelias.');
      UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      RAISE_APPLICATION_ERROR (-20002, 'Blogas log failas.');
      UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.WRITE_ERROR THEN
      RAISE_APPLICATION_ERROR (-20003, 'Negalima áraðyti á log failà.');
      UTL_FILE.FCLOSE_ALL;
    WHEN OTHERS THEN
      UTL_FILE.PUTF (v_filehandle, 'Klaida: %s\n', SQLERRM);
      UTL_FILE.PUTF (v_filehandle, 'Axx sàraðo generavimas nutrauktas: %s\n\n', TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
      UTL_FILE.PUT_LINE (v_filehandle, '------------------------------------------------------------------');
      UTL_FILE.NEW_LINE (v_filehandle);
      UTL_FILE.FCLOSE_ALL;
  END Naujas_Sarasas_A;


  --Generuojamas regioninis A sàraðas
  PROCEDURE Naujas_Sarasas_B (in_SarasoID IN VARCHAR2, in_RegionoKodas IN VARCHAR2, in_SkyriausOrgNo IN INTEGER, in_PoskyrioOrgNo IN INTEGER) AS
    v_data DATE;
    v_filehandle UTL_FILE.FILE_TYPE;
  BEGIN
    --Atidaromas log failas
    v_filehandle := UTL_FILE.FOPEN ('ORACLE_LOG_DIR', 'PerVIS_Saukimo_sarasu_generavimas.log', 'a');

    v_data:=sysdate;
    INSERT INTO PRS_B (PersonalCode, SarasoID, EilesNr, TeritOrgNo, RevOrgNo, UserID, RevisionDate)
      SELECT PersonalCode, in_SarasoID||in_RegionoKodas, rownum as Eil_Nr, OfficeCode, 0, User, SysDate
      FROM (SELECT PRS_A.EilesNr, PRS_A.PersonalCode, DCH.OfficeCode
            FROM PRS_A
              INNER JOIN PER ON PRS_A.PersonalCode=PER.PersonalCode
              INNER JOIN PRV ON PRS_A.PersonalCode=PRV.PersonalCode
              INNER JOIN DCH ON PRS_A.PersonalCode=DCH.PersonalCode
            WHERE PRS_A.SarasoID=in_SarasoID
              --iki einamøjø metø pradþios sukakæs 19 - 25 metø amþius
              AND PER.BirthDate BETWEEN add_months(trunc(sysdate,'YEAR'),-26*12) AND add_months(trunc(sysdate,'YEAR'),-19*12)-1
              --neátraukti kandidatø á NPPKT kuriø priëmimo bûsena "Vykdomas priëmimas"
              AND PRS_A.PersonalCode Not IN (SELECT PersonalCode
                                              FROM TKT
                                              WHERE TKT.KTKTKodas='S'
                                                AND TKT.KTKBKodas=0
                                                AND RegistravimoData<sysdate
                                                AND nvl(PabaigosData,'2100-12-31')>sysdate)
              AND PRV.KKPTKodas='N' --neparengtojo rezervo karys
              AND (DCH.OfficeCode=in_SkyriausOrgNo OR DCH.OfficeCode=in_PoskyrioOrgNo)
            ORDER BY 1) a1;

    UTL_FILE.PUTF (v_filehandle, '     - %s%s sàraðo áraðø kiekis: %s     %s\n', in_SarasoID, in_RegionoKodas, SQL%ROWCOUNT, TO_CHAR(TO_DATE('00:00:00','HH24:MI:SS')+(SYSDATE-v_data),'HH24:MI:SS'));
 
    COMMIT;
   
    UTL_FILE.FCLOSE (v_filehandle);

  EXCEPTION
 
    WHEN UTL_FILE.INVALID_PATH THEN
      RAISE_APPLICATION_ERROR (-20001, 'Blogas log failo kelias.');
      UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      RAISE_APPLICATION_ERROR (-20002, 'Blogas log failas.');
      UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.WRITE_ERROR THEN
      RAISE_APPLICATION_ERROR (-20003, 'Negalima áraðyti á log failà.');
      UTL_FILE.FCLOSE_ALL;
    WHEN OTHERS THEN
      UTL_FILE.PUTF (v_filehandle, 'Klaida: %s\n', SQLERRM);
      UTL_FILE.PUTF (v_filehandle, 'Axxyy saraðo generavimas nutrauktas: %s\n\n', TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
      UTL_FILE.PUT_LINE (v_filehandle, '------------------------------------------------------------------');
      UTL_FILE.NEW_LINE (v_filehandle);
      UTL_FILE.FCLOSE_ALL;
  END Naujas_Sarasas_B;


  --Skaièiuojama saraðo kontrolinë suma
  FUNCTION Saraso_Kontroline_Suma (in_SarasoID IN VARCHAR2) RETURN VARCHAR2 AS
    v_Kontroline_Suma VARCHAR2(32);
  BEGIN
    IF LENGTH(in_SarasoID)=3 THEN
      FOR ks_rec IN (SELECT to_char(sum(owa_opt_lock.checksum(PRS_A.PersonalCode||PRS_A.SarasoID||PRS_A.EilesNr||PRS_A.AtsitiktinisSkaicius))) CHECKSUM
                      FROM prs_A
                      WHERE PRS_A.SarasoID=in_SarasoID)
      LOOP
        v_Kontroline_Suma:=nvl(ks_rec.CHECKSUM,0);
       
        EXIT;
      END LOOP;
    ELSE
      FOR ks_rec IN (SELECT to_char(sum(owa_opt_lock.checksum(PRS_B.PersonalCode||PRS_B.SarasoID||PRS_B.EilesNr||PRS_B.TeritOrgNo))) CHECKSUM
                      FROM prs_B
                      WHERE PRS_B.SarasoID=in_SarasoID)
      LOOP
        v_Kontroline_Suma:=nvl(ks_rec.CHECKSUM,0);
       
        EXIT;
      END LOOP;
    END IF;

    RETURN v_Kontroline_Suma;

  END Saraso_Kontroline_Suma;


  --Iðsaugoma saraðo kontrolinë suma
  PROCEDURE Issaugoma_Kontroline_Suma (in_SarasoID IN VARCHAR2) AS
    v_Kontroline_Suma VARCHAR2(32);
    v_data DATE;
    v_filehandle UTL_FILE.FILE_TYPE;
  BEGIN
    --Atidaromas log failas
    v_filehandle := UTL_FILE.FOPEN ('ORACLE_LOG_DIR', 'PerVIS_Saukimo_sarasu_generavimas.log', 'a');

    v_data:=sysdate;
    v_Kontroline_Suma:=SAUKIMO_SARASAS.Saraso_Kontroline_Suma(in_SarasoID);
    IF LENGTH(in_SarasoID)=3 THEN
      UPDATE PRSK SET
          KontrolineSuma=v_Kontroline_Suma,
          ObjektuSkaicius=(SELECT count(*)
              FROM prs_A
              WHERE PRSK.SarasoID=PRS_A.SarasoID),
          RevOrgNo=0,
          UserID=User,
          RevisionDate=sysdate
        WHERE SarasoID=in_SarasoID;
    ELSE
      UPDATE PRSK SET
          KontrolineSuma=v_Kontroline_Suma,
          ObjektuSkaicius=(SELECT count(*)
              FROM prs_B
              WHERE PRSK.SarasoID=PRS_B.SarasoID),
          RevOrgNo=0,
          UserID=User,
          RevisionDate=sysdate
        WHERE SarasoID=in_SarasoID;
    END IF;

    UTL_FILE.PUTF (v_filehandle, '     - %s sàraðo konrolinë suma: %s      %s\n', in_SarasoID, v_Kontroline_Suma, TO_CHAR(TO_DATE('00:00:00','HH24:MI:SS')+(SYSDATE-v_data),'HH24:MI:SS'));
 
    COMMIT;

    UTL_FILE.FCLOSE (v_filehandle);

  EXCEPTION
 
    WHEN UTL_FILE.INVALID_PATH THEN
      RAISE_APPLICATION_ERROR (-20001, 'Blogas log failo kelias.');
      UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      RAISE_APPLICATION_ERROR (-20002, 'Blogas log failas.');
      UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.WRITE_ERROR THEN
      RAISE_APPLICATION_ERROR (-20003, 'Negalima áraðyti á log failà.');
      UTL_FILE.FCLOSE_ALL;
    WHEN OTHERS THEN
      UTL_FILE.PUTF (v_filehandle, 'Klaida: %s\n', SQLERRM);
      UTL_FILE.PUTF (v_filehandle, 'Kontroliniø sumø iðsaugojimas nutrauktas: %s\n\n', TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
      UTL_FILE.PUT_LINE (v_filehandle, '------------------------------------------------------------------');
      UTL_FILE.NEW_LINE (v_filehandle);
      UTL_FILE.FCLOSE_ALL;
  END Issaugoma_Kontroline_Suma;


  --Ðaukimo sàraðø generavimo uþbaigimas
  PROCEDURE Nauju_Sarasu_Uzbaigimas AS
    v_filehandle UTL_FILE.FILE_TYPE;
  BEGIN
    --Atidaromas log failas
    v_filehandle := UTL_FILE.FOPEN ('ORACLE_LOG_DIR', 'PerVIS_Saukimo_sarasu_generavimas.log', 'a');
   
    UTL_FILE.NEW_LINE (v_filehandle);
    UTL_FILE.PUTF (v_filehandle, 'Ðaukimo sàraðø generavimas baigtas: %s\n\n', TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
    UTL_FILE.PUT_LINE (v_filehandle, '------------------------------------------------------------------');
    UTL_FILE.NEW_LINE (v_filehandle);

    UTL_FILE.FCLOSE (v_filehandle);

  EXCEPTION
 
    WHEN UTL_FILE.INVALID_PATH THEN
      RAISE_APPLICATION_ERROR (-20001, 'Blogas log failo kelias.');
      UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      RAISE_APPLICATION_ERROR (-20002, 'Blogas log failas.');
      UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.WRITE_ERROR THEN
      RAISE_APPLICATION_ERROR (-20003, 'Negalima áraðyti á log failà.');
      UTL_FILE.FCLOSE_ALL;
    WHEN OTHERS THEN
      UTL_FILE.PUTF (v_filehandle, 'Klaida: %s\n', SQLERRM);
      UTL_FILE.PUTF (v_filehandle, 'Ðaukimo saraðø generavimas nutrauktas: %s\n\n', TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
      UTL_FILE.PUT_LINE (v_filehandle, '------------------------------------------------------------------');
      UTL_FILE.NEW_LINE (v_filehandle);
      UTL_FILE.FCLOSE_ALL;
  END Nauju_Sarasu_Uzbaigimas;


  --Ðaukimo sàraðø generavimas nuo A iki Z
  PROCEDURE Sarasu_Generavimas_Nuo_A_Iki_Z (in_SarasoID IN VARCHAR2) AS
    v_SarasoID varchar(2);
    v_return varchar(8);
   
  BEGIN
   
    --Ðaukimo sàraðø generavimo inicijavimas
    SAUKIMO_SARASAS.Nauju_Sarasu_Inicijavimas(in_SarasoID);   
   
   
    --Generuojamas A sàraðas
    SAUKIMO_SARASAS.Naujas_Sarasas_A(in_SarasoID);   
   
   
    --Generuojamas regioninis A sàraðas
    FOR B_sarasas_rec IN (SELECT RegionoKodas, SkyriausOrgNo, PoskyrioOrgNo
      FROM kdch_reg
      ORDER BY 1)
    LOOP
      SAUKIMO_SARASAS.Naujas_Sarasas_B(in_SarasoID, B_sarasas_rec.RegionoKodas, B_sarasas_rec.SkyriausOrgNo, B_sarasas_rec.PoskyrioOrgNo);   
    END LOOP;
   
   
    --Iðsaugoma sàraðo kontrolinë suma
    FOR sarasoID_rec IN (SELECT SarasoID
                        FROM PRSK 
                        WHERE SarasoID Like in_SarasoID||'%'
                        ORDER BY SarasoID)
    LOOP
      SAUKIMO_SARASAS.Issaugoma_Kontroline_Suma(sarasoID_rec.SarasoID);   
    END LOOP;
   
   
    --Ðaukimo sàraðø generavimo uþbaigimas
    SAUKIMO_SARASAS.Nauju_Sarasu_Uzbaigimas;
   
  END Sarasu_Generavimas_Nuo_A_Iki_Z;

END SAUKIMO_SARASAS;
/



Ši eilutė idomi

WHERE PersonalCode Not In(SELECT PersonalCode FROM PRA WHERE KKPAKodas In('V','Z','X'))




Kas tie V, Z, X ?|

Redagavo ozzWANTED· 2015 Geg. 15 11:05:56

Parašė Pakartoti slaptažodį· 2015 Geg. 14 22:05:17
#2

galima būtų sužinot šaltinį?

Parašė minimukas· 2015 Geg. 14 22:05:32
#3

https://uzdarbis.lt/t342655/sauk...try2739359

Parašė ozzWANTED· 2015 Geg. 15 11:05:53
#4

Man rodos čia kažkokia neaiški programavimo kalba. Nebus koks senasis Visual Basic su savo "dim"

Parašė Žmogus· 2015 Geg. 16 10:05:39
#5

Čia VB su oracle kažkokiu tai papildomų dalykų :D