1

Topic: Slightly on PL/SQL I will ask

Health I wish,
Help  in pl\skl,
1.   function in pl\skl to which input the certain text divided arbitrary kol-vom of gaps is transferred, as a result to receive the text divided strictly by one gap
2. Function of creation postponed , on an input: id, start date \time, pl\skl the code; if id exists, the code to update, differently - to create

2

Re: Slightly on PL/SQL I will ask

regexp_replace (str, "\s +", ' ');

3

Re: Slightly on PL/SQL I will ask

And still slightly)
There is a table of debtors (person):
Id_person, FIO
1, Ivanov I.I.
2, Petrov I.I.
3, Sidorov. ..
4, Sergeev I.I.
Etc.
There is a table of debts (debt):
Id_person, Id_debt, Id_portfolio, Debt_sum
1,1,1,100
1,2,2,200
3,3,1,300
4,4,3,400
Etc.
There is a table of briefcases (portfolio):
Id_portfolio,Pportfolio_name,Sign_date,End_date
1, PORTFOLIO_1,01.01.2011,21.01.2013
2, PORTFOLIO_2,06.05.2012,20.09.2012
3, PORTFOLIO_3,21.11.2012,16.03.2013
4, PORTFOLIO_4,01.12.2012,11.06.2013
Etc.
There is a table of months (the first day of each month) (calendar):
Cal_date
01.01.2011
01.02.2011
01.03.2011
01.04.2011
Etc.
There is a table payments (payment):
Id_debt, Payment_sum, Date
1,10,12.05.2012
1,20,28.05.2012
3,30,10.06.2012
4,10,01.12.2012
Etc.
IT IS REQUIRED:
To select debtors with the total to collecting (the total of all debts of the debtor) more than 150 roubles
On every month to count a briefcase in operation.
Briefcase in operation - the total of debts which were in operation at least one day in a month:
Cal_date, Portfolio_sum
..., ...
01.05.2012,600
..., ...
01.12.2012,800
Etc.
To count efficiency on each briefcase (the total of all payments/sums of all debts):
Portfolio_name, Efficiency
PORTFOLIO_1,15%
PORTFOLIO_2,0%
PORTFOLIO_3,2.5%
Etc.
To count efficiency in every month (the total of all payments for month/portfolio of operation for a month)
Cal_date, Efficiency
..., ...
01.05.2012,5%
..., ...
01.12.2012,1.25%
Etc.
To select all debts without payments

4

Re: Slightly on PL/SQL I will ask

Write though that that, try also who  to you helps.

5

Re: Slightly on PL/SQL I will ask

Jebrail wrote:

try

It hardly.
More likely too writes about "all of you malicious" and "something strange from me want".
And, still about "10 years on a forum did not come".
. It is time on pension. Or in holiday...

6

Re: Slightly on PL/SQL I will ask

by... wrote:

it is passed...
It hardly.
More likely too writes about "all of you malicious" and "something strange from me want".
And, still about "10 years on a forum did not come".
. It is time on pension. Or in holiday...

Not, well not absolutely so, but. Want, really, strange.
I not , fur-trees-sticks. I try here "on a paper" to understand logic,  to count ...
And Dzhebrailu thanks I will tell, helps than , unlike  wink

7

Re: Slightly on PL/SQL I will ask

dekloper wrote:

I not ,

... To   also addressed.
P.S.
Cold that-whether? ...

8

Re: Slightly on PL/SQL I will ask

dekloper wrote:

health I wish,
Help  in pl\skl,
2. Function of creation postponed , on an input: id, start date \time, pl\skl the code; if id exists, the code to update, differently - to create

dbms_job
1) to create SUBMIT Procedure - Submits a new job to the job queue
2)
CHANGE Procedure - Alters any of the user-definable parameters associated with a job
WHAT Procedure - Alters the job description for a specified job

Not sickly rights are necessary
.....
stax

9

Re: Slightly on PL/SQL I will ask

Stax wrote:

not sickly rights

As a whole, a lie are necessary.

10

Re: Slightly on PL/SQL I will ask

dekloper wrote:

and still slightly)
There is a table of debtors (person):

The spring, laziness to type labels
in a type

SQL> ed
Wrote file afiedt.buf
1 with person (Id_person, FIO) as (
2 select 1, ' Ivanov I.I. ' from dual union all
3 select 2, ' Petrov I.I. ' from dual union all
4 select 3, ' Sidorov. .. ' from dual union all
5 select 4, ' Sergeev I.I. ' from dual)
6* select * from person
SQL> /
ID_PERSON FIO
---------- -------------
1 Ivanov I.I.
2 Petrov I.I.
3 Sidorov. ..
4 Sergeev I.I.
SQL>

.......
stax

11

Re: Slightly on PL/SQL I will ask

Elic wrote:

it is passed...
As a whole, a lie.

In what a lie?  with =7 exists from other circuit what it is necessary  to change?

I do not have rights
....
stax

12

Re: Slightly on PL/SQL I will ask

Stax wrote:

  with =7 exists

And who about dbms_job asked you?
Also give you, at last, at least to one impudent beggar to be bent.

13

Re: Slightly on PL/SQL I will ask

dekloper wrote:

2. Function of creation postponed , on an input: id, start date \time, pl\skl the code; if id exists, the code to update, differently - to create

2 Stax
imho, instead of either to update, or to create job - easier each time to create new job (one-time, self-liquidated after working off)

14

Re: Slightly on PL/SQL I will ask

Viewer wrote:

it is passed...
2 Stax
imho, instead of either to update, or to create job - easier each time to create new job (one-time, self-liquidated after working off)

other task
existing  can   (with an interval)
.....
stax

15

Re: Slightly on PL/SQL I will ask

Stax wrote:

  other task

I would argue, but is inclined to agree with Elic th...

16

Re: Slightly on PL/SQL I will ask

Viewer wrote:

it is passed...
I would argue, but is inclined to agree with Elic th...

Good, we come on the other hand
How to change job (id=7) in "other/another's circuit"?

I do not understand an important difference + and simply to replace
And existing can was it is created and with another  a surrounding
.....
stax

17

Re: Slightly on PL/SQL I will ask

Stax wrote:

how to change job (id=7) in "other/another's circuit"?

the Strange setting of a question. What for?

18

Re: Slightly on PL/SQL I will ask

Stax;
To setting of the task 2 there are questions, in it many not clear moments which can affect the decision.
. Now I will use your approval smile

19

Re: Slightly on PL/SQL I will ask

Elic wrote:

it is passed...
The strange setting of a question. What for?

I do not know, with  to architects (dekloper)
.....
stax

20

Re: Slightly on PL/SQL I will ask

Viewer wrote:

Stax;
To setting of the task 2 there are questions, in it many not clear moments which can affect the decision.
. Now I will use your approval smile

smile))

To fulfill

wrote:

2. Function of creation postponed , on an input: id, start date \time, pl\skl the code; if id exists, that the code to update , differently - to create

For me it is uneasy
.....
stax

21

Re: Slightly on PL/SQL I will ask

Stax wrote:

I do not know, with  to architects (dekloper)

I Repeat, you are itself invented.

22

Re: Slightly on PL/SQL I will ask

Elic wrote:

it is passed...
I repeat, you are itself invented.

I repeat

dekloper wrote:

health I wish,
Help  in pl\skl,
2. Function of creation postponed , on an input: id, start date \time, pl\skl the code; if id exists, the code to update, differently - to create

.....
stax

23

Re: Slightly on PL/SQL I will ask

Stanislav, act in film from a bench hammer and look for a word dbms_job in a question.
Control in a head: in dba_jobs is not present id.

24

Re: Slightly on PL/SQL I will ask

Elic wrote:

Stanislav, act in film from a bench hammer and look for a word dbms_job in a question.
Control in a head: in dba_jobs is not present id.

Eternally  a bench hammer
, on a context it is clear that I meant under  ( from the identifier)
JOB NUMBER NOT NULL Identifier of job. Neither import/export nor repeated executions change this value.
As the field (parameter) is called in a label/twist I anywhere did not mention
Named job  Let it pass (them  the right),  to name  job_id (so more it is pleasant to me)

On dba_jobs it is necessary not sickly rights
....
stax

25

Re: Slightly on PL/SQL I will ask

Elic wrote:

Stanislav, act in film from a bench hammer and look for a word dbms_job in a question.

Read about a bench hammer, reddened and up to the end a phrase did not disassemble
In a question the word dbms_job () should be not mandatory
And here in the answer it is heavy to do without a mention dbms_job (DBMS_SCHEDULER)
By the way in DBMS_SCHEDULER considered my discontent and instead of  already a job name
.....
stax