Shulou(Shulou.com)06/01 Report--

Create synonyms and sequences

1. Synonyms (synonym)

Synonyms are alternative names for objects. It is divided into public synonyms and private synonyms.

[create a public synonym]

SQL > create public synonym emp for pb.employees@prod

Then all users need to enter:

SQL > select * from emp

Private synonyms are pattern objects. They must either be in their own schema or must be qualified with a schema name. The existence of public synonyms has nothing to do with patterns.

Create synonym syntax:

CREATE [PUBLIC] SYNONYM synonym for OBJECT

The syntax for deleting synonyms:

DROP [PUBLIC] SYNONYM synonym

As with the view, the synonym is automatically recompiled the next time it is accessed, or it can be compiled explicitly:

ALTERSYNONYM synonym COMPILE

[case]

SQL > conn peenboo

SQL > createsynonym emp_s for emp_anon_v

SQL > createsynonym dept_s for dept_anon_v

SQL > createsynonym dsum_s for dep_sum_v

SQL > describeemp_s

SQL > describeemp_anon_v

SQL > select* from emp_s

SQL > insertinto dept_s values (99Med TEMPENTDEPTCHEM1800)

SQL > insertinto emp_s values (sysdate,'AC_MGR',10000,0,99)

SQL > updateemp_s set salary = salary * 1.1

SQL > rollback

SQL > selectmax (salaries / staff) from dsum_s

SQL > dropview emp_anon_v

SQL > dropview dept_anon_v

SQL > select* from dep_sum_v;-failed

SQL > alterview dept_sum_v compile;-- failed

SQL > dropview dep_sum_v

SQL > select* from dsum_s;-- failed

SQL > alter synonym emp_s compile;-No error is thrown, the view based on has been deleted, and synonyms are in a corrupted state

SQL > dropsynonym emp_s

SQL > dropsynonym dept_s

SQL > dropsynonym dsum_s

2. Sequence (sequence)

Sequence is the only integer worth generating structure. Is a valuable tool for generating primary keys. The sequence mechanism has nothing to do with tables, row locking mechanisms, and commit or rollback processes.

(1) create sequence syntax:

CREATESEQUENCE [shema.] sequencename

[INCREMENTBY number]

[STARTWITH number]

[MAXVALUEnumber | NOMAXVALUE]

[MINVALUEnumber | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHEnumber | NOCACHE]

[ORDER | NOORDER]

1. Sequence_name: specify the name of the sequence

2. STARTWITH start_num: specify the integer from which the sequence starts. The default value is 1.

3. INCREMENTBY increment_num: specify the increment of each increase in the sequence. This value defaults to 1.

4. MINVALUEminimum_num: specifies the minimum integer of the sequence, which must be less than or equal to start_num and less than maxmum_num

5. NOMINVALUE: specify that the minimum value of ascending sequence is 1, and that of descending sequence is-10 ^ 26

NOMINVALUE is the default

6. MAXVALUEmaximum_num: specify the maximum integer of the sequence, which must be greater than or equal to start_num and greater than minimum_num

7. NOMAXVALUE specifies that the maximum value of ascending sequence is 10 ^ 27, and that of descending sequence is-1.

NOMAXVALUE is the default

8. CYCLE: specifies that the sequence continues to generate integers even if it has reached the maximum or minimum, and when the ascending sequence reaches the maximum, the next generated value is the minimum. When the descending sequence reaches the minimum, the next generated value is the maximum

9. NOCYCLE: specifies that the sequence can no longer generate integers after reaching the maximum or minimum value. This is the default value.

10. CACHEcache_num: specify the number of integers to keep in memory. By default, 20 integers are cached, at least 2 integers can be cached, and the maximum number of integers that can be cached is

CELL (maximum_num-minimum_num) / ABS (increment_num)

11. NOCACHE specifies that no integers are cached

12. ORDER ensures that integers are generated in the order in which they are requested, which can be used when using RAC

13. NOORDERED does not ensure that integers are generated in the order of the request, which is the default value

SQL > createsequence seq1

(2) use sequence

Pseudo-column NEXTVAL selects the next value, and pseudo-column CURRVAL selects the previous value (current value) of the session.

Note: the CURRVAL of the sequence is the last value sent to the current session, not necessarily the wrong last value. CURRVAL cannot exist until NEXTVAL is selected.

CREATESEQUENCE order_seq start with 10

Createsequence line_seq start with 10

Insertinto orders (order_id,order_date,customer_id)

Values (order_seq.nextval,sysdate,'1000')

Insertinto order_items (order_id,order_item_id,product_id)

Values (order_seq.currval,line_seq.nextval,'A1111')

Insertinto order_items (order_id,order_item_id,product_id)

Values (order_seq.currval,line_seq.nextval,'B111')

If the analyst points out that the sequence cannot be spaced, then another way to generate a unique value must be used.

The current order can be stored in this table and initialized to 10:

Createtable current_on (order_number number)

Insertinto current_on values (10)

Commit

The code to create the order will then be:

Updatecurrent_on set order_number=order_number + 1

Insertinto orders (order_number,order_date,customer_number)

Values ((select order_number from current_on), sysdate,'1000')

Commit

The syntax for modifying the sequence is as follows:

ALTERSEQUENCE [shema.] sequencename

[INCREMENTBY number]

[STARTWITH number]

[MAXVALUEnumber | NOMAXVALUE]

[MINVALUEnumber | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHEnumber | NOCACHE]

[ORDER | NOORDER]

Altersequence order_seq cache 1000

If you want to reset the sequence to its starting value, the only way is to delete the rebuild.

Dropsequence order_seq

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.