oracle implementation

Copy Code code as follows: --Get Early Start time Create or Replace function Fd_lastxunstart (RQ in date) return "string is" Refstr VARCHAR2 (50); V_RQ date; Begin --Get the date of the last day V_RQ: = trunc (RQ); Select Case Decode (trunc (To_char (V_RQ, ' DD ')-1)/10), 0, ' Early ', 1, ' Mid ', ' Late ') When ' early ' then--return to late last month To_char (Add_months (V_RQ,-1), ' yyyymm ') | | ' 21 ' When ' mid ' then To_char (V_RQ, ' yyyymm ') | | ' More ' else To_c

Oracle has a very practical way of implementing summary reports. In the financial system or other report statistics function, I want to have a lot of cases similar to the following report. With the grouping_id function, it will be easy to implement, item No. of force of high charge increase/decrease of average daily balance cost rate of the previous year average daily balance cost rate total cost rate of average daily balance 1 Bank Loan 1 for commerc

The WMS warehouse management system implements the "sending and receiving daily report" data query function. SQL statement: Oracle [SQL] www.2cto.com SELECT AA. PRODUCT_CNAME, AA. PRODUCT_ID, AA. PRODUCT_CODE, AA. MODEL, AA. MASTER_UNIT, AA. OTHER_UNIT, AA. PACKAGE_RATIO, AA. CUSTOMER, SUM (week) AS QTY_OUT_TODAY, QTY_TODAY, SUM (week) AS week, SUM (QTY_OUT) AS QTY_OUT, SUM (QTY_IN) AS QTY_IN FROM (select customer, PRODUCT_CNAME, PRODUCT_ID, PRODUCT_C

The customer requested that hive be used to implement the decode function similar to orale. Okay, start work. Oracle decode function Syntax: DECODE (value, if1, then1, if2, thne2, if3, then3,... else ). The first problem involved is the dynamic input parameters. The decode function requires an even number of input functions, and then implements logic judgment functions similar to if and else. In this case, the java Object... args is used to pass in mu

Now, if a permission number is provided and you want to retrieve the user set with the permission, you need to match the given permission number among multiple permission numbers separated by commas. If you use like, one is less efficient, and the other is inaccurate. Therefore, the comma-separated column-to-row method is used. Currently, this method is only applicable to Oracle databases. This method only requires SQL statements to implement column-t

Now, if you give a permission number to retrieve a collection of users with this permission, you will need to match the given permission number in a comma-delimited number of permission numbers. If you use like to do, an inefficient, two matching is not accurate. So we used a comma-delimited list of ways to change careers. Currently, this method is only suitable for use in Oracle databases. This method requires only SQL statements to implement column

oracle| Dynamic | Cursors Many friends during development, you use a cursor for statistics (no temporary tables) and return statistical results, but the SQL behind the cursor is dynamic, such as the SELECT * from TableName where? Order?. "?" represent the conditions, so how to deal with it. I practiced it with the help of my colleagues. summed it up. Suppose the TableName table has field as follows: Field1 VARCHAR2 (50) Field2 Varchar2 (50) Field3 Va

--oracle implement self-increment id--create a T _studentinfo table The CREATE TABLE t_studentinfo ("id" integer NOT NULL primary key, Xsname NVARCHAR2 (+) NOT NULL, Xsage integer NOT NULL, Mobile varchar (n), Email varchar (+), Address nvarchar2 (300));--Create a Sequence, the sequence name is called seq_studentinfo_identity--to create a sequence (the rules for sequence names are generally recommended to start with SEQ, then underline, followed

Assume that, as shown in the following table, the number of rows corresponding to each I value is not fixed. SQL> select * from T; I a d---------------1 B 10:55:421 A 2008-03-27 10:55:461 D 10:55:302 Z 2008-03-27 10:55:552 t 10:55:59 To obtain the following results, note that the string must be sorted by the time of column D: 1 d, B,2 Z, T This is a typical column-and-column conversion. There are several implementation methods. 1. UDF

Implementation of oracle|server| Server to Oracle Connection server Author: Jennifer This article takes SQL Server 2k as an example to illustrate the specific implementation of SQL Server to an Oracle connection server. 1. require PC to install Oralce client software and sq

forced log mode of the databaseCheck whether the log is forced before:Change:. Stop business, no session, stop listening, and lsnrctl stop;2.3.1 shut down the recyclebin of the database (configured during DDL implementation)2.3.2 create an ogg ddl object3. Deploy the new version of OGG3.1 upload the new version of OGG SoftwareUnbind the tar package and run./ggsci in the corresponding directory.GGSCI> create subdirs3.2. Environment VariablesOracle use

processingOpen C2 (r_c1.pbom_id );LoopFetch C2 into r_c2;Exit when C2 % notfound; -- no record exitedIf r_c2.cjjc_name is not null thenStr1: = str1 | to_char (r_c2.cjjc_name );End if;If r_c2.bz_name is not null thenStr2: = str2 | r_c2.bz_name | to_char (r_c2.dd_count );Elsif r_c2.gx_name is not null thenStr2: = str2 | to_char (r_c2.gx_name) | to_char (r_c2.dd_count );End if;End loop;Close C2;Insert into SC _print_syn (a, B, IP, p_id, r_id)Values (str1, str2, s_ip, pd_id, r_c1.pbom_id );Commit;E

The preceding article has written a field control and table control implementation under a page: http://blog.csdn.net/haqer0825/article/details/6987393 Learn another way to do this today: Using Af:switcher Put two groups of formlayout in a panelbox area when there is no value, display the set of controls with no values, and display the control with value bound to VO when there are values. Defaultfacet represents the default reality which group

1 Create Tabletest_table (2Id Number PRIMARY KEY,3NAMEVARCHAR2(Ten),4NicknameVARCHAR2(Ten)5 )6 7 Createsequence Seq_test_trigger8MinValue19MaxValue999999999999999999999999999TenStart with 1 OneIncrement by 1; A - Create TriggerTest_trigger -BeforeInsert ontest_table the forEach row - when(new.id is NULL) - begin - SelectSeq_test_trigger.nextval + into: New.id - fromdual; + End; A at - Create Triggertri_test_id -BeforeInsert onTest_table--S_depart is the table name - fo

First, create a table:CREATE TABLE test ( ID number (+) NOT null primary key, name VARCHAR2 (32));Then, customize a sequence (sequence)CREATE SEQUENCE test_sequenceincrement by 1--each time you add a few start with 1--counting from 1 nomaxvalue--not setting the maximum nocycle--always accumulating, not looping nocache- -Do not build buffersYou also need to create a trigger (trigger)Create TRIGGER Test_trigger before INSERT on test for each ROW time (new.id is null)--Starts the trigger

First, create a table:CREATE TABLE Example (ID Number (4) Not NULL PRIMARY KEY,NAME VARCHAR (25));Then, customize a sequence (sequence):CREATE SEQUENCE example_sequenceINCREMENT by 1--add a few each timeStart with 1-counting starting from 1Nomaxvalue--Do not set the maximum valueNocycle--keep accumulating, not loopingNOCACHE--Do not build buffersCreate a second trigger:CREATE TRIGGER Example_triger beforeINSERT on example for each ROW time (new.id is null)--Starts the trigger generation ID numbe

"," C2 "," C3 ",) VALUES (Null,null,null,null) /*+ Sys_dl_cursor */is the way SQL loader, which means that bulkcopy needs to be used with caution because he will invalidate the index of the table. An exclusive lock on the table is executed before the SQL loader is executed: Lock table "LC0079999". " Jkl_test "in EXCLUSIVE MODE NOWAIT Conclusion: According to the problems that are currently discovered, bulkcopy should be used only for very few concurrent scenarios such as logs, and it is recomm

See a lot of examples on the net is basically the same, no too much explanation, for a beginner MySQL is a bit difficult, I transferred part of the following text: http://www.cnblogs.com/buro79xxd/archive/2012/08/29/2662489.htmlTarget: 1. Identify requirements: Group According to the department, showing the employees in the department by salary ranking.Create a table: 2. To create the instance data:drop table if exists heyf_t10;CREATE TABLE heyf_t10 (empid int, deptid int, salary decimal (10,2))

Label:Before using the Oracle database, I was using a MySQL database, I know that the MySQL database implementation is updated, no update, there is a statement, But Oracle's ability to do this is a moment of frustration for me, but I'm sure the Oracle database will provide such statements. So... 1) Oracle

The Oracle tutorial being looked at is: PL/SQL implementation of Oracle database task scheduling. Absrtact: This article mainly on the database recovery and system task scheduling, combined with the general database background processing experience, put forward a more practical and innovative solutions, broaden the background of the development of the database of